Wrong result with derived table, impossible where , derived_merge=on and EXISTS in maria-5.3

[Philip Stoev]

Requires derived_merge=on .Not repeatable before WL#106 .

The following query

SELECT *
FROM (SELECT * FROM t2 ) AS alias1
WHERE EXISTS (
 SELECT t3.f2
 FROM t3 , t1
 WHERE t1.f3 = t3.f3
 AND alias1.f2 != 0
) ;

returns no rows when executed with derived merge and 1 row when executed with no derived table or with derived_merge=off.

Explain:

| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 3 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |

minimal optimizer switch: derived_merge=off
entire optimizer_switch:

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=off,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on

test case:

CREATE TABLE t1 ( f3 int) ;
INSERT IGNORE INTO t1 VALUES (8),(0);

CREATE TABLE t2 ( f2 int) ;
INSERT IGNORE INTO t2 VALUES (4),(NULL);

CREATE TABLE t3 ( f2 int, f3 int) ;
INSERT IGNORE INTO t3 VALUES (7,8);

SELECT *
FROM (SELECT * FROM t2 ) AS alias1
WHERE EXISTS (
 SELECT t3.f2
 FROM t3 , t1
 WHERE t1.f3 = t3.f3
 AND alias1.f2 != 0
) ;

[Philip Stoev (pstoev-askmonty)]

 bzr version-info
revision-id: <email address hidden>
date: 2011-07-06 10:30:51 +0400
build-date: 2011-07-06 17:33:36 +0300
revno: 3085
branch-nick: maria-5.3

[Igor Babaev (igorb-seattle)]

This bug can be reproduced with a view instead if the derived table:

MariaDB [test]> CREATE VIEW v2 AS SELECT * FROM t2;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> EXPLAIN SELECT * FROM v2 AS alias1 WHERE EXISTS ( SELECT t3.f2 FROM t3 , t1 WHERE t1.f3 = t3.f3 AND alias1.f2 != 0 );
+----+--------------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+--------------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
2 rows in set (0.00 sec)

[Igor Babaev (igorb-seattle)]

The bug existed before the merge with MWL#106.

The following demonstrates this for rev #3048:

MariaDB [test]> CREATE TABLE t1 (a int) ;
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> INSERT INTO t1 VALUES (4), (NULL);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [test]> CREATE TABLE t2 (a int) ;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> INSERT INTO t2 VALUES (8), (0);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [test]> CREATE TABLE t3 (a int, b int) ;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> INSERT INTO t3 VALUES (7,8);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> CREATE VIEW v1 AS SELECT * FROM t1;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> SELECT * FROM v1 t
    -> WHERE EXISTS (SELECT t3.a FROM t3, t2
    -> WHERE t2.a = t3.b AND t.a != 0);
Empty set (0.01 sec)

MariaDB [test]> EXPLAIN
    -> SELECT * FROM v1 t
    -> WHERE EXISTS (SELECT t3.a FROM t3, t2
    -> WHERE t2.a = t3.b AND t.a != 0);
+----+--------------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+--------------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
2 rows in set (0.00 sec)