2 @message |
assert
+ Initial script failed, check output:
+ Statement failed, SQLSTATE = HY000
+ ALTER CHARACTER SET "SYSTEM"."UTF8" failed
+ -Cannot CREATE/ALTER/DROP CHARACTER SET in SYSTEM schema
+ After line 16 in file /var/tmp/qa_2024/test_11667/gh_8061.tmp.sql
- 1000
- select c1.cust_no
- from customer c1
- where exists (
- select 1 from sales s1 where s1.cust_no = c1.cust_no and s1.qty_ordered > 10
- )
- Check unnesting of single EXISTS
- Select Expression
- ....-> Filter
- ........-> Hash Join (semi)
- ............-> Table "CUSTOMER" as "C1" Full Scan
- ............-> Record Buffer (record length: NN)
- ................-> Filter
- ....................-> Table "SALES" as "S1" Full Scan
- 2000
- select c2.cust_no
- from customer c2
- where c2.cust_no = any (
- select s2.cust_no
- from sales s2
- where s2.qty_ordered > 10
- )
- Check unnesting of ANY
- Select Expression
- ....-> Filter
- ........-> Hash Join (semi)
- ............-> Table "CUSTOMER" as "C2" Full Scan
- ............-> Record Buffer (record length: NN)
- ................-> Filter
- ....................-> Table "SALES" as "S2" Full Scan
- 3000
- select c3.cust_no
- from customer c3
- where exists (
- select s3.cust_no
- from sales s3
- where s3.cust_no = c3.cust_no and
- exists (
- select x.emp_no
- from employee x
- where
- x.emp_no = s3.sales_rep
- and (
- x.dept_no > 0
- or
- x.job_code > ''
- )
- )
- )
- Check unnesting of two nested EXISTS
- Select Expression
- ....-> Filter
- ........-> Hash Join (semi)
- ............-> Table "CUSTOMER" as "C3" Full Scan
- ............-> Record Buffer (record length: NN)
- ................-> Filter
- ....................-> Hash Join (semi)
- ........................-> Table "SALES" as "S3" Full Scan
- ........................-> Record Buffer (record length: NN)
- ............................-> Filter
- ................................-> Table "EMPLOYEE" as "X" Full Scan
- 4000
- select c4.cust_no
- from customer c4
- where c4.cust_no in
- (
- select s4.cust_no
- from sales s4
- where
- s4.paid > ''
- or
- s4.sales_rep in (
- select x.emp_no
- from employee x
- where
- x.dept_no > 0
- or
- x.job_code > ''
- )
- )
- Check unnesting of IN (NOTE: inner sub-query cannot be unnested due to OR condition present, but the outer sub-query *can*; see also bugs/gh_8265_test.py)
- Sub-query
- ....-> Filter
- ........-> Filter
- ............-> Table "EMPLOYEE" as "X" Access By ID
- ................-> Bitmap
- ....................-> Index "EMPLOYEE_PK" Unique Scan
- Select Expression
- ....-> Filter
- ........-> Hash Join (semi)
- ............-> Table "CUSTOMER" as "C4" Full Scan
- ............-> Record Buffer (record length: NN)
- ................-> Filter
- ....................-> Table "SALES" as "S4" Full Scan
LOG DETAILS:
2025-06-24 05:22:58.488
2025-06-24 05:22:58.496 act = <firebird.qa.plugin.Action object at [hex]>
2025-06-24 05:22:58.503 tmp_sql = PosixPath('/var/tmp/qa_2024/test_11667/gh_8061.tmp.sql')
2025-06-24 05:22:58.516 capsys = <_pytest.capture.CaptureFixture object at [hex]>
2025-06-24 05:22:58.527
2025-06-24 05:22:58.535 @pytest.mark.version('>=5.0.1')
2025-06-24 05:22:58.543 def test_1(act: Action, tmp_sql: Path, capsys):
2025-06-24 05:22:58.550 employee_data_sql = zipfile.Path(act.files_dir / 'standard_sample_databases.zip', at='sample-DB_-_firebird.sql')
2025-06-24 05:22:58.556 tmp_sql.write_bytes(employee_data_sql.read_bytes())
2025-06-24 05:22:58.562
2025-06-24 05:22:58.568 act.isql(switches = ['-q'], charset='utf8', input_file = tmp_sql, combine_output = True)
2025-06-24 05:22:58.574
2025-06-24 05:22:58.584 if act.return_code == 0:
2025-06-24 05:22:58.594
2025-06-24 05:22:58.605 srv_cfg = driver_config.register_server(name = f'srv_cfg_8061', config = '')
2025-06-24 05:22:58.616 db_cfg_name = f'db_cfg_8061'
2025-06-24 05:22:58.629 db_cfg_object = driver_config.register_database(name = db_cfg_name)
2025-06-24 05:22:58.638 db_cfg_object.server.value = srv_cfg.name
2025-06-24 05:22:58.647 db_cfg_object.database.value = str(act.db.db_path)
2025-06-24 05:22:58.660 if act.is_version('<6'):
2025-06-24 05:22:58.670 db_cfg_object.config.value = f"""
2025-06-24 05:22:58.678 SubQueryConversion = true
2025-06-24 05:22:58.685 """
2025-06-24 05:22:58.690
2025-06-24 05:22:58.697 with connect(db_cfg_name, user = act.db.user, password = act.db.password) as con:
2025-06-24 05:22:58.703 cur = con.cursor()
2025-06-24 05:22:58.711 for q_idx, q_tuple in query_map.items():
2025-06-24 05:22:58.722 test_sql, qry_comment = q_tuple[:2]
2025-06-24 05:22:58.731 ps = cur.prepare(test_sql)
2025-06-24 05:22:58.739 print(q_idx)
2025-06-24 05:22:58.745 print(test_sql)
2025-06-24 05:22:58.751 print(qry_comment)
2025-06-24 05:22:58.757 print( '\n'.join([replace_leading(s) for s in ps.detailed_plan.split('\n')]) )
2025-06-24 05:22:58.763 ps.free()
2025-06-24 05:22:58.769
2025-06-24 05:22:58.775 else:
2025-06-24 05:22:58.781 # If retcode !=0 then we can print the whole output of failed gbak:
2025-06-24 05:22:58.787 print('Initial script failed, check output:')
2025-06-24 05:22:58.795 for line in act.clean_stdout.splitlines():
2025-06-24 05:22:58.801 print(line)
2025-06-24 05:22:58.807 act.reset()
2025-06-24 05:22:58.812
2025-06-24 05:22:58.818 act.expected_stdout = f"""
2025-06-24 05:22:58.829 1000
2025-06-24 05:22:58.839 {query_map[1000][0]}
2025-06-24 05:22:58.847 {query_map[1000][1]}
2025-06-24 05:22:58.853 Select Expression
2025-06-24 05:22:58.860 ....-> Filter
2025-06-24 05:22:58.871 ........-> Hash Join (semi) (keys: 1, total key length: 4)
2025-06-24 05:22:58.882 ............-> Table "CUSTOMER" as "C1" Full Scan
2025-06-24 05:22:58.893 ............-> Record Buffer (record length: 33)
2025-06-24 05:22:58.903 ................-> Filter
2025-06-24 05:22:58.914 ....................-> Table "SALES" as "S1" Full Scan
2025-06-24 05:22:58.926
2025-06-24 05:22:58.934 2000
2025-06-24 05:22:58.942 {query_map[2000][0]}
2025-06-24 05:22:58.947 {query_map[2000][1]}
2025-06-24 05:22:58.954 Select Expression
2025-06-24 05:22:58.967 ....-> Filter
2025-06-24 05:22:58.977 ........-> Hash Join (semi) (keys: 1, total key length: 4)
2025-06-24 05:22:58.983 ............-> Table "CUSTOMER" as "C2" Full Scan
2025-06-24 05:22:58.987 ............-> Record Buffer (record length: 33)
2025-06-24 05:22:58.998 ................-> Filter
2025-06-24 05:22:59.009 ....................-> Table "SALES" as "S2" Full Scan
2025-06-24 05:22:59.015
2025-06-24 05:22:59.021 3000
2025-06-24 05:22:59.025 {query_map[3000][0]}
2025-06-24 05:22:59.030 {query_map[3000][1]}
2025-06-24 05:22:59.034 Select Expression
2025-06-24 05:22:59.039 ....-> Filter
2025-06-24 05:22:59.043 ........-> Hash Join (semi) (keys: 1, total key length: 4)
2025-06-24 05:22:59.053 ............-> Table "CUSTOMER" as "C3" Full Scan
2025-06-24 05:22:59.060 ............-> Record Buffer (record length: 58)
2025-06-24 05:22:59.067 ................-> Filter
2025-06-24 05:22:59.076 ....................-> Hash Join (semi) (keys: 1, total key length: 2)
2025-06-24 05:22:59.089 ........................-> Table "SALES" as "S3" Full Scan
2025-06-24 05:22:59.099 ........................-> Record Buffer (record length: 41)
2025-06-24 05:22:59.106 ............................-> Filter
2025-06-24 05:22:59.111 ................................-> Table "EMPLOYEE" as "X" Full Scan
2025-06-24 05:22:59.117
2025-06-24 05:22:59.123 4000
2025-06-24 05:22:59.130 {query_map[4000][0]}
2025-06-24 05:22:59.139 {query_map[4000][1]}
2025-06-24 05:22:59.145 Sub-query
2025-06-24 05:22:59.152 ....-> Filter
2025-06-24 05:22:59.159 ........-> Filter
2025-06-24 05:22:59.166 ............-> Table "EMPLOYEE" as "X" Access By ID
2025-06-24 05:22:59.172 ................-> Bitmap
2025-06-24 05:22:59.179 ....................-> Index "EMPLOYEE_PK" Unique Scan
2025-06-24 05:22:59.184 Select Expression
2025-06-24 05:22:59.190 ....-> Filter
2025-06-24 05:22:59.195 ........-> Hash Join (semi) (keys: 1, total key length: 4)
2025-06-24 05:22:59.203 ............-> Table "CUSTOMER" as "C4" Full Scan
2025-06-24 05:22:59.210 ............-> Record Buffer (record length: 33)
2025-06-24 05:22:59.215 ................-> Filter
2025-06-24 05:22:59.220 ....................-> Table "SALES" as "S4" Full Scan
2025-06-24 05:22:59.226
2025-06-24 05:22:59.232 """
2025-06-24 05:22:59.238 act.stdout = capsys.readouterr().out
2025-06-24 05:22:59.244 > assert act.clean_stdout == act.clean_expected_stdout
2025-06-24 05:22:59.255 E assert
2025-06-24 05:22:59.262 E + Initial script failed, check output:
2025-06-24 05:22:59.268 E + Statement failed, SQLSTATE = HY000
2025-06-24 05:22:59.274 E + ALTER CHARACTER SET "SYSTEM"."UTF8" failed
2025-06-24 05:22:59.280 E + -Cannot CREATE/ALTER/DROP CHARACTER SET in SYSTEM schema
2025-06-24 05:22:59.287 E + After line 16 in file /var/tmp/qa_2024/test_11667/gh_8061.tmp.sql
2025-06-24 05:22:59.293 E - 1000
2025-06-24 05:22:59.299 E - select c1.cust_no
2025-06-24 05:22:59.305 E - from customer c1
2025-06-24 05:22:59.311 E - where exists (
2025-06-24 05:22:59.317 E - select 1 from sales s1 where s1.cust_no = c1.cust_no and s1.qty_ordered > 10
2025-06-24 05:22:59.323 E - )
2025-06-24 05:22:59.329 E - Check unnesting of single EXISTS
2025-06-24 05:22:59.339 E - Select Expression
2025-06-24 05:22:59.346 E - ....-> Filter
2025-06-24 05:22:59.351 E - ........-> Hash Join (semi)
2025-06-24 05:22:59.359 E - ............-> Table "CUSTOMER" as "C1" Full Scan
2025-06-24 05:22:59.366 E - ............-> Record Buffer (record length: NN)
2025-06-24 05:22:59.376 E - ................-> Filter
2025-06-24 05:22:59.382 E - ....................-> Table "SALES" as "S1" Full Scan
2025-06-24 05:22:59.388 E - 2000
2025-06-24 05:22:59.394 E - select c2.cust_no
2025-06-24 05:22:59.400 E - from customer c2
2025-06-24 05:22:59.406 E - where c2.cust_no = any (
2025-06-24 05:22:59.416 E - select s2.cust_no
2025-06-24 05:22:59.425 E - from sales s2
2025-06-24 05:22:59.432 E - where s2.qty_ordered > 10
2025-06-24 05:22:59.437 E - )
2025-06-24 05:22:59.443 E - Check unnesting of ANY
2025-06-24 05:22:59.449 E - Select Expression
2025-06-24 05:22:59.455 E - ....-> Filter
2025-06-24 05:22:59.461 E - ........-> Hash Join (semi)
2025-06-24 05:22:59.466 E - ............-> Table "CUSTOMER" as "C2" Full Scan
2025-06-24 05:22:59.472 E - ............-> Record Buffer (record length: NN)
2025-06-24 05:22:59.478 E - ................-> Filter
2025-06-24 05:22:59.484 E - ....................-> Table "SALES" as "S2" Full Scan
2025-06-24 05:22:59.489 E - 3000
2025-06-24 05:22:59.495 E - select c3.cust_no
2025-06-24 05:22:59.503 E - from customer c3
2025-06-24 05:22:59.509 E - where exists (
2025-06-24 05:22:59.515 E - select s3.cust_no
2025-06-24 05:22:59.520 E - from sales s3
2025-06-24 05:22:59.526 E - where s3.cust_no = c3.cust_no and
2025-06-24 05:22:59.532 E - exists (
2025-06-24 05:22:59.537 E - select x.emp_no
2025-06-24 05:22:59.543 E - from employee x
2025-06-24 05:22:59.548 E - where
2025-06-24 05:22:59.554 E - x.emp_no = s3.sales_rep
2025-06-24 05:22:59.560 E - and (
2025-06-24 05:22:59.565 E - x.dept_no > 0
2025-06-24 05:22:59.571 E - or
2025-06-24 05:22:59.577 E - x.job_code > ''
2025-06-24 05:22:59.582 E - )
2025-06-24 05:22:59.588 E - )
2025-06-24 05:22:59.594 E - )
2025-06-24 05:22:59.600 E - Check unnesting of two nested EXISTS
2025-06-24 05:22:59.606 E - Select Expression
2025-06-24 05:22:59.611 E - ....-> Filter
2025-06-24 05:22:59.617 E - ........-> Hash Join (semi)
2025-06-24 05:22:59.623 E - ............-> Table "CUSTOMER" as "C3" Full Scan
2025-06-24 05:22:59.628 E - ............-> Record Buffer (record length: NN)
2025-06-24 05:22:59.634 E - ................-> Filter
2025-06-24 05:22:59.640 E - ....................-> Hash Join (semi)
2025-06-24 05:22:59.646 E - ........................-> Table "SALES" as "S3" Full Scan
2025-06-24 05:22:59.651 E - ........................-> Record Buffer (record length: NN)
2025-06-24 05:22:59.657 E - ............................-> Filter
2025-06-24 05:22:59.663 E - ................................-> Table "EMPLOYEE" as "X" Full Scan
2025-06-24 05:22:59.669 E - 4000
2025-06-24 05:22:59.674 E - select c4.cust_no
2025-06-24 05:22:59.680 E - from customer c4
2025-06-24 05:22:59.686 E - where c4.cust_no in
2025-06-24 05:22:59.692 E - (
2025-06-24 05:22:59.697 E - select s4.cust_no
2025-06-24 05:22:59.703 E - from sales s4
2025-06-24 05:22:59.708 E - where
2025-06-24 05:22:59.714 E - s4.paid > ''
2025-06-24 05:22:59.719 E - or
2025-06-24 05:22:59.725 E - s4.sales_rep in (
2025-06-24 05:22:59.731 E - select x.emp_no
2025-06-24 05:22:59.736 E - from employee x
2025-06-24 05:22:59.742 E - where
2025-06-24 05:22:59.747 E - x.dept_no > 0
2025-06-24 05:22:59.753 E - or
2025-06-24 05:22:59.758 E - x.job_code > ''
2025-06-24 05:22:59.764 E - )
2025-06-24 05:22:59.769 E - )
2025-06-24 05:22:59.775 E - Check unnesting of IN (NOTE: inner sub-query cannot be unnested due to OR condition present, but the outer sub-query *can*; see also bugs/gh_8265_test.py)
2025-06-24 05:22:59.780 E - Sub-query
2025-06-24 05:22:59.785 E - ....-> Filter
2025-06-24 05:22:59.791 E - ........-> Filter
2025-06-24 05:22:59.797 E - ............-> Table "EMPLOYEE" as "X" Access By ID
2025-06-24 05:22:59.802 E - ................-> Bitmap
2025-06-24 05:22:59.809 E - ....................-> Index "EMPLOYEE_PK" Unique Scan
2025-06-24 05:22:59.815 E - Select Expression
2025-06-24 05:22:59.823 E - ....-> Filter
2025-06-24 05:22:59.834 E - ........-> Hash Join (semi)
2025-06-24 05:22:59.841 E - ............-> Table "CUSTOMER" as "C4" Full Scan
2025-06-24 05:22:59.847 E - ............-> Record Buffer (record length: NN)
2025-06-24 05:22:59.854 E - ................-> Filter
2025-06-24 05:22:59.861 E - ....................-> Table "SALES" as "S4" Full Scan
2025-06-24 05:22:59.867
2025-06-24 05:22:59.874 tests/bugs/gh_8061_test.py:232: AssertionError
2025-06-24 05:22:59.881 ---------------------------- Captured stdout setup -----------------------------
2025-06-24 05:22:59.892 Creating db: localhost:/var/tmp/qa_2024/test_11667/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]>
tmp_sql = PosixPath('/var/tmp/qa_2024/test_11667/gh_8061.tmp.sql')
capsys = <_pytest.capture.CaptureFixture pytest object at [hex]>
@pytest.mark.version('>=5.0.1')
def test_1(act: Action, tmp_sql: Path, capsys):
employee_data_sql = zipfile.Path(act.files_dir / 'standard_sample_databases.zip', at='sample-DB_-_firebird.sql')
tmp_sql.write_bytes(employee_data_sql.read_bytes())
act.isql(switches = ['-q'], charset='utf8', input_file = tmp_sql, combine_output = True)
if act.return_code == 0:
srv_cfg = driver_config.register_server(name = f'srv_cfg_8061', config = '')
db_cfg_name = f'db_cfg_8061'
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 = cur.prepare(test_sql)
print(q_idx)
print(test_sql)
print(qry_comment)
print( '\n'.join([replace_leading(s) for s in ps.detailed_plan.split('\n')]) )
ps.free()
else:
# If retcode !=0 then we can print the whole output of failed gbak:
print('Initial script failed, check output:')
for line in act.clean_stdout.splitlines():
print(line)
act.reset()
act.expected_stdout = f"""
1000
{query_map[1000][0]}
{query_map[1000][1]}
Select Expression
....-> Filter
........-> Hash Join (semi) (keys: 1, total key length: 4)
............-> Table "CUSTOMER" as "C1" Full Scan
............-> Record Buffer (record length: 33)
................-> Filter
....................-> Table "SALES" as "S1" Full Scan
2000
{query_map[2000][0]}
{query_map[2000][1]}
Select Expression
....-> Filter
........-> Hash Join (semi) (keys: 1, total key length: 4)
............-> Table "CUSTOMER" as "C2" Full Scan
............-> Record Buffer (record length: 33)
................-> Filter
....................-> Table "SALES" as "S2" Full Scan
3000
{query_map[3000][0]}
{query_map[3000][1]}
Select Expression
....-> Filter
........-> Hash Join (semi) (keys: 1, total key length: 4)
............-> Table "CUSTOMER" as "C3" Full Scan
............-> Record Buffer (record length: 58)
................-> Filter
....................-> Hash Join (semi) (keys: 1, total key length: 2)
........................-> Table "SALES" as "S3" Full Scan
........................-> Record Buffer (record length: 41)
............................-> Filter
................................-> Table "EMPLOYEE" as "X" Full Scan
4000
{query_map[4000][0]}
{query_map[4000][1]}
Sub-query
....-> Filter
........-> Filter
............-> Table "EMPLOYEE" as "X" Access By ID
................-> Bitmap
....................-> Index "EMPLOYEE_PK" Unique Scan
Select Expression
....-> Filter
........-> Hash Join (semi) (keys: 1, total key length: 4)
............-> Table "CUSTOMER" as "C4" Full Scan
............-> Record Buffer (record length: 33)
................-> Filter
....................-> Table "SALES" as "S4" Full Scan
"""
act.stdout = capsys.readouterr().out
> assert act.clean_stdout == act.clean_expected_stdout
E assert
E + Initial script failed, check output:
E + Statement failed, SQLSTATE = HY000
E + ALTER CHARACTER SET "SYSTEM"."UTF8" failed
E + -Cannot CREATE/ALTER/DROP CHARACTER SET in SYSTEM schema
E + After line 16 in file /var/tmp/qa_2024/test_11667/gh_8061.tmp.sql
E - 1000
E - select c1.cust_no
E - from customer c1
E - where exists (
E - select 1 from sales s1 where s1.cust_no = c1.cust_no and s1.qty_ordered > 10
E - )
E - Check unnesting of single EXISTS
E - Select Expression
E - ....-> Filter
E - ........-> Hash Join (semi)
E - ............-> Table "CUSTOMER" as "C1" Full Scan
E - ............-> Record Buffer (record length: NN)
E - ................-> Filter
E - ....................-> Table "SALES" as "S1" Full Scan
E - 2000
E - select c2.cust_no
E - from customer c2
E - where c2.cust_no = any (
E - select s2.cust_no
E - from sales s2
E - where s2.qty_ordered > 10
E - )
E - Check unnesting of ANY
E - Select Expression
E - ....-> Filter
E - ........-> Hash Join (semi)
E - ............-> Table "CUSTOMER" as "C2" Full Scan
E - ............-> Record Buffer (record length: NN)
E - ................-> Filter
E - ....................-> Table "SALES" as "S2" Full Scan
E - 3000
E - select c3.cust_no
E - from customer c3
E - where exists (
E - select s3.cust_no
E - from sales s3
E - where s3.cust_no = c3.cust_no and
E - exists (
E - select x.emp_no
E - from employee x
E - where
E - x.emp_no = s3.sales_rep
E - and (
E - x.dept_no > 0
E - or
E - x.job_code > ''
E - )
E - )
E - )
E - Check unnesting of two nested EXISTS
E - Select Expression
E - ....-> Filter
E - ........-> Hash Join (semi)
E - ............-> Table "CUSTOMER" as "C3" Full Scan
E - ............-> Record Buffer (record length: NN)
E - ................-> Filter
E - ....................-> Hash Join (semi)
E - ........................-> Table "SALES" as "S3" Full Scan
E - ........................-> Record Buffer (record length: NN)
E - ............................-> Filter
E - ................................-> Table "EMPLOYEE" as "X" Full Scan
E - 4000
E - select c4.cust_no
E - from customer c4
E - where c4.cust_no in
E - (
E - select s4.cust_no
E - from sales s4
E - where
E - s4.paid > ''
E - or
E - s4.sales_rep in (
E - select x.emp_no
E - from employee x
E - where
E - x.dept_no > 0
E - or
E - x.job_code > ''
E - )
E - )
E - Check unnesting of IN (NOTE: inner sub-query cannot be unnested due to OR condition present, but the outer sub-query *can*; see also bugs/gh_8265_test.py)
E - Sub-query
E - ....-> Filter
E - ........-> Filter
E - ............-> Table "EMPLOYEE" as "X" Access By ID
E - ................-> Bitmap
E - ....................-> Index "EMPLOYEE_PK" Unique Scan
E - Select Expression
E - ....-> Filter
E - ........-> Hash Join (semi)
E - ............-> Table "CUSTOMER" as "C4" Full Scan
E - ............-> Record Buffer (record length: NN)
E - ................-> Filter
E - ....................-> Table "SALES" as "S4" Full Scan
tests/bugs/gh_8061_test.py:232: AssertionError
|