Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

change generated column expression bug when have index on this generated column #10924

Closed
crazycs520 opened this issue Jun 24, 2019 · 0 comments
Assignees
Labels
type/bug The issue is confirmed as a bug.

Comments

@crazycs520
Copy link
Contributor

crazycs520 commented Jun 24, 2019

Bug Report

Please answer these questions before submitting your issue. Thanks!

  1. What did you do?
    planner: allow queries on virtual columns to use index #10802 : This PR make virtual columns can be indexed.
    Next reproduce is base on this PR.
mysql>create table t1 (a int, b int as (a+1), index idx(b));
Query OK, 0 rows affected
Time: 0.071s
mysql>insert into t1 set a=1
Query OK, 1 row affected
Time: 0.008s
mysql>alter table t1 modify column b int as (a+2)
Query OK, 0 rows affected
Time: 0.072s
mysql>select b from t1 use index(idx);
+---+
| b |
+---+
| 2 |
+---+
1 row in set
Time: 0.007s
mysql>select b from t1 ignore index(idx);
+---+
| b |
+---+
| 3 |
+---+
mysql>admin check table t1;
(8003, u't1 err:[admin:1]index:&admin.RecordData{Handle:1, Values:[]types.Datum{types.Datum{k:0x1, collation:0x0, decimal:0x0, length:0x0, i:2, b:[]uint8(nil), x:interface {}(nil)}}} != record:&admin.RecordData{Handle:1, Values:[]types.Datum{types.Datum{k:0x1, collation:0x0, decimal:0x0, length:0x0, i:3, b:[]uint8(nil), x:interface {}(nil)}}}')
  1. How to fix it?
    When modify generated column generated expression and there is a index on the column, TiDB should do at least 1 thing on the below:
  • backfill the index data on the generated column again.
  • prohibit modify virtual generated column generated expression and there is a index on the column

Maybe we can simply prohibit this action first. But this behaviour is not compatible with mysql.

  1. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?
mysql>select tidb_version();
+--------------------------------------------------------------------------+
| tidb_version()                                                           |
+--------------------------------------------------------------------------+
| Release Version: v3.0.0-rc.1-236-g45f437eef                              |
| Git Commit Hash: 45f437eef076ff7bb6591d05589522eb2cf8d495                |
| Git Branch: 10802                                                        |
| UTC Build Time: 2019-06-24 08:44:22                                      |
| GoVersion: go version go1.12 darwin/amd64                                |
| Race Enabled: false                                                      |
| TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e |
| Check Table Before Drop: false                                           |
+--------------------------------------------------------------------------+
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
type/bug The issue is confirmed as a bug.
Projects
None yet
Development

No branches or pull requests

2 participants