Wrong result (missing rows) with semijoin+materialization, IN subquery, InnoDB, TEMPTABLE view or derived_merge=off
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
Critical
|
Sergey Petrunia |
Bug Description
The following query
SELECT * FROM v
WHERE ( a, a ) IN (
SELECT alias2.b, alias2.a
FROM t1 AS alias1, t1 AS alias2
WHERE alias2.b = alias1.a
AND ( alias1.b >= alias1.a OR alias2.b = 'z' )
on the test data returns 6 rows if it's executed with semijoin=on and materialization=on, and 19 rows otherwise. The latter is correct.
On a variation of the test data, where column b is nullable, the query returns no rows at all. This variation is added to the test case as ALTER TABLE followed by the same query.
bzr version-info
revision-id: <email address hidden>
date: 2012-03-05 22:33:46 -0800
build-date: 2012-03-11 05:27:06 +0400
revno: 3455
Also reproducible on 5.5 (revno 3316).
Not reproducible on MySQL 5.6 (revno 3706).
EXPLAIN with semijoin=on, materialization=on (wrong result):
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 19 100.00
1 PRIMARY <derived3> ALL NULL NULL NULL NULL 19 100.00 Using where; Using join buffer (flat, BNL join)
2 MATERIALIZED alias1 ALL a NULL NULL NULL 19 100.00 Using where
2 MATERIALIZED alias2 ref a a 4 test.alias1.a 1 100.00 Using where
3 DERIVED t1 ALL NULL NULL NULL NULL 19 100.00
Warnings:
Note 1003 select `v`.`a` AS `a`,`v`.`b` AS `b` from `test`.`v` semi join (`test`.`t1` `alias1` join `test`.`t1` `alias2`) where ((`test`
Minimal optimizer_switch: materialization
Full optimizer_switch (default): index_merge=
Test case (with the variation):
--source include/
SET SESSION optimizer_switch = 'materializatio
CREATE TABLE t1 (
a VARCHAR(1),
b VARCHAR(1) NOT NULL,
KEY(a)
) ENGINE=InnoDB;
INSERT INTO t1 VALUES
('j','j'
('y','y'
('m','m'
('q','q'
CREATE ALGORITHM=TEMPTABLE VIEW v AS SELECT * FROM t1;
# This query returns 6 rows instead of 19
SELECT * FROM v
WHERE ( a, a ) IN (
SELECT alias2.b, alias2.a
FROM t1 AS alias1, t1 AS alias2
WHERE alias2.b = alias1.a
AND ( alias1.b >= alias1.a OR alias2.b = 'z' )
);
# End of the main part.
# The rest is the test case variation, where we make column b nullable
ALTER TABLE t1 MODIFY COLUMN b VARCHAR(1);
# This query returns an empty set
SELECT * FROM v
WHERE ( a, a ) IN (
SELECT alias2.b, alias2.a
FROM t1 AS alias1, t1 AS alias2
WHERE alias2.b = alias1.a
AND ( alias1.b >= alias1.a OR alias2.b = 'z' )
);
# End of test case
Changed in maria: | |
assignee: | Oleksandr "Sanja" Byelkin (sanja-byelkin) → Sergey Petrunia (sergefp) |
importance: | Undecided → Critical |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |
The part of where condition attached to to the materialized VIEW seems to be wrong:
#0 evaluate_ join_record (join=0x9f54a28, join_tab=0x9f5cdb8, error=0) at sql_select.cc:15531 records= false) at sql_select.cc:15436 join_record (join=0x9f54a28, join_tab=0x9f5cbb0, error=0) at sql_select.cc:15633 records= false) at sql_select.cc:15436 >table- >alias. Ptr item(select_ cond)
#1 0x08372131 in sub_select (join=0x9f54a28, join_tab=0x9f5cdb8, end_of_
#2 0x0836341b in evaluate_
#3 0x08372131 in sub_select (join=0x9f54a28, join_tab=0x9f5cbb0, end_of_
#4 0x083733b2 in do_select (join=0x9f54a28, fields=0x9edb54c, table=0x0, procedure=0x0) at sql_select.cc:15097
#5 0x0838c2b7 in JOIN::exec (this=0x9f54a28) at sql_select.cc:2731
(gdb) p join_tab-
$233 = 0x9f3f158 "v"
(gdb) p dbug_print_
$234 = 0x8b9f560 "((`j2`.`t1a`.`b` >= `j2`.`t1a`.`a`) or ((`j2`.`t1a`.`a` = 'z') and (`v`.`a` = 'z')))"