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

ha_partition and innodb index intersection produce fewer rows (MySQL Bug#70703)

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5.33a
    • 5.5.37
    • None
    • None

    Description

      Even after fix for MDEV-5112 (and MySQL Bug#69581), a query that runs index_merge intersection (union is probably affected too) over partitioned table, may return wrong query result.

      Testcase:

      create table t11 (
      		 
        a int not null,
      		 
        b int not null,
      		 
        pk int not null,
      		 
        primary key (pk),
      		 
        key(a),
      		 
        key(b)
      		 
      ) partition by hash(pk) partitions 10;
      		 
       
      		 
      insert into t11 values (1,2,4); -- both
      		 
      insert into t11 values (1,0,17);  -- left
      		 
      insert into t11 values (1,2,25);   -- both
      		 
       
      		 
      insert into t11 values (10,20,122); 
      insert into t11 values (10,20,123);
      		 
       
      		 
      -- Now, fill in some data so that the optimizer choses index_merge
      		 
      create table t12 (a int);
      		 
      insert into t12 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
      		 
       
      		 
      insert into t11 select 1,2, 200 + A.a + 10*B.a + 100*C.a from t12 A, t12 B, t12 C;
      		 
       
      		 
      insert into t11 select 10+A.a + 10*B.a + 100*C.a + 1000*D.a,
      		 
                             10+A.a + 10*B.a + 100*C.a  + 1000*D.a, 
                             2000 + A.a + 10*B.a + 100*C.a + 1000*D.a
      		 
                             from t12 A, t12 B, t12 C ,t12 D;
      		 
       
      		 
      -- This should show index_merge, using intersect
      		 
      explain select * from t11 where a=1 and b=2 and  pk between 1 and 999999 ;
      		 
      -- 794 rows in output
      		 
      select * from t11 where a=1 and b=2 and  pk between 1 and 999 ;
      		 
      -- 802 rows in output
      		 
      select * from t11 ignore index(a,b)  where a=1 and b=2 and  pk between 1 and 999 ;

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-5821 Wrong result of SELECT query while two indexed colums are in WHERE section

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            View 12 older comments
            psergei Sergei Petrunia added a comment -

            == Ideas on how this could be fixed ==

            Ordered index scans over ha_partition return records in the order of (key, extended_key_parts, ...).
            There is no way to change that.

            It could be nice to get index_merge to inform the storage engine that it needs the index scans to be rowid-ordered but not key-ordered, but this is a too big change for a GA version.

            psergei Sergei Petrunia added a comment - == Ideas on how this could be fixed == Ordered index scans over ha_partition return records in the order of (key, extended_key_parts, ...). There is no way to change that. It could be nice to get index_merge to inform the storage engine that it needs the index scans to be rowid-ordered but not key-ordered, but this is a too big change for a GA version.
            psergei Sergei Petrunia added a comment -

            A possible solution:

            index scan on ha_partition must return records ordered by

            key_value, ext_key_parts (if there are any)

            When ROR-index_merge is used, the key_value part is fixed, which means that
            index scans will return rows ordered by:

            ext_key_parts (if there are any)

            On the other hand, ROR-index_merge requires that rows come in the order that
            matches the ordering imposed by ha_partition::cmp_ref().

            The solution is:
            1. Let index scan on ha_partition return rows ordered by

            key_rec_cmp, then part_id - for tables with extended keys (we do this now)
            key_rec_cmp, then underlying_rowid, then part_id - for tables without extended keys.

            2. Let ha_partition::cmp_ref() compare rowids by comparing underlying_rowid
            first, then comparing part_id.

            psergei Sergei Petrunia added a comment - A possible solution: index scan on ha_partition must return records ordered by key_value, ext_key_parts (if there are any) When ROR-index_merge is used, the key_value part is fixed, which means that index scans will return rows ordered by: ext_key_parts (if there are any) On the other hand, ROR-index_merge requires that rows come in the order that matches the ordering imposed by ha_partition::cmp_ref(). The solution is: 1. Let index scan on ha_partition return rows ordered by key_rec_cmp, then part_id - for tables with extended keys (we do this now) key_rec_cmp, then underlying_rowid, then part_id - for tables without extended keys. 2. Let ha_partition::cmp_ref() compare rowids by comparing underlying_rowid first, then comparing part_id.
            psergei Sergei Petrunia added a comment -

            Committed another fix. I'll need a review for it.

            psergei Sergei Petrunia added a comment - Committed another fix. I'll need a review for it.
            psergei Sergei Petrunia added a comment -

            igor, as you have requested: please find the attached testcase (mdev5555-innodb.test) which fails in MySQL 5.6.

            psergei Sergei Petrunia added a comment - igor , as you have requested: please find the attached testcase (mdev5555-innodb.test) which fails in MySQL 5.6.
            psergei Sergei Petrunia added a comment -

            Fix pushed into 5.5 tree

            psergei Sergei Petrunia added a comment - Fix pushed into 5.5 tree

            People

              psergei Sergei Petrunia
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.