Wrong result for MIN/MAX on an indexed column with materialization and semijoin
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Critical
|
Sergey Petrunia |
Bug Description
The following query
SELECT MIN(a) FROM t1, t2
WHERE b IN
( SELECT c FROM t3 GROUP BY c );
produces NULL while running with materialization=on and semijoin=on, and a numeric result otherwise. The latter is correct.
revno: 3334
revision-id: <email address hidden>
Reproducible on 3315 as well.
Minimal optimizer_switch: none required (materialization=on and semijoin=on by default)
Full optimizer_switch: index_merge=
EXPLAIN EXTENDED SELECT MIN(a) FROM t1, t2
WHERE b IN
( SELECT c FROM t3 GROUP BY c );
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 5 const 1 100.00
2 MATERIALIZED t3 system NULL NULL NULL NULL 1 100.00
Warnings:
Note 1003 select min(1) AS `MIN(a)` from <materialize> (select 2 from `test`.`t3` group by 2) join `test`.`t1` join `test`.`t2` where (`<subquery2>`.`c` = 2)
Explain without materialization or without semijoin (correct result):
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
1 PRIMARY t2 system NULL NULL NULL NULL 1
2 DEPENDENT SUBQUERY t3 system NULL NULL NULL NULL 1
Test case:
CREATE TABLE t1 ( a INT, KEY(a) );
INSERT INTO t1 VALUES (1);
CREATE TABLE t2 ( b INT );
INSERT INTO t2 VALUES (2);
CREATE TABLE t3 ( c INT );
INSERT INTO t3 VALUES (2);
SELECT MIN(a) FROM t1, t2
WHERE b IN
( SELECT c FROM t3 GROUP BY c );
# End of test case
# Note: the same result can be achieved using only one table with one column,
# see below; but the first test case looked more natural and readable to me.
CREATE TABLE t1 ( a INT, KEY(a) );
INSERT INTO t1 VALUES (1);
SELECT MIN(alias1.a) FROM t1 alias1, t1 alias2
WHERE alias2.a IN
( SELECT a FROM t1 GROUP BY a );
summary: |
- Wrong result for MIN/MAX on an indexed column with materialization + Wrong result for MIN/MAX on an indexed column with materialization and + semijoin |
description: | updated |
description: | updated |
Changed in maria: | |
milestone: | none → 5.3 |
Changed in maria: | |
assignee: | nobody → Sergey Petrunia (sergefp) |
Changed in maria: | |
importance: | Undecided → Critical |
status: | New → Confirmed |
Changed in maria: | |
status: | Confirmed → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |