[MDEV-3817] Wrong result with index_merge+index_merge_intersection, InnoDB table, join, AND and OR conditions Created: 2012-10-24  Updated: 2014-02-07  Resolved: 2014-02-07

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.0
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates

 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`)))



 Comments   
Comment by Sergei Petrunia [ 2012-10-30 ]

There are four fixes made in init_ror_merged_scans():

[psergey-1] psergey@askmonty.org-20120601232556-2jojg7cuat4qxhcu

  1. BUG#1006164: Multi-table DELETE that uses innodb + index_merge/intersect may fail to delete rows
  2. - Set index columns to be read when using index_merge, even if TABLE->no_keyread is
  3. set for the table (happens for multi-table UPDATEs)

[psergey-2] psergey@askmonty.org-20120704103445-ro4sk3cwbbzn3t4p

  1. MDEV-376: Wrong result (missing rows) with index_merge+index_merge_intersection, join
  2. - Let QUICK_RANGE_SELECT::init_ror_merged_scan() call quick->reset() only
  3. after we've set the column read bitmaps.

[guilhem-1] guilhem.bichot@oracle.com-20110805143029-ywrzuz15uzgontr0

  1. Fix for BUG#12698916 - "JOIN QUERY GIVES WRONG RESULT AT 2ND EXEC. OR
  2. AFTER FLUSH TABLES [-INT VS NULL]"
  3. Range optimizer was wrongly changing the TABLE::read_set so InnoDB
  4. was not filling one column.

[guilhem-2] guilhem.bichot@oracle.com-20111209150650-tzx3ldzxe1yfwji6

  1. Fix for BUG#12912171 - ASSERTION FAILED: QUICK->HEAD->READ_SET == SAVE_READ_SET
  2. and
  3. BUG#13256446 - ASSERTION QUICK->HEAD->READ_SET == SAVE_READ_SET' FAILED IN OPT_RANGE.CC:1606
Comment by Sergei Petrunia [ 2012-10-30 ]

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...

Comment by Sergei Petrunia [ 2012-10-30 ]

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.
Comment by Sergei Petrunia [ 2012-10-30 ]

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

Comment by Sergei Petrunia [ 2014-02-06 ]

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)

Comment by Elena Stepanova [ 2014-02-07 ]

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.
 
------------------------------------------------------------

Comment by Elena Stepanova [ 2014-02-07 ]

Fixed in 10.0.0

Generated at Thu Feb 08 06:51:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.