[MDEV-14349] Wrong result (extra rows) on select from view with subqueries and ANY Created: 2017-11-10  Updated: 2023-11-28

Status: Confirmed
Project: MariaDB Server
Component/s: Data Manipulation - Subquery, Optimizer, Views
Affects Version/s: 5.5, 10.1, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Igor Babaev
Resolution: Unresolved Votes: 0
Labels: 11.0-sel


 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.

 Comments   
Comment by Elena Stepanova [ 2020-01-20 ]

Still reproducible on all of 5.5-10.5.

Generated at Thu Feb 08 08:12:52 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.