|
Description:
In theory, the result of sql1 ⊆ the result of sql2:
SELECT c1%'a' AS `f1` FROM (SELECT c1 FROM t) AS `t1` WHERE (CONCAT_WS(0, 0.01, c1)) OR (NULL>=ALL (SELECT 1 FROM t)) HAVING NOT ((`f1` != 1) IS FALSE) ORDER BY c1; -- sql1
|
SELECT c1%'a' AS `f1` FROM (SELECT c1 FROM t) AS `t1` WHERE (CONCAT_WS(0, 0.01, c1)) OR (NULL>=ALL (SELECT 1 FROM t)) HAVING 1 ORDER BY c1; -- sql2
|
Because the 'HAVING 1' in sql2 is always true, but the 'HAVING NOT ((`f1` != 1) IS FALSE)' in sql1 may not be true.
However, I can't find my line 'NULL' after changing 'HAVING NOT ((`f1` != 1) IS FALSE)' to 'HAVING 1', 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%'a' AS `f1` FROM (SELECT c1 FROM t) AS `t1` WHERE (CONCAT_WS(0, 0.01, c1)) OR (NULL>=ALL (SELECT 1 FROM t)) HAVING NOT ((`f1` != 1) IS FALSE) ORDER BY c1; -- sql1
|
+------+
|
| f1 |
|
+------+
|
| NULL |
|
+------+
|
1 row in set, 4 warnings (0.00 sec)
|
|
mysql> SELECT c1%'a' AS `f1` FROM (SELECT c1 FROM t) AS `t1` WHERE (CONCAT_WS(0, 0.01, c1)) OR (NULL>=ALL (SELECT 1 FROM t)) HAVING 1 ORDER BY c1; -- sql2
|
Empty set, 1 warning (0.00 sec)
|
How to repeat:
drop table if exists t;
|
CREATE TABLE t (c1 CHAR(20));
|
INSERT INTO t VALUES ('1');
|
|
SELECT c1%'a' AS `f1` FROM (SELECT c1 FROM t) AS `t1` WHERE (CONCAT_WS(0, 0.01, c1)) OR (NULL>=ALL (SELECT 1 FROM t)) HAVING NOT ((`f1` != 1) IS FALSE) ORDER BY c1; -- sql1
|
SELECT c1%'a' AS `f1` FROM (SELECT c1 FROM t) AS `t1` WHERE (CONCAT_WS(0, 0.01, c1)) OR (NULL>=ALL (SELECT 1 FROM t)) HAVING 1 ORDER BY c1; -- sql2
|
Hope these can be helpful for your debugging:
1. The bug cannot be reproduced after removing ORDER BY;
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:5.5.61, it cannot be reproduced in mariadb:5.5.60:
MariaDB [TEST]> select version();
|
+-------------------------+
|
| version() |
|
+-------------------------+
|
| 5.5.61-MariaDB-1~trusty |
|
+-------------------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [TEST]> SELECT c1%'a' AS `f1` FROM (SELECT c1 FROM t) AS `t1` WHERE (CONCAT_WS(0, 0.01, c1)) OR (NULL>=ALL (SELECT 1 FROM t)) HAVING NOT ((`f1` != 1) IS FALSE) ORDER BY c1; -- sql1
|
+------+
|
| f1 |
|
+------+
|
| NULL |
|
+------+
|
1 row in set, 2 warnings (0.00 sec)
|
|
MariaDB [TEST]> SELECT c1%'a' AS `f1` FROM (SELECT c1 FROM t) AS `t1` WHERE (CONCAT_WS(0, 0.01, c1)) OR (NULL>=ALL (SELECT 1 FROM t)) HAVING 1 ORDER BY c1; -- sql2
|
Empty set (0.00 sec)
|
|
MariaDB [TEST]> select version();
|
+-------------------------+
|
| version() |
|
+-------------------------+
|
| 5.5.60-MariaDB-1~trusty |
|
+-------------------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [TEST]> SELECT c1%'a' AS `f1` FROM (SELECT c1 FROM t) AS `t1` WHERE (CONCAT_WS(0, 0.01, c1)) OR (NULL>=ALL (SELECT 1 FROM t)) HAVING NOT ((`f1` != 1) IS FALSE) ORDER BY c1; -- sql1
|
+------+
|
| f1 |
|
+------+
|
| NULL |
|
+------+
|
1 row in set, 2 warnings (0.00 sec)
|
|
MariaDB [TEST]> SELECT c1%'a' AS `f1` FROM (SELECT c1 FROM t) AS `t1` WHERE (CONCAT_WS(0, 0.01, c1)) OR (NULL>=ALL (SELECT 1 FROM t)) HAVING 1 ORDER BY c1; -- sql2
|
+------+
|
| f1 |
|
+------+
|
| NULL |
|
+------+
|
1 row in set, 1 warning (0.00 sec)
|
|