Wrong result with nested IN and singlerow subqueries and equality propagation

Bug #833702 reported by Philip Stoev
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MariaDB
Fix Released
High
Timour Katchaounov

Bug Description

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.

Related branches

Changed in maria:
milestone: none → 5.3
Revision history for this message
Sergey Petrunia (sergefp) wrote :

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.

Changed in maria:
assignee: nobody → Sergey Petrunia (sergefp)
Changed in maria:
assignee: Sergey Petrunia (sergefp) → Timour Katchaounov (timour)
Changed in maria:
status: New → Confirmed
importance: Undecided → High
Changed in maria:
status: Confirmed → In Progress
Revision history for this message
Timour Katchaounov (timour) wrote :

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

Revision history for this message
Timour Katchaounov (timour) wrote :

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!

summary: - Wrong result with nested correlated subqueries, OR condition
+ Wrong result with nested IN and singlerow subqueries and equality
+ propagation
Changed in maria:
status: In Progress → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers