Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.0
-
None
-
None
-
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`)))
|
Attachments
Activity
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.
|
------------------------------------------------------------
|
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