Wrong result with nested IN and singlerow subqueries and equality propagation

[Philip Stoev]

The following query:

SELECT * FROM t2
WHERE t2.b IN (
 SELECT b
 FROM t3
 WHERE t3.a = t2.a
 AND a < SOME (SELECT * FROM t4)
) OR ( t2.c > 242 );

returns no rows. Expected result:

+------+------+------+
| c | a | b |
+------+------+------+
| 10 | 7 | 0 |
+------+------+------+

explain:

+----+--------------------+-------+--------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+---------------+------+---------+------+------+-------------+
| 1 | PRIMARY | t2 | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DEPENDENT SUBQUERY | t3 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
| 3 | SUBQUERY | t4 | ALL | NULL | NULL | NULL | NULL | 2 | |
+----+--------------------+-------+--------+---------------+------+---------+------+------+-------------+
3 rows in set (0.00 sec)

Not influenced by any particular switch. full 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=off,derived_merge=off,derived_with_keys=off,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=off,mrr_cost_based=off,mrr_sort_keys=off,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=off,table_elimination=on

bzr version-info:

revision-id: <email address hidden>
date: 2011-08-23 15:51:47 +0300
build-date: 2011-08-25 14:52:56 +0300
revno: 3166
branch-nick: maria-5.3

test case:

CREATE TABLE t2 ( c int , a int, b int);
INSERT INTO t2 VALUES (10,7,0);

CREATE TABLE t3 ( a int, b int) ;
INSERT INTO t3 VALUES (5,0),(7,0);

CREATE TABLE t4 (a int);
INSERT INTO t4 VALUES (2),(8);

SELECT * FROM t2
WHERE t2.b IN (
 SELECT b
 FROM t3
 WHERE t3.a = t2.a
 AND a < SOME (SELECT * FROM t4)
) OR ( t2.c > 242 );

Reproducible with maria-5.3. Not reproducible with maria 5.2, mysql-5.5. Does not involve NULLS, empty or constant tables.

[Sergey Petrunia (sergefp)]

The problem can be still repeated when one has set optimizer_switch='semijoin=off' or 'materialization=off', or both.

Seems to be a generic subquery code problem.

[Timour Katchaounov (timour)]

The problem is related to IN-TO_EXISTS, and not to materialization.
The initial example cannot show this because the subquery is correlated,
and materialization is not applicable.

Also the OR in the outer query is not relevant.

The following simpler example demonstrates this:

set @@optimizer_switch='in_to_exists=off,materialization=on';
SELECT * FROM t2 WHERE t2.b IN (SELECT b FROM t3 WHERE a < ANY (SELECT * FROM t4) and t3.a = 7);
+------+------+------+
| c | a | b |
+------+------+------+
| 10 | 7 | 0 |
+------+------+------+

set @@optimizer_switch='in_to_exists=on,materialization=off';
SELECT * FROM t2 WHERE t2.b IN (SELECT b FROM t3 WHERE a < ANY (SELECT * FROM t4) and t3.a = 7);

=> empty result

[Timour Katchaounov (timour)]

The following query has a TRUE WHERE clause for the IN subquery,
thus it's IN predicate must be TRUE in the same way as the previous query.

set @@optimizer_switch='in_to_exists=off,materialization=on,subquery_cache=off';
SELECT * FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.a < ANY (SELECT * FROM t4) and t3.b = 0);
--> (10, 7, 0)

set @@optimizer_switch='in_to_exists=on,materialization=off,subquery_cache=off';
SELECT * FROM t2 WHERE t2.b IN (SELECT t3.b FROM t3 WHERE t3.a < ANY (SELECT * FROM t4) and t3.b = 0);
--> (10, 7, 0)

SELECT * FROM t2 WHERE t2.b IN (SELECT b FROM t3 WHERE a < ANY (SELECT * FROM t4) and t3.a = 7);
--> empty result, should be same as above => BUG!