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

ColumnStore treating NULL and empty strings as the same

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5.2
    • 23.02.1
    • DMLProc, PrimProc
    • None
    • Relh8

    Description

      1.) Location, Call, and Code default to NULL instead of '' (as in innodb).

      CREATE TABLE `test_table1` (
      `Site` varchar(5) DEFAULT '',
      `Location` varchar(55) DEFAULT '',
      `Call` varchar(25) DEFAULT '',
      `Code` varchar(4) DEFAULT ''
      ) ENGINE=Columnstore;

      INSERT INTO test_table1(Site) VALUES ('tst');

      MariaDB [mpcol]> SELECT * FROM test_table1\G
      ----------------- 1. row ---------------------
      Site: tst
      Location: NULL
      Call: NULL
      Code: NULL
      1 row in set (0.043 sec)

      =================== INNODB ==================================
      CREATE TABLE `test_table1_innodb` (
      `Site` varchar(5) DEFAULT '',
      `Location` varchar(55) DEFAULT '',
      `Call` varchar(25) DEFAULT '',
      `Code` varchar(4) DEFAULT ''
      );

      INSERT INTO test_table1_innodb(Site) VALUES ('tst');

      SELECT * FROM test_table1_innodb\G
      ------------------- 1. row --------------------
      Site: tst
      Location:
      Call:
      Code:
      1 row in set (0.000 sec)

      2). Updating a field to an empty string updates the field to NULL

      UPDATE test_table1 SET Location='loc';
      --------------
      Site: tst
      Location: loc
      Call: NULL
      Code: NULL
      1 row in set (0.008 sec)

      UPDATE test_table1 SET Location='';

      MariaDB [my.test]> select * from test_table1\G
      ----------------------------
      Site: tst
      Location: NULL
      Call: NULL
      Code: NULL
      1 row in set (0.008 sec)

      3). Searching for an empty string returns records with NULL:

      select Code from test_table1 where Code=''\G
      ---------------
      Code: NULL
      1 row in set (0.008 sec)

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              mpflaum Maria M Pflaum
              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.