Bug #119961 INTERSECT ALL drops ZEROFILL column metadata, causing incorrect string CAST results and query logic failures

• Submitted: 2 Mar 5:46
• Modified: 2 Mar 13:24
• Reporter: Guo Yuxiao
• Status: Not a Bug
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 9.6.0
• OS: Any
• CPU Architecture: Any
• Tags: Logic bug

[2 Mar 5:46] Guo Yuxiao

Description:
When performing an INTERSECT ALL set operation on a column defined with the ZEROFILL attribute, the resulting intermediate result set silently loses its ZEROFILL metadata.

Consequently, any subsequent operations that rely on the formatted string representation of this column—such as an explicit CAST(col AS CHAR) or implicit string comparisons—yield unpadded values. For instance, a value that should be evaluated as the string '00000' is incorrectly evaluated as '0'.

How to repeat:
DROP TABLE IF EXISTS t_test;
CREATE TABLE t_test (c0 smallint(5) unsigned zerofill);
INSERT INTO t_test VALUES (0);

--  '00000'
SELECT CAST(c0 AS CHAR) AS result FROM t_test;
+--------+
| result |
+--------+
| 00000  |
+--------+
1 row in set (0.000 sec)

--  Bug '0' ( zerofill)
SELECT CAST(c0 AS CHAR) AS result FROM (
    SELECT c0 FROM t_test
    INTERSECT ALL
    SELECT c0 FROM t_test
) AS sub;
+--------+
| result |
+--------+
| 0      |
+--------+
1 row in set (0.001 sec)

[2 Mar 13:24] Roy Lyseng

This is not a bug.
The ZEROFILL property is not propagated through expressions, like in INTERSECT or UNION.
If you need this property to be propagated, add the CAST(c0 AS CHAR) on each of the selected columns, and remove it from the outermost expression.