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

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

    XMLWordPrintable

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

          Activity

            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.