Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 10.0.0
-
Fix Version/s: None
-
Component/s: None
-
Labels:None
Description
The following test case
--source include/have_innodb.inc SET optimizer_switch='index_merge=on,index_merge_intersection=on'; CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, c VARCHAR(1024) CHARACTER SET utf8, d INT, KEY (b) ) ENGINE=InnoDB; INSERT INTO t1 VALUES (1, 9, 'one', 11), (2, 6, 'two', 12), (3, 2, 'three', 13), (4, 5, 'four', 14); CREATE TABLE t2 (e INT, g INT) ENGINE=InnoDB; INSERT INTO t2 VALUES (1,9), (2,6) ; SELECT * FROM t1, t2 WHERE g = b AND ( a < 7 OR a > e ); DROP TABLE t1, t2;
produces a wrong result. It returns the right 2 rows, but values in the 2nd row are strange:
a b c d e g 1 9 one 11 1 9 2 6 0 2 6
There are no c='' and d=0 in the dataset, they should be 'two' and 12 instead.
bzr version-info
revision-id: timour@askmonty.org-20121022095529-87ykx0dubnj62c9y date: 2012-10-22 12:55:29 +0300 revno: 3452 branch: ~maria-captains/maria/10.0-serg
Not reproducible on MariaDB 5.2 revno 3163, 5.3 revno 3587, 5.5 revno 3562, MySQL 5.6 revno 4229.
Not reproducible with t1 being a MyISAM or Aria table.
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 t2 ALL NULL NULL NULL NULL 2 100.00 1 SIMPLE t1 ALL PRIMARY,b NULL NULL NULL 4 75.00 Range checked for each record (index map: 0x3) Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t1`.`d` AS `d`,`test`.`t2`.`e` AS `e`,`test`.`t2`.`g` AS `g` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`b` = `test`.`t2`.`g`) and ((`test`.`t1`.`a` < 7) or (`test`.`t1`.`a` > `test`.`t2`.`e`)))
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
the line
bitmap_copy(&column_bitmap, head->read_set);
in QUICK_RANGE_SELECT::init_ror_merged_scan() is meaningless in current code, it always copies a bitmap into itself...
Details about the bug fixed by [psergey-1]:
- For some reason, I was unable to repeat in 5.3 just before the fix was
pushed. - In 5.5.23 (the version that was complained about) the bug is repeatable.
DELETE uses index ROR-intersect, quick->need_to_fetch_row= FALSE
= The first branch re-uses the primary handler object and has
prebuilt->select_lock_type == LOCK_X, which makes column bitmaps
irreleavant.
= The second branch uses the default LOCK_NONE, which causes build_template()
to analyze contents of table->read_set, which has no column bits set.
This causes the reads not to unpack the index column, which causes record
misses.
in mysql-server-trunk, the DELETE from [psergey-1] will also use ROR-interesct.
However, prebuilt->select_lock_type == LOCK_X for both the primary handler and
for the clone
, which causes all index columns to be read by both
index_merge branches, and the problem from [psergey-1] is not repeatable.
is caused by ha_innobase::clone(), which was introduced by this cset:
annamalai.gurusami@oracle.com-20120510044831-d4xkpk2ky5sioeeq
Bug #14007649 65111: INNODB SOMETIMES FAILS TO UPDATE ROWS INSERTED
BY A CONCURRENT TRANSACTIO
It seems to be no longer repeatable in current 10.0.8:
mysql> SELECT * FROM t1, t2 WHERE g = b AND ( a < 7 OR a > e );
-----------------------+
| a | b | c | d | e | g |
-----------------------+
| 1 | 9 | one | 11 | 1 | 9 |
| 2 | 6 | two | 12 | 2 | 6 |
-----------------------+
2 rows in set (0.00 sec)
This bug was actually fixed, apparently it was left open by mistake:
------------------------------------------------------------ revno: 3471 revision-id: psergey@askmonty.org-20121103082436-wyyctfe5gpl4vu3f parent: sergii@pisem.net-20121103112851-bvy1mlhirxbkedvf committer: Sergey Petrunya <psergey@askmonty.org> branch nick: 10.0-serg-fix-imerge timestamp: Sat 2012-11-03 12:24:36 +0400 message: # MDEV-3817: Wrong result with index_merge+index_merge_intersection, InnoDB table, join, AND and OR conditions Reconcile the fixes from: # # guilhem.bichot@oracle.com-20110805143029-ywrzuz15uzgontr0 # Fix for BUG#12698916 - "JOIN QUERY GIVES WRONG RESULT AT 2ND EXEC. OR # AFTER FLUSH TABLES [-INT VS NULL]" # # guilhem.bichot@oracle.com-20111209150650-tzx3ldzxe1yfwji6 # Fix for BUG#12912171 - ASSERTION FAILED: QUICK->HEAD->READ_SET == SAVE_READ_SET # and # and related fixes from: BUG#1006164, MDEV-376: Now, ROR-merged QUICK_RANGE_SELECT objects make no assumptions about the values of table->read_set and table->write_set. Each QUICK_ROR_SELECT has (and had before) its own column bitmap, but now, all QUICK_ROR_SELECT's functions that care: reset(), init_ror_merged_scan(), and get_next() will set table->read_set when invoked and restore it back to what it was before the call before they return. This allows to avoid the mess when somebody else modifies table->read_set for some reason. ------------------------------------------------------------
Fixed in 10.0.0
There are four fixes made in init_ror_merged_scans():
[psergey-1] psergey@askmonty.org-20120601232556-2jojg7cuat4qxhcu
[psergey-2] psergey@askmonty.org-20120704103445-ro4sk3cwbbzn3t4p
MDEV-376: Wrong result (missing rows) with index_merge+index_merge_intersection, join[guilhem-1] guilhem.bichot@oracle.com-20110805143029-ywrzuz15uzgontr0
[guilhem-2] guilhem.bichot@oracle.com-20111209150650-tzx3ldzxe1yfwji6