Description:
Functional Index is not correclty working.
It should be work as generated column.
Functional Index
---------------
alter table t_funcindx add index idx_truncate((truncate(col1/10,0)));
Generated Column
---------------
alter table t_funcindx add COLUMN generated0 int GENERATED ALWAYS AS (truncate((col1/10),0)) VIRTUAL;
alter table t_funcindx add index idx_generated0(generated0);
Both of them should be work with "where truncate((col1/10),0) = 200" but functional index work with "where truncate((col1/10),0) = 200.000;"
How to repeat:
## Create table
root@localhost [poc]> CREATE TABLE `t_funcindx` (
`id` int NOT NULL AUTO_INCREMENT,
-> `id` int NOT NULL AUTO_INCREMENT,
-> `col1` int DEFAULT NULL,
-> `col2` int DEFAULT NULL,
-> `generated0` int GENERATED ALWAYS AS (truncate((`col1` / 10),0)) VIRTUAL,
-> PRIMARY KEY (`id`),
-> KEY `indx_sum` (((`col1` + `col2`))),
-> KEY `idx_trunc` (((`col1` / 10))),
-> KEY `idx_trunc_0` ((truncate((`col1` / 10),0))),
-> KEY `idx_generated0` (`generated0`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=2001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.05 sec)
## Insert Data
insert into t_funcindx(col1,col2) values(2000,900);
insert into t_funcindx(col1,col2) values(9000,800);
insert into t_funcindx(col1,col2) values(3000,100);
insert into t_funcindx(col1,col2) values(1000,1000);
insert into t_funcindx(col1,col2) values(4000,700);
insert into t_funcindx(col1,col2) values(7000,400);
insert into t_funcindx(col1,col2) values(6000,200);
insert into t_funcindx(col1,col2) values(3000,500);
insert into t_funcindx(col1,col2) values(7000,200);
insert into t_funcindx(col1,col2) values(4000,400);
## Confirmation
root@localhost [poc]> explain select count(*) from t_funcindx where generated0 = 200;
+----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | t_funcindx | NULL | ref | idx_generated0 | idx_generated0 | 5 | const | 1 | 100.00 | Using index |
+----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
root@localhost [poc]> explain select * from t_funcindx where truncate((col1/10),0) = 200;
+----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t_funcindx | NULL | ref | idx_generated0 | idx_generated0 | 5 | const | 1 | 100.00 | NULL |
+----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
root@localhost [poc]> explain select * from t_funcindx where truncate((col1/10),0) = 200.0000;
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t_funcindx | NULL | ref | idx_trunc_0 | idx_trunc_0 | 6 | const | 1 | 100.00 | NULL |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
## Drop Generated Column and Index on it.
root@localhost [poc]> alter table t_funcindx drop index idx_generated0;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost [poc]> alter table t_funcindx drop column generated0;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost [poc]> show create table t_funcindx\G
*************************** 1. row ***************************
Table: t_funcindx
Create Table: CREATE TABLE `t_funcindx` (
`id` int NOT NULL AUTO_INCREMENT,
`col1` int DEFAULT NULL,
`col2` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `indx_sum` (((`col1` + `col2`))),
KEY `idx_trunc` (((`col1` / 10))),
KEY `idx_trunc_0` ((truncate((`col1` / 10),0)))
) ENGINE=InnoDB AUTO_INCREMENT=2011 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
## Explain
root@localhost [poc]> explain select * from t_funcindx where truncate((col1/10),0) = 200;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t_funcindx | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
root@localhost [poc]> explain select * from t_funcindx where truncate((col1/10),0) = 200.000;
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t_funcindx | NULL | ref | idx_trunc_0 | idx_trunc_0 | 6 | const | 1 | 100.00 | NULL |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
root@localhost [poc]>
## Expectation
"KEY `idx_trunc_0` ((truncate((`col1` / 10),0)))" is index that generated with truncate function, so truncate((col1/10),0) = 200 should be work like generated column.
Suggested fix:
It supporse to be work like generated column.
Functional Key Parts
https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-functional-key-part...
Description: Functional Index is not correclty working. It should be work as generated column. Functional Index --------------- alter table t_funcindx add index idx_truncate((truncate(col1/10,0))); Generated Column --------------- alter table t_funcindx add COLUMN generated0 int GENERATED ALWAYS AS (truncate((col1/10),0)) VIRTUAL; alter table t_funcindx add index idx_generated0(generated0); Both of them should be work with "where truncate((col1/10),0) = 200" but functional index work with "where truncate((col1/10),0) = 200.000;" How to repeat: ## Create table root@localhost [poc]> CREATE TABLE `t_funcindx` ( `id` int NOT NULL AUTO_INCREMENT, -> `id` int NOT NULL AUTO_INCREMENT, -> `col1` int DEFAULT NULL, -> `col2` int DEFAULT NULL, -> `generated0` int GENERATED ALWAYS AS (truncate((`col1` / 10),0)) VIRTUAL, -> PRIMARY KEY (`id`), -> KEY `indx_sum` (((`col1` + `col2`))), -> KEY `idx_trunc` (((`col1` / 10))), -> KEY `idx_trunc_0` ((truncate((`col1` / 10),0))), -> KEY `idx_generated0` (`generated0`) -> ) ENGINE=InnoDB AUTO_INCREMENT=2001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; Query OK, 0 rows affected (0.05 sec) ## Insert Data insert into t_funcindx(col1,col2) values(2000,900); insert into t_funcindx(col1,col2) values(9000,800); insert into t_funcindx(col1,col2) values(3000,100); insert into t_funcindx(col1,col2) values(1000,1000); insert into t_funcindx(col1,col2) values(4000,700); insert into t_funcindx(col1,col2) values(7000,400); insert into t_funcindx(col1,col2) values(6000,200); insert into t_funcindx(col1,col2) values(3000,500); insert into t_funcindx(col1,col2) values(7000,200); insert into t_funcindx(col1,col2) values(4000,400); ## Confirmation root@localhost [poc]> explain select count(*) from t_funcindx where generated0 = 200; +----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | t_funcindx | NULL | ref | idx_generated0 | idx_generated0 | 5 | const | 1 | 100.00 | Using index | +----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) root@localhost [poc]> explain select * from t_funcindx where truncate((col1/10),0) = 200; +----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | t_funcindx | NULL | ref | idx_generated0 | idx_generated0 | 5 | const | 1 | 100.00 | NULL | +----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) root@localhost [poc]> explain select * from t_funcindx where truncate((col1/10),0) = 200.0000; +----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | t_funcindx | NULL | ref | idx_trunc_0 | idx_trunc_0 | 6 | const | 1 | 100.00 | NULL | +----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) ## Drop Generated Column and Index on it. root@localhost [poc]> alter table t_funcindx drop index idx_generated0; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 root@localhost [poc]> alter table t_funcindx drop column generated0; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 root@localhost [poc]> show create table t_funcindx\G *************************** 1. row *************************** Table: t_funcindx Create Table: CREATE TABLE `t_funcindx` ( `id` int NOT NULL AUTO_INCREMENT, `col1` int DEFAULT NULL, `col2` int DEFAULT NULL, PRIMARY KEY (`id`), KEY `indx_sum` (((`col1` + `col2`))), KEY `idx_trunc` (((`col1` / 10))), KEY `idx_trunc_0` ((truncate((`col1` / 10),0))) ) ENGINE=InnoDB AUTO_INCREMENT=2011 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) ## Explain root@localhost [poc]> explain select * from t_funcindx where truncate((col1/10),0) = 200; +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t_funcindx | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using where | +----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) root@localhost [poc]> explain select * from t_funcindx where truncate((col1/10),0) = 200.000; +----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | t_funcindx | NULL | ref | idx_trunc_0 | idx_trunc_0 | 6 | const | 1 | 100.00 | NULL | +----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) root@localhost [poc]> ## Expectation "KEY `idx_trunc_0` ((truncate((`col1` / 10),0)))" is index that generated with truncate function, so truncate((col1/10),0) = 200 should be work like generated column. Suggested fix: It supporse to be work like generated column. Functional Key Parts https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-functional-key-part...