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

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

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects versions: 5.5.33a
    • Fix versions: 5.5.37
    • Components: None
    • Labels:
      None
    • Sprint:

      Description

      Even after fix for MDEV-5112 Closed (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

              • Assignee:
                psergey Sergei Petrunia
                Reporter:
                psergey Sergei Petrunia
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: