[MDEV-23144] Combination of GENERATE ALWAYS and COLLATE does not work Created: 2020-07-10  Updated: 2021-04-19  Resolved: 2020-07-11

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Create Table
Affects Version/s: 10.5.4
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Hartmut Holzgraefe Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None

Issue Links:
Duplicate
is duplicated by MDEV-12161 Can't specify collation for virtual c... Closed

 Description   

E.g.

CREATE TABLE a (b varchar(1) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS ('x'));

leads to a syntax error near GENERATED.

The KB syntax page for CREATE TABLE says:

column_definition:
 
  data_type
 
    [NOT NULL | NULL] [DEFAULT default_value | (expression)]
 
    [AUTO_INCREMENT] [ZEROFILL] [UNIQUE [KEY] | [PRIMARY] KEY]
 
    [INVISIBLE] [{WITH|WITHOUT} SYSTEM VERSIONING]
 
    [COMMENT 'string'] [REF_SYSTEM_ID = value]
 
    [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
 
    [reference_definition]
 
  | data_type [GENERATED ALWAYS] 
  AS { { ROW {START|END} } | { (expression) [VIRTUAL | PERSISTENT | STORED] } }
 
      [UNIQUE [KEY]] [COMMENT 'string']

As CHARACTER SET and COLLATE are part of "data_type" the statement above should work.

On the other hand the parser grammer says

field_def:
 
          /* empty */ { }
 
        | attribute_list
 
        | attribute_list compressed_deprecated_column_attribute
 
        | attribute_list compressed_deprecated_column_attribute attribute_list
 
        | opt_generated_always AS virtual_column_func
 
          vcol_opt_specifier vcol_opt_attribute
 
        | opt_generated_always AS ROW_SYM START_SYM opt_asrow_attribute
 
        | opt_generated_always AS ROW_SYM END opt_asrow_attribute
 
        ;

As CHARACTER SET and COLLATE are part of "attribute_list", they indeed can't be combined with GENERATE ALWAYS.

I'm not sure whether there's an actual reason for this (e.g. charset and collation always determined by the generating expression), or whether the parser syntax should indeed be fixed.

 Comments   
Comment by Elena Stepanova [ 2020-07-10 ]

Looks like a duplicate of MDEV-12161.

Comment by Hartmut Holzgraefe [ 2020-07-10 ]

Yes, looks like a duplicate indeed

Generated at Thu Feb 08 09:20:10 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.