--source include/have_debug.inc

--echo # Show all hidden columns, so that we can inspect that the column
--echo # is created with the correct character set/collation, length, data type
--echo # etc.

CREATE TABLE t1 (
  str_col VARCHAR(255),
  dbl_col DOUBLE,
  decimal_col DECIMAL(5, 2),
  date_col DATE,
  time_col TIME,
  time_with_fractions_col TIME(6),
  datetime_col DATETIME,
  datetime_with_fractions_col DATETIME(6),
  json_col JSON,
  dbl_with_length DOUBLE(4, 2),
  unsigned_int INT UNSIGNED,
  geometry_col POINT,
  INDEX idx1 ((CONVERT(SUBSTRING(str_col, 2, 1) USING latin1))),
  INDEX idx2 ((ABS(dbl_col))),
  INDEX idx3 ((TRUNCATE(decimal_col, 0))),
  INDEX idx4 ((YEAR(date_col))),
  INDEX idx5 ((ADDTIME('01:00:00', time_col))),
  INDEX idx6 ((ADDTIME('01:00:00', time_with_fractions_col))),
  INDEX idx7 ((DATE_SUB(datetime_col, INTERVAL 30 DAY))),
  INDEX idx8 ((DATE_SUB(datetime_with_fractions_col, INTERVAL 30 DAY))),
  INDEX idx9 ((JSON_VALID(json_col))),
  INDEX idx10 ((ROUND(dbl_col, 2))),
  INDEX idx11 ((dbl_with_length * 2.0)),
  INDEX idx12 ((ABS(unsigned_int))),
  INDEX idx13 ((ST_X(geometry_col)))
);

SET SESSION debug="+d,show_hidden_columns";
SHOW CREATE TABLE t1;
SET SESSION debug="-d,show_hidden_columns";
DROP TABLE t1;

--echo # Check that the hidden generated column is renamed if the functional
--echo # index is renamed.
CREATE TABLE t1 (col1 INT, INDEX my_index((FLOOR(col1))));
SET SESSION debug="+d,show_hidden_columns";
SHOW CREATE TABLE t1;
ALTER TABLE t1 RENAME INDEX my_index TO foobar;
SHOW CREATE TABLE t1;
SET SESSION debug="-d,show_hidden_columns";
DROP TABLE t1;

--echo # Check that the hidden generated columns are removed if a functional
--echo # index is deleted.
SET SESSION debug="+d,show_hidden_columns";
CREATE TABLE t1 (
  col1 INT
, col2 INT
, INDEX idx1 ((ABS(col1)))
, INDEX idx2 ((col1 + 1), (col2 + 2)));
SHOW CREATE TABLE t1;
ALTER TABLE t1 DROP INDEX idx2;
SHOW CREATE TABLE t1;
DROP TABLE t1;
SET SESSION debug="-d,show_hidden_columns";

# Ensure that multi-valued index isn''t covering and index scan isn''t used
SET SESSION debug="+d,show_hidden_columns";
CREATE TABLE t1(j json, INDEX mv_idx((CAST(j AS UNSIGNED ARRAY))));
SHOW CREATE TABLE t1;
SELECT 680963a1f6dcb151c3e713b46b4c3452 FROM t1 ;
EXPLAIN SELECT 680963a1f6dcb151c3e713b46b4c3452 FROM t1 ;
SET SESSION debug="-d,show_hidden_columns";
DROP TABLE t1;

--echo #
--echo # Bug#28037195 ASSERTION `FALSE' IN IN
--echo #              ITEM_FIELD::REPLACE_FIELD_PROCESSOR AT SQL_TABLE.CC:5850
--echo #
CREATE TABLE t2 (c1 INT);
--error ER_BAD_FIELD_ERROR
CREATE INDEX name_collision ON t2((ABS(cq)));
DROP TABLE t2;


--echo #
--echo # Bug#28069679 WL#1075 ASSERTION `!M_EXPRESSION->FIXED' FAILED
--echo #
CREATE TABLE t1 (
  col1 INT,
  INDEX idx ((PI()))
);
DROP TABLE t1;

