Debian Codegraph @

mysql-test/suite/funcs_1/r/is_statistics.result

raw
1 SHOW TABLES FROM information_schema LIKE 'STATISTICS';
2 Tables_in_information_schema (STATISTICS)
3 STATISTICS
4 #######################################################################
5 # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
6 #######################################################################
7 DROP VIEW IF EXISTS test.v1;
8 DROP PROCEDURE IF EXISTS test.p1;
9 DROP FUNCTION IF EXISTS test.f1;
10 CREATE VIEW test.v1 AS SELECT * FROM information_schema.STATISTICS;
11 CREATE PROCEDURE test.p1() SELECT * FROM information_schema.STATISTICS;
12 CREATE FUNCTION test.f1() returns BIGINT
13 BEGIN
14 DECLARE counter BIGINT DEFAULT NULL;
15 SELECT COUNT(*) INTO counter FROM information_schema.STATISTICS;
16 RETURN counter;
17 END//
18 # Attention: The printing of the next result sets is disabled.
19 SELECT * FROM information_schema.STATISTICS;
20 SELECT * FROM test.v1;
21 CALL test.p1;
22 SELECT test.f1();
23 DROP VIEW test.v1;
24 DROP PROCEDURE test.p1;
25 DROP FUNCTION test.f1;
26 #########################################################################
27 # Testcase 3.2.14.1: INFORMATION_SCHEMA.STATISTICS layout
28 #########################################################################
29 DESCRIBE information_schema.STATISTICS;
30 Field Type Null Key Default Extra
31 TABLE_CATALOG varchar(512) NO NULL
32 TABLE_SCHEMA varchar(64) NO NULL
33 TABLE_NAME varchar(64) NO NULL
34 NON_UNIQUE bigint(1) NO NULL
35 INDEX_SCHEMA varchar(64) NO NULL
36 INDEX_NAME varchar(64) NO NULL
37 SEQ_IN_INDEX int(2) unsigned NO NULL
38 COLUMN_NAME varchar(64) NO NULL
39 COLLATION varchar(1) YES NULL
40 CARDINALITY bigint(21) YES NULL
41 SUB_PART bigint(3) YES NULL
42 PACKED varchar(10) YES NULL
43 NULLABLE varchar(3) NO NULL
44 INDEX_TYPE varchar(16) NO NULL
45 COMMENT varchar(16) NO NULL
46 INDEX_COMMENT varchar(1024) NO NULL
47 IGNORED varchar(3) NO NULL
48 SHOW CREATE TABLE information_schema.STATISTICS;
49 Table Create Table
50 STATISTICS CREATE TEMPORARY TABLE `STATISTICS` (
51 `TABLE_CATALOG` varchar(512) NOT NULL,
52 `TABLE_SCHEMA` varchar(64) NOT NULL,
53 `TABLE_NAME` varchar(64) NOT NULL,
54 `NON_UNIQUE` bigint(1) NOT NULL,
55 `INDEX_SCHEMA` varchar(64) NOT NULL,
56 `INDEX_NAME` varchar(64) NOT NULL,
57 `SEQ_IN_INDEX` int(2) unsigned NOT NULL,
58 `COLUMN_NAME` varchar(64) NOT NULL,
59 `COLLATION` varchar(1),
60 `CARDINALITY` bigint(21),
61 `SUB_PART` bigint(3),
62 `PACKED` varchar(10),
63 `NULLABLE` varchar(3) NOT NULL,
64 `INDEX_TYPE` varchar(16) NOT NULL,
65 `COMMENT` varchar(16) NOT NULL,
66 `INDEX_COMMENT` varchar(1024) NOT NULL,
67 `IGNORED` varchar(3) NOT NULL
68 ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
69 SHOW COLUMNS FROM information_schema.STATISTICS;
70 Field Type Null Key Default Extra
71 TABLE_CATALOG varchar(512) NO NULL
72 TABLE_SCHEMA varchar(64) NO NULL
73 TABLE_NAME varchar(64) NO NULL
74 NON_UNIQUE bigint(1) NO NULL
75 INDEX_SCHEMA varchar(64) NO NULL
76 INDEX_NAME varchar(64) NO NULL
77 SEQ_IN_INDEX int(2) unsigned NO NULL
78 COLUMN_NAME varchar(64) NO NULL
79 COLLATION varchar(1) YES NULL
80 CARDINALITY bigint(21) YES NULL
81 SUB_PART bigint(3) YES NULL
82 PACKED varchar(10) YES NULL
83 NULLABLE varchar(3) NO NULL
84 INDEX_TYPE varchar(16) NO NULL
85 COMMENT varchar(16) NO NULL
86 INDEX_COMMENT varchar(1024) NO NULL
87 IGNORED varchar(3) NO NULL
88 SELECT table_catalog, table_schema, table_name, index_schema, index_name
89 FROM information_schema.statistics WHERE table_catalog IS NOT NULL
90 ORDER BY table_schema, table_name, index_schema, index_name;
91 table_catalog table_schema table_name index_schema index_name
92 def mysql columns_priv mysql PRIMARY
93 def mysql columns_priv mysql PRIMARY
94 def mysql columns_priv mysql PRIMARY
95 def mysql columns_priv mysql PRIMARY
96 def mysql columns_priv mysql PRIMARY
97 def mysql column_stats mysql PRIMARY
98 def mysql column_stats mysql PRIMARY
99 def mysql column_stats mysql PRIMARY
100 def mysql db mysql PRIMARY
101 def mysql db mysql PRIMARY
102 def mysql db mysql PRIMARY
103 def mysql db mysql User
104 def mysql event mysql PRIMARY
105 def mysql event mysql PRIMARY
106 def mysql func mysql PRIMARY
107 def mysql global_priv mysql PRIMARY
108 def mysql global_priv mysql PRIMARY
109 def mysql gtid_slave_pos mysql PRIMARY
110 def mysql gtid_slave_pos mysql PRIMARY
111 def mysql help_category mysql name
112 def mysql help_category mysql PRIMARY
113 def mysql help_keyword mysql name
114 def mysql help_keyword mysql PRIMARY
115 def mysql help_relation mysql help_topic_id
116 def mysql help_relation mysql PRIMARY
117 def mysql help_relation mysql PRIMARY
118 def mysql help_topic mysql name
119 def mysql help_topic mysql PRIMARY
120 def mysql index_stats mysql PRIMARY
121 def mysql index_stats mysql PRIMARY
122 def mysql index_stats mysql PRIMARY
123 def mysql index_stats mysql PRIMARY
124 def mysql plugin mysql PRIMARY
125 def mysql proc mysql PRIMARY
126 def mysql proc mysql PRIMARY
127 def mysql proc mysql PRIMARY
128 def mysql procs_priv mysql Grantor
129 def mysql procs_priv mysql PRIMARY
130 def mysql procs_priv mysql PRIMARY
131 def mysql procs_priv mysql PRIMARY
132 def mysql procs_priv mysql PRIMARY
133 def mysql procs_priv mysql PRIMARY
134 def mysql proxies_priv mysql Grantor
135 def mysql proxies_priv mysql PRIMARY
136 def mysql proxies_priv mysql PRIMARY
137 def mysql proxies_priv mysql PRIMARY
138 def mysql proxies_priv mysql PRIMARY
139 def mysql roles_mapping mysql Host
140 def mysql roles_mapping mysql Host
141 def mysql roles_mapping mysql Host
142 def mysql servers mysql PRIMARY
143 def mysql tables_priv mysql Grantor
144 def mysql tables_priv mysql PRIMARY
145 def mysql tables_priv mysql PRIMARY
146 def mysql tables_priv mysql PRIMARY
147 def mysql tables_priv mysql PRIMARY
148 def mysql table_stats mysql PRIMARY
149 def mysql table_stats mysql PRIMARY
150 def mysql time_zone mysql PRIMARY
151 def mysql time_zone_leap_second mysql PRIMARY
152 def mysql time_zone_name mysql PRIMARY
153 def mysql time_zone_transition mysql PRIMARY
154 def mysql time_zone_transition mysql PRIMARY
155 def mysql time_zone_transition_type mysql PRIMARY
156 def mysql time_zone_transition_type mysql PRIMARY
157 def sys sys_config sys PRIMARY
158 Warnings:
159 Warning 1286 Unknown storage engine 'InnoDB'
160 Warning 1286 Unknown storage engine 'InnoDB'
161 Warning 1286 Unknown storage engine 'InnoDB'
162 ####################################################################################
163 # Testcase 3.2.14.2 + 3.2.14.3: INFORMATION_SCHEMA.STATISTICS accessible information
164 ####################################################################################
165 DROP DATABASE IF EXISTS db_datadict;
166 DROP DATABASE IF EXISTS db_datadict_2;
167 CREATE DATABASE db_datadict;
168 CREATE DATABASE db_datadict_2;
169 DROP USER 'testuser1'@'localhost';
170 CREATE USER 'testuser1'@'localhost';
171 DROP USER 'testuser2'@'localhost';
172 CREATE USER 'testuser2'@'localhost';
173 GRANT ALL ON test.* TO 'testuser1'@'localhost';
174 GRANT ALL ON test.* TO 'testuser2'@'localhost';
175 CREATE TABLE db_datadict.t1
176 (f1 INT NOT NULL, PRIMARY KEY(f1), f2 INT, INDEX f2_ind(f2))
177 ENGINE = <engine_type>;
178 CREATE TABLE db_datadict.t2
179 (f1 INT NOT NULL, PRIMARY KEY(f1), f2 INT, INDEX f2_ind(f2))
180 ENGINE = <engine_type>;
181 CREATE TABLE db_datadict_2.t3
182 (f1 INT NOT NULL, f2 INT, f5 DATE,
183 PRIMARY KEY(f1), INDEX f2f1_ind(f2,f1), UNIQUE(f5))
184 ENGINE = MEMORY;
185 CREATE TABLE db_datadict_2.t4
186 (f1 INT NOT NULL, PRIMARY KEY(f1), f2 INT, INDEX f2_ind(f2))
187 ENGINE = MEMORY;
188 SELECT * FROM information_schema.statistics
189 WHERE table_schema LIKE 'db_datadict%'
190 ORDER BY table_schema,table_name,index_name,seq_in_index,column_name;
191 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT IGNORED
192 def db_datadict t1 1 db_datadict f2_ind 1 f2 NULL 0 NULL NULL YES HASH NO
193 def db_datadict t1 0 db_datadict PRIMARY 1 f1 NULL 0 NULL NULL HASH NO
194 def db_datadict t2 1 db_datadict f2_ind 1 f2 NULL 0 NULL NULL YES HASH NO
195 def db_datadict t2 0 db_datadict PRIMARY 1 f1 NULL 0 NULL NULL HASH NO
196 def db_datadict_2 t3 1 db_datadict_2 f2f1_ind 1 f2 NULL NULL NULL NULL YES HASH NO
197 def db_datadict_2 t3 1 db_datadict_2 f2f1_ind 2 f1 NULL 0 NULL NULL HASH NO
198 def db_datadict_2 t3 0 db_datadict_2 f5 1 f5 NULL 0 NULL NULL YES HASH NO
199 def db_datadict_2 t3 0 db_datadict_2 PRIMARY 1 f1 NULL 0 NULL NULL HASH NO
200 def db_datadict_2 t4 1 db_datadict_2 f2_ind 1 f2 NULL 0 NULL NULL YES HASH NO
201 def db_datadict_2 t4 0 db_datadict_2 PRIMARY 1 f1 NULL 0 NULL NULL HASH NO
202 SHOW GRANTS FOR 'testuser1'@'localhost';
203 Grants for testuser1@localhost
204 GRANT USAGE ON *.* TO `testuser1`@`localhost`
205 GRANT ALL PRIVILEGES ON `test`.* TO `testuser1`@`localhost`
206 SHOW GRANTS FOR 'testuser2'@'localhost';
207 Grants for testuser2@localhost
208 GRANT USAGE ON *.* TO `testuser2`@`localhost`
209 GRANT ALL PRIVILEGES ON `test`.* TO `testuser2`@`localhost`
210 connect testuser1, localhost, testuser1, , test;
211 SELECT * FROM information_schema.statistics
212 WHERE table_schema LIKE 'db_datadict%'
213 ORDER BY table_schema,table_name,index_name,seq_in_index,column_name;
214 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT IGNORED
215 SHOW GRANTS FOR 'testuser1'@'localhost';
216 Grants for testuser1@localhost
217 GRANT USAGE ON *.* TO `testuser1`@`localhost`
218 GRANT ALL PRIVILEGES ON `test`.* TO `testuser1`@`localhost`
219 SHOW GRANTS FOR 'testuser2'@'localhost';
220 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'mysql'
221 connect testuser2, localhost, testuser2, , test;
222 SELECT * FROM information_schema.statistics
223 WHERE table_schema LIKE 'db_datadict%'
224 ORDER BY table_schema,table_name,index_name,seq_in_index,column_name;
225 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT IGNORED
226 SHOW GRANTS FOR 'testuser1'@'localhost';
227 ERROR 42000: Access denied for user 'testuser2'@'localhost' to database 'mysql'
228 SHOW GRANTS FOR 'testuser2'@'localhost';
229 Grants for testuser2@localhost
230 GRANT USAGE ON *.* TO `testuser2`@`localhost`
231 GRANT ALL PRIVILEGES ON `test`.* TO `testuser2`@`localhost`
232 connection default;
233 GRANT SELECT ON db_datadict.t1 TO 'testuser1'@'localhost' WITH GRANT OPTION;
234 GRANT SELECT(f1,f5) ON db_datadict_2.t3 TO 'testuser1'@'localhost';
235 SELECT * FROM information_schema.statistics
236 WHERE table_schema LIKE 'db_datadict%'
237 ORDER BY table_schema,table_name,index_name,seq_in_index,column_name;
238 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT IGNORED
239 def db_datadict t1 1 db_datadict f2_ind 1 f2 NULL 0 NULL NULL YES HASH NO
240 def db_datadict t1 0 db_datadict PRIMARY 1 f1 NULL 0 NULL NULL HASH NO
241 def db_datadict t2 1 db_datadict f2_ind 1 f2 NULL 0 NULL NULL YES HASH NO
242 def db_datadict t2 0 db_datadict PRIMARY 1 f1 NULL 0 NULL NULL HASH NO
243 def db_datadict_2 t3 1 db_datadict_2 f2f1_ind 1 f2 NULL NULL NULL NULL YES HASH NO
244 def db_datadict_2 t3 1 db_datadict_2 f2f1_ind 2 f1 NULL 0 NULL NULL HASH NO
245 def db_datadict_2 t3 0 db_datadict_2 f5 1 f5 NULL 0 NULL NULL YES HASH NO
246 def db_datadict_2 t3 0 db_datadict_2 PRIMARY 1 f1 NULL 0 NULL NULL HASH NO
247 def db_datadict_2 t4 1 db_datadict_2 f2_ind 1 f2 NULL 0 NULL NULL YES HASH NO
248 def db_datadict_2 t4 0 db_datadict_2 PRIMARY 1 f1 NULL 0 NULL NULL HASH NO
249 SHOW GRANTS FOR 'testuser1'@'localhost';
250 Grants for testuser1@localhost
251 GRANT USAGE ON *.* TO `testuser1`@`localhost`
252 GRANT ALL PRIVILEGES ON `test`.* TO `testuser1`@`localhost`
253 GRANT SELECT (`f5`, `f1`) ON `db_datadict_2`.`t3` TO `testuser1`@`localhost`
254 GRANT SELECT ON `db_datadict`.`t1` TO `testuser1`@`localhost` WITH GRANT OPTION
255 SHOW GRANTS FOR 'testuser2'@'localhost';
256 Grants for testuser2@localhost
257 GRANT USAGE ON *.* TO `testuser2`@`localhost`
258 GRANT ALL PRIVILEGES ON `test`.* TO `testuser2`@`localhost`
259 connection testuser1;
260 SELECT * FROM information_schema.statistics
261 WHERE table_schema LIKE 'db_datadict%'
262 ORDER BY table_schema,table_name,index_name,seq_in_index,column_name;
263 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT IGNORED
264 def db_datadict t1 1 db_datadict f2_ind 1 f2 NULL 0 NULL NULL YES HASH NO
265 def db_datadict t1 0 db_datadict PRIMARY 1 f1 NULL 0 NULL NULL HASH NO
266 def db_datadict_2 t3 0 db_datadict_2 f5 1 f5 NULL 0 NULL NULL YES HASH NO
267 def db_datadict_2 t3 0 db_datadict_2 PRIMARY 1 f1 NULL 0 NULL NULL HASH NO
268 SHOW GRANTS FOR 'testuser1'@'localhost';
269 Grants for testuser1@localhost
270 GRANT USAGE ON *.* TO `testuser1`@`localhost`
271 GRANT ALL PRIVILEGES ON `test`.* TO `testuser1`@`localhost`
272 GRANT SELECT (`f5`, `f1`) ON `db_datadict_2`.`t3` TO `testuser1`@`localhost`
273 GRANT SELECT ON `db_datadict`.`t1` TO `testuser1`@`localhost` WITH GRANT OPTION
274 SHOW GRANTS FOR 'testuser2'@'localhost';
275 ERROR 42000: Access denied for user 'testuser1'@'localhost' to database 'mysql'
276 connection testuser2;
277 SELECT * FROM information_schema.statistics
278 WHERE table_schema LIKE 'db_datadict%'
279 ORDER BY table_schema,table_name,index_name,seq_in_index,column_name;
280 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT IGNORED
281 SHOW GRANTS FOR 'testuser1'@'localhost';
282 ERROR 42000: Access denied for user 'testuser2'@'localhost' to database 'mysql'
283 SHOW GRANTS FOR 'testuser2'@'localhost';
284 Grants for testuser2@localhost
285 GRANT USAGE ON *.* TO `testuser2`@`localhost`
286 GRANT ALL PRIVILEGES ON `test`.* TO `testuser2`@`localhost`
287 connection default;
288 REVOKE SELECT,GRANT OPTION ON db_datadict.t1 FROM 'testuser1'@'localhost';
289 SHOW GRANTS FOR 'testuser1'@'localhost';
290 Grants for testuser1@localhost
291 GRANT USAGE ON *.* TO `testuser1`@`localhost`
292 GRANT ALL PRIVILEGES ON `test`.* TO `testuser1`@`localhost`
293 GRANT SELECT (`f5`, `f1`) ON `db_datadict_2`.`t3` TO `testuser1`@`localhost`
294 connection testuser1;
295 SELECT * FROM information_schema.statistics
296 WHERE table_schema LIKE 'db_datadict%'
297 ORDER BY table_schema,table_name,index_name,seq_in_index,column_name;
298 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT IGNORED
299 def db_datadict_2 t3 0 db_datadict_2 f5 1 f5 NULL 0 NULL NULL YES HASH NO
300 def db_datadict_2 t3 0 db_datadict_2 PRIMARY 1 f1 NULL 0 NULL NULL HASH NO
301 SHOW GRANTS FOR 'testuser1'@'localhost';
302 Grants for testuser1@localhost
303 GRANT USAGE ON *.* TO `testuser1`@`localhost`
304 GRANT ALL PRIVILEGES ON `test`.* TO `testuser1`@`localhost`
305 GRANT SELECT (`f5`, `f1`) ON `db_datadict_2`.`t3` TO `testuser1`@`localhost`
306 connection default;
307 disconnect testuser1;
308 disconnect testuser2;
309 DROP USER 'testuser1'@'localhost';
310 DROP USER 'testuser2'@'localhost';
311 DROP DATABASE db_datadict;
312 DROP DATABASE db_datadict_2;
313 #########################################################################
314 # 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.STATISTICS modifications
315 #########################################################################
316 DROP TABLE IF EXISTS test.t1_my_table;
317 DROP DATABASE IF EXISTS db_datadict;
318 CREATE DATABASE db_datadict;
319 CREATE TABLE test.t1_1 (f1 BIGINT,
320 f2 TEXT, f2x TEXT, f3 CHAR(10), f3x CHAR(10), f4 BIGINT, f4x BIGINT,
321 f5 POINT, f5x POINT NOT NULL)
322 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci
323 ENGINE = <other_engine_type>;
324 CREATE TABLE test.t1_2 (f1 BIGINT, f2 BIGINT)
325 ENGINE = <engine_type>;
326 SELECT table_name FROM information_schema.statistics
327 WHERE table_name LIKE 't1_%';
328 table_name
329 ALTER TABLE test.t1_1 ADD PRIMARY KEY (f1,f3);
330 SELECT * FROM information_schema.statistics
331 WHERE table_name LIKE 't1_%'
332 ORDER BY table_schema,table_name,index_name,seq_in_index,column_name;
333 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT IGNORED
334 def test t1_1 0 test PRIMARY 1 f1 A NULL NULL NULL BTREE NO
335 def test t1_1 0 test PRIMARY 2 f3 A 0 NULL NULL BTREE NO
336 ALTER TABLE test.t1_1 DROP PRIMARY KEY;
337 SELECT table_name FROM information_schema.statistics
338 WHERE table_name LIKE 't1_%';
339 table_name
340 ALTER TABLE test.t1_1 ADD PRIMARY KEY (f1);
341 SELECT * FROM information_schema.statistics
342 WHERE table_name LIKE 't1_%';
343 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT IGNORED
344 def test t1_1 0 test PRIMARY 1 f1 A 0 NULL NULL BTREE NO
345 ALTER TABLE test.t1_1 ADD INDEX (f4);
346 CREATE INDEX f3_f1 ON test.t1_1 (f3,f1);
347 CREATE UNIQUE INDEX f4x_uni ON test.t1_1 (f4x);
348 CREATE INDEX f2_hash USING HASH ON test.t1_2 (f2);
349 CREATE INDEX f1_idx ON test.t1_2 (f1) COMMENT = 'COMMENT';
350 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 '= 'COMMENT'' at line 1
351 CREATE INDEX not_null ON test.t1_1 (f3x);
352 CREATE INDEX f2_prefix ON test.t1_1 (f2(20));
353 SELECT * FROM information_schema.statistics
354 WHERE table_name LIKE 't1_%' AND index_name <> 'PRIMARY'
355 ORDER BY table_schema,table_name,index_name,seq_in_index,column_name;
356 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME NON_UNIQUE INDEX_SCHEMA INDEX_NAME SEQ_IN_INDEX COLUMN_NAME COLLATION CARDINALITY SUB_PART PACKED NULLABLE INDEX_TYPE COMMENT INDEX_COMMENT IGNORED
357 def test t1_1 1 test f2_prefix 1 f2 A NULL 20 NULL YES BTREE NO
358 def test t1_1 1 test f3_f1 1 f3 A NULL NULL NULL BTREE NO
359 def test t1_1 1 test f3_f1 2 f1 A NULL NULL NULL BTREE NO
360 def test t1_1 1 test f4 1 f4 A NULL NULL NULL YES BTREE NO
361 def test t1_1 0 test f4x_uni 1 f4x A NULL NULL NULL YES BTREE NO
362 def test t1_1 1 test not_null 1 f3x A NULL NULL NULL YES BTREE NO
363 def test t1_2 1 test f2_hash 1 f2 NULL 0 NULL NULL YES HASH NO
364 DROP TABLE test.t1_2;
365 SELECT DISTINCT table_name FROM information_schema.statistics
366 WHERE table_name = 't1_1';
367 table_name
368 t1_1
369 RENAME TABLE test.t1_1 TO test.t1_1x;
370 SELECT DISTINCT table_name FROM information_schema.statistics
371 WHERE table_name = 't1_1x';
372 table_name
373 t1_1x
374 SELECT DISTINCT table_schema,table_name FROM information_schema.statistics
375 WHERE table_name LIKE 't1_1%';
376 table_schema table_name
377 test t1_1x
378 RENAME TABLE test.t1_1x TO db_datadict.t1_1x;
379 SELECT DISTINCT table_schema,table_name FROM information_schema.statistics
380 WHERE table_name LIKE 't1_1%';
381 table_schema table_name
382 db_datadict t1_1x
383 SELECT DISTINCT table_name FROM information_schema.statistics
384 WHERE table_name = 't1_1x';
385 table_name
386 t1_1x
387 DROP TABLE db_datadict.t1_1x;
388 SELECT DISTINCT table_name FROM information_schema.statistics
389 WHERE table_name = 't1_1x';
390 table_name
391 CREATE TEMPORARY TABLE test.t1_1x (PRIMARY KEY(f1,f2))
392 ENGINE = <engine_type>
393 AS SELECT 1 AS f1, 2 AS f2;
394 SELECT * FROM information_schema.statistics
395 WHERE table_name = 't1_1x';
396 DROP TEMPORARY TABLE test.t1_1x;
397 CREATE TABLE db_datadict.t1_1x (PRIMARY KEY(f1))
398 ENGINE = <engine_type>
399 AS SELECT 1 AS f1, 2 AS f2;
400 SELECT table_name FROM information_schema.statistics
401 WHERE table_name = 't1_1x';
402 table_name
403 t1_1x
404 DROP DATABASE db_datadict;
405 SELECT table_name FROM information_schema.statistics
406 WHERE table_name = 't1_1x';
407 table_name
408 ########################################################################
409 # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
410 # DDL on INFORMATION_SCHEMA tables are not supported
411 ########################################################################
412 DROP DATABASE IF EXISTS db_datadict;
413 CREATE DATABASE db_datadict;
414 CREATE TABLE db_datadict.t1 (f1 BIGINT)
415 ENGINE = <engine_type>;
416 INSERT INTO information_schema.statistics
417 SELECT * FROM information_schema.statistics;
418 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
419 UPDATE information_schema.statistics SET table_schema = 'test'
420 WHERE table_name = 't1';
421 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
422 DELETE FROM information_schema.statistics WHERE table_name = 't1';
423 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
424 TRUNCATE information_schema.statistics;
425 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
426 CREATE INDEX my_idx_on_statistics
427 ON information_schema.statistics(table_schema);
428 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
429 ALTER TABLE information_schema.statistics DROP PRIMARY KEY;
430 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
431 ALTER TABLE information_schema.statistics ADD f1 INT;
432 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
433 DROP TABLE information_schema.statistics;
434 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
435 ALTER TABLE information_schema.statistics RENAME db_datadict.statistics;
436 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
437 ALTER TABLE information_schema.statistics RENAME information_schema.xstatistics;
438 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
439 DROP DATABASE db_datadict;