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

Wrong result with InnoDB when index merge is forced

Details

    Description

      When using dbt3_s001 (see mysql-test/main/dbt3_s001.inc) with InnoDB engine one gets:

      MariaDB [dbt3_s001]> SELECT COUNT(*) FROM lineitem WHERE l_receiptDATE < '1994-09-10' AND l_suppkey = 3 OR l_linenumber < 6 AND l_receiptDATE <> '1993-03-17' AND l_receiptDATE <> '1995-11-24' OR l_orderkey = 816;        
      +----------+
      | COUNT(*) |
      +----------+
      |     5375 |
      +----------+
      1 row in set (0.054 sec)
       
      MariaDB [dbt3_s001]> SELECT COUNT(*) FROM lineitem  FORCE INDEX (i_l_receiptdate, i_l_orderkey) WHERE l_receiptDATE < '1994-09-10' AND l_suppkey = 3 OR l_linenumber < 6 AND l_receiptDATE <> '1993-03-17' AND l_receiptDATE <> '1995-11-24' OR l_orderkey = 816;       +----------+
      | COUNT(*) |
      +----------+
      |     2994 |
      +----------+
      1 row in set (0.072 sec)
      

      The second result is incorrect.
      The following execution plans are used:

      MariaDB [dbt3_s001]> EXPLAIN SELECT COUNT(*) FROM lineitem WHERE l_receiptDATE < '1994-09-10' AND l_suppkey = 3 OR l_linenumber < 6 AND l_receiptDATE <> '1993-03-17' AND l_receiptDATE <> '1995-11-24' OR l_orderkey = 816;                                            
      +------+-------------+----------+------+------------------------------------------------------------------------+------+---------+------+------+-------------+
      | id   | select_type | table    | type | possible_keys                                                          | key  | key_len | ref  | rows | Extra       |
      +------+-------------+----------+------+------------------------------------------------------------------------+------+---------+------+------+-------------+
      |    1 | SIMPLE      | lineitem | ALL  | PRIMARY,i_l_suppkey,i_l_receiptdate,i_l_orderkey,i_l_orderkey_quantity | NULL | NULL    | NULL | 5871 | Using where |
      +------+-------------+----------+------+------------------------------------------------------------------------+------+---------+------+------+-------------+
      1 row in set (0.006 sec)
       
      MariaDB [dbt3_s001]> EXPLAIN SELECT COUNT(*) FROM lineitem  FORCE INDEX (i_l_receiptdate, i_l_orderkey) WHERE l_receiptDATE < '1994-09-10' AND l_suppkey = 3 OR l_linenumber < 6 AND l_receiptDATE <> '1993-03-17' AND l_receiptDATE <> '1995-11-24' OR l_orderkey = 816;
      +------+-------------+----------+-------------+------------------------------+------------------------------+---------+------+------+-------------------------------------------------------------+
      | id   | select_type | table    | type        | possible_keys                | key                          | key_len | ref  | rows | Extra                                                       |
      +------+-------------+----------+-------------+------------------------------+------------------------------+---------+------+------+-------------------------------------------------------------+
      |    1 | SIMPLE      | lineitem | index_merge | i_l_receiptdate,i_l_orderkey | i_l_receiptdate,i_l_orderkey | 4,4     | NULL | 3336 | Using sort_union(i_l_receiptdate,i_l_orderkey); Using where |
      +------+-------------+----------+-------------+------------------------------+------------------------------+---------+------+------+-------------------------------------------------------------+
      

      It does not matter what settings are used for 'use_stat_tables' and 'optimizer_use_condition_selectivity'.
      The same database with MyISAM returns correct results with the same plans as for InnoDB.
      Later versions most probably are affected as well

      Attachments

        Activity

          igor Igor Babaev added a comment -

          Checked with the last 5.5: when using InnoDB storage engine for dbt3_s001 correct results are returned and with the same execution plans as for 10.3.

          igor Igor Babaev added a comment - Checked with the last 5.5: when using InnoDB storage engine for dbt3_s001 correct results are returned and with the same execution plans as for 10.3.

          Bisect points at this commit in 10.2.35:

          commit 291be494744abe90f4bdf6b5a35c4c26ee8ddda5
          Date:   Thu Sep 24 22:02:00 2020 -0700
           
              MDEV-23811: With large number of indexes optimizer chooses an inefficient plan
          

          elenst Elena Stepanova added a comment - Bisect points at this commit in 10.2.35: commit 291be494744abe90f4bdf6b5a35c4c26ee8ddda5 Date: Thu Sep 24 22:02:00 2020 -0700   MDEV-23811: With large number of indexes optimizer chooses an inefficient plan
          igor Igor Babaev added a comment -

          With
          set optimizer_switch='extended_keys=off';
          we have correct result for InnoDB with index merge as well:

          MariaDB [dbt3_s001]> SELECT COUNT(*) FROM lineitem  FORCE INDEX (i_l_receiptdate, i_l_orderkey) WHERE l_receiptDATE < '1994-09-10' AND l_suppkey = 3 OR l_linenumber < 6 AND l_receiptDATE <> '1993-03-17' AND l_receiptDATE <> '1995-11-24' OR l_orderkey = 816;
          +----------+
          | COUNT(*) |
          +----------+
          |     5375 |
          +----------+
           
          MariaDB [dbt3_s001]> EXPLAIN SELECT COUNT(*) FROM lineitem  FORCE INDEX (i_l_receiptdate, i_l_orderkey) WHERE l_receiptDATE < '1994-09-10' AND l_suppkey = 3 OR l_linenumber < 6 AND l_receiptDATE <> '1993-03-17' AND l_receiptDATE <> '1995-11-24' OR l_orderkey = 816;
          +------+-------------+----------+-------------+------------------------------+------------------------------+---------+------+------+-------------------------------------------------------------+
          | id   | select_type | table    | type        | possible_keys                | key                          | key_len | ref  | rows | Extra                                                       |
          +------+-------------+----------+-------------+------------------------------+------------------------------+---------+------+------+-------------------------------------------------------------+
          |    1 | SIMPLE      | lineitem | index_merge | i_l_receiptdate,i_l_orderkey | i_l_receiptdate,i_l_orderkey | 4,4     | NULL | 5873 | Using sort_union(i_l_receiptdate,i_l_orderkey); Using where |
          +------+-------------+----------+-------------+------------------------------+------------------------------+---------+------+------+-------------------------------------------------------------+
          

          This explains why for this query we have different results in InnoDB and MyISAM: MyISAM does not use extended keys while InnoDB may use them. Note that the primary key for lineitem has 2 components and extended keys for i_l_receiptdate in InnoDB potentially can have 3 components: (l_receipdate,l_orderkey,I_lineitem).
          Yet no valid extended keys can be used for this query.

          igor Igor Babaev added a comment - With set optimizer_switch='extended_keys=off'; we have correct result for InnoDB with index merge as well: MariaDB [dbt3_s001]> SELECT COUNT(*) FROM lineitem FORCE INDEX (i_l_receiptdate, i_l_orderkey) WHERE l_receiptDATE < '1994-09-10' AND l_suppkey = 3 OR l_linenumber < 6 AND l_receiptDATE <> '1993-03-17' AND l_receiptDATE <> '1995-11-24' OR l_orderkey = 816; +----------+ | COUNT(*) | +----------+ | 5375 | +----------+   MariaDB [dbt3_s001]> EXPLAIN SELECT COUNT(*) FROM lineitem FORCE INDEX (i_l_receiptdate, i_l_orderkey) WHERE l_receiptDATE < '1994-09-10' AND l_suppkey = 3 OR l_linenumber < 6 AND l_receiptDATE <> '1993-03-17' AND l_receiptDATE <> '1995-11-24' OR l_orderkey = 816; +------+-------------+----------+-------------+------------------------------+------------------------------+---------+------+------+-------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+----------+-------------+------------------------------+------------------------------+---------+------+------+-------------------------------------------------------------+ | 1 | SIMPLE | lineitem | index_merge | i_l_receiptdate,i_l_orderkey | i_l_receiptdate,i_l_orderkey | 4,4 | NULL | 5873 | Using sort_union(i_l_receiptdate,i_l_orderkey); Using where | +------+-------------+----------+-------------+------------------------------+------------------------------+---------+------+------+-------------------------------------------------------------+ This explains why for this query we have different results in InnoDB and MyISAM: MyISAM does not use extended keys while InnoDB may use them. Note that the primary key for lineitem has 2 components and extended keys for i_l_receiptdate in InnoDB potentially can have 3 components: (l_receipdate,l_orderkey,I_lineitem). Yet no valid extended keys can be used for this query.
          alice Alice Sherepa added a comment -

          On 10.5+ (179c2833721292a9182) there are correct results in both cases

          alice Alice Sherepa added a comment - On 10.5+ (179c2833721292a9182) there are correct results in both cases

          People

            igor Igor Babaev
            igor Igor Babaev
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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