Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-4642

NOT IN subquery containing an isnull in the OR predicate crashes server

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Blocker
    • Resolution: Fixed
    • 5.5.1
    • 5.6.3, 6.1.1
    • None
    • None

    Description

      The following query crashes the server:

      MariaDB [test]> create table cs1 (a int)engine=columnstore;
      Query OK, 0 rows affected (0.661 sec)
       
      MariaDB [test]> insert into cs1 values (1), (2), (3), (4), (null);
      Query OK, 5 rows affected (0.291 sec)
      Records: 5  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> create table cs2 (b int, c int)engine=columnstore;
      Query OK, 0 rows affected (0.597 sec)
       
      MariaDB [test]> insert into cs2 values (1, 100), (1, 101), (2, 200),
          -> (3, 300), (3, 301), (3, 302), (null, null);
      Query OK, 7 rows affected (0.250 sec)
      Records: 7  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> select * from cs1 where a not in (select b from cs2 where b is not null or b is null);
      ERROR 2013 (HY000): Lost connection to MySQL server during query
      

      However, if the OR arguments are interchanged, the crash does not happen:

      MariaDB [test]> select * from cs1 where a not in (select b from cs2 where b is null or b is not null);
      Empty set (0.020 sec)
      

      In addition, the following 2 queries return inconsistent results:

      MariaDB [test]> select * from cs1 where a not in (select b from cs2 where b=123 or b is null);
      +------+
      | a    |
      +------+
      |    1 |
      |    2 |
      |    3 |
      |    4 |
      | NULL |
      +------+
      5 rows in set (0.152 sec)
       
      MariaDB [test]> select * from cs1 where a not in (select b from cs2 where b is null or b=123);
      Empty set (0.021 sec)
      

      Attachments

        Activity

          People

            dleeyh Daniel Lee (Inactive)
            tntnatbry Gagan Goel (Inactive)
            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.