[MDEV-3523] LP:833702 - Wrong result with nested IN and singlerow subqueries and equality propagation Created: 2011-08-25  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Philip Stoev (Inactive) Assignee: Timour Katchaounov (Inactive)
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug833702.xml    

 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.

 Comments   
Comment by Sergei Petrunia [ 2011-08-26 ]

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.

Comment by Timour Katchaounov (Inactive) [ 2011-10-28 ]

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

Comment by Timour Katchaounov (Inactive) [ 2011-10-30 ]

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!

Comment by Rasmus Johansson (Inactive) [ 2011-11-01 ]

Launchpad bug id: 833702

Generated at Thu Feb 08 06:49:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.