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-27 05:18:16.363
2025-06-27 05:18:16.369 act = <firebird.qa.plugin.Action object at [hex]>
2025-06-27 05:18:16.374 capsys = <_pytest.capture.CaptureFixture object at [hex]>
2025-06-27 05:18:16.379
2025-06-27 05:18:16.384 @pytest.mark.version('>=5.0.2')
2025-06-27 05:18:16.390 def test_1(act: Action, capsys):
2025-06-27 05:18:16.395
2025-06-27 05:18:16.401 srv_cfg = driver_config.register_server(name = f'srv_cfg_8265', config = '')
2025-06-27 05:18:16.406 db_cfg_name = f'db_cfg_8265'
2025-06-27 05:18:16.413 db_cfg_object = driver_config.register_database(name = db_cfg_name)
2025-06-27 05:18:16.420 db_cfg_object.server.value = srv_cfg.name
2025-06-27 05:18:16.427 db_cfg_object.database.value = str(act.db.db_path)
2025-06-27 05:18:16.435 if act.is_version('<6'):
2025-06-27 05:18:16.446 db_cfg_object.config.value = f"""
2025-06-27 05:18:16.454 SubQueryConversion = true
2025-06-27 05:18:16.460 """
2025-06-27 05:18:16.465
2025-06-27 05:18:16.471 with connect(db_cfg_name, user = act.db.user, password = act.db.password) as con:
2025-06-27 05:18:16.476 cur = con.cursor()
2025-06-27 05:18:16.481 for q_idx, q_tuple in query_map.items():
2025-06-27 05:18:16.486 test_sql, qry_comment = q_tuple[:2]
2025-06-27 05:18:16.491 ps,rs = None, None
2025-06-27 05:18:16.496 try:
2025-06-27 05:18:16.501 ps = cur.prepare(test_sql)
2025-06-27 05:18:16.507 print(q_idx)
2025-06-27 05:18:16.513 print(test_sql)
2025-06-27 05:18:16.519 print(qry_comment)
2025-06-27 05:18:16.530
2025-06-27 05:18:16.541 # Print explained plan with padding eash line by dots in order to see indentations:
2025-06-27 05:18:16.550 print( '\n'.join([replace_leading(s) for s in ps.detailed_plan.split('\n')]) )
2025-06-27 05:18:16.558 rs = cur.execute(ps)
2025-06-27 05:18:16.572 # Print data:
2025-06-27 05:18:16.582 for r in rs:
2025-06-27 05:18:16.591 print(r[0])
2025-06-27 05:18:16.599 except DatabaseError as e:
2025-06-27 05:18:16.608 print(e.__str__())
2025-06-27 05:18:16.619 print(e.gds_codes)
2025-06-27 05:18:16.628 finally:
2025-06-27 05:18:16.636 # explained by hvlad, 26.10.24 17:42
2025-06-27 05:18:16.643 if rs:
2025-06-27 05:18:16.650 rs.close()
2025-06-27 05:18:16.657 if ps:
2025-06-27 05:18:16.663 ps.free()
2025-06-27 05:18:16.668
2025-06-27 05:18:16.674 act.expected_stdout = f"""
2025-06-27 05:18:16.684 1000
2025-06-27 05:18:16.693 {query_map[1000][0]}
2025-06-27 05:18:16.702 {query_map[1000][1]}
2025-06-27 05:18:16.711 Select Expression
2025-06-27 05:18:16.722 ....-> Aggregate
2025-06-27 05:18:16.732 ........-> Filter
2025-06-27 05:18:16.740 ............-> Hash Join (semi) (keys: 1, total key length: 4)
2025-06-27 05:18:16.751 ................-> Table "TEST1" as "Q1_A" Full Scan
2025-06-27 05:18:16.762 ................-> Record Buffer (record length: 82)
2025-06-27 05:18:16.773 ....................-> Filter
2025-06-27 05:18:16.783 ........................-> Hash Join (semi) (keys: 1, total key length: 4)
2025-06-27 05:18:16.791 ............................-> Table "TEST2" as "Q1_B" Full Scan
2025-06-27 05:18:16.797 ............................-> Record Buffer (record length: 57)
2025-06-27 05:18:16.803 ................................-> Filter
2025-06-27 05:18:16.808 ....................................-> Table "TEST3" as "Q1_C" Full Scan
2025-06-27 05:18:16.812 10
2025-06-27 05:18:16.817
2025-06-27 05:18:16.821 2000
2025-06-27 05:18:16.826 {query_map[2000][0]}
2025-06-27 05:18:16.835 {query_map[2000][1]}
2025-06-27 05:18:16.845 Sub-query
2025-06-27 05:18:16.853 ....-> Filter
2025-06-27 05:18:16.860 ........-> Filter
2025-06-27 05:18:16.867 ............-> Table "TEST3" as "Q2_C" Full Scan
2025-06-27 05:18:16.873 Select Expression
2025-06-27 05:18:16.880 ....-> Aggregate
2025-06-27 05:18:16.887 ........-> Filter
2025-06-27 05:18:16.895 ............-> Hash Join (semi) (keys: 1, total key length: 4)
2025-06-27 05:18:16.905 ................-> Table "TEST1" as "Q2_A" Full Scan
2025-06-27 05:18:16.912 ................-> Record Buffer (record length: 33)
2025-06-27 05:18:16.918 ....................-> Filter
2025-06-27 05:18:16.923 ........................-> Table "TEST2" as "Q2_B" Full Scan
2025-06-27 05:18:16.927 10
2025-06-27 05:18:16.932
2025-06-27 05:18:16.936 3000
2025-06-27 05:18:16.940 {query_map[3000][0]}
2025-06-27 05:18:16.944 {query_map[3000][1]}
2025-06-27 05:18:16.949 Sub-query
2025-06-27 05:18:16.954 ....-> Filter
2025-06-27 05:18:16.959 ........-> Filter
2025-06-27 05:18:16.964 ............-> Table "TEST3" as "Q3_C" Full Scan
2025-06-27 05:18:16.970 Sub-query
2025-06-27 05:18:16.976 ....-> Filter
2025-06-27 05:18:16.985 ........-> Filter
2025-06-27 05:18:16.993 ............-> Table "TEST2" as "Q3_B" Full Scan
2025-06-27 05:18:17.001 Select Expression
2025-06-27 05:18:17.007 ....-> Aggregate
2025-06-27 05:18:17.020 ........-> Filter
2025-06-27 05:18:17.029 ............-> Table "TEST1" as "Q3_A" Full Scan
2025-06-27 05:18:17.040 10
2025-06-27 05:18:17.049
2025-06-27 05:18:17.058 4000
2025-06-27 05:18:17.065 {query_map[4000][0]}
2025-06-27 05:18:17.077 {query_map[4000][1]}
2025-06-27 05:18:17.088 Sub-query
2025-06-27 05:18:17.097 ....-> Filter
2025-06-27 05:18:17.105 ........-> Filter
2025-06-27 05:18:17.112 ............-> Table "TEST3" as "Q4_C" Full Scan
2025-06-27 05:18:17.119 Sub-query
2025-06-27 05:18:17.127 ....-> Filter
2025-06-27 05:18:17.138 ........-> Filter
2025-06-27 05:18:17.147 ............-> Table "TEST2" as "Q4_B" Full Scan
2025-06-27 05:18:17.156 Select Expression
2025-06-27 05:18:17.168 ....-> Aggregate
2025-06-27 05:18:17.176 ........-> Filter
2025-06-27 05:18:17.182 ............-> Table "TEST1" as "Q4_A" Full Scan
2025-06-27 05:18:17.193 10
2025-06-27 05:18:17.202 """
2025-06-27 05:18:17.210 act.stdout = capsys.readouterr().out
2025-06-27 05:18:17.217 > assert act.clean_stdout == act.clean_expected_stdout
2025-06-27 05:18:17.222 E assert
2025-06-27 05:18:17.228 E 1000
2025-06-27 05:18:17.234 E select count(*) from test1 q1_a
2025-06-27 05:18:17.244 E where
2025-06-27 05:18:17.253 E q1_a.id in (
2025-06-27 05:18:17.260 E select q1_b.pid from test2 q1_b
2025-06-27 05:18:17.266 E where
2025-06-27 05:18:17.276 E q1_b.id in (
2025-06-27 05:18:17.286 E select q1_c.pid from test3 q1_c
2025-06-27 05:18:17.293 E where q1_c.name like '%ABC%'
2025-06-27 05:18:17.301 E )
2025-06-27 05:18:17.312 E )
2025-06-27 05:18:17.320 E Both sub-queries can (and should) be unnested.
2025-06-27 05:18:17.327 E Select Expression
2025-06-27 05:18:17.335 E ....-> Aggregate
2025-06-27 05:18:17.346 E ........-> Filter
2025-06-27 05:18:17.359 E ............-> Hash Join (semi)
2025-06-27 05:18:17.369 E - ................-> Table "TEST1" as "Q1_A" Full Scan
2025-06-27 05:18:17.379 E + ................-> Table "PUBLIC"."TEST1" as "Q1_A" Full Scan
2025-06-27 05:18:17.392 E ................-> Record Buffer (record length: NN)
2025-06-27 05:18:17.400 E ....................-> Filter
2025-06-27 05:18:17.411 E ........................-> Hash Join (semi)
2025-06-27 05:18:17.423 E - ............................-> Table "TEST2" as "Q1_B" Full Scan
2025-06-27 05:18:17.431 E + ............................-> Table "PUBLIC"."TEST2" as "Q1_B" Full Scan
2025-06-27 05:18:17.443 E ............................-> Record Buffer (record length: NN)
2025-06-27 05:18:17.454 E ................................-> Filter
2025-06-27 05:18:17.464 E - ....................................-> Table "TEST3" as "Q1_C" Full Scan
2025-06-27 05:18:17.476 E + ....................................-> Table "PUBLIC"."TEST3" as "Q1_C" Full Scan
2025-06-27 05:18:17.498 E 10
2025-06-27 05:18:17.505 E 2000
2025-06-27 05:18:17.512 E select count(*) from test1 q2_a
2025-06-27 05:18:17.518 E where
2025-06-27 05:18:17.523 E q2_a.id in (
2025-06-27 05:18:17.529 E select q2_b.pid from test2 q2_b
2025-06-27 05:18:17.535 E where
2025-06-27 05:18:17.541 E 1=1 or q2_b.id in (
2025-06-27 05:18:17.550 E select q2_c.pid from test3 q2_c
2025-06-27 05:18:17.558 E where q2_c.name like '%ABC%'
2025-06-27 05:18:17.565 E )
2025-06-27 05:18:17.571 E )
2025-06-27 05:18:17.578 E Inner sub-query can NOT be unnested due to `OR` condition present, but the outer sub-query can
2025-06-27 05:18:17.588 E Sub-query
2025-06-27 05:18:17.599 E ....-> Filter
2025-06-27 05:18:17.611 E ........-> Filter
2025-06-27 05:18:17.621 E - ............-> Table "TEST3" as "Q2_C" Full Scan
2025-06-27 05:18:17.634 E + ............-> Table "PUBLIC"."TEST3" as "Q2_C" Full Scan
2025-06-27 05:18:17.657 E Select Expression
2025-06-27 05:18:17.666 E ....-> Aggregate
2025-06-27 05:18:17.673 E ........-> Filter
2025-06-27 05:18:17.680 E ............-> Hash Join (semi)
2025-06-27 05:18:17.686 E - ................-> Table "TEST1" as "Q2_A" Full Scan
2025-06-27 05:18:17.692 E + ................-> Table "PUBLIC"."TEST1" as "Q2_A" Full Scan
2025-06-27 05:18:17.703 E ................-> Record Buffer (record length: NN)
2025-06-27 05:18:17.709 E ....................-> Filter
2025-06-27 05:18:17.714 E - ........................-> Table "TEST2" as "Q2_B" Full Scan
2025-06-27 05:18:17.724 E + ........................-> Table "PUBLIC"."TEST2" as "Q2_B" Full Scan
2025-06-27 05:18:17.751 E 10
2025-06-27 05:18:17.763 E 3000
2025-06-27 05:18:17.777 E select count(*) from test1 q3_a
2025-06-27 05:18:17.788 E where
2025-06-27 05:18:17.801 E 1=1 or q3_a.id in (
2025-06-27 05:18:17.810 E select q3_b.pid from test2 q3_b
2025-06-27 05:18:17.821 E where q3_b.id in (
2025-06-27 05:18:17.831 E select id from test3 q3_c
2025-06-27 05:18:17.842 E where q3_c.name like '%ABC%'
2025-06-27 05:18:17.854 E )
2025-06-27 05:18:17.864 E )
2025-06-27 05:18:17.873 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-27 05:18:17.880 E Sub-query
2025-06-27 05:18:17.887 E ....-> Filter
2025-06-27 05:18:17.894 E ........-> Filter
2025-06-27 05:18:17.900 E - ............-> Table "TEST3" as "Q3_C" Full Scan
2025-06-27 05:18:17.906 E + ............-> Table "PUBLIC"."TEST3" as "Q3_C" Full Scan
2025-06-27 05:18:17.918 E Sub-query
2025-06-27 05:18:17.923 E ....-> Filter
2025-06-27 05:18:17.929 E ........-> Filter
2025-06-27 05:18:17.935 E - ............-> Table "TEST2" as "Q3_B" Full Scan
2025-06-27 05:18:17.942 E + ............-> Table "PUBLIC"."TEST2" as "Q3_B" Full Scan
2025-06-27 05:18:17.961 E Select Expression
2025-06-27 05:18:17.970 E ....-> Aggregate
2025-06-27 05:18:17.978 E ........-> Filter
2025-06-27 05:18:17.987 E - ............-> Table "TEST1" as "Q3_A" Full Scan
2025-06-27 05:18:17.998 E + ............-> Table "PUBLIC"."TEST1" as "Q3_A" Full Scan
2025-06-27 05:18:18.014 E 10
2025-06-27 05:18:18.021 E 4000
2025-06-27 05:18:18.027 E select count(*) from test1 q4_a
2025-06-27 05:18:18.038 E where
2025-06-27 05:18:18.045 E 1=1 or q4_a.id in (
2025-06-27 05:18:18.050 E select id from test2 q4_b
2025-06-27 05:18:18.056 E where
2025-06-27 05:18:18.063 E 1=1 or q4_b.id in (
2025-06-27 05:18:18.072 E select id from test3 q4_c
2025-06-27 05:18:18.079 E where q4_c.name like '%ABC%'
2025-06-27 05:18:18.085 E )
2025-06-27 05:18:18.090 E )
2025-06-27 05:18:18.097 E Both sub-queries can NOT be unnested due to OR conditions present
2025-06-27 05:18:18.102 E Sub-query
2025-06-27 05:18:18.108 E ....-> Filter
2025-06-27 05:18:18.114 E ........-> Filter
2025-06-27 05:18:18.121 E - ............-> Table "TEST3" as "Q4_C" Full Scan
2025-06-27 05:18:18.127 E + ............-> Table "PUBLIC"."TEST3" as "Q4_C" Full Scan
2025-06-27 05:18:18.139 E Sub-query
2025-06-27 05:18:18.145 E ....-> Filter
2025-06-27 05:18:18.151 E ........-> Filter
2025-06-27 05:18:18.159 E - ............-> Table "TEST2" as "Q4_B" Full Scan
2025-06-27 05:18:18.169 E + ............-> Table "PUBLIC"."TEST2" as "Q4_B" Full Scan
2025-06-27 05:18:18.184 E Select Expression
2025-06-27 05:18:18.191 E ....-> Aggregate
2025-06-27 05:18:18.197 E ........-> Filter
2025-06-27 05:18:18.203 E - ............-> Table "TEST1" as "Q4_A" Full Scan
2025-06-27 05:18:18.209 E + ............-> Table "PUBLIC"."TEST1" as "Q4_A" Full Scan
2025-06-27 05:18:18.221 E 10
2025-06-27 05:18:18.226
2025-06-27 05:18:18.236 tests/bugs/gh_8265_test.py:272: AssertionError
2025-06-27 05:18:18.246 ---------------------------- Captured stdout setup -----------------------------
2025-06-27 05:18:18.253 Creating db: localhost:/var/tmp/qa_2024/test_11715/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
|