Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
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`.`alias2`.`a` = `test`.`alias1`.`a`) and (`test`.`alias2`.`b` = `test`.`alias1`.`a`) and (`v`.`a` = `test`.`alias1`.`a`) and ((`test`.`alias1`.`b` >= `test`.`alias1`.`a`) or ((`test`.`alias1`.`a` = 'z') and (`v`.`a` = 'z'))))
Minimal optimizer_switch: materialization=on,semijoin=on
Full optimizer_switch (default): index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on
Test case (with the variation):
--source include/have_innodb.inc
SET SESSION optimizer_switch = 'materialization=on,semijoin=on';
CREATE TABLE t1 (
a VARCHAR(1),
b VARCHAR(1) NOT NULL,
KEY(a)
) ENGINE=InnoDB;
INSERT INTO t1 VALUES
('j','j'),('v','v'),('c','c'),('m','m'),('d','d'),
('y','y'),('t','t'),('d','d'),('s','s'),('r','r'),
('m','m'),('b','b'),('x','x'),('g','g'),('p','p'),
('q','q'),('w','w'),('d','d'),('e','e');
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
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Wrong result (missing rows) with semijoin+materialization, IN subquery, InnoDB, TEMPTABLE view or derived_merge=off
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
#1 0x08372131 in sub_select (join=0x9f54a28, join_tab=0x9f5cdb8, end_of_records=false) at sql_select.cc:15436
#2 0x0836341b in evaluate_join_record (join=0x9f54a28, join_tab=0x9f5cbb0, error=0) at sql_select.cc:15633
#3 0x08372131 in sub_select (join=0x9f54a28, join_tab=0x9f5cbb0, end_of_records=false) at sql_select.cc:15436
#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->table->alias.Ptr
$233 = 0x9f3f158 "v"
(gdb) p dbug_print_item(select_cond)
$234 = 0x8b9f560 "((`j2`.`t1a`.`b` >= `j2`.`t1a`.`a`) or ((`j2`.`t1a`.`a` = 'z') and (`v`.`a` = 'z')))"