Bug #119590 EXCEPT (SELECT NULL) returns incorrect result (missing row) on DECIMAL primary key column

• Submitted: 24 Dec 6:41
• Modified: 26 Dec 6:22
• Reporter: Seren Zhou
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 9.5.0
• OS: Any
• CPU Architecture: Any

[24 Dec 6:41] Seren Zhou

Description:
When performing an EXCEPT (SELECT NULL) operation on a table containing a DECIMAL(20,0) column defined as a PRIMARY KEY, the query returns an incorrect result set. Specifically, certain large decimal values are silently dropped from the result, even though they are clearly not NULL.

If the PRIMARY KEY constraint is removed from table t1, the query correctly returns all 3 rows.

How to repeat:
CREATE TABLE t1(c1 DECIMAL(20,0) PRIMARY KEY);

INSERT INTO t1(c1) VALUES(-325390907);
INSERT INTO t1(c1) VALUES(-9);
INSERT INTO t1(c1) VALUES(570878339434786781);

-- Execute the problematic query
SELECT c1 FROM t1 EXCEPT(SELECT NULL);

/*
ACTUAL OUTPUT (Incorrect, missing the large positive value):
+------------+
| c1         |
+------------+
| -325390907 |
|         -9 |
+------------+
2 rows in set

EXPECTED OUTPUT:
+--------------------+
| c1                 |
+--------------------+
|         -325390907 |
|                 -9 |
| 570878339434786781 |
+--------------------+
3 rows in set
*/

[26 Dec 6:22] Chaithra Marsur Gopala Reddy

Hi Seren Zhou,

Thank you for the test case. Verified as described.