generated columns could use illegal mix of collations and can not insert data while using new collation

[aytrack]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1. set new_collations_enabled_on_first_bootstrap = true
2. execute the sql.

create table t4(a char, b varchar(10) as (concat('a' collate utf8mb4_general_ci, 'b')) stored) collate utf8_bin;
select concat('aa' collate utf8mb4_general_ci, 'b' collate utf8_bin);
insert into t4(a) values ('a');
show create table t4;

2. What did you expect to see? (Required)

can not create the table.

MySQL > create table t4(a char, b varchar(10) as (concat('a' collate utf8mb4_general_ci, 'b')) stored) collate utf8_bin;
(1253, "COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'utf8'")

3. What did you see instead (Required)

mysql root@127.0.0.1:test> create table t4(a char, b varchar(10) as (concat('a' collate utf8mb4_general_ci, 'b')) stored) collate utf8_bin;
Query OK, 0 rows affected
Time: 0.007s
mysql root@127.0.0.1:test> select concat('aa' collate utf8mb4_general_ci, 'b' collate utf8_bin);
(1253, "COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'utf8'")
mysql root@127.0.0.1:test> insert into t4(a) values ('a');
(1253, "COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'utf8'")
mysql root@127.0.0.1:test> show create table t4;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                  |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t4    | CREATE TABLE `t4` (\n  `a` char(1) DEFAULT NULL,\n  `b` varchar(10) GENERATED ALWAYS AS (concat(_utf8'a' collate utf8mb4_general_ci, _utf8'b')) STORED\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set
Time: 0.005s
mysql root@127.0.0.1:test> select * from mysql.tidb;
+-----------------------+----------------+----------------------------------------------------+
| VARIABLE_NAME         | VARIABLE_VALUE | COMMENT                                            |
+-----------------------+----------------+----------------------------------------------------+
| bootstrapped          | True           | Bootstrap flag. Do not delete.                     |
| tidb_server_version   | 51             | Bootstrap version. Do not delete.                  |
| system_tz             | Asia/Shanghai  | TiDB Global System Timezone.                       |
| new_collation_enabled | True           | If the new collations are enabled. Do not edit it. |
+-----------------------+----------------+----------------------------------------------------+

4. What is your TiDB version? (Required)

mysql root@127.0.0.1:test> select tidb_version();
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v4.0.9-38-gdbade8cda
Edition: Community
Git Commit Hash: dbade8cda4c5a329037746e171449e0a1dfdb8b3
Git Branch: release-4.0\nUTC Build Time: 2021-01-14 02:58:15
GoVersion: go1.15
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

and master (3dd842f)

[tiancaiamao]

2. What did you expect to see? (Required)
   can not create the table.

I test on mysql 8.0 and it can create the table. The behaviour is just the same as tidb.
So I don't think it's a bug.

mysql> create table t4(a char, b varchar(10) as (concat('a' collate utf8mb4_general_ci, 'b')) stored) collate utf8_bin;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                    |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3778 | 'utf8mb3_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead. |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table t4;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                    |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t4    | CREATE TABLE `t4` (
  `a` char(1) COLLATE utf8mb3_bin DEFAULT NULL,
  `b` varchar(10) COLLATE utf8mb3_bin GENERATED ALWAYS AS (concat((_utf8mb4'a' collate utf8mb4_general_ci),_utf8mb4'b')) STORED
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)