MySQL Bugs: #93219: Incorrect character set used when CHARACTER SET is missing for ASCII

Bug #93219	Incorrect character set used when CHARACTER SET is missing for ASCII
Submitted:	16 Nov 2018 3:07	Modified:	16 Nov 2018 6:10
Reporter:	monty solomon	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.7.23	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The server doesn't generate an error when CHARACTER SET is missing from the data type in the column_definition.

It adds the column but it doesn't use the specified character set and it doesn't use the table default or the database default.

After executing the following ALTER TABLE statement on a table with a DEFAULT CHARSET=utf8mb4 the added column has the character set latin1.

ALTER TABLE broken ADD COLUMN rate VARCHAR(25) ASCII DEFAULT 'SECONDLY' NOT NULL, LOCK=NONE;

The same problem occurs when using the CREATE TABLE statement.

How to repeat:
Execute these commands

CREATE DATABASE foo DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE foo;
CREATE TABLE broken (id INT) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
SHOW CREATE TABLE broken\G
ALTER TABLE broken ADD COLUMN rate VARCHAR(25) ASCII DEFAULT 'SECONDLY' NOT NULL, LOCK=NONE;
SHOW CREATE TABLE broken\G
DROP TABLE broken;
CREATE TABLE broken (
id INT,
rate VARCHAR(25) ASCII DEFAULT 'SECONDLY' NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
SHOW CREATE TABLE broken\G

Get this output and notice the character set for the column is latin1 after the ALTER or after the CREATE.

mysql> CREATE TABLE broken (id INT) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE broken\G
*************************** 1. row ***************************
       Table: broken
Create Table: CREATE TABLE `broken` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> ALTER TABLE broken ADD COLUMN rate VARCHAR(25) ASCII DEFAULT 'SECONDLY' NOT NULL, LOCK=NONE;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE broken\G
*************************** 1. row ***************************
       Table: broken
Create Table: CREATE TABLE `broken` (
  `id` int(11) DEFAULT NULL,
  `rate` varchar(25) CHARACTER SET latin1 NOT NULL DEFAULT 'SECONDLY'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> DROP TABLE broken;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE broken (
    -> id INT,
    -> rate VARCHAR(25) ASCII DEFAULT 'SECONDLY' NOT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE broken\G
*************************** 1. row ***************************
       Table: broken
Create Table: CREATE TABLE `broken` (
  `id` int(11) DEFAULT NULL,
  `rate` varchar(25) CHARACTER SET latin1 NOT NULL DEFAULT 'SECONDLY'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

Suggested fix:
Generate an error instead of silently using the wrong character set

Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4

The MODIFY clause exhibits the same behavior

DROP DATABASE IF EXISTS foo;
CREATE DATABASE foo DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE foo;
CREATE TABLE broken (id INT) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
SHOW CREATE TABLE broken\G
ALTER TABLE broken ADD COLUMN rate VARCHAR(25) ASCII;
SHOW CREATE TABLE broken\G
ALTER TABLE broken MODIFY COLUMN rate VARCHAR(25) CHARACTER SET utf8;
SHOW CREATE TABLE broken\G
ALTER TABLE broken MODIFY COLUMN rate VARCHAR(25) ASCII;
SHOW CREATE TABLE broken\G

mysql> DROP DATABASE IF EXISTS foo;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DATABASE foo DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 1 row affected (0.00 sec)

mysql> USE foo;
Database changed
mysql> CREATE TABLE broken (id INT) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE broken\G
*************************** 1. row ***************************
       Table: broken
Create Table: CREATE TABLE `broken` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> ALTER TABLE broken ADD COLUMN rate VARCHAR(25) ASCII;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE broken\G
*************************** 1. row ***************************
       Table: broken
Create Table: CREATE TABLE `broken` (
  `id` int(11) DEFAULT NULL,
  `rate` varchar(25) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> ALTER TABLE broken MODIFY COLUMN rate VARCHAR(25) CHARACTER SET utf8;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE broken\G
*************************** 1. row ***************************
       Table: broken
Create Table: CREATE TABLE `broken` (
  `id` int(11) DEFAULT NULL,
  `rate` varchar(25) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> ALTER TABLE broken MODIFY COLUMN rate VARCHAR(25) ASCII;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE broken\G
*************************** 1. row ***************************
       Table: broken
Create Table: CREATE TABLE `broken` (
  `id` int(11) DEFAULT NULL,
  `rate` varchar(25) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

Well the parser does have this for "ASCII" :

ascii:
          ASCII_SYM        { $$= &my_charset_latin1; }
        | BINARY_SYM ASCII_SYM { $$= &my_charset_latin1_bin; }
        | ASCII_SYM BINARY_SYM { $$= &my_charset_latin1_bin; }
        ;

It seems strange that ASCII is a shorthand for 
CHARACTER SET latin1 
instead of 
CHARACTER SET ascii.

I just found this in the manual

https://dev.mysql.com/doc/refman/5.7/en/string-type-overview.html
The ASCII attribute is shorthand for CHARACTER SET latin1.

Changing the status to not a bug.

Thanks.