[MDEV-23040] sql_mode mixture: a table with TRIM() in DEFAULT refuses to INSERT Created: 2020-06-29  Updated: 2020-06-29

Status: Open
Project: MariaDB Server
Component/s: Data types, Parser
Affects Version/s: 10.3, 10.4, 10.5
Fix Version/s: 10.3

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: Compatibility

Issue Links:
Relates
relates to MDEV-10342 Providing compatibility for basic SQL... Closed
relates to MDEV-19632 Replication aborts with ER_SLAVE_CONV... Closed
relates to MDEV-23005 sql_mode mixture: a table with DECODE... Open

 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.

 Comments   
Comment by Alexander Barkov [ 2020-06-29 ]

The same problem is repeatable with virtual columns:

SET sql_mode=DEFAULT;
 
CREATE OR REPLACE TABLE t1 (
 
  a VARCHAR(30),
 
  b VARCHAR(30) GENERATED ALWAYS AS (TRIM(a))
 
);
 
SET sql_mode=ORACLE;
 
FLUSH TABLE t1;
 
SHOW CREATE TABLE t1;


+-------+------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                           |
 
+-------+------------------------------------------------------------------------------------------------------------------------+
 
| t1    | CREATE TABLE "t1" (
 
  "a" varchar(30) DEFAULT NULL,
 
  "b" varchar(30) GENERATED ALWAYS AS (trim_oracle("a")) VIRTUAL
 
) |
 
+-------+------------------------------------------------------------------------------------------------------------------------+

Notice, TRIM() was replaced to TRIM_ORACLE().

Generated at Thu Feb 08 09:19:23 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.