Adding stored generated column unsupported via ALTER TABLE.

[amyangfei]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

in MySQL

mysql> create table t(info varchar(100));                                                                                                                                         Query OK, 0 rows affected (0.03 sec)

mysql> insert into t values ('{}');                                                                                                                                               Query OK, 1 row affected (0.00 sec)

mysql> alter table t add column info_json json as (`info`) stored;                                                                                                                Query OK, 1 row affected (0.12 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from t;
+------+-----------+
| info | info_json |
+------+-----------+
| {}   | {}        |
+------+-----------+
1 row in set (0.01 sec)

in TiDB:

mysql> create table t(info varchar(100));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values ('{}');
Query OK, 1 row affected (0.00 sec)

mysql> alter table t add column info_json json as (`info`) stored;
Query OK, 0 rows affected (0.05 sec)

mysql> select * from t;
+------+-----------+
| info | info_json |
+------+-----------+
| {}   | NULL      |
+------+-----------+
1 row in set (0.01 sec)

2. What did you expect to see?

should we get the same result as MySQL?

3. What did you see instead?

different value for generated column with MySQL

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

has bug in both release-2.1 and master bracnh

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.24-log |
+------------+
1 row in set (0.00 sec)

mysql> select tidb_version();
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                                      |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v2.1.0-rc.3-186-gaa979705d
Git Commit Hash: aa979705df0333d98395d8f548114e989eb0dcbd
Git Branch: master
UTC Build Time: 2018-11-20 01:38:21
GoVersion: go version go1.11.1 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> select tidb_version();
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                                     |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v3.0.0-beta-49-g2f9ab6099
Git Commit Hash: 2f9ab60995c972be14c407e204e68ea1621e585d
Git Branch: master
UTC Build Time: 2019-02-12 10:57:22
GoVersion: go version go1.11.1 linux/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[bb7133]

Hi @amyangfei, thanks for reporting!

[ghost]

Confirming that this is still an issue in master, except there is an error now for adding a generated column via ALTER:

drop table if exists t;
create table t(info varchar(100));
insert into t values ('{}');
alter table t add column info_json json as (`info`) stored;
select * from t;

..

mysql> alter table t add column info_json json as (`info`) stored;
ERROR 3106 (HY000): 'Adding generated stored column through ALTER TABLE' is not supported for generated columns.
mysql> select * from t;
+------+
| info |
+------+
| {}   |
+------+
1 row in set (0.00 sec)

If I modify the testcase and change it to a virtual column it produces the expected result:

drop table if exists t;
create table t(info varchar(100));
insert into t values ('{}');
alter table t add column info_json json as (`info`);
select * from t;

..

mysql> alter table t add column info_json json as (`info`);
Query OK, 0 rows affected (0.27 sec)

mysql> select * from t;
+------+-----------+
| info | info_json |
+------+-----------+
| {}   | {}        |
+------+-----------+
1 row in set (0.01 sec)

Thus, I think the issue is about not being able to add stored columns through ALTER TABLE. I will updated the title to say 'unsupported' instead of invalid.

[xiongjiwei]

close due to the duplicate one is closed, see #9372