--echo #
--echo # Bug#28069710 WL#1075 ASSERTION
--echo #              `R0 != STRING_RESULT && R1 != STRING_RESULT'
--echo #
CREATE TABLE t1 (
  col1 VARCHAR(255),
  INDEX idx1 ((col1 * col1))
);
DROP TABLE t1;


--echo #
--echo # Bug#28069731 WL#1075 ASSERTION `!TABLE || (!TABLE->WRITE_SET ||
--echo #              BITMAP_IS_SET(TABLE->WRITE_SE
--echo #
CREATE TABLE table10_innodb_int_autoinc (
  col_int_key int(11) DEFAULT NULL,
  col_char_32_key char(32) DEFAULT NULL,
  col_char_32_not_null_key char(32) NOT NULL,
  pk int(11) NOT NULL AUTO_INCREMENT,
  col_int_not_null_key int(11) NOT NULL,
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key),
  KEY col_char_32_key (col_char_32_key),
  KEY col_char_32_not_null_key (col_char_32_not_null_key),
  KEY col_int_not_null_key (col_int_not_null_key),
  KEY i1 (((col_int_key + col_int_key))),
  KEY i2 (((col_int_key < 5))),
  KEY i3 (((col_int_key < 9))),
  KEY i5 (((col_int_not_null_key > 1)))
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO table10_innodb_int_autoinc VALUES
  (43,'if','that',1,99)
, (1,'we','she',2,1203568640)
, (7,'f','kxc',3,40)
, (-1846411264,'xce','mean',4,5)
, (9,'r','c',5,0)
, (5,'ek','the',6,138)
, (77,'l','i',7,-1414922240)
, (1,'k','q',8,1)
, (-1284505600,'sa','w',9,8)
, (NULL,'ate','t',10,4);
HANDLER table10_innodb_int_autoinc OPEN AS alias1 ;
HANDLER alias1 READ `PRIMARY` NEXT WHERE `col_int_not_null_key` <= 'j' LIMIT 8;
DROP TABLE table10_innodb_int_autoinc;

--echo # Test that removing and altering functional indexes works correctly
--echo # with different casing.
CREATE TABLE t1 (
  col1 INT
, INDEX myKey ((ABS(col1))));

--echo # Get the expected name of the hidden generated column.
SELECT MD5(CONCAT("myKey", "0"));

SET SESSION debug="+d,show_hidden_columns";

--echo # Column names aren't case sensitive, so this DDL should find the column
--echo # and give an error saying that the column cannot be removed due to
--echo # functional index.
--error ER_CANNOT_DROP_COLUMN_FUNCTIONAL_INDEX
ALTER TABLE t1 DROP COLUMN 84A85350b439e4a8875061c9b8a13f70;
SHOW CREATE TABLE t1;

--echo # Index names aren't case sensitive, so this DDL should rename the index
--echo # and the hidden generated column as well.
ALTER TABLE t1 RENAME INDEX myKEY TO renaMEDkey;
SHOW CREATE TABLE t1;

--echo # Index names aren't case sensitive, so this DDL should remove the index
--echo # and the column.
ALTER TABLE t1 DROP INDEX renamedkey;
SHOW CREATE TABLE t1;
DROP TABLE t1;
SET SESSION debug="-d,show_hidden_columns";

--echo #
--echo # Bug#28216553 ASSERTION `FIELD_TYPE < (MYSQL_TYPE_BIT + 1) || FIELD_TYPE
--echo #
CREATE TABLE table1130 ( pk INTEGER AUTO_INCREMENT, a1 INTEGER NOT NULL, b1
INTEGER NULL, c1 BLOB NULL, d1 VARCHAR(2) NULL, PRIMARY KEY (pk), KEY
((COALESCE(a1))));
DROP TABLE table1130;

--error ER_FUNCTIONAL_INDEX_ON_FIELD
CREATE TABLE table29 (
  pk INTEGER AUTO_INCREMENT
, a1 VARCHAR(5) NOT NULL
, PRIMARY KEY (pk)
, KEY ((a1)));

