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-29 05:35:22.414
2025-06-29 05:35:22.421 act = <firebird.qa.plugin.Action object at [hex]>
2025-06-29 05:35:22.428 capsys = <_pytest.capture.CaptureFixture object at [hex]>
2025-06-29 05:35:22.434
2025-06-29 05:35:22.441 @pytest.mark.version('>=5.0.2')
2025-06-29 05:35:22.446 def test_1(act: Action, capsys):
2025-06-29 05:35:22.452
2025-06-29 05:35:22.460 srv_cfg = driver_config.register_server(name = f'srv_cfg_8265', config = '')
2025-06-29 05:35:22.470 db_cfg_name = f'db_cfg_8265'
2025-06-29 05:35:22.478 db_cfg_object = driver_config.register_database(name = db_cfg_name)
2025-06-29 05:35:22.490 db_cfg_object.server.value = srv_cfg.name
2025-06-29 05:35:22.502 db_cfg_object.database.value = str(act.db.db_path)
2025-06-29 05:35:22.511 if act.is_version('<6'):
2025-06-29 05:35:22.519 db_cfg_object.config.value = f"""
2025-06-29 05:35:22.530 SubQueryConversion = true
2025-06-29 05:35:22.540 """
2025-06-29 05:35:22.551
2025-06-29 05:35:22.564 with connect(db_cfg_name, user = act.db.user, password = act.db.password) as con:
2025-06-29 05:35:22.573 cur = con.cursor()
2025-06-29 05:35:22.585 for q_idx, q_tuple in query_map.items():
2025-06-29 05:35:22.597 test_sql, qry_comment = q_tuple[:2]
2025-06-29 05:35:22.609 ps,rs = None, None
2025-06-29 05:35:22.617 try:
2025-06-29 05:35:22.629 ps = cur.prepare(test_sql)
2025-06-29 05:35:22.639 print(q_idx)
2025-06-29 05:35:22.648 print(test_sql)
2025-06-29 05:35:22.655 print(qry_comment)
2025-06-29 05:35:22.662
2025-06-29 05:35:22.673 # Print explained plan with padding eash line by dots in order to see indentations:
2025-06-29 05:35:22.684 print( '\n'.join([replace_leading(s) for s in ps.detailed_plan.split('\n')]) )
2025-06-29 05:35:22.697 rs = cur.execute(ps)
2025-06-29 05:35:22.708 # Print data:
2025-06-29 05:35:22.720 for r in rs:
2025-06-29 05:35:22.731 print(r[0])
2025-06-29 05:35:22.741 except DatabaseError as e:
2025-06-29 05:35:22.749 print(e.__str__())
2025-06-29 05:35:22.757 print(e.gds_codes)
2025-06-29 05:35:22.763 finally:
2025-06-29 05:35:22.770 # explained by hvlad, 26.10.24 17:42
2025-06-29 05:35:22.777 if rs:
2025-06-29 05:35:22.784 rs.close()
2025-06-29 05:35:22.790 if ps:
2025-06-29 05:35:22.802 ps.free()
2025-06-29 05:35:22.811
2025-06-29 05:35:22.820 act.expected_stdout = f"""
2025-06-29 05:35:22.828 1000
2025-06-29 05:35:22.835 {query_map[1000][0]}
2025-06-29 05:35:22.843 {query_map[1000][1]}
2025-06-29 05:35:22.856 Select Expression
2025-06-29 05:35:22.868 ....-> Aggregate
2025-06-29 05:35:22.880 ........-> Filter
2025-06-29 05:35:22.892 ............-> Hash Join (semi) (keys: 1, total key length: 4)
2025-06-29 05:35:22.905 ................-> Table "TEST1" as "Q1_A" Full Scan
2025-06-29 05:35:22.916 ................-> Record Buffer (record length: 82)
2025-06-29 05:35:22.925 ....................-> Filter
2025-06-29 05:35:22.937 ........................-> Hash Join (semi) (keys: 1, total key length: 4)
2025-06-29 05:35:22.948 ............................-> Table "TEST2" as "Q1_B" Full Scan
2025-06-29 05:35:22.962 ............................-> Record Buffer (record length: 57)
2025-06-29 05:35:22.972 ................................-> Filter
2025-06-29 05:35:22.981 ....................................-> Table "TEST3" as "Q1_C" Full Scan
2025-06-29 05:35:22.988 10
2025-06-29 05:35:22.994
2025-06-29 05:35:23.000 2000
2025-06-29 05:35:23.007 {query_map[2000][0]}
2025-06-29 05:35:23.020 {query_map[2000][1]}
2025-06-29 05:35:23.033 Sub-query
2025-06-29 05:35:23.045 ....-> Filter
2025-06-29 05:35:23.056 ........-> Filter
2025-06-29 05:35:23.065 ............-> Table "TEST3" as "Q2_C" Full Scan
2025-06-29 05:35:23.072 Select Expression
2025-06-29 05:35:23.080 ....-> Aggregate
2025-06-29 05:35:23.091 ........-> Filter
2025-06-29 05:35:23.099 ............-> Hash Join (semi) (keys: 1, total key length: 4)
2025-06-29 05:35:23.107 ................-> Table "TEST1" as "Q2_A" Full Scan
2025-06-29 05:35:23.114 ................-> Record Buffer (record length: 33)
2025-06-29 05:35:23.120 ....................-> Filter
2025-06-29 05:35:23.127 ........................-> Table "TEST2" as "Q2_B" Full Scan
2025-06-29 05:35:23.136 10
2025-06-29 05:35:23.150
2025-06-29 05:35:23.164 3000
2025-06-29 05:35:23.174 {query_map[3000][0]}
2025-06-29 05:35:23.188 {query_map[3000][1]}
2025-06-29 05:35:23.198 Sub-query
2025-06-29 05:35:23.208 ....-> Filter
2025-06-29 05:35:23.218 ........-> Filter
2025-06-29 05:35:23.226 ............-> Table "TEST3" as "Q3_C" Full Scan
2025-06-29 05:35:23.236 Sub-query
2025-06-29 05:35:23.245 ....-> Filter
2025-06-29 05:35:23.252 ........-> Filter
2025-06-29 05:35:23.259 ............-> Table "TEST2" as "Q3_B" Full Scan
2025-06-29 05:35:23.265 Select Expression
2025-06-29 05:35:23.271 ....-> Aggregate
2025-06-29 05:35:23.283 ........-> Filter
2025-06-29 05:35:23.294 ............-> Table "TEST1" as "Q3_A" Full Scan
2025-06-29 05:35:23.303 10
2025-06-29 05:35:23.309
2025-06-29 05:35:23.316 4000
2025-06-29 05:35:23.322 {query_map[4000][0]}
2025-06-29 05:35:23.330 {query_map[4000][1]}
2025-06-29 05:35:23.337 Sub-query
2025-06-29 05:35:23.345 ....-> Filter
2025-06-29 05:35:23.350 ........-> Filter
2025-06-29 05:35:23.356 ............-> Table "TEST3" as "Q4_C" Full Scan
2025-06-29 05:35:23.363 Sub-query
2025-06-29 05:35:23.375 ....-> Filter
2025-06-29 05:35:23.381 ........-> Filter
2025-06-29 05:35:23.387 ............-> Table "TEST2" as "Q4_B" Full Scan
2025-06-29 05:35:23.393 Select Expression
2025-06-29 05:35:23.398 ....-> Aggregate
2025-06-29 05:35:23.403 ........-> Filter
2025-06-29 05:35:23.408 ............-> Table "TEST1" as "Q4_A" Full Scan
2025-06-29 05:35:23.416 10
2025-06-29 05:35:23.428 """
2025-06-29 05:35:23.435 act.stdout = capsys.readouterr().out
2025-06-29 05:35:23.442 > assert act.clean_stdout == act.clean_expected_stdout
2025-06-29 05:35:23.451 E assert
2025-06-29 05:35:23.461 E 1000
2025-06-29 05:35:23.468 E select count(*) from test1 q1_a
2025-06-29 05:35:23.474 E where
2025-06-29 05:35:23.480 E q1_a.id in (
2025-06-29 05:35:23.487 E select q1_b.pid from test2 q1_b
2025-06-29 05:35:23.493 E where
2025-06-29 05:35:23.498 E q1_b.id in (
2025-06-29 05:35:23.505 E select q1_c.pid from test3 q1_c
2025-06-29 05:35:23.511 E where q1_c.name like '%ABC%'
2025-06-29 05:35:23.518 E )
2025-06-29 05:35:23.523 E )
2025-06-29 05:35:23.531 E Both sub-queries can (and should) be unnested.
2025-06-29 05:35:23.539 E Select Expression
2025-06-29 05:35:23.547 E ....-> Aggregate
2025-06-29 05:35:23.555 E ........-> Filter
2025-06-29 05:35:23.566 E ............-> Hash Join (semi)
2025-06-29 05:35:23.574 E - ................-> Table "TEST1" as "Q1_A" Full Scan
2025-06-29 05:35:23.584 E + ................-> Table "PUBLIC"."TEST1" as "Q1_A" Full Scan
2025-06-29 05:35:23.604 E ................-> Record Buffer (record length: NN)
2025-06-29 05:35:23.611 E ....................-> Filter
2025-06-29 05:35:23.618 E ........................-> Hash Join (semi)
2025-06-29 05:35:23.626 E - ............................-> Table "TEST2" as "Q1_B" Full Scan
2025-06-29 05:35:23.638 E + ............................-> Table "PUBLIC"."TEST2" as "Q1_B" Full Scan
2025-06-29 05:35:23.654 E ............................-> Record Buffer (record length: NN)
2025-06-29 05:35:23.663 E ................................-> Filter
2025-06-29 05:35:23.670 E - ....................................-> Table "TEST3" as "Q1_C" Full Scan
2025-06-29 05:35:23.678 E + ....................................-> Table "PUBLIC"."TEST3" as "Q1_C" Full Scan
2025-06-29 05:35:23.698 E 10
2025-06-29 05:35:23.709 E 2000
2025-06-29 05:35:23.717 E select count(*) from test1 q2_a
2025-06-29 05:35:23.726 E where
2025-06-29 05:35:23.738 E q2_a.id in (
2025-06-29 05:35:23.747 E select q2_b.pid from test2 q2_b
2025-06-29 05:35:23.754 E where
2025-06-29 05:35:23.761 E 1=1 or q2_b.id in (
2025-06-29 05:35:23.776 E select q2_c.pid from test3 q2_c
2025-06-29 05:35:23.789 E where q2_c.name like '%ABC%'
2025-06-29 05:35:23.799 E )
2025-06-29 05:35:23.807 E )
2025-06-29 05:35:23.815 E Inner sub-query can NOT be unnested due to `OR` condition present, but the outer sub-query can
2025-06-29 05:35:23.822 E Sub-query
2025-06-29 05:35:23.829 E ....-> Filter
2025-06-29 05:35:23.841 E ........-> Filter
2025-06-29 05:35:23.852 E - ............-> Table "TEST3" as "Q2_C" Full Scan
2025-06-29 05:35:23.859 E + ............-> Table "PUBLIC"."TEST3" as "Q2_C" Full Scan
2025-06-29 05:35:23.870 E Select Expression
2025-06-29 05:35:23.877 E ....-> Aggregate
2025-06-29 05:35:23.884 E ........-> Filter
2025-06-29 05:35:23.894 E ............-> Hash Join (semi)
2025-06-29 05:35:23.903 E - ................-> Table "TEST1" as "Q2_A" Full Scan
2025-06-29 05:35:23.912 E + ................-> Table "PUBLIC"."TEST1" as "Q2_A" Full Scan
2025-06-29 05:35:23.927 E ................-> Record Buffer (record length: NN)
2025-06-29 05:35:23.933 E ....................-> Filter
2025-06-29 05:35:23.939 E - ........................-> Table "TEST2" as "Q2_B" Full Scan
2025-06-29 05:35:23.945 E + ........................-> Table "PUBLIC"."TEST2" as "Q2_B" Full Scan
2025-06-29 05:35:23.959 E 10
2025-06-29 05:35:23.971 E 3000
2025-06-29 05:35:23.981 E select count(*) from test1 q3_a
2025-06-29 05:35:23.993 E where
2025-06-29 05:35:24.005 E 1=1 or q3_a.id in (
2025-06-29 05:35:24.015 E select q3_b.pid from test2 q3_b
2025-06-29 05:35:24.026 E where q3_b.id in (
2025-06-29 05:35:24.035 E select id from test3 q3_c
2025-06-29 05:35:24.048 E where q3_c.name like '%ABC%'
2025-06-29 05:35:24.057 E )
2025-06-29 05:35:24.064 E )
2025-06-29 05:35:24.072 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-29 05:35:24.078 E Sub-query
2025-06-29 05:35:24.085 E ....-> Filter
2025-06-29 05:35:24.091 E ........-> Filter
2025-06-29 05:35:24.097 E - ............-> Table "TEST3" as "Q3_C" Full Scan
2025-06-29 05:35:24.103 E + ............-> Table "PUBLIC"."TEST3" as "Q3_C" Full Scan
2025-06-29 05:35:24.119 E Sub-query
2025-06-29 05:35:24.127 E ....-> Filter
2025-06-29 05:35:24.136 E ........-> Filter
2025-06-29 05:35:24.145 E - ............-> Table "TEST2" as "Q3_B" Full Scan
2025-06-29 05:35:24.153 E + ............-> Table "PUBLIC"."TEST2" as "Q3_B" Full Scan
2025-06-29 05:35:24.167 E Select Expression
2025-06-29 05:35:24.194 E ....-> Aggregate
2025-06-29 05:35:24.208 E ........-> Filter
2025-06-29 05:35:24.222 E - ............-> Table "TEST1" as "Q3_A" Full Scan
2025-06-29 05:35:24.235 E + ............-> Table "PUBLIC"."TEST1" as "Q3_A" Full Scan
2025-06-29 05:35:24.251 E 10
2025-06-29 05:35:24.257 E 4000
2025-06-29 05:35:24.263 E select count(*) from test1 q4_a
2025-06-29 05:35:24.270 E where
2025-06-29 05:35:24.279 E 1=1 or q4_a.id in (
2025-06-29 05:35:24.292 E select id from test2 q4_b
2025-06-29 05:35:24.302 E where
2025-06-29 05:35:24.311 E 1=1 or q4_b.id in (
2025-06-29 05:35:24.320 E select id from test3 q4_c
2025-06-29 05:35:24.328 E where q4_c.name like '%ABC%'
2025-06-29 05:35:24.335 E )
2025-06-29 05:35:24.348 E )
2025-06-29 05:35:24.360 E Both sub-queries can NOT be unnested due to OR conditions present
2025-06-29 05:35:24.372 E Sub-query
2025-06-29 05:35:24.386 E ....-> Filter
2025-06-29 05:35:24.395 E ........-> Filter
2025-06-29 05:35:24.402 E - ............-> Table "TEST3" as "Q4_C" Full Scan
2025-06-29 05:35:24.414 E + ............-> Table "PUBLIC"."TEST3" as "Q4_C" Full Scan
2025-06-29 05:35:24.431 E Sub-query
2025-06-29 05:35:24.438 E ....-> Filter
2025-06-29 05:35:24.445 E ........-> Filter
2025-06-29 05:35:24.452 E - ............-> Table "TEST2" as "Q4_B" Full Scan
2025-06-29 05:35:24.467 E + ............-> Table "PUBLIC"."TEST2" as "Q4_B" Full Scan
2025-06-29 05:35:24.491 E Select Expression
2025-06-29 05:35:24.500 E ....-> Aggregate
2025-06-29 05:35:24.509 E ........-> Filter
2025-06-29 05:35:24.516 E - ............-> Table "TEST1" as "Q4_A" Full Scan
2025-06-29 05:35:24.524 E + ............-> Table "PUBLIC"."TEST1" as "Q4_A" Full Scan
2025-06-29 05:35:24.539 E 10
2025-06-29 05:35:24.550
2025-06-29 05:35:24.560 tests/bugs/gh_8265_test.py:272: AssertionError
2025-06-29 05:35:24.568 ---------------------------- Captured stdout setup -----------------------------
2025-06-29 05:35:24.576 Creating db: localhost:/var/tmp/qa_2024/test_11719/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
|