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

NOT IN subquery does not return rows with NULL qualifying column values

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 1.1.6
    • 1.4.3
    • PrimProc
    • None
    • 2019-06, 2020-1, 2020-2

    Description

      When an outer query column values is NULL and the subquery is returning an empty set, the outer query and the predicate is NULL, the row is supposed to be part of the result set. But the column store does not return such rows. For example: I got the following result from INNODB:

      {{select t1.c1,t1.c2, t1.c3 from t1 where t1.c1 not in (select t2.c1 from t2 where t2.c2 >100)
      --------------

      ------------------------

      c1 c2 c3

      ------------------------

      1 1 char-row1-c3c4
      NULL NULL char-row2
      3 30 NULL
      4 40 char-row4-c3
      5 NULL char-row5-c3
      6 6 char-row6-c3
      NULL 7 NULL

      ------------------------

      Where for my column store "mydb" it produced the following result:

      select t1.c1,t1.c2, t1.c3 from t1 where t1.c1 not in (select t2.c1 from t2 where t2.c2 >100)
      --------------

      ------------------------

      c1 c2 c3

      ------------------------

      1 1 char-row1-c3c4
      3 30 NULL
      4 40 char-row4-c3
      5 NULL char-row5-c3
      6 6 char-row6-c3

      ------------------------}}

      To reproduce the problem:

      create database mydb;
      create database innodb;
      run the bug2_setup.sql to create and load the tables.
      bug2.sql contains the test sql query.

      Attachments

        Issue Links

          Activity

            People

              dleeyh Daniel Lee (Inactive)
              rprakash Ravi Prakash (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.