From c236f69d95321663c597a06f6e4e7ec440443ea5 Mon Sep 17 00:00:00 2001
From: Sergei Petrunia <psergey@askmonty.org>
Date: Tue, 16 Mar 2021 20:57:36 +0300
Subject: Rename IGNORED INDEX tests to match the SQL syntax

ignore_indexes -> ignored_index
ignore_indexes_innodb -> ignored_index_innodb
---
 mysql-test/main/ignored_index.result | 481 +++++++++++++++++++++++++++++++++++
 1 file changed, 481 insertions(+)
 create mode 100644 mysql-test/main/ignored_index.result

(limited to 'mysql-test/main/ignored_index.result')

diff --git a/mysql-test/main/ignored_index.result b/mysql-test/main/ignored_index.result
new file mode 100644
index 00000000000..733e44a3afa
--- /dev/null
+++ b/mysql-test/main/ignored_index.result
@@ -0,0 +1,481 @@
+#
+# MDEV-7317: Make an index ignorable to the optimizer
+#
+# Test of ALTER INDEX syntax.
+CREATE TABLE t1 ( a INT, KEY (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	Ignored
+t1	1	a	1	a	A	NULL	NULL	NULL	YES	BTREE			NO
+ALTER TABLE t1 ALTER INDEX a IGNORED;
+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	Ignored
+t1	1	a	1	a	A	NULL	NULL	NULL	YES	BTREE			YES
+ALTER TABLE t1 ALTER INDEX a NOT IGNORED;
+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	Ignored
+t1	1	a	1	a	A	NULL	NULL	NULL	YES	BTREE			NO
+DROP TABLE t1;
+# 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;
+Warnings:
+Note	1831	Duplicate index `b_not_ignorable`. This is deprecated and will be disallowed in a future release
+CREATE INDEX a_b_ignorable ON t1(a, b) IGNORED;
+SHOW INDEXES FROM t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
+t1	1	a_ignorable	1	a	A	NULL	NULL	NULL	YES	BTREE			YES
+t1	1	b_not_ignorable	1	a	A	NULL	NULL	NULL	YES	BTREE			NO
+t1	1	a_b_ignorable	1	a	A	NULL	NULL	NULL	YES	BTREE			YES
+t1	1	a_b_ignorable	2	b	A	NULL	NULL	NULL	YES	BTREE			YES
+DROP TABLE t1;
+# 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;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+test.t2	analyze	status	Engine-independent statistics collected
+test.t2	analyze	status	Table is already up to date
+EXPLAIN SELECT a FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a	5	NULL	5	Using index
+ALTER TABLE t1 ALTER INDEX a IGNORED;
+EXPLAIN SELECT a FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
+ALTER TABLE t1 ALTER INDEX a NOT IGNORED;
+EXPLAIN SELECT a FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a	5	NULL	5	Using index
+EXPLAIN SELECT a FROM t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	5	
+ALTER TABLE t2 ALTER INDEX a NOT IGNORED;
+EXPLAIN SELECT a FROM t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index	NULL	a	5	NULL	5	Using index
+DROP TABLE t1, t2;
+# 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;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
+t1	1	a	1	a	A	NULL	NULL	NULL	YES	BTREE			NO
+t1	1	b	1	b	A	NULL	NULL	NULL	YES	BTREE			YES
+ALTER TABLE t1 RENAME INDEX a TO a1;
+ALTER TABLE t1 RENAME INDEX b TO b1;
+SHOW INDEXES FROM t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
+t1	1	a1	1	a	A	NULL	NULL	NULL	YES	BTREE			NO
+t1	1	b1	1	b	A	NULL	NULL	NULL	YES	BTREE			YES
+ALTER TABLE t1 ALTER INDEX a1 IGNORED;
+ALTER TABLE t1 ALTER INDEX b1 NOT IGNORED;
+SHOW INDEXES FROM t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
+t1	1	a1	1	a	A	NULL	NULL	NULL	YES	BTREE			YES
+t1	1	b1	1	b	A	NULL	NULL	NULL	YES	BTREE			NO
+DROP TABLE t1;
+# 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;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL,
+  `c` int(11) DEFAULT NULL,
+  KEY `a` (`a`),
+  KEY `b` (`b`) IGNORED,
+  KEY `c` (`c`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+# Test that primary key indexes can't be made ignorable.
+CREATE TABLE t1 ( a INT, PRIMARY KEY (a) IGNORED );
+ERROR HY000: A primary key cannot be marked as IGNORE
+CREATE TABLE t1 ( a INT PRIMARY KEY IGNORED );
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'IGNORED )' at line 1
+CREATE TABLE t1 ( a INT KEY IGNORED );
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'IGNORED )' at line 1
+ALTER TABLE t1 ALTER INDEX PRIMARY IGNORED;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'PRIMARY IGNORED' at line 1
+CREATE TABLE t1(a INT NOT NULL);
+ALTER TABLE t1 ADD PRIMARY KEY (a) IGNORED;
+ERROR HY000: A primary key cannot be marked as IGNORE
+DROP TABLE t1;
+CREATE TABLE t1 (
+a INT, KEY (a),
+b INT, KEY (b) IGNORED
+);
+ALTER TABLE t1 RENAME INDEX no_such_index TO x;
+ERROR 42000: Key 'no_such_index' doesn't exist in table 't1'
+ALTER TABLE t1 ALTER INDEX no_such_index IGNORED;
+ERROR 42000: Key 'no_such_index' doesn't exist in table 't1'
+DROP TABLE t1;
+CREATE TABLE t1 (
+a INT, KEY (a),
+b INT, KEY (b) IGNORED
+);
+ALTER TABLE t1 RENAME INDEX no_such_index TO x;
+ERROR 42000: Key 'no_such_index' doesn't exist in table 't1'
+ALTER TABLE t1 ALTER INDEX no_such_index IGNORED;
+ERROR 42000: Key 'no_such_index' doesn't exist in table 't1'
+#
+# Repeated alter actions. Should work.
+#
+ALTER TABLE t1 ALTER INDEX a IGNORED, ALTER INDEX a NOT IGNORED;
+SHOW INDEXES FROM t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
+t1	1	a	1	a	A	NULL	NULL	NULL	YES	BTREE			NO
+t1	1	b	1	b	A	NULL	NULL	NULL	YES	BTREE			YES
+ALTER TABLE t1 ALTER INDEX a NOT IGNORED, ALTER INDEX b IGNORED;
+SHOW INDEXES FROM t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
+t1	1	a	1	a	A	NULL	NULL	NULL	YES	BTREE			NO
+t1	1	b	1	b	A	NULL	NULL	NULL	YES	BTREE			YES
+#
+# Various combinations of RENAME INDEX and ALTER INDEX ... IGNORED.
+#
+ALTER TABLE t1 RENAME INDEX a TO x, RENAME INDEX x TO a;
+ERROR 42000: Key 'x' doesn't exist in table 't1'
+ALTER TABLE t1 RENAME INDEX a TO x, ALTER INDEX x IGNORED;
+ERROR 42000: Key 'x' doesn't exist in table 't1'
+ALTER TABLE t1 RENAME INDEX a TO x, ALTER INDEX a NOT IGNORED;
+ERROR 42000: Key 'a' doesn't exist in table 't1'
+ALTER TABLE t1 ALTER INDEX a NOT IGNORED, RENAME INDEX a TO x;
+ERROR 42000: Key 'a' doesn't exist in table 't1'
+#
+# Various algorithms and their effects.
+#
+INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3);
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+ALTER TABLE t1 ALTER INDEX a IGNORED, ALGORITHM = COPY;
+affected rows: 3
+info: Records: 3  Duplicates: 0  Warnings: 0
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+SHOW INDEXES FROM t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
+t1	1	a	1	a	A	3	NULL	NULL	YES	BTREE			YES
+t1	1	b	1	b	A	3	NULL	NULL	YES	BTREE			YES
+ALTER TABLE t1 ALTER INDEX a NOT IGNORED, ALGORITHM = INPLACE;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	Table is already up to date
+SHOW INDEXES FROM t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
+t1	1	a	1	a	A	3	NULL	NULL	YES	BTREE			NO
+t1	1	b	1	b	A	3	NULL	NULL	YES	BTREE			YES
+ALTER TABLE t1 ALTER INDEX a IGNORED, ALGORITHM = DEFAULT;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	Table is already up to date
+SHOW INDEXES FROM t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
+t1	1	a	1	a	A	3	NULL	NULL	YES	BTREE			YES
+t1	1	b	1	b	A	3	NULL	NULL	YES	BTREE			YES
+ALTER TABLE t1 ALTER INDEX a NOT IGNORED;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	Table is already up to date
+SHOW INDEXES FROM t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
+t1	1	a	1	a	A	3	NULL	NULL	YES	BTREE			NO
+t1	1	b	1	b	A	3	NULL	NULL	YES	BTREE			YES
+ALTER TABLE t1 ADD INDEX ab(a, b), ALTER INDEX ab IGNORED;
+ERROR 42000: Key 'ab' doesn't exist in table 't1'
+DROP TABLE t1;
+#
+# The first NOT NULL UNIQUE index may of course be IGNORED if it is
+# not promoted to a primary key
+#
+CREATE TABLE t1 (
+a INT NOT NULL,
+b INT NOT NULL PRIMARY KEY,
+UNIQUE KEY (a) IGNORED
+);
+SHOW INDEXES FROM t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
+t1	0	PRIMARY	1	b	A	0	NULL	NULL		BTREE			NO
+t1	0	a	1	a	A	0	NULL	NULL		BTREE			YES
+DROP TABLE t1;
+# 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;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
+t1	0	a	1	a	A	0	NULL	NULL		BTREE			NO
+t1	0	b	1	b	A	0	NULL	NULL		BTREE			YES
+DROP TABLE t1;
+CREATE TABLE t1 ( a INT NOT NULL, UNIQUE KEY (a) IGNORED);
+ERROR HY000: A primary key cannot be marked as IGNORE
+CREATE TEMPORARY TABLE t1 ( a INT, KEY (a) IGNORED);
+INSERT INTO t1 VALUES (0), (1), (2), (3);
+SHOW INDEXES FROM t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
+t1	1	a	1	a	A	NULL	NULL	NULL	YES	BTREE			YES
+EXPLAIN SELECT a FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	
+ALTER TABLE t1 ALTER INDEX a NOT IGNORED;
+EXPLAIN SELECT a FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a	5	NULL	4	Using index
+DROP TABLE t1;
+#
+# IGNORED fulltext indexes.
+#
+CREATE TABLE t1 (a VARCHAR(200), b TEXT, FULLTEXT (a,b));
+INSERT INTO t1 VALUES('Some data', 'for full-text search');
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	Warning	Engine-independent statistics are not collected for column 'b'
+test.t1	analyze	status	OK
+EXPLAIN SELECT * FROM t1 WHERE MATCH(a, b) AGAINST ("collections");
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	fulltext	a	a	0		1	Using where
+ALTER TABLE t1 ALTER INDEX a IGNORED;
+EXPLAIN SELECT * FROM t1 WHERE MATCH(a, b) AGAINST ("collections");
+ERROR HY000: Can't find FULLTEXT index matching the column list
+DROP TABLE t1;
+#
+# IGNORED indexes on AUTO_INCREMENT columns.
+#
+CREATE TABLE t1 ( a INT AUTO_INCREMENT, KEY ( a ) );
+INSERT INTO t1 VALUES (), (), ();
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+EXPLAIN SELECT a FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a	4	NULL	3	Using index
+ALTER TABLE t1 ALTER INDEX a IGNORED;
+SHOW INDEXES FROM t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
+t1	1	a	1	a	A	3	NULL	NULL		BTREE			YES
+EXPLAIN SELECT a FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+DROP TABLE t1;
+#
+# IGNORED spatial indexes
+#
+CREATE TABLE t1 (
+fid INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
+g GEOMETRY NOT NULL,
+SPATIAL KEY key1(g)
+);
+EXPLAIN SELECT fid, AsText(g) FROM t1
+WHERE Within(g, GeomFromText('Polygon((140 140,160 140,160 160,140 160,140 140))'));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	key1	key1	34	NULL	8	Using where
+ALTER TABLE t1 ALTER INDEX key1 IGNORED;
+SHOW INDEXES FROM t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
+t1	0	PRIMARY	1	fid	A	150	NULL	NULL		BTREE			NO
+t1	1	key1	1	g	A	NULL	32	NULL		SPATIAL			YES
+EXPLAIN SELECT fid, AsText(g) FROM t1
+WHERE Within(g, GeomFromText('Polygon((140 140,160 140,160 160,140 160,140 140))'));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	150	Using where
+DROP TABLE t1;
+CREATE TABLE t1 ( a INT GENERATED ALWAYS AS (1), KEY key1(a));
+INSERT INTO t1 VALUES (),(),();
+SHOW INDEXES FROM t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
+t1	1	key1	1	a	A	NULL	NULL	NULL	YES	BTREE			NO
+EXPLAIN SELECT a FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	key1	5	NULL	3	Using index
+ALTER TABLE t1 ALTER INDEX key1 IGNORED;
+SHOW INDEXES FROM t1;
+Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Ignored
+t1	1	key1	1	a	A	NULL	NULL	NULL	YES	BTREE			YES
+EXPLAIN SELECT a FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	
+DROP TABLE t1;
+#
+# Partitioning on keys with an IGNORED index, IGNORED indexes over
+# partitioned tables.
+#
+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;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	Engine-independent statistics collected
+test.t1	analyze	status	OK
+EXPLAIN SELECT a FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	14	
+EXPLAIN SELECT b FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	b	2	NULL	14	Using index
+EXPLAIN SELECT * FROM t1 WHERE a = '04';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	8	Using where
+ALTER TABLE t1 ALTER INDEX a NOT IGNORED;
+EXPLAIN SELECT a FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a	2	NULL	14	Using index
+EXPLAIN SELECT * FROM t1 WHERE a = '04';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	a	a	2	const	2	Using where
+ALTER TABLE t1 ALTER INDEX b IGNORED;
+EXPLAIN SELECT b FROM t1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	14	
+DROP TABLE t1;
+#
+# Using FORCE INDEX for an IGNORED index
+#
+CREATE TABLE t1(a INT, key k1(a));
+INSERT INTO t1 VALUES (1),(2),(3);
+EXPLAIN SELECT * FROM t1 FORCE INDEX(k1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	k1	5	NULL	3	Using index
+ALTER TABLE t1 ALTER INDEX k1 IGNORED;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  KEY `k1` (`a`) IGNORED
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+EXPLAIN SELECT * FROM t1 FORCE INDEX(k1);
+ERROR 42000: Key 'k1' doesn't exist in table 't1'
+DROP TABLE t1;
+#
+# MDEV-25075: Ignorable index makes the resulting CREATE TABLE invalid
+#
+CREATE TABLE t1 (a INT, KEY (a));
+ALTER TABLE t1 ALTER INDEX a IGNORED;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  KEY `a` (`a`) IGNORED
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 ( a INT, KEY (a) IGNORED);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  KEY `a` (`a`) IGNORED
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+#
+# Tests to check usage of IGNORED keyword
+#
+CREATE TABLE IGNORED(a INT);
+DROP TABLE IGNORED;
+CREATE TABLE t1(a INT);
+SELECT * FROM t1 IGNORED;
+a
+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|
+DROP TABLE t1;
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+CREATE PROCEDURE test_sp()
+BEGIN
+ignored:
+LOOP
+LEAVE ignored;
+END LOOP;
+END|
+DROP PROCEDURE test_sp;
+CREATE PROCEDURE test_sp()
+BEGIN
+set @@ignored= 1;
+END|
+ERROR HY000: Unknown system variable 'ignored'
+CREATE PROCEDURE proc()
+BEGIN
+SET IGNORED= a+b;
+END |
+ERROR HY000: Unknown system variable 'IGNORED'
+#
+# ALLOWING ALTER KEY syntax in ALTER TABLE
+#
+CREATE TABLE t1 (a INT, KEY (a));
+ALTER TABLE t1 ALTER INDEX a IGNORED;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  KEY `a` (`a`) IGNORED
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
+CREATE TABLE t1 (a INT, KEY (a));
+ALTER TABLE t1 ALTER KEY a IGNORED;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  KEY `a` (`a`) IGNORED
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+DROP TABLE t1;
-- 
cgit v1.2.1