Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-23040

sql_mode mixture: a table with TRIM() in DEFAULT refuses to INSERT

    XMLWordPrintable

Details

    Description

      I create a table and insert one row:

      SET sql_mode=DEFAULT;
      CREATE OR REPLACE TABLE t1 (a VARCHAR(30) NOT NULL DEFAULT TRIM(''));
      INSERT INTO t1 VALUES (DEFAULT);
      SELECT a, LENGTH(a) FROM t1;
      

      +---+-----------+
      | a | LENGTH(a) |
      +---+-----------+
      |   |         0 |
      +---+-----------+
      

      Looks good so far.

      Now I change sql_mode and insert one more row:

      SET sql_mode=ORACLE;
      INSERT INTO t1 VALUES (DEFAULT);
      SELECT a, LENGTH(a) FROM t1;
      

      +---+-----------+
      | a | LENGTH(a) |
      +---+-----------+
      |   |         0 |
      |   |         0 |
      +---+-----------+
      

      Still looks good so far.

      Now I force the table to reopen and insert one more row:

      FLUSH TABLES;
      SET sql_mode=ORACLE;
      INSERT INTO t1 VALUES (DEFAULT);
      

      ERROR 1048 (23000): Column 'a' cannot be null
      

      Ooops.

      SHOW CREATE TABLE t1;
      

      +-------+--------------------------------------------------------------------------+
      | Table | Create Table                                                             |
      +-------+--------------------------------------------------------------------------+
      | t1    | CREATE TABLE "t1" (
        "a" varchar(30) NOT NULL DEFAULT trim_oracle('')
      ) |
      +-------+--------------------------------------------------------------------------+
      

      The default implementation of TRIM() was replaced to Oracle-compatible implementation. Looks wrong.

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.