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

Wrong result (missing row) with index_merge, multiple indexes, AND and OR conditions

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Won't Fix
    • 5.2.13, 5.1.66
    • 5.1.73, 5.2.15
    • None

    Description

      The problem is also reproducible on MySQL 5.1-5.6 and filed as http://bugs.mysql.com/bug.php?id=68194.
      On MariaDB, reproducible on 5.1 and 5.2, but not on 5.3 and higher, where index_merge is not used for the query even if it's on in the optimizer_switch, and the result is correct.

      set optimizer_switch='index_merge=on';
      SELECT  * FROM t1 WHERE 
      pk IN ( 255, 2, 193, 255, 106 )  
      OR ( 
      ( f5 IN ( 'why' , 'uv' ) OR NOT ( f4 = 'mm' ) ) 
      AND 
      ( ( pk = 1 ) OR f1 NOT BETWEEN 8 AND 3 + 133 ) 
      ) 
      AND ( f4 LIKE 'Wyoming' OR f5 LIKE 'Oregon' ) 
      ;
      pk	f1	f2	f3	f4	f5
      set optimizer_switch='index_merge=off';
      SELECT  * FROM t1 WHERE 
      pk IN ( 255, 2, 193, 255, 106 )  
      OR ( 
      ( f5 IN ( 'why' , 'uv' ) OR NOT ( f4 = 'mm' ) ) 
      AND 
      ( ( pk = 1 ) OR f1 NOT BETWEEN 8 AND 3 + 133 ) 
      ) 
      AND ( f4 LIKE 'Wyoming' OR f5 LIKE 'Oregon' ) 
      ;
      pk	f1	f2	f3	f4	f5
      2	345	123	h	M	w

      EXPLAIN with index_merge=on:

      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	t1	ALL	PRIMARY,f1,f4,f5	NULL	NULL	NULL	29	100.00	Using where
      Warnings:
      Note	1003	select `test`.`t1`.`pk` AS `pk`,`test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f2` AS `f2`,`test`.`t1`.`f3` AS `f3`,`test`.`t1`.`f4` AS `f4`,`test`.`t1`.`f5` AS `f5` from `test`.`t1` where ((`test`.`t1`.`pk` in (255,2,193,255,106)) or (((`test`.`t1`.`f5` in ('why','uv')) or (`test`.`t1`.`f4` <> 'mm')) and ((`test`.`t1`.`pk` = 1) or (`test`.`t1`.`f1` not between 8 and (3 + 133))) and ((`test`.`t1`.`f4` like 'Wyoming') or (`test`.`t1`.`f5` like 'Oregon'))))

      optimizer_switch (default):

      index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on

      Test case:

      CREATE TABLE t1 (
        pk INT PRIMARY KEY,
        f1 INT,
        f2 INT,
        f3 VARCHAR(10),
        f4 VARCHAR(10),
        f5 VARCHAR(64),
        KEY (f1),
        key (f4),
        key (f5)
      ) ENGINE=MyISAM;
       
      INSERT INTO t1 VALUES  
       (2, 345, 123, 'h', 'M', 'w') , 
       (3, 46,   61235, 'N', 'w', 'r') , 
       (4, 69,   0, 'why', 'Washington', 'itis') ,  
       (5, 7325, 0,  'z', 'n', 'm') ,
       (6, 297, 234, 'r', 'r', 'then') , 
       (7, 5352, 0, 'California', 'zp', 'pfkxceksatefqsdksjijcszxwbjj') , 
       (8, 102,  54729, 'a', 'r', 'f') ,  
       (9, 6623,  27839, 't', 'want', 'xceksatefqsdksjijcs') , 
       (10, 5189,  239, 'e', 'Illinois', 'say') , 
       (11, 16638, NULL, 's', 'Iowa', 'Alabama') , 
       (12, 343, 234, 'now', 'Oklahoma', 'now') , 
       (13, 3,   37398, 'tefqsdksji', 'Louisiana', 'Arkansas') ,  
       (14, 2620, 182, 'ef', 'f', 'Minnesota') ,  
       (15, 7778, 0, 'qs', 'mm', 'now') ,  
       (16, 454, 153, 'sdk', 'a', 'dksjijcszxwbjjvvk') ,  
       (17, 0,  353, 's', 'j', 'n') , 
       (18, 16406,  24, 'MA', 'i', 'r') , 
       (19, 60642,  75, 'l', 'California', 'ok') ,  
       (20, 6,   52133, 'm', 'New Jer', 'e') ,  
       (21, 8025, 3, 'zxwbjjvvk', 'did', 'h') , 
       (22, 575,  5, 'South Caro', 'w', 'bj') ,
       (23, 3,   37398, 'tefqsdksji', 'Louisiana', 'Arkansas') ,  
       (24, 2620, 182, 'ef', 'f', 'Minnesota') ,  
       (25, 7778, 0, 'qs', 'mm', 'now') ,  
       (26, 454, 153, 'sdk', 'a', 'dksjijcszxwbjjvvk') ,  
       (27, 0,  353, 's', 'j', 'n') , 
       (28, 16406,  24, 'MA', 'i', 'r') , 
       (29, 60642,  75, 'l', 'California', 'ok') ,  
       (30, 6,   52133, 'm', 'New Jer', 'e') 
      ;
       
      set optimizer_switch='index_merge=on';
       
      SELECT  * FROM t1 WHERE 
        pk IN ( 255, 2, 193, 255, 106 )  
        OR ( 
            ( f5 IN ( 'why' , 'uv' ) OR NOT ( f4 = 'mm' ) ) 
              AND 
            ( ( pk = 1 ) OR f1 NOT BETWEEN 8 AND 3 + 133 ) 
          ) 
          AND ( f4 LIKE 'Wyoming' OR f5 LIKE 'Oregon' ) 
      ;
       

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.