[MDEV-3051] LP:715069 - Wrong result with GROUP BY inside subquery and materialization=off in maria-5.3-mwl89 Created: 2011-02-08  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 LPexportBug715069.xml    

 Description   

Not repeatable with maria-5.3 . The query below does not return any rows even though the row 8,8 matches both the ON and the WHERE predicates.

CREATE TABLE t1 ( f1 int(11), f2 int(11), f10 varchar(1), PRIMARY KEY (f1)) ;
INSERT INTO t1 VALUES (8,8,'u'),(10,5,'o');

SET SESSION optimizer_switch = 'materialization=off';
SELECT alias2.f1 , alias2.f2
FROM t1 AS alias1
RIGHT JOIN t1 AS alias2 ON alias2.f10
WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT f2 , f1 FROM t1 GROUP BY f2 , f1 );

explain:

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY alias2 ALL NULL NULL NULL NULL 2 Using where
1 PRIMARY alias1 index NULL PRIMARY 4 NULL 2 Using where; Using index
2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using filesort

correct result:

SET SESSION optimizer_switch = 'materialization=on';
SELECT alias2.f1 , alias2.f2 FROM t1 AS alias1 RIGHT JOIN t1 AS alias2 ON alias2.f10 WHERE ( alias2.f1 , alias2.f2 ) IN ( SELECT f2 , f1 FROM t1 GROUP BY f2 , f1 );
f1 f2
8 8

 Comments   
Comment by Timour Katchaounov (Inactive) [ 2011-02-14 ]

Re: Wrong result with GROUP BY inside subquery and materialization=off in maria-5.3-mwl89
The bug is specific to the in-to-exists strategy. A simpler example:

SELECT alias2.f1 FROM t1 AS alias1 right JOIN t1 AS alias2 ON alias2.f10
WHERE ( alias2.f1 ) IN ( SELECT f2 FROM t1 GROUP BY f2, f1);

Comment by Timour Katchaounov (Inactive) [ 2011-04-04 ]

Re: Wrong result with GROUP BY inside subquery and materialization=off in maria-5.3-mwl89
It turns out the bug is not specific to IN-TO-EXISTS. The following simple test case shows this:

CREATE TABLE t1 (f1a int, f2a int not null, f3a varchar(3) not null, PRIMARY KEY (f1a)) ;
INSERT INTO t1 VALUES
(8,8,'a1a'),
(10,5,'b1b');

CREATE TABLE t2 (f1b int, f2b int not null, f3b varchar(3) not null, PRIMARY KEY (f1b)) ;
INSERT INTO t2 VALUES
(10,5,'d1d');

SET @@optimizer_switch = 'materialization=off,subquery_cache=off';
– wrong empty result
SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT f1a, f2a FROM t1 GROUP BY f1a, f2a);

SET @@optimizer_switch = 'materialization=on,subquery_cache=off';
– wrong empty result
SELECT * FROM t2 WHERE (f1b, f2b) IN (SELECT f1a, f2a FROM t1 GROUP BY f1a, f2a);

Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 715069

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