Bug #83859 unreasonable behavior for default value of set when meets with string

• Submitted: 17 Nov 2016 6:46
• Modified: 18 Nov 2016 7:46
• Reporter: 帅 Bang
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.6
• OS: Linux
• CPU Architecture: Any

[17 Nov 2016 6:46] 帅 Bang

Description:
mysql> drop table t1,t2;
mysql> create table t1(c1 set('a','b','c') collate utf8mb4_bin default 'A' collate utf8mb4_general_ci);
Query OK, 0 rows affected (0.01 sec)

mysql> create table t2(c1 set('a','b','c') collate utf8mb4_general_ci default 'A' collate utf8mb4_bin);
ERROR 1067 (42000): Invalid default value for 'c1'

when the collation of column is different from the default values's, mysql seems to use the default value's collation to tell whether the default value is valid or not.

IMHO, this is unreasonable and collation of column should have been used.

How to repeat:
drop table t1,t2;
create table t1(c1 set('a','b','c') collate utf8mb4_bin default 'A' collate utf8mb4_general_ci);
create table t2(c1 set('a','b','c') collate utf8mb4_general_ci default 'A' collate utf8mb4_bin);

Suggested fix:
create table t1(c1 set('a','b','c') collate utf8mb4_bin default 'A' collate utf8mb4_general_ci); failed 

and

create table t2(c1 set('a','b','c') collate utf8mb4_general_ci default 'A' collate utf8mb4_bin); succeed

[18 Nov 2016 7:46] MySQL Verification Team

Hello Bang,

Thank you for the report.
Observed with 5.7.16.

Thanks,
Umesh

[9 Feb 2017 14:26] Bernt Marius Johnsen

mysql> create table t1(c1 set('a','b','c') collate utf8mb4_general_ci default
'A' collate utf8mb4_bin);
ERROR 1067 (42000): Invalid default value for 'c1'

The first collate relates to the column while the second collate sets
the collation of the literal 'A'.

A SET column in MySQL does not have a collation, but the column will
have a character set which in this case is derived from the
collation. Thus the statement above is equivalent to

create table t1(c1 set('a','b','c') charset utf8mb4 default 'A' collate
utf8mb4_bin);

While the succeeding statement

create table t1(c1 set('a','b','c') collate utf8mb4_bin default 'A' collate
utf8mb4_general_ci);

is equivalent to

create table t1(c1 set('a','b','c') charset utf8mb4 default 'A' collate
utf8mb4_general_ci);

Since
    'A' collate utf8mb4_bin = 'a'

is false, this is not a legal default value for c1.

On the other hand
   'A' collate utf8mb4_general_ci = 'a'
is true, so that will be a legal default value.