mirror of
https://github.com/MariaDB/server.git
synced 2025-02-04 21:02:17 +01:00
2b6d241ee4
The crash happened with an indexed virtual column whose value is evaluated using a function that has a different meaning in sql_mode='' vs sql_mode=ORACLE: - DECODE() - LTRIM() - RTRIM() - LPAD() - RPAD() - REPLACE() - SUBSTR() For example: CREATE TABLE t1 ( b VARCHAR(1), g CHAR(1) GENERATED ALWAYS AS (SUBSTR(b,0,0)) VIRTUAL, KEY g(g) ); So far we had replacement XXX_ORACLE() functions for all mentioned function, e.g. SUBSTR_ORACLE() for SUBSTR(). So it was possible to correctly re-parse SUBSTR_ORACLE() even in sql_mode=''. But it was not possible to re-parse the MariaDB version of SUBSTR() after switching to sql_mode=ORACLE. It was erroneously mis-interpreted as SUBSTR_ORACLE(). As a result, this combination worked fine: SET sql_mode=ORACLE; CREATE TABLE t1 ... g CHAR(1) GENERATED ALWAYS AS (SUBSTR(b,0,0)) VIRTUAL, ...; INSERT ... FLUSH TABLES; SET sql_mode=''; INSERT ... But the other way around it crashed: SET sql_mode=''; CREATE TABLE t1 ... g CHAR(1) GENERATED ALWAYS AS (SUBSTR(b,0,0)) VIRTUAL, ...; INSERT ... FLUSH TABLES; SET sql_mode=ORACLE; INSERT ... At CREATE time, SUBSTR was instantiated as Item_func_substr and printed in the FRM file as substr(). At re-open time with sql_mode=ORACLE, "substr()" was erroneously instantiated as Item_func_substr_oracle. Fix: The fix proposes a symmetric solution. It provides a way to re-parse reliably all sql_mode dependent functions to their original CREATE TABLE time meaning, no matter what the open-time sql_mode is. We take advantage of the same idea we previously used to resolve sql_mode dependent data types. Now all sql_mode dependent functions are printed by SHOW using a schema qualifier when the current sql_mode differs from the function sql_mode: SET sql_mode=''; CREATE TABLE t1 ... SUBSTR(a,b,c) ..; SET sql_mode=ORACLE; SHOW CREATE TABLE t1; -> mariadb_schema.substr(a,b,c) SET sql_mode=ORACLE; CREATE TABLE t2 ... SUBSTR(a,b,c) ..; SET sql_mode=''; SHOW CREATE TABLE t1; -> oracle_schema.substr(a,b,c) Old replacement names like substr_oracle() are still understood for backward compatibility and used in FRM files (for downgrade compatibility), but they are not printed by SHOW any more.
170 lines
6.1 KiB
Text
170 lines
6.1 KiB
Text
SET sql_mode=ORACLE;
|
|
#
|
|
# MDEV-15664 sql_mode=ORACLE: Make TRIM return NULL instead of empty string
|
|
#
|
|
SELECT TRIM('abc'), TRIM('abc ')||'.', '.'||TRIM(' abc ')||'.', TRIM(' '), TRIM(NULL), TRIM(SPACE(0)),TRIM(SPACE(10)) FROM dual;
|
|
TRIM('abc') TRIM('abc ')||'.' '.'||TRIM(' abc ')||'.' TRIM(' ') TRIM(NULL) TRIM(SPACE(0)) TRIM(SPACE(10))
|
|
abc abc. .abc. NULL NULL NULL NULL
|
|
SELECT TRIM(TRAILING 'abc' FROM 'abc');
|
|
TRIM(TRAILING 'abc' FROM 'abc')
|
|
NULL
|
|
SELECT TRIM(TRAILING 'abc' FROM 'abc ');
|
|
TRIM(TRAILING 'abc' FROM 'abc ')
|
|
abc
|
|
SELECT TRIM(TRAILING 'abc' FROM ' abc');
|
|
TRIM(TRAILING 'abc' FROM ' abc')
|
|
|
|
SELECT TRIM(LEADING 'abc' FROM 'abc');
|
|
TRIM(LEADING 'abc' FROM 'abc')
|
|
NULL
|
|
SELECT TRIM(LEADING 'abc' FROM 'abc ');
|
|
TRIM(LEADING 'abc' FROM 'abc ')
|
|
|
|
SELECT TRIM(LEADING 'abc' FROM ' abc');
|
|
TRIM(LEADING 'abc' FROM ' abc')
|
|
abc
|
|
SELECT TRIM(BOTH 'abc' FROM 'abc');
|
|
TRIM(BOTH 'abc' FROM 'abc')
|
|
NULL
|
|
SELECT TRIM(BOTH 'abc' FROM 'abc ');
|
|
TRIM(BOTH 'abc' FROM 'abc ')
|
|
|
|
SELECT TRIM(BOTH 'abc' FROM ' abc');
|
|
TRIM(BOTH 'abc' FROM ' abc')
|
|
|
|
SELECT RTRIM('abc'), RTRIM('abc ')||'.', RTRIM(' abc ')||'.', RTRIM(' '), RTRIM(NULL), RTRIM(SPACE(0)),RTRIM(SPACE(10)) FROM dual;
|
|
RTRIM('abc') RTRIM('abc ')||'.' RTRIM(' abc ')||'.' RTRIM(' ') RTRIM(NULL) RTRIM(SPACE(0)) RTRIM(SPACE(10))
|
|
abc abc. abc. NULL NULL NULL NULL
|
|
SELECT LTRIM('abc'), LTRIM('abc '), LTRIM(' abc '), LTRIM(' '), LTRIM(NULL), LTRIM(SPACE(0)),LTRIM(SPACE(10)) FROM dual;
|
|
LTRIM('abc') LTRIM('abc ') LTRIM(' abc ') LTRIM(' ') LTRIM(NULL) LTRIM(SPACE(0)) LTRIM(SPACE(10))
|
|
abc abc abc NULL NULL NULL NULL
|
|
CREATE TABLE t1 (c1 VARCHAR(10),ord INTEGER);
|
|
INSERT INTO t1 VALUES ('abc',1);
|
|
INSERT INTO t1 VALUES (SPACE(0),2);
|
|
INSERT INTO t1 VALUES ('',3);
|
|
INSERT INTO t1 VALUES (' ',4);
|
|
INSERT INTO t1 VALUES (' ',5);
|
|
INSERT INTO t1 VALUES (' a ',6);
|
|
INSERT INTO t1 VALUES ('aa',7);
|
|
INSERT INTO t1 VALUES ('aabb',8);
|
|
INSERT INTO t1 VALUES ('bbaa',9);
|
|
INSERT INTO t1 VALUES ('aabbaa',10);
|
|
SELECT ord,'['||c1||']','.'||COALESCE(TRIM(LEADING 'a' FROM c1),'NULL')||'.' FROM t1 ORDER BY ord;
|
|
ord '['||c1||']' '.'||COALESCE(TRIM(LEADING 'a' FROM c1),'NULL')||'.'
|
|
1 [abc] .bc.
|
|
2 [] .NULL.
|
|
3 [] .NULL.
|
|
4 [ ] . .
|
|
5 [ ] . .
|
|
6 [ a ] . a .
|
|
7 [aa] .NULL.
|
|
8 [aabb] .bb.
|
|
9 [bbaa] .bbaa.
|
|
10 [aabbaa] .bbaa.
|
|
SELECT ord,'['||c1||']','.'||COALESCE(TRIM(TRAILING 'a' FROM c1),'NULL')||'.' FROM t1 ORDER BY ord;
|
|
ord '['||c1||']' '.'||COALESCE(TRIM(TRAILING 'a' FROM c1),'NULL')||'.'
|
|
1 [abc] .abc.
|
|
2 [] .NULL.
|
|
3 [] .NULL.
|
|
4 [ ] . .
|
|
5 [ ] . .
|
|
6 [ a ] . a .
|
|
7 [aa] .NULL.
|
|
8 [aabb] .aabb.
|
|
9 [bbaa] .bb.
|
|
10 [aabbaa] .aabb.
|
|
SELECT ord,'['||c1||']','.'||COALESCE(TRIM(BOTH 'a' FROM c1),'NULL')||'.' FROM t1 ORDER BY ord;
|
|
ord '['||c1||']' '.'||COALESCE(TRIM(BOTH 'a' FROM c1),'NULL')||'.'
|
|
1 [abc] .bc.
|
|
2 [] .NULL.
|
|
3 [] .NULL.
|
|
4 [ ] . .
|
|
5 [ ] . .
|
|
6 [ a ] . a .
|
|
7 [aa] .NULL.
|
|
8 [aabb] .bb.
|
|
9 [bbaa] .bb.
|
|
10 [aabbaa] .bb.
|
|
SELECT ord,'['||c1||']',COALESCE(LTRIM(c1),'NULL') FROM t1 ORDER BY ord;
|
|
ord '['||c1||']' COALESCE(LTRIM(c1),'NULL')
|
|
1 [abc] abc
|
|
2 [] NULL
|
|
3 [] NULL
|
|
4 [ ] NULL
|
|
5 [ ] NULL
|
|
6 [ a ] a
|
|
7 [aa] aa
|
|
8 [aabb] aabb
|
|
9 [bbaa] bbaa
|
|
10 [aabbaa] aabbaa
|
|
SELECT ord,'['||c1||']',COALESCE(RTRIM(c1),'NULL')||'.' FROM t1 ORDER BY ord;
|
|
ord '['||c1||']' COALESCE(RTRIM(c1),'NULL')||'.'
|
|
1 [abc] abc.
|
|
2 [] NULL.
|
|
3 [] NULL.
|
|
4 [ ] NULL.
|
|
5 [ ] NULL.
|
|
6 [ a ] a.
|
|
7 [aa] aa.
|
|
8 [aabb] aabb.
|
|
9 [bbaa] bbaa.
|
|
10 [aabbaa] aabbaa.
|
|
EXPLAIN EXTENDED SELECT TRIM('abc'),
|
|
TRIM(BOTH 'a' FROM 'abc'),
|
|
TRIM(LEADING 'a' FROM 'abc'),
|
|
TRIM(TRAILING 'a' FROM 'abc') ;
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 select trim('abc') AS "TRIM('abc')",trim(both 'a' from 'abc') AS "TRIM(BOTH 'a' FROM 'abc')",trim(leading 'a' from 'abc') AS "TRIM(LEADING 'a' FROM 'abc')",trim(trailing 'a' from 'abc') AS "TRIM(TRAILING 'a' FROM 'abc')"
|
|
EXPLAIN EXTENDED SELECT RTRIM('abc'),
|
|
LTRIM('abc');
|
|
id select_type table type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
|
|
Warnings:
|
|
Note 1003 select rtrim('abc') AS "RTRIM('abc')",ltrim('abc') AS "LTRIM('abc')"
|
|
CREATE VIEW v1 AS SELECT ord,TRIM('abc'),RTRIM('abc'),LTRIM('abc'),
|
|
'['||c1||']',
|
|
TRIM(LEADING 'a' FROM c1),
|
|
TRIM(TRAILING 'a' FROM c1),
|
|
TRIM(BOTH 'a' FROM c1),
|
|
LTRIM(c1),
|
|
RTRIM(c1)
|
|
FROM t1 ORDER BY ord ;
|
|
SHOW CREATE VIEW v1;
|
|
View Create View character_set_client collation_connection
|
|
v1 CREATE VIEW "v1" AS select "t1"."ord" AS "ord",trim('abc') AS "TRIM('abc')",rtrim('abc') AS "RTRIM('abc')",ltrim('abc') AS "LTRIM('abc')",concat(concat('[',"t1"."c1"),']') AS "'['||c1||']'",trim(leading 'a' from "t1"."c1") AS "TRIM(LEADING 'a' FROM c1)",trim(trailing 'a' from "t1"."c1") AS "TRIM(TRAILING 'a' FROM c1)",trim(both 'a' from "t1"."c1") AS "TRIM(BOTH 'a' FROM c1)",ltrim("t1"."c1") AS "LTRIM(c1)",rtrim("t1"."c1") AS "RTRIM(c1)" from "t1" order by "t1"."ord" latin1 latin1_swedish_ci
|
|
SELECT * FROM v1;
|
|
ord TRIM('abc') RTRIM('abc') LTRIM('abc') '['||c1||']' TRIM(LEADING 'a' FROM c1) TRIM(TRAILING 'a' FROM c1) TRIM(BOTH 'a' FROM c1) LTRIM(c1) RTRIM(c1)
|
|
1 abc abc abc [abc] bc abc bc abc abc
|
|
2 abc abc abc [] NULL NULL NULL NULL NULL
|
|
3 abc abc abc [] NULL NULL NULL NULL NULL
|
|
4 abc abc abc [ ] NULL NULL
|
|
5 abc abc abc [ ] NULL NULL
|
|
6 abc abc abc [ a ] a a a a a
|
|
7 abc abc abc [aa] NULL NULL NULL aa aa
|
|
8 abc abc abc [aabb] bb aabb bb aabb aabb
|
|
9 abc abc abc [bbaa] bbaa bb bb bbaa bbaa
|
|
10 abc abc abc [aabbaa] bbaa aabb bb aabbaa aabbaa
|
|
DROP VIEW v1;
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1 (c1 VARCHAR(10) NOT NULL);
|
|
CREATE TABLE t2 AS SELECT TRIM(LEADING 'a' FROM c1) AS C1,
|
|
TRIM(TRAILING 'a' FROM c1) AS C2,
|
|
TRIM(BOTH 'a' FROM c1) AS C3,
|
|
LTRIM(c1) AS C4,
|
|
RTRIM(c1) AS C5
|
|
FROM t1;
|
|
SHOW CREATE TABLE t2;
|
|
Table Create Table
|
|
t2 CREATE TABLE "t2" (
|
|
"C1" varchar(10) DEFAULT NULL,
|
|
"C2" varchar(10) DEFAULT NULL,
|
|
"C3" varchar(10) DEFAULT NULL,
|
|
"C4" varchar(10) DEFAULT NULL,
|
|
"C5" varchar(10) DEFAULT NULL
|
|
)
|
|
DROP TABLE t2;
|
|
DROP TABLE t1;
|
|
CREATE TABLE trim_oracle (trim_oracle int);
|
|
DROP TABLE trim_oracle;
|