[MDEV-23005] sql_mode mixture: a table with DECODE() in a virtual column refuses to work - Jira

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.3(EOL), 10.4(EOL), 10.5
Fix Version/s: 10.5
Component/s: OTHER
Labels:
- Compatibility

Description

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)

Attachments

Issue Links

relates to

MDEV-10342 Providing compatibility for basic SQL built-in functions

Closed

MDEV-19632 Replication aborts with ER_SLAVE_CONVERSION_FAILED upon CREATE ... SELECT in ORACLE mode

Closed

MDEV-23040 sql_mode mixture: a table with TRIM() in DEFAULT refuses to INSERT

Open

Activity

Alexander Barkov added a comment - 2020-06-24 15:32

The reason of failure on ~~MDEV-19632~~ is the same:
the data type DATE does not get replaced to sql_mode independent name, e.g. MARIADB_DATE.

Alexander Barkov added a comment - 2020-06-24 15:32 The reason of failure on MDEV-19632 is the same: the data type DATE does not get replaced to sql_mode independent name, e.g. MARIADB_DATE.

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2020-06-24 15:27

Updated:: 2024-09-10 14:57

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server