| Bug #93233 | Server permits NOT NULL in data type contrary to specification | ||
|---|---|---|---|
| Submitted: | 16 Nov 2018 17:37 | Modified: | 29 Jan 2019 13:55 |
| Reporter: | monty solomon | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
| Version: | 5.7.23 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[16 Nov 2018 17:37]
monty solomon
[16 Nov 2018 17:39]
monty solomon
It rejects it when NOT NULL appears before the CHARACTER SET CREATE TABLE c ( a VARCHAR(20) NOT NULL, b VARCHAR(60) NOT NULL CHARACTER SET ascii, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=ascii ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHARACTER SET ascii, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=ascii' at line 3
[16 Nov 2018 17:59]
monty solomon
It looks like it allows the COLLATE after the NOT NULL
CREATE TABLE c (
a VARCHAR(20) NOT NULL,
b VARCHAR(60) CHARACTER SET ascii NOT NULL COLLATE ascii_bin,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii ;
mysql> CREATE TABLE c (
-> a VARCHAR(20) NOT NULL,
-> b VARCHAR(60) CHARACTER SET ascii NOT NULL COLLATE ascii_bin,
-> PRIMARY KEY (`a`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=ascii ;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> SHOW CREATE TABLE c\G
*************************** 1. row ***************************
Table: c
Create Table: CREATE TABLE `c` (
`a` varchar(20) NOT NULL,
`b` varchar(60) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii
1 row in set (0.00 sec)
[16 Nov 2018 18:01]
monty solomon
In the description of the data_type field the doc states https://dev.mysql.com/doc/refman/5.7/en/create-table.html Character data types (CHAR, VARCHAR, TEXT) can include CHARACTER SET and COLLATE attributes to specify the character set and collation for the column.
[21 Nov 2018 16:32]
MySQL Verification Team
Hi, Thank you for your bug report. This is a documentation bug. Our reference manual must specify when can NULL or NOT NULL in relation to the character set and collation definitions. Also, when it is acceptable to have CHARACTER SET and when CHARSET. Verified as reported.
[21 Nov 2018 16:32]
MySQL Verification Team
It will be also examined whether there is any parser bug within this report.
[28 Nov 2018 15:40]
Paul DuBois
re: "when it is acceptable to have CHARACTER SET and when CHARSET" Always. https://dev.mysql.com/doc/refman/8.0/en/string-type-overview.html https://dev.mysql.com/doc/refman/8.0/en/create-table.html Both sections state: CHARSET is a synonym for CHARACTER SET.
[29 Nov 2018 14:01]
Paul DuBois
Posted by developer:
As it turns out, this is not an issue regarding NULL/NOT NULL.
Per the SQL standard and MySQL, COLLATE is not part of the data type,
it is one of the attributes (like NULL and NOT NULL) that follow the
data type. This is why NOT NULL cannot preceded CHARACTER SET, but
can precede COLLATE.
I have modified the syntax for the CREATE TABLE section thus:
column_definition:
data_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
[COLLATE collation_name]
[COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
[STORAGE {DISK|MEMORY|DEFAULT}]
[reference_definition]
And modified the later CHARACTER SET/COLLATION description:
Character data types (CHAR, VARCHAR, the TEXT types, ENUM, SET, and any synonyms) can include CHARACTER SET to specify the character set for the column. CHARSET is a synonym for CHARACTER SET. A collation for the character set can be specified with the COLLATE attribute, along with any other attributes.
[30 Nov 2018 3:01]
monty solomon
The column_definition specification states a specific ordering but it appears that those options can appear in any order and that they can be duplicated.
column_definition:
data_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
[COLLATE collation_name]
[COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
[STORAGE {DISK|MEMORY|DEFAULT}]
[reference_definition]
mysql> drop table if exists ordering; CREATE TABLE ordering(id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE COMMENT 'test' PRIMARY KEY UNIQUE KEY NOT NULL, team varchar(32) CHARACTER SET ascii COMMENT 'test' COLLATE ascii_bin UNIQUE KEY NOT NULL); show create table ordering\G
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.04 sec)
*************************** 1. row ***************************
Table: ordering
Create Table: CREATE TABLE `ordering` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'test',
`team` varchar(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL COMMENT 'test',
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
UNIQUE KEY `team` (`team`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)
[30 Nov 2018 3:03]
monty solomon
The server doesn't complain about invalid collation and character sets mixes. It seems to ignore the collation.
The COLLATE ascii_bin for the BIGINT id column is ignored.
mysql> drop table if exists ordering; CREATE TABLE ordering(id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE COMMENT 'test' PRIMARY KEY COLLATE ascii_bin UNIQUE KEY NOT NULL, team varchar(32) CHARACTER SET ascii COMMENT 'test' COLLATE ascii_bin UNIQUE KEY NOT NULL); show create table ordering\G
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.04 sec)
*************************** 1. row ***************************
Table: ordering
Create Table: CREATE TABLE `ordering` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'test',
`team` varchar(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL COMMENT 'test',
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
UNIQUE KEY `team` (`team`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)
[30 Nov 2018 3:18]
monty solomon
It looks like the COLLATE option must be part of the data_type in the second entry of the column_definition.
| data_type [GENERATED ALWAYS] AS (expression)
[VIRTUAL | STORED] [NOT NULL | NULL]
[UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
mysql> drop table if exists ordering; CREATE TABLE ordering(id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE COMMENT 'test' PRIMARY KEY COLLATE ascii_bin UNIQUE KEY NOT NULL, team varchar(32) CHARACTER SET ascii COMMENT 'test' COLLATE ascii_bin UNIQUE KEY NOT NULL, teamgen varchar(32) CHARACTER SET ascii COLLATE ascii_bin AS (UPPER(team)) NOT NULL); show create table ordering\G
Query OK, 0 rows affected, 1 warning (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
*************************** 1. row ***************************
Table: ordering
Create Table: CREATE TABLE `ordering` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'test',
`team` varchar(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL COMMENT 'test',
`teamgen` varchar(32) CHARACTER SET ascii COLLATE ascii_bin GENERATED ALWAYS AS (upper(`team`)) VIRTUAL NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
UNIQUE KEY `team` (`team`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)
mysql> drop table if exists ordering; CREATE TABLE ordering(id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE COMMENT 'test' PRIMARY KEY COLLATE ascii_bin UNIQUE KEY NOT NULL, team varchar(32) CHARACTER SET ascii COMMENT 'test' COLLATE ascii_bin UNIQUE KEY NOT NULL, teamgen varchar(32) CHARACTER SET ascii NOT NULL COLLATE ascii_bin AS (UPPER(team))); show create table ordering\G
Query OK, 0 rows affected, 1 warning (0.00 sec)
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS (UPPER(team)))' at line 1
[30 Nov 2018 3:20]
monty solomon
It looks like some ordering is required as specified for the second entry
Here NOT NULL is before AS and is rejected.
mysql> drop table if exists ordering; CREATE TABLE ordering(id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE COMMENT 'test' PRIMARY KEY COLLATE ascii_bin UNIQUE KEY NOT NULL, team varchar(32) CHARACTER SET ascii COMMENT 'test' COLLATE ascii_bin UNIQUE KEY NOT NULL, teamgen varchar(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL AS (UPPER(team))); show create table ordering\G
Query OK, 0 rows affected, 1 warning (0.00 sec)
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS (UPPER(team)))' at line 1
Here NOT NULL is after UNIQUE and is okay
mysql> drop table if exists ordering; CREATE TABLE ordering(id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE COMMENT 'test' PRIMARY KEY COLLATE ascii_bin UNIQUE KEY NOT NULL, team varchar(32) CHARACTER SET ascii COMMENT 'test' COLLATE ascii_bin UNIQUE KEY NOT NULL, teamgen varchar(32) CHARACTER SET ascii COLLATE ascii_bin AS (UPPER(team)) UNIQUE NOT NULL); show create table ordering\G
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.02 sec)
*************************** 1. row ***************************
Table: ordering
Create Table: CREATE TABLE `ordering` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'test',
`team` varchar(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL COMMENT 'test',
`teamgen` varchar(32) CHARACTER SET ascii COLLATE ascii_bin GENERATED ALWAYS AS (upper(`team`)) VIRTUAL NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id` (`id`),
UNIQUE KEY `team` (`team`),
UNIQUE KEY `teamgen` (`teamgen`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)
[5 Dec 2018 13:38]
MySQL Verification Team
Hi, I do not think that there are good reasons to reopen this report. As our Paul Dubois wrote: " Per the SQL standard and MySQL, COLLATE is not part of the data type, it is one of the attributes (like NULL and NOT NULL) that follow the data type. This is why NOT NULL cannot preceded CHARACTER SET, but can precede COLLATE. " Hence, as COLLATE is not a part of the data type, so it can be applied for some and rejected for the other ones, like the numerical types. Also, the order of NULL / NOT NULL cause is not strict, which is also mentioned in our Manual.
[5 Dec 2018 18:08]
monty solomon
Paul wrote "NOT NULL cannot precede CHARACTER SET, but can precede COLLATE."
That doesn't work for generated columns. COLLATE, if used, must be used immediately after the data_type when specifying a generated column.
Is that a server error or an omission in the documentation?
| data_type [GENERATED ALWAYS] AS (expression)
[VIRTUAL | STORED] [NOT NULL | NULL]
[UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT 'string']
mysql> CREATE TABLE ordering (team varchar(32), teamgen varchar(32) CHARACTER SET ascii GENERATED ALWAYS AS (UPPER(team)) VIRTUAL NOT NULL);
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE ordering (team varchar(32), teamgen varchar(32) CHARACTER SET ascii COLLATE ascii_bin GENERATED ALWAYS AS (UPPER(team)) VIRTUAL NOT NULL);
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE ordering (team varchar(32), teamgen varchar(32) CHARACTER SET ascii GENERATED ALWAYS AS (UPPER(team)) COLLATE ascii_bin VIRTUAL NOT NULL);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COLLATE ascii_bin VIRTUAL NOT NULL)' at line 1
mysql> CREATE TABLE ordering (team varchar(32), teamgen varchar(32) CHARACTER SET ascii GENERATED ALWAYS AS (UPPER(team)) VIRTUAL COLLATE ascii_bin NOT NULL);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COLLATE ascii_bin NOT NULL)' at line 1
mysql> CREATE TABLE ordering (team varchar(32), teamgen varchar(32) CHARACTER SET ascii GENERATED ALWAYS AS (UPPER(team)) VIRTUAL NOT NULL COLLATE ascii_bin);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COLLATE ascii_bin)' at line 1
[5 Dec 2018 18:13]
monty solomon
Those errors for the placement of the COLLATE attribute when specifying generated columns also contradict "A collation for the character set can be specified with the COLLATE attribute, along with any other attributes."
[6 Dec 2018 13:41]
MySQL Verification Team
Hi, It is quite obvious to me that you have proved your point when it comes to the generated columns. Verified as a documentation bug.
[29 Jan 2019 13:55]
Paul DuBois
Posted by developer: Updated the generated column syntax at: https://dev.mysql.com/doc/refman/5.7/en/create-table.html https://dev.mysql.com/doc/refman/8.0/en/create-table.html to reflect correct permitted location of COLLATE clause.
