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

CHAR(2) NOT NULL: Empty string or SPACE(N) get converted to DEFAULT

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 5.5.1, 5.6.1
    • Fix Version/s: 23.02
    • Component/s: PrimProc
    • Labels:
      None

      Description

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a CHAR(2) NOT NULL DEFAULT 'aa') ENGINE=ColumnStore;
      INSERT INTO t1 VALUES ('');
      INSERT INTO t1 VALUES (' ');
      INSERT INTO t1 VALUES ('  ');
      SELECT HEX(a) FROM t1;
      

      +--------+
      | HEX(a) |
      +--------+
      | 6161   |
      | 6161   |
      | 6161   |
      +--------+
      

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a CHAR(2) NOT NULL DEFAULT ' ') ENGINE=ColumnStore;
      INSERT INTO t1 VALUES ('');
      INSERT INTO t1 VALUES (' ');
      INSERT INTO t1 VALUES ('  ');
      SELECT HEX(a) FROM t1;
      

      +--------+
      | HEX(a) |
      +--------+
      | 20     |
      | 20     |
      | 20     |
      +--------+
      

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (a CHAR(2) NOT NULL DEFAULT '  ') ENGINE=ColumnStore;
      INSERT INTO t1 VALUES ('');
      INSERT INTO t1 VALUES (' ');
      INSERT INTO t1 VALUES ('  ');
      SELECT HEX(a) FROM t1;
      

       

      In all above cases, empty strings as well as strings consisting only of space characters were replaced to the DEFAULT value.

      Looks wrong. Strings consisting of only spaces should never be replaced to DEFAULT.

      CHAR values should be padded with spaces to their full length.
      For MariaDB compatibility, the SELECT behaviour should depend on sql_mode=PAD_CHAR_TO_FULL_LENGTH:

      • trailing spaces should be preserved on SELECT if PAD_CHAR_TO_FULL_LENGTH is set
      • trailing spaces should be trimmed on SELECT, if PAD_CHAR_TO_FULL_LENGTH is not set

      Also, inserting an empty string should:

      • either fail on NOT NULL violation for now
      • or store the true empty string (when we fix the flaw that an empty string is NULL)

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              bar Alexander Barkov
              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.