Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.3.22, 10.11.1
-
None
-
ubuntu 18.04
Description
Description:
In theory, the result of sql2(DISTINCT) ⊆ the result of sql1:
SELECT (-DAYNAME('2008-07-15')-f2 DIV 1) AS f1 FROM (SELECT 0 AS f2 FROM t HAVING 1) AS t1; -- sql1 |
SELECT DISTINCT (-DAYNAME('2008-07-15')-f2 DIV 1) AS f1 FROM (SELECT 0 AS f2 FROM t HAVING 1) AS t1; -- sql2 |
However, the value -0 changed to 0 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.01 sec) |
|
|
mysql> SELECT (-DAYNAME('2008-07-15')-f2 DIV 1) AS f1 FROM (SELECT 0 AS f2 FROM t HAVING 1) AS t1; -- sql1 |
+------+ |
| f1 |
|
+------+ |
| -0 |
|
+------+ |
1 row in set, 1 warning (0.00 sec) |
|
|
mysql> SELECT DISTINCT (-DAYNAME('2008-07-15')-f2 DIV 1) AS f1 FROM (SELECT 0 AS f2 FROM t HAVING 1) AS t1; -- sql2 |
+------+ |
| f1 |
|
+------+ |
| 0 |
|
+------+ |
1 row in set, 1 warning (0.00 sec) |
How to repeat:
drop table if exists t; |
CREATE TABLE t (c1 INT); |
INSERT INTO t VALUES (1); |
|
|
SELECT (-DAYNAME('2008-07-15')-f2 DIV 1) AS f1 FROM (SELECT 0 AS f2 FROM t HAVING 1) AS t1; -- sql1 |
SELECT DISTINCT (-DAYNAME('2008-07-15')-f2 DIV 1) AS f1 FROM (SELECT 0 AS f2 FROM t HAVING 1) AS t1; -- sql2 |
Hope these can be helpful for your debugging:
1. The bug cannot be reproduced after removing HAVING 1;
2. 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.22, it cannot be reproduced in mariadb:10.3.21:
MariaDB [TEST]> select version(); |
+----------------------------------------+ |
| version() |
|
+----------------------------------------+ |
| 10.3.22-MariaDB-1:10.3.22+maria~bionic |
|
+----------------------------------------+ |
1 row in set (0.000 sec) |
|
|
MariaDB [TEST]> SELECT (-DAYNAME('2008-07-15')-f2 DIV 1) AS f1 FROM (SELECT 0 AS f2 FROM t HAVING 1) AS t1; -- sql1 |
+------+ |
| f1 |
|
+------+ |
| -0 |
|
+------+ |
1 row in set, 1 warning (0.001 sec) |
|
|
MariaDB [TEST]> SELECT DISTINCT (-DAYNAME('2008-07-15')-f2 DIV 1) AS f1 FROM (SELECT 0 AS f2 FROM t HAVING 1) AS t1; -- sql2 |
+------+ |
| f1 |
|
+------+ |
| 0 |
|
+------+ |
1 row in set, 1 warning (0.001 sec) |
|
|
MariaDB [TEST]> select version(); |
+----------------------------------------+ |
| version() |
|
+----------------------------------------+ |
| 10.3.21-MariaDB-1:10.3.21+maria~bionic |
|
+----------------------------------------+ |
1 row in set (0.000 sec) |
|
|
MariaDB [TEST]> SELECT (-DAYNAME('2008-07-15')-f2 DIV 1) AS f1 FROM (SELECT 0 AS f2 FROM t HAVING 1) AS t1; -- sql1 |
+------+ |
| f1 |
|
+------+ |
| -1 |
|
+------+ |
1 row in set (0.000 sec) |
|
|
MariaDB [TEST]> SELECT DISTINCT (-DAYNAME('2008-07-15')-f2 DIV 1) AS f1 FROM (SELECT 0 AS f2 FROM t HAVING 1) AS t1; -- sql2 |
+------+ |
| f1 |
|
+------+ |
| -1 |
|
+------+ |
1 row in set (0.000 sec) |
|