Description:
In theory, the result of sql2(DISTINCT) ⊆ the result of sql1:
SELECT c1 FROM (SELECT c1, c2 FROM t) AS `t1` WHERE (c1 BETWEEN DAYNAME('2003-02-12') AND c1); -- sql1
SELECT c1 FROM (SELECT DISTINCT c1, c2 FROM t) AS `t1` WHERE (c1 BETWEEN DAYNAME('2003-02-12') AND c1); -- sql2
However, there is an extra line in the result of sql2, seems like a logical bug:
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.31 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT c1 FROM (SELECT c1, c2 FROM t) AS `t1` WHERE (c1 BETWEEN DAYNAME('2003-02-12') AND c1);
+--------+
| c1 |
+--------+
| 71.051 |
+--------+
1 row in set (0.00 sec)
mysql> SELECT c1 FROM (SELECT DISTINCT c1, c2 FROM t) AS `t1` WHERE (c1 BETWEEN DAYNAME('2003-02-12') AND c1);
+--------+
| c1 |
+--------+
| 71.051 |
| 0 |
+--------+
2 rows in set (0.00 sec)
How to repeat:
drop table if exists t;
create table t (c1 double, c2 decimal(40, 20), key (c1));
insert into t values (-13064,-2),(71.0510,12.991),(-0,47.1515);
SELECT c1 FROM (SELECT c1, c2 FROM t) AS `t1` WHERE (c1 BETWEEN DAYNAME('2003-02-12') AND c1);
SELECT c1 FROM (SELECT DISTINCT c1, c2 FROM t) AS `t1` WHERE (c1 BETWEEN DAYNAME('2003-02-12') AND c1);
Suggested fix:
The following list may be helpful for your debugging:
1. The bug cannot be reproduced after removing key (c1);
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.13, it cannot be reproduced in mysql:8.0.12:
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.12 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT c1 FROM (SELECT c1, c2 FROM t) AS `t1` WHERE (c1 BETWEEN DAYNAME('2003-02-12') AND c1);
+--------+
| c1 |
+--------+
| 0 |
| 71.051 |
+--------+
2 rows in set (0.00 sec)
mysql> SELECT c1 FROM (SELECT DISTINCT c1, c2 FROM t) AS `t1` WHERE (c1 BETWEEN DAYNAME('2003-02-12') AND c1);
+--------+
| c1 |
+--------+
| 71.051 |
| 0 |
+--------+
2 rows in set (0.00 sec)
Description: In theory, the result of sql2(DISTINCT) ⊆ the result of sql1: SELECT c1 FROM (SELECT c1, c2 FROM t) AS `t1` WHERE (c1 BETWEEN DAYNAME('2003-02-12') AND c1); -- sql1 SELECT c1 FROM (SELECT DISTINCT c1, c2 FROM t) AS `t1` WHERE (c1 BETWEEN DAYNAME('2003-02-12') AND c1); -- sql2 However, there is an extra line in the result of sql2, seems like a logical bug: mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.31 | +-----------+ 1 row in set (0.00 sec) mysql> SELECT c1 FROM (SELECT c1, c2 FROM t) AS `t1` WHERE (c1 BETWEEN DAYNAME('2003-02-12') AND c1); +--------+ | c1 | +--------+ | 71.051 | +--------+ 1 row in set (0.00 sec) mysql> SELECT c1 FROM (SELECT DISTINCT c1, c2 FROM t) AS `t1` WHERE (c1 BETWEEN DAYNAME('2003-02-12') AND c1); +--------+ | c1 | +--------+ | 71.051 | | 0 | +--------+ 2 rows in set (0.00 sec) How to repeat: drop table if exists t; create table t (c1 double, c2 decimal(40, 20), key (c1)); insert into t values (-13064,-2),(71.0510,12.991),(-0,47.1515); SELECT c1 FROM (SELECT c1, c2 FROM t) AS `t1` WHERE (c1 BETWEEN DAYNAME('2003-02-12') AND c1); SELECT c1 FROM (SELECT DISTINCT c1, c2 FROM t) AS `t1` WHERE (c1 BETWEEN DAYNAME('2003-02-12') AND c1); Suggested fix: The following list may be helpful for your debugging: 1. The bug cannot be reproduced after removing key (c1); 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.13, it cannot be reproduced in mysql:8.0.12: mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.12 | +-----------+ 1 row in set (0.00 sec) mysql> SELECT c1 FROM (SELECT c1, c2 FROM t) AS `t1` WHERE (c1 BETWEEN DAYNAME('2003-02-12') AND c1); +--------+ | c1 | +--------+ | 0 | | 71.051 | +--------+ 2 rows in set (0.00 sec) mysql> SELECT c1 FROM (SELECT DISTINCT c1, c2 FROM t) AS `t1` WHERE (c1 BETWEEN DAYNAME('2003-02-12') AND c1); +--------+ | c1 | +--------+ | 71.051 | | 0 | +--------+ 2 rows in set (0.00 sec)