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 23:47:15.181
2024-12-24 23:47:15.185 act = <firebird.qa.plugin.Action object at [hex]>
2024-12-24 23:47:15.191 capsys = <_pytest.capture.CaptureFixture object at [hex]>
2024-12-24 23:47:15.195
2024-12-24 23:47:15.200 @pytest.mark.trace
2024-12-24 23:47:15.205 @pytest.mark.version('>=5.0')
2024-12-24 23:47:15.209 def test_1(act: Action, capsys):
2024-12-24 23:47:15.214
2024-12-24 23:47:15.219 test_script = f"""
2024-12-24 23:47:15.223 recreate table tdetl(id int);
2024-12-24 23:47:15.228 recreate table tmain(id int primary key using index tmain_pk, x int);
2024-12-24 23:47:15.233 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 23:47:15.237
2024-12-24 23:47:15.242 insert into tmain(id,x)
2024-12-24 23:47:15.247 select i, -100 + mod(i,200) from (select row_number()over() i from rdb$types rows 200);
2024-12-24 23:47:15.251
2024-12-24 23:47:15.256 insert into tdetl(id, pid, y,z)
2024-12-24 23:47:15.261 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 23:47:15.266 commit;
2024-12-24 23:47:15.270
2024-12-24 23:47:15.275 create index tmain_x on tmain(x);
2024-12-24 23:47:15.280 create index tdetl_y on tdetl(y);
2024-12-24 23:47:15.285 create index tdetl_z on tdetl(z);
2024-12-24 23:47:15.291
2024-12-24 23:47:15.296 set statistics index tdetl_fk;
2024-12-24 23:47:15.302 commit;
2024-12-24 23:47:15.307
2024-12-24 23:47:15.312 set term ^;
2024-12-24 23:47:15.317 create or alter procedure sp_test returns(id int, c int)
2024-12-24 23:47:15.322 as
2024-12-24 23:47:15.327 declare k cursor for (
2024-12-24 23:47:15.332 select m4.id, d4.y, d4.z
2024-12-24 23:47:15.337 from tmain m4
2024-12-24 23:47:15.343 cross join lateral (
2024-12-24 23:47:15.348 select y, z
2024-12-24 23:47:15.353 from tdetl dx
2024-12-24 23:47:15.358 where
2024-12-24 23:47:15.363 dx.pid = m4.id
2024-12-24 23:47:15.368 and m4.x between dx.y and dx.z
2024-12-24 23:47:15.373 ) d4
2024-12-24 23:47:15.378 where exists(select count(*) from tdetl dy group by dy.pid having count(*) > 2)
2024-12-24 23:47:15.382 );
2024-12-24 23:47:15.387 begin
2024-12-24 23:47:15.392
2024-12-24 23:47:15.397 if ( not exists(select * from tmain m0 where m0.x > 0) ) then
2024-12-24 23:47:15.403 exception;
2024-12-24 23:47:15.408
2024-12-24 23:47:15.413 ----------------------------
2024-12-24 23:47:15.418
2024-12-24 23:47:15.422 with recursive
2024-12-24 23:47:15.427 r as (
2024-12-24 23:47:15.432 select 0 as i, d0.id, d0.pid
2024-12-24 23:47:15.437 from tdetl d0
2024-12-24 23:47:15.441 where d0.pid is null
2024-12-24 23:47:15.446 UNION ALL
2024-12-24 23:47:15.451 select r.i + 1, dx.id, dx.pid
2024-12-24 23:47:15.456 from tdetl dx
2024-12-24 23:47:15.461 join r on dx.pid = r.id
2024-12-24 23:47:15.466 where exists(
2024-12-24 23:47:15.470 select * from tmain m0a
2024-12-24 23:47:15.475 where
2024-12-24 23:47:15.480 m0a.id <> dx.pid
2024-12-24 23:47:15.486 -- ::: NB ::: Rest part is IN-list with computable but non-invariant elements.
2024-12-24 23:47:15.491 -- Execution plan for this kind was changed 07-sep-2023, see:
2024-12-24 23:47:15.496 -- https://github.com/FirebirdSQL/firebird/commit/5df6668c7bf5a4b27e15f687f8c6cc40e260ced8
2024-12-24 23:47:15.501 -- (Allow computable but non-invariant lists to be used for index lookup)
2024-12-24 23:47:15.505 -- See also: tests/functional/tabloid/test_e260ced8.py
2024-12-24 23:47:15.510 -- Here "Index "TMAIN_X" List Scan (full match)" will be!
2024-12-24 23:47:15.515 -- Old: "Index "TMAIN_X" Range Scan (full match)"
2024-12-24 23:47:15.520 and m0a.x in (dx.y, dx.z) -- ### ATTENTION ###
2024-12-24 23:47:15.525 )
2024-12-24 23:47:15.529 )
2024-12-24 23:47:15.534 select count(*) from r where r.i > 2
2024-12-24 23:47:15.539 into c;
2024-12-24 23:47:15.543
2024-12-24 23:47:15.548 ----------------------------
2024-12-24 23:47:15.553 c = decode( (select mod(count(*), 3) from tmain m1a)
2024-12-24 23:47:15.557 ,0, (select min(x) from tmain m1b)
2024-12-24 23:47:15.562 ,1, (select min(d1b.pid) from tdetl d1b)
2024-12-24 23:47:15.566 ,2, (select max(d1c.pid) from tdetl d1c)
2024-12-24 23:47:15.571 );
2024-12-24 23:47:15.575
2024-12-24 23:47:15.580 ----------------------------
2024-12-24 23:47:15.584 for
2024-12-24 23:47:15.589 select m2.id, count(*)
2024-12-24 23:47:15.594 from tmain m2
2024-12-24 23:47:15.598 join tdetl d using(id)
2024-12-24 23:47:15.603 where m2.x > 0
2024-12-24 23:47:15.607 group by 1
2024-12-24 23:47:15.612 into id, c
2024-12-24 23:47:15.616 do
2024-12-24 23:47:15.621 suspend;
2024-12-24 23:47:15.625 ----------------------------
2024-12-24 23:47:15.630 for
2024-12-24 23:47:15.634 select m3.id, 0
2024-12-24 23:47:15.639 from tmain m3
2024-12-24 23:47:15.643 where
2024-12-24 23:47:15.648 m3.x > 0 and
2024-12-24 23:47:15.652 not exists(select * from tdetl d where d.pid = m3.id)
2024-12-24 23:47:15.657 into id, c
2024-12-24 23:47:15.662 do
2024-12-24 23:47:15.666 suspend;
2024-12-24 23:47:15.671 end
2024-12-24 23:47:15.676 ^
2024-12-24 23:47:15.680 set term ;^
2024-12-24 23:47:15.685 commit;
2024-12-24 23:47:15.689 """
2024-12-24 23:47:15.694
2024-12-24 23:47:15.698 with act.trace(db_events=trace, encoding = locale.getpreferredencoding(), encoding_errors='utf8'):
2024-12-24 23:47:15.703 act.isql(switches = ['-q'], input = test_script, combine_output = True, io_enc = locale.getpreferredencoding())
2024-12-24 23:47:15.708
2024-12-24 23:47:15.712 # Parse trace log:
2024-12-24 23:47:15.717 start_show = 0
2024-12-24 23:47:15.722 for line in act.trace_log:
2024-12-24 23:47:15.727 if line.startswith("^^^"):
2024-12-24 23:47:15.733 start_show = 1
2024-12-24 23:47:15.739 continue
2024-12-24 23:47:15.745 if start_show and line.rstrip():
2024-12-24 23:47:15.751 print( replace_leading(line,'.') )
2024-12-24 23:47:15.756
2024-12-24 23:47:15.761 expected_stdout = f"""
2024-12-24 23:47:15.766 Sub-query (invariant)
2024-12-24 23:47:15.771 ....-> Filter
2024-12-24 23:47:15.780 ........-> Aggregate
2024-12-24 23:47:15.789 ............-> Table "TDETL" as "K DY" Access By ID
2024-12-24 23:47:15.796 ................-> Index "TDETL_FK" Full Scan
2024-12-24 23:47:15.801 Cursor "K"(line, column)
2024-12-24 23:47:15.807 ....-> Filter (preliminary)
2024-12-24 23:47:15.813 ........-> Nested Loop Join (inner)
2024-12-24 23:47:15.818 ............-> Table "TMAIN" as "K M4" Full Scan
2024-12-24 23:47:15.823 ............-> Filter
2024-12-24 23:47:15.829 ................-> Table "TDETL" as "K D4 DX" Access By ID
2024-12-24 23:47:15.834 ....................-> Bitmap And
2024-12-24 23:47:15.838 ........................-> Bitmap And
2024-12-24 23:47:15.846 ............................-> Bitmap
2024-12-24 23:47:15.857 ................................-> Index "TDETL_Z" Range Scan (lower bound: 1/1)
2024-12-24 23:47:15.865 ............................-> Bitmap
2024-12-24 23:47:15.871 ................................-> Index "TDETL_Y" Range Scan (upper bound: 1/1)
2024-12-24 23:47:15.879 ........................-> Bitmap
2024-12-24 23:47:15.887 ............................-> Index "TDETL_FK" Range Scan (full match)
2024-12-24 23:47:15.893 Sub-query
2024-12-24 23:47:15.899 ....-> Filter
2024-12-24 23:47:15.905 ........-> Table "TMAIN" as "M0" Access By ID
2024-12-24 23:47:15.911 ............-> Bitmap
2024-12-24 23:47:15.918 ................-> Index "TMAIN_X" Range Scan (lower bound: 1/1)
2024-12-24 23:47:15.925 Sub-query
2024-12-24 23:47:15.931 ....-> Filter
2024-12-24 23:47:15.938 ........-> Table "TMAIN" as "R M0A" Access By ID
2024-12-24 23:47:15.945 ............-> Bitmap
2024-12-24 23:47:15.951 ................-> Index "TMAIN_X" List Scan (full match)
2024-12-24 23:47:15.957 Select Expression(line, column)
2024-12-24 23:47:15.963 ....-> Singularity Check
2024-12-24 23:47:15.970 ........-> Aggregate
2024-12-24 23:47:15.975 ............-> Filter
2024-12-24 23:47:15.980 ................-> Recursion
2024-12-24 23:47:15.987 ....................-> Filter
2024-12-24 23:47:15.994 ........................-> Table "TDETL" as "R D0" Access By ID
2024-12-24 23:47:16.000 ............................-> Bitmap
2024-12-24 23:47:16.005 ................................-> Index "TDETL_FK" Range Scan (full match)
2024-12-24 23:47:16.011 ....................-> Filter
2024-12-24 23:47:16.016 ........................-> Table "TDETL" as "R DX" Access By ID
2024-12-24 23:47:16.022 ............................-> Bitmap
2024-12-24 23:47:16.027 ................................-> Index "TDETL_FK" Range Scan (full match)
2024-12-24 23:47:16.034 Sub-query(line, column)
2024-12-24 23:47:16.042 ....-> Singularity Check
2024-12-24 23:47:16.049 ........-> Aggregate
2024-12-24 23:47:16.056 ............-> Table "TMAIN" as "M1A" Full Scan
2024-12-24 23:47:16.062 Sub-query(line, column)
2024-12-24 23:47:16.069 ....-> Singularity Check
2024-12-24 23:47:16.074 ........-> Aggregate
2024-12-24 23:47:16.080 ............-> Table "TMAIN" as "M1B" Access By ID
2024-12-24 23:47:16.086 ................-> Index "TMAIN_X" Full Scan
2024-12-24 23:47:16.092 Sub-query(line, column)
2024-12-24 23:47:16.097 ....-> Singularity Check
2024-12-24 23:47:16.103 ........-> Aggregate
2024-12-24 23:47:16.109 ............-> Table "TDETL" as "D1B" Access By ID
2024-12-24 23:47:16.115 ................-> Index "TDETL_FK" Full Scan
2024-12-24 23:47:16.120 Sub-query(line, column)
2024-12-24 23:47:16.125 ....-> Singularity Check
2024-12-24 23:47:16.131 ........-> Aggregate
2024-12-24 23:47:16.136 ............-> Table "TDETL" as "D1C" Full Scan
2024-12-24 23:47:16.142 Select Expression(line, column)
2024-12-24 23:47:16.149 ....-> Aggregate
2024-12-24 23:47:16.154 ........-> Nested Loop Join (inner)
2024-12-24 23:47:16.159 ............-> Filter
2024-12-24 23:47:16.165 ................-> Table "TMAIN" as "M2" Access By ID
2024-12-24 23:47:16.174 ....................-> Index "TMAIN_PK" Full Scan
2024-12-24 23:47:16.181 ........................-> Bitmap
2024-12-24 23:47:16.188 ............................-> Index "TMAIN_X" Range Scan (lower bound: 1/1)
2024-12-24 23:47:16.193 ............-> Filter
2024-12-24 23:47:16.199 ................-> Table "TDETL" as "D" Access By ID
2024-12-24 23:47:16.203 ....................-> Bitmap
2024-12-24 23:47:16.209 ........................-> Index "TDETL_PK" Unique Scan
2024-12-24 23:47:16.214 Sub-query
2024-12-24 23:47:16.219 ....-> Filter
2024-12-24 23:47:16.224 ........-> Table "TDETL" as "D" Access By ID
2024-12-24 23:47:16.231 ............-> Bitmap
2024-12-24 23:47:16.238 ................-> Index "TDETL_FK" Range Scan (full match)
2024-12-24 23:47:16.245 Select Expression(line, column)
2024-12-24 23:47:16.250 ....-> Filter
2024-12-24 23:47:16.256 ........-> Table "TMAIN" as "M3" Access By ID
2024-12-24 23:47:16.261 ............-> Bitmap
2024-12-24 23:47:16.266 ................-> Index "TMAIN_X" Range Scan (lower bound: 1/1)
2024-12-24 23:47:16.272 ......0 ms
2024-12-24 23:47:16.277 """
2024-12-24 23:47:16.283
2024-12-24 23:47:16.289 act.expected_stdout = expected_stdout
2024-12-24 23:47:16.295 act.stdout = capsys.readouterr().out
2024-12-24 23:47:16.301 > assert act.clean_stdout == act.clean_expected_stdout
2024-12-24 23:47:16.309 E assert
2024-12-24 23:47:16.315 E Sub-query (invariant)
2024-12-24 23:47:16.321 E ....-> Filter
2024-12-24 23:47:16.326 E ........-> Aggregate
2024-12-24 23:47:16.333 E ............-> Table "TDETL" as "K DY" Access By ID
2024-12-24 23:47:16.339 E ................-> Index "TDETL_FK" Full Scan
2024-12-24 23:47:16.345 E Cursor "K"(line, column)
2024-12-24 23:47:16.350 E ....-> Filter (preliminary)
2024-12-24 23:47:16.357 E ........-> Nested Loop Join (inner)
2024-12-24 23:47:16.364 E ............-> Table "TMAIN" as "K M4" Full Scan
2024-12-24 23:47:16.370 E ............-> Filter
2024-12-24 23:47:16.376 E ................-> Table "TDETL" as "K D4 DX" Access By ID
2024-12-24 23:47:16.384 E ....................-> Bitmap And
2024-12-24 23:47:16.393 E ........................-> Bitmap And
2024-12-24 23:47:16.399 E ............................-> Bitmap
2024-12-24 23:47:16.406 E ................................-> Index "TDETL_Z" Range Scan (lower bound: 1/1)
2024-12-24 23:47:16.411 E ............................-> Bitmap
2024-12-24 23:47:16.418 E ................................-> Index "TDETL_Y" Range Scan (upper bound: 1/1)
2024-12-24 23:47:16.425 E ........................-> Bitmap
2024-12-24 23:47:16.432 E ............................-> Index "TDETL_FK" Range Scan (full match)
2024-12-24 23:47:16.440 E Sub-query
2024-12-24 23:47:16.446 E ....-> Filter
2024-12-24 23:47:16.452 E ........-> Table "TMAIN" as "M0" Access By ID
2024-12-24 23:47:16.458 E ............-> Bitmap
2024-12-24 23:47:16.463 E ................-> Index "TMAIN_X" Range Scan (lower bound: 1/1)
2024-12-24 23:47:16.468 E Sub-query
2024-12-24 23:47:16.473 E ....-> Filter
2024-12-24 23:47:16.478 E ........-> Table "TMAIN" as "R M0A" Access By ID
2024-12-24 23:47:16.483 E - ............-> Bitmap
2024-12-24 23:47:16.491 E + ............-> Bitmap Or
2024-12-24 23:47:16.505 E + ................-> Bitmap
2024-12-24 23:47:16.511 E - ................-> Index "TMAIN_X" List Scan (full match)
2024-12-24 23:47:16.522 E + ....................-> Index "TMAIN_X" Range Scan (full match)
2024-12-24 23:47:16.532 E + ................-> Bitmap
2024-12-24 23:47:16.538 E + ....................-> Index "TMAIN_X" Range Scan (full match)
2024-12-24 23:47:16.543 E Select Expression(line, column)
2024-12-24 23:47:16.548 E ....-> Singularity Check
2024-12-24 23:47:16.558 E ........-> Aggregate
2024-12-24 23:47:16.567 E ............-> Filter
2024-12-24 23:47:16.573 E ................-> Recursion
2024-12-24 23:47:16.581 E ....................-> Filter
2024-12-24 23:47:16.588 E ........................-> Table "TDETL" as "R D0" Access By ID
2024-12-24 23:47:16.596 E ............................-> Bitmap
2024-12-24 23:47:16.605 E ................................-> Index "TDETL_FK" Range Scan (full match)
2024-12-24 23:47:16.612 E ....................-> Filter
2024-12-24 23:47:16.620 E ........................-> Table "TDETL" as "R DX" Access By ID
2024-12-24 23:47:16.627 E ............................-> Bitmap
2024-12-24 23:47:16.635 E ................................-> Index "TDETL_FK" Range Scan (full match)
2024-12-24 23:47:16.648 E Sub-query(line, column)
2024-12-24 23:47:16.657 E ....-> Singularity Check
2024-12-24 23:47:16.666 E ........-> Aggregate
2024-12-24 23:47:16.675 E ............-> Table "TMAIN" as "M1A" Full Scan
2024-12-24 23:47:16.683 E Sub-query(line, column)
2024-12-24 23:47:16.689 E ....-> Singularity Check
2024-12-24 23:47:16.694 E ........-> Aggregate
2024-12-24 23:47:16.699 E ............-> Table "TMAIN" as "M1B" Access By ID
2024-12-24 23:47:16.704 E ................-> Index "TMAIN_X" Full Scan
2024-12-24 23:47:16.709 E Sub-query(line, column)
2024-12-24 23:47:16.714 E ....-> Singularity Check
2024-12-24 23:47:16.719 E ........-> Aggregate
2024-12-24 23:47:16.725 E ............-> Table "TDETL" as "D1B" Access By ID
2024-12-24 23:47:16.734 E ................-> Index "TDETL_FK" Full Scan
2024-12-24 23:47:16.740 E Sub-query(line, column)
2024-12-24 23:47:16.747 E ....-> Singularity Check
2024-12-24 23:47:16.753 E ........-> Aggregate
2024-12-24 23:47:16.759 E ............-> Table "TDETL" as "D1C" Full Scan
2024-12-24 23:47:16.764 E Select Expression(line, column)
2024-12-24 23:47:16.769 E ....-> Aggregate
2024-12-24 23:47:16.774 E ........-> Nested Loop Join (inner)
2024-12-24 23:47:16.780 E ............-> Filter
2024-12-24 23:47:16.785 E ................-> Table "TMAIN" as "M2" Access By ID
2024-12-24 23:47:16.790 E ....................-> Index "TMAIN_PK" Full Scan
2024-12-24 23:47:16.796 E ........................-> Bitmap
2024-12-24 23:47:16.802 E ............................-> Index "TMAIN_X" Range Scan (lower bound: 1/1)
2024-12-24 23:47:16.809 E ............-> Filter
2024-12-24 23:47:16.818 E ................-> Table "TDETL" as "D" Access By ID
2024-12-24 23:47:16.823 E ....................-> Bitmap
2024-12-24 23:47:16.829 E ........................-> Index "TDETL_PK" Unique Scan
2024-12-24 23:47:16.835 E Sub-query
2024-12-24 23:47:16.843 E ....-> Filter
2024-12-24 23:47:16.850 E ........-> Table "TDETL" as "D" Access By ID
2024-12-24 23:47:16.855 E ............-> Bitmap
2024-12-24 23:47:16.863 E ................-> Index "TDETL_FK" Range Scan (full match)
2024-12-24 23:47:16.870 E Select Expression(line, column)
2024-12-24 23:47:16.876 E ....-> Filter
2024-12-24 23:47:16.881 E ........-> Table "TMAIN" as "M3" Access By ID
2024-12-24 23:47:16.887 E ............-> Bitmap
2024-12-24 23:47:16.892 E ................-> Index "TMAIN_X" Range Scan (lower bound: 1/1)
2024-12-24 23:47:16.897 E ......0 ms
2024-12-24 23:47:16.902
2024-12-24 23:47:16.907 tests/bugs/gh_7466_plans_tracking_test.py:280: AssertionError
2024-12-24 23:47:16.912 ---------------------------- Captured stdout setup -----------------------------
2024-12-24 23:47:16.917 Creating db: localhost:/var/tmp/qa_2024/test_11546/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
|