Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
5.5, 10.1, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Description
CREATE TABLE t1 (a INT); |
INSERT INTO t1 VALUES (1),(2); |
|
|
CREATE TABLE t2 (b INT); |
INSERT INTO t2 VALUES (3),(4); |
|
|
CREATE TABLE t3 (c INT); |
INSERT INTO t3 VALUES (5),(6); |
|
|
SELECT * FROM t1 WHERE a <> ANY ( |
SELECT b FROM t2 WHERE b IN ( 1 <> ANY ( SELECT c FROM t3 ) ) |
);
|
|
|
CREATE OR REPLACE VIEW v AS |
SELECT * FROM t1 WHERE a <> ANY ( |
SELECT b FROM t2 WHERE b IN ( 1 <> ANY ( SELECT c FROM t3 ) ) |
);
|
|
|
SELECT * FROM v; |
|
|
DROP VIEW v; |
DROP TABLE t1, t2, t3; |
I think the result of the original query, without the view, is correct, because:
- the inner-most subquery should return (5,6)
- 1 <> ANY(...) should return false;
- b IN (...) should return false;
- SELECT ... FROM t2 should return empty set;
- a <> ANY(...) should return false;
- SELECT ... FROM t1 should be empty.
However it's sometimes hard to predict what the standard says in regard to a combination of ANY, <> and IN. In any case, the results should be obviously the same, whether the query is executed directly or via a view, which does not happen now.
MariaDB [test]> SELECT * FROM t1 WHERE a <> ANY ( |
-> SELECT b FROM t2 WHERE b IN ( 1 <> ANY ( SELECT c FROM t3 ) ) |
-> );
|
Empty set (0.00 sec) |
MariaDB [test]> CREATE OR REPLACE VIEW v AS |
-> SELECT * FROM t1 WHERE a <> ANY ( |
-> SELECT b FROM t2 WHERE b IN ( 1 <> ANY ( SELECT c FROM t3 ) ) |
-> );
|
Query OK, 0 rows affected (0.10 sec) |
|
|
MariaDB [test]>
|
MariaDB [test]> SELECT * FROM v; |
+------+ |
| a |
|
+------+ |
| 1 |
|
| 2 |
|
+------+ |
2 rows in set (0.00 sec) |
Also reproducible on MySQL 5.5-5.7.