DDL should report an error when creating a generated column with function "grouping"

[lcwangchao]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

> create table t(a int, b int as ((grouping(a))) stored);

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

In MySQL, it reports an error:

mysql> create table t(a int, b int as ((grouping(a))) stored);
ERROR 1111 (HY000): Invalid use of group function

3. What did you see instead (Required)

mysql> create table t(a int, b int as ((grouping(a))) stored);
Query OK, 0 rows affected (0.09 sec)

4. What is your TiDB version? (Required)

master

[kennedy8312]

Could you please help confirm if the behavior in v6.1.0 is correct or not?

mysql> create table t(a int, b int as ((grouping(a))) stored);
ERROR 3102 (HY000): Expression of generated column 'b' contains a disallowed function.
mysql> select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v6.1.0
Edition: Community
Git Commit Hash: 1a89dec
Git Branch: heads/refs/tags/v6.1.0
UTC Build Time: 2022-06-05 05:09:33
GoVersion: go1.18.2
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |

[kennedy8312]

/type regression

[kennedy8312]

Regression Analysis
There was an error preventing the create table on v7.1.0 but not v7.2.0.

[kennedy8312]

Discussed with D3Hunter. The expected behavior should be a user error instead of a successful ddl. Adding the regression tag.
/type regression

[kennedy8312]

Regression Analysis
PR caused this regression: 44436

[kennedy8312]

Regression Analysis
Commit 8b67cea : Success
Commit aedbcd0 : Failure
-----------------Regression Test Result on 8b67cea-----------------
Server on 8b67cea started.
Validation passed. 8b67cea is a good label
-----------------Test Case-----------------
mysql -h 127.0.0.1 -P 4000 -u root -D test --local-infile=true < issue49909_testcase.sql
issue49909_testcase.sql:
drop table if exists t;
create table t(a int, b int as ((grouping(a))) stored);
-----------------Test Result-----------------
Result :
ERROR 3102 (HY000) at line 2: Expression of generated column 'b' contains a disallowed function.
-----------------TiDB Version-----------------
tidb_version()
Release Version: v7.2.0-alpha-360-g8b67cea5e1-dirty
Edition: Community
Git Commit Hash: 8b67cea
Git Branch: HEAD
UTC Build Time: 2024-03-04 06:43:21
GoVersion: go1.21.4
Race Enabled: false
Check Table Before Drop: false
Store: unistore

-----------------End of regression test on 8b67cea-----------------

[kennedy8312]

-----------------Regression Test Result on aedbcd0-----------------
Server on aedbcd0 started.
Validation failed at run 1. aedbcd0 is a bad label. Expected: ERROR 3102 (HY000) at line 2: Expression of generated column 'b' contains a disallowed function.. Actual:
-----------------Test Case-----------------
mysql -h 127.0.0.1 -P 4000 -u root -D test --local-infile=true < issue49909_testcase.sql
issue49909_testcase.sql:
drop table if exists t;
create table t(a int, b int as ((grouping(a))) stored);
-----------------Test Result-----------------
Result :

-----------------TiDB Version-----------------
tidb_version()
Release Version: v7.2.0-alpha-361-gaedbcd085b-dirty
Edition: Community
Git Commit Hash: aedbcd0
Git Branch: HEAD
UTC Build Time: 2024-03-04 06:44:13
GoVersion: go1.21.4
Race Enabled: false
Check Table Before Drop: false
Store: unistore

-----------------End of regression test on aedbcd0-----------------

[kennedy8312]

Regression Analysis
From v5.4.0 - v7.1.0, the create table ddl would be blocked by "ERROR 3102 (HY000) at line 2: Expression of generated column 'b' contains a disallowed function.". For v7.5, it is not blocked by any errors. The fix should be cherry-pick to v7.5 branch at least?