1 files changed, 464 insertions, 0 deletions
diff --git a/mysql-test/main/ignored_index.test b/mysql-test/main/ignored_index.test
new file mode 100644
index 00000000..a3d46fe6
--- /dev/null
+++ b/mysql-test/main/ignored_index.test
@@ -0,0 +1,464 @@
+--echo #
+--echo # MDEV-7317: Make an index ignorable to the optimizer
+--echo #
+
+
+--echo # Test of ALTER INDEX syntax.
+
+CREATE TABLE t1 ( a INT, KEY (a) );
+SHOW KEYS FROM t1;
+ALTER TABLE t1 ALTER INDEX a IGNORED;
+SHOW KEYS FROM t1;
+ALTER TABLE t1 ALTER INDEX a NOT IGNORED;
+SHOW KEYS FROM t1;
+DROP TABLE t1;
+
+--echo # Test of CREATE INDEX syntax with IGNORED indexes.
+
+CREATE TABLE t1 ( a INT, b INT );
+CREATE INDEX a_ignorable ON t1(a) IGNORED;
+CREATE INDEX b_not_ignorable ON t1(a) NOT IGNORED;
+CREATE INDEX a_b_ignorable ON t1(a, b) IGNORED;
+SHOW INDEXES FROM t1;
+DROP TABLE t1;
+
+--echo # Test that IGNORED indexes are not used.
+
+CREATE TABLE t1 ( a INT, KEY (a) );
+CREATE TABLE t2 ( a INT, KEY (a) IGNORED );
+
+INSERT INTO t1 VALUES (1), (2), (3), (4), (5);
+INSERT INTO t2 SELECT * FROM t1;
+
+ANALYZE TABLE t1, t2;
+
+EXPLAIN SELECT a FROM t1;
+ALTER TABLE t1 ALTER INDEX a IGNORED;
+EXPLAIN SELECT a FROM t1;
+ALTER TABLE t1 ALTER INDEX a NOT IGNORED;
+EXPLAIN SELECT a FROM t1;
+
+EXPLAIN SELECT a FROM t2;
+ALTER TABLE t2 ALTER INDEX a NOT IGNORED;
+EXPLAIN SELECT a FROM t2;
+
+DROP TABLE t1, t2;
+
+--echo # Test that renaming an index does not change ignorability and vice versa.
+
+CREATE TABLE t1 (
+  a INT, INDEX (a),
+  b INT, INDEX (b) IGNORED
+);
+
+SHOW INDEXES FROM t1;
+
+ALTER TABLE t1 RENAME INDEX a TO a1;
+ALTER TABLE t1 RENAME INDEX b TO b1;
+
+SHOW INDEXES FROM t1;
+
+ALTER TABLE t1 ALTER INDEX a1 IGNORED;
+ALTER TABLE t1 ALTER INDEX b1 NOT IGNORED;
+
+SHOW INDEXES FROM t1;
+
+DROP TABLE t1;
+
+--echo # Test of SHOW CREATE TABLE.
+
+CREATE TABLE t1 (
+  a INT,
+  b INT,
+  c INT,
+  INDEX (a) NOT IGNORED,
+  INDEX (b) IGNORED,
+  INDEX (c)
+);
+
+SHOW CREATE TABLE t1;
+
+DROP TABLE t1;
+
+--echo # Test that primary key indexes can't be made ignorable.
+
+--error ER_PK_INDEX_CANT_BE_IGNORED
+CREATE TABLE t1 ( a INT, PRIMARY KEY (a) IGNORED );
+--error ER_PARSE_ERROR
+CREATE TABLE t1 ( a INT PRIMARY KEY IGNORED );
+--error ER_PARSE_ERROR
+CREATE TABLE t1 ( a INT KEY IGNORED );
+--error ER_PARSE_ERROR
+ALTER TABLE t1 ALTER INDEX PRIMARY IGNORED;
+
+CREATE TABLE t1(a INT NOT NULL);
+--error ER_PK_INDEX_CANT_BE_IGNORED
+ALTER TABLE t1 ADD PRIMARY KEY (a) IGNORED;
+
+DROP TABLE t1;
+
+
+CREATE TABLE t1 (
+  a INT, KEY (a),
+  b INT, KEY (b) IGNORED
+);
+
+--error ER_KEY_DOES_NOT_EXISTS
+ALTER TABLE t1 RENAME INDEX no_such_index TO x;
+--error ER_KEY_DOES_NOT_EXISTS
+ALTER TABLE t1 ALTER INDEX no_such_index IGNORED;
+
+DROP TABLE t1;
+
+
+CREATE TABLE t1 (
+  a INT, KEY (a),
+  b INT, KEY (b) IGNORED
+);
+
+--error ER_KEY_DOES_NOT_EXISTS
+ALTER TABLE t1 RENAME INDEX no_such_index TO x;
+--error ER_KEY_DOES_NOT_EXISTS
+ALTER TABLE t1 ALTER INDEX no_such_index IGNORED;
+
+
+--echo #
+--echo # Repeated alter actions. Should work.
+--echo #
+ALTER TABLE t1 ALTER INDEX a IGNORED, ALTER INDEX a NOT IGNORED;
+SHOW INDEXES FROM t1;
+ALTER TABLE t1 ALTER INDEX a NOT IGNORED, ALTER INDEX b IGNORED;
+SHOW INDEXES FROM t1;
+
+
+--echo #
+--echo # Various combinations of RENAME INDEX and ALTER INDEX ... IGNORED.
+--echo #
+--error ER_KEY_DOES_NOT_EXISTS
+ALTER TABLE t1 RENAME INDEX a TO x, RENAME INDEX x TO a;
+--error ER_KEY_DOES_NOT_EXISTS
+ALTER TABLE t1 RENAME INDEX a TO x, ALTER INDEX x IGNORED;
+--error ER_KEY_DOES_NOT_EXISTS
+ALTER TABLE t1 RENAME INDEX a TO x, ALTER INDEX a NOT IGNORED;
+--error ER_KEY_DOES_NOT_EXISTS
+ALTER TABLE t1 ALTER INDEX a NOT IGNORED, RENAME INDEX a TO x;
+
+
+--echo #
+--echo # Various algorithms and their effects.
+--echo #
+
+INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);
+ANALYZE TABLE t1;
+
+--enable_info
+ALTER TABLE t1 ALTER INDEX a IGNORED, ALGORITHM = COPY;
+--disable_info
+ANALYZE TABLE t1;
+SHOW INDEXES FROM t1;
+
+--enable_info
+ALTER TABLE t1 ALTER INDEX a NOT IGNORED, ALGORITHM = INPLACE;
+--disable_info
+ANALYZE TABLE t1;
+SHOW INDEXES FROM t1;
+
+--enable_info
+ALTER TABLE t1 ALTER INDEX a IGNORED, ALGORITHM = DEFAULT;
+--disable_info
+ANALYZE TABLE t1;
+SHOW INDEXES FROM t1;
+
+--enable_info
+ALTER TABLE t1 ALTER INDEX a NOT IGNORED;
+--disable_info
+ANALYZE TABLE t1;
+SHOW INDEXES FROM t1;
+
+--error ER_KEY_DOES_NOT_EXISTS
+ALTER TABLE t1 ADD INDEX ab(a, b), ALTER INDEX ab IGNORED;
+
+DROP TABLE t1;
+
+
+--echo #
+--echo # The first NOT NULL UNIQUE index may of course be IGNORED if it is
+--echo # not promoted to a primary key
+--echo #
+
+CREATE TABLE t1 (
+  a INT NOT NULL,
+  b INT NOT NULL PRIMARY KEY,
+  UNIQUE KEY (a) IGNORED
+);
+SHOW INDEXES FROM t1;
+DROP TABLE t1;
+
+--echo # The check above applies only to the first NOT NULL UNIQUE index.
+CREATE TABLE t1 (
+  a INT NOT NULL,
+  b INT NOT NULL,
+  UNIQUE KEY (a),
+  UNIQUE KEY (b) IGNORED
+);
+SHOW INDEXES FROM t1;
+DROP TABLE t1;
+
+--error ER_PK_INDEX_CANT_BE_IGNORED
+CREATE TABLE t1 ( a INT NOT NULL, UNIQUE KEY (a) IGNORED);
+
+
+CREATE TEMPORARY TABLE t1 ( a INT, KEY (a) IGNORED);
+INSERT INTO t1 VALUES (0), (1), (2), (3);
+SHOW INDEXES FROM t1;
+EXPLAIN SELECT a FROM t1;
+
+ALTER TABLE t1 ALTER INDEX a NOT IGNORED;
+EXPLAIN SELECT a FROM t1;
+
+DROP TABLE t1;
+
+--echo #
+--echo # IGNORED fulltext indexes.
+--echo #
+CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b));
+INSERT INTO t1 VALUES('Some data', 'for full-text search');
+ANALYZE TABLE t1;
+
+let $query=
+EXPLAIN SELECT * FROM t1 WHERE MATCH(a, b) AGAINST ("collections");
+
+--eval $query
+ALTER TABLE t1 ALTER INDEX a IGNORED;
+
+--error ER_FT_MATCHING_KEY_NOT_FOUND
+--eval $query
+
+DROP TABLE t1;
+
+
+--echo #
+--echo # IGNORED indexes on AUTO_INCREMENT columns.
+--echo #
+CREATE TABLE t1 ( a INT AUTO_INCREMENT, KEY ( a ) );
+INSERT INTO t1 VALUES (), (), ();
+ANALYZE TABLE t1;
+
+EXPLAIN SELECT a FROM t1;
+ALTER TABLE t1 ALTER INDEX a IGNORED;
+SHOW INDEXES FROM t1;
+EXPLAIN SELECT a FROM t1;
+
+DROP TABLE t1;
+
+--echo #
+--echo # IGNORED spatial indexes
+--echo #
+
+
+CREATE TABLE t1 (
+  fid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+  g GEOMETRY NOT NULL,
+  SPATIAL KEY key1(g)
+);
+
+--disable_query_log
+let $1=150;
+let $2=150;
+while ($1)
+{
+  eval INSERT INTO t1 (g) VALUES (GeomFromText('LineString($1 $1, $2 $2)'));
+  dec $1;
+  inc $2;
+}
+--enable_query_log
+
+let $query= EXPLAIN SELECT fid, AsText(g) FROM t1
+WHERE Within(g, GeomFromText('Polygon((140 140,160 140,160 160,140 160,140 140))'));
+
+eval $query;
+ALTER TABLE t1 ALTER INDEX key1 IGNORED;
+SHOW INDEXES FROM t1;
+eval $query;
+
+DROP TABLE t1;
+
+CREATE TABLE t1 ( a INT GENERATED ALWAYS AS (1), KEY key1(a));
+INSERT INTO t1 VALUES (),(),();
+SHOW INDEXES FROM t1;
+EXPLAIN SELECT a FROM t1;
+ALTER TABLE t1 ALTER INDEX key1 IGNORED;
+SHOW INDEXES FROM t1;
+EXPLAIN SELECT a FROM t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Partitioning on keys with an IGNORED index, IGNORED indexes over
+--echo # partitioned tables.
+--echo #
+
+--source include/have_partition.inc
+
+CREATE TABLE t1 (
+  a CHAR(2) NOT NULL,
+  b CHAR(2) NOT NULL,
+  c INT(10) UNSIGNED NOT NULL,
+  d VARCHAR(255) DEFAULT NULL,
+  e VARCHAR(1000) DEFAULT NULL,
+  KEY (a) IGNORED,
+  KEY (b)
+) PARTITION BY KEY (a) PARTITIONS 20;
+
+
+INSERT INTO t1 (a, b, c, d, e) VALUES
+('07', '03', 343, '1', '07_03_343'),
+('01', '04', 343, '2', '01_04_343'),
+('01', '06', 343, '3', '01_06_343'),
+('01', '07', 343, '4', '01_07_343'),
+('01', '08', 343, '5', '01_08_343'),
+('01', '09', 343, '6', '01_09_343'),
+('03', '03', 343, '7', '03_03_343'),
+('03', '06', 343, '8', '03_06_343'),
+('03', '07', 343, '9', '03_07_343'),
+('04', '03', 343, '10', '04_03_343'),
+('04', '06', 343, '11', '04_06_343'),
+('05', '03', 343, '12', '05_03_343'),
+('11', '03', 343, '13', '11_03_343'),
+('11', '04', 343, '14', '11_04_343');
+
+ANALYZE TABLE t1;
+
+EXPLAIN SELECT a FROM t1;
+EXPLAIN SELECT b FROM t1;
+EXPLAIN SELECT * FROM t1 WHERE a = '04';
+ALTER TABLE t1 ALTER INDEX a NOT IGNORED;
+EXPLAIN SELECT a FROM t1;
+EXPLAIN SELECT * FROM t1 WHERE a = '04';
+
+ALTER TABLE t1 ALTER INDEX b IGNORED;
+EXPLAIN SELECT b FROM t1;
+DROP TABLE t1;
+
+--echo #
+--echo # Using FORCE INDEX for an IGNORED index
+--echo #
+
+CREATE TABLE t1(a INT, key k1(a));
+INSERT INTO t1 VALUES (1),(2),(3);
+
+EXPLAIN SELECT * FROM t1 FORCE INDEX(k1);
+ALTER TABLE t1 ALTER INDEX k1 IGNORED;
+SHOW CREATE TABLE t1;
+--error ER_KEY_DOES_NOT_EXISTS
+EXPLAIN SELECT * FROM t1 FORCE INDEX(k1);
+
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-25075: Ignorable index makes the resulting CREATE TABLE invalid
+--echo #
+
+CREATE TABLE t1 (a INT, KEY (a));
+ALTER TABLE t1 ALTER INDEX a IGNORED;
+show create table t1;
+
+DROP TABLE t1;
+
+CREATE TABLE t1 ( a INT, KEY (a) IGNORED);
+show create table t1;
+
+DROP TABLE t1;
+
+--echo #
+--echo # Tests to check usage of IGNORED keyword
+--echo #
+
+CREATE TABLE IGNORED(a INT);
+DROP TABLE IGNORED;
+
+CREATE TABLE t1(a INT);
+SELECT * FROM t1 IGNORED;
+
+DELIMITER |;
+
+CREATE FUNCTION f1(a INT) RETURNS INT
+BEGIN
+  DECLARE IGNORED INT DEFAULT 0;
+  RETURN 0;
+END|
+
+CREATE FUNCTION f2(a INT) RETURNS INT
+BEGIN
+  DECLARE IGNORED INT DEFAULT 0;
+  DECLARE x INT DEFAULT 0;
+  SET x= IGNORED;
+  RETURN 0;
+END|
+
+DELIMITER ;|
+DROP TABLE t1;
+
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+
+DELIMITER |;
+CREATE PROCEDURE test_sp()
+BEGIN
+   ignored:
+   LOOP
+      LEAVE ignored;
+   END LOOP;
+END|
+
+DELIMITER ;|
+DROP PROCEDURE test_sp;
+
+DELIMITER |;
+--error ER_UNKNOWN_SYSTEM_VARIABLE
+CREATE PROCEDURE test_sp()
+BEGIN
+  set @@ignored= 1;
+END|
+DELIMITER ;|
+
+DELIMITER |;
+--error ER_UNKNOWN_SYSTEM_VARIABLE
+CREATE PROCEDURE proc()
+BEGIN
+  SET IGNORED= a+b;
+END |
+DELIMITER ;|
+
+--echo #
+--echo # ALLOWING ALTER KEY syntax in ALTER TABLE
+--echo #
+
+CREATE TABLE t1 (a INT, KEY (a));
+ALTER TABLE t1 ALTER INDEX a IGNORED;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+CREATE TABLE t1 (a INT, KEY (a));
+ALTER TABLE t1 ALTER KEY a IGNORED;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+--echo #
+--echo #  MDEV-25078, part #2: allow IF EXISTS
+--echo #
+
+create table t1 (a int, b int, c int, key(a), key(b), key(c));
+alter table t1 alter key if exists no_such_key ignored;
+alter table t1 alter key if exists a ignored;
+show create table t1;
+alter table t1
+  alter key if exists no_such_key ignored,
+  alter key if exists c ignored ;
+show create table t1;
+alter table t1 
+  alter key if exists no_such_key not ignored, 
+  alter key if exists c not ignored ;
+show create table t1;
+drop table t1;
+
+