2 @message |
assert
1000
select count(*) from test1 q1_a
where
q1_a.id in (
select q1_b.pid from test2 q1_b
where
q1_b.id in (
select q1_c.pid from test3 q1_c
where q1_c.name like '%ABC%'
)
)
Both sub-queries can (and should) be unnested.
Select Expression
....-> Aggregate
........-> Filter
............-> Hash Join (semi)
- ................-> Table "TEST1" as "Q1_A" Full Scan
+ ................-> Table "PUBLIC"."TEST1" as "Q1_A" Full Scan
................-> Record Buffer (record length: NN)
....................-> Filter
........................-> Hash Join (semi)
- ............................-> Table "TEST2" as "Q1_B" Full Scan
+ ............................-> Table "PUBLIC"."TEST2" as "Q1_B" Full Scan
............................-> Record Buffer (record length: NN)
................................-> Filter
- ....................................-> Table "TEST3" as "Q1_C" Full Scan
+ ....................................-> Table "PUBLIC"."TEST3" as "Q1_C" Full Scan
10
2000
select count(*) from test1 q2_a
where
q2_a.id in (
select q2_b.pid from test2 q2_b
where
1=1 or q2_b.id in (
select q2_c.pid from test3 q2_c
where q2_c.name like '%ABC%'
)
)
Inner sub-query can NOT be unnested due to `OR` condition present, but the outer sub-query can
Sub-query
....-> Filter
........-> Filter
- ............-> Table "TEST3" as "Q2_C" Full Scan
+ ............-> Table "PUBLIC"."TEST3" as "Q2_C" Full Scan
Select Expression
....-> Aggregate
........-> Filter
............-> Hash Join (semi)
- ................-> Table "TEST1" as "Q2_A" Full Scan
+ ................-> Table "PUBLIC"."TEST1" as "Q2_A" Full Scan
................-> Record Buffer (record length: NN)
....................-> Filter
- ........................-> Table "TEST2" as "Q2_B" Full Scan
+ ........................-> Table "PUBLIC"."TEST2" as "Q2_B" Full Scan
10
3000
select count(*) from test1 q3_a
where
1=1 or q3_a.id in (
select q3_b.pid from test2 q3_b
where q3_b.id in (
select id from test3 q3_c
where q3_c.name like '%ABC%'
)
)
Outer sub-query can NOT be unnested due to `OR` condition present, so the inner sub-query should NOT be unnested too
Sub-query
....-> Filter
........-> Filter
- ............-> Table "TEST3" as "Q3_C" Full Scan
+ ............-> Table "PUBLIC"."TEST3" as "Q3_C" Full Scan
Sub-query
....-> Filter
........-> Filter
- ............-> Table "TEST2" as "Q3_B" Full Scan
+ ............-> Table "PUBLIC"."TEST2" as "Q3_B" Full Scan
Select Expression
....-> Aggregate
........-> Filter
- ............-> Table "TEST1" as "Q3_A" Full Scan
+ ............-> Table "PUBLIC"."TEST1" as "Q3_A" Full Scan
10
4000
select count(*) from test1 q4_a
where
1=1 or q4_a.id in (
select id from test2 q4_b
where
1=1 or q4_b.id in (
select id from test3 q4_c
where q4_c.name like '%ABC%'
)
)
Both sub-queries can NOT be unnested due to OR conditions present
Sub-query
....-> Filter
........-> Filter
- ............-> Table "TEST3" as "Q4_C" Full Scan
+ ............-> Table "PUBLIC"."TEST3" as "Q4_C" Full Scan
Sub-query
....-> Filter
........-> Filter
- ............-> Table "TEST2" as "Q4_B" Full Scan
+ ............-> Table "PUBLIC"."TEST2" as "Q4_B" Full Scan
Select Expression
....-> Aggregate
........-> Filter
- ............-> Table "TEST1" as "Q4_A" Full Scan
+ ............-> Table "PUBLIC"."TEST1" as "Q4_A" Full Scan
10
LOG DETAILS:
2025-06-28 05:17:07.111
2025-06-28 05:17:07.122 act = <firebird.qa.plugin.Action object at [hex]>
2025-06-28 05:17:07.134 capsys = <_pytest.capture.CaptureFixture object at [hex]>
2025-06-28 05:17:07.145
2025-06-28 05:17:07.157 @pytest.mark.version('>=5.0.2')
2025-06-28 05:17:07.169 def test_1(act: Action, capsys):
2025-06-28 05:17:07.179
2025-06-28 05:17:07.189 srv_cfg = driver_config.register_server(name = f'srv_cfg_8265', config = '')
2025-06-28 05:17:07.196 db_cfg_name = f'db_cfg_8265'
2025-06-28 05:17:07.203 db_cfg_object = driver_config.register_database(name = db_cfg_name)
2025-06-28 05:17:07.210 db_cfg_object.server.value = srv_cfg.name
2025-06-28 05:17:07.218 db_cfg_object.database.value = str(act.db.db_path)
2025-06-28 05:17:07.231 if act.is_version('<6'):
2025-06-28 05:17:07.240 db_cfg_object.config.value = f"""
2025-06-28 05:17:07.245 SubQueryConversion = true
2025-06-28 05:17:07.256 """
2025-06-28 05:17:07.264
2025-06-28 05:17:07.270 with connect(db_cfg_name, user = act.db.user, password = act.db.password) as con:
2025-06-28 05:17:07.275 cur = con.cursor()
2025-06-28 05:17:07.281 for q_idx, q_tuple in query_map.items():
2025-06-28 05:17:07.288 test_sql, qry_comment = q_tuple[:2]
2025-06-28 05:17:07.294 ps,rs = None, None
2025-06-28 05:17:07.299 try:
2025-06-28 05:17:07.304 ps = cur.prepare(test_sql)
2025-06-28 05:17:07.309 print(q_idx)
2025-06-28 05:17:07.313 print(test_sql)
2025-06-28 05:17:07.319 print(qry_comment)
2025-06-28 05:17:07.324
2025-06-28 05:17:07.330 # Print explained plan with padding eash line by dots in order to see indentations:
2025-06-28 05:17:07.338 print( '\n'.join([replace_leading(s) for s in ps.detailed_plan.split('\n')]) )
2025-06-28 05:17:07.344 rs = cur.execute(ps)
2025-06-28 05:17:07.351 # Print data:
2025-06-28 05:17:07.356 for r in rs:
2025-06-28 05:17:07.361 print(r[0])
2025-06-28 05:17:07.366 except DatabaseError as e:
2025-06-28 05:17:07.371 print(e.__str__())
2025-06-28 05:17:07.376 print(e.gds_codes)
2025-06-28 05:17:07.380 finally:
2025-06-28 05:17:07.385 # explained by hvlad, 26.10.24 17:42
2025-06-28 05:17:07.390 if rs:
2025-06-28 05:17:07.395 rs.close()
2025-06-28 05:17:07.401 if ps:
2025-06-28 05:17:07.406 ps.free()
2025-06-28 05:17:07.412
2025-06-28 05:17:07.418 act.expected_stdout = f"""
2025-06-28 05:17:07.424 1000
2025-06-28 05:17:07.434 {query_map[1000][0]}
2025-06-28 05:17:07.442 {query_map[1000][1]}
2025-06-28 05:17:07.449 Select Expression
2025-06-28 05:17:07.457 ....-> Aggregate
2025-06-28 05:17:07.463 ........-> Filter
2025-06-28 05:17:07.468 ............-> Hash Join (semi) (keys: 1, total key length: 4)
2025-06-28 05:17:07.473 ................-> Table "TEST1" as "Q1_A" Full Scan
2025-06-28 05:17:07.478 ................-> Record Buffer (record length: 82)
2025-06-28 05:17:07.482 ....................-> Filter
2025-06-28 05:17:07.487 ........................-> Hash Join (semi) (keys: 1, total key length: 4)
2025-06-28 05:17:07.492 ............................-> Table "TEST2" as "Q1_B" Full Scan
2025-06-28 05:17:07.498 ............................-> Record Buffer (record length: 57)
2025-06-28 05:17:07.504 ................................-> Filter
2025-06-28 05:17:07.509 ....................................-> Table "TEST3" as "Q1_C" Full Scan
2025-06-28 05:17:07.514 10
2025-06-28 05:17:07.518
2025-06-28 05:17:07.523 2000
2025-06-28 05:17:07.527 {query_map[2000][0]}
2025-06-28 05:17:07.532 {query_map[2000][1]}
2025-06-28 05:17:07.536 Sub-query
2025-06-28 05:17:07.541 ....-> Filter
2025-06-28 05:17:07.546 ........-> Filter
2025-06-28 05:17:07.551 ............-> Table "TEST3" as "Q2_C" Full Scan
2025-06-28 05:17:07.555 Select Expression
2025-06-28 05:17:07.560 ....-> Aggregate
2025-06-28 05:17:07.565 ........-> Filter
2025-06-28 05:17:07.571 ............-> Hash Join (semi) (keys: 1, total key length: 4)
2025-06-28 05:17:07.577 ................-> Table "TEST1" as "Q2_A" Full Scan
2025-06-28 05:17:07.583 ................-> Record Buffer (record length: 33)
2025-06-28 05:17:07.588 ....................-> Filter
2025-06-28 05:17:07.594 ........................-> Table "TEST2" as "Q2_B" Full Scan
2025-06-28 05:17:07.604 10
2025-06-28 05:17:07.612
2025-06-28 05:17:07.620 3000
2025-06-28 05:17:07.626 {query_map[3000][0]}
2025-06-28 05:17:07.638 {query_map[3000][1]}
2025-06-28 05:17:07.648 Sub-query
2025-06-28 05:17:07.655 ....-> Filter
2025-06-28 05:17:07.662 ........-> Filter
2025-06-28 05:17:07.668 ............-> Table "TEST3" as "Q3_C" Full Scan
2025-06-28 05:17:07.673 Sub-query
2025-06-28 05:17:07.682 ....-> Filter
2025-06-28 05:17:07.688 ........-> Filter
2025-06-28 05:17:07.695 ............-> Table "TEST2" as "Q3_B" Full Scan
2025-06-28 05:17:07.702 Select Expression
2025-06-28 05:17:07.708 ....-> Aggregate
2025-06-28 05:17:07.714 ........-> Filter
2025-06-28 05:17:07.724 ............-> Table "TEST1" as "Q3_A" Full Scan
2025-06-28 05:17:07.730 10
2025-06-28 05:17:07.735
2025-06-28 05:17:07.741 4000
2025-06-28 05:17:07.747 {query_map[4000][0]}
2025-06-28 05:17:07.754 {query_map[4000][1]}
2025-06-28 05:17:07.764 Sub-query
2025-06-28 05:17:07.773 ....-> Filter
2025-06-28 05:17:07.779 ........-> Filter
2025-06-28 05:17:07.785 ............-> Table "TEST3" as "Q4_C" Full Scan
2025-06-28 05:17:07.790 Sub-query
2025-06-28 05:17:07.794 ....-> Filter
2025-06-28 05:17:07.799 ........-> Filter
2025-06-28 05:17:07.804 ............-> Table "TEST2" as "Q4_B" Full Scan
2025-06-28 05:17:07.808 Select Expression
2025-06-28 05:17:07.814 ....-> Aggregate
2025-06-28 05:17:07.819 ........-> Filter
2025-06-28 05:17:07.825 ............-> Table "TEST1" as "Q4_A" Full Scan
2025-06-28 05:17:07.830 10
2025-06-28 05:17:07.835 """
2025-06-28 05:17:07.840 act.stdout = capsys.readouterr().out
2025-06-28 05:17:07.853 > assert act.clean_stdout == act.clean_expected_stdout
2025-06-28 05:17:07.862 E assert
2025-06-28 05:17:07.869 E 1000
2025-06-28 05:17:07.875 E select count(*) from test1 q1_a
2025-06-28 05:17:07.883 E where
2025-06-28 05:17:07.891 E q1_a.id in (
2025-06-28 05:17:07.896 E select q1_b.pid from test2 q1_b
2025-06-28 05:17:07.903 E where
2025-06-28 05:17:07.908 E q1_b.id in (
2025-06-28 05:17:07.913 E select q1_c.pid from test3 q1_c
2025-06-28 05:17:07.918 E where q1_c.name like '%ABC%'
2025-06-28 05:17:07.923 E )
2025-06-28 05:17:07.928 E )
2025-06-28 05:17:07.934 E Both sub-queries can (and should) be unnested.
2025-06-28 05:17:07.940 E Select Expression
2025-06-28 05:17:07.946 E ....-> Aggregate
2025-06-28 05:17:07.952 E ........-> Filter
2025-06-28 05:17:07.957 E ............-> Hash Join (semi)
2025-06-28 05:17:07.961 E - ................-> Table "TEST1" as "Q1_A" Full Scan
2025-06-28 05:17:07.969 E + ................-> Table "PUBLIC"."TEST1" as "Q1_A" Full Scan
2025-06-28 05:17:07.983 E ................-> Record Buffer (record length: NN)
2025-06-28 05:17:07.991 E ....................-> Filter
2025-06-28 05:17:08.001 E ........................-> Hash Join (semi)
2025-06-28 05:17:08.010 E - ............................-> Table "TEST2" as "Q1_B" Full Scan
2025-06-28 05:17:08.017 E + ............................-> Table "PUBLIC"."TEST2" as "Q1_B" Full Scan
2025-06-28 05:17:08.031 E ............................-> Record Buffer (record length: NN)
2025-06-28 05:17:08.043 E ................................-> Filter
2025-06-28 05:17:08.051 E - ....................................-> Table "TEST3" as "Q1_C" Full Scan
2025-06-28 05:17:08.059 E + ....................................-> Table "PUBLIC"."TEST3" as "Q1_C" Full Scan
2025-06-28 05:17:08.075 E 10
2025-06-28 05:17:08.084 E 2000
2025-06-28 05:17:08.095 E select count(*) from test1 q2_a
2025-06-28 05:17:08.105 E where
2025-06-28 05:17:08.113 E q2_a.id in (
2025-06-28 05:17:08.119 E select q2_b.pid from test2 q2_b
2025-06-28 05:17:08.126 E where
2025-06-28 05:17:08.132 E 1=1 or q2_b.id in (
2025-06-28 05:17:08.138 E select q2_c.pid from test3 q2_c
2025-06-28 05:17:08.143 E where q2_c.name like '%ABC%'
2025-06-28 05:17:08.149 E )
2025-06-28 05:17:08.155 E )
2025-06-28 05:17:08.166 E Inner sub-query can NOT be unnested due to `OR` condition present, but the outer sub-query can
2025-06-28 05:17:08.178 E Sub-query
2025-06-28 05:17:08.189 E ....-> Filter
2025-06-28 05:17:08.197 E ........-> Filter
2025-06-28 05:17:08.208 E - ............-> Table "TEST3" as "Q2_C" Full Scan
2025-06-28 05:17:08.216 E + ............-> Table "PUBLIC"."TEST3" as "Q2_C" Full Scan
2025-06-28 05:17:08.231 E Select Expression
2025-06-28 05:17:08.238 E ....-> Aggregate
2025-06-28 05:17:08.250 E ........-> Filter
2025-06-28 05:17:08.258 E ............-> Hash Join (semi)
2025-06-28 05:17:08.266 E - ................-> Table "TEST1" as "Q2_A" Full Scan
2025-06-28 05:17:08.275 E + ................-> Table "PUBLIC"."TEST1" as "Q2_A" Full Scan
2025-06-28 05:17:08.290 E ................-> Record Buffer (record length: NN)
2025-06-28 05:17:08.296 E ....................-> Filter
2025-06-28 05:17:08.302 E - ........................-> Table "TEST2" as "Q2_B" Full Scan
2025-06-28 05:17:08.309 E + ........................-> Table "PUBLIC"."TEST2" as "Q2_B" Full Scan
2025-06-28 05:17:08.320 E 10
2025-06-28 05:17:08.326 E 3000
2025-06-28 05:17:08.334 E select count(*) from test1 q3_a
2025-06-28 05:17:08.341 E where
2025-06-28 05:17:08.350 E 1=1 or q3_a.id in (
2025-06-28 05:17:08.358 E select q3_b.pid from test2 q3_b
2025-06-28 05:17:08.366 E where q3_b.id in (
2025-06-28 05:17:08.378 E select id from test3 q3_c
2025-06-28 05:17:08.386 E where q3_c.name like '%ABC%'
2025-06-28 05:17:08.392 E )
2025-06-28 05:17:08.399 E )
2025-06-28 05:17:08.407 E Outer sub-query can NOT be unnested due to `OR` condition present, so the inner sub-query should NOT be unnested too
2025-06-28 05:17:08.414 E Sub-query
2025-06-28 05:17:08.422 E ....-> Filter
2025-06-28 05:17:08.430 E ........-> Filter
2025-06-28 05:17:08.436 E - ............-> Table "TEST3" as "Q3_C" Full Scan
2025-06-28 05:17:08.443 E + ............-> Table "PUBLIC"."TEST3" as "Q3_C" Full Scan
2025-06-28 05:17:08.455 E Sub-query
2025-06-28 05:17:08.462 E ....-> Filter
2025-06-28 05:17:08.471 E ........-> Filter
2025-06-28 05:17:08.478 E - ............-> Table "TEST2" as "Q3_B" Full Scan
2025-06-28 05:17:08.489 E + ............-> Table "PUBLIC"."TEST2" as "Q3_B" Full Scan
2025-06-28 05:17:08.502 E Select Expression
2025-06-28 05:17:08.507 E ....-> Aggregate
2025-06-28 05:17:08.513 E ........-> Filter
2025-06-28 05:17:08.518 E - ............-> Table "TEST1" as "Q3_A" Full Scan
2025-06-28 05:17:08.526 E + ............-> Table "PUBLIC"."TEST1" as "Q3_A" Full Scan
2025-06-28 05:17:08.541 E 10
2025-06-28 05:17:08.548 E 4000
2025-06-28 05:17:08.558 E select count(*) from test1 q4_a
2025-06-28 05:17:08.567 E where
2025-06-28 05:17:08.575 E 1=1 or q4_a.id in (
2025-06-28 05:17:08.582 E select id from test2 q4_b
2025-06-28 05:17:08.587 E where
2025-06-28 05:17:08.593 E 1=1 or q4_b.id in (
2025-06-28 05:17:08.599 E select id from test3 q4_c
2025-06-28 05:17:08.605 E where q4_c.name like '%ABC%'
2025-06-28 05:17:08.614 E )
2025-06-28 05:17:08.620 E )
2025-06-28 05:17:08.626 E Both sub-queries can NOT be unnested due to OR conditions present
2025-06-28 05:17:08.632 E Sub-query
2025-06-28 05:17:08.637 E ....-> Filter
2025-06-28 05:17:08.642 E ........-> Filter
2025-06-28 05:17:08.649 E - ............-> Table "TEST3" as "Q4_C" Full Scan
2025-06-28 05:17:08.656 E + ............-> Table "PUBLIC"."TEST3" as "Q4_C" Full Scan
2025-06-28 05:17:08.671 E Sub-query
2025-06-28 05:17:08.681 E ....-> Filter
2025-06-28 05:17:08.689 E ........-> Filter
2025-06-28 05:17:08.695 E - ............-> Table "TEST2" as "Q4_B" Full Scan
2025-06-28 05:17:08.701 E + ............-> Table "PUBLIC"."TEST2" as "Q4_B" Full Scan
2025-06-28 05:17:08.712 E Select Expression
2025-06-28 05:17:08.719 E ....-> Aggregate
2025-06-28 05:17:08.726 E ........-> Filter
2025-06-28 05:17:08.732 E - ............-> Table "TEST1" as "Q4_A" Full Scan
2025-06-28 05:17:08.738 E + ............-> Table "PUBLIC"."TEST1" as "Q4_A" Full Scan
2025-06-28 05:17:08.749 E 10
2025-06-28 05:17:08.755
2025-06-28 05:17:08.761 tests/bugs/gh_8265_test.py:272: AssertionError
2025-06-28 05:17:08.773 ---------------------------- Captured stdout setup -----------------------------
2025-06-28 05:17:08.784 Creating db: localhost:/var/tmp/qa_2024/test_11717/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.version('>=5.0.2')
def test_1(act: Action, capsys):
srv_cfg = driver_config.register_server(name = f'srv_cfg_8265', config = '')
db_cfg_name = f'db_cfg_8265'
db_cfg_object = driver_config.register_database(name = db_cfg_name)
db_cfg_object.server.value = srv_cfg.name
db_cfg_object.database.value = str(act.db.db_path)
if act.is_version('<6'):
db_cfg_object.config.value = f"""
SubQueryConversion = true
"""
with connect(db_cfg_name, user = act.db.user, password = act.db.password) as con:
cur = con.cursor()
for q_idx, q_tuple in query_map.items():
test_sql, qry_comment = q_tuple[:2]
ps,rs = None, None
try:
ps = cur.prepare(test_sql)
print(q_idx)
print(test_sql)
print(qry_comment)
# Print explained plan with padding eash line by dots in order to see indentations:
print( '\n'.join([replace_leading(s) for s in ps.detailed_plan.split('\n')]) )
rs = cur.execute(ps)
# Print data:
for r in rs:
print(r[0])
except DatabaseError as e:
print(e.__str__())
print(e.gds_codes)
finally:
# explained by hvlad, 26.10.24 17:42
if rs:
rs.close()
if ps:
ps.free()
act.expected_stdout = f"""
1000
{query_map[1000][0]}
{query_map[1000][1]}
Select Expression
....-> Aggregate
........-> Filter
............-> Hash Join (semi) (keys: 1, total key length: 4)
................-> Table "TEST1" as "Q1_A" Full Scan
................-> Record Buffer (record length: 82)
....................-> Filter
........................-> Hash Join (semi) (keys: 1, total key length: 4)
............................-> Table "TEST2" as "Q1_B" Full Scan
............................-> Record Buffer (record length: 57)
................................-> Filter
....................................-> Table "TEST3" as "Q1_C" Full Scan
10
2000
{query_map[2000][0]}
{query_map[2000][1]}
Sub-query
....-> Filter
........-> Filter
............-> Table "TEST3" as "Q2_C" Full Scan
Select Expression
....-> Aggregate
........-> Filter
............-> Hash Join (semi) (keys: 1, total key length: 4)
................-> Table "TEST1" as "Q2_A" Full Scan
................-> Record Buffer (record length: 33)
....................-> Filter
........................-> Table "TEST2" as "Q2_B" Full Scan
10
3000
{query_map[3000][0]}
{query_map[3000][1]}
Sub-query
....-> Filter
........-> Filter
............-> Table "TEST3" as "Q3_C" Full Scan
Sub-query
....-> Filter
........-> Filter
............-> Table "TEST2" as "Q3_B" Full Scan
Select Expression
....-> Aggregate
........-> Filter
............-> Table "TEST1" as "Q3_A" Full Scan
10
4000
{query_map[4000][0]}
{query_map[4000][1]}
Sub-query
....-> Filter
........-> Filter
............-> Table "TEST3" as "Q4_C" Full Scan
Sub-query
....-> Filter
........-> Filter
............-> Table "TEST2" as "Q4_B" Full Scan
Select Expression
....-> Aggregate
........-> Filter
............-> Table "TEST1" as "Q4_A" Full Scan
10
"""
act.stdout = capsys.readouterr().out
> assert act.clean_stdout == act.clean_expected_stdout
E assert
E 1000
E select count(*) from test1 q1_a
E where
E q1_a.id in (
E select q1_b.pid from test2 q1_b
E where
E q1_b.id in (
E select q1_c.pid from test3 q1_c
E where q1_c.name like '%ABC%'
E )
E )
E Both sub-queries can (and should) be unnested.
E Select Expression
E ....-> Aggregate
E ........-> Filter
E ............-> Hash Join (semi)
E - ................-> Table "TEST1" as "Q1_A" Full Scan
E + ................-> Table "PUBLIC"."TEST1" as "Q1_A" Full Scan
E ................-> Record Buffer (record length: NN)
E ....................-> Filter
E ........................-> Hash Join (semi)
E - ............................-> Table "TEST2" as "Q1_B" Full Scan
E + ............................-> Table "PUBLIC"."TEST2" as "Q1_B" Full Scan
E ............................-> Record Buffer (record length: NN)
E ................................-> Filter
E - ....................................-> Table "TEST3" as "Q1_C" Full Scan
E + ....................................-> Table "PUBLIC"."TEST3" as "Q1_C" Full Scan
E 10
E 2000
E select count(*) from test1 q2_a
E where
E q2_a.id in (
E select q2_b.pid from test2 q2_b
E where
E 1=1 or q2_b.id in (
E select q2_c.pid from test3 q2_c
E where q2_c.name like '%ABC%'
E )
E )
E Inner sub-query can NOT be unnested due to `OR` condition present, but the outer sub-query can
E Sub-query
E ....-> Filter
E ........-> Filter
E - ............-> Table "TEST3" as "Q2_C" Full Scan
E + ............-> Table "PUBLIC"."TEST3" as "Q2_C" Full Scan
E Select Expression
E ....-> Aggregate
E ........-> Filter
E ............-> Hash Join (semi)
E - ................-> Table "TEST1" as "Q2_A" Full Scan
E + ................-> Table "PUBLIC"."TEST1" as "Q2_A" Full Scan
E ................-> Record Buffer (record length: NN)
E ....................-> Filter
E - ........................-> Table "TEST2" as "Q2_B" Full Scan
E + ........................-> Table "PUBLIC"."TEST2" as "Q2_B" Full Scan
E 10
E 3000
E select count(*) from test1 q3_a
E where
E 1=1 or q3_a.id in (
E select q3_b.pid from test2 q3_b
E where q3_b.id in (
E select id from test3 q3_c
E where q3_c.name like '%ABC%'
E )
E )
E Outer sub-query can NOT be unnested due to `OR` condition present, so the inner sub-query should NOT be unnested too
E Sub-query
E ....-> Filter
E ........-> Filter
E - ............-> Table "TEST3" as "Q3_C" Full Scan
E + ............-> Table "PUBLIC"."TEST3" as "Q3_C" Full Scan
E Sub-query
E ....-> Filter
E ........-> Filter
E - ............-> Table "TEST2" as "Q3_B" Full Scan
E + ............-> Table "PUBLIC"."TEST2" as "Q3_B" Full Scan
E Select Expression
E ....-> Aggregate
E ........-> Filter
E - ............-> Table "TEST1" as "Q3_A" Full Scan
E + ............-> Table "PUBLIC"."TEST1" as "Q3_A" Full Scan
E 10
E 4000
E select count(*) from test1 q4_a
E where
E 1=1 or q4_a.id in (
E select id from test2 q4_b
E where
E 1=1 or q4_b.id in (
E select id from test3 q4_c
E where q4_c.name like '%ABC%'
E )
E )
E Both sub-queries can NOT be unnested due to OR conditions present
E Sub-query
E ....-> Filter
E ........-> Filter
E - ............-> Table "TEST3" as "Q4_C" Full Scan
E + ............-> Table "PUBLIC"."TEST3" as "Q4_C" Full Scan
E Sub-query
E ....-> Filter
E ........-> Filter
E - ............-> Table "TEST2" as "Q4_B" Full Scan
E + ............-> Table "PUBLIC"."TEST2" as "Q4_B" Full Scan
E Select Expression
E ....-> Aggregate
E ........-> Filter
E - ............-> Table "TEST1" as "Q4_A" Full Scan
E + ............-> Table "PUBLIC"."TEST1" as "Q4_A" Full Scan
E 10
tests/bugs/gh_8265_test.py:272: AssertionError
|