Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-3817

Wrong result with index_merge+index_merge_intersection, InnoDB table, join, AND and OR conditions

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

          Ascending order - Click to sort in descending order
          psergei Sergei Petrunia added a comment -

          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
          psergei Sergei Petrunia added a comment - There are four fixes made in init_ror_merged_scans(): [psergey-1] psergey@askmonty.org-20120601232556-2jojg7cuat4qxhcu BUG#1006164: Multi-table DELETE that uses innodb + index_merge/intersect may fail to delete rows - Set index columns to be read when using index_merge, even if TABLE->no_keyread is set for the table (happens for multi-table UPDATEs) [psergey-2] psergey@askmonty.org-20120704103445-ro4sk3cwbbzn3t4p MDEV-376 : Wrong result (missing rows) with index_merge+index_merge_intersection, join - Let QUICK_RANGE_SELECT::init_ror_merged_scan() call quick->reset() only after we've set the column read bitmaps. [guilhem-1] 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] " Range optimizer was wrongly changing the TABLE::read_set so InnoDB was not filling one column. [guilhem-2] guilhem.bichot@oracle.com-20111209150650-tzx3ldzxe1yfwji6 Fix for BUG#12912171 - ASSERTION FAILED: QUICK->HEAD->READ_SET == SAVE_READ_SET and BUG#13256446 - ASSERTION QUICK->HEAD->READ_SET == SAVE_READ_SET' FAILED IN OPT_RANGE.CC:1606
          psergei Sergei Petrunia added a comment -

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

          psergei Sergei Petrunia added a comment - 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...
          psergei Sergei Petrunia added a comment -

          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.
          psergei Sergei Petrunia added a comment - 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.
          psergei Sergei Petrunia added a comment -

          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

          psergei Sergei Petrunia added a comment - 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
          psergei Sergei Petrunia added a comment -

          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)

          psergei Sergei Petrunia added a comment - 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)
          elenst Elena Stepanova added a comment -

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

          elenst Elena Stepanova added a comment - 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. ------------------------------------------------------------
          elenst Elena Stepanova added a comment -

          Fixed in 10.0.0

          elenst Elena Stepanova added a comment - Fixed in 10.0.0

          People

            psergei Sergei Petrunia
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.