From 9ed36a8aa9977db14947a90eff0bc8890e8e3220 Mon Sep 17 00:00:00 2001 From: Sergei Petrunia 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/ignore_indexes.result | 481 --------------------------- mysql-test/main/ignore_indexes.test | 444 ------------------------- mysql-test/main/ignore_indexes_innodb.result | 7 - mysql-test/main/ignore_indexes_innodb.test | 16 - mysql-test/main/ignored_index.result | 481 +++++++++++++++++++++++++++ mysql-test/main/ignored_index.test | 444 +++++++++++++++++++++++++ mysql-test/main/ignored_index_innodb.result | 7 + mysql-test/main/ignored_index_innodb.test | 16 + 8 files changed, 948 insertions(+), 948 deletions(-) delete mode 100644 mysql-test/main/ignore_indexes.result delete mode 100644 mysql-test/main/ignore_indexes.test delete mode 100644 mysql-test/main/ignore_indexes_innodb.result delete mode 100644 mysql-test/main/ignore_indexes_innodb.test create mode 100644 mysql-test/main/ignored_index.result create mode 100644 mysql-test/main/ignored_index.test create mode 100644 mysql-test/main/ignored_index_innodb.result create mode 100644 mysql-test/main/ignored_index_innodb.test diff --git a/mysql-test/main/ignore_indexes.result b/mysql-test/main/ignore_indexes.result deleted file mode 100644 index 733e44a3afa..00000000000 --- a/mysql-test/main/ignore_indexes.result +++ /dev/null @@ -1,481 +0,0 @@ -# -# 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; diff --git a/mysql-test/main/ignore_indexes.test b/mysql-test/main/ignore_indexes.test deleted file mode 100644 index a1084f3eb9c..00000000000 --- a/mysql-test/main/ignore_indexes.test +++ /dev/null @@ -1,444 +0,0 @@ ---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; diff --git a/mysql-test/main/ignore_indexes_innodb.result b/mysql-test/main/ignore_indexes_innodb.result deleted file mode 100644 index cc7af31ef97..00000000000 --- a/mysql-test/main/ignore_indexes_innodb.result +++ /dev/null @@ -1,7 +0,0 @@ -create table t1 (a int, b int, key a1(a)) engine=innodb; -insert into t1 values (1,1),(2,2),(3,3); -set alter_algorithm='instant'; -alter table t1 alter index a1 ignored; -alter table t1 alter index a1 not ignored; -set alter_algorithm=default; -drop table t1; diff --git a/mysql-test/main/ignore_indexes_innodb.test b/mysql-test/main/ignore_indexes_innodb.test deleted file mode 100644 index c25cdc8d928..00000000000 --- a/mysql-test/main/ignore_indexes_innodb.test +++ /dev/null @@ -1,16 +0,0 @@ ---source include/have_innodb.inc - -# -# Check if marking index as [not] ignored is an instant operation with InnoDB -# -create table t1 (a int, b int, key a1(a)) engine=innodb; -insert into t1 values (1,1),(2,2),(3,3); - -set alter_algorithm='instant'; -alter table t1 alter index a1 ignored; - -alter table t1 alter index a1 not ignored; - -set alter_algorithm=default; -drop table t1; - 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; diff --git a/mysql-test/main/ignored_index.test b/mysql-test/main/ignored_index.test new file mode 100644 index 00000000000..a1084f3eb9c --- /dev/null +++ b/mysql-test/main/ignored_index.test @@ -0,0 +1,444 @@ +--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; diff --git a/mysql-test/main/ignored_index_innodb.result b/mysql-test/main/ignored_index_innodb.result new file mode 100644 index 00000000000..cc7af31ef97 --- /dev/null +++ b/mysql-test/main/ignored_index_innodb.result @@ -0,0 +1,7 @@ +create table t1 (a int, b int, key a1(a)) engine=innodb; +insert into t1 values (1,1),(2,2),(3,3); +set alter_algorithm='instant'; +alter table t1 alter index a1 ignored; +alter table t1 alter index a1 not ignored; +set alter_algorithm=default; +drop table t1; diff --git a/mysql-test/main/ignored_index_innodb.test b/mysql-test/main/ignored_index_innodb.test new file mode 100644 index 00000000000..c25cdc8d928 --- /dev/null +++ b/mysql-test/main/ignored_index_innodb.test @@ -0,0 +1,16 @@ +--source include/have_innodb.inc + +# +# Check if marking index as [not] ignored is an instant operation with InnoDB +# +create table t1 (a int, b int, key a1(a)) engine=innodb; +insert into t1 values (1,1),(2,2),(3,3); + +set alter_algorithm='instant'; +alter table t1 alter index a1 ignored; + +alter table t1 alter index a1 not ignored; + +set alter_algorithm=default; +drop table t1; + -- cgit v1.2.1