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

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5.33a
    • Fix Version/s: 5.5.37
    • Component/s: None
    • Labels:
      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

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

                Dates

                Created:
                Updated:
                Resolved: