pingcap / pingcap/tidb

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

Open
#43,455 6 comments 0 reactions 1 assignee Claimed by @xhebox View on GitHub
affects-7.5 affects-8.1 affects-8.5 may-affects-5.1 may-affects-5.2 may-affects-5.3 may-affects-5.4 may-affects-6.1 may-affects-6.5 may-affects-7.1 severity/major sig/sql-infra type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)
```sql
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)
```sql
[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)
```sql
[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)
```sql
[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 |
+-----------------------------------------------------------+
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.