2 @message |
assert
Sub-query (invariant)
....-> Filter
........-> Aggregate
............-> Table "TDETL" as "K DY" Access By ID
................-> Index "TDETL_FK" Full Scan
Cursor "K"(line, column)
....-> Filter (preliminary)
........-> Nested Loop Join (inner)
............-> Table "TMAIN" as "K M4" Full Scan
............-> Filter
................-> Table "TDETL" as "K D4 DX" Access By ID
....................-> Bitmap And
........................-> Bitmap And
............................-> Bitmap
................................-> Index "TDETL_Z" Range Scan (lower bound: 1/1)
............................-> Bitmap
................................-> Index "TDETL_Y" Range Scan (upper bound: 1/1)
........................-> Bitmap
............................-> Index "TDETL_FK" Range Scan (full match)
Sub-query
....-> Filter
........-> Table "TMAIN" as "M0" Access By ID
............-> Bitmap
................-> Index "TMAIN_X" Range Scan (lower bound: 1/1)
Sub-query
....-> Filter
........-> Table "TMAIN" as "R M0A" Access By ID
- ............-> Bitmap
+ ............-> Bitmap Or
+ ................-> Bitmap
- ................-> Index "TMAIN_X" List Scan (full match)
+ ....................-> Index "TMAIN_X" Range Scan (full match)
+ ................-> Bitmap
+ ....................-> Index "TMAIN_X" Range Scan (full match)
Select Expression(line, column)
....-> Singularity Check
........-> Aggregate
............-> Filter
................-> Recursion
....................-> Filter
........................-> Table "TDETL" as "R D0" Access By ID
............................-> Bitmap
................................-> Index "TDETL_FK" Range Scan (full match)
....................-> Filter
........................-> Table "TDETL" as "R DX" Access By ID
............................-> Bitmap
................................-> Index "TDETL_FK" Range Scan (full match)
Sub-query(line, column)
....-> Singularity Check
........-> Aggregate
............-> Table "TMAIN" as "M1A" Full Scan
Sub-query(line, column)
....-> Singularity Check
........-> Aggregate
............-> Table "TMAIN" as "M1B" Access By ID
................-> Index "TMAIN_X" Full Scan
Sub-query(line, column)
....-> Singularity Check
........-> Aggregate
............-> Table "TDETL" as "D1B" Access By ID
................-> Index "TDETL_FK" Full Scan
Sub-query(line, column)
....-> Singularity Check
........-> Aggregate
............-> Table "TDETL" as "D1C" Full Scan
Select Expression(line, column)
....-> Aggregate
........-> Nested Loop Join (inner)
............-> Filter
................-> Table "TMAIN" as "M2" Access By ID
....................-> Index "TMAIN_PK" Full Scan
........................-> Bitmap
............................-> Index "TMAIN_X" Range Scan (lower bound: 1/1)
............-> Filter
................-> Table "TDETL" as "D" Access By ID
....................-> Bitmap
........................-> Index "TDETL_PK" Unique Scan
Sub-query
....-> Filter
........-> Table "TDETL" as "D" Access By ID
............-> Bitmap
................-> Index "TDETL_FK" Range Scan (full match)
Select Expression(line, column)
....-> Filter
........-> Table "TMAIN" as "M3" Access By ID
............-> Bitmap
................-> Index "TMAIN_X" Range Scan (lower bound: 1/1)
......0 ms
LOG DETAILS:
2024-12-24 12:33:45.271
2024-12-24 12:33:45.271 act = <firebird.qa.plugin.Action object at [hex]>
2024-12-24 12:33:45.271 capsys = <_pytest.capture.CaptureFixture object at [hex]>
2024-12-24 12:33:45.272
2024-12-24 12:33:45.272 @pytest.mark.trace
2024-12-24 12:33:45.272 @pytest.mark.version('>=5.0')
2024-12-24 12:33:45.272 def test_1(act: Action, capsys):
2024-12-24 12:33:45.272
2024-12-24 12:33:45.272 test_script = f"""
2024-12-24 12:33:45.272 recreate table tdetl(id int);
2024-12-24 12:33:45.272 recreate table tmain(id int primary key using index tmain_pk, x int);
2024-12-24 12:33:45.272 recreate table tdetl(id int primary key using index tdetl_pk, pid int references tmain using index tdetl_fk, y int, z int);
2024-12-24 12:33:45.272
2024-12-24 12:33:45.272 insert into tmain(id,x)
2024-12-24 12:33:45.272 select i, -100 + mod(i,200) from (select row_number()over() i from rdb$types rows 200);
2024-12-24 12:33:45.272
2024-12-24 12:33:45.272 insert into tdetl(id, pid, y,z)
2024-12-24 12:33:45.272 select i, 1+mod(i,10), mod(i,30), mod(i,70) from (select row_number()over() i from rdb$types,rdb$types rows 1000);
2024-12-24 12:33:45.272 commit;
2024-12-24 12:33:45.272
2024-12-24 12:33:45.272 create index tmain_x on tmain(x);
2024-12-24 12:33:45.272 create index tdetl_y on tdetl(y);
2024-12-24 12:33:45.273 create index tdetl_z on tdetl(z);
2024-12-24 12:33:45.273
2024-12-24 12:33:45.273 set statistics index tdetl_fk;
2024-12-24 12:33:45.273 commit;
2024-12-24 12:33:45.273
2024-12-24 12:33:45.273 set term ^;
2024-12-24 12:33:45.273 create or alter procedure sp_test returns(id int, c int)
2024-12-24 12:33:45.273 as
2024-12-24 12:33:45.273 declare k cursor for (
2024-12-24 12:33:45.273 select m4.id, d4.y, d4.z
2024-12-24 12:33:45.273 from tmain m4
2024-12-24 12:33:45.273 cross join lateral (
2024-12-24 12:33:45.273 select y, z
2024-12-24 12:33:45.273 from tdetl dx
2024-12-24 12:33:45.273 where
2024-12-24 12:33:45.273 dx.pid = m4.id
2024-12-24 12:33:45.273 and m4.x between dx.y and dx.z
2024-12-24 12:33:45.273 ) d4
2024-12-24 12:33:45.273 where exists(select count(*) from tdetl dy group by dy.pid having count(*) > 2)
2024-12-24 12:33:45.274 );
2024-12-24 12:33:45.274 begin
2024-12-24 12:33:45.274
2024-12-24 12:33:45.274 if ( not exists(select * from tmain m0 where m0.x > 0) ) then
2024-12-24 12:33:45.274 exception;
2024-12-24 12:33:45.274
2024-12-24 12:33:45.274 ----------------------------
2024-12-24 12:33:45.274
2024-12-24 12:33:45.274 with recursive
2024-12-24 12:33:45.274 r as (
2024-12-24 12:33:45.274 select 0 as i, d0.id, d0.pid
2024-12-24 12:33:45.274 from tdetl d0
2024-12-24 12:33:45.274 where d0.pid is null
2024-12-24 12:33:45.274 UNION ALL
2024-12-24 12:33:45.274 select r.i + 1, dx.id, dx.pid
2024-12-24 12:33:45.274 from tdetl dx
2024-12-24 12:33:45.274 join r on dx.pid = r.id
2024-12-24 12:33:45.274 where exists(
2024-12-24 12:33:45.274 select * from tmain m0a
2024-12-24 12:33:45.275 where
2024-12-24 12:33:45.275 m0a.id <> dx.pid
2024-12-24 12:33:45.275 -- ::: NB ::: Rest part is IN-list with computable but non-invariant elements.
2024-12-24 12:33:45.275 -- Execution plan for this kind was changed 07-sep-2023, see:
2024-12-24 12:33:45.275 -- https://github.com/FirebirdSQL/firebird/commit/5df6668c7bf5a4b27e15f687f8c6cc40e260ced8
2024-12-24 12:33:45.275 -- (Allow computable but non-invariant lists to be used for index lookup)
2024-12-24 12:33:45.275 -- See also: tests/functional/tabloid/test_e260ced8.py
2024-12-24 12:33:45.275 -- Here "Index "TMAIN_X" List Scan (full match)" will be!
2024-12-24 12:33:45.275 -- Old: "Index "TMAIN_X" Range Scan (full match)"
2024-12-24 12:33:45.275 and m0a.x in (dx.y, dx.z) -- ### ATTENTION ###
2024-12-24 12:33:45.275 )
2024-12-24 12:33:45.275 )
2024-12-24 12:33:45.275 select count(*) from r where r.i > 2
2024-12-24 12:33:45.275 into c;
2024-12-24 12:33:45.275
2024-12-24 12:33:45.275 ----------------------------
2024-12-24 12:33:45.275 c = decode( (select mod(count(*), 3) from tmain m1a)
2024-12-24 12:33:45.275 ,0, (select min(x) from tmain m1b)
2024-12-24 12:33:45.276 ,1, (select min(d1b.pid) from tdetl d1b)
2024-12-24 12:33:45.276 ,2, (select max(d1c.pid) from tdetl d1c)
2024-12-24 12:33:45.276 );
2024-12-24 12:33:45.276
2024-12-24 12:33:45.276 ----------------------------
2024-12-24 12:33:45.276 for
2024-12-24 12:33:45.276 select m2.id, count(*)
2024-12-24 12:33:45.276 from tmain m2
2024-12-24 12:33:45.276 join tdetl d using(id)
2024-12-24 12:33:45.276 where m2.x > 0
2024-12-24 12:33:45.276 group by 1
2024-12-24 12:33:45.276 into id, c
2024-12-24 12:33:45.276 do
2024-12-24 12:33:45.276 suspend;
2024-12-24 12:33:45.276 ----------------------------
2024-12-24 12:33:45.276 for
2024-12-24 12:33:45.276 select m3.id, 0
2024-12-24 12:33:45.276 from tmain m3
2024-12-24 12:33:45.276 where
2024-12-24 12:33:45.277 m3.x > 0 and
2024-12-24 12:33:45.277 not exists(select * from tdetl d where d.pid = m3.id)
2024-12-24 12:33:45.277 into id, c
2024-12-24 12:33:45.277 do
2024-12-24 12:33:45.277 suspend;
2024-12-24 12:33:45.277 end
2024-12-24 12:33:45.277 ^
2024-12-24 12:33:45.277 set term ;^
2024-12-24 12:33:45.277 commit;
2024-12-24 12:33:45.277 """
2024-12-24 12:33:45.277
2024-12-24 12:33:45.277 with act.trace(db_events=trace, encoding = locale.getpreferredencoding(), encoding_errors='utf8'):
2024-12-24 12:33:45.277 act.isql(switches = ['-q'], input = test_script, combine_output = True, io_enc = locale.getpreferredencoding())
2024-12-24 12:33:45.277
2024-12-24 12:33:45.277 # Parse trace log:
2024-12-24 12:33:45.277 start_show = 0
2024-12-24 12:33:45.277 for line in act.trace_log:
2024-12-24 12:33:45.277 if line.startswith("^^^"):
2024-12-24 12:33:45.277 start_show = 1
2024-12-24 12:33:45.278 continue
2024-12-24 12:33:45.278 if start_show and line.rstrip():
2024-12-24 12:33:45.278 print( replace_leading(line,'.') )
2024-12-24 12:33:45.278
2024-12-24 12:33:45.278 expected_stdout = f"""
2024-12-24 12:33:45.278 Sub-query (invariant)
2024-12-24 12:33:45.278 ....-> Filter
2024-12-24 12:33:45.278 ........-> Aggregate
2024-12-24 12:33:45.278 ............-> Table "TDETL" as "K DY" Access By ID
2024-12-24 12:33:45.278 ................-> Index "TDETL_FK" Full Scan
2024-12-24 12:33:45.278 Cursor "K"(line, column)
2024-12-24 12:33:45.278 ....-> Filter (preliminary)
2024-12-24 12:33:45.278 ........-> Nested Loop Join (inner)
2024-12-24 12:33:45.278 ............-> Table "TMAIN" as "K M4" Full Scan
2024-12-24 12:33:45.278 ............-> Filter
2024-12-24 12:33:45.278 ................-> Table "TDETL" as "K D4 DX" Access By ID
2024-12-24 12:33:45.278 ....................-> Bitmap And
2024-12-24 12:33:45.278 ........................-> Bitmap And
2024-12-24 12:33:45.278 ............................-> Bitmap
2024-12-24 12:33:45.279 ................................-> Index "TDETL_Z" Range Scan (lower bound: 1/1)
2024-12-24 12:33:45.279 ............................-> Bitmap
2024-12-24 12:33:45.279 ................................-> Index "TDETL_Y" Range Scan (upper bound: 1/1)
2024-12-24 12:33:45.279 ........................-> Bitmap
2024-12-24 12:33:45.279 ............................-> Index "TDETL_FK" Range Scan (full match)
2024-12-24 12:33:45.279 Sub-query
2024-12-24 12:33:45.279 ....-> Filter
2024-12-24 12:33:45.279 ........-> Table "TMAIN" as "M0" Access By ID
2024-12-24 12:33:45.279 ............-> Bitmap
2024-12-24 12:33:45.279 ................-> Index "TMAIN_X" Range Scan (lower bound: 1/1)
2024-12-24 12:33:45.279 Sub-query
2024-12-24 12:33:45.279 ....-> Filter
2024-12-24 12:33:45.279 ........-> Table "TMAIN" as "R M0A" Access By ID
2024-12-24 12:33:45.279 ............-> Bitmap
2024-12-24 12:33:45.279 ................-> Index "TMAIN_X" List Scan (full match)
2024-12-24 12:33:45.279 Select Expression(line, column)
2024-12-24 12:33:45.279 ....-> Singularity Check
2024-12-24 12:33:45.279 ........-> Aggregate
2024-12-24 12:33:45.279 ............-> Filter
2024-12-24 12:33:45.280 ................-> Recursion
2024-12-24 12:33:45.280 ....................-> Filter
2024-12-24 12:33:45.280 ........................-> Table "TDETL" as "R D0" Access By ID
2024-12-24 12:33:45.280 ............................-> Bitmap
2024-12-24 12:33:45.280 ................................-> Index "TDETL_FK" Range Scan (full match)
2024-12-24 12:33:45.280 ....................-> Filter
2024-12-24 12:33:45.280 ........................-> Table "TDETL" as "R DX" Access By ID
2024-12-24 12:33:45.280 ............................-> Bitmap
2024-12-24 12:33:45.280 ................................-> Index "TDETL_FK" Range Scan (full match)
2024-12-24 12:33:45.280 Sub-query(line, column)
2024-12-24 12:33:45.280 ....-> Singularity Check
2024-12-24 12:33:45.280 ........-> Aggregate
2024-12-24 12:33:45.280 ............-> Table "TMAIN" as "M1A" Full Scan
2024-12-24 12:33:45.280 Sub-query(line, column)
2024-12-24 12:33:45.280 ....-> Singularity Check
2024-12-24 12:33:45.280 ........-> Aggregate
2024-12-24 12:33:45.280 ............-> Table "TMAIN" as "M1B" Access By ID
2024-12-24 12:33:45.280 ................-> Index "TMAIN_X" Full Scan
2024-12-24 12:33:45.280 Sub-query(line, column)
2024-12-24 12:33:45.281 ....-> Singularity Check
2024-12-24 12:33:45.281 ........-> Aggregate
2024-12-24 12:33:45.281 ............-> Table "TDETL" as "D1B" Access By ID
2024-12-24 12:33:45.281 ................-> Index "TDETL_FK" Full Scan
2024-12-24 12:33:45.281 Sub-query(line, column)
2024-12-24 12:33:45.281 ....-> Singularity Check
2024-12-24 12:33:45.281 ........-> Aggregate
2024-12-24 12:33:45.281 ............-> Table "TDETL" as "D1C" Full Scan
2024-12-24 12:33:45.281 Select Expression(line, column)
2024-12-24 12:33:45.281 ....-> Aggregate
2024-12-24 12:33:45.281 ........-> Nested Loop Join (inner)
2024-12-24 12:33:45.281 ............-> Filter
2024-12-24 12:33:45.281 ................-> Table "TMAIN" as "M2" Access By ID
2024-12-24 12:33:45.281 ....................-> Index "TMAIN_PK" Full Scan
2024-12-24 12:33:45.281 ........................-> Bitmap
2024-12-24 12:33:45.281 ............................-> Index "TMAIN_X" Range Scan (lower bound: 1/1)
2024-12-24 12:33:45.281 ............-> Filter
2024-12-24 12:33:45.281 ................-> Table "TDETL" as "D" Access By ID
2024-12-24 12:33:45.281 ....................-> Bitmap
2024-12-24 12:33:45.281 ........................-> Index "TDETL_PK" Unique Scan
2024-12-24 12:33:45.282 Sub-query
2024-12-24 12:33:45.282 ....-> Filter
2024-12-24 12:33:45.282 ........-> Table "TDETL" as "D" Access By ID
2024-12-24 12:33:45.282 ............-> Bitmap
2024-12-24 12:33:45.282 ................-> Index "TDETL_FK" Range Scan (full match)
2024-12-24 12:33:45.282 Select Expression(line, column)
2024-12-24 12:33:45.282 ....-> Filter
2024-12-24 12:33:45.282 ........-> Table "TMAIN" as "M3" Access By ID
2024-12-24 12:33:45.282 ............-> Bitmap
2024-12-24 12:33:45.282 ................-> Index "TMAIN_X" Range Scan (lower bound: 1/1)
2024-12-24 12:33:45.282 ......0 ms
2024-12-24 12:33:45.282 """
2024-12-24 12:33:45.282
2024-12-24 12:33:45.282 act.expected_stdout = expected_stdout
2024-12-24 12:33:45.282 act.stdout = capsys.readouterr().out
2024-12-24 12:33:45.282 > assert act.clean_stdout == act.clean_expected_stdout
2024-12-24 12:33:45.282 E assert
2024-12-24 12:33:45.282 E Sub-query (invariant)
2024-12-24 12:33:45.282 E ....-> Filter
2024-12-24 12:33:45.283 E ........-> Aggregate
2024-12-24 12:33:45.283 E ............-> Table "TDETL" as "K DY" Access By ID
2024-12-24 12:33:45.283 E ................-> Index "TDETL_FK" Full Scan
2024-12-24 12:33:45.283 E Cursor "K"(line, column)
2024-12-24 12:33:45.283 E ....-> Filter (preliminary)
2024-12-24 12:33:45.283 E ........-> Nested Loop Join (inner)
2024-12-24 12:33:45.283 E ............-> Table "TMAIN" as "K M4" Full Scan
2024-12-24 12:33:45.283 E ............-> Filter
2024-12-24 12:33:45.283 E ................-> Table "TDETL" as "K D4 DX" Access By ID
2024-12-24 12:33:45.283 E ....................-> Bitmap And
2024-12-24 12:33:45.283 E ........................-> Bitmap And
2024-12-24 12:33:45.283 E ............................-> Bitmap
2024-12-24 12:33:45.283 E ................................-> Index "TDETL_Z" Range Scan (lower bound: 1/1)
2024-12-24 12:33:45.283 E ............................-> Bitmap
2024-12-24 12:33:45.283 E ................................-> Index "TDETL_Y" Range Scan (upper bound: 1/1)
2024-12-24 12:33:45.283 E ........................-> Bitmap
2024-12-24 12:33:45.283 E ............................-> Index "TDETL_FK" Range Scan (full match)
2024-12-24 12:33:45.283 E Sub-query
2024-12-24 12:33:45.283 E ....-> Filter
2024-12-24 12:33:45.284 E ........-> Table "TMAIN" as "M0" Access By ID
2024-12-24 12:33:45.284 E ............-> Bitmap
2024-12-24 12:33:45.284 E ................-> Index "TMAIN_X" Range Scan (lower bound: 1/1)
2024-12-24 12:33:45.284 E Sub-query
2024-12-24 12:33:45.284 E ....-> Filter
2024-12-24 12:33:45.284 E ........-> Table "TMAIN" as "R M0A" Access By ID
2024-12-24 12:33:45.284 E - ............-> Bitmap
2024-12-24 12:33:45.284 E + ............-> Bitmap Or
2024-12-24 12:33:45.284 E + ................-> Bitmap
2024-12-24 12:33:45.284 E - ................-> Index "TMAIN_X" List Scan (full match)
2024-12-24 12:33:45.284 E + ....................-> Index "TMAIN_X" Range Scan (full match)
2024-12-24 12:33:45.284 E + ................-> Bitmap
2024-12-24 12:33:45.284 E + ....................-> Index "TMAIN_X" Range Scan (full match)
2024-12-24 12:33:45.284 E Select Expression(line, column)
2024-12-24 12:33:45.285 E ....-> Singularity Check
2024-12-24 12:33:45.285 E ........-> Aggregate
2024-12-24 12:33:45.285 E ............-> Filter
2024-12-24 12:33:45.285 E ................-> Recursion
2024-12-24 12:33:45.285 E ....................-> Filter
2024-12-24 12:33:45.285 E ........................-> Table "TDETL" as "R D0" Access By ID
2024-12-24 12:33:45.285 E ............................-> Bitmap
2024-12-24 12:33:45.285 E ................................-> Index "TDETL_FK" Range Scan (full match)
2024-12-24 12:33:45.285 E ....................-> Filter
2024-12-24 12:33:45.285 E ........................-> Table "TDETL" as "R DX" Access By ID
2024-12-24 12:33:45.285 E ............................-> Bitmap
2024-12-24 12:33:45.285 E ................................-> Index "TDETL_FK" Range Scan (full match)
2024-12-24 12:33:45.285 E Sub-query(line, column)
2024-12-24 12:33:45.285 E ....-> Singularity Check
2024-12-24 12:33:45.285 E ........-> Aggregate
2024-12-24 12:33:45.285 E ............-> Table "TMAIN" as "M1A" Full Scan
2024-12-24 12:33:45.285 E Sub-query(line, column)
2024-12-24 12:33:45.285 E ....-> Singularity Check
2024-12-24 12:33:45.285 E ........-> Aggregate
2024-12-24 12:33:45.286 E ............-> Table "TMAIN" as "M1B" Access By ID
2024-12-24 12:33:45.286 E ................-> Index "TMAIN_X" Full Scan
2024-12-24 12:33:45.286 E Sub-query(line, column)
2024-12-24 12:33:45.286 E ....-> Singularity Check
2024-12-24 12:33:45.286 E ........-> Aggregate
2024-12-24 12:33:45.286 E ............-> Table "TDETL" as "D1B" Access By ID
2024-12-24 12:33:45.286 E ................-> Index "TDETL_FK" Full Scan
2024-12-24 12:33:45.286 E Sub-query(line, column)
2024-12-24 12:33:45.286 E ....-> Singularity Check
2024-12-24 12:33:45.286 E ........-> Aggregate
2024-12-24 12:33:45.286 E ............-> Table "TDETL" as "D1C" Full Scan
2024-12-24 12:33:45.286 E Select Expression(line, column)
2024-12-24 12:33:45.286 E ....-> Aggregate
2024-12-24 12:33:45.286 E ........-> Nested Loop Join (inner)
2024-12-24 12:33:45.286 E ............-> Filter
2024-12-24 12:33:45.286 E ................-> Table "TMAIN" as "M2" Access By ID
2024-12-24 12:33:45.286 E ....................-> Index "TMAIN_PK" Full Scan
2024-12-24 12:33:45.286 E ........................-> Bitmap
2024-12-24 12:33:45.286 E ............................-> Index "TMAIN_X" Range Scan (lower bound: 1/1)
2024-12-24 12:33:45.286 E ............-> Filter
2024-12-24 12:33:45.287 E ................-> Table "TDETL" as "D" Access By ID
2024-12-24 12:33:45.287 E ....................-> Bitmap
2024-12-24 12:33:45.287 E ........................-> Index "TDETL_PK" Unique Scan
2024-12-24 12:33:45.287 E Sub-query
2024-12-24 12:33:45.287 E ....-> Filter
2024-12-24 12:33:45.287 E ........-> Table "TDETL" as "D" Access By ID
2024-12-24 12:33:45.287 E ............-> Bitmap
2024-12-24 12:33:45.287 E ................-> Index "TDETL_FK" Range Scan (full match)
2024-12-24 12:33:45.287 E Select Expression(line, column)
2024-12-24 12:33:45.287 E ....-> Filter
2024-12-24 12:33:45.287 E ........-> Table "TMAIN" as "M3" Access By ID
2024-12-24 12:33:45.287 E ............-> Bitmap
2024-12-24 12:33:45.287 E ................-> Index "TMAIN_X" Range Scan (lower bound: 1/1)
2024-12-24 12:33:45.287 E ......0 ms
2024-12-24 12:33:45.287
2024-12-24 12:33:45.287 tests\bugs\gh_7466_plans_tracking_test.py:280: AssertionError
2024-12-24 12:33:45.287 ---------------------------- Captured stdout setup ----------------------------
2024-12-24 12:33:45.287 Creating db: localhost:H:\QA\temp\qa2024.tmp\fbqa\test_11566\test.fdb [page_size=None, sql_dialect=None, charset='NONE', user=SYSDBA, password=masterkey]
|
3 #text |
act = <firebird.qa.plugin.Action pytest object at [hex]>
capsys = <_pytest.capture.CaptureFixture pytest object at [hex]>
@pytest.mark.trace
@pytest.mark.version('>=5.0')
def test_1(act: Action, capsys):
test_script = f"""
recreate table tdetl(id int);
recreate table tmain(id int primary key using index tmain_pk, x int);
recreate table tdetl(id int primary key using index tdetl_pk, pid int references tmain using index tdetl_fk, y int, z int);
insert into tmain(id,x)
select i, -100 + mod(i,200) from (select row_number()over() i from rdb$types rows 200);
insert into tdetl(id, pid, y,z)
select i, 1+mod(i,10), mod(i,30), mod(i,70) from (select row_number()over() i from rdb$types,rdb$types rows 1000);
commit;
create index tmain_x on tmain(x);
create index tdetl_y on tdetl(y);
create index tdetl_z on tdetl(z);
set statistics index tdetl_fk;
commit;
set term ^;
create or alter procedure sp_test returns(id int, c int)
as
declare k cursor for (
select m4.id, d4.y, d4.z
from tmain m4
cross join lateral (
select y, z
from tdetl dx
where
dx.pid = m4.id
and m4.x between dx.y and dx.z
) d4
where exists(select count(*) from tdetl dy group by dy.pid having count(*) > 2)
);
begin
if ( not exists(select * from tmain m0 where m0.x > 0) ) then
exception;
----------------------------
with recursive
r as (
select 0 as i, d0.id, d0.pid
from tdetl d0
where d0.pid is null
UNION ALL
select r.i + 1, dx.id, dx.pid
from tdetl dx
join r on dx.pid = r.id
where exists(
select * from tmain m0a
where
m0a.id <> dx.pid
-- ::: NB ::: Rest part is IN-list with computable but non-invariant elements.
-- Execution plan for this kind was changed 07-sep-2023, see:
-- https://github.com/FirebirdSQL/firebird/commit/5df6668c7bf5a4b27e15f687f8c6cc40e260ced8
-- (Allow computable but non-invariant lists to be used for index lookup)
-- See also: tests/functional/tabloid/test_e260ced8.py
-- Here "Index "TMAIN_X" List Scan (full match)" will be!
-- Old: "Index "TMAIN_X" Range Scan (full match)"
and m0a.x in (dx.y, dx.z) -- ### ATTENTION ###
)
)
select count(*) from r where r.i > 2
into c;
----------------------------
c = decode( (select mod(count(*), 3) from tmain m1a)
,0, (select min(x) from tmain m1b)
,1, (select min(d1b.pid) from tdetl d1b)
,2, (select max(d1c.pid) from tdetl d1c)
);
----------------------------
for
select m2.id, count(*)
from tmain m2
join tdetl d using(id)
where m2.x > 0
group by 1
into id, c
do
suspend;
----------------------------
for
select m3.id, 0
from tmain m3
where
m3.x > 0 and
not exists(select * from tdetl d where d.pid = m3.id)
into id, c
do
suspend;
end
^
set term ;^
commit;
"""
with act.trace(db_events=trace, encoding = locale.getpreferredencoding(), encoding_errors='utf8'):
act.isql(switches = ['-q'], input = test_script, combine_output = True, io_enc = locale.getpreferredencoding())
# Parse trace log:
start_show = 0
for line in act.trace_log:
if line.startswith("^^^"):
start_show = 1
continue
if start_show and line.rstrip():
print( replace_leading(line,'.') )
expected_stdout = f"""
Sub-query (invariant)
....-> Filter
........-> Aggregate
............-> Table "TDETL" as "K DY" Access By ID
................-> Index "TDETL_FK" Full Scan
Cursor "K"(line, column)
....-> Filter (preliminary)
........-> Nested Loop Join (inner)
............-> Table "TMAIN" as "K M4" Full Scan
............-> Filter
................-> Table "TDETL" as "K D4 DX" Access By ID
....................-> Bitmap And
........................-> Bitmap And
............................-> Bitmap
................................-> Index "TDETL_Z" Range Scan (lower bound: 1/1)
............................-> Bitmap
................................-> Index "TDETL_Y" Range Scan (upper bound: 1/1)
........................-> Bitmap
............................-> Index "TDETL_FK" Range Scan (full match)
Sub-query
....-> Filter
........-> Table "TMAIN" as "M0" Access By ID
............-> Bitmap
................-> Index "TMAIN_X" Range Scan (lower bound: 1/1)
Sub-query
....-> Filter
........-> Table "TMAIN" as "R M0A" Access By ID
............-> Bitmap
................-> Index "TMAIN_X" List Scan (full match)
Select Expression(line, column)
....-> Singularity Check
........-> Aggregate
............-> Filter
................-> Recursion
....................-> Filter
........................-> Table "TDETL" as "R D0" Access By ID
............................-> Bitmap
................................-> Index "TDETL_FK" Range Scan (full match)
....................-> Filter
........................-> Table "TDETL" as "R DX" Access By ID
............................-> Bitmap
................................-> Index "TDETL_FK" Range Scan (full match)
Sub-query(line, column)
....-> Singularity Check
........-> Aggregate
............-> Table "TMAIN" as "M1A" Full Scan
Sub-query(line, column)
....-> Singularity Check
........-> Aggregate
............-> Table "TMAIN" as "M1B" Access By ID
................-> Index "TMAIN_X" Full Scan
Sub-query(line, column)
....-> Singularity Check
........-> Aggregate
............-> Table "TDETL" as "D1B" Access By ID
................-> Index "TDETL_FK" Full Scan
Sub-query(line, column)
....-> Singularity Check
........-> Aggregate
............-> Table "TDETL" as "D1C" Full Scan
Select Expression(line, column)
....-> Aggregate
........-> Nested Loop Join (inner)
............-> Filter
................-> Table "TMAIN" as "M2" Access By ID
....................-> Index "TMAIN_PK" Full Scan
........................-> Bitmap
............................-> Index "TMAIN_X" Range Scan (lower bound: 1/1)
............-> Filter
................-> Table "TDETL" as "D" Access By ID
....................-> Bitmap
........................-> Index "TDETL_PK" Unique Scan
Sub-query
....-> Filter
........-> Table "TDETL" as "D" Access By ID
............-> Bitmap
................-> Index "TDETL_FK" Range Scan (full match)
Select Expression(line, column)
....-> Filter
........-> Table "TMAIN" as "M3" Access By ID
............-> Bitmap
................-> Index "TMAIN_X" Range Scan (lower bound: 1/1)
......0 ms
"""
act.expected_stdout = expected_stdout
act.stdout = capsys.readouterr().out
> assert act.clean_stdout == act.clean_expected_stdout
E assert
E Sub-query (invariant)
E ....-> Filter
E ........-> Aggregate
E ............-> Table "TDETL" as "K DY" Access By ID
E ................-> Index "TDETL_FK" Full Scan
E Cursor "K"(line, column)
E ....-> Filter (preliminary)
E ........-> Nested Loop Join (inner)
E ............-> Table "TMAIN" as "K M4" Full Scan
E ............-> Filter
E ................-> Table "TDETL" as "K D4 DX" Access By ID
E ....................-> Bitmap And
E ........................-> Bitmap And
E ............................-> Bitmap
E ................................-> Index "TDETL_Z" Range Scan (lower bound: 1/1)
E ............................-> Bitmap
E ................................-> Index "TDETL_Y" Range Scan (upper bound: 1/1)
E ........................-> Bitmap
E ............................-> Index "TDETL_FK" Range Scan (full match)
E Sub-query
E ....-> Filter
E ........-> Table "TMAIN" as "M0" Access By ID
E ............-> Bitmap
E ................-> Index "TMAIN_X" Range Scan (lower bound: 1/1)
E Sub-query
E ....-> Filter
E ........-> Table "TMAIN" as "R M0A" Access By ID
E - ............-> Bitmap
E + ............-> Bitmap Or
E + ................-> Bitmap
E - ................-> Index "TMAIN_X" List Scan (full match)
E + ....................-> Index "TMAIN_X" Range Scan (full match)
E + ................-> Bitmap
E + ....................-> Index "TMAIN_X" Range Scan (full match)
E Select Expression(line, column)
E ....-> Singularity Check
E ........-> Aggregate
E ............-> Filter
E ................-> Recursion
E ....................-> Filter
E ........................-> Table "TDETL" as "R D0" Access By ID
E ............................-> Bitmap
E ................................-> Index "TDETL_FK" Range Scan (full match)
E ....................-> Filter
E ........................-> Table "TDETL" as "R DX" Access By ID
E ............................-> Bitmap
E ................................-> Index "TDETL_FK" Range Scan (full match)
E Sub-query(line, column)
E ....-> Singularity Check
E ........-> Aggregate
E ............-> Table "TMAIN" as "M1A" Full Scan
E Sub-query(line, column)
E ....-> Singularity Check
E ........-> Aggregate
E ............-> Table "TMAIN" as "M1B" Access By ID
E ................-> Index "TMAIN_X" Full Scan
E Sub-query(line, column)
E ....-> Singularity Check
E ........-> Aggregate
E ............-> Table "TDETL" as "D1B" Access By ID
E ................-> Index "TDETL_FK" Full Scan
E Sub-query(line, column)
E ....-> Singularity Check
E ........-> Aggregate
E ............-> Table "TDETL" as "D1C" Full Scan
E Select Expression(line, column)
E ....-> Aggregate
E ........-> Nested Loop Join (inner)
E ............-> Filter
E ................-> Table "TMAIN" as "M2" Access By ID
E ....................-> Index "TMAIN_PK" Full Scan
E ........................-> Bitmap
E ............................-> Index "TMAIN_X" Range Scan (lower bound: 1/1)
E ............-> Filter
E ................-> Table "TDETL" as "D" Access By ID
E ....................-> Bitmap
E ........................-> Index "TDETL_PK" Unique Scan
E Sub-query
E ....-> Filter
E ........-> Table "TDETL" as "D" Access By ID
E ............-> Bitmap
E ................-> Index "TDETL_FK" Range Scan (full match)
E Select Expression(line, column)
E ....-> Filter
E ........-> Table "TMAIN" as "M3" Access By ID
E ............-> Bitmap
E ................-> Index "TMAIN_X" Range Scan (lower bound: 1/1)
E ......0 ms
tests\bugs\gh_7466_plans_tracking_test.py:280: AssertionError
|