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

wrong resultset from query with semijoin=on

    XMLWordPrintable

Details

    Description

      Please check the following test case kindly supplied by a customer of ours.
      The 2nd query produces wrong resultset (should list all rows , it only lists those
      explicited by the second condition).
      If optimizer switch semijoin is turned off, it will produce correct results.

      This bug is not present in Oracle MySQL 5.6.

      CREATE TABLE `store_loan` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `local_name` varchar(64) NOT NULL,
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;
       
      insert  into `store_loan`(`id`,`local_name`) values 
      (1,'Cash Advance'),
      (2,'Cash Advance'),
      (3,'Rollover'),
      (4,'AL Installment'),
      (5,'AL Installment'),
      (6,'AL Installment'),
      (7,'AL Installment'),
      (8,'AL Installment'),
      (9,'AL Installment'),
      (10,'Internet Payday'),
      (11,'Rollover - Internet Payday'),
      (12,'AL Monthly Installment'),
      (13,'AL Semi-Monthly Installment');
       
      ## Make sure @@Optimizer_switch semijoin=on is set
      ## current settings in my env is: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=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,extended_keys=on,exists_to_in=on,orderby_uses_equalities=off
      ##
      ## I would expect both queries below to return the same result set since the sub-queries in query 1 returns the same data s the IN() clause in query 2
       
      #Query 1
      SELECT SQL_NO_CACHE sl.id 
      FROM store_loan sl
      WHERE (
      	sl.id IN (SELECT A.id FROM store_loan AS A WHERE A.local_name IN (SELECT B.local_name FROM store_loan AS B WHERE B.id IN (0,4,12,13,1,10,3,11)))  
      	OR 
      	(sl.id IN (0,4,12,13,1,10,3,11))
      );
      +----+
      | id |
      +----+
      |  1 |
      |  3 |
      |  4 |
      | 10 |
      | 11 |
      | 12 |
      | 13 |
      +----+
      7 rows in set (0.01 sec)
       
      #Query 2
      SELECT SQL_NO_CACHE sl.id 
      FROM store_loan sl
      WHERE (
       	sl.id IN (1,2,3,4,5,6,7,8,9,10,11,12,13)
      	OR 
      	(sl.id IN (0,4,12,13,1,10,3,11))
      );
      +----+
      | id |
      +----+
      |  1 |
      |  2 |
      |  3 |
      |  4 |
      |  5 |
      |  6 |
      |  7 |
      |  8 |
      |  9 |
      | 10 |
      | 11 |
      | 12 |
      | 13 |
      +----+
      13 rows in set (0.00 sec)
      

      Attachments

        Activity

          People

            varun Varun Gupta (Inactive)
            rpizzi Rick Pizzi (Inactive)
            Votes:
            2 Vote for this issue
            Watchers:
            4 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.