Should refuse to create expression index with ((null)) #61133
Description
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
CREATE TABLE `t` (
`id` int,
KEY `idx_name` ((null))
);
alter table t set tiflash replica 1;
insert t values(0);
2. What did you expect to see? (Required)
An expression with ((null)) is meaningless for an index. This could created by accident from the SQL input. TiDB should refuse to create expression index with ((null)).
In mysql 9.3.0/ 8.0.x, mysql also refuse to create expression like this.
docker pull mysql:latest
docker run -itd --rm --name mysql-test -p 3306:3306 -e MYSQL_ROOT_PASSWORD='123456' mysql
MySQL [email protected]:(none)> select version();
+-----------+
| version() |
+-----------+
| 9.3.0 |
+-----------+
MySQL [email protected]:test> CREATE TABLE `t` (
-> `id` int,
-> KEY `idx_name` ((null))
-> );
(3761, "The used storage engine cannot index the expression 'NULL'.")
docker pull mysql:8.0
docker run -itd --rm --name mysql-test -p 3306:3306 -e MYSQL_ROOT_PASSWORD='123456' mysql:8.0
MySQL [email protected]:test> select version()
+-----------+
| version() |
+-----------+
| 8.0.42 |
+-----------+
1 row in set
Time: 0.011s
MySQL [email protected]:test> CREATE TABLE `t` (
-> `id` int,
-> KEY `idx_name` ((null))
-> );
(3761, "The used storage engine cannot index the expression 'NULL'.")
MySQL [email protected]:test> set sql_mode = '';
Query OK, 0 rows affected
Time: 0.001s
MySQL [email protected]:test> CREATE TABLE `t` (
-> `id` int,
-> KEY `idx_name` ((null))
-> );
(3761, "The used storage engine cannot index the expression 'NULL'.")
3. What did you see instead (Required)
TiDB accept this kind of invalid expression index. If tiflash replica is created and new rows inserted, it cause TiFlash crashes becuase unexpectedly need to create a "Nothing" data type in the storage layer. pingcap/tiflash#9891
TiDB [email protected]:test> CREATE TABLE `t2` (
-> `id` int,
-> KEY `idx_name` ((null))
-> );
TiDB [email protected]:test> show create table t2 \G
***************************[ 1. row ]***************************
Table | t2
Create Table | CREATE TABLE `t2` (
`id` int DEFAULT NULL,
KEY `idx_name` ((null))
4. What is your TiDB version? (Required)
versions since v6.5.0, where expression index is GA