Details
Description
Description:
In theory, the result of sql2(DISTINCT) ⊆ the result of sql1:
SELECT (LAST_DAY('2004-12-20 21:48:16') DIV c1) AS f1 FROM t; -- sql1 |
SELECT DISTINCT (LAST_DAY('2004-12-20 21:48:16') DIV c1) AS f1 FROM t; -- sql2 |
However, the value -200412310000 changed to -2147483648 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 (LAST_DAY('2004-12-20 21:48:16') DIV c1) AS f1 FROM t; -- sql1 |
+---------------+ |
| f1 |
|
+---------------+ |
| -200412310000 |
|
+---------------+ |
1 row in set (0.00 sec) |
|
|
mysql> SELECT DISTINCT (LAST_DAY('2004-12-20 21:48:16') DIV c1) AS f1 FROM t; -- sql2 |
+-------------+ |
| f1 |
|
+-------------+ |
| -2147483648 |
|
+-------------+ |
1 row in set (0.00 sec) |
How to repeat:
drop table if exists t; |
CREATE TABLE t (c1 DECIMAL(40,20)); |
INSERT INTO t VALUES (-0.0001); |
|
|
SELECT (LAST_DAY('2004-12-20 21:48:16') DIV c1) AS f1 FROM t; -- sql1 |
SELECT DISTINCT (LAST_DAY('2004-12-20 21:48:16') DIV c1) AS f1 FROM t; -- sql2 |
Hope these can be helpful for your debugging:
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.3.12, it cannot be reproduced in mariadb:10.3.11:
MariaDB [TEST]> select version(); |
+----------------------------------------+ |
| version() |
|
+----------------------------------------+ |
| 10.3.12-MariaDB-1:10.3.12+maria~bionic |
|
+----------------------------------------+ |
1 row in set (0.000 sec) |
|
|
MariaDB [TEST]> SELECT (LAST_DAY('2004-12-20 21:48:16') DIV c1) AS f1 FROM t; -- sql1 |
+---------------+ |
| f1 |
|
+---------------+ |
| -200412310000 |
|
+---------------+ |
1 row in set (0.000 sec) |
|
|
MariaDB [TEST]> SELECT DISTINCT (LAST_DAY('2004-12-20 21:48:16') DIV c1) AS f1 FROM t; -- sql2 |
+-------------+ |
| f1 |
|
+-------------+ |
| -2147483648 |
|
+-------------+ |
1 row in set (0.000 sec) |
|
|
MariaDB [TEST]> select version(); |
+----------------------------------------+ |
| version() |
|
+----------------------------------------+ |
| 10.3.11-MariaDB-1:10.3.11+maria~bionic |
|
+----------------------------------------+ |
1 row in set (0.000 sec) |
|
|
MariaDB [TEST]> SELECT (LAST_DAY('2004-12-20 21:48:16') DIV c1) AS f1 FROM t; -- sql1 |
+---------------+ |
| f1 |
|
+---------------+ |
| -200412310000 |
|
+---------------+ |
1 row in set (0.000 sec) |
|
|
MariaDB [TEST]> SELECT DISTINCT (LAST_DAY('2004-12-20 21:48:16') DIV c1) AS f1 FROM t; -- sql2 |
+---------------+ |
| f1 |
|
+---------------+ |
| -200412310000 |
|
+---------------+ |
1 row in set (0.000 sec) |
Attachments
Issue Links
- relates to
-
MDEV-30250 Value changed after relaxing ON condition [5.5.54, 10.11.1]
-
- Confirmed
-