Wrong result with ICP, outer join, subquery in maria-5.3-icp
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Sergey Petrunia |
Bug Description
The following query:
SELECT *
FROM t1
LEFT JOIN t2
ON 1
WHERE ( t2.b ) NOT IN (
SELECT t4.b
FROM t3, t4
WHERE t4.b <= 2
AND t4.c = t3.c
);
returns no rows when executed with maria-5.3-icp and index_condition
explain:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 1
1 PRIMARY t2 ALL NULL NULL NULL NULL 1 Using where
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 1
2 DEPENDENT SUBQUERY t4 ref_or_null b b 5 func 2 Using index condition; Using where; Full scan on NULL key
minimal switch: index_condition
full switch: index_merge=
bzr version-info
revision-id: <email address hidden>
date: 2011-11-06 01:23:03 -0700
build-date: 2011-11-07 10:39:33 +0200
revno: 3269
branch-nick: maria-5.3-icp
test case:
CREATE TABLE t1 ( c varchar(1)) ENGINE=InnoDB;
INSERT INTO t1 VALUES ('c');
CREATE TABLE t2 ( b int) ENGINE=InnoDB;
CREATE TABLE t3 ( c varchar(1)) ENGINE=InnoDB;
INSERT INTO t3 VALUES ('c');
CREATE TABLE t4 ( b int, c varchar(1), KEY (b)) ENGINE=InnoDB;
INSERT INTO t4 VALUES (7,'c');
SET SESSION optimizer_
SELECT *
FROM t1
LEFT JOIN t2
ON 1
WHERE ( t2.b ) NOT IN (
SELECT t4.b
FROM t3, t4
WHERE t4.b <= 2
AND t4.c = t3.c
);
Related branches
Changed in maria: | |
milestone: | none → 5.3 |
assignee: | nobody → Igor Babaev (igorb-seattle) |
description: | updated |
Changed in maria: | |
status: | New → Confirmed |
Changed in maria: | |
importance: | Undecided → High |
assignee: | Igor Babaev (igorb-seattle) → Sergey Petrunia (sergefp) |
Changed in maria: | |
status: | Confirmed → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |
A testcase with MyISAM (without const tables):
drop table t1,t2,t3,t4;
CREATE TABLE t1 (c varchar(1));
INSERT INTO t1 VALUES ('c'), ('c');
CREATE TABLE t2 (c varchar(1), b int);
INSERT INTO t2 VALUES ('d', NULL),('d', NULL);
CREATE TABLE t3 (c varchar(1));
INSERT INTO t3 VALUES ('c');
INSERT INTO t3 VALUES ('c');
CREATE TABLE t4 ( b int, c varchar(1), KEY (b));
INSERT INTO t4 VALUES (7,'c');
INSERT INTO t4 VALUES (7,'c');
SET SESSION optimizer_ switch= 'index_ condition_ pushdown= ON';
explain
SELECT *
FROM t1
LEFT JOIN t2
ON t1.c=t2.b
WHERE ( t2.b ) NOT IN (
SELECT t4.b
FROM t3 STRAIGHT_JOIN t4
WHERE t4.b <= 2
AND t4.c = t3.c
);