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 06:37:14.747
2025-06-27 06:37:14.747 act = <firebird.qa.plugin.Action object at [hex]>
2025-06-27 06:37:14.747 capsys = <_pytest.capture.CaptureFixture object at [hex]>
2025-06-27 06:37:14.747
2025-06-27 06:37:14.747 @pytest.mark.version('>=5.0.2')
2025-06-27 06:37:14.747 def test_1(act: Action, capsys):
2025-06-27 06:37:14.747
2025-06-27 06:37:14.747 srv_cfg = driver_config.register_server(name = f'srv_cfg_8265', config = '')
2025-06-27 06:37:14.747 db_cfg_name = f'db_cfg_8265'
2025-06-27 06:37:14.747 db_cfg_object = driver_config.register_database(name = db_cfg_name)
2025-06-27 06:37:14.747 db_cfg_object.server.value = srv_cfg.name
2025-06-27 06:37:14.747 db_cfg_object.database.value = str(act.db.db_path)
2025-06-27 06:37:14.747 if act.is_version('<6'):
2025-06-27 06:37:14.747 db_cfg_object.config.value = f"""
2025-06-27 06:37:14.747 SubQueryConversion = true
2025-06-27 06:37:14.747 """
2025-06-27 06:37:14.747
2025-06-27 06:37:14.747 with connect(db_cfg_name, user = act.db.user, password = act.db.password) as con:
2025-06-27 06:37:14.748 cur = con.cursor()
2025-06-27 06:37:14.748 for q_idx, q_tuple in query_map.items():
2025-06-27 06:37:14.748 test_sql, qry_comment = q_tuple[:2]
2025-06-27 06:37:14.748 ps,rs = None, None
2025-06-27 06:37:14.748 try:
2025-06-27 06:37:14.748 ps = cur.prepare(test_sql)
2025-06-27 06:37:14.748 print(q_idx)
2025-06-27 06:37:14.748 print(test_sql)
2025-06-27 06:37:14.748 print(qry_comment)
2025-06-27 06:37:14.748
2025-06-27 06:37:14.748 # Print explained plan with padding eash line by dots in order to see indentations:
2025-06-27 06:37:14.748 print( '\n'.join([replace_leading(s) for s in ps.detailed_plan.split('\n')]) )
2025-06-27 06:37:14.748 rs = cur.execute(ps)
2025-06-27 06:37:14.748 # Print data:
2025-06-27 06:37:14.748 for r in rs:
2025-06-27 06:37:14.748 print(r[0])
2025-06-27 06:37:14.748 except DatabaseError as e:
2025-06-27 06:37:14.748 print(e.__str__())
2025-06-27 06:37:14.748 print(e.gds_codes)
2025-06-27 06:37:14.748 finally:
2025-06-27 06:37:14.749 # explained by hvlad, 26.10.24 17:42
2025-06-27 06:37:14.749 if rs:
2025-06-27 06:37:14.749 rs.close()
2025-06-27 06:37:14.749 if ps:
2025-06-27 06:37:14.749 ps.free()
2025-06-27 06:37:14.749
2025-06-27 06:37:14.749 act.expected_stdout = f"""
2025-06-27 06:37:14.749 1000
2025-06-27 06:37:14.749 {query_map[1000][0]}
2025-06-27 06:37:14.749 {query_map[1000][1]}
2025-06-27 06:37:14.749 Select Expression
2025-06-27 06:37:14.749 ....-> Aggregate
2025-06-27 06:37:14.749 ........-> Filter
2025-06-27 06:37:14.749 ............-> Hash Join (semi) (keys: 1, total key length: 4)
2025-06-27 06:37:14.749 ................-> Table "TEST1" as "Q1_A" Full Scan
2025-06-27 06:37:14.749 ................-> Record Buffer (record length: 82)
2025-06-27 06:37:14.749 ....................-> Filter
2025-06-27 06:37:14.749 ........................-> Hash Join (semi) (keys: 1, total key length: 4)
2025-06-27 06:37:14.749 ............................-> Table "TEST2" as "Q1_B" Full Scan
2025-06-27 06:37:14.750 ............................-> Record Buffer (record length: 57)
2025-06-27 06:37:14.750 ................................-> Filter
2025-06-27 06:37:14.750 ....................................-> Table "TEST3" as "Q1_C" Full Scan
2025-06-27 06:37:14.750 10
2025-06-27 06:37:14.750
2025-06-27 06:37:14.750 2000
2025-06-27 06:37:14.750 {query_map[2000][0]}
2025-06-27 06:37:14.750 {query_map[2000][1]}
2025-06-27 06:37:14.750 Sub-query
2025-06-27 06:37:14.750 ....-> Filter
2025-06-27 06:37:14.750 ........-> Filter
2025-06-27 06:37:14.750 ............-> Table "TEST3" as "Q2_C" Full Scan
2025-06-27 06:37:14.750 Select Expression
2025-06-27 06:37:14.750 ....-> Aggregate
2025-06-27 06:37:14.750 ........-> Filter
2025-06-27 06:37:14.750 ............-> Hash Join (semi) (keys: 1, total key length: 4)
2025-06-27 06:37:14.750 ................-> Table "TEST1" as "Q2_A" Full Scan
2025-06-27 06:37:14.750 ................-> Record Buffer (record length: 33)
2025-06-27 06:37:14.750 ....................-> Filter
2025-06-27 06:37:14.751 ........................-> Table "TEST2" as "Q2_B" Full Scan
2025-06-27 06:37:14.751 10
2025-06-27 06:37:14.751
2025-06-27 06:37:14.751 3000
2025-06-27 06:37:14.751 {query_map[3000][0]}
2025-06-27 06:37:14.751 {query_map[3000][1]}
2025-06-27 06:37:14.751 Sub-query
2025-06-27 06:37:14.751 ....-> Filter
2025-06-27 06:37:14.751 ........-> Filter
2025-06-27 06:37:14.751 ............-> Table "TEST3" as "Q3_C" Full Scan
2025-06-27 06:37:14.751 Sub-query
2025-06-27 06:37:14.751 ....-> Filter
2025-06-27 06:37:14.751 ........-> Filter
2025-06-27 06:37:14.751 ............-> Table "TEST2" as "Q3_B" Full Scan
2025-06-27 06:37:14.751 Select Expression
2025-06-27 06:37:14.751 ....-> Aggregate
2025-06-27 06:37:14.751 ........-> Filter
2025-06-27 06:37:14.751 ............-> Table "TEST1" as "Q3_A" Full Scan
2025-06-27 06:37:14.751 10
2025-06-27 06:37:14.751
2025-06-27 06:37:14.752 4000
2025-06-27 06:37:14.752 {query_map[4000][0]}
2025-06-27 06:37:14.752 {query_map[4000][1]}
2025-06-27 06:37:14.752 Sub-query
2025-06-27 06:37:14.752 ....-> Filter
2025-06-27 06:37:14.752 ........-> Filter
2025-06-27 06:37:14.752 ............-> Table "TEST3" as "Q4_C" Full Scan
2025-06-27 06:37:14.752 Sub-query
2025-06-27 06:37:14.752 ....-> Filter
2025-06-27 06:37:14.752 ........-> Filter
2025-06-27 06:37:14.752 ............-> Table "TEST2" as "Q4_B" Full Scan
2025-06-27 06:37:14.752 Select Expression
2025-06-27 06:37:14.752 ....-> Aggregate
2025-06-27 06:37:14.752 ........-> Filter
2025-06-27 06:37:14.752 ............-> Table "TEST1" as "Q4_A" Full Scan
2025-06-27 06:37:14.752 10
2025-06-27 06:37:14.752 """
2025-06-27 06:37:14.752 act.stdout = capsys.readouterr().out
2025-06-27 06:37:14.752 > assert act.clean_stdout == act.clean_expected_stdout
2025-06-27 06:37:14.752 E assert
2025-06-27 06:37:14.753 E 1000
2025-06-27 06:37:14.753 E select count(*) from test1 q1_a
2025-06-27 06:37:14.753 E where
2025-06-27 06:37:14.753 E q1_a.id in (
2025-06-27 06:37:14.753 E select q1_b.pid from test2 q1_b
2025-06-27 06:37:14.753 E where
2025-06-27 06:37:14.753 E q1_b.id in (
2025-06-27 06:37:14.753 E select q1_c.pid from test3 q1_c
2025-06-27 06:37:14.753 E where q1_c.name like '%ABC%'
2025-06-27 06:37:14.753 E )
2025-06-27 06:37:14.753 E )
2025-06-27 06:37:14.753 E Both sub-queries can (and should) be unnested.
2025-06-27 06:37:14.753 E Select Expression
2025-06-27 06:37:14.753 E ....-> Aggregate
2025-06-27 06:37:14.753 E ........-> Filter
2025-06-27 06:37:14.753 E ............-> Hash Join (semi)
2025-06-27 06:37:14.753 E - ................-> Table "TEST1" as "Q1_A" Full Scan
2025-06-27 06:37:14.753 E + ................-> Table "PUBLIC"."TEST1" as "Q1_A" Full Scan
2025-06-27 06:37:14.753 E ................-> Record Buffer (record length: NN)
2025-06-27 06:37:14.754 E ....................-> Filter
2025-06-27 06:37:14.754 E ........................-> Hash Join (semi)
2025-06-27 06:37:14.754 E - ............................-> Table "TEST2" as "Q1_B" Full Scan
2025-06-27 06:37:14.754 E + ............................-> Table "PUBLIC"."TEST2" as "Q1_B" Full Scan
2025-06-27 06:37:14.754 E ............................-> Record Buffer (record length: NN)
2025-06-27 06:37:14.754 E ................................-> Filter
2025-06-27 06:37:14.754 E - ....................................-> Table "TEST3" as "Q1_C" Full Scan
2025-06-27 06:37:14.754 E + ....................................-> Table "PUBLIC"."TEST3" as "Q1_C" Full Scan
2025-06-27 06:37:14.754 E 10
2025-06-27 06:37:14.754 E 2000
2025-06-27 06:37:14.754 E select count(*) from test1 q2_a
2025-06-27 06:37:14.754 E where
2025-06-27 06:37:14.754 E q2_a.id in (
2025-06-27 06:37:14.754 E select q2_b.pid from test2 q2_b
2025-06-27 06:37:14.754 E where
2025-06-27 06:37:14.754 E 1=1 or q2_b.id in (
2025-06-27 06:37:14.754 E select q2_c.pid from test3 q2_c
2025-06-27 06:37:14.754 E where q2_c.name like '%ABC%'
2025-06-27 06:37:14.754 E )
2025-06-27 06:37:14.755 E )
2025-06-27 06:37:14.755 E Inner sub-query can NOT be unnested due to `OR` condition present, but the outer sub-query can
2025-06-27 06:37:14.755 E Sub-query
2025-06-27 06:37:14.755 E ....-> Filter
2025-06-27 06:37:14.755 E ........-> Filter
2025-06-27 06:37:14.755 E - ............-> Table "TEST3" as "Q2_C" Full Scan
2025-06-27 06:37:14.755 E + ............-> Table "PUBLIC"."TEST3" as "Q2_C" Full Scan
2025-06-27 06:37:14.755 E Select Expression
2025-06-27 06:37:14.755 E ....-> Aggregate
2025-06-27 06:37:14.755 E ........-> Filter
2025-06-27 06:37:14.755 E ............-> Hash Join (semi)
2025-06-27 06:37:14.755 E - ................-> Table "TEST1" as "Q2_A" Full Scan
2025-06-27 06:37:14.755 E + ................-> Table "PUBLIC"."TEST1" as "Q2_A" Full Scan
2025-06-27 06:37:14.755 E ................-> Record Buffer (record length: NN)
2025-06-27 06:37:14.755 E ....................-> Filter
2025-06-27 06:37:14.755 E - ........................-> Table "TEST2" as "Q2_B" Full Scan
2025-06-27 06:37:14.755 E + ........................-> Table "PUBLIC"."TEST2" as "Q2_B" Full Scan
2025-06-27 06:37:14.756 E 10
2025-06-27 06:37:14.756 E 3000
2025-06-27 06:37:14.756 E select count(*) from test1 q3_a
2025-06-27 06:37:14.756 E where
2025-06-27 06:37:14.756 E 1=1 or q3_a.id in (
2025-06-27 06:37:14.756 E select q3_b.pid from test2 q3_b
2025-06-27 06:37:14.756 E where q3_b.id in (
2025-06-27 06:37:14.756 E select id from test3 q3_c
2025-06-27 06:37:14.756 E where q3_c.name like '%ABC%'
2025-06-27 06:37:14.756 E )
2025-06-27 06:37:14.756 E )
2025-06-27 06:37:14.756 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 06:37:14.756 E Sub-query
2025-06-27 06:37:14.756 E ....-> Filter
2025-06-27 06:37:14.756 E ........-> Filter
2025-06-27 06:37:14.756 E - ............-> Table "TEST3" as "Q3_C" Full Scan
2025-06-27 06:37:14.756 E + ............-> Table "PUBLIC"."TEST3" as "Q3_C" Full Scan
2025-06-27 06:37:14.756 E Sub-query
2025-06-27 06:37:14.756 E ....-> Filter
2025-06-27 06:37:14.756 E ........-> Filter
2025-06-27 06:37:14.757 E - ............-> Table "TEST2" as "Q3_B" Full Scan
2025-06-27 06:37:14.757 E + ............-> Table "PUBLIC"."TEST2" as "Q3_B" Full Scan
2025-06-27 06:37:14.757 E Select Expression
2025-06-27 06:37:14.757 E ....-> Aggregate
2025-06-27 06:37:14.757 E ........-> Filter
2025-06-27 06:37:14.757 E - ............-> Table "TEST1" as "Q3_A" Full Scan
2025-06-27 06:37:14.757 E + ............-> Table "PUBLIC"."TEST1" as "Q3_A" Full Scan
2025-06-27 06:37:14.757 E 10
2025-06-27 06:37:14.757 E 4000
2025-06-27 06:37:14.757 E select count(*) from test1 q4_a
2025-06-27 06:37:14.757 E where
2025-06-27 06:37:14.757 E 1=1 or q4_a.id in (
2025-06-27 06:37:14.757 E select id from test2 q4_b
2025-06-27 06:37:14.757 E where
2025-06-27 06:37:14.757 E 1=1 or q4_b.id in (
2025-06-27 06:37:14.757 E select id from test3 q4_c
2025-06-27 06:37:14.757 E where q4_c.name like '%ABC%'
2025-06-27 06:37:14.757 E )
2025-06-27 06:37:14.757 E )
2025-06-27 06:37:14.758 E Both sub-queries can NOT be unnested due to OR conditions present
2025-06-27 06:37:14.758 E Sub-query
2025-06-27 06:37:14.758 E ....-> Filter
2025-06-27 06:37:14.758 E ........-> Filter
2025-06-27 06:37:14.758 E - ............-> Table "TEST3" as "Q4_C" Full Scan
2025-06-27 06:37:14.758 E + ............-> Table "PUBLIC"."TEST3" as "Q4_C" Full Scan
2025-06-27 06:37:14.758 E Sub-query
2025-06-27 06:37:14.758 E ....-> Filter
2025-06-27 06:37:14.758 E ........-> Filter
2025-06-27 06:37:14.758 E - ............-> Table "TEST2" as "Q4_B" Full Scan
2025-06-27 06:37:14.758 E + ............-> Table "PUBLIC"."TEST2" as "Q4_B" Full Scan
2025-06-27 06:37:14.758 E Select Expression
2025-06-27 06:37:14.758 E ....-> Aggregate
2025-06-27 06:37:14.758 E ........-> Filter
2025-06-27 06:37:14.758 E - ............-> Table "TEST1" as "Q4_A" Full Scan
2025-06-27 06:37:14.758 E + ............-> Table "PUBLIC"."TEST1" as "Q4_A" Full Scan
2025-06-27 06:37:14.759 E 10
2025-06-27 06:37:14.759
2025-06-27 06:37:14.759 tests\bugs\gh_8265_test.py:272: AssertionError
2025-06-27 06:37:14.759 ---------------------------- Captured stdout setup ----------------------------
2025-06-27 06:37:14.759 Creating db: localhost:H:\QA\temp\qa2024.tmp\fbqa\test_11738\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
|