select sum(case when 1 then a end) from t group by a causes schema change

[wjhuang2016]

Description

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> create table t4(a decimal(16, 2));
OK, 0 row affected

mysql> select sum(case when 1 then a end) from t4 group by a;
OK, 0 row in set (0.00 sec)

mysql> show create table t4;
+-------+------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                     |
+-------+------------------------------------------------------------------------------------------------------------------+
| t4    | CREATE TABLE `t4` (
  `a` decimal(65,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

create index idx on t4(a);
insert into t4 values (12.34);

... restart TiDB server ...

mysql> select * from t4 use index(idx) where a=12.34;
Empty set (0.00 sec)

mysql> select * from t4 use index() where a=12.34;
+-------+
| a     |
+-------+
| 12.34 |
+-------+
1 row in set (0.00 sec)

This bug can be fixed by #19083.

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

decimal(16,2) in show create table, that is, the schema won't change after executing the SQL.

3. What did you see instead (Required)

decimal(16,2) in show create table, that is, the schema changes after executing the SQL.
Because of that, the index is wrong because it does not match the schema.

4. What is your TiDB version? (Required)

v3.0.16, master

SIG slack channel

#sig-exec

Score

• 300

Mentor

• @SunRunAway

[time-and-fate]

/label sig/ddl

[zz-jason]

@wjhuang2016 why release 4.0 is not affected?

[wjhuang2016]

@wjhuang2016 why release 4.0 is not affected?

Release 4.0 is also affected.
What is your TiDB version? doesn't mean affected version?

[bb7133]

Close this issue since it is fixed by #20095

[lzmhhh123]

can not reproduce.