Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-23005

sql_mode mixture: a table with DECODE() in a virtual column refuses to work

    XMLWordPrintable

    Details

      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

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration