Bug #117926 INTERSECT may incorrectly eliminate duplicate

• Submitted: 9 Apr 19:08
• Modified: 11 Apr 21:03
• Reporter: jinhui lai
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.41, 9.2.0, 8.4.4
• OS: Ubuntu (22.04)
• CPU Architecture: Any

[9 Apr 19:08] jinhui lai

Description:
When a column contains NULL values, INTERSECT may incorrectly eliminate duplicate rows.

How to repeat:
docker run -it --name mysql-latest -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql:latest

CREATE TABLE t0(c0 INT) ;
CREATE TABLE t1(c0 INT) ;
INSERT INTO t0(c0) VALUES(1);
INSERT INTO t1(c0) VALUES(NULL),(NULL);
SELECT * FROM t0 CROSS JOIN t1 INTERSECT SELECT * FROM t0 CROSS JOIN t1; -- wrong result
+------+------+
| c0   | c0   |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.00 sec)
SELECT * FROM t0 CROSS JOIN t1;-- right result
+------+------+
| c0   | c0   |
+------+------+
|    1 | NULL |
|    1 | NULL |
+------+------+
2 rows in set (0.00 sec)

CREATE TABLE t0(c0 INT PRIMARY KEY) ;
CREATE TABLE t1(c0 INT) ;
INSERT INTO t0(c0) VALUES(1);
INSERT INTO t1(c0) VALUES(2),(3);
SELECT * FROM t0 CROSS JOIN t1 INTERSECT SELECT * FROM t0 CROSS JOIN t1; -- right result
+------+------+
| c0   | c0   |
+------+------+
|    1 |    2 |
|    1 |    3 |
+------+------+
SELECT * FROM t0 CROSS JOIN t1; -- right result
+------+------+
| c0   | c0   |
+------+------+
|    1 |    2 |
|    1 |    3 |
+------+------+

[10 Apr 7:02] MySQL Verification Team

Hello jinhui lai,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

[10 Apr 14:34] Dag Wanvik

Posted by developer:
 
This is not a bug. The user seems to want INTERSECT ALL instead;
INTERSECT will remove duplicates from all operands.