Bug #117911 INTERSECT operation return wrong result
Submitted: 8 Apr 12:06 Modified: 8 Apr 14:36
Reporter: jinhui lai Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:9.2.0 OS:Ubuntu (22.04)
Assigned to: CPU Architecture:Any
Tags: regression

[8 Apr 12:06] jinhui lai 
Description:
INTERSECT operation return wrong result!

How to repeat:
docker run -it --name mysql-latest -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql:latest
CREATE TABLE IF NOT EXISTS t0(c0 INT) ;
CREATE TABLE IF NOT EXISTS t1(c1 INT PRIMARY KEY) ;
INSERT INTO t0(c0) VALUES(NULL);
INSERT INTO t1(c1) VALUES(1);
SELECT DISTINCT * FROM t1 LEFT JOIN t0 ON t1.c1 = t0.c0;
+----+------+
| c1 | c0   |
+----+------+
|  1 | NULL |
+----+------+
1 row in set (0.01 sec)
SELECT DISTINCT * FROM t1 RIGHT JOIN t0 ON t1.c1 = t0.c0;
+------+------+
| c1   | c0   |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.00 sec)
SELECT DISTINCT * FROM t1 INNER JOIN t0 ON t1.c1 = t0.c0;
Empty set (0.00 sec)
SELECT DISTINCT * FROM t1 LEFT JOIN t0 ON t1.c1 = t0.c0 INTERSECT SELECT DISTINCT * FROM t1 RIGHT JOIN t0 ON t1.c1 = t0.c0; -- wrong result
+----+------+
| c1 | c0   |
+----+------+
|  1 | NULL |
+----+------+
1 row in set (0.00 sec)
[8 Apr 14:36] MySQL Verification Team 
Hello jinhui lai,

Thank you for the report and test case.
I'm not seeing any issues in 8.0.41/8.4.4 but only on 9.2.0. Please confirm.

-- 8.0.41

./mtr --nocheck-testcases bug117911
Logging: ./mtr  --nocheck-testcases bug117911
MySQL Version 8.0.41
Checking supported features
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
Creating var directory '/export/home/tmp/ushastry/work/binaries/mysql-8.0.41/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS t0(c0 INT) ;
CREATE TABLE IF NOT EXISTS t1(c1 INT PRIMARY KEY) ;
INSERT INTO t0(c0) VALUES(NULL);
INSERT INTO t1(c1) VALUES(1);
SELECT DISTINCT * FROM t1 LEFT JOIN t0 ON t1.c1 = t0.c0;
c1	c0
1	NULL
SELECT DISTINCT * FROM t1 RIGHT JOIN t0 ON t1.c1 = t0.c0;
c1	c0
NULL	NULL
SELECT DISTINCT * FROM t1 INNER JOIN t0 ON t1.c1 = t0.c0;
c1	c0
SELECT DISTINCT * FROM t1 LEFT JOIN t0 ON t1.c1 = t0.c0 INTERSECT SELECT DISTINCT * FROM t1 RIGHT JOIN t0 ON t1.c1 = t0.c0;
c1	c0
[ 50%] main.bug117911                            [ pass ]    134
[100%] shutdown_report                           [ pass ]

-- 8.4.4

./mtr --nocheck-testcases bug117911
Logging: ./mtr  --nocheck-testcases bug117911
MySQL Version 8.4.4
Checking supported features
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
Creating var directory '/export/home/tmp/ushastry/work/binaries/mysql-8.4.4/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS t0(c0 INT) ;
CREATE TABLE IF NOT EXISTS t1(c1 INT PRIMARY KEY) ;
INSERT INTO t0(c0) VALUES(NULL);
INSERT INTO t1(c1) VALUES(1);
SELECT DISTINCT * FROM t1 LEFT JOIN t0 ON t1.c1 = t0.c0;
c1	c0
1	NULL
SELECT DISTINCT * FROM t1 RIGHT JOIN t0 ON t1.c1 = t0.c0;
c1	c0
NULL	NULL
SELECT DISTINCT * FROM t1 INNER JOIN t0 ON t1.c1 = t0.c0;
c1	c0
SELECT DISTINCT * FROM t1 LEFT JOIN t0 ON t1.c1 = t0.c0 INTERSECT SELECT DISTINCT * FROM t1 RIGHT JOIN t0 ON t1.c1 = t0.c0;
c1	c0
[ 50%] main.bug117911                            [ pass ]     67
[100%] shutdown_report                           [ pass ]
------------------------------------------------------------------------------
The servers were restarted 0 times
The servers were reinitialized 0 times
Spent 0.067 of 9 seconds executing testcases

-- 9.2.0

./mtr --nocheck-testcases bug117911
Logging: ./mtr  --nocheck-testcases bug117911
MySQL Version 9.2.0
Checking supported features
Using 'all' suites
Collecting tests
Checking leftover processes
Removing old var directory
Creating var directory '/export/home/tmp/ushastry/work/binaries/mysql-9.2.0/mysql-test/var'
Installing system database
Using parallel: 1

==============================================================================
                  TEST NAME                       RESULT  TIME (ms) COMMENT
------------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS t0(c0 INT) ;
CREATE TABLE IF NOT EXISTS t1(c1 INT PRIMARY KEY) ;
INSERT INTO t0(c0) VALUES(NULL);
INSERT INTO t1(c1) VALUES(1);
SELECT DISTINCT * FROM t1 LEFT JOIN t0 ON t1.c1 = t0.c0;
c1	c0
1	NULL
SELECT DISTINCT * FROM t1 RIGHT JOIN t0 ON t1.c1 = t0.c0;
c1	c0
NULL	NULL
SELECT DISTINCT * FROM t1 INNER JOIN t0 ON t1.c1 = t0.c0;
c1	c0
SELECT DISTINCT * FROM t1 LEFT JOIN t0 ON t1.c1 = t0.c0 INTERSECT SELECT DISTINCT * FROM t1 RIGHT JOIN t0 ON t1.c1 = t0.c0;
c1	c0
1	NULL
[ 50%] main.bug117911                            [ pass ]     54
[100%] shutdown_report                           [ pass ]
------------------------------------------------------------------------------
The servers were restarted 0 times
The servers were reinitialized 0 times
Spent 0.054 of 9 seconds executing testcases

regards,
Umesh