Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5.4, 10.11.1, 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
-
ubuntu 18.04
Description
Description:
In theory, the result of sql1 ⊆ the result of sql2:
SELECT (~c1*ABS(0.8)|DATE_ADD('2017-11-22', INTERVAL 1 WEEK)) AS `f1` FROM t HAVING !(`f1`=1); -- sql1 |
SELECT (~c1*ABS(0.8)|DATE_ADD('2017-11-22', INTERVAL 1 WEEK)) AS `f1` FROM t HAVING 1; -- sql2 |
Because the 'HAVING 1' in sql2 is always true, but the 'HAVING !(`f1`=1)' in sql1 may not be true.
However, the value `14757395258967642091` changed to `14757395258987761147` after relaxing HAVING condition, 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 (~c1*ABS(0.8)|DATE_ADD('2017-11-22', INTERVAL 1 WEEK)) AS `f1` FROM t HAVING !(`f1`=1); -- sql1 |
+----------------------+
|
| f1 |
|
+----------------------+
|
| 14757395258967642091 | |
+----------------------+
|
1 row in set, 2 warnings (0.01 sec) |
|
|
mysql> SELECT (~c1*ABS(0.8)|DATE_ADD('2017-11-22', INTERVAL 1 WEEK)) AS `f1` FROM t HAVING 1; -- sql2 |
+----------------------+
|
| f1 |
|
+----------------------+
|
| 14757395258987761147 | |
+----------------------+
|
1 row in set (0.00 sec) |
How to repeat:
drop table if exists t; |
CREATE TABLE t (c1 VARCHAR(20)); |
INSERT INTO t VALUES ('\n3'); |
|
|
SELECT (~c1*ABS(0.8)|DATE_ADD('2017-11-22', INTERVAL 1 WEEK)) AS `f1` FROM t HAVING !(`f1`=1); -- sql1 |
SELECT (~c1*ABS(0.8)|DATE_ADD('2017-11-22', INTERVAL 1 WEEK)) AS `f1` FROM t HAVING 1; -- 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.5.4, it cannot be reproduced in mariadb:10.5.3:
MariaDB [TEST]> select version();
|
+-------------------------------------+
|
| version() |
|
+-------------------------------------+
|
| 10.5.4-MariaDB-1:10.5.4+maria~focal | |
+-------------------------------------+
|
1 row in set (0.000 sec) |
|
|
MariaDB [TEST]> SELECT (~c1*ABS(0.8)|DATE_ADD('2017-11-22', INTERVAL 1 WEEK)) AS `f1` FROM t HAVING !(`f1`=1); -- sql1 |
+----------------------+
|
| f1 |
|
+----------------------+
|
| 14757395258967642091 | |
+----------------------+
|
1 row in set, 2 warnings (0.000 sec) |
|
|
MariaDB [TEST]> SELECT (~c1*ABS(0.8)|DATE_ADD('2017-11-22', INTERVAL 1 WEEK)) AS `f1` FROM t HAVING 1; -- sql2 |
+----------------------+
|
| f1 |
|
+----------------------+
|
| 14757395258987761147 | |
+----------------------+
|
1 row in set (0.000 sec) |
|
|
MariaDB [TEST]> select version();
|
+-------------------------------------+
|
| version() |
|
+-------------------------------------+
|
| 10.5.3-MariaDB-1:10.5.3+maria~focal | |
+-------------------------------------+
|
1 row in set (0.000 sec) |
|
|
MariaDB [TEST]> SELECT (~c1*ABS(0.8)|DATE_ADD('2017-11-22', INTERVAL 1 WEEK)) AS `f1` FROM t HAVING !(`f1`=1); -- sql1 |
+---------------------+
|
| f1 |
|
+---------------------+
|
| 9223372036854775807 | |
+---------------------+
|
1 row in set, 4 warnings (0.000 sec) |
|
|
MariaDB [TEST]> SELECT (~c1*ABS(0.8)|DATE_ADD('2017-11-22', INTERVAL 1 WEEK)) AS `f1` FROM t HAVING 1; -- sql2 |
+---------------------+
|
| f1 |
|
+---------------------+
|
| 9223372036854775807 | |
+---------------------+
|
1 row in set, 1 warning (0.000 sec) |