113 lines
2.9 KiB
Plaintext
113 lines
2.9 KiB
Plaintext
--source ./invisible_indexes_xengine.inc
|
|
|
|
--echo #
|
|
--echo # Tests that don't work on MyISAM ( native partitioning, indexes on
|
|
--echo # generated columns, etc.)
|
|
--echo #
|
|
|
|
#--echo #
|
|
#--echo # Partitioning on keys with an invisible index, invisible indexes over
|
|
#--echo # partitioned tables.
|
|
#--echo #
|
|
#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) INVISIBLE,
|
|
# 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 VISIBLE;
|
|
#EXPLAIN SELECT a FROM t1;
|
|
#EXPLAIN SELECT * FROM t1 WHERE a = '04';
|
|
#
|
|
#ALTER TABLE t1 ALTER INDEX b INVISIBLE;
|
|
#EXPLAIN SELECT b FROM t1;
|
|
#
|
|
#DROP TABLE t1;
|
|
|
|
#CREATE TABLE t1 ( a INT GENERATED ALWAYS AS (1), KEY (a) INVISIBLE );
|
|
#SHOW INDEXES FROM t1;
|
|
#EXPLAIN SELECT a FROM t1;
|
|
#DROP TABLE t1;
|
|
#
|
|
#
|
|
#--echo #
|
|
#--echo # Test that referential constraints implemented by the indexes are still
|
|
#--echo # enforced while the index is invisible.
|
|
#--echo #
|
|
#
|
|
#CREATE TABLE t1p ( a INT KEY );
|
|
#CREATE TABLE t1c ( t1p_a INT );
|
|
#ALTER TABLE t1c ADD CONSTRAINT FOREIGN KEY ( t1p_a ) REFERENCES t1p( a );
|
|
#ALTER TABLE t1c ALTER INDEX t1p_a INVISIBLE;
|
|
#
|
|
#--error ER_NO_REFERENCED_ROW_2
|
|
#INSERT INTO t1c VALUES ( 1 );
|
|
#SELECT * FROM t1c;
|
|
#
|
|
#DROP TABLE t1c, t1p;
|
|
|
|
--echo #
|
|
--echo # Bug#25837038: FEATURE REQUEST : USE INVISIBLE INDEXES SPECIFIC QUERY
|
|
--echo #
|
|
|
|
CREATE TABLE t1 ( a INT, KEY( a ) INVISIBLE );
|
|
|
|
INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
|
|
--replace_column 10 X 11 X
|
|
EXPLAIN SELECT a FROM t1;
|
|
|
|
--connect ( con1, localhost, root, , )
|
|
--connection con1
|
|
SELECT @@optimizer_switch;
|
|
--replace_column 10 X 11 X
|
|
EXPLAIN SELECT a FROM t1;
|
|
SET @@optimizer_switch='use_invisible_indexes=on';
|
|
--replace_column 10 X 11 X
|
|
EXPLAIN SELECT a FROM t1;
|
|
|
|
--connection default
|
|
SELECT @@optimizer_switch;
|
|
--replace_column 10 X 11 X
|
|
EXPLAIN SELECT a FROM t1;
|
|
|
|
--connection con1
|
|
SELECT @@optimizer_switch;
|
|
SET @@optimizer_switch='use_invisible_indexes=off';
|
|
--replace_column 10 X 11 X
|
|
EXPLAIN SELECT a FROM t1;
|
|
|
|
--disconnect con1
|
|
--connection default
|
|
--replace_column 10 X 11 X
|
|
EXPLAIN SELECT a FROM t1;
|
|
|
|
DROP TABLE t1;
|
|
|
|
--source suite/xengine/include/check_xengine_log_error.inc
|