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

CS uses 'NULL' literal as NULL when UPDATEs varchar field

    XMLWordPrintable

Details

    • 2019-05, 2019-06

    Description

      CS converts 'NULL' literal into NULL value. Consider the output for Columnstore and InnoDB tables:

      MariaDB [test]> create table cs1(v8 varchar(8), t text ) engine=columnstore;
      Query OK, 0 rows affected (0.92 sec)
       
      MariaDB [test]> insert into cs1 values ('some','other'),('some2','other2');
      Query OK, 2 rows affected (0.43 sec)
      Records: 2  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> create table i1 (v8 varchar(8), t text);
      Query OK, 0 rows affected (0.53 sec)
       
      MariaDB [test]> insert into i1 select * from cs1;
      sQuery OK, 2 rows affected (0.58 sec)
      Records: 2  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> select * from i1;
      +-------+--------+
      | v8    | t      |
      +-------+--------+
      | some  | other  |
      | some2 | other2 |
      +-------+--------+
      2 rows in set (0.00 sec)
       
      MariaDB [test]> select * from cs1;
      +-------+--------+
      | v8    | t      |
      +-------+--------+
      | some  | other  |
      | some2 | other2 |
      +-------+--------+
      2 rows in set (0.01 sec)
       
      MariaDB [test]> update cs1 set v8 = 'NULL' where v8 = 'some';
      Query OK, 1 row affected (0.14 sec)
      Rows matched: 0  Changed: 0  Warnings: 0
       
      MariaDB [test]> select * from cs1 where v8 is null;
      +------+-------+
      | v8   | t     |
      +------+-------+
      | NULL | other |
      +------+-------+
      1 row in set (0.02 sec)
       
      MariaDB [test]> update i1 set v8 = 'NULL' where v8 = 'some';
      Query OK, 1 row affected (0.15 sec)
      Rows matched: 1  Changed: 1  Warnings: 0
       
      MariaDB [test]> select * from i1 where v8 is null;
      Empty set (0.00 sec)
       
      MariaDB [test]> update i1 set t = 'NULL' where v8 = 'some2';
      Query OK, 1 row affected (0.23 sec)
      Rows matched: 1  Changed: 1  Warnings: 0
       
      MariaDB [test]> select * from i1 where t is null;
      Empty set (0.00 sec)
       
      MariaDB [test]> update cs1 set t = 'NULL' where v8 = 'some2';
      Query OK, 1 row affected (0.15 sec)
      Rows matched: 0  Changed: 0  Warnings: 0
       
      MariaDB [test]> select * from cs1 where t is null;
      +-------+------+
      | v8    | t    |
      +-------+------+
      | some2 | NULL |
      +-------+------+
      1 row in set (0.02 sec)
      

      Attachments

        Activity

          People

            leonid.fedorov Leonid Fedorov
            drrtuy Roman
            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.