MySQL Bugs: #112194: Incorrect result when using INNER JOIN with SELECT FROM VIEW together with OR co

Bug #112194	Incorrect result when using INNER JOIN with SELECT FROM VIEW together with OR co
Submitted:	28 Aug 2023 5:31	Modified:	6 Sep 2023 9:03
Reporter:	IVAN HO	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.34	OS:	Red Hat
Assigned to:		CPU Architecture:	Any

Description:
- SQL1 & SQL3 return correct results
- SQL2 return wrong results
- Testing environment is static, there is no data changes.

# SQL-1 [ SELECT FROM VIEW ]
# Result => Correct

select a.emp_id, a.emp_name, a.salary, b.dept_name
from emp a
inner join dept b on (b.dept_id in (select dept_id from vw_emp where a.dept_id=b.dept_id and a.salary > 2000))
where a.emp_id=3
order by emp_id
;

# SQL-2 [ SELECT FROM VIEW and adding "OR (FALSE)" in the INNER JOIN ]
# Result => Wrong

select a.emp_id, a.emp_name, a.salary, b.dept_name
from emp a
inner join dept b on (b.dept_id in (select dept_id from vw_emp where a.dept_id=b.dept_id and a.salary > 2000) OR (FALSE))
where a.emp_id=3
order by emp_id
;

# SQL-3 [ SELECT FROM TABLE and adding "OR (FALSE)" in the INNER JOIN ]
# Result => Correct

select a.emp_id, a.emp_name, a.salary, b.dept_name
from emp a
inner join dept b on (b.dept_id in (select dept_id from emp where a.dept_id=b.dept_id and a.salary > 2000) OR (FALSE))
where a.emp_id=3
order by emp_id
;

# SQL-1 result
+--------+----------+--------+-----------+
| emp_id | emp_name | salary | dept_name |
+--------+----------+--------+-----------+
|      3 | emp_C    |   3000 | dept_C    |
+--------+----------+--------+-----------+

# SQL-2 result
+--------+----------+--------+-----------+
| emp_id | emp_name | salary | dept_name |
+--------+----------+--------+-----------+
|      3 | emp_C    |   3000 | dept_A    |
|      3 | emp_C    |   3000 | dept_B    |
|      3 | emp_C    |   3000 | dept_C    |
+--------+----------+--------+-----------+

# SQL-3 result
+--------+----------+--------+-----------+
| emp_id | emp_name | salary | dept_name |
+--------+----------+--------+-----------+
|      3 | emp_C    |   3000 | dept_C    |
+--------+----------+--------+-----------+

# [ Conclusion ]
Incorrect result when using INNER JOIN with SELECT FROM VIEW together with OR condition

How to repeat:
Run "testcase.sql" on MySQL 8.0.34.

test case

Attachment: testcase.sql (application/octet-stream, text), 801 bytes.

Hello IVAN HO,

Thank you for the report and test case.
I'm not seeing any issues on 8.0.34/5.7 builds, am I missing anything here? Please let us know. Thank you.

 bin/mysql -uroot -S /tmp/mysql.sock -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.34 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
mysql> drop database if exists testcase;
mysql> create database testcase ;
Query OK, 1 row affected (0.01 sec)

mysql> use testcase ;
Database changed
mysql>
mysql>
mysql> drop table if exists emp;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table emp (emp_id int, emp_name varchar(100), salary int, dept_id int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into emp values
    -> (1, 'emp_A', 1000, 10),
    -> (2, 'emp_B', 2000, 20),
    -> (3, 'emp_C', 3000, 30),
    -> (4, 'emp_D', 2000, 10),
    -> (5, 'emp_E', 3000, 20);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> # Create simple view by copying table "emp"
mysql> drop view if exists vw_emp ;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE VIEW `vw_emp` AS
    -> select emp_id, emp_name, salary, dept_id from emp;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>
mysql> drop table if exists dept;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table dept (dept_id int, dept_name varchar(100));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into dept values
    -> (10, 'dept_A'),
    -> (20, 'dept_B'),
    -> (30, 'dept_C'),
    -> (40, 'dept_D'),
    -> (50, 'dept_E'),
    -> (60, 'dept_F');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select a.emp_id, a.emp_name, a.salary, b.dept_name
    -> from emp a
    -> inner join dept b on (b.dept_id in (select dept_id from vw_emp where a.dept_id=b.dept_id and a.salary > 2000))
    -> where a.emp_id=3
    -> order by emp_id
    -> ;
+--------+----------+--------+-----------+
| emp_id | emp_name | salary | dept_name |
+--------+----------+--------+-----------+
|      3 | emp_C    |   3000 | dept_C    |
+--------+----------+--------+-----------+
1 row in set (0.00 sec)

mysql> select a.emp_id, a.emp_name, a.salary, b.dept_name
    -> from emp a
    -> inner join dept b on (b.dept_id in (select dept_id from vw_emp where a.dept_id=b.dept_id and a.salary > 2000) OR (FALSE))
    -> where a.emp_id=3
    -> order by emp_id
    -> ;
+--------+----------+--------+-----------+
| emp_id | emp_name | salary | dept_name |
+--------+----------+--------+-----------+
|      3 | emp_C    |   3000 | dept_C    |
+--------+----------+--------+-----------+
1 row in set (0.00 sec)

mysql> select a.emp_id, a.emp_name, a.salary, b.dept_name
    -> from emp a
    -> inner join dept b on (b.dept_id in (select dept_id from emp where a.dept_id=b.dept_id and a.salary > 2000) OR (FALSE))
    -> where a.emp_id=3
    -> order by emp_id
    -> ;
+--------+----------+--------+-----------+
| emp_id | emp_name | salary | dept_name |
+--------+----------+--------+-----------+
|      3 | emp_C    |   3000 | dept_C    |
+--------+----------+--------+-----------+
1 row in set (0.00 sec)

mysql>

regards,
Umesh

We have some new findings. The problem might be related to "optimizer_switch". 
In our environment, we use "optimizer_switch = derived_merge=off".

[ Case 1 ]
Add below parameter to my.cnf.
optimizer_switch                = derived_merge=off

mysql> select @@global.optimizer_switch\G
*************************** 1. row ***************************
@@global.optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=off,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on
1 row in set (0.00 sec)

Result :
- All 3 SQL produce the same output, ie. return 1 row.

[ Case 2 ]
Use default optimizer_switch setting.

mysql> select @@global.optimizer_switch\G
*************************** 1. row ***************************
@@global.optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on
1 row in set (0.00 sec)

Result :
- The 1st & 3rd SQL produce the same output, ie. return 1 row.
- The 2nd SQL produces WRONG output, ie. return 3 rows.

Thank you for the feedback.
Verified as described.

regards,
Umesh

Short Summary :

# Case-1
MySQL 8.0.34
optimizer_switch = derived_merge=off
SQL-1 return rows => 1
SQL-2 return rows => 3
SQL-3 return rows => 1

# Case-2
MySQL 8.0.34
Use default value of "optimizer_switch"
SQL-1 return rows => 1
SQL-2 return rows => 1
SQL-3 return rows => 1

# Case-3
MySQL 8.0.33
optimizer_switch = derived_merge=off
SQL-1 return rows => 1
SQL-2 return rows => 3
SQL-3 return rows => 1

# Case-4
MySQL 8.0.33
Use default value of "optimizer_switch"
SQL-1 return rows => 1
SQL-2 return rows => 1
SQL-3 return rows => 1