[MDEV-3259] LP:610890 - SHOW CREATE TABLE produces invalid SQL when used with virtual columns with non-default character sets Created: 2010-07-28  Updated: 2015-02-20  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Philip Stoev (Inactive) Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug610890.xml    

 Description   

SHOW CREATE TABLE produces a CREATE TABLE statement where any CHARACTER SET clauses are incorrectly placed, making the SQL invalid. SHOW CREATE TABLE is used by mysqldump.

mysql> create table t1 (f1 char(32), v2 char(32) character set ucs2 AS (f1) VIRTUAL);
mysql> show create table t1\G

                                                      • 1. row ***************************
                                                        Table: t1
                                                        Create Table: CREATE TABLE `t1` (
                                                        `f1` char(32) DEFAULT NULL,
                                                        `v2` char(32) AS (f1) VIRTUAL CHARACTER SET ucs2
                                                        ) ENGINE=InnoDB DEFAULT CHARSET=latin1
                                                        1 row in set (0.00 sec)

This SQL is not valid – CHARACTER SET was placed after VIRTUAL, even though it must be between char(32) and AS.

All of the following must come before the AS () virtual column definition:

  • field type and dimensions;
  • unsigned
  • zerofill
  • character set
  • collation

 Comments   
Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 610890

Comment by Claudio Nanni [ 2015-02-20 ]

Hi,

I read 'Fixed' but this is still present in 5.5.40,
a non default collation added in the Table definition is automatically added to the PERSISTENT column definition which is not yet supported thou.

MariaDB [test]> status
--------------
bin/mysql Ver 15.1 Distrib 5.5.40-MariaDB, for Linux (x86_64) using readline 5.1

  1. no collation specified

DROP TABLE table1;
CREATE TABLE table1 ( a INT NOT NULL, b VARCHAR(32), c INT AS (a mod 10) VIRTUAL, d VARCHAR(5) AS (left(b,5)) PERSISTENT);

  1. no problem
    SHOW CREATE TABLE table1;

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

table1 CREATE TABLE `table1` (
`a` int(11) NOT NULL,
`b` varchar(32) DEFAULT NULL,
`c` int(11) AS (a mod 10) VIRTUAL,
`d` varchar(5) AS (left(b,5)) PERSISTENT
) ENGINE=InnoDB DEFAULT CHARSET=latin1

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  1. specify only character set
    DROP TABLE table2;
    CREATE TABLE table2 ( a INT NOT NULL, b VARCHAR(32), c INT AS (a mod 10) VIRTUAL, d VARCHAR(5) AS (left(b,5)) PERSISTENT) character set utf8;
    Query OK, 0 rows affected (0.05 sec)
  1. no problem
    SHOW CREATE TABLE table2;
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    table2 CREATE TABLE `table2` (
    `a` int(11) NOT NULL,
    `b` varchar(32) DEFAULT NULL,
    `c` int(11) AS (a mod 10) VIRTUAL,
    `d` varchar(5) AS (left(b,5)) PERSISTENT
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

  1. specify charset and collation
    DROP TABLE table3;
    CREATE TABLE table3 ( a INT NOT NULL, b VARCHAR(32), c INT AS (a mod 10) VIRTUAL, d VARCHAR(5) AS (left(b,5)) PERSISTENT) character set utf8 collate utf8_unicode_ci;
    Query OK, 0 rows affected (0.06 sec)
  1. problem: implicitly added the collaton to PERSISTENT column definition, which is not supported yet
    SHOW CREATE TABLE table3;
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    table3 CREATE TABLE `table3` (
    `a` int(11) NOT NULL,
    `b` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
    `c` int(11) AS (a mod 10) VIRTUAL,
    `d` varchar(5) COLLATE utf8_unicode_ci AS (left(b,5)) PERSISTENT
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE `table3` (
-> `a` int(11) NOT NULL,
-> `b` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
-> `c` int(11) AS (a mod 10) VIRTUAL,
-> `d` varchar(5) COLLATE utf8_unicode_ci AS (left(b,5)) PERSISTENT
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_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 (left(b,5)) PERSISTENT
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unic' at line 5

  1. removing the explicit collation definition, fixes it
    DROP TABLE table4;
    CREATE TABLE `table4` ( `a` int(11) NOT NULL, `b` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL, `c` int(11) AS (a mod 10) VIRTUAL, `d` varchar(5) AS (left(b,5)) PERSISTENT ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
    Query OK, 0 rows affected (0.05 sec)
  1. of course it is readded
    SHOW CREATE TABLE table4;
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    table4 CREATE TABLE `table4` (
    `a` int(11) NOT NULL,
    `b` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
    `c` int(11) AS (a mod 10) VIRTUAL,
    `d` varchar(5) COLLATE utf8_unicode_ci AS (left(b,5)) PERSISTENT
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Generated at Thu Feb 08 06:47:23 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.