Details
Description
Description:
In theory, the result of sql1 ⊆ the result of sql2:
SELECT 1 FROM (SELECT c1 AS f1 FROM t) AS t1 WHERE ((-f1)=ANY (SELECT c2 FROM t)) OR ((~1>=ALL (SELECT c1 FROM t)) IS F |
LSE); -- sql1
|
SELECT 1 FROM (SELECT c1 AS f1 FROM t) AS t1 WHERE ((-f1)>=ANY (SELECT c2 FROM t)) OR ((~1>=ALL (SELECT c1 FROM t)) IS |
FALSE); -- sql2
|
Because the condition >=ANY in sql2 is more relaxed than the condition =ANY in sql1.
However, MariaDB tells us the result of sql2(empty) ⊆ result of sql1(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 1 FROM (SELECT c1 AS f1 FROM t) AS t1 WHERE ((-f1)=ANY (SELECT c2 FROM t)) OR ((~1>=ALL (SELECT c1 FROM t)) IS FALSE); -- sql1 |
+---+
|
| 1 | |
+---+
|
| 1 | |
+---+
|
1 row in set (0.01 sec) |
|
|
mysql> SELECT 1 FROM (SELECT c1 AS f1 FROM t) AS t1 WHERE ((-f1)>=ANY (SELECT c2 FROM t)) OR ((~1>=ALL (SELECT c1 FROM t)) IS FALSE); -- sql2 |
Empty set (0.00 sec) |
How to repeat:
drop table if exists t; |
CREATE TABLE t (c1 FLOAT,c2 VARCHAR(20),key(c1)); |
INSERT INTO t VALUES (94.1106,'-0'),(1,'3 '),(0.0001,'-1'); |
|
|
SELECT 1 FROM (SELECT c1 AS f1 FROM t) AS t1 WHERE ((-f1)=ANY (SELECT c2 FROM t)) OR ((~1>=ALL (SELECT c1 FROM t)) IS FALSE); -- sql1 |
SELECT 1 FROM (SELECT c1 AS f1 FROM t) AS t1 WHERE ((-f1)>=ANY (SELECT c2 FROM t)) OR ((~1>=ALL (SELECT c1 FROM t)) IS FALSE); -- sql2 |
Hope these can be helpful for your debugging:
1. The bug cannot be reproduced after removing key(c1);
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.5.3, it cannot be reproduced in mariadb:10.5.2:
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 1 FROM (SELECT c1 AS f1 FROM t) AS t1 WHERE ((-f1)=ANY (SELECT c2 FROM t)) OR ((~1>=ALL (SELECT c1 FROM t)) IS FALSE); -- sql1 |
+---+
|
| 1 | |
+---+
|
| 1 | |
+---+
|
1 row in set (0.000 sec) |
|
|
MariaDB [TEST]> SELECT 1 FROM (SELECT c1 AS f1 FROM t) AS t1 WHERE ((-f1)>=ANY (SELECT c2 FROM t)) OR ((~1>=ALL (SELECT c1 FROM t)) IS FALSE); -- sql2 |
Empty set (0.000 sec) |
|
|
MariaDB [TEST]> select version();
|
+--------------------------------------+
|
| version() |
|
+--------------------------------------+
|
| 10.5.2-MariaDB-1:10.5.2+maria~bionic | |
+--------------------------------------+
|
1 row in set (0.000 sec) |
|
|
MariaDB [TEST]> SELECT 1 FROM (SELECT c1 AS f1 FROM t) AS t1 WHERE ((-f1)=ANY (SELECT c2 FROM t)) OR ((~1>=ALL (SELECT c1 FROM t)) IS FALSE); -- sql1 |
+---+
|
| 1 | |
+---+
|
| 1 | |
| 1 | |
| 1 | |
+---+
|
3 rows in set (0.000 sec) |
|
|
MariaDB [TEST]> SELECT 1 FROM (SELECT c1 AS f1 FROM t) AS t1 WHERE ((-f1)>=ANY (SELECT c2 FROM t)) OR ((~1>=ALL (SELECT c1 FROM t)) IS FALSE); -- sql2 |
+---+
|
| 1 | |
+---+
|
| 1 | |
| 1 | |
| 1 | |
+---+
|
3 rows in set (0.000 sec) |
Attachments
Issue Links
- is duplicated by
-
MDEV-34499 Logically, the return of >=ANY should contain =ANY
-
- Closed
-