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

SHOW CREATE TABLE returns invalid DDL when using virtual columns along with a table collation

    Details

    • Sprint:
      10.0.23

      Description

      We can create a table with a virtual column and a collation:

      MariaDB [tmp]> CREATE TABLE vcol_test
           (
               v_col1 varchar(255) as (col1) persistent,
               col1 varchar(50)
           ) COLLATE=latin1_general_ci;
      Query OK, 0 rows affected (0.05 sec)

      noted: edited to make copy/paste easier

      Then let's see the definition with SHOW CREATE TABLE:

      MariaDB [tmp]> SHOW CREATE TABLE vcol_test;
      +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | Table     | Create Table                                                                                                                                                                                                                    |
      +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      | vcol_test | CREATE TABLE `vcol_test` (
        `v_col1` varchar(255) COLLATE latin1_general_ci AS (col1) PERSISTENT,
        `col1` varchar(50) COLLATE latin1_general_ci DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci |
      +-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)

      Let's drop the table and try to recreate it:

      MariaDB [tmp]> DROP TABLE vcol_test;
      Query OK, 0 rows affected (0.03 sec)
       
      MariaDB [tmp]> CREATE TABLE `vcol_test` (
          ->   `v_col1` varchar(255) COLLATE latin1_general_ci AS (col1) PERSISTENT,
          ->   `col1` varchar(50) COLLATE latin1_general_ci DEFAULT NULL
          -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
      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 'AS (col1) PERSISTENT,
        `col1` varchar(50) COLLATE latin1_general_ci DEFAULT NUL' at line 2

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                serg Sergei Golubchik
                Reporter:
                GeoffMontee Geoff Montee
              • Votes:
                1 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: