Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3(EOL), 10.4(EOL), 10.5
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
- relates to
-
MDEV-10342
Providing compatibility for basic SQL built-in functions
-
- Closed
-
-
-
- Closed
-
-
-
- Open
-
The same problem is repeatable with virtual columns:
);+-------+------------------------------------------------------------------------------------------------------------------------+| 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().