|
Description:
In theory, the result of sql2(DISTINCT) ⊆ the result of sql1:
SELECT PI()+~1&f2 AS f1 FROM (SELECT DATE_SUB(DATE_ADD('2009-10-15', INTERVAL 1 MICROSECOND), INTERVAL 1 DAY_SECOND) AS f2 FROM t) AS t1; -- sql1
|
SELECT PI()+~1&f2 AS f1 FROM (SELECT DISTINCT DATE_SUB(DATE_ADD('2009-10-15', INTERVAL 1 MICROSECOND), INTERVAL 1 DAY_SECOND) AS f2 FROM t) AS t1; -- sql2
|
However, the value 20091014235959 changed to 2009 after adding DISTINCT, seems like a logical bug:
mysql> select version();
|
+-----------------------------------------+
|
| version() |
|
+-----------------------------------------+
|
| 10.11.1-MariaDB-1:10.11.1+maria~ubu2204 |
|
+-----------------------------------------+
|
1 row in set (0.00 sec)
|
|
mysql> SELECT PI()+~1&f2 AS f1 FROM (SELECT DATE_SUB(DATE_ADD('2009-10-15', INTERVAL 1 MICROSECOND), INTERVAL 1 DAY_SECOND) AS f2 FROM t) AS t1; -- sql1
|
+----------------+
|
| f1 |
|
+----------------+
|
| 20091014235959 |
|
+----------------+
|
1 row in set, 1 warning (0.00 sec)
|
|
mysql> SELECT PI()+~1&f2 AS f1 FROM (SELECT DISTINCT DATE_SUB(DATE_ADD('2009-10-15', INTERVAL 1 MICROSECOND), INTERVAL 1 DAY_SECOND) AS f2 FROM t) AS t1; -- sql2
|
+------+
|
| f1 |
|
+------+
|
| 2009 |
|
+------+
|
1 row in set, 2 warnings (0.00 sec)
|
How to repeat:
drop table if exists t;
|
CREATE TABLE t (c1 INT);
|
INSERT INTO t VALUES (1);
|
|
SELECT PI()+~1&f2 AS f1 FROM (SELECT DATE_SUB(DATE_ADD('2009-10-15', INTERVAL 1 MICROSECOND), INTERVAL 1 DAY_SECOND) AS f2 FROM t) AS t1; -- sql1
|
SELECT PI()+~1&f2 AS f1 FROM (SELECT DISTINCT DATE_SUB(DATE_ADD('2009-10-15', INTERVAL 1 MICROSECOND), INTERVAL 1 DAY_SECOND) AS f2 FROM t) AS t1; -- sql2
|
Hope these can be helpful for your debugging:
1. The bug cannot be reproduced after removing the date function (change to '2009-10-15' AS f2);
2. The bug cannot be reproduced after removing FROM t;
3. We look for the first version of the bug in all docker images (with format x.x.x) of https://hub.docker.com/_/mariadb/tags
We found that the bug first occurred in mariadb:5.5.62, it cannot be reproduced in mariadb:5.5.61:
MariaDB [TEST]> select version();
|
+-------------------------+
|
| version() |
|
+-------------------------+
|
| 5.5.62-MariaDB-1~trusty |
|
+-------------------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [TEST]> SELECT PI()+~1&f2 AS f1 FROM (SELECT DATE_SUB(DATE_ADD('2009-10-15', INTERVAL 1 MICROSECOND), INTERVAL 1 DAY_SECOND) AS f2 FROM t) AS t1; -- sql1
|
+----------------+
|
| f1 |
|
+----------------+
|
| 20091014235959 |
|
+----------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [TEST]> SELECT PI()+~1&f2 AS f1 FROM (SELECT DISTINCT DATE_SUB(DATE_ADD('2009-10-15', INTERVAL 1 MICROSECOND), INTERVAL 1 DAY_SECOND) AS f2 FROM t) AS t1; -- sql2
|
+------+
|
| f1 |
|
+------+
|
| 2009 |
|
+------+
|
1 row in set, 1 warning (0.00 sec)
|
|
MariaDB [TEST]> select version();
|
+-------------------------+
|
| version() |
|
+-------------------------+
|
| 5.5.61-MariaDB-1~trusty |
|
+-------------------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [TEST]> SELECT PI()+~1&f2 AS f1 FROM (SELECT DATE_SUB(DATE_ADD('2009-10-15', INTERVAL 1 MICROSECOND), INTERVAL 1 DAY_SECOND) AS f2 FROM t) AS t1; -- sql1
|
+------+
|
| f1 |
|
+------+
|
| 0 |
|
+------+
|
1 row in set (0.00 sec)
|
|
MariaDB [TEST]> SELECT PI()+~1&f2 AS f1 FROM (SELECT DISTINCT DATE_SUB(DATE_ADD('2009-10-15', INTERVAL 1 MICROSECOND), INTERVAL 1 DAY_SECOND) AS f2 FROM t) AS t1; -- sql2
|
+------+
|
| f1 |
|
+------+
|
| 0 |
|
+------+
|
1 row in set, 1 warning (0.00 sec)
|
|