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-30 05:41:19.352
2025-06-30 05:41:19.362 act = <firebird.qa.plugin.Action object at [hex]>
2025-06-30 05:41:19.370 capsys = <_pytest.capture.CaptureFixture object at [hex]>
2025-06-30 05:41:19.378
2025-06-30 05:41:19.390 @pytest.mark.version('>=5.0.2')
2025-06-30 05:41:19.401 def test_1(act: Action, capsys):
2025-06-30 05:41:19.410
2025-06-30 05:41:19.416 srv_cfg = driver_config.register_server(name = f'srv_cfg_8265', config = '')
2025-06-30 05:41:19.422 db_cfg_name = f'db_cfg_8265'
2025-06-30 05:41:19.429 db_cfg_object = driver_config.register_database(name = db_cfg_name)
2025-06-30 05:41:19.437 db_cfg_object.server.value = srv_cfg.name
2025-06-30 05:41:19.444 db_cfg_object.database.value = str(act.db.db_path)
2025-06-30 05:41:19.455 if act.is_version('<6'):
2025-06-30 05:41:19.468 db_cfg_object.config.value = f"""
2025-06-30 05:41:19.481 SubQueryConversion = true
2025-06-30 05:41:19.492 """
2025-06-30 05:41:19.501
2025-06-30 05:41:19.513 with connect(db_cfg_name, user = act.db.user, password = act.db.password) as con:
2025-06-30 05:41:19.523 cur = con.cursor()
2025-06-30 05:41:19.533 for q_idx, q_tuple in query_map.items():
2025-06-30 05:41:19.548 test_sql, qry_comment = q_tuple[:2]
2025-06-30 05:41:19.558 ps,rs = None, None
2025-06-30 05:41:19.570 try:
2025-06-30 05:41:19.582 ps = cur.prepare(test_sql)
2025-06-30 05:41:19.594 print(q_idx)
2025-06-30 05:41:19.605 print(test_sql)
2025-06-30 05:41:19.613 print(qry_comment)
2025-06-30 05:41:19.620
2025-06-30 05:41:19.627 # Print explained plan with padding eash line by dots in order to see indentations:
2025-06-30 05:41:19.637 print( '\n'.join([replace_leading(s) for s in ps.detailed_plan.split('\n')]) )
2025-06-30 05:41:19.648 rs = cur.execute(ps)
2025-06-30 05:41:19.656 # Print data:
2025-06-30 05:41:19.663 for r in rs:
2025-06-30 05:41:19.670 print(r[0])
2025-06-30 05:41:19.681 except DatabaseError as e:
2025-06-30 05:41:19.692 print(e.__str__())
2025-06-30 05:41:19.701 print(e.gds_codes)
2025-06-30 05:41:19.711 finally:
2025-06-30 05:41:19.719 # explained by hvlad, 26.10.24 17:42
2025-06-30 05:41:19.732 if rs:
2025-06-30 05:41:19.744 rs.close()
2025-06-30 05:41:19.756 if ps:
2025-06-30 05:41:19.764 ps.free()
2025-06-30 05:41:19.772
2025-06-30 05:41:19.780 act.expected_stdout = f"""
2025-06-30 05:41:19.786 1000
2025-06-30 05:41:19.797 {query_map[1000][0]}
2025-06-30 05:41:19.808 {query_map[1000][1]}
2025-06-30 05:41:19.815 Select Expression
2025-06-30 05:41:19.822 ....-> Aggregate
2025-06-30 05:41:19.832 ........-> Filter
2025-06-30 05:41:19.842 ............-> Hash Join (semi) (keys: 1, total key length: 4)
2025-06-30 05:41:19.850 ................-> Table "TEST1" as "Q1_A" Full Scan
2025-06-30 05:41:19.859 ................-> Record Buffer (record length: 82)
2025-06-30 05:41:19.866 ....................-> Filter
2025-06-30 05:41:19.875 ........................-> Hash Join (semi) (keys: 1, total key length: 4)
2025-06-30 05:41:19.883 ............................-> Table "TEST2" as "Q1_B" Full Scan
2025-06-30 05:41:19.889 ............................-> Record Buffer (record length: 57)
2025-06-30 05:41:19.894 ................................-> Filter
2025-06-30 05:41:19.899 ....................................-> Table "TEST3" as "Q1_C" Full Scan
2025-06-30 05:41:19.904 10
2025-06-30 05:41:19.909
2025-06-30 05:41:19.915 2000
2025-06-30 05:41:19.924 {query_map[2000][0]}
2025-06-30 05:41:19.936 {query_map[2000][1]}
2025-06-30 05:41:19.944 Sub-query
2025-06-30 05:41:19.950 ....-> Filter
2025-06-30 05:41:19.956 ........-> Filter
2025-06-30 05:41:19.963 ............-> Table "TEST3" as "Q2_C" Full Scan
2025-06-30 05:41:19.972 Select Expression
2025-06-30 05:41:19.979 ....-> Aggregate
2025-06-30 05:41:19.987 ........-> Filter
2025-06-30 05:41:19.996 ............-> Hash Join (semi) (keys: 1, total key length: 4)
2025-06-30 05:41:20.008 ................-> Table "TEST1" as "Q2_A" Full Scan
2025-06-30 05:41:20.016 ................-> Record Buffer (record length: 33)
2025-06-30 05:41:20.024 ....................-> Filter
2025-06-30 05:41:20.030 ........................-> Table "TEST2" as "Q2_B" Full Scan
2025-06-30 05:41:20.036 10
2025-06-30 05:41:20.042
2025-06-30 05:41:20.052 3000
2025-06-30 05:41:20.060 {query_map[3000][0]}
2025-06-30 05:41:20.067 {query_map[3000][1]}
2025-06-30 05:41:20.073 Sub-query
2025-06-30 05:41:20.080 ....-> Filter
2025-06-30 05:41:20.087 ........-> Filter
2025-06-30 05:41:20.096 ............-> Table "TEST3" as "Q3_C" Full Scan
2025-06-30 05:41:20.107 Sub-query
2025-06-30 05:41:20.116 ....-> Filter
2025-06-30 05:41:20.124 ........-> Filter
2025-06-30 05:41:20.131 ............-> Table "TEST2" as "Q3_B" Full Scan
2025-06-30 05:41:20.137 Select Expression
2025-06-30 05:41:20.143 ....-> Aggregate
2025-06-30 05:41:20.158 ........-> Filter
2025-06-30 05:41:20.170 ............-> Table "TEST1" as "Q3_A" Full Scan
2025-06-30 05:41:20.179 10
2025-06-30 05:41:20.186
2025-06-30 05:41:20.193 4000
2025-06-30 05:41:20.200 {query_map[4000][0]}
2025-06-30 05:41:20.209 {query_map[4000][1]}
2025-06-30 05:41:20.215 Sub-query
2025-06-30 05:41:20.220 ....-> Filter
2025-06-30 05:41:20.225 ........-> Filter
2025-06-30 05:41:20.231 ............-> Table "TEST3" as "Q4_C" Full Scan
2025-06-30 05:41:20.236 Sub-query
2025-06-30 05:41:20.242 ....-> Filter
2025-06-30 05:41:20.248 ........-> Filter
2025-06-30 05:41:20.254 ............-> Table "TEST2" as "Q4_B" Full Scan
2025-06-30 05:41:20.259 Select Expression
2025-06-30 05:41:20.265 ....-> Aggregate
2025-06-30 05:41:20.270 ........-> Filter
2025-06-30 05:41:20.277 ............-> Table "TEST1" as "Q4_A" Full Scan
2025-06-30 05:41:20.283 10
2025-06-30 05:41:20.292 """
2025-06-30 05:41:20.303 act.stdout = capsys.readouterr().out
2025-06-30 05:41:20.313 > assert act.clean_stdout == act.clean_expected_stdout
2025-06-30 05:41:20.320 E assert
2025-06-30 05:41:20.329 E 1000
2025-06-30 05:41:20.338 E select count(*) from test1 q1_a
2025-06-30 05:41:20.344 E where
2025-06-30 05:41:20.349 E q1_a.id in (
2025-06-30 05:41:20.354 E select q1_b.pid from test2 q1_b
2025-06-30 05:41:20.359 E where
2025-06-30 05:41:20.364 E q1_b.id in (
2025-06-30 05:41:20.373 E select q1_c.pid from test3 q1_c
2025-06-30 05:41:20.381 E where q1_c.name like '%ABC%'
2025-06-30 05:41:20.389 E )
2025-06-30 05:41:20.397 E )
2025-06-30 05:41:20.405 E Both sub-queries can (and should) be unnested.
2025-06-30 05:41:20.412 E Select Expression
2025-06-30 05:41:20.420 E ....-> Aggregate
2025-06-30 05:41:20.428 E ........-> Filter
2025-06-30 05:41:20.436 E ............-> Hash Join (semi)
2025-06-30 05:41:20.444 E - ................-> Table "TEST1" as "Q1_A" Full Scan
2025-06-30 05:41:20.452 E + ................-> Table "PUBLIC"."TEST1" as "Q1_A" Full Scan
2025-06-30 05:41:20.467 E ................-> Record Buffer (record length: NN)
2025-06-30 05:41:20.475 E ....................-> Filter
2025-06-30 05:41:20.483 E ........................-> Hash Join (semi)
2025-06-30 05:41:20.491 E - ............................-> Table "TEST2" as "Q1_B" Full Scan
2025-06-30 05:41:20.499 E + ............................-> Table "PUBLIC"."TEST2" as "Q1_B" Full Scan
2025-06-30 05:41:20.521 E ............................-> Record Buffer (record length: NN)
2025-06-30 05:41:20.532 E ................................-> Filter
2025-06-30 05:41:20.541 E - ....................................-> Table "TEST3" as "Q1_C" Full Scan
2025-06-30 05:41:20.549 E + ....................................-> Table "PUBLIC"."TEST3" as "Q1_C" Full Scan
2025-06-30 05:41:20.563 E 10
2025-06-30 05:41:20.575 E 2000
2025-06-30 05:41:20.584 E select count(*) from test1 q2_a
2025-06-30 05:41:20.597 E where
2025-06-30 05:41:20.609 E q2_a.id in (
2025-06-30 05:41:20.622 E select q2_b.pid from test2 q2_b
2025-06-30 05:41:20.635 E where
2025-06-30 05:41:20.645 E 1=1 or q2_b.id in (
2025-06-30 05:41:20.658 E select q2_c.pid from test3 q2_c
2025-06-30 05:41:20.669 E where q2_c.name like '%ABC%'
2025-06-30 05:41:20.683 E )
2025-06-30 05:41:20.692 E )
2025-06-30 05:41:20.700 E Inner sub-query can NOT be unnested due to `OR` condition present, but the outer sub-query can
2025-06-30 05:41:20.707 E Sub-query
2025-06-30 05:41:20.721 E ....-> Filter
2025-06-30 05:41:20.732 E ........-> Filter
2025-06-30 05:41:20.749 E - ............-> Table "TEST3" as "Q2_C" Full Scan
2025-06-30 05:41:20.759 E + ............-> Table "PUBLIC"."TEST3" as "Q2_C" Full Scan
2025-06-30 05:41:20.775 E Select Expression
2025-06-30 05:41:20.782 E ....-> Aggregate
2025-06-30 05:41:20.787 E ........-> Filter
2025-06-30 05:41:20.792 E ............-> Hash Join (semi)
2025-06-30 05:41:20.798 E - ................-> Table "TEST1" as "Q2_A" Full Scan
2025-06-30 05:41:20.803 E + ................-> Table "PUBLIC"."TEST1" as "Q2_A" Full Scan
2025-06-30 05:41:20.814 E ................-> Record Buffer (record length: NN)
2025-06-30 05:41:20.820 E ....................-> Filter
2025-06-30 05:41:20.826 E - ........................-> Table "TEST2" as "Q2_B" Full Scan
2025-06-30 05:41:20.834 E + ........................-> Table "PUBLIC"."TEST2" as "Q2_B" Full Scan
2025-06-30 05:41:20.847 E 10
2025-06-30 05:41:20.853 E 3000
2025-06-30 05:41:20.861 E select count(*) from test1 q3_a
2025-06-30 05:41:20.870 E where
2025-06-30 05:41:20.878 E 1=1 or q3_a.id in (
2025-06-30 05:41:20.885 E select q3_b.pid from test2 q3_b
2025-06-30 05:41:20.891 E where q3_b.id in (
2025-06-30 05:41:20.897 E select id from test3 q3_c
2025-06-30 05:41:20.904 E where q3_c.name like '%ABC%'
2025-06-30 05:41:20.910 E )
2025-06-30 05:41:20.916 E )
2025-06-30 05:41:20.925 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-30 05:41:20.933 E Sub-query
2025-06-30 05:41:20.941 E ....-> Filter
2025-06-30 05:41:20.948 E ........-> Filter
2025-06-30 05:41:20.956 E - ............-> Table "TEST3" as "Q3_C" Full Scan
2025-06-30 05:41:20.965 E + ............-> Table "PUBLIC"."TEST3" as "Q3_C" Full Scan
2025-06-30 05:41:20.981 E Sub-query
2025-06-30 05:41:20.989 E ....-> Filter
2025-06-30 05:41:20.997 E ........-> Filter
2025-06-30 05:41:21.006 E - ............-> Table "TEST2" as "Q3_B" Full Scan
2025-06-30 05:41:21.016 E + ............-> Table "PUBLIC"."TEST2" as "Q3_B" Full Scan
2025-06-30 05:41:21.032 E Select Expression
2025-06-30 05:41:21.044 E ....-> Aggregate
2025-06-30 05:41:21.057 E ........-> Filter
2025-06-30 05:41:21.071 E - ............-> Table "TEST1" as "Q3_A" Full Scan
2025-06-30 05:41:21.085 E + ............-> Table "PUBLIC"."TEST1" as "Q3_A" Full Scan
2025-06-30 05:41:21.109 E 10
2025-06-30 05:41:21.118 E 4000
2025-06-30 05:41:21.127 E select count(*) from test1 q4_a
2025-06-30 05:41:21.139 E where
2025-06-30 05:41:21.149 E 1=1 or q4_a.id in (
2025-06-30 05:41:21.156 E select id from test2 q4_b
2025-06-30 05:41:21.162 E where
2025-06-30 05:41:21.167 E 1=1 or q4_b.id in (
2025-06-30 05:41:21.173 E select id from test3 q4_c
2025-06-30 05:41:21.179 E where q4_c.name like '%ABC%'
2025-06-30 05:41:21.192 E )
2025-06-30 05:41:21.201 E )
2025-06-30 05:41:21.209 E Both sub-queries can NOT be unnested due to OR conditions present
2025-06-30 05:41:21.216 E Sub-query
2025-06-30 05:41:21.222 E ....-> Filter
2025-06-30 05:41:21.228 E ........-> Filter
2025-06-30 05:41:21.235 E - ............-> Table "TEST3" as "Q4_C" Full Scan
2025-06-30 05:41:21.242 E + ............-> Table "PUBLIC"."TEST3" as "Q4_C" Full Scan
2025-06-30 05:41:21.255 E Sub-query
2025-06-30 05:41:21.261 E ....-> Filter
2025-06-30 05:41:21.268 E ........-> Filter
2025-06-30 05:41:21.275 E - ............-> Table "TEST2" as "Q4_B" Full Scan
2025-06-30 05:41:21.281 E + ............-> Table "PUBLIC"."TEST2" as "Q4_B" Full Scan
2025-06-30 05:41:21.294 E Select Expression
2025-06-30 05:41:21.300 E ....-> Aggregate
2025-06-30 05:41:21.306 E ........-> Filter
2025-06-30 05:41:21.312 E - ............-> Table "TEST1" as "Q4_A" Full Scan
2025-06-30 05:41:21.319 E + ............-> Table "PUBLIC"."TEST1" as "Q4_A" Full Scan
2025-06-30 05:41:21.335 E 10
2025-06-30 05:41:21.344
2025-06-30 05:41:21.351 tests/bugs/gh_8265_test.py:272: AssertionError
2025-06-30 05:41:21.358 ---------------------------- Captured stdout setup -----------------------------
2025-06-30 05:41:21.365 Creating db: localhost:/var/tmp/qa_2024/test_11722/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
|