can not change collation for a column which used with generated column

aytrack commented 1 year ago

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists t;
create table t (a char, b varchar(10), c varchar(20) as (concat(a, b)), d varchar(20) as (concat(b, 'À')) stored, index idx(c), unique key (d) ) character set utf8mb4 collate utf8mb4_bin;
alter table t modify column a char character set utf8mb4 collate utf8mb4_unicode_ci;
insert into t(a, b) values ('a', 'abc'), ('A', 'ABC');
select * from t where a = 'a';

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

[11:29:00]TiDB root:test> drop table if exists t;
                       -> create table t (a char, b varchar(10), c varchar(20) as (concat(a, b)), d varchar(20) as (concat(b, 'À')) stored, index idx(c), unique key (d) ) character set utf8mb4 collate utf8mb4_bin;
                       -> alter table t modify column a char character set utf8mb4 collate utf8mb4_unicode_ci;
                       -> insert into t(a, b) values ('a', 'abc'), ('A', 'ABC');
                       -> select * from t where a = 'a';
                       ->
Reconnecting...
Query OK, 0 rows affected
Time: 0.003s

Query OK, 0 rows affected
Time: 0.012s

Query OK, 0 rows affected
Time: 0.009s

Query OK, 2 rows affected
Time: 0.001s

+---+-----+------+------+
| a | b   | c    | d    |
+---+-----+------+------+
| a | abc | aabc | abcÀ |
| A | ABC | AABC | ABCÀ |
+---+-----+------+------+
2 rows in set
Time: 0.004s
[11:29:09]TiDB root:test> show create table t;
+-------+-----------------------------------------------------------------------+
| Table | Create Table                                                          |
+-------+-----------------------------------------------------------------------+
| t     | CREATE TABLE `t` (                                                    |
|       |   `a` char(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL,                |
|       |   `b` varchar(10) DEFAULT NULL,                                       |
|       |   `c` varchar(20) GENERATED ALWAYS AS (concat(`a`, `b`)) VIRTUAL,     |
|       |   `d` varchar(20) GENERATED ALWAYS AS (concat(`b`, _utf8'À')) STORED, |
|       |   KEY `idx` (`c`),                                                    |
|       |   UNIQUE KEY `d` (`d`)                                                |
|       | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin           |
+-------+-----------------------------------------------------------------------+

3. What did you see instead (Required)

[11:40:05]TiDB root:test> drop table if exists t;
                       -> create table t (a char, b varchar(10), c varchar(20) as (concat(a, b)), d varchar(20) as (concat(b, 'À')) stored, index idx(c), unique key (d) ) character set utf8mb4 collate utf8mb4_bin;
                       -> alter table t modify column a char character set utf8mb4 collate utf8mb4_unicode_ci;
Query OK, 0 rows affected
Time: 0.004s

Query OK, 0 rows affected
Time: 0.014s
(3106, "'[ddl:3108]Column 'a' has a generated column dependency.' is not supported for generated columns.")
[11:40:10]TiDB root:test> insert into t(a, b) values ('a', 'abc'), ('A', 'ABC');
                       -> select * from t where a = 'a';
Query OK, 2 rows affected
Time: 0.003s

+---+-----+------+------+
| a | b   | c    | d    |
+---+-----+------+------+
| a | abc | aabc | abcÀ |
+---+-----+------+------+
1 row in set
Time: 0.008s

4. What is your TiDB version? (Required)

[11:40:34]TiDB root:test> select tidb_version();
+-----------------------------------------------------------+
| tidb_version()                                            |
+-----------------------------------------------------------+
| Release Version: v7.2.0-alpha                             |
| Edition: Community                                        |
| Git Commit Hash: df0b41364bbbcab3a387dcc4dee76954456f5f9d |
| Git Branch: heads/refs/tags/v7.2.0-alpha                  |
| UTC Build Time: 2023-04-26 14:25:23                       |
| GoVersion: go1.20.3                                       |
| Race Enabled: false                                       |
| TiKV Min Version: 6.2.0-alpha                             |
| Check Table Before Drop: false                            |
| Store: unistore                                           |
+-----------------------------------------------------------+

aytrack commented 1 year ago

Introduced by #43350

tiancaiamao commented 1 year ago

So this is the side effect after fixing https://github.com/pingcap/tidb/issues/24321 In other words, the expected behaviour here conflict with the expected behaviour in #24321

Depending on the correctness definition, we have 3 options:

Prohibite modifying the column type(needn't to change the data) involved in the generated column (close #24321, leave this one open)
Allow modifying the column type(needn't to change the data) involved in the generated column (reopen #24321, close this one) ... and update document for the limitations.
Support modify column type involving data change on generated column, fully mysql compatible but takes much more work.

bb7133 commented 1 year ago

So this is the side effect after fixing #24321 In other words, the expected behaviour here conflict with the expected behaviour in #24321

Depending on the correctness definition, we have 3 options:

Prohibite modifying the column type(needn't to change the data) involved in the generated column (close #24321, leave this one open)

Allow modifying the column type(needn't to change the data) involved in the generated column (reopen #24321, close this one) ... and update document for the limitations.

Support modify column type involving data change on generated column, fully mysql compatible but takes much more work.

Let's choose 1 because the data reorg for generated columns is not implemented yet(a missing piece of data reorg).

bb7133 commented 1 year ago

We don't even support changing the collation for a column with index covered:

tidb> create table t(a varchar(20) collate utf8mb4_bin, key idx(a));
Query OK, 0 rows affected (0.01 sec)

tidb> alter table t modify column a char character set utf8mb4 collate utf8mb4_unicode_ci;
ERROR 8200 (HY000): Unsupported modifying collation of column 'a' from 'utf8mb4_bin' to 'utf8mb4_general_ci' when index is defined on it.

aytrack commented 1 year ago

When will this issue be fixed?

634750802 commented 11 months ago

Same issue in hive metastore:

ALTER TABLE `NOTIFICATION_SEQUENCE` MODIFY COLUMN `NNI_ID` BIGINT(20) GENERATED ALWAYS AS (1) STORED NOT NULL;

TiDB Version: v7.1.2

Source code:

pingcap / tidb