Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Wrong result with nested correlated subqueries, OR condition
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
Re: Wrong result with nested correlated subqueries, OR condition
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!
Launchpad bug id: 833702
Re: Wrong result with nested correlated subqueries, OR condition
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.