--echo #
--echo # Bug#28226443 VIRTUAL BOOL ITEM_FIELD::REPLACE_FIELD_PROCESSOR(UCHAR*):
--echo #              ASSERTION `FALSE' FAIL
--echo #
CREATE TABLE t1(a INT, b INT, c INT, UNIQUE INDEX i((a+b)));
--error ER_KEY_COLUMN_DOES_NOT_EXITS
ALTER TABLE t1 ADD INDEX p(a9e26254e651465c89ff715d5733e97c);
--error ER_BAD_FIELD_ERROR
ALTER TABLE t1 ADD INDEX g((a + a9e26254e651465c89ff715d5733e97c));
DROP TABLE t1;

--echo #
--echo # Bug#28263237 ASSERTION `FALSE' IN ITEM_FIELD::REPLACE_FIELD_PROCESSOR
--echo #              AT SQL_TABLE.CC:6303
--echo #
--error ER_FUNCTIONAL_INDEX_ON_FIELD
CREATE TABLE table1286 (a1 BIT(24) NULL, KEY ((a1)));

--error ER_FUNCTIONAL_INDEX_ON_FIELD
CREATE TABLE  table276 ( pk INTEGER AUTO_INCREMENT, a1
SET('Nebraska','gfjzdfpngmbhvftlmiwrgduhdsbnkswbacwjvotkav','fjzdf') NULL,
PRIMARY KEY (pk), KEY ((a1)) );

CREATE TABLE t1(
  e ENUM('a', 'bbb', 'cccccc')
, s SET('a', 'bbb', 'cccccc')
, b BIT(5)
, KEY ((NULLIF(e, null)))
, KEY ((NULLIF(s, null)))
, KEY ((NULLIF(b, null))));
SET SESSION debug="+d,show_hidden_columns";
SHOW CREATE TABLE t1;
SET SESSION debug="-d,show_hidden_columns";
DROP TABLE t1;

--error ER_SPATIAL_FUNCTIONAL_INDEX
CREATE TABLE table121 (
  pk INTEGER AUTO_INCREMENT
, a1 SET('Michigan','w','d') NOT NULL
, PRIMARY KEY (pk)
, KEY ((ST_Centroid(a1))));

--echo # Test that the collation is set correctly for numeric columns. If we
--echo # have forgotten to set the correct collation, the hidden generated
--echo # column will have a VARBINARY field, and we won't be able to use the
--echo # index due to collation mismatch.
CREATE TABLE t1 (
  col1 INT
, INDEX idx1 ((SOUNDEX(col1))));
SET SESSION debug="+d,show_hidden_columns";
SHOW CREATE TABLE t1;
SET SESSION debug="-d,show_hidden_columns";

EXPLAIN SELECT * FROM t1 WHERE SOUNDEX(col1) = "aa";
DROP TABLE t1;

--echo #
--echo # Bug#29317684 REPLICATION IS SENSITIVE TO ORDER OF HIDDEN COLUMNS FOR
--echo #              FUNCTIONAL INDEXES
--echo #

--echo # See that hidden columns are inserted at the end, sorted by their name
SET SESSION debug="+d,show_hidden_columns";
CREATE TABLE t1 (col1 INT, INDEX functional_index_1 ((col1 + 1)));
SHOW CREATE TABLE t1;
CREATE INDEX functional_index_2 ON t1 ((col1 + 2));
SHOW CREATE TABLE t1;
CREATE INDEX functional_index_3 ON t1 ((col1 + 3));
SHOW CREATE TABLE t1;
CREATE INDEX functional_index_4 ON t1 ((col1 + 4));
SHOW CREATE TABLE t1;
ALTER TABLE t1 ADD COLUMN col2 INT;
SHOW CREATE TABLE t1;
CREATE INDEX functional_index_5 ON t1 ((col1 + col2));
SHOW CREATE TABLE t1;
DROP TABLE t1;

CREATE TABLE t1 (
  col1 BIT(5),
  col2 BIT(10),
  KEY functional_index_1 ((NULLIF(col1, NULL))),
  KEY functional_index_2 ((NULLIF(col2, NULL)))
);
SHOW CREATE TABLE t1;
DROP TABLE t1;

SET SESSION debug="-d,show_hidden_columns";