Wrong result with GROUP BY inside subquery and materialization=off in maria-5.3-mwl89
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Timour Katchaounov |
Bug 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'
SET SESSION optimizer_switch = 'materializatio
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 = 'materializatio
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
Related branches
Changed in maria: | |
milestone: | none → 5.3 |
assignee: | nobody → Timour Katchaounov (timour) |
Changed in maria: | |
status: | Confirmed → In Progress |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |
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);