Details
-
Type:
Bug
-
Status: Open
-
Priority:
Major
-
Resolution: Unresolved
-
Affects Version/s: 10.0.17
-
Fix Version/s: 10.0
-
Component/s: Virtual Columns
-
Labels:
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)
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
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
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.