| Bug #117930 | INTERSECT operation fails on INT ZEROFILL column type. | ||
|---|---|---|---|
| Submitted: | 10 Apr 8:29 | Modified: | 11 Apr 16:53 |
| Reporter: | jinhui lai | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 9.2.0, 8.4.4, 8.0.41 | OS: | Ubuntu (22.04) |
| Assigned to: | CPU Architecture: | Any | |
[10 Apr 8:42]
jinhui lai
Sorry, i think this is a better case.
DROP TABLE IF EXISTS t0, t1;
CREATE TABLE t0(c0 INT ZEROFILL) ;
CREATE TABLE t1 LIKE t0;
INSERT IGNORE INTO t0(c0) VALUES(-1),("");
INSERT IGNORE INTO t1(c0) VALUES(-1),(NULL);
-- this query's result is wrong, others is right
SELECT * FROM t0 CROSS JOIN t1 ON t0.c0 >= t1.c0 INTERSECT SELECT * FROM t0 CROSS JOIN t1 ON t0.c0 <= t1.c0;
+------+------+
| c0 | c0 |
+------+------+
| 0 | 0 |
+------+------+
1 row in set (0.00 sec)
SELECT * FROM t0 CROSS JOIN t1 ON t0.c0 >= t1.c0;
+------------+------------+
| c0 | c0 |
+------------+------------+
| 0000000000 | 0000000000 |
| 0000000000 | 0000000000 |
+------------+------------+
2 rows in set (0.00 sec)
SELECT * FROM t0 CROSS JOIN t1 ON t0.c0 <= t1.c0;
+------------+------------+
| c0 | c0 |
+------------+------------+
| 0000000000 | 0000000000 |
| 0000000000 | 0000000000 |
+------------+------------+
2 rows in set (0.00 sec)
SELECT * FROM t0 CROSS JOIN t1 ON t0.c0 = t1.c0;
+------------+------------+
| c0 | c0 |
+------------+------------+
| 0000000000 | 0000000000 |
| 0000000000 | 0000000000 |
+------------+------------+
2 rows in set (0.00 sec)
SELECT * FROM t0 CROSS JOIN t1 ON t0.c0 >= t1.c0 AND t0.c0 <= t1.c0;
+------------+------------+
| c0 | c0 |
+------------+------------+
| 0000000000 | 0000000000 |
| 0000000000 | 0000000000 |
+------------+------------+
2 rows in set (0.00 sec)
DROP TABLE IF EXISTS t0, t1;
CREATE TABLE t0(c0 INT) ;
CREATE TABLE t1 LIKE t0;
INSERT IGNORE INTO t0(c0) VALUES(-1),("");
INSERT IGNORE INTO t1(c0) VALUES(-1),(NULL);
SELECT * FROM t0 CROSS JOIN t1 ON t0.c0 >= t1.c0 INTERSECT SELECT * FROM t0 CROSS JOIN t1 ON t0.c0 <= t1.c0;
+------+------+
| c0 | c0 |
+------+------+
| -1 | -1 |
+------+------+
1 row in set (0.00 sec)
SELECT * FROM t0 CROSS JOIN t1 ON t0.c0 >= t1.c0;
+------+------+
| c0 | c0 |
+------+------+
| 0 | -1 |
| -1 | -1 |
+------+------+
2 rows in set (0.00 sec)
SELECT * FROM t0 CROSS JOIN t1 ON t0.c0 <= t1.c0;
+------+------+
| c0 | c0 |
+------+------+
| -1 | -1 |
+------+------+
1 row in set (0.00 sec)
SELECT * FROM t0 CROSS JOIN t1 ON t0.c0 = t1.c0;
+------+------+
| c0 | c0 |
+------+------+
| -1 | -1 |
+------+------+
1 row in set (0.00 sec)
SELECT * FROM t0 CROSS JOIN t1 ON t0.c0 = t1.c0 AND t0.c0 <= t1.c0;
+------+------+
| c0 | c0 |
+------+------+
| -1 | -1 |
+------+------+
1 row in set (0.00 sec)
[10 Apr 10:12]
MySQL Verification Team
Hello jinhui lai, Thank you for the report and test case. Verified as described. regards, Umesh
[11 Apr 16:53]
Dag Wanvik
Posted by developer: Closing as not a bug. The SQL standard semantics of INTERSECT will always remove duplicates in the result set. If duplicates are desired, use INTERSECT ALL.
Description: When INTERSECT processes a column of type INT ZEROFILL, it returns an INT result and incorrectly removes duplicates. How to repeat: docker run -it --name mysql-latest -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql:latest DROP TABLE IF EXISTS t0, t1; CREATE TABLE t0(c0 INT ZEROFILL) ; CREATE TABLE t1 LIKE t0; INSERT IGNORE INTO t0(c0) VALUES(-1),(""); INSERT IGNORE INTO t1(c0) VALUES(-1),(NULL); SELECT * FROM t0 CROSS JOIN t1 ON t0.c0 >= t1.c0 INTERSECT SELECT * FROM t0 CROSS JOIN t1 ON t0.c0 <= t1.c0; --wrong result +------+------+ | c0 | c0 | +------+------+ | 0 | 0 | +------+------+ 1 row in set (0.00 sec) SELECT * FROM t0 CROSS JOIN t1 ON t0.c0 = t1.c0; --right result +------------+------------+ | c0 | c0 | +------------+------------+ | 0000000000 | 0000000000 | | 0000000000 | 0000000000 | +------------+------------+ 2 rows in set (0.00 sec) SELECT * FROM t0 CROSS JOIN t1 ON t0.c0 = t1.c0 AND t0.c0 <= t1.c0; --right result +------------+------------+ | c0 | c0 | +------------+------------+ | 0000000000 | 0000000000 | | 0000000000 | 0000000000 | +------------+------------+ 2 rows in set (0.00 sec) DROP TABLE IF EXISTS t0, t1; CREATE TABLE t0(c0 INT) ; CREATE TABLE t1 LIKE t0; INSERT IGNORE INTO t0(c0) VALUES(-1),(""); INSERT IGNORE INTO t1(c0) VALUES(-1),(NULL); SELECT * FROM t0 CROSS JOIN t1 ON t0.c0 >= t1.c0 INTERSECT SELECT * FROM t0 CROSS JOIN t1 ON t0.c0 <= t1.c0; --right result +------+------+ | c0 | c0 | +------+------+ | -1 | -1 | +------+------+ 1 row in set (0.00 sec) SELECT * FROM t0 CROSS JOIN t1 ON t0.c0 = t1.c0; --right result +------+------+ | c0 | c0 | +------+------+ | -1 | -1 | +------+------+ 1 row in set (0.00 sec) SELECT * FROM t0 CROSS JOIN t1 ON t0.c0 = t1.c0 AND t0.c0 <= t1.c0; --right result +------+------+ | c0 | c0 | +------+------+ | -1 | -1 | +------+------+ 1 row in set (0.00 sec)