Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.0.15, 10.11.1
-
ubuntu 18.04
Description
Description:
In theory, the result of sql2(DISTINCT) ⊆ the result of sql1:
SELECT ~f1 FROM (SELECT (DATE_ADD(REPEAT(c1, 3), INTERVAL 1 MINUTE_MICROSECOND)) AS f1 FROM t) AS t1; -- sql1 |
SELECT ~f1 FROM (SELECT DISTINCT (DATE_ADD(REPEAT(c1, 3), INTERVAL 1 MINUTE_MICROSECOND)) AS f1 FROM t) AS t1; -- sql2 |
However, the value 18446744063608551615 changed to 18446744073709551614 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 ~f1 FROM (SELECT (DATE_ADD(REPEAT(c1, 3), INTERVAL 1 MINUTE_MICROSECOND)) AS f1 FROM t) AS t1; -- sql1 |
+----------------------+ |
| ~f1 |
|
+----------------------+ |
| 18446744063608551615 |
|
+----------------------+ |
1 row in set (0.00 sec) |
|
|
mysql> SELECT ~f1 FROM (SELECT DISTINCT (DATE_ADD(REPEAT(c1, 3), INTERVAL 1 MINUTE_MICROSECOND)) AS f1 FROM t) AS t1; -- sql2 |
+----------------------+ |
| ~f1 |
|
+----------------------+ |
| 18446744073709551614 |
|
+----------------------+ |
1 row in set, 1 warning (0.00 sec) |
How to repeat:
drop table if exists t; |
CREATE TABLE t (c1 DECIMAL(40,20)); |
INSERT INTO t VALUES (1); |
|
|
SELECT ~f1 FROM (SELECT (DATE_ADD(REPEAT(c1, 3), INTERVAL 1 MINUTE_MICROSECOND)) AS f1 FROM t) AS t1; -- sql1 |
SELECT ~f1 FROM (SELECT DISTINCT (DATE_ADD(REPEAT(c1, 3), INTERVAL 1 MINUTE_MICROSECOND)) AS f1 FROM t) AS t1; -- sql2 |
Hope these can be helpful for your debugging:
1. The bug cannot be reproduced after changing REPEAT(c1, 3) to REPEAT(c1, 2);
2. The bug cannot be reproduced after changing MINUTE_MICROSECOND to SECOND;
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:10.0.15, it cannot be reproduced in mariadb:5.5.64 (we can't find more intermediate versions in dockerhub):
MariaDB [TEST]> select version(); |
+------------------------------+ |
| version() |
|
+------------------------------+ |
| 10.0.15-MariaDB-1~wheezy-log |
|
+------------------------------+ |
1 row in set (0.00 sec) |
|
|
MariaDB [TEST]> SELECT ~f1 FROM (SELECT (DATE_ADD(REPEAT(c1, 3), INTERVAL 1 MINUTE_MICROSECOND)) AS f1 FROM t) AS t1; -- sql1 |
+----------------------+ |
| ~f1 |
|
+----------------------+ |
| 18446744063608551615 |
|
+----------------------+ |
1 row in set (0.00 sec) |
|
|
MariaDB [TEST]> SELECT ~f1 FROM (SELECT DISTINCT (DATE_ADD(REPEAT(c1, 3), INTERVAL 1 MINUTE_MICROSECOND)) AS f1 FROM t) AS t1; -- sql2 |
+----------------------+ |
| ~f1 |
|
+----------------------+ |
| 18446744073709551614 |
|
+----------------------+ |
1 row in set, 1 warning (0.00 sec) |
|
|
MariaDB [TEST]> select version(); |
+-------------------------+ |
| version() |
|
+-------------------------+ |
| 5.5.64-MariaDB-1~trusty |
|
+-------------------------+ |
1 row in set (0.00 sec) |
|
|
MariaDB [TEST]> SELECT ~f1 FROM (SELECT (DATE_ADD(REPEAT(c1, 3), INTERVAL 1 MINUTE_MICROSECOND)) AS f1 FROM t) AS t1; -- sql1 |
+------+ |
| ~f1 |
|
+------+ |
| NULL | |
+------+ |
1 row in set, 2 warnings (0.00 sec) |
|
|
MariaDB [TEST]> SELECT ~f1 FROM (SELECT DISTINCT (DATE_ADD(REPEAT(c1, 3), INTERVAL 1 MINUTE_MICROSECOND)) AS f1 FROM t) AS t1; -- sql2 |
+------+ |
| ~f1 |
|
+------+ |
| NULL | |
+------+ |
1 row in set, 2 warnings (0.00 sec) |
Attachments
Issue Links
- relates to
-
-
- Confirmed
-