Wrong result with derived_merge=ON, DISTINCT or GROUP BY, EXISTS
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Critical
|
Timour Katchaounov |
Bug Description
The following query
SELECT alias1.*
FROM t1, ( SELECT * FROM t1 ) AS alias1
WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a )
with 10 rows in the table, each of which meets the WHERE condition, with derived_merge=ON returns 9 rows instead of 100.
If I remove at least one row from the table, the query starts producing the full resultset.
With derived_merge=OFF the query returns 100 rows as expected.
Same happens with GROUP BY instead of DISTINCT.
revno: 3324
revision-id: <email address hidden>
Reproducible on mariadb 5.2.9 (with default optimizer_switch) and on mysql 5.5.18.
EXPLAIN with derived_merge=on:
EXPLAIN SELECT alias1.* FROM t1, ( SELECT * FROM t1 ) AS alias1 WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index NULL b 5 NULL 10 Using index
1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
3 DEPENDENT SUBQUERY t1 range b b 5 NULL 6Using where; Using index for group-by; Using temporary
EXPLAIN with derived_merge=off:
EXPLAIN SELECT alias1.* FROM t1, ( SELECT * FROM t1 ) AS alias1 WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index NULL b 5 NULL 10 Using index
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
3 DEPENDENT SUBQUERY t1 range b b 5 NULL 6Range checked for each record (index map: 0x1); Using temporary
2 DERIVED t1 ALL NULL NULL NULL NULL 10
Minimal optimizer_switch: none (default)
Full optimizer_switch: index_merge=
Test case:
CREATE TABLE t1 ( a INT, b INT, KEY (b) );
INSERT INTO t1 VALUES
(100,10)
(105,15)
SELECT alias1.*
FROM t1, ( SELECT * FROM t1 ) AS alias1
WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ;
Related branches
Changed in maria: | |
milestone: | none → 5.3 |
assignee: | nobody → Igor Babaev (igorb-seattle) |
description: | updated |
Changed in maria: | |
milestone: | 5.3 → 5.2 |
importance: | Undecided → Critical |
status: | New → Confirmed |
assignee: | Igor Babaev (igorb-seattle) → Timour Katchaounov (timour) |
Changed in maria: | |
status: | Confirmed → In Progress |
While saying that the issue is reprodubile on mariadb 5.2 and mysql 5.5.18, I just mean that they produce the same wrong resultset.
mariadb 5.2.9:
EXPLAIN SELECT alias1.*
FROM t1, ( SELECT * FROM t1 ) AS alias1
WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index NULL b 5 NULL 10 Using index
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 10 Using where; Using join buffer
3 DEPENDENT SUBQUERY t1 range b b 5 NULL 6Using where; Using index for group-by; Using temporary
2 DERIVED t1 ALL NULL NULL NULL NULL 10
mysql 5.5.18:
EXPLAIN SELECT alias1.*
FROM t1, ( SELECT * FROM t1 ) AS alias1
WHERE EXISTS ( SELECT DISTINCT b FROM t1 WHERE b <= alias1.a ) ;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 index NULL b 5 NULL 10 Using index
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 10 Using where; Using join buffer
3 DEPENDENT SUBQUERY t1 range b b 5 NULL 6Using where; Using index for group-by; Using temporary
2 DERIVED t1 ALL NULL NULL NULL NULL 10