[MDEV-23040] sql_mode mixture: a table with TRIM() in DEFAULT refuses to INSERT - Jira

XML

Word

Printable

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.

relates to

MDEV-10342 Providing compatibility for basic SQL built-in functions

MDEV-19632 Replication aborts with ER_SLAVE_CONVERSION_FAILED upon CREATE ... SELECT in ORACLE mode

MDEV-23005 sql_mode mixture: a table with DECODE() in a virtual column refuses to work

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