Debian Codegraph @

mysql-test/suite/innodb/t/innodb_i_s_columns.test

raw
1 --echo #
2 --echo # BUG#30837086 : SELECT FROM I_S.INNODB_COLUMNS IS SLOW IN 8.0
3 --echo #
4
5 --echo # Table without partition and virual columns
6 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, d INT, c INT AS(a+b));
7 --disable_query_log
8 let $table_id = `SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'`;
9 eval SELECT NAME, POS, MTYPE, PRTYPE FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID=$table_id;
10 --enable_query_log
11 DROP TABLE t1;
12
13 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, c INT AS(a+b), d INT);
14 --disable_query_log
15 let $table_id = `SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'`;
16 eval SELECT NAME, POS, MTYPE, PRTYPE FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID=$table_id;
17 --enable_query_log
18 DROP TABLE t1;
19
20 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, c INT AS(a+b), b INT, d INT);
21 --disable_query_log
22 let $table_id = `SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'`;
23 eval SELECT NAME, POS, MTYPE, PRTYPE FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID=$table_id;
24 --enable_query_log
25 DROP TABLE t1;
26
27 CREATE TABLE t1 (c INT AS(a+b), a INT NOT NULL PRIMARY KEY, b INT, d INT);
28 --disable_query_log
29 let $table_id = `SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'`;
30 eval SELECT NAME, POS, MTYPE, PRTYPE FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID=$table_id;
31 --enable_query_log
32 DROP TABLE t1;
33
34 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, c INT AS(a+b), d INT, e INT AS(b+d) STORED,f INT AS(a+b+d));
35 --disable_query_log
36 let $table_id = `SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'`;
37 eval SELECT NAME, POS, MTYPE, PRTYPE FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID=$table_id;
38 --enable_query_log
39 DROP TABLE t1;
40
41 --echo # Table with partition and virtual column
42 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, d INT, c INT AS(a+b))
43 PARTITION BY HASH (a) PARTITIONS 3;
44 --disable_query_log
45 let $table_id = `SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1#p#p0'`;
46 eval SELECT NAME, POS, MTYPE, PRTYPE FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID=$table_id;
47 let $table_id = `SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1#p#p1'`;
48 eval SELECT NAME, POS, MTYPE, PRTYPE FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID=$table_id;
49 let $table_id = `SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1#p#p2'`;
50 eval SELECT NAME, POS, MTYPE, PRTYPE FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID=$table_id;
51 --enable_query_log
52 DROP TABLE t1;
53
54 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, c INT AS(a+b), d INT, e INT AS(b+d) STORED,f INT AS(a+b+d))
55 PARTITION BY HASH (a) PARTITIONS 3;
56 --disable_query_log
57 let $table_id = `SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1#p#p0'`;
58 eval SELECT NAME, POS, MTYPE, PRTYPE FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID=$table_id;
59 let $table_id = `SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1#p#p1'`;
60 eval SELECT NAME, POS, MTYPE, PRTYPE FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID=$table_id;
61 let $table_id = `SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1#p#p2'`;
62 eval SELECT NAME, POS, MTYPE, PRTYPE FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID=$table_id;
63 --enable_query_log
64 DROP TABLE t1;
65
66 --echo # Table with FTS Index
67 --error 1214
68 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, d VARCHAR(100), c INT AS(a+b), FULLTEXT idx (d))
69 PARTITION BY HASH (a) PARTITIONS 3;
70
71 CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY, b INT, d VARCHAR(100), c INT AS(a+b), FULLTEXT idx (d));
72 --disable_query_log
73 let $table_id = `SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'`;
74 eval SELECT NAME, POS, MTYPE, PRTYPE FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID=$table_id;
75 --enable_query_log
76 DROP TABLE t1;
77
78 --echo # Table with functional index
79 CREATE TABLE t1 (int_col INTEGER, string_col VARCHAR(255));
80 CREATE INDEX int_func_index ON t1 ((ABS(int_col)));
81 --disable_query_log
82 let $table_id = `SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'`;
83 eval SELECT NAME, POS, MTYPE, PRTYPE FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID=$table_id;
84 --enable_query_log
85 CREATE INDEX string_func_index ON t1 ((SUBSTRING(string_col, 1, 2)));
86 --disable_query_log
87 let $table_id = `SELECT TABLE_ID FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1'`;
88 eval SELECT NAME, POS, MTYPE, PRTYPE FROM INFORMATION_SCHEMA.INNODB_COLUMNS WHERE TABLE_ID=$table_id;
89 --enable_query_log
90 DROP TABLE t1;