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

    • 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

            There are two problems here.

            First, that COLLATE clause is propagated from the table level to column level. This part doesn't relate to virtual columns, it happens for all columns.

            Secondly, that COLLATE is not accepted for virtual columns.
            See also the old issue MDEV-3259.

            elenst Elena Stepanova added a comment - There are two problems here. First, that COLLATE clause is propagated from the table level to column level. This part doesn't relate to virtual columns, it happens for all columns. Secondly, that COLLATE is not accepted for virtual columns. See also the old issue MDEV-3259 .
            danblack Daniel Black added a comment -

            The second part is due to sql_yacc.yy

            --- a/sql/sql_yacc.yy
            +++ b/sql/sql_yacc.yy
            @@ -6154,7 +6154,7 @@ field_spec:
             
             field_def:
                       opt_attribute
                    | opt_generated_always AS
                       '(' virtual_column_func ')'
                       vcol_opt_specifier vcol_opt_attribute

            opt_attribute includes the COLLATE (and COMMENT) bits that should also possible with virtual columns.

            I've included a patch. Patch doesn't work because the same options are on both sides o the yacc |. I don't know how to resolve that.

            danblack Daniel Black added a comment - The second part is due to sql_yacc.yy --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -6154,7 +6154,7 @@ field_spec:   field_def: opt_attribute | opt_generated_always AS '(' virtual_column_func ')' vcol_opt_specifier vcol_opt_attribute opt_attribute includes the COLLATE (and COMMENT) bits that should also possible with virtual columns. I've included a patch. Patch doesn't work because the same options are on both sides o the yacc |. I don't know how to resolve that.
            danblack Daniel Black added a comment -

            Solved part 2 of allowing collation on a virtual column..

            Part 1, displaying collation (and character sets) on virtual columns when set as table options still remains.

            danblack Daniel Black added a comment - Solved part 2 of allowing collation on a virtual column.. Part 1, displaying collation (and character sets) on virtual columns when set as table options still remains.
            danblack Daniel Black added a comment -

            tried porting same patch to 10.0 however the collation was ignored. Didn't put a lot of effort in there. As such pull request is against 10.1. 10.0 has a different parser constructs as you know.

            danblack Daniel Black added a comment - tried porting same patch to 10.0 however the collation was ignored. Didn't put a lot of effort in there. As such pull request is against 10.1. 10.0 has a different parser constructs as you know.

            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.