Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 5.5.25
-
Fix Version/s: 5.5.27
-
Component/s: None
-
Labels:None
Description
The following query
SELECT ta.* FROM t1 AS ta, t1 AS tb WHERE ( tb.b != ta.b OR tb.a = ta.a ) AND ( tb.b = ta.c OR tb.b = ta.b );
on test data returns 2 rows with index_merge=ON and index_merge_intersection=ON, and 3 rows otherwise. 3 rows is the correct result.
bzr version-info
revision-id: monty@askmonty.org-20120627141312-z65pj80390f0f5pp date: 2012-06-27 17:13:12 +0300 build-date: 2012-07-02 05:43:00 +0400 revno: 3460
Could not reproduce on MariaDB 5.3 or MySQL trunk.
Minimal optimizer_switch:
index_merge=on,index_merge_intersection=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,engine_condition_pushdown=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,extended_keys=off
EXPLAIN (with the minimal optimizer_switch):
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE ta ALL a,b NULL NULL NULL 3 100.00 1 SIMPLE tb ALL a,b NULL NULL NULL 3 100.00 Range checked for each record (index map: 0x3) Warnings: Note 1003 select `test`.`ta`.`a` AS `a`,`test`.`ta`.`b` AS `b`,`test`.`ta`.`c` AS `c` from `test`.`t1` `ta` join `test`.`t1` `tb` where (((`test`.`tb`.`b` <> `test`.`ta`.`b`) or (`test`.`tb`.`a` = `test`.`ta`.`a`)) and ((`test`.`tb`.`b` = `test`.`ta`.`c`) or (`test`.`tb`.`b` = `test`.`ta`.`b`)))
Test case:
--source include/have_innodb.inc CREATE TABLE t1 ( a INT, b CHAR(1), c CHAR(1), KEY(a), KEY(b) ) ENGINE=InnoDB; INSERT INTO t1 VALUES (8,'v','v'),(8,'m','m'),(9,'d','d'); SET optimizer_switch = 'index_merge=on,index_merge_intersection=on'; SELECT ta.* FROM t1 AS ta, t1 AS tb WHERE ( tb.b != ta.b OR tb.a = ta.a ) AND ( tb.b = ta.c OR tb.b = ta.b );
Expected result:
a b c ----------------- 8 v v 8 m m 9 d d
Actual result:
a b c ----------------- 8 v v 8 m m
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
- Range optimization is correct (Initally I thought it was malfunctioning)
- the record is missing because QUICK_ROR_INTERSECT_SELECT didn't return it.
- it didn't return it because it got a=NULL from an index scan over "a=8"...
I've finally figured it out. The problem is caused by changes inside InnoDB/XtraDB.
index_merge/intersection code does the following:
1. handler->index_init( index_no, ..);
...
2. table->mark_columns_used_by_index(index);
3. table->prepare_for_position();
4. table->column_bitmaps_set(...);
5.
. call handler->read_range_first(), which will call handler->index_read_map() or handler->index_read().
Call #4 is supposed to inform the storage engine about changes made by calls #2 and #3. InnoDB doesn't implement handler::column_bitmap_signal(), so it ignores call #4.
ha_innobase::index_read_map() used to call build_template(), which would rebuilt row_prebuilt_t structure and take changed made by calls #3 and #4 into account.
This doesn't happen anymore; current innodb in 5.5 will only build row_prebuilt_t structure in handler->index_init() call. As a result, read operations on the innodb table do not fill in values for columns requested by #3 and #4, which causes the WHERE condition to be evaluated incorrectly, and produce this bug.
Fix pushed.
