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

Wrong result from query with NOT IN predicate in WHERE

Details

    • Bug
    • Status: Closed (View Workflow)
    • Blocker
    • Resolution: Fixed
    • 10.3.3
    • 10.3.3
    • Optimizer
    • None

    Description

      If the system variable @@in_subquery_conversion_threshold is set to a small enough number then queries with NOT IN predicate in most cases return wrong result sets.
      The following test case demonstrate this:

      create table t1 (a int, b int);
      		 
      insert into t1 values 
       (1,2), (4,6), (9,7),(1,1), (2,5), (7,8);
      		 
       
      		 
      select * from t1
      		 
      where (a,b) not in ((1,2),(8,9), (5,1));
      		 
       
      		 
      set @@in_subquery_conversion_threshold= 2;
      		 
      select * from t1
      		 
      where (a,b) not in ((1,2),(8,9), (5,1));

      When running this test we have:

      MariaDB [test]> 
      MariaDB [test]> select * from t1
      		 
          -> where (a,b) not in ((1,2),(8,9), (5,1));
      		 
      +------+------+
      		 
      | a    | b    |
      		 
      +------+------+
      		 
      |    4 |    6 |
      		 
      |    9 |    7 |
      		 
      |    1 |    1 |
      		 
      |    2 |    5 |
      		 
      |    7 |    8 |
      		 
      +------+------+
      		 
       
      		 
      MariaDB [test]> set @@in_subquery_conversion_threshold= 2;
      		 
       
      		 
      MariaDB [test]> select * from t1
      		 
          -> where (a,b) not in ((1,2),(8,9), (5,1));
      		 
      +------+------+
      		 
      | a    | b    |
      		 
      +------+------+
      		 
      |    1 |    2 |
      		 
      +------+------+

      Attachments

        Issue Links

          is caused by

          Task - A task that needs to be done. MDEV-12176 Transform [NOT] IN predicate with long list of values INTO [NOT] IN subquery.

          • Major - Major loss of function.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-14835 Server crashes in strlen / Field_iterator_table::create_item when IN list reaches in_predicate_conversion_threshold

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            igor Igor Babaev (Inactive) created issue -
            igor Igor Babaev (Inactive) made changes -
            Field Original Value New Value
            Description If the system variable @@in_subquery_conversion_threshold is set to a small enough number then queries with NOT IN predicate may return wrong result sets.
            The following test case demonstrate this:
            {noformat}
            create table t1 (a int, b int);
            insert into t1 values
             (1,2), (4,6), (9,7),(1,1), (2,5), (7,8);

            select * from t1
            where (a,b) not in ((1,2),(8,9), (5,1));

            set @@in_subquery_conversion_threshold= 2;
            select * from t1
            where (a,b) not in ((1,2),(8,9), (5,1));
            {noformat}
            When running this test we have:
            {noformat}
            MariaDB [test]>
            MariaDB [test]> select * from t1
                -> where (a,b) not in ((1,2),(8,9), (5,1));
            +------+------+
            | a | b |
            +------+------+
            | 4 | 6 |
            | 9 | 7 |
            | 1 | 1 |
            | 2 | 5 |
            | 7 | 8 |
            +------+------+

            MariaDB [test]> set @@in_subquery_conversion_threshold= 2;

            MariaDB [test]> select * from t1
                -> where (a,b) not in ((1,2),(8,9), (5,1));
            +------+------+
            | a | b |
            +------+------+
            | 1 | 2 |
            +------+------+






            		If the system variable @@in_subquery_conversion_threshold is set to a small enough number then queries with NOT IN predicate may return wrong result sets.
            The following test case demonstrate this:
            {noformat}
            create table t1 (a int, b int);
            insert into t1 values
             (1,2), (4,6), (9,7),(1,1), (2,5), (7,8);

            select * from t1
            where (a,b) not in ((1,2),(8,9), (5,1));

            set @@in_subquery_conversion_threshold= 2;
            select * from t1
            where (a,b) not in ((1,2),(8,9), (5,1));
            {noformat}
            When running this test we have:
            {noformat}
            MariaDB [test]>
            MariaDB [test]> select * from t1
                -> where (a,b) not in ((1,2),(8,9), (5,1));
            +------+------+
            | a | b |
            +------+------+
            | 4 | 6 |
            | 9 | 7 |
            | 1 | 1 |
            | 2 | 5 |
            | 7 | 8 |
            +------+------+

            MariaDB [test]> set @@in_subquery_conversion_threshold= 2;

            MariaDB [test]> select * from t1
                -> where (a,b) not in ((1,2),(8,9), (5,1));
            +------+------+
            | a | b |
            +------+------+
            | 1 | 2 |
            +------+------+
            {noformat}






            marko Marko Mäkelä made changes -
            Affects Version/s 10.3.3 [ 22644 ]
            Affects Version/s 10.3 [ 22126 ]
            marko Marko Mäkelä made changes -
            Fix Version/s 10.3 [ 22126 ]
            marko Marko Mäkelä made changes -
            Status Open [ 1 ] Confirmed [ 10101 ]
            marko Marko Mäkelä added a comment -

            The parameter in_subquery_conversion_threshold was introduced in MDEV-12176.

            marko Marko Mäkelä added a comment - The parameter in_subquery_conversion_threshold was introduced in MDEV-12176 .
            marko Marko Mäkelä made changes -
            igor Igor Babaev (Inactive) made changes -
            Description If the system variable @@in_subquery_conversion_threshold is set to a small enough number then queries with NOT IN predicate may return wrong result sets.
            The following test case demonstrate this:
            {noformat}
            create table t1 (a int, b int);
            insert into t1 values
             (1,2), (4,6), (9,7),(1,1), (2,5), (7,8);

            select * from t1
            where (a,b) not in ((1,2),(8,9), (5,1));

            set @@in_subquery_conversion_threshold= 2;
            select * from t1
            where (a,b) not in ((1,2),(8,9), (5,1));
            {noformat}
            When running this test we have:
            {noformat}
            MariaDB [test]>
            MariaDB [test]> select * from t1
                -> where (a,b) not in ((1,2),(8,9), (5,1));
            +------+------+
            | a | b |
            +------+------+
            | 4 | 6 |
            | 9 | 7 |
            | 1 | 1 |
            | 2 | 5 |
            | 7 | 8 |
            +------+------+

            MariaDB [test]> set @@in_subquery_conversion_threshold= 2;

            MariaDB [test]> select * from t1
                -> where (a,b) not in ((1,2),(8,9), (5,1));
            +------+------+
            | a | b |
            +------+------+
            | 1 | 2 |
            +------+------+
            {noformat}






            		If the system variable @@in_subquery_conversion_threshold is set to a small enough number then queries with NOT IN predicate in most cases return wrong result sets.
            The following test case demonstrate this:
            {noformat}
            create table t1 (a int, b int);
            insert into t1 values
             (1,2), (4,6), (9,7),(1,1), (2,5), (7,8);

            select * from t1
            where (a,b) not in ((1,2),(8,9), (5,1));

            set @@in_subquery_conversion_threshold= 2;
            select * from t1
            where (a,b) not in ((1,2),(8,9), (5,1));
            {noformat}
            When running this test we have:
            {noformat}
            MariaDB [test]>
            MariaDB [test]> select * from t1
                -> where (a,b) not in ((1,2),(8,9), (5,1));
            +------+------+
            | a | b |
            +------+------+
            | 4 | 6 |
            | 9 | 7 |
            | 1 | 1 |
            | 2 | 5 |
            | 7 | 8 |
            +------+------+

            MariaDB [test]> set @@in_subquery_conversion_threshold= 2;

            MariaDB [test]> select * from t1
                -> where (a,b) not in ((1,2),(8,9), (5,1));
            +------+------+
            | a | b |
            +------+------+
            | 1 | 2 |
            +------+------+
            {noformat}






            igor Igor Babaev (Inactive) made changes -
            Status Confirmed [ 10101 ] In Progress [ 3 ]
            serg Sergei Golubchik made changes -
            Priority Major [ 3 ] Blocker [ 1 ]
            igor Igor Babaev (Inactive) added a comment -

            A fix for this bug was pushed into 10.3

            igor Igor Babaev (Inactive) added a comment - A fix for this bug was pushed into 10.3
            igor Igor Babaev (Inactive) made changes -
            Fix Version/s 10.3.3 [ 22644 ]
            Fix Version/s 10.3 [ 22126 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            alice Alice Sherepa made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 83621 ] MariaDB v4 [ 153115 ]

            People

              igor Igor Babaev (Inactive)
              igor Igor Babaev (Inactive)
              Votes:
              0 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.