Tidb View compatibility issues with mysql.

[ramanich1]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

--Tidb is allowing variables in view select , mysql is not:
create table t1 (a int, b int);
set @v1=10;
create view v1 (c,d) as select a,b+@v1 from t1;

--WITH CHECK OPTION should be  syntactically compatible :
create view v2  as select a from t1 WITH CHECK OPTION;

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

mysql> create view v1 (c,d) as select a,b+@v1 from t1;
ERROR 1351 (HY000): View's SELECT contains a variable or parameter

mysql> create view v2  as select a from t1 WITH CHECK OPTION;
Query OK, 0 rows affected (0.01 sec)

3. What did you see instead (Required)

mysql> create view v1 (c,d) as select a,b+@v1 from t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create view v2  as select a from t1 WITH CHECK OPTION;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 46 near "CHECK OPTION"

4. What is your TiDB version? (Required)

| Release Version: v5.5.0-alpha-182-gad9430039
Edition: Community
Git Commit Hash: ad9430039f54bb9af78d44831c176bc5eafcbba0
Git Branch: master
UTC Build Time: 2022-02-18 17:57:13
GoVersion: go1.17.2
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |

[morgo]

The behavior of MySQL is interesting:

WITH CHECK OPTION applies to updatable views (not supported by TiDB) and validate that any update can only be made to rows that will still meet the original view definition.

It allows user variables in the WITH CHECK OPTION case, but not in the regular case. There must be a security implication that is not clear.

[mjonss]

I created a separate issue for "Tidb is allowing variables in view select , mysql is not", #53176