Description:
In theory, the result of sql2(DISTINCT) ⊆ the result of sql1:
SELECT 1%`f1` FROM (SELECT DAYNAME('2010-07-20') AS `f1` FROM t WHERE DATE_ADD(TIMEDIFF('2000-08-04 02:09:39', '2002-08-11 22:00:35'), INTERVAL 1 YEAR) NOT IN (1)) AS `t2`; -- sql1
SELECT 1%`f1` FROM (SELECT DISTINCT DAYNAME('2010-07-20') AS `f1` FROM t WHERE DATE_ADD(TIMEDIFF('2000-08-04 02:09:39', '2002-08-11 22:00:35'), INTERVAL 1 YEAR) NOT IN (1)) AS `t2`; -- sql2
However, the value 0 changed to NULL after adding DISTINCT, seems like a logical bug:
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.31 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT 1%`f1` FROM (SELECT DAYNAME('2010-07-20') AS `f1` FROM t WHERE DATE_ADD(TIMEDIFF('2000-08-04 02:09:39', '2002-08-11 22:00:35'), INTERVAL 1 YEAR) NOT IN (1)) AS `t2`;
+--------+
| 1%`f1` |
+--------+
| 0 |
+--------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT 1%`f1` FROM (SELECT DISTINCT DAYNAME('2010-07-20') AS `f1` FROM t WHERE DATE_ADD(TIMEDIFF('2000-08-04 02:09:39', '2002-08-11 22:00:35'), INTERVAL 1 YEAR) NOT IN (1)) AS `t2`;
+--------+
| 1%`f1` |
+--------+
| NULL |
+--------+
1 row in set, 3 warnings (0.00 sec)
How to repeat:
drop table if exists t;
CREATE TABLE t (c1 int);
INSERT INTO t VALUES (1);
SELECT 1%`f1` FROM (SELECT DAYNAME('2010-07-20') AS `f1` FROM t WHERE DATE_ADD(TIMEDIFF('2000-08-04 02:09:39', '2002-08-11 22:00:35'), INTERVAL 1 YEAR) NOT IN (1)) AS `t2`;
SELECT 1%`f1` FROM (SELECT DISTINCT DAYNAME('2010-07-20') AS `f1` FROM t WHERE DATE_ADD(TIMEDIFF('2000-08-04 02:09:39', '2002-08-11 22:00:35'), INTERVAL 1 YEAR) NOT IN (1)) AS `t2`;
Suggested fix:
The following list may be helpful for your debugging:
1. The bug cannot be reproduced after deleting any date function;
2. 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.28, it cannot be reproduced in mysql:8.0.27:
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.27 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT 1%`f1` FROM (SELECT DAYNAME('2010-07-20') AS `f1` FROM t WHERE DATE_ADD(TIMEDIFF('2000-08-04 02:09:39', '2002-08-11 22:00:35'), INTERVAL 1 YEAR) NOT IN (1)) AS `t2`;
Empty set, 2 warnings (0.00 sec)
mysql> SELECT 1%`f1` FROM (SELECT DISTINCT DAYNAME('2010-07-20') AS `f1` FROM t WHERE DATE_ADD(TIMEDIFF('2000-08-04 02:09:39', '2002-08-11 22:00:35'), INTERVAL 1 YEAR) NOT IN (1)) AS `t2`;
Empty set, 2 warnings (0.00 sec)
Description: In theory, the result of sql2(DISTINCT) ⊆ the result of sql1: SELECT 1%`f1` FROM (SELECT DAYNAME('2010-07-20') AS `f1` FROM t WHERE DATE_ADD(TIMEDIFF('2000-08-04 02:09:39', '2002-08-11 22:00:35'), INTERVAL 1 YEAR) NOT IN (1)) AS `t2`; -- sql1 SELECT 1%`f1` FROM (SELECT DISTINCT DAYNAME('2010-07-20') AS `f1` FROM t WHERE DATE_ADD(TIMEDIFF('2000-08-04 02:09:39', '2002-08-11 22:00:35'), INTERVAL 1 YEAR) NOT IN (1)) AS `t2`; -- sql2 However, the value 0 changed to NULL after adding DISTINCT, seems like a logical bug: mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.31 | +-----------+ 1 row in set (0.00 sec) mysql> SELECT 1%`f1` FROM (SELECT DAYNAME('2010-07-20') AS `f1` FROM t WHERE DATE_ADD(TIMEDIFF('2000-08-04 02:09:39', '2002-08-11 22:00:35'), INTERVAL 1 YEAR) NOT IN (1)) AS `t2`; +--------+ | 1%`f1` | +--------+ | 0 | +--------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT 1%`f1` FROM (SELECT DISTINCT DAYNAME('2010-07-20') AS `f1` FROM t WHERE DATE_ADD(TIMEDIFF('2000-08-04 02:09:39', '2002-08-11 22:00:35'), INTERVAL 1 YEAR) NOT IN (1)) AS `t2`; +--------+ | 1%`f1` | +--------+ | NULL | +--------+ 1 row in set, 3 warnings (0.00 sec) How to repeat: drop table if exists t; CREATE TABLE t (c1 int); INSERT INTO t VALUES (1); SELECT 1%`f1` FROM (SELECT DAYNAME('2010-07-20') AS `f1` FROM t WHERE DATE_ADD(TIMEDIFF('2000-08-04 02:09:39', '2002-08-11 22:00:35'), INTERVAL 1 YEAR) NOT IN (1)) AS `t2`; SELECT 1%`f1` FROM (SELECT DISTINCT DAYNAME('2010-07-20') AS `f1` FROM t WHERE DATE_ADD(TIMEDIFF('2000-08-04 02:09:39', '2002-08-11 22:00:35'), INTERVAL 1 YEAR) NOT IN (1)) AS `t2`; Suggested fix: The following list may be helpful for your debugging: 1. The bug cannot be reproduced after deleting any date function; 2. 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.28, it cannot be reproduced in mysql:8.0.27: mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.27 | +-----------+ 1 row in set (0.00 sec) mysql> SELECT 1%`f1` FROM (SELECT DAYNAME('2010-07-20') AS `f1` FROM t WHERE DATE_ADD(TIMEDIFF('2000-08-04 02:09:39', '2002-08-11 22:00:35'), INTERVAL 1 YEAR) NOT IN (1)) AS `t2`; Empty set, 2 warnings (0.00 sec) mysql> SELECT 1%`f1` FROM (SELECT DISTINCT DAYNAME('2010-07-20') AS `f1` FROM t WHERE DATE_ADD(TIMEDIFF('2000-08-04 02:09:39', '2002-08-11 22:00:35'), INTERVAL 1 YEAR) NOT IN (1)) AS `t2`; Empty set, 2 warnings (0.00 sec)