2 @message |
assert
+ Initial script failed, check output:
+ Statement failed, SQLSTATE = 22021
+ unsuccessful metadata update
+ -ALTER CHARACTER SET "SYSTEM"."UTF8" failed
+ -COLLATION "SYSTEM"."CI_COLL" for CHARACTER SET "SYSTEM"."UTF8" is not defined
+ After line 8 in file H:\QA\temp\qa2024.tmp\fbqa\test_11697\gh_8061.tmp.sql
- 1000
- 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.job_country = c3.country
- )
- )
- Subqueries that are correlated to non-parent; for example,
- subquery SQ3 is contained by SQ2 (parent of SQ3) and SQ2 in turn is contained
- by SQ1 and SQ3 is correlated to tables defined in SQ1.
- Sub-query
- ....-> Filter
- ........-> Table "EMPLOYEE" as "X" Full Scan
- Sub-query
- ....-> Filter (preliminary)
- ........-> Filter
- ............-> Table "SALES" as "S3" Access By ID
- ................-> Bitmap
- ....................-> Index "SALES_CUSTOMER_FK_CUST_NO" Range Scan (full match)
- Select Expression
- ....-> Filter
- ........-> Table "CUSTOMER" as "C3" Full Scan
- 2000
- select c3.cust_no
- from customer c3
- where exists (
- select s3.cust_no
- from sales s3
- where s3.cust_no = c3.cust_no
- group by s3.cust_no
- )
- A group-by subquery is correlated; in this case, unnesting implies doing join
- after group-by. Changing the given order of the two operations may not be always legal.
- Sub-query
- ....-> Aggregate
- ........-> Filter
- ............-> Table "SALES" as "S3" Access By ID
- ................-> Index "SALES_CUSTOMER_FK_CUST_NO" Range Scan (full match)
- Select Expression
- ....-> Filter
- ........-> Table "CUSTOMER" as "C3" Full Scan
- 3000
- select s1.cust_no
- from sales s1
- where exists (
- select 1 from customer c1 where s1.cust_no = c1.cust_no
- union all
- select 1 from employee x1 where s1.sales_rep = x1.emp_no
- )
- For disjunctive subqueries, the outer columns in the connecting
- or correlating conditions are not the same.
- Sub-query
- ....-> Union
- ........-> Filter
- ............-> Table "CUSTOMER" as "C1" Access By ID
- ................-> Bitmap
- ....................-> Index "CUSTOMER_PK" Unique Scan
- ........-> Filter
- ............-> Table "EMPLOYEE" as "X1" Access By ID
- ................-> Bitmap
- ....................-> Index "EMPLOYEE_PK" Unique Scan
- Select Expression
- ....-> Filter
- ........-> Table "SALES" as "S1" Full Scan
- 4000
- select x1.emp_no
- from employee x1
- where
- (
- x1.job_country = 'USA' or
- exists (
- select 1
- from sales s1
- where s1.sales_rep = x1.emp_no
- )
- )
- An `OR` condition in compound WHERE expression, see https://jonathanlewis.wordpress.com/2007/02/26/subquery-with-or/
- Sub-query
- ....-> Filter
- ........-> Table "SALES" as "S1" Access By ID
- ............-> Bitmap
- ................-> Index "SALES_EMPLOYEE_FK_SALES_REP" Range Scan (full match)
- Select Expression
- ....-> Filter
- ........-> Table "EMPLOYEE" as "X1" Full Scan
LOG DETAILS:
2025-07-02 06:41:20.213
2025-07-02 06:41:20.213 act = <firebird.qa.plugin.Action object at [hex]>
2025-07-02 06:41:20.214 tmp_sql = WindowsPath('H:/QA/temp/qa2024.tmp/fbqa/test_11697/gh_8061.tmp.sql')
2025-07-02 06:41:20.214 capsys = <_pytest.capture.CaptureFixture object at [hex]>
2025-07-02 06:41:20.214
2025-07-02 06:41:20.214 @pytest.mark.version('>=5.0.1')
2025-07-02 06:41:20.214 def test_1(act: Action, tmp_sql: Path, capsys):
2025-07-02 06:41:20.214 employee_data_sql = zipfile.Path(act.files_dir / 'standard_sample_databases.zip', at='sample-DB_-_firebird.sql')
2025-07-02 06:41:20.214 tmp_sql.write_bytes(employee_data_sql.read_bytes())
2025-07-02 06:41:20.214
2025-07-02 06:41:20.214 act.isql(switches = ['-q'], charset='utf8', input_file = tmp_sql, combine_output = True)
2025-07-02 06:41:20.214
2025-07-02 06:41:20.214 if act.return_code == 0:
2025-07-02 06:41:20.214
2025-07-02 06:41:20.214 srv_cfg = driver_config.register_server(name = f'srv_cfg_8061_addi', config = '')
2025-07-02 06:41:20.214 db_cfg_name = f'db_cfg_8061_addi'
2025-07-02 06:41:20.214 db_cfg_object = driver_config.register_database(name = db_cfg_name)
2025-07-02 06:41:20.214 db_cfg_object.server.value = srv_cfg.name
2025-07-02 06:41:20.214 db_cfg_object.database.value = str(act.db.db_path)
2025-07-02 06:41:20.214 if act.is_version('<6'):
2025-07-02 06:41:20.214 db_cfg_object.config.value = f"""
2025-07-02 06:41:20.215 SubQueryConversion = true
2025-07-02 06:41:20.215 """
2025-07-02 06:41:20.215
2025-07-02 06:41:20.215 with connect(db_cfg_name, user = act.db.user, password = act.db.password) as con:
2025-07-02 06:41:20.215 cur = con.cursor()
2025-07-02 06:41:20.215 for q_idx, q_tuple in query_map.items():
2025-07-02 06:41:20.215 test_sql, qry_comment = q_tuple[:2]
2025-07-02 06:41:20.215 ps = cur.prepare(test_sql)
2025-07-02 06:41:20.215 print(q_idx)
2025-07-02 06:41:20.215 print(test_sql)
2025-07-02 06:41:20.215 print(qry_comment)
2025-07-02 06:41:20.215 print( '\n'.join([replace_leading(s) for s in ps.detailed_plan.split('\n')]) )
2025-07-02 06:41:20.215 ps.free()
2025-07-02 06:41:20.215
2025-07-02 06:41:20.215 else:
2025-07-02 06:41:20.215 # If retcode !=0 then we can print the whole output of failed gbak:
2025-07-02 06:41:20.215 print('Initial script failed, check output:')
2025-07-02 06:41:20.215 for line in act.clean_stdout.splitlines():
2025-07-02 06:41:20.215 print(line)
2025-07-02 06:41:20.216 act.reset()
2025-07-02 06:41:20.216
2025-07-02 06:41:20.216 act.expected_stdout = f"""
2025-07-02 06:41:20.216 1000
2025-07-02 06:41:20.216 {query_map[1000][0]}
2025-07-02 06:41:20.216 {query_map[1000][1]}
2025-07-02 06:41:20.216 Sub-query
2025-07-02 06:41:20.216 ....-> Filter
2025-07-02 06:41:20.216 ........-> Table "EMPLOYEE" as "X" Full Scan
2025-07-02 06:41:20.216 Sub-query
2025-07-02 06:41:20.216 ....-> Filter (preliminary)
2025-07-02 06:41:20.216 ........-> Filter
2025-07-02 06:41:20.216 ............-> Table "SALES" as "S3" Access By ID
2025-07-02 06:41:20.216 ................-> Bitmap
2025-07-02 06:41:20.216 ....................-> Index "SALES_CUSTOMER_FK_CUST_NO" Range Scan (full match)
2025-07-02 06:41:20.216 Select Expression
2025-07-02 06:41:20.216 ....-> Filter
2025-07-02 06:41:20.216 ........-> Table "CUSTOMER" as "C3" Full Scan
2025-07-02 06:41:20.216
2025-07-02 06:41:20.216 2000
2025-07-02 06:41:20.217 {query_map[2000][0]}
2025-07-02 06:41:20.217 {query_map[2000][1]}
2025-07-02 06:41:20.217 Sub-query
2025-07-02 06:41:20.217 ....-> Aggregate
2025-07-02 06:41:20.217 ........-> Filter
2025-07-02 06:41:20.217 ............-> Table "SALES" as "S3" Access By ID
2025-07-02 06:41:20.217 ................-> Index "SALES_CUSTOMER_FK_CUST_NO" Range Scan (full match)
2025-07-02 06:41:20.217 Select Expression
2025-07-02 06:41:20.217 ....-> Filter
2025-07-02 06:41:20.217 ........-> Table "CUSTOMER" as "C3" Full Scan
2025-07-02 06:41:20.217
2025-07-02 06:41:20.217 3000
2025-07-02 06:41:20.217 {query_map[3000][0]}
2025-07-02 06:41:20.217 {query_map[3000][1]}
2025-07-02 06:41:20.217 Sub-query
2025-07-02 06:41:20.217 ....-> Union
2025-07-02 06:41:20.217 ........-> Filter
2025-07-02 06:41:20.217 ............-> Table "CUSTOMER" as "C1" Access By ID
2025-07-02 06:41:20.217 ................-> Bitmap
2025-07-02 06:41:20.217 ....................-> Index "CUSTOMER_PK" Unique Scan
2025-07-02 06:41:20.218 ........-> Filter
2025-07-02 06:41:20.218 ............-> Table "EMPLOYEE" as "X1" Access By ID
2025-07-02 06:41:20.218 ................-> Bitmap
2025-07-02 06:41:20.218 ....................-> Index "EMPLOYEE_PK" Unique Scan
2025-07-02 06:41:20.218 Select Expression
2025-07-02 06:41:20.218 ....-> Filter
2025-07-02 06:41:20.218 ........-> Table "SALES" as "S1" Full Scan
2025-07-02 06:41:20.218
2025-07-02 06:41:20.218 4000
2025-07-02 06:41:20.218 {query_map[4000][0]}
2025-07-02 06:41:20.218 {query_map[4000][1]}
2025-07-02 06:41:20.218 Sub-query
2025-07-02 06:41:20.218 ....-> Filter
2025-07-02 06:41:20.218 ........-> Table "SALES" as "S1" Access By ID
2025-07-02 06:41:20.218 ............-> Bitmap
2025-07-02 06:41:20.218 ................-> Index "SALES_EMPLOYEE_FK_SALES_REP" Range Scan (full match)
2025-07-02 06:41:20.218 Select Expression
2025-07-02 06:41:20.218 ....-> Filter
2025-07-02 06:41:20.218 ........-> Table "EMPLOYEE" as "X1" Full Scan
2025-07-02 06:41:20.218 """
2025-07-02 06:41:20.218 act.stdout = capsys.readouterr().out
2025-07-02 06:41:20.219 > assert act.clean_stdout == act.clean_expected_stdout
2025-07-02 06:41:20.219 E assert
2025-07-02 06:41:20.219 E + Initial script failed, check output:
2025-07-02 06:41:20.219 E + Statement failed, SQLSTATE = 22021
2025-07-02 06:41:20.219 E + unsuccessful metadata update
2025-07-02 06:41:20.219 E + -ALTER CHARACTER SET "SYSTEM"."UTF8" failed
2025-07-02 06:41:20.219 E + -COLLATION "SYSTEM"."CI_COLL" for CHARACTER SET "SYSTEM"."UTF8" is not defined
2025-07-02 06:41:20.219 E + After line 8 in file H:\QA\temp\qa2024.tmp\fbqa\test_11697\gh_8061.tmp.sql
2025-07-02 06:41:20.219 E - 1000
2025-07-02 06:41:20.219 E - select c3.cust_no
2025-07-02 06:41:20.219 E - from customer c3
2025-07-02 06:41:20.219 E - where exists (
2025-07-02 06:41:20.219 E - select s3.cust_no
2025-07-02 06:41:20.219 E - from sales s3
2025-07-02 06:41:20.219 E - where s3.cust_no = c3.cust_no and
2025-07-02 06:41:20.219 E - exists (
2025-07-02 06:41:20.219 E - select x.emp_no
2025-07-02 06:41:20.219 E - from employee x
2025-07-02 06:41:20.220 E - where
2025-07-02 06:41:20.220 E - x.job_country = c3.country
2025-07-02 06:41:20.220 E - )
2025-07-02 06:41:20.220 E - )
2025-07-02 06:41:20.220 E - Subqueries that are correlated to non-parent; for example,
2025-07-02 06:41:20.220 E - subquery SQ3 is contained by SQ2 (parent of SQ3) and SQ2 in turn is contained
2025-07-02 06:41:20.220 E - by SQ1 and SQ3 is correlated to tables defined in SQ1.
2025-07-02 06:41:20.220 E - Sub-query
2025-07-02 06:41:20.220 E - ....-> Filter
2025-07-02 06:41:20.220 E - ........-> Table "EMPLOYEE" as "X" Full Scan
2025-07-02 06:41:20.220 E - Sub-query
2025-07-02 06:41:20.220 E - ....-> Filter (preliminary)
2025-07-02 06:41:20.220 E - ........-> Filter
2025-07-02 06:41:20.220 E - ............-> Table "SALES" as "S3" Access By ID
2025-07-02 06:41:20.220 E - ................-> Bitmap
2025-07-02 06:41:20.220 E - ....................-> Index "SALES_CUSTOMER_FK_CUST_NO" Range Scan (full match)
2025-07-02 06:41:20.220 E - Select Expression
2025-07-02 06:41:20.220 E - ....-> Filter
2025-07-02 06:41:20.220 E - ........-> Table "CUSTOMER" as "C3" Full Scan
2025-07-02 06:41:20.221 E - 2000
2025-07-02 06:41:20.221 E - select c3.cust_no
2025-07-02 06:41:20.221 E - from customer c3
2025-07-02 06:41:20.221 E - where exists (
2025-07-02 06:41:20.221 E - select s3.cust_no
2025-07-02 06:41:20.221 E - from sales s3
2025-07-02 06:41:20.221 E - where s3.cust_no = c3.cust_no
2025-07-02 06:41:20.221 E - group by s3.cust_no
2025-07-02 06:41:20.221 E - )
2025-07-02 06:41:20.221 E - A group-by subquery is correlated; in this case, unnesting implies doing join
2025-07-02 06:41:20.221 E - after group-by. Changing the given order of the two operations may not be always legal.
2025-07-02 06:41:20.221 E - Sub-query
2025-07-02 06:41:20.221 E - ....-> Aggregate
2025-07-02 06:41:20.221 E - ........-> Filter
2025-07-02 06:41:20.221 E - ............-> Table "SALES" as "S3" Access By ID
2025-07-02 06:41:20.221 E - ................-> Index "SALES_CUSTOMER_FK_CUST_NO" Range Scan (full match)
2025-07-02 06:41:20.221 E - Select Expression
2025-07-02 06:41:20.221 E - ....-> Filter
2025-07-02 06:41:20.221 E - ........-> Table "CUSTOMER" as "C3" Full Scan
2025-07-02 06:41:20.221 E - 3000
2025-07-02 06:41:20.222 E - select s1.cust_no
2025-07-02 06:41:20.222 E - from sales s1
2025-07-02 06:41:20.222 E - where exists (
2025-07-02 06:41:20.222 E - select 1 from customer c1 where s1.cust_no = c1.cust_no
2025-07-02 06:41:20.222 E - union all
2025-07-02 06:41:20.222 E - select 1 from employee x1 where s1.sales_rep = x1.emp_no
2025-07-02 06:41:20.222 E - )
2025-07-02 06:41:20.222 E - For disjunctive subqueries, the outer columns in the connecting
2025-07-02 06:41:20.222 E - or correlating conditions are not the same.
2025-07-02 06:41:20.222 E - Sub-query
2025-07-02 06:41:20.222 E - ....-> Union
2025-07-02 06:41:20.222 E - ........-> Filter
2025-07-02 06:41:20.222 E - ............-> Table "CUSTOMER" as "C1" Access By ID
2025-07-02 06:41:20.222 E - ................-> Bitmap
2025-07-02 06:41:20.222 E - ....................-> Index "CUSTOMER_PK" Unique Scan
2025-07-02 06:41:20.222 E - ........-> Filter
2025-07-02 06:41:20.222 E - ............-> Table "EMPLOYEE" as "X1" Access By ID
2025-07-02 06:41:20.222 E - ................-> Bitmap
2025-07-02 06:41:20.222 E - ....................-> Index "EMPLOYEE_PK" Unique Scan
2025-07-02 06:41:20.223 E - Select Expression
2025-07-02 06:41:20.223 E - ....-> Filter
2025-07-02 06:41:20.223 E - ........-> Table "SALES" as "S1" Full Scan
2025-07-02 06:41:20.223 E - 4000
2025-07-02 06:41:20.223 E - select x1.emp_no
2025-07-02 06:41:20.223 E - from employee x1
2025-07-02 06:41:20.223 E - where
2025-07-02 06:41:20.223 E - (
2025-07-02 06:41:20.223 E - x1.job_country = 'USA' or
2025-07-02 06:41:20.223 E - exists (
2025-07-02 06:41:20.223 E - select 1
2025-07-02 06:41:20.223 E - from sales s1
2025-07-02 06:41:20.223 E - where s1.sales_rep = x1.emp_no
2025-07-02 06:41:20.223 E - )
2025-07-02 06:41:20.223 E - )
2025-07-02 06:41:20.223 E - An `OR` condition in compound WHERE expression, see https://jonathanlewis.wordpress.com/2007/02/26/subquery-with-or/
2025-07-02 06:41:20.223 E - Sub-query
2025-07-02 06:41:20.223 E - ....-> Filter
2025-07-02 06:41:20.224 E - ........-> Table "SALES" as "S1" Access By ID
2025-07-02 06:41:20.224 E - ............-> Bitmap
2025-07-02 06:41:20.224 E - ................-> Index "SALES_EMPLOYEE_FK_SALES_REP" Range Scan (full match)
2025-07-02 06:41:20.224 E - Select Expression
2025-07-02 06:41:20.224 E - ....-> Filter
2025-07-02 06:41:20.224 E - ........-> Table "EMPLOYEE" as "X1" Full Scan
2025-07-02 06:41:20.224
2025-07-02 06:41:20.224 tests\bugs\gh_8061_addi_test.py:225: AssertionError
2025-07-02 06:41:20.224 ---------------------------- Captured stdout setup ----------------------------
2025-07-02 06:41:20.224 Creating db: localhost:H:\QA\temp\qa2024.tmp\fbqa\test_11697\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 = WindowsPath('H:/QA/temp/qa2024.tmp/fbqa/test_11697/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_addi', config = '')
db_cfg_name = f'db_cfg_8061_addi'
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]}
Sub-query
....-> Filter
........-> Table "EMPLOYEE" as "X" Full Scan
Sub-query
....-> Filter (preliminary)
........-> Filter
............-> Table "SALES" as "S3" Access By ID
................-> Bitmap
....................-> Index "SALES_CUSTOMER_FK_CUST_NO" Range Scan (full match)
Select Expression
....-> Filter
........-> Table "CUSTOMER" as "C3" Full Scan
2000
{query_map[2000][0]}
{query_map[2000][1]}
Sub-query
....-> Aggregate
........-> Filter
............-> Table "SALES" as "S3" Access By ID
................-> Index "SALES_CUSTOMER_FK_CUST_NO" Range Scan (full match)
Select Expression
....-> Filter
........-> Table "CUSTOMER" as "C3" Full Scan
3000
{query_map[3000][0]}
{query_map[3000][1]}
Sub-query
....-> Union
........-> Filter
............-> Table "CUSTOMER" as "C1" Access By ID
................-> Bitmap
....................-> Index "CUSTOMER_PK" Unique Scan
........-> Filter
............-> Table "EMPLOYEE" as "X1" Access By ID
................-> Bitmap
....................-> Index "EMPLOYEE_PK" Unique Scan
Select Expression
....-> Filter
........-> Table "SALES" as "S1" Full Scan
4000
{query_map[4000][0]}
{query_map[4000][1]}
Sub-query
....-> Filter
........-> Table "SALES" as "S1" Access By ID
............-> Bitmap
................-> Index "SALES_EMPLOYEE_FK_SALES_REP" Range Scan (full match)
Select Expression
....-> Filter
........-> Table "EMPLOYEE" as "X1" 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 = 22021
E + unsuccessful metadata update
E + -ALTER CHARACTER SET "SYSTEM"."UTF8" failed
E + -COLLATION "SYSTEM"."CI_COLL" for CHARACTER SET "SYSTEM"."UTF8" is not defined
E + After line 8 in file H:\QA\temp\qa2024.tmp\fbqa\test_11697\gh_8061.tmp.sql
E - 1000
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.job_country = c3.country
E - )
E - )
E - Subqueries that are correlated to non-parent; for example,
E - subquery SQ3 is contained by SQ2 (parent of SQ3) and SQ2 in turn is contained
E - by SQ1 and SQ3 is correlated to tables defined in SQ1.
E - Sub-query
E - ....-> Filter
E - ........-> Table "EMPLOYEE" as "X" Full Scan
E - Sub-query
E - ....-> Filter (preliminary)
E - ........-> Filter
E - ............-> Table "SALES" as "S3" Access By ID
E - ................-> Bitmap
E - ....................-> Index "SALES_CUSTOMER_FK_CUST_NO" Range Scan (full match)
E - Select Expression
E - ....-> Filter
E - ........-> Table "CUSTOMER" as "C3" Full Scan
E - 2000
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
E - group by s3.cust_no
E - )
E - A group-by subquery is correlated; in this case, unnesting implies doing join
E - after group-by. Changing the given order of the two operations may not be always legal.
E - Sub-query
E - ....-> Aggregate
E - ........-> Filter
E - ............-> Table "SALES" as "S3" Access By ID
E - ................-> Index "SALES_CUSTOMER_FK_CUST_NO" Range Scan (full match)
E - Select Expression
E - ....-> Filter
E - ........-> Table "CUSTOMER" as "C3" Full Scan
E - 3000
E - select s1.cust_no
E - from sales s1
E - where exists (
E - select 1 from customer c1 where s1.cust_no = c1.cust_no
E - union all
E - select 1 from employee x1 where s1.sales_rep = x1.emp_no
E - )
E - For disjunctive subqueries, the outer columns in the connecting
E - or correlating conditions are not the same.
E - Sub-query
E - ....-> Union
E - ........-> Filter
E - ............-> Table "CUSTOMER" as "C1" Access By ID
E - ................-> Bitmap
E - ....................-> Index "CUSTOMER_PK" Unique Scan
E - ........-> Filter
E - ............-> Table "EMPLOYEE" as "X1" Access By ID
E - ................-> Bitmap
E - ....................-> Index "EMPLOYEE_PK" Unique Scan
E - Select Expression
E - ....-> Filter
E - ........-> Table "SALES" as "S1" Full Scan
E - 4000
E - select x1.emp_no
E - from employee x1
E - where
E - (
E - x1.job_country = 'USA' or
E - exists (
E - select 1
E - from sales s1
E - where s1.sales_rep = x1.emp_no
E - )
E - )
E - An `OR` condition in compound WHERE expression, see https://jonathanlewis.wordpress.com/2007/02/26/subquery-with-or/
E - Sub-query
E - ....-> Filter
E - ........-> Table "SALES" as "S1" Access By ID
E - ............-> Bitmap
E - ................-> Index "SALES_EMPLOYEE_FK_SALES_REP" Range Scan (full match)
E - Select Expression
E - ....-> Filter
E - ........-> Table "EMPLOYEE" as "X1" Full Scan
tests\bugs\gh_8061_addi_test.py:225: AssertionError
|