|
Note, DECODE() is parsed differently depending on sql_mode. There are two different implementations:
- for sql_mode=DEFAULT
- for sql_mode=ORACLE.
If I change sql_mode after creating a table using DECODE() in a virtual column, the table becomes not functional in some cases.
Working example
If I create a table using sql_mode=ORACLE with a column using Oracle-style DECODE() function, later I can see the table definition even when sql_mode=ORACLE.
SET sql_mode=ORACLE;
|
CREATE OR REPLACE TABLE t1 (a INT, b VARCHAR(32) GENERATED ALWAYS AS (DECODE(a, 1,'a1',2,'a2')));
|
SET sql_mode=DEFAULT;
|
SHOW CREATE TABLE t1;
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| t1 | CREATE TABLE `t1` (
|
`a` int(11) DEFAULT NULL,
|
`b` varchar(32) GENERATED ALWAYS AS (decode_oracle(`a`,1,'a1',2,'a2')) VIRTUAL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Notice, DECODE() was translated to DECODE_ORACLE(). Looks fine so far.
Non working example
Now if I do the other way around, i.e. create a table with sql_mode=DEFAULT using MariaDB-style DECODE()), later I cannot see its definition with sql_mode=ORACLE:
SET sql_mode=DEFAULT;
|
CREATE OR REPLACE TABLE t1 (a VARCHAR(32), b VARCHAR(32) GENERATED ALWAYS AS (HEX(DECODE('a', 'x'))));
|
SET sql_mode=ORACLE;
|
SHOW CREATE TABLE t1;
|
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '))'
|
The problem happens because DECODE() stays in the table definition as such - it does not get translated to unabiguous sql_mode independent name, for example DECODE_MARIADB().
Note, DML actions on this table also return the same error:
SET sql_mode=DEFAULT;
|
CREATE OR REPLACE TABLE t1 (a VARCHAR(32), b VARCHAR(32) GENERATED ALWAYS AS (HEX(DECODE('a', 'x'))));
|
SET sql_mode=ORACLE;
|
DELETE FROM t1;
|
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '))' at line 1
|
SET sql_mode=DEFAULT;
|
CREATE OR REPLACE TABLE t1 (a VARCHAR(32), b VARCHAR(32) GENERATED ALWAYS AS (HEX(DECODE('a', 'x'))));
|
SET sql_mode=ORACLE;
|
TRUNCATE TABLE t1;
|
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '))' at line 1
|
Information schema queries are also affected: they do not return any useful information:
SET sql_mode=DEFAULT;
|
CREATE OR REPLACE TABLE t1 (a VARCHAR(32), b VARCHAR(32) GENERATED ALWAYS AS (HEX(DECODE('a', 'x'))));
|
SET sql_mode=ORACLE;
|
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1'\G
|
*************************** 1. row ***************************
|
TABLE_CATALOG: def
|
TABLE_SCHEMA: test
|
TABLE_NAME: t1
|
TABLE_TYPE: BASE TABLE
|
ENGINE: NULL
|
VERSION: NULL
|
ROW_FORMAT: NULL
|
TABLE_ROWS: NULL
|
AVG_ROW_LENGTH: NULL
|
DATA_LENGTH: NULL
|
MAX_DATA_LENGTH: NULL
|
INDEX_LENGTH: NULL
|
DATA_FREE: NULL
|
AUTO_INCREMENT: NULL
|
CREATE_TIME: NULL
|
UPDATE_TIME: NULL
|
CHECK_TIME: NULL
|
TABLE_COLLATION: NULL
|
CHECKSUM: NULL
|
CREATE_OPTIONS: NULL
|
TABLE_COMMENT: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '))' at line 1
|
MAX_INDEX_LENGTH: NULL
|
TEMPORARY: NULL
|
1 row in set, 1 warning (0.003 sec)
|
|