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

    XMLWordPrintable

Details

    • 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

              serg Sergei Golubchik
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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