Bug #118689 Inconsistent data returns when using INTERESECT operator

• Submitted: 21 Jul 11:33
• Modified: 4 Aug 9:43
• Reporter: 策 吕
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 9.2.0, 8.0.42, 8.4.5, 9.3.0
• OS: Linux
• CPU Architecture: Any

[21 Jul 11:33] 策 吕

Description:
When a SELECT query retrieves data from a column with the ZEROFILL attribute, the formatting is correctly applied in the result set. However, when the same column is used within a query involving a set operator like INTERSECT, the ZEROFILL formatting attribute is lost in the final result set. This creates an inconsistency in behavior, where the presentation of data depends on whether a set operator is used, which can lead to unexpected results for users and applications.

*What did you expect to see? (Required)*
mysql> SELECT DISTINCT t1.c0 FROM  t0 INNER JOIN t1 ON ((-1.757248483E9)<=(t1.c0));
+------------+
| c0         |
+------------+
| 0000000000 |
| 0000000001 |
+------------+
2 rows in set (0.00 sec)

mysql> (SELECT DISTINCT t1.c0 FROM  t0 LEFT JOIN t1 ON ((-1.757248483E9)<=(t1.c0))) INTERSECT (SELECT DISTINCT t1.c0 FROM  t0 RIGHT JOIN t1 ON ((-1.757248483E9)<=(t1.c0)));
+------------+
| c0         |
+------------+
| 0000000000 |
| 0000000001 |
+------------+
2 rows in set (0.00 sec)

*What did you see instead (Required)*
mysql> SELECT DISTINCT t1.c0 FROM  t0 INNER JOIN t1 ON ((-1.757248483E9)<=(t1.c0));
+------------+
| c0         |
+------------+
| 0000000000 |
| 0000000001 |
+------------+
2 rows in set (0.00 sec)

mysql> (SELECT DISTINCT t1.c0 FROM  t0 LEFT JOIN t1 ON ((-1.757248483E9)<=(t1.c0))) INTERSECT (SELECT DISTINCT t1.c0 FROM  t0 RIGHT JOIN t1 ON ((-1.757248483E9)<=(t1.c0)));
+----+
| c0 |
+----+
|  0 |
|  1 |
+----+
2 rows in set (0.00 sec)

How to repeat:
DROP DATABASE IF EXISTS database61;
CREATE DATABASE database61;
USE database61;
CREATE TABLE t0(c0 INT ZEROFILL NOT NULL );

CREATE TABLE t1 LIKE t0;
REPLACE INTO t1 VALUES (false), (false), (true);
INSERT INTO t0(c0) VALUES (false) ON DUPLICATE KEY UPDATE c0=t0.c0;
REPLACE INTO t0 VALUES (false);
REPLACE INTO t1(c0) VALUES (true);
INSERT INTO t0 VALUES (false), (false), (false);
REPLACE INTO t1(c0) VALUES (false);
REPLACE INTO t1(c0) VALUES (false);
REPLACE INTO t0(c0) VALUES (false);
INSERT INTO t1 VALUES (false), (false) ON DUPLICATE KEY UPDATE c0=(~ ('YsISRq'));
INSERT IGNORE INTO t0 VALUES (false), (NULL);

INSERT IGNORE INTO t1(c0) VALUES (false);
REPLACE INTO t1(c0) VALUES (true);

UPDATE t0 SET c0=0.9256709693178293;
INSERT IGNORE INTO t1(c0) VALUES (true);
REPLACE INTO t1 VALUES (true), (true), (true);
INSERT IGNORE INTO t1 VALUES (true);

REPLACE INTO t0 VALUES (false);
REPLACE INTO t1 VALUES (true);
INSERT INTO t1(c0) VALUES (true) ON DUPLICATE KEY UPDATE c0=t1.c0;

INSERT IGNORE INTO t0(c0) VALUES (false) ON DUPLICATE KEY UPDATE c0=t0.c0;

INSERT IGNORE INTO t1(c0) VALUES (false), (true) ON DUPLICATE KEY UPDATE c0=((t1.c0)OR((CASE t1.c0 WHEN 'z.' THEN t1.c0 ELSE 'e' END )));
REPLACE INTO t1(c0) VALUES (false);
UPDATE t1 SET c0=t1.c0 WHERE 0.19610550901368273;
INSERT IGNORE INTO t0(c0) VALUES (true);
REPLACE INTO t0 VALUES (true);
UPDATE t0 SET c0=0.2800239623483346 WHERE (((CASE NULL WHEN false THEN -1146250208 ELSE t0.c0 END )) IS NULL);
INSERT IGNORE INTO t0 VALUES (true);
INSERT IGNORE INTO t1(c0) VALUES (false), (false);

SELECT DISTINCT t1.c0 FROM t0 INNER JOIN t1 ON ((-1.757248483E9)<=(t1.c0));

(SELECT DISTINCT t1.c0 FROM t0 LEFT JOIN t1 ON ((-1.757248483E9)<=(t1.c0))) INTERSECT (SELECT DISTINCT t1.c0 FROM t0 RIGHT JOIN t1 ON ((-1.757248483E9)<=(t1.c0)));

[21 Jul 12:36] MySQL Verification Team

Hello 策 吕,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh

[4 Aug 9:43] Roy Lyseng

Posted by developer:
 
This is not a bug.
Column properties such as ZEROFILL are not preserved across operators such as INTERSECT, UNION or EXCEPT.
This would also create a possible confusion regarding how to propagate one column with ZEROFILL from one branch and one column without ZEROFILL from the other branch.