Description:
In theory, the result of sql2(DISTINCT) ⊆ the result of sql1:
(SELECT ('2009-05-09 01:41:51'>>1) AS `f1`) UNION (SELECT `f2` FROM (SELECT (NULL) AS `f2` FROM (SELECT 1 FROM t) AS `t1`) AS `t2`); -- sql1
(SELECT ('2009-05-09 01:41:51'>>1) AS `f1`) UNION (SELECT `f2` FROM (SELECT DISTINCT (NULL) AS `f2` FROM (SELECT 1 FROM t) AS `t1`) AS `t2`); -- sql2
However, the numeric format 1004 changed to 1004.000000000000000000000000000000 after adding DISTINCT, seems like a logical bug:
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.31 |
+-----------+
1 row in set (0.01 sec)
mysql> (SELECT ('2009-05-09 01:41:51'>>1) AS `f1`) UNION (SELECT `f2` FROM (SELECT (NULL) AS `f2` FROM (SELECT 1 FROM t) AS `t1`) AS `t2`);
+------+
| f1 |
+------+
| 1004 |
| NULL |
+------+
2 rows in set, 1 warning (0.00 sec)
mysql> (SELECT ('2009-05-09 01:41:51'>>1) AS `f1`) UNION (SELECT `f2` FROM (SELECT DISTINCT (NULL) AS `f2` FROM (SELECT 1 FROM t) AS `t1`) AS `t2`);
+-------------------------------------+
| f1 |
+-------------------------------------+
| 1004.000000000000000000000000000000 |
| NULL |
+-------------------------------------+
2 rows in set, 1 warning (0.01 sec)
How to repeat:
drop table if exists t;
CREATE TABLE t (c1 int);
INSERT INTO t VALUES (1);
(SELECT ('2009-05-09 01:41:51'>>1) AS `f1`) UNION (SELECT `f2` FROM (SELECT (NULL) AS `f2` FROM (SELECT 1 FROM t) AS `t1`) AS `t2`);
(SELECT ('2009-05-09 01:41:51'>>1) AS `f1`) UNION (SELECT `f2` FROM (SELECT DISTINCT (NULL) AS `f2` FROM (SELECT 1 FROM t) AS `t1`) AS `t2`);
Suggested fix:
The following list may be helpful for your debugging:
1. The bug cannot be reproduced after changing NULL to 1;
2. The bug cannot be reproduced after removing the outer query SELECT `f2` FROM (...) AS `t2`;
3. We look for the first version of the bug in all docker images (with format x.x.x) of https://hub.docker.com/_/mysql/tags
We found that the bug first occurred in mysql:8.0.21, it cannot be reproduced in mysql:8.0.20:
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.20 |
+-----------+
1 row in set (0.00 sec)
mysql> (SELECT ('2009-05-09 01:41:51'>>1) AS `f1`) UNION (SELECT `f2` FROM (SELECT (NULL) AS `f2` FROM (SELECT 1 FROM t) AS `t1`) AS `t2`);
+------+
| f1 |
+------+
| 1004 |
| NULL |
+------+
2 rows in set, 1 warning (0.00 sec)
mysql> (SELECT ('2009-05-09 01:41:51'>>1) AS `f1`) UNION (SELECT `f2` FROM (SELECT DISTINCT (NULL) AS `f2` FROM (SELECT 1 FROM t) AS `t1`) AS `t2`);
+------+
| f1 |
+------+
| 1004 |
| NULL |
+------+
2 rows in set, 1 warning (0.00 sec)
Description: In theory, the result of sql2(DISTINCT) ⊆ the result of sql1: (SELECT ('2009-05-09 01:41:51'>>1) AS `f1`) UNION (SELECT `f2` FROM (SELECT (NULL) AS `f2` FROM (SELECT 1 FROM t) AS `t1`) AS `t2`); -- sql1 (SELECT ('2009-05-09 01:41:51'>>1) AS `f1`) UNION (SELECT `f2` FROM (SELECT DISTINCT (NULL) AS `f2` FROM (SELECT 1 FROM t) AS `t1`) AS `t2`); -- sql2 However, the numeric format 1004 changed to 1004.000000000000000000000000000000 after adding DISTINCT, seems like a logical bug: mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.31 | +-----------+ 1 row in set (0.01 sec) mysql> (SELECT ('2009-05-09 01:41:51'>>1) AS `f1`) UNION (SELECT `f2` FROM (SELECT (NULL) AS `f2` FROM (SELECT 1 FROM t) AS `t1`) AS `t2`); +------+ | f1 | +------+ | 1004 | | NULL | +------+ 2 rows in set, 1 warning (0.00 sec) mysql> (SELECT ('2009-05-09 01:41:51'>>1) AS `f1`) UNION (SELECT `f2` FROM (SELECT DISTINCT (NULL) AS `f2` FROM (SELECT 1 FROM t) AS `t1`) AS `t2`); +-------------------------------------+ | f1 | +-------------------------------------+ | 1004.000000000000000000000000000000 | | NULL | +-------------------------------------+ 2 rows in set, 1 warning (0.01 sec) How to repeat: drop table if exists t; CREATE TABLE t (c1 int); INSERT INTO t VALUES (1); (SELECT ('2009-05-09 01:41:51'>>1) AS `f1`) UNION (SELECT `f2` FROM (SELECT (NULL) AS `f2` FROM (SELECT 1 FROM t) AS `t1`) AS `t2`); (SELECT ('2009-05-09 01:41:51'>>1) AS `f1`) UNION (SELECT `f2` FROM (SELECT DISTINCT (NULL) AS `f2` FROM (SELECT 1 FROM t) AS `t1`) AS `t2`); Suggested fix: The following list may be helpful for your debugging: 1. The bug cannot be reproduced after changing NULL to 1; 2. The bug cannot be reproduced after removing the outer query SELECT `f2` FROM (...) AS `t2`; 3. We look for the first version of the bug in all docker images (with format x.x.x) of https://hub.docker.com/_/mysql/tags We found that the bug first occurred in mysql:8.0.21, it cannot be reproduced in mysql:8.0.20: mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.20 | +-----------+ 1 row in set (0.00 sec) mysql> (SELECT ('2009-05-09 01:41:51'>>1) AS `f1`) UNION (SELECT `f2` FROM (SELECT (NULL) AS `f2` FROM (SELECT 1 FROM t) AS `t1`) AS `t2`); +------+ | f1 | +------+ | 1004 | | NULL | +------+ 2 rows in set, 1 warning (0.00 sec) mysql> (SELECT ('2009-05-09 01:41:51'>>1) AS `f1`) UNION (SELECT `f2` FROM (SELECT DISTINCT (NULL) AS `f2` FROM (SELECT 1 FROM t) AS `t1`) AS `t2`); +------+ | f1 | +------+ | 1004 | | NULL | +------+ 2 rows in set, 1 warning (0.00 sec)