CREATE TABLE t1 (int_col INTEGER, string_col VARCHAR(255));
INSERT INTO t1 (int_col, string_col) VALUES (-1, "foo"), (1, "bar");
CREATE INDEX int_func_index ON t1 ((ABS(int_col)));
CREATE INDEX string_func_index ON t1 ((SUBSTRING(string_col, 1, 2)));
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
# We should see the functional index syntax in SHOW CREATE TABLE
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `int_col` int DEFAULT NULL,
  `string_col` varchar(255) DEFAULT NULL,
  KEY `int_func_index` ((abs(`int_col`))),
  KEY `string_func_index` ((substr(`string_col`,1,2)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
# INFORMATION_SCHEMA.STATISTICS should show the expression and no column
# name
SELECT * FROM INFORMATION_SCHEMA.STATISTICS
WHERE INDEX_NAME IN ('int_func_index', 'string_func_index');
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	NON_UNIQUE	INDEX_SCHEMA	INDEX_NAME	SEQ_IN_INDEX	COLUMN_NAME	COLLATION	CARDINALITY	SUB_PART	PACKED	NULLABLE	INDEX_TYPE	COMMENT	INDEX_COMMENT	IS_VISIBLE	EXPRESSION
def	test	t1	1	test	int_func_index	1	NULL	A	1	NULL	NULL	YES	BTREE			YES	abs(`int_col`)
def	test	t1	1	test	string_func_index	1	NULL	A	2	NULL	NULL	YES	BTREE			YES	substr(`string_col`,1,2)
# INFORMATION_SCHEMA.COLUMNS should not show the hidden generated
# columns.
SELECT COUNT(*) AS should_be_2 FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = "t1";
should_be_2
2
# INFORMATION_SCHEMA.INNODB_COLUMNS should not show the hidden generated
# columns.
SELECT COUNT(*) AS should_be_2 FROM INFORMATION_SCHEMA.INNODB_COLUMNS c
JOIN INFORMATION_SCHEMA.INNODB_TABLES t ON (c.TABLE_ID = t.TABLE_ID)
WHERE t.NAME = "test/t1";
should_be_2
2
# The optimizer should be able to make use of the functional index.
# Also, the query printed as a warning from EXPLAIN should display the
# expression and not the name of the hidden generated column.
EXPLAIN SELECT * FROM t1 WHERE SUBSTRING(string_col, 1, 2) = "fo";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	string_func_index	string_func_index	11	const	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`int_col` AS `int_col`,`test`.`t1`.`string_col` AS `string_col` from `test`.`t1` where (substr(`string_col`,1,2) = 'fo')
EXPLAIN SELECT * FROM t1 WHERE ABS(int_col) = 1;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	int_func_index	int_func_index	5	const	2	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`int_col` AS `int_col`,`test`.`t1`.`string_col` AS `string_col` from `test`.`t1` where (abs(`int_col`) = 1)
# Creating multiple nameless functional indexes should automatically
# generate new names that doesn't collide.
CREATE TABLE t2 (
col1 INT,
INDEX ((col1 * 2)),
INDEX ((col1 * 4)),
INDEX ((col1 * 6)));
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `col1` int DEFAULT NULL,
  KEY `functional_index` (((`col1` * 2))),
  KEY `functional_index_2` (((`col1` * 4))),
  KEY `functional_index_3` (((`col1` * 6)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
DROP TABLE t2;
# Adding a multi-column index with both a functional part and a
# non-functional part should work just fine.
CREATE INDEX combined_index ON t1 ((int_col + int_col), string_col);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `int_col` int DEFAULT NULL,
  `string_col` varchar(255) DEFAULT NULL,
  KEY `int_func_index` ((abs(`int_col`))),
  KEY `string_func_index` ((substr(`string_col`,1,2))),
  KEY `combined_index` (((`int_col` + `int_col`)),`string_col`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
# ...and, the optimizer should be able to use this multi-column index
EXPLAIN SELECT * FROM t1 WHERE int_col + int_col = 2 AND string_col = "bar";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	combined_index	combined_index	1032	const,const	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`int_col` AS `int_col`,`test`.`t1`.`string_col` AS `string_col` from `test`.`t1` where ((`test`.`t1`.`string_col` = 'bar') and ((`int_col` + `int_col`) = 2))
# Test that if we have a string column with the same name as a function,
# we will pick the function. To pick the column, skip the enclosing
# parentheses around the expression.
ALTER TABLE t1 ADD COLUMN rand VARCHAR(255);
CREATE INDEX name_collision ON t1 ((rand(2)));
ERROR HY000: Expression of functional index 'name_collision' contains a disallowed function.
CREATE INDEX name_collision ON t1 ((`rand`(2)));
ERROR HY000: Expression of functional index 'name_collision' contains a disallowed function.
DROP TABLE t1;
# Check that in case of errors when saving data to the generated column,
# the functional index name is printed and not the auto generated name
# of the generated column.
CREATE TABLE t1 (f1 JSON, f2 VARCHAR(255));
CREATE INDEX my_functional_index ON t1 ((CAST(f1 AS DECIMAL(2, 1))));
CREATE INDEX my_functional_index_2 ON t1 ((CAST(f2 AS CHAR(1))));
CREATE INDEX idx1 ON t1 ((CAST(f2 AS JSON)));
ERROR 42000: Cannot create a functional index on a function that returns a JSON or GEOMETRY value.
INSERT INTO t1 (f1) VALUES (CAST(1000 AS JSON));
ERROR 22003: Value is out of range for functional index 'my_functional_index' at row 1
INSERT INTO t1 (f2) VALUES ("lorem ipsum");
ERROR 01000: Data truncated for functional index 'my_functional_index_2' at row 1
# In non-strict mode, a warning should be printed.
SET @@sql_mode='';
INSERT INTO t1 (f1) VALUES (CAST(1000 AS JSON));
Warnings:
Warning	3752	Value is out of range for functional index 'my_functional_index' at row 1
INSERT INTO t1 (f2) VALUES ("lorem ipsum");
Warnings:
Warning	3751	Data truncated for functional index 'my_functional_index_2' at row 1
DROP TABLE t1;
SET @@sql_mode=DEFAULT;
CREATE TABLE t1 (t1_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE INDEX idx1 ON t1 ((t1_id + t1_id));
ERROR HY000: Functional index 'idx1' cannot refer to an auto-increment column.
DROP TABLE t1;
# Check that "SHOW KEYS" prints out the expression for generated columns
# (Column_name should be NULL, and Expression should contain the
# expression).
CREATE TABLE t1 (col1 INT, col2 INT);
CREATE INDEX idx1 ON t1 (col1, (col1 + col2));
SHOW KEYS FROM t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Visible	Expression
t1	1	idx1	1	col1	A	0	NULL	NULL	YES	BTREE			YES	NULL
t1	1	idx1	2	NULL	A	0	NULL	NULL	YES	BTREE			YES	(`col1` + `col2`)
# Create a functional index with many functional key parts. Bug#27168185.
CREATE INDEX idx2
ON t1 ((col1 + 1), (col1 + 2), (col1 + 3), (col1 + 4), (col1 + 5));
# Check that if we try to add a column with the same name as a hidden
# column, we get an internal error. The column name of the generated
# column is `!hidden!index_name!key_part!counter`.
ALTER TABLE t1 ADD COLUMN `!hidden!idx1!1!0` INT NOT NULL;
ERROR HY000: Internal error: The column name '!hidden!idx1!1!0' is already in use by a hidden column
ALTER TABLE t1 ADD COLUMN `!hidden!idx3!0!0` INT NOT NULL;
CREATE INDEX idx3 ON t1 ((col1-col2));
ALTER TABLE t1 ADD COLUMN `!hidden!idx3!0!1` INT NOT NULL;
ERROR HY000: Internal error: The column name '!hidden!idx3!0!1' is already in use by a hidden column
DROP TABLE t1;
# Creating a functional index on already existing columns using
# ALTER TABLE should work just fine.
CREATE TABLE t1 (col1 INT, col2 INT);
ALTER TABLE t1 ADD INDEX ((ABS(col1))), ADD INDEX ((ABS(col2)));
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `col1` int DEFAULT NULL,
  `col2` int DEFAULT NULL,
  KEY `functional_index` ((abs(`col1`))),
  KEY `functional_index_2` ((abs(`col2`)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
# Adding a functional index to a column that is created in the same
# ALTER TABLE statement should work.
ALTER TABLE t1 ADD COLUMN col3 INT, ADD INDEX ((col1 - col3));
DROP TABLE t1;
# Adding a functional index using CREATE TABLE should work
CREATE TABLE t1 (col1 INT, INDEX ((ABS(col1))));
DROP TABLE t1;
# Print out functional indexes on a temporary table. Note that
# "Column_name" should be NULL, and Expression should contain the
# expression for the functional index.
CREATE TEMPORARY TABLE t1(a INT);
CREATE INDEX idx ON t1 ((ABS(a)));
SHOW KEYS FROM t1;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Visible	Expression
t1	1	idx	1	NULL	A	0	NULL	NULL	YES	BTREE			YES	abs(`a`)
DROP TABLE t1;
CREATE TABLE t1 (col1 INT, INDEX ((CONCAT(''))));
ERROR HY000: The used storage engine cannot index the expression 'concat(_utf8mb4'')'.
# Check that a functional index cannot be a part of the primary key
CREATE TABLE t1 (col1 INT, PRIMARY KEY ((ABS(col1))));
ERROR HY000: The primary key cannot be a functional index
CREATE TABLE t1 (col1 INT, PRIMARY KEY (col1, (ABS(col1))));
ERROR HY000: The primary key cannot be a functional index
CREATE TABLE t1 (col1 INT, col2 INT, PRIMARY KEY (col1, (ABS(col1)), col2));
ERROR HY000: The primary key cannot be a functional index
CREATE TABLE t1 (col1 INT, col2 INT);
ALTER TABLE t1 ADD PRIMARY KEY ((ABS(col1)));
ERROR HY000: The primary key cannot be a functional index
ALTER TABLE t1 ADD PRIMARY KEY (col2, (ABS(col1)));
ERROR HY000: The primary key cannot be a functional index
ALTER TABLE t1 ADD PRIMARY KEY (col1, col2, (ABS(col1)));
ERROR HY000: The primary key cannot be a functional index
DROP TABLE t1;
# Check that descending functional index works
CREATE TABLE t1 (col1 INT, INDEX ((ABS(col1)) DESC));
EXPLAIN SELECT col1 FROM t1 WHERE ABS(col1) < 1 ORDER BY ABS(col1) DESC;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	functional_index	functional_index	5	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`col1` AS `col1` from `test`.`t1` where (abs(`col1`) < 1) order by abs(`test`.`t1`.`col1`) desc
DROP TABLE t1;
# Test integer index over a JSON key
CREATE TABLE t1(f1 JSON, INDEX idx1 ((CAST(f1->"$.id" AS UNSIGNED))));
INSERT INTO t1 VALUES(CAST('{"id":1}' AS JSON)), (CAST('{"id":2}' AS JSON)),
(CAST('{"id":3}' AS JSON)), (CAST('{"id":4}' AS JSON)),
(CAST('{"id":5}' AS JSON)), (CAST('{"id":6}' AS JSON)),
(CAST('{"id":7}' AS JSON)), (CAST('{"id":8}' AS JSON)),
(CAST('{"id":9}' AS JSON)), (CAST('{"id":10}' AS JSON));
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
SELECT * FROM t1 WHERE f1->"$.id"= 5;
f1
{"id": 5}
EXPLAIN SELECT * FROM t1 WHERE f1->"$.id"= 5;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	idx1	idx1	9	const	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where (cast(json_extract(`f1`,_utf8mb4'$.id') as unsigned) = 5)
SELECT * FROM t1 WHERE f1->"$.id" IN (1,2,3);
f1
{"id": 1}
{"id": 2}
{"id": 3}
EXPLAIN SELECT * FROM t1 WHERE f1->"$.id" IN (1,2,3);
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	idx1	idx1	9	NULL	3	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where (cast(json_extract(`f1`,_utf8mb4'$.id') as unsigned) in (1,2,3))
DROP TABLE t1;
Test string index over a JSON key
CREATE TABLE t1(f1 JSON, INDEX idx1 ((CAST(f1->>"$.id" AS CHAR(10)))));
INSERT INTO t1 VALUES
(CAST('{"id":"a"}' AS JSON)), (CAST('{"id":"b"}' AS JSON)),
(CAST('{"id":"v"}' AS JSON)), (CAST('{"id":"c"}' AS JSON)),
(CAST('{"id":"x"}' AS JSON)), (CAST('{"id":"\'z"}' AS JSON)),
  (JSON_OBJECT("id",JSON_QUOTE("n"))), (CAST('{"id":"w"}' AS JSON)),
  (CAST('{"id":"m"}' AS JSON)), (CAST('{"id":"q"}' AS JSON));
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
SELECT * FROM t1 WHERE CAST(f1->>"$.id" AS CHAR(10)) = "\"n\"";
f1
{"id": "\"n\""}
EXPLAIN SELECT * FROM t1 WHERE CAST(f1->>"$.id" AS CHAR(10)) = "\"n\"";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	idx1	idx1	43	const	1	100.00	NULL
Note	1003	/* select#1 */ select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where (cast(json_unquote(json_extract(`f1`,_utf8mb4'$.id')) as char(10) charset utf8mb4) = '"n"')
Warnings:
SELECT * FROM t1 WHERE CAST(f1->>"$.id" AS CHAR(10)) IN ("'z", "\"n\"","a");
f1
{"id": "'z"}
{"id": "\"n\""}
{"id": "a"}
EXPLAIN SELECT * FROM t1 WHERE CAST(f1->>"$.id" AS CHAR(10)) IN ("'z", "\"n\"","a");
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	range	idx1	idx1	43	NULL	3	100.00	Using where
Note	1003	/* select#1 */ select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where (cast(json_unquote(json_extract(`f1`,_utf8mb4'$.id')) as char(10) charset utf8mb4) in ('\'z','"n"','a'))
Warnings:
DROP TABLE t1;
CREATE TABLE t1(f1 JSON, INDEX idx1 ((CAST(f1->>"$.name" AS CHAR(30)) COLLATE utf8mb4_bin)));
INSERT INTO t1 VALUES
('{"name": "james"}'),
('{"name": "JAMES"}'),
('{"name": "Peter"}'),
('{"name": "parker"}');
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
# Should not return any rows, and should use the index
SELECT * FROM t1 WHERE f1->>"$.name" = "James";
f1
EXPLAIN SELECT * FROM t1 WHERE f1->>"$.name" = "James";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	idx1	idx1	123	const	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where ((cast(json_unquote(json_extract(`f1`,_utf8mb4'$.name')) as char(30) charset utf8mb4) collate utf8mb4_bin) = 'James')
# Should return one row, and should use the index
SELECT * FROM t1 WHERE f1->>"$.name" = "james";
f1
{"name": "james"}
EXPLAIN SELECT * FROM t1 WHERE f1->>"$.name" = "james";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	idx1	idx1	123	const	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where ((cast(json_unquote(json_extract(`f1`,_utf8mb4'$.name')) as char(30) charset utf8mb4) collate utf8mb4_bin) = 'james')
# Should return one row, and should use the index
SELECT * FROM t1 WHERE CAST(f1->>"$.name" AS CHAR(30)) COLLATE utf8mb4_bin = "james";
f1
{"name": "james"}
EXPLAIN SELECT * FROM t1 WHERE CAST(f1->>"$.name" AS CHAR(30)) COLLATE utf8mb4_bin = "james";
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	NULL	ref	idx1	idx1	123	const	1	100.00	NULL
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`f1` AS `f1` from `test`.`t1` where ((cast(json_unquote(json_extract(`f1`,_utf8mb4'$.name')) as char(30) charset utf8mb4) collate utf8mb4_bin) = 'james')
DROP TABLE t1;
# Test that doing an UPDATE on a hidden generated column returns "column
# not found".
CREATE TABLE t1 (col1 INT, INDEX idx1 ((col1 + col1)));
# Update the hidden generated column.
UPDATE t1 SET `!hidden!idx1!0!0` = 123;
ERROR 42S22: Unknown column '!hidden!idx1!0!0' in 'field list'
DROP TABLE t1;
#
# Bug#28037245 VIRTUAL COLUMN IS ALLOWED IN THE WHERE CLAUSE FOR SQL
#              QUERY
#
CREATE TABLE t3 (c1 INT);
CREATE INDEX int_func_index ON t3 ((ABS(c1)));
SELECT * FROM t3 WHERE `!hidden!int_func_index!0!0`=1;
ERROR 42S22: Unknown column '!hidden!int_func_index!0!0' in 'where clause'
DROP TABLE t3;
#
# Bug#28037375 ALTER TABLE DROP OF A NORMAL COLUMN RAISES ERROR
#
CREATE TABLE t6 (c1 INT, c2 INT);
CREATE INDEX int_func_index ON t6 ((ABS(c1)));
ALTER TABLE t6 DROP COLUMN c2;
DROP TABLE t6;
# Check that a functional index on a function that returns geometry data
# isn't allowed.
CREATE TABLE t1(x VARCHAR(100), KEY ((ST_GeomFromText(x))));
ERROR HY000: Spatial functional index is not supported.
# See that we don't print out the names of the hidden generated columns,
# but rather the expression that they represent.
CREATE TABLE t1(x VARCHAR(30), INDEX idx ((CAST(x->>'$.name' AS CHAR(30)))));
INSERT INTO t1 VALUES ('{"name":"knut"}');
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
EXPLAIN FORMAT=JSON SELECT * FROM t1 WHERE CAST(x->>'$.name' AS CHAR(30)) = 'knut';
EXPLAIN
{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.35"
    },
    "table": {
      "table_name": "t1",
      "access_type": "ref",
      "possible_keys": [
        "idx"
      ],
      "key": "idx",
      "used_key_parts": [
        "cast(json_unquote(json_extract(`x`,_utf8mb4'$.name')) as char(30) charset utf8mb4)"
      ],
      "key_length": "123",
      "ref": [
        "const"
      ],
      "rows_examined_per_scan": 1,
      "rows_produced_per_join": 1,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.25",
        "eval_cost": "0.10",
        "prefix_cost": "0.35",
        "data_read_per_join": "248"
      },
      "used_columns": [
        "x",
        "cast(json_unquote(json_extract(`x`,_utf8mb4'$.name')) as char(30) charset utf8mb4)"
      ]
    }
  }
}
Warnings:
Note	1003	/* select#1 */ select `test`.`t1`.`x` AS `x` from `test`.`t1` where (cast(json_unquote(json_extract(`x`,_utf8mb4'$.name')) as char(30) charset utf8mb4) = 'knut')
DROP TABLE t1;
# Check that we cannot drop the last visible column when we have hidden
# generated columns.
CREATE TABLE t(x INT, KEY((1+1)));
ALTER TABLE t DROP COLUMN x;
ERROR HY000: A table must have at least one visible column.
DROP TABLE t;
# See that we print out the correct error message in case of duplicate
# index names.
CREATE TABLE t (x INT);
CREATE INDEX idx ON t ((x+1));
CREATE INDEX idx ON t ((x+2));
ERROR 42000: Duplicate key name 'idx'
DROP TABLE t;
#
# Bug#28206859 SERVER CRASHED WHEN NORMAL INDEX WAS CREATED USING NEW
#              FUNCTIONAL INDEX SYNTAX
#
CREATE TABLE t2(a INT, b INT, INDEX id2 ((a)));
ERROR HY000: Functional index on a column is not supported. Consider using a regular index instead.
#
# Bug#28206912 ASSERTION `TABLE_LIST->TABLE' FAILED.
#
CREATE TABLE t2(a INT, b INT, INDEX id2 ((a+b+c)));
ERROR 42S22: Unknown column 'c' in 'functional index'
#
# Bug#28216475 SIG 11 IN ITEM_FIELD::REPLACE_FIELD_PROCESSOR DURING
#              CREATE TABLE
#
CREATE TABLE IF NOT EXISTS table470 ( pk INTEGER AUTO_INCREMENT, a1
VARCHAR(3) NOT NULL, b1 DATETIME NOT NULL, c1 TEXT NOT NULL, d1 TEXT NULL,
PRIMARY KEY (a1), KEY ((VALUES(d1))));
ERROR HY000: Expression of functional index 'functional_index' contains a disallowed function.
CREATE TABLE  table44 ( pk INTEGER AUTO_INCREMENT, a1 TEXT NOT NULL, b1
DATETIME NOT NULL, c1 TIME NOT NULL, d1 BLOB NOT NULL, PRIMARY KEY (a1), KEY
((VALUES(d1))));
ERROR HY000: Expression of functional index 'functional_index' contains a disallowed function.
#
# Bug #28222789 SIG 11 IN BITMAP<64U>::INTERSECT DURING CREATE INDEX
#
CREATE TABLE t1 (
pk int(11) NOT NULL AUTO_INCREMENT,
col_int_nokey int(11) DEFAULT NULL,
col_int_key int(11) DEFAULT NULL,
col_date_key date DEFAULT NULL,
col_date_nokey date DEFAULT NULL,
col_time_key time DEFAULT NULL,
col_time_nokey time DEFAULT NULL,
col_datetime_key datetime DEFAULT NULL,
col_datetime_nokey datetime DEFAULT NULL,
col_varchar_key varchar(1) DEFAULT NULL,
col_varchar_nokey varchar(1) DEFAULT NULL,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key),
KEY col_date_key (col_date_key),
KEY col_time_key (col_time_key),
KEY col_datetime_key (col_datetime_key),
KEY col_varchar_key (col_varchar_key,col_int_key),
KEY ind25 ((dayofmonth(col_time_nokey))),
KEY ind211 ((cast(col_date_nokey as date))),
KEY ind602 ((is_uuid(col_time_nokey)))
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci;
Warnings:
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
Warning	1681	Integer display width is deprecated and will be removed in a future release.
CREATE INDEX ind320 ON t1 ((pk >= col_time_nokey LIKE
ST_GeomFromGeoJSON(col_varchar_key) ));
ERROR HY000: Incorrect arguments to like
DROP TABLE t1;
#
# Bug#28218591 WRONG ERROR MESSAGE WHILE CREATING FUNCTIONAL INDEX
#
CREATE TABLE t(a INT, b INT, INDEX c((null)));
ERROR HY000: The used storage engine cannot index the expression 'NULL'.
# Blank functional index name should give a "Incorrect index name" error
CREATE TABLE t1(x INT, KEY `` ((x + 1)));
ERROR 42000: Incorrect index name ''
# Check that we don't use the functional index in case of a collation
# mismatch
CREATE TABLE t(x VARCHAR(10), KEY k ((CAST(CONCAT(x,x) AS BINARY))));
INSERT INTO t VALUES ('x');
ANALYZE TABLE t;
Table	Op	Msg_type	Msg_text
test.t	analyze	status	OK
EXPLAIN SELECT * FROM t WHERE CONCAT(x,x) = 'XX';
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using where
Warnings:
Note	1003	/* select#1 */ select `test`.`t`.`x` AS `x` from `test`.`t` where (concat(`test`.`t`.`x`,`test`.`t`.`x`) = 'XX')
DROP TABLE t;
#
# Bug#28244585 ERROR WHILE CREATING TABLE WITH FUNCTIONAL INDEXES INSIDE
#              PROCEDURES.
CREATE PROCEDURE p1() BEGIN
CREATE TABLE t(a INT,b INT,UNIQUE INDEX i((a+b)));
END//
CALL p1();
CALL p1();
ERROR 42S01: Table 't' already exists
DROP TABLE t;
CALL p1();
DROP TABLE t;
DROP PROCEDURE p1;
# Code coverage tests
CREATE TABLE t1 (
col1 FLOAT
, col2 TIMESTAMP
, col3 YEAR
, INDEX ((ABS(col1)))
, INDEX ((ADDDATE(col2, INTERVAL 2 DAY)))
, INDEX ((ABS(col3)))
);
DROP TABLE t1;
CREATE TABLE t1 (
col4 TEXT NOT NULL
, INDEX ((ST_AsBinary(col4)))
);
ERROR HY000: Cannot create a functional index on an expression that returns a BLOB or TEXT. Please consider using CAST.
# See that we get a reasonable error message when trying to remove a
# column that is a part of a functional index.
CREATE TABLE t1 (col1 INT, col2 INT, col3 INT, INDEX idx ((col1 + col2 + col3)));
ALTER TABLE t1 DROP COLUMN col2;
ERROR HY000: Column 'col2' has a functional index dependency and cannot be dropped or renamed.
ALTER TABLE t1 DROP COLUMN col3;
ERROR HY000: Column 'col3' has a functional index dependency and cannot be dropped or renamed.
ALTER TABLE t1 DROP INDEX idx;
ALTER TABLE t1 DROP COLUMN col3;
DROP TABLE t1;
# Check that INFORMATION_SCHEMA.KEY_COLUMN_USAGE doesn't reveal any
# functional indexes, since this view provides information about columns
# and a functional index does not represent a column per se.
CREATE TABLE t (
col1 INT
, UNIQUE INDEX regular_index (col1)
, UNIQUE INDEX functional_index ((ABS(col1))));
SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE table_name = "t";
CONSTRAINT_NAME
regular_index
DROP TABLE t;
#
# Bug#28526493 ASSERTION WHEN CREATING A FUNCTIONAL INDEX ON A SUBQUERY
#
CREATE TABLE t1 (x INT);
CREATE TABLE t2 (y INT, KEY (((SELECT * FROM t1))));
ERROR HY000: Expression of functional index 'functional_index' contains a disallowed function.
CREATE TABLE t2 (y INT, KEY (((SELECT x FROM t1))));
ERROR HY000: Expression of functional index 'functional_index' contains a disallowed function.
CREATE TABLE t2 (y INT, KEY (((SELECT 1 FROM t1))));
ERROR HY000: Expression of functional index 'functional_index' contains a disallowed function.
DROP TABLE t1;
#
# Bug#28643252	ASSERT FAILURE WHEN FUNCTIONAL INDEX EXPRESSION IS A LIST
#
CREATE TABLE t (j JSON, KEY k (((j,j))));
ERROR HY000: Expression of functional index 'k' cannot refer to a row value.
#
# Bug#29360763	WL#1075: FUNCTIONAL INDEX: INCORRECT ERROR MESSAGE REVEALS
#              HIDDEN COLUMN NAME
#
CREATE TABLE t1 (
j1 JSON,
j3 JSON,
KEY my_idx ((CAST(j1->'$[0]' as SIGNED))),
KEY my_idx_char ((CAST(j3->'$[0]' as CHAR(10))))
);
ALTER TABLE t1 RENAME KEY my_idx_char TO my_idx;
ERROR 42000: Duplicate key name 'my_idx'
DROP TABLE t1;
#
# Bug#29317684 REPLICATION IS SENSITIVE TO ORDER OF HIDDEN COLUMNS FOR
#              FUNCTIONAL INDEXES
#
CREATE TABLE t1 (col1 INT, INDEX ((col1 + col1)));
SELECT COLUMN_NAME, ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = "t1";
COLUMN_NAME	ORDINAL_POSITION
col1	1
ALTER TABLE t1 ADD COLUMN col2 INT;
SELECT COLUMN_NAME, ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = "t1";
COLUMN_NAME	ORDINAL_POSITION
col1	1
col2	2
DROP TABLE t1;
# See that INSTANT ADD COLUMN still works with this bugfix.
CREATE TABLE t1 (col1 INT, col2 INT AS (col1 + col1), INDEX (col2));
ALTER TABLE t1 ADD COLUMN new_col INT AFTER col1, ALGORITHM=INSTANT;
DROP TABLE t1;
#
# Bug#29934661 FUNCTIONAL INDEX SEEMS TO MALFUNCTION WITH UNSIGNED COLUMN
#
# The hidden column that is added should have a data type that can store
# the absolute value of an unsigned data type.
CREATE TABLE t0(c0 INT UNSIGNED, INDEX idx ((ABS(c0))));
INSERT INTO t0 (c0) VALUES (4294967294);
DROP TABLE t0;
#
# Bug#29723353: ADDING FUNCTIONAL INDEX ON JSON EXPRESSION CHANGES RESULT
#
CREATE TABLE t(j JSON);
INSERT INTO t VALUES ('{"x":"abc"}'), ('{"x":"ABC"}');
SELECT * FROM t WHERE j->>'$.x' = 'abc';
j
{"x": "abc"}
SELECT * FROM t WHERE j->'$.x' = 'abc';
j
{"x": "abc"}
CREATE INDEX idx ON t((CAST(j->>'$.x' AS CHAR(100))));
SELECT * FROM t WHERE j->>'$.x' = 'abc';
j
{"x": "abc"}
SELECT * FROM t WHERE j->'$.x' = 'abc';
j
{"x": "abc"}
ALTER TABLE t DROP INDEX idx;
CREATE INDEX idx ON t((CAST(j->'$.x' AS CHAR(100))));
SELECT * FROM t WHERE j->>'$.x' = 'abc';
j
{"x": "abc"}
SELECT * FROM t WHERE j->'$.x' = 'abc';
j
{"x": "abc"}
DROP TABLE t;
##
## Bug #31017765 CREATE TABLE ... SELECT FAILS IF THERE IS A FUNCTIONAL INDEX
##
##
CREATE TEMPORARY TABLE issue_functional_key_part (
sold_on DATETIME NOT NULL DEFAULT NOW(),
INDEX sold_on_date ((DATE(sold_on)))
)
SELECT NOW() `sold_on`;
#
# Bug#30838749: PERFORMANCE DROP WHEN CREATING A FUNCTIONAL INDEX
#
CREATE TABLE t1(x INT, KEY k ((x+1)));
CREATE TABLE t2(j JSON);
LOAD DATA INFILE 'MYSQLTEST_VARDIR/tmp/bug30838749' INTO TABLE t2
LINES STARTING BY 'EXPLAIN' TERMINATED BY 'EOL';
SELECT j->'$**.used_columns' FROM t2;
j->'$**.used_columns'
[["x"]]
DROP TABLE t1, t2;
#
# Bug#32287186: FAILED TO CREATE FUNCTION INDEX,
# COLUMN NAME IS UPPERCASE CHARACTER
#
# Verify that normal index creation is case-insensitive on
# column name
CREATE TABLE t1 (id INT, name VARCHAR(50), INDEX (NAME));
CREATE TABLE t2 (id INT, name VARCHAR(50), INDEX (name));
CREATE TABLE t3 (id INT, NAME VARCHAR(50), INDEX (name));
CREATE TABLE t4 (id INT, NAME VARCHAR(50), INDEX (NAME));
# Now verify that functional index creation is case-insensitive
# on column name as well
CREATE TABLE t5 (id INT, name VARCHAR(50),
INDEX ((SUBSTR(name, 1, 2))));
CREATE TABLE t6 (id INT, name VARCHAR(50),
INDEX ((SUBSTR(NAME, 1, 2))));
CREATE TABLE t7 (id INT, NAME VARCHAR(50),
INDEX ((SUBSTR(name, 1, 2))));
CREATE TABLE t8 (id INT, NAME VARCHAR(50),
INDEX ((SUBSTR(NAME, 1, 2))));
# Cleanup
DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
DROP TABLE t4;
DROP TABLE t5;
DROP TABLE t6;
DROP TABLE t7;
DROP TABLE t8;
#
# Bug#33851055: Correlated subquery does not use functional index
#
CREATE TABLE t (
id INT DEFAULT NULL,
KEY functional_index ((id * id))
);
CREATE TABLE t2 (
id INT NOT NULL AUTO_INCREMENT,
val INT,
PRIMARY KEY (id)
);
INSERT INTO t(id) VALUES (1), (1), (2), (-1), (-2);
INSERT INTO t2(val) VALUES (1), (2), (3), (4);
ANALYZE TABLE t,t2;
Table	Op	Msg_type	Msg_text
test.t	analyze	status	OK
test.t2	analyze	status	OK
EXPLAIN SELECT id, val, (SELECT id FROM t WHERE (id * id) = val LIMIT 1) AS tid FROM t2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t2	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	NULL
2	DEPENDENT SUBQUERY	t	NULL	ref	functional_index	functional_index	9	test.t2.val	2	100.00	Using where
Warnings:
Note	1276	Field or reference 'test.t2.val' of SELECT #2 was resolved in SELECT #1
Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id`,`test`.`t2`.`val` AS `val`,(/* select#2 */ select `test`.`t`.`id` from `test`.`t` where ((`id` * `id`) = `test`.`t2`.`val`) limit 1) AS `tid` from `test`.`t2`
SELECT id, val, (SELECT id FROM t WHERE (id * id) = val LIMIT 1) AS tid FROM t2;
id	val	tid
1	1	1
2	2	NULL
3	3	NULL
4	4	2
EXPLAIN SELECT id, val, (SELECT id FROM t WHERE (id * id) BETWEEN val and val + 2 LIMIT 1) AS tid FROM t2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t2	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	NULL
2	DEPENDENT SUBQUERY	t	NULL	ALL	functional_index	NULL	NULL	NULL	5	20.00	Range checked for each record (index map: 0x1)
Warnings:
Note	1276	Field or reference 'test.t2.val' of SELECT #2 was resolved in SELECT #1
Note	1276	Field or reference 'test.t2.val' of SELECT #2 was resolved in SELECT #1
Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id`,`test`.`t2`.`val` AS `val`,(/* select#2 */ select `test`.`t`.`id` from `test`.`t` where ((`id` * `id`) between `test`.`t2`.`val` and (`test`.`t2`.`val` + 2)) limit 1) AS `tid` from `test`.`t2`
SELECT id, val, (SELECT id FROM t WHERE (id * id) BETWEEN val and val + 2 LIMIT 1) AS tid FROM t2;
id	val	tid
1	1	1
2	2	2
3	3	2
4	4	2
EXPLAIN SELECT id, val, (SELECT id FROM t WHERE (id * id) IN (val) LIMIT 1) AS tid FROM t2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t2	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	NULL
2	DEPENDENT SUBQUERY	t	NULL	ref	functional_index	functional_index	9	test.t2.val	2	100.00	Using where
Warnings:
Note	1276	Field or reference 'test.t2.val' of SELECT #2 was resolved in SELECT #1
Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id`,`test`.`t2`.`val` AS `val`,(/* select#2 */ select `test`.`t`.`id` from `test`.`t` where ((`id` * `id`) = `test`.`t2`.`val`) limit 1) AS `tid` from `test`.`t2`
SELECT id, val, (SELECT id FROM t WHERE (id * id) IN (val) LIMIT 1) AS tid FROM t2;
id	val	tid
1	1	1
2	2	NULL
3	3	NULL
4	4	2
EXPLAIN SELECT id, val, (SELECT id FROM t WHERE (id * id) = val + (SELECT 1 FROM t WHERE id > 1) LIMIT 1) AS tid FROM t2;
id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	t2	NULL	ALL	NULL	NULL	NULL	NULL	4	100.00	NULL
2	DEPENDENT SUBQUERY	t	NULL	ref	functional_index	functional_index	9	func	2	100.00	Using where
3	SUBQUERY	t	NULL	ALL	NULL	NULL	NULL	NULL	5	33.33	Using where
Warnings:
Note	1276	Field or reference 'test.t2.val' of SELECT #2 was resolved in SELECT #1
Note	1003	/* select#1 */ select `test`.`t2`.`id` AS `id`,`test`.`t2`.`val` AS `val`,(/* select#2 */ select `test`.`t`.`id` from `test`.`t` where ((`id` * `id`) = (`test`.`t2`.`val` + (/* select#3 */ select 1 from `test`.`t` where (`test`.`t`.`id` > 1)))) limit 1) AS `tid` from `test`.`t2`
SELECT id, val, (SELECT id FROM t WHERE (id * id) = val + (SELECT 1 FROM t WHERE id > 1) LIMIT 1) AS tid FROM t2;
id	val	tid
1	1	NULL
2	2	NULL
3	3	2
4	4	NULL
DROP TABLE t, t2;
#
# Bug#35352161: Mysql dies when a functional index on FIND_IN_SET is added
#

# Create the table without INDEXes first.
CREATE TABLE t1 (
id          INT NOT NULL AUTO_INCREMENT,
roles       SET('Admin', 'Moderator', 'Editor') NOT NULL,
PRIMARY KEY (id)
);

# Fill the table.
INSERT INTO t1 VALUES (NULL, "Admin");
INSERT INTO t1 VALUES (NULL, "Moderator");
INSERT INTO t1 VALUES (NULL, "Editor");
INSERT INTO t1 VALUES (NULL, "Editor,Admin");

# Show operation without index.
SHOW INDEXES FROM t1 WHERE key_name="i1";
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Visible	Expression
EXPLAIN FORMAT=tree SELECT id FROM t1 WHERE FIND_IN_SET('Admin', roles)>0;
EXPLAIN
-> Filter: (find_in_set('Admin',t1.roles) > 0)  (rows=4)
    -> Table scan on t1  (rows=4)

SELECT id FROM t1 WHERE FIND_IN_SET('Admin', roles)>0;
id
1
4

# ADD function INDEX: If Bug#35352161 is present, this will fail.
ALTER TABLE t1 ADD INDEX i1 ((FIND_IN_SET('Admin', roles)));

# Show operation using functional index i1.
# (Prove index exists, we're using it, and it gives correct results.)
SHOW INDEXES FROM t1 WHERE key_name="i1";
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Visible	Expression
t1	1	i1	1	NULL	A	2	NULL	NULL		BTREE			YES	find_in_set(_utf8mb4\'Admin\',`roles`)
EXPLAIN FORMAT=tree SELECT id FROM t1 WHERE FIND_IN_SET('Admin', roles)>0;
EXPLAIN
-> Filter: (find_in_set(_utf8mb4'Admin',roles) > 0)  (rows=2)
    -> Index range scan on t1 using i1 over (0 < find_in_set(_utf8mb4'Admin',`roles`))  (rows=2)

SELECT id FROM t1 WHERE FIND_IN_SET('Admin', roles)>0;
id
1
4

# Clean up.
DROP TABLE t1;

# Create table including index.
# If Bug#35352161 is present, the INDEX part will fail.
CREATE TABLE t1 (
id    INT NOT NULL AUTO_INCREMENT,
roles SET('Admin', 'Moderator', 'Editor') NOT NULL,
PRIMARY KEY (id),
INDEX i1((FIND_IN_SET('Admin', roles)))
);

# Clean up.
DROP TABLE t1;

#
# Bug#35497623: SHOW INDEXES with generated column may access wrong key-part
#

# SHOW INDEXES on regular table:
CREATE TABLE t1 (c1 INT);
CREATE TABLE t2
(UNIQUE i3 TYPE BTREE (c1, (c1 REGEXP c1))) TABLE t1;
SHOW INDEXES IN t2;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Visible	Expression
t2	0	i3	1	c1	A	0	NULL	NULL	YES	BTREE			YES	NULL
t2	0	i3	2	NULL	A	0	NULL	NULL	YES	BTREE			YES	regexp_like(`c1`,`c1`)
/* regular table */
DROP TABLE t2;
DROP TABLE t1;

# SHOW INDEXES on temporary table (different code path!):
CREATE TABLE t1 (c1 INT);
CREATE TEMPORARY TABLE t2
(UNIQUE i3 TYPE BTREE (c1, (c1 REGEXP c1))) TABLE t1;
SHOW INDEXES IN t2;
Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Visible	Expression
t2	0	i3	1	c1	A	0	NULL	NULL	YES	BTREE			YES	NULL
t2	0	i3	2	NULL	A	0	NULL	NULL	YES	BTREE			YES	regexp_like(`c1`,`c1`)
/* temporary table */
DROP TABLE t2;
DROP TABLE t1;