Debian Codegraph @

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

raw
1 #
2 # Check access to I_S.x_extensions tables
3 #
4 CREATE SCHEMA db_datadict;
5 CREATE USER 'testuser1'@'localhost';
6 CREATE USER 'testuser2'@'localhost';
7 CREATE TABLE db_datadict.t1
8 (f1 CHAR(10), f2 TEXT, f3 DATE, f4 INT AUTO_INCREMENT,
9 UNIQUE INDEX MUL_IDX(f1,f3), PRIMARY KEY (f4));
10 CREATE VIEW db_datadict.v1 AS SELECT 1 AS f1, 1 AS f2;
11 GRANT SELECT(f1, f2) ON db_datadict.t1 TO 'testuser1'@'localhost';
12 GRANT SELECT(f2) ON db_datadict.v1 TO 'testuser1'@'localhost';
13 CREATE TABLE db_datadict.t2
14 (f1 CHAR(10), f2 TEXT, f3 DATE, f4 INT, PRIMARY KEY (f1,f4));
15 GRANT INSERT(f1, f2) ON db_datadict.t2 TO 'testuser2'@'localhost';
16 # Checking tables_extensions as root
17 SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE, VERSION, ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT, '<create time>' AS CREATE_TIME, UPDATE_TIME, CHECK_TIME, TABLE_COLLATION, CHECKSUM, CREATE_OPTIONS, TABLE_COMMENT FROM information_schema.tables WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name;
18 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT
19 def db_datadict t1 BASE TABLE InnoDB 10 Dynamic 0 0 16384 0 16384 0 1 <create time> NULL NULL utf8mb4_0900_ai_ci NULL
20 def db_datadict t2 BASE TABLE InnoDB 10 Dynamic 0 0 16384 0 0 0 NULL <create time> NULL NULL utf8mb4_0900_ai_ci NULL
21 def db_datadict v1 VIEW NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL <create time> NULL NULL NULL NULL NULL VIEW
22 SELECT * FROM information_schema.tables_extensions WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name;
23 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME ENGINE_ATTRIBUTE SECONDARY_ENGINE_ATTRIBUTE
24 def db_datadict t1 NULL NULL
25 def db_datadict t2 NULL NULL
26 def db_datadict v1 NULL NULL
27 SELECT (SELECT count(*) FROM information_schema.tables WHERE table_schema = 'db_datadict') AS TABLES_ROWS, (SELECT COUNT(*) FROM information_schema.tables_extensions WHERE table_schema = 'db_datadict') AS TABLES_EXT_ROWS;
28 TABLES_ROWS TABLES_EXT_ROWS
29 3 3
30 # Checking columns_extensions as root
31 SELECT * FROM information_schema.columns WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name, column_name;
32 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT GENERATION_EXPRESSION SRS_ID
33 def db_datadict t1 f1 1 NULL YES char 10 40 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci char(10) MUL select,insert,update,references NULL
34 def db_datadict t1 f2 2 NULL YES text 65535 65535 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci text select,insert,update,references NULL
35 def db_datadict t1 f3 3 NULL YES date NULL NULL NULL NULL NULL NULL NULL date select,insert,update,references NULL
36 def db_datadict t1 f4 4 NULL NO int NULL NULL 10 0 NULL NULL NULL int PRI auto_increment select,insert,update,references NULL
37 def db_datadict t2 f1 1 NULL NO char 10 40 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci char(10) PRI select,insert,update,references NULL
38 def db_datadict t2 f2 2 NULL YES text 65535 65535 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci text select,insert,update,references NULL
39 def db_datadict t2 f3 3 NULL YES date NULL NULL NULL NULL NULL NULL NULL date select,insert,update,references NULL
40 def db_datadict t2 f4 4 NULL NO int NULL NULL 10 0 NULL NULL NULL int PRI select,insert,update,references NULL
41 def db_datadict v1 f1 1 0 NO int NULL NULL 10 0 NULL NULL NULL int select,insert,update,references NULL
42 def db_datadict v1 f2 2 0 NO int NULL NULL 10 0 NULL NULL NULL int select,insert,update,references NULL
43 SELECT * FROM information_schema.columns_extensions WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name, column_name;
44 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ENGINE_ATTRIBUTE SECONDARY_ENGINE_ATTRIBUTE
45 def db_datadict t1 f1 NULL NULL
46 def db_datadict t1 f2 NULL NULL
47 def db_datadict t1 f3 NULL NULL
48 def db_datadict t1 f4 NULL NULL
49 def db_datadict t2 f1 NULL NULL
50 def db_datadict t2 f2 NULL NULL
51 def db_datadict t2 f3 NULL NULL
52 def db_datadict t2 f4 NULL NULL
53 def db_datadict v1 f1 NULL NULL
54 def db_datadict v1 f2 NULL NULL
55 SELECT (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = 'db_datadict') AS COLUMNS_ROWS, (SELECT COUNT(*) FROM information_schema.columns_extensions WHERE table_schema = 'db_datadict') AS COLUMNS_EXT_ROWS;
56 COLUMNS_ROWS COLUMNS_EXT_ROWS
57 10 10
58 # Checking table_constraints_extensions as root
59 SELECT * FROM information_schema.table_constraints WHERE constraint_schema = 'db_datadict' ORDER BY constraint_schema, table_name, constraint_name;
60 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE ENFORCED
61 def db_datadict MUL_IDX db_datadict t1 UNIQUE YES
62 def db_datadict PRIMARY db_datadict t1 PRIMARY KEY YES
63 def db_datadict PRIMARY db_datadict t2 PRIMARY KEY YES
64 SELECT * FROM information_schema.table_constraints_extensions WHERE constraint_schema = 'db_datadict' ORDER BY constraint_schema, table_name, constraint_name;
65 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_NAME ENGINE_ATTRIBUTE SECONDARY_ENGINE_ATTRIBUTE
66 def db_datadict MUL_IDX t1 NULL NULL
67 def db_datadict PRIMARY t1 NULL NULL
68 def db_datadict PRIMARY t2 NULL NULL
69 SELECT (SELECT COUNT(*) FROM information_schema.table_constraints WHERE constraint_schema = 'db_datadict') AS CONSTRAINTS_ROWS, (SELECT COUNT(*) FROM information_schema.table_constraints_extensions WHERE constraint_schema = 'db_datadict') AS CONSTRAINTS_EXT_ROWS;
70 CONSTRAINTS_ROWS CONSTRAINTS_EXT_ROWS
71 3 3
72 # Checking tables_extensions as testuser1
73 SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE, VERSION, ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT, '<create time>' AS CREATE_TIME, UPDATE_TIME, CHECK_TIME, TABLE_COLLATION, CHECKSUM, CREATE_OPTIONS, TABLE_COMMENT FROM information_schema.tables WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name;
74 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT
75 def db_datadict t1 BASE TABLE InnoDB 10 Dynamic 0 0 16384 0 16384 0 1 <create time> NULL NULL utf8mb4_0900_ai_ci NULL
76 def db_datadict v1 VIEW NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL <create time> NULL NULL NULL NULL NULL VIEW
77 SELECT * FROM information_schema.tables_extensions WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name;
78 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME ENGINE_ATTRIBUTE SECONDARY_ENGINE_ATTRIBUTE
79 def db_datadict t1 NULL NULL
80 def db_datadict v1 NULL NULL
81 SELECT (SELECT count(*) FROM information_schema.tables WHERE table_schema = 'db_datadict') AS TABLES_ROWS, (SELECT COUNT(*) FROM information_schema.tables_extensions WHERE table_schema = 'db_datadict') AS TABLES_EXT_ROWS;
82 TABLES_ROWS TABLES_EXT_ROWS
83 2 2
84 # Checking columns_extensions as testuser1
85 SELECT * FROM information_schema.columns WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name, column_name;
86 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT GENERATION_EXPRESSION SRS_ID
87 def db_datadict t1 f1 1 NULL YES char 10 40 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci char(10) MUL select NULL
88 def db_datadict t1 f2 2 NULL YES text 65535 65535 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci text select NULL
89 def db_datadict v1 f2 2 0 NO int NULL NULL 10 0 NULL NULL NULL int select NULL
90 SELECT * FROM information_schema.columns_extensions WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name, column_name;
91 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ENGINE_ATTRIBUTE SECONDARY_ENGINE_ATTRIBUTE
92 def db_datadict t1 f1 NULL NULL
93 def db_datadict t1 f2 NULL NULL
94 def db_datadict v1 f2 NULL NULL
95 SELECT (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = 'db_datadict') AS COLUMNS_ROWS, (SELECT COUNT(*) FROM information_schema.columns_extensions WHERE table_schema = 'db_datadict') AS COLUMNS_EXT_ROWS;
96 COLUMNS_ROWS COLUMNS_EXT_ROWS
97 3 3
98 # Checking table_constraints_extensions as testuser1
99 SELECT * FROM information_schema.table_constraints WHERE constraint_schema = 'db_datadict' ORDER BY constraint_schema, table_name, constraint_name;
100 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE ENFORCED
101 def db_datadict MUL_IDX db_datadict t1 UNIQUE YES
102 def db_datadict PRIMARY db_datadict t1 PRIMARY KEY YES
103 SELECT * FROM information_schema.table_constraints_extensions WHERE constraint_schema = 'db_datadict' ORDER BY constraint_schema, table_name, constraint_name;
104 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_NAME ENGINE_ATTRIBUTE SECONDARY_ENGINE_ATTRIBUTE
105 def db_datadict MUL_IDX t1 NULL NULL
106 def db_datadict PRIMARY t1 NULL NULL
107 SELECT (SELECT COUNT(*) FROM information_schema.table_constraints WHERE constraint_schema = 'db_datadict') AS CONSTRAINTS_ROWS, (SELECT COUNT(*) FROM information_schema.table_constraints_extensions WHERE constraint_schema = 'db_datadict') AS CONSTRAINTS_EXT_ROWS;
108 CONSTRAINTS_ROWS CONSTRAINTS_EXT_ROWS
109 2 2
110 # Checking tables_extensions as testuser1
111 SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE, VERSION, ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT, '<create time>' AS CREATE_TIME, UPDATE_TIME, CHECK_TIME, TABLE_COLLATION, CHECKSUM, CREATE_OPTIONS, TABLE_COMMENT FROM information_schema.tables WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name;
112 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ENGINE VERSION ROW_FORMAT TABLE_ROWS AVG_ROW_LENGTH DATA_LENGTH MAX_DATA_LENGTH INDEX_LENGTH DATA_FREE AUTO_INCREMENT CREATE_TIME UPDATE_TIME CHECK_TIME TABLE_COLLATION CHECKSUM CREATE_OPTIONS TABLE_COMMENT
113 def db_datadict t2 BASE TABLE InnoDB 10 Dynamic 0 0 16384 0 0 0 NULL <create time> NULL NULL utf8mb4_0900_ai_ci NULL
114 SELECT * FROM information_schema.tables_extensions WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name;
115 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME ENGINE_ATTRIBUTE SECONDARY_ENGINE_ATTRIBUTE
116 def db_datadict t2 NULL NULL
117 SELECT (SELECT count(*) FROM information_schema.tables WHERE table_schema = 'db_datadict') AS TABLES_ROWS, (SELECT COUNT(*) FROM information_schema.tables_extensions WHERE table_schema = 'db_datadict') AS TABLES_EXT_ROWS;
118 TABLES_ROWS TABLES_EXT_ROWS
119 1 1
120 # Checking columns_extensions as testuser2
121 SELECT * FROM information_schema.columns WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name, column_name;
122 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT GENERATION_EXPRESSION SRS_ID
123 def db_datadict t2 f1 1 NULL NO char 10 40 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci char(10) PRI insert NULL
124 def db_datadict t2 f2 2 NULL YES text 65535 65535 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci text insert NULL
125 SELECT * FROM information_schema.columns_extensions WHERE table_schema = 'db_datadict' ORDER BY table_schema, table_name, column_name;
126 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ENGINE_ATTRIBUTE SECONDARY_ENGINE_ATTRIBUTE
127 def db_datadict t2 f1 NULL NULL
128 def db_datadict t2 f2 NULL NULL
129 SELECT (SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = 'db_datadict') AS COLUMNS_ROWS, (SELECT COUNT(*) FROM information_schema.columns_extensions WHERE table_schema = 'db_datadict') AS COLUMNS_EXT_ROWS;
130 COLUMNS_ROWS COLUMNS_EXT_ROWS
131 2 2
132 # Checking table_constraints_extensions as testuser2
133 SELECT * FROM information_schema.table_constraints WHERE constraint_schema = 'db_datadict' ORDER BY constraint_schema, table_name, constraint_name;
134 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE ENFORCED
135 def db_datadict PRIMARY db_datadict t2 PRIMARY KEY YES
136 SELECT * FROM information_schema.table_constraints_extensions WHERE constraint_schema = 'db_datadict' ORDER BY constraint_schema, table_name, constraint_name;
137 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_NAME ENGINE_ATTRIBUTE SECONDARY_ENGINE_ATTRIBUTE
138 def db_datadict PRIMARY t2 NULL NULL
139 SELECT (SELECT COUNT(*) FROM information_schema.table_constraints WHERE constraint_schema = 'db_datadict') AS CONSTRAINTS_ROWS, (SELECT COUNT(*) FROM information_schema.table_constraints_extensions WHERE constraint_schema = 'db_datadict') AS CONSTRAINTS_EXT_ROWS;
140 CONSTRAINTS_ROWS CONSTRAINTS_EXT_ROWS
141 1 1
142 # Cleanup
143 DROP TABLE db_datadict.t2;
144 DROP TABLE db_datadict.t1;
145 DROP USER 'testuser2'@'localhost';
146 DROP USER 'testuser1'@'localhost';
147 DROP SCHEMA db_datadict;