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

NOT IN subquery for some SELECTs and DMLs does not work as expected

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Critical
    • Resolution: Unresolved
    • 6.2.2
    • Icebox
    • MDB Plugin
    • None

    Description

      Certain NOT IN subqueries for SELECTs and DMLs don't work as expected.

      MariaDB [test]> create table t1 (a int, b varchar(100))engine=columnstore;
      Query OK, 0 rows affected (0.213 sec)
       
      MariaDB [test]> create table i1 (a int, b varchar(100));
      Query OK, 0 rows affected (0.007 sec)
       
      MariaDB [test]> insert into t1 values (1, 'val'), (2, 'val'), (null, 'nullval'), (4, 'val');
      Query OK, 4 rows affected (0.507 sec)
      Records: 4  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> insert into i1 values (1, 'val'), (3, 'val'), (null, 'nullval'), (4, 'val');
      Query OK, 4 rows affected (0.004 sec)
      Records: 4  Duplicates: 0  Warnings: 0
      -- Works as expected
      MariaDB [test]> select * from i1 where a not in (select a from t1);
      +------+------+
      | a    | b    |
      +------+------+
      |    3 | val  |
      +------+------+
      1 row in set (0.128 sec)
       
      -- Doesn't work as expected
      MariaDB [test]> select * from t1 where a not in (select a from i1);
      Empty set (0.038 sec)
       
      -- Doesn't work as expected
      MariaDB [test]> update i1 set b='Update' where a not in (select a from t1);
      Query OK, 0 rows affected (0.063 sec)
      Rows matched: 0  Changed: 0  Warnings: 0
       
      MariaDB [test]> select * from i1;
      +------+---------+
      | a    | b       |
      +------+---------+
      |    1 | val     |
      |    3 | val     |
      | NULL | nullval |
      |    4 | val     |
      +------+---------+
      4 rows in set (0.001 sec)
       
      -- Doesn't work as expected, updates more records than required
      MariaDB [test]> update t1 set b='Update' where a not in (select a from i1);
      Query OK, 3 rows affected (0.447 sec)
      Rows matched: 3  Changed: 3  Warnings: 0
       
      MariaDB [test]> select * from t1;
      +------+---------+
      | a    | b       |
      +------+---------+
      |    1 | Update  |
      |    2 | Update  |
      | NULL | nullval |
      |    4 | Update  |
      +------+---------+
      4 rows in set (0.028 sec)
       
      -- works as expected
      MariaDB [test]> update t1 set b='Update2' where a not in (select a from t1);
      Query OK, 0 rows affected (0.247 sec)
      Rows matched: 0  Changed: 0  Warnings: 0
       
      MariaDB [test]> select * from t1;
      +------+---------+
      | a    | b       |
      +------+---------+
      |    1 | Update  |
      |    2 | Update  |
      | NULL | nullval |
      |    4 | Update  |
      +------+---------+
      4 rows in set (0.016 sec)
      

      As part of the fix for this issue, the test cases for the following queries should also be added:
      1. SELECTs involving IN/NOT IN subquery with a CS and a foreign table.
      2. UPDATEs and DELETEs involving IN/NOT IN subquery:

      • With a CS and a foreign table.
      • With two CS tables.

      Attachments

        Issue Links

          Activity

            People

              leonid.fedorov Leonid Fedorov
              tntnatbry Gagan Goel (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.