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

LP:825095 - Wrong result with materialization and NOT IN with 2 expressions

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • None
    • None
    • None

    Description

      The following query:

      SELECT *
      FROM t1
      WHERE (a, b ) NOT IN (
       SELECT a , b
       FROM t2
      );

      does not return any rows when executed with materialization, even though it should return

       a | b
      --+--
       4 | 4
       4 | 2
      (2 rows)

      explain:

      1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
      2 SUBQUERY t2 ALL NULL NULL NULL NULL 2  

      minimal optimizer switch: in_to_exists=off,materialization=on

      full optimizer_switch:

      index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=on,in_to_exists=off,semijoin=off,partial_match_rowid_merge=on,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,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 (a int,b int);
      INSERT INTO t1 VALUES (4,4),(4,2);

      CREATE TABLE t2 (b int, a int);
      INSERT INTO t2 VALUES (4,3),(8,4);

      SET SESSION optimizer_switch='in_to_exists=off,materialization=on';

      SELECT *
      FROM t1
      WHERE (a, b ) NOT IN (
       SELECT a , b
       FROM t2
      );

      Repeatable in maria-5.3. Not repeatable in maria-5.2, mysql-5.5 . Does not involve NULLs, empty tables, empty subqueries or constant tables. Seems the particular values in the tables are significant.

      Attachments

        Activity

          People

            timour Timour Katchaounov (Inactive)
            philipstoev Philip Stoev (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            0 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.