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.
47 lines
1.7 KiB
Text
47 lines
1.7 KiB
Text
--source include/have_innodb.inc
|
|
|
|
--echo #
|
|
--echo # MDEV-27744 LPAD in vcol created in ORACLE mode makes table corrupted in non-ORACLE
|
|
--echo #
|
|
|
|
FLUSH TABLES;
|
|
SET sql_mode='';
|
|
CREATE TABLE t (d INT,b VARCHAR(1),c CHAR(1),g CHAR(1) GENERATED ALWAYS AS (SUBSTR(b,0,0)) VIRTUAL,PRIMARY KEY(b),KEY g(g)) ENGINE=InnoDB;
|
|
--error ER_WRONG_VALUE_COUNT_ON_ROW
|
|
INSERT INTO t VALUES (0);
|
|
SET sql_mode='ORACLE';
|
|
INSERT INTO t SET c=REPEAT (1,0);
|
|
--error ER_BAD_FIELD_ERROR
|
|
ALTER TABLE t CHANGE COLUMN a b INT;
|
|
DELETE FROM t;
|
|
SET sql_mode='';
|
|
FLUSH TABLES;
|
|
INSERT INTO t SET c='0';
|
|
DROP TABLE t;
|
|
FLUSH TABLES;
|
|
|
|
SET sql_mode='';
|
|
CREATE TABLE t (a INT(1),d INT(1),b VARCHAR(1),c CHAR(1),vadc INT(1) GENERATED ALWAYS AS ( (a + length (d))) STORED,vbc CHAR(1) GENERATED ALWAYS AS (SUBSTR(b,0,0)) VIRTUAL,vbidxc CHAR(1) GENERATED ALWAYS AS (SUBSTR(b,0,0)) VIRTUAL,PRIMARY KEY(b (1),a,d),KEY d (d),KEY a (a),KEY c_renamed (c (1),b (1)),KEY b (b (1),c (1),a),KEY vbidxc (vbidxc),KEY a_2 (a,vbidxc),KEY vbidxc_2 (vbidxc,d)) DEFAULT CHARSET=latin1 ENGINE=InnoDB;
|
|
--error ER_WRONG_VALUE_COUNT_ON_ROW
|
|
INSERT INTO t VALUES (0,0,1,0,1,0,1,0,0);
|
|
SET SESSION sql_mode='ORACLE';
|
|
INSERT INTO t SET c=REPEAT (1,0);
|
|
--error ER_DUP_FIELDNAME
|
|
ALTER TABLE t CHANGE COLUMN a b CHAR(1);
|
|
DELETE FROM t;
|
|
SET SESSION sql_mode=DEFAULT;
|
|
DROP TABLE t;
|
|
|
|
SET sql_mode='';
|
|
CREATE TABLE t1 (d INT,b VARCHAR(1),c CHAR(1),g CHAR(1) GENERATED ALWAYS AS (SUBSTR(b,0,0)) VIRTUAL,PRIMARY KEY(b),KEY g(g)) ENGINE=InnoDB;
|
|
--error ER_WRONG_VALUE_COUNT_ON_ROW
|
|
INSERT INTO t1 VALUES (0);
|
|
SET sql_mode='ORACLE';
|
|
INSERT INTO t1 SET c=REPEAT (1,0);
|
|
--error ER_BAD_FIELD_ERROR
|
|
ALTER TABLE t1 CHANGE COLUMN a b INT;
|
|
DELETE FROM t1;
|
|
SET sql_mode='';
|
|
FLUSH TABLES;
|
|
INSERT INTO t1 SET c='0';
|
|
DROP TABLE t1;
|