Debian Codegraph @

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

raw
1 SHOW TABLES FROM information_schema LIKE 'VIEWS';
2 Tables_in_information_schema (VIEWS)
3 VIEWS
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.VIEWS;
11 CREATE PROCEDURE test.p1() SELECT * FROM information_schema.VIEWS;
12 CREATE FUNCTION test.f1() returns BIGINT
13 BEGIN
14 DECLARE counter BIGINT DEFAULT NULL;
15 SELECT COUNT(*) INTO counter FROM information_schema.VIEWS;
16 RETURN counter;
17 END//
18 # Attention: The printing of the next result sets is disabled.
19 SELECT * FROM information_schema.VIEWS;
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.13.1: INFORMATION_SCHEMA.VIEWS layout
28 #########################################################################
29 DESCRIBE information_schema.VIEWS;
30 Field Type Null Key Default Extra
31 TABLE_CATALOG varchar(64) YES NULL
32 TABLE_SCHEMA varchar(64) YES NULL
33 TABLE_NAME varchar(64) YES NULL
34 VIEW_DEFINITION longtext YES NULL
35 CHECK_OPTION enum('NONE','LOCAL','CASCADED') YES NULL
36 IS_UPDATABLE enum('NO','YES') YES NULL
37 DEFINER varchar(288) YES NULL
38 SECURITY_TYPE varchar(7) YES NULL
39 CHARACTER_SET_CLIENT varchar(64) NO NULL
40 COLLATION_CONNECTION varchar(64) NO NULL
41 SHOW CREATE TABLE information_schema.VIEWS;
42 View Create View character_set_client collation_connection
43 VIEWS CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `information_schema`.`VIEWS` AS select (`cat`.`name` collate utf8mb3_tolower_ci) AS `TABLE_CATALOG`,(`sch`.`name` collate utf8mb3_tolower_ci) AS `TABLE_SCHEMA`,(`vw`.`name` collate utf8mb3_tolower_ci) AS `TABLE_NAME`,if((can_access_view(`sch`.`name`,`vw`.`name`,`vw`.`view_definer`,`vw`.`options`) = true),`vw`.`view_definition_utf8`,'') AS `VIEW_DEFINITION`,`vw`.`view_check_option` AS `CHECK_OPTION`,`vw`.`view_is_updatable` AS `IS_UPDATABLE`,`vw`.`view_definer` AS `DEFINER`,if((`vw`.`view_security_type` = 'DEFAULT'),'DEFINER',`vw`.`view_security_type`) AS `SECURITY_TYPE`,`cs`.`name` AS `CHARACTER_SET_CLIENT`,`conn_coll`.`name` AS `COLLATION_CONNECTION` from (((((`mysql`.`tables` `vw` join `mysql`.`schemata` `sch` on((`vw`.`schema_id` = `sch`.`id`))) join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) join `mysql`.`collations` `conn_coll` on((`conn_coll`.`id` = `vw`.`view_connection_collation_id`))) join `mysql`.`collations` `client_coll` on((`client_coll`.`id` = `vw`.`view_client_collation_id`))) join `mysql`.`character_sets` `cs` on((`cs`.`id` = `client_coll`.`character_set_id`))) where ((0 <> can_access_table(`sch`.`name`,`vw`.`name`)) and (`vw`.`type` = 'VIEW')) utf8mb3 utf8mb3_general_ci
44 SHOW COLUMNS FROM information_schema.VIEWS;
45 Field Type Null Key Default Extra
46 TABLE_CATALOG varchar(64) YES NULL
47 TABLE_SCHEMA varchar(64) YES NULL
48 TABLE_NAME varchar(64) YES NULL
49 VIEW_DEFINITION longtext YES NULL
50 CHECK_OPTION enum('NONE','LOCAL','CASCADED') YES NULL
51 IS_UPDATABLE enum('NO','YES') YES NULL
52 DEFINER varchar(288) YES NULL
53 SECURITY_TYPE varchar(7) YES NULL
54 CHARACTER_SET_CLIENT varchar(64) NO NULL
55 COLLATION_CONNECTION varchar(64) NO NULL
56 SELECT table_catalog, table_schema, table_name
57 FROM information_schema.views WHERE table_catalog IS NOT NULL order by table_name;
58 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME
59 def sys host_summary
60 def sys host_summary_by_file_io
61 def sys host_summary_by_file_io_type
62 def sys host_summary_by_stages
63 def sys host_summary_by_statement_latency
64 def sys host_summary_by_statement_type
65 def sys innodb_buffer_stats_by_schema
66 def sys innodb_buffer_stats_by_table
67 def sys innodb_lock_waits
68 def sys io_by_thread_by_latency
69 def sys io_global_by_file_by_bytes
70 def sys io_global_by_file_by_latency
71 def sys io_global_by_wait_by_bytes
72 def sys io_global_by_wait_by_latency
73 def sys latest_file_io
74 def sys memory_by_host_by_current_bytes
75 def sys memory_by_thread_by_current_bytes
76 def sys memory_by_user_by_current_bytes
77 def sys memory_global_by_current_bytes
78 def sys memory_global_total
79 def sys metrics
80 def sys processlist
81 def sys ps_check_lost_instrumentation
82 def sys schema_auto_increment_columns
83 def sys schema_index_statistics
84 def sys schema_object_overview
85 def sys schema_redundant_indexes
86 def sys schema_table_lock_waits
87 def sys schema_table_statistics
88 def sys schema_table_statistics_with_buffer
89 def sys schema_tables_with_full_table_scans
90 def sys schema_unused_indexes
91 def sys session
92 def sys session_ssl_status
93 def sys statement_analysis
94 def sys statements_with_errors_or_warnings
95 def sys statements_with_full_table_scans
96 def sys statements_with_runtimes_in_95th_percentile
97 def sys statements_with_sorting
98 def sys statements_with_temp_tables
99 def sys user_summary
100 def sys user_summary_by_file_io
101 def sys user_summary_by_file_io_type
102 def sys user_summary_by_stages
103 def sys user_summary_by_statement_latency
104 def sys user_summary_by_statement_type
105 def sys version
106 def sys wait_classes_global_by_avg_latency
107 def sys wait_classes_global_by_latency
108 def sys waits_by_host_by_latency
109 def sys waits_by_user_by_latency
110 def sys waits_global_by_latency
111 def sys x$host_summary
112 def sys x$host_summary_by_file_io
113 def sys x$host_summary_by_file_io_type
114 def sys x$host_summary_by_stages
115 def sys x$host_summary_by_statement_latency
116 def sys x$host_summary_by_statement_type
117 def sys x$innodb_buffer_stats_by_schema
118 def sys x$innodb_buffer_stats_by_table
119 def sys x$innodb_lock_waits
120 def sys x$io_by_thread_by_latency
121 def sys x$io_global_by_file_by_bytes
122 def sys x$io_global_by_file_by_latency
123 def sys x$io_global_by_wait_by_bytes
124 def sys x$io_global_by_wait_by_latency
125 def sys x$latest_file_io
126 def sys x$memory_by_host_by_current_bytes
127 def sys x$memory_by_thread_by_current_bytes
128 def sys x$memory_by_user_by_current_bytes
129 def sys x$memory_global_by_current_bytes
130 def sys x$memory_global_total
131 def sys x$processlist
132 def sys x$ps_digest_95th_percentile_by_avg_us
133 def sys x$ps_digest_avg_latency_distribution
134 def sys x$ps_schema_table_statistics_io
135 def sys x$schema_flattened_keys
136 def sys x$schema_index_statistics
137 def sys x$schema_table_lock_waits
138 def sys x$schema_table_statistics
139 def sys x$schema_table_statistics_with_buffer
140 def sys x$schema_tables_with_full_table_scans
141 def sys x$session
142 def sys x$statement_analysis
143 def sys x$statements_with_errors_or_warnings
144 def sys x$statements_with_full_table_scans
145 def sys x$statements_with_runtimes_in_95th_percentile
146 def sys x$statements_with_sorting
147 def sys x$statements_with_temp_tables
148 def sys x$user_summary
149 def sys x$user_summary_by_file_io
150 def sys x$user_summary_by_file_io_type
151 def sys x$user_summary_by_stages
152 def sys x$user_summary_by_statement_latency
153 def sys x$user_summary_by_statement_type
154 def sys x$wait_classes_global_by_avg_latency
155 def sys x$wait_classes_global_by_latency
156 def sys x$waits_by_host_by_latency
157 def sys x$waits_by_user_by_latency
158 def sys x$waits_global_by_latency
159 ################################################################################
160 # Testcase 3.2.13.2 + 3.2.13.3: INFORMATION_SCHEMA.VIEWS accessible information
161 ################################################################################
162 DROP DATABASE IF EXISTS db_datadict;
163 CREATE DATABASE db_datadict;
164 DROP USER 'testuser1'@'localhost';
165 CREATE USER 'testuser1'@'localhost';
166 DROP USER 'testuser2'@'localhost';
167 CREATE USER 'testuser2'@'localhost';
168 DROP USER 'test_no_views'@'localhost';
169 CREATE USER 'test_no_views'@'localhost';
170 CREATE TABLE db_datadict.t1(f1 INT, f2 INT, f3 INT)
171 ENGINE = <engine_type>;
172 CREATE VIEW db_datadict.v_granted_to_1 AS SELECT * FROM db_datadict.t1;
173 CREATE VIEW db_datadict.v_granted_glob AS SELECT f2, f3 FROM db_datadict.t1;
174 GRANT SELECT ON db_datadict.t1 TO 'testuser1'@'localhost';
175 GRANT SELECT ON db_datadict.v_granted_to_1 TO 'testuser1'@'localhost';
176 GRANT SHOW VIEW, CREATE VIEW ON db_datadict.* TO 'testuser2'@'localhost';
177 SELECT * FROM information_schema.views
178 WHERE table_schema = 'db_datadict' ORDER BY table_name;
179 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION
180 def db_datadict v_granted_glob select `db_datadict`.`t1`.`f2` AS `f2`,`db_datadict`.`t1`.`f3` AS `f3` from `db_datadict`.`t1` NONE YES root@localhost DEFINER utf8mb4 utf8mb4_0900_ai_ci
181 def db_datadict v_granted_to_1 select `db_datadict`.`t1`.`f1` AS `f1`,`db_datadict`.`t1`.`f2` AS `f2`,`db_datadict`.`t1`.`f3` AS `f3` from `db_datadict`.`t1` NONE YES root@localhost DEFINER utf8mb4 utf8mb4_0900_ai_ci
182 # Establish connection testuser1 (user=testuser1)
183 SELECT * FROM information_schema.views
184 WHERE table_schema = 'db_datadict' ORDER BY table_name;
185 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION
186 def db_datadict v_granted_to_1 NONE YES root@localhost DEFINER utf8mb4 utf8mb4_0900_ai_ci
187 # Establish connection testuser2 (user=testuser2)
188 SELECT * FROM information_schema.views
189 WHERE table_schema = 'db_datadict' ORDER BY table_name;
190 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION
191 def db_datadict v_granted_glob NONE YES root@localhost DEFINER utf8mb4 utf8mb4_0900_ai_ci
192 def db_datadict v_granted_to_1 NONE YES root@localhost DEFINER utf8mb4 utf8mb4_0900_ai_ci
193 # Establish connection test_no_views (user=test_no_views)
194 SELECT * FROM information_schema.views
195 WHERE table_schema = 'db_datadict' ORDER BY table_name;
196 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION
197 # Switch to connection default and close all other connections
198 DROP USER 'testuser1'@'localhost';
199 DROP USER 'testuser2'@'localhost';
200 DROP USER 'test_no_views'@'localhost';
201 DROP DATABASE db_datadict;
202 #########################################################################
203 # 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.VIEWS modifications
204 #########################################################################
205 DROP TABLE IF EXISTS test.t1_my_table;
206 DROP DATABASE IF EXISTS db_datadict;
207 CREATE DATABASE db_datadict;
208 CREATE TABLE test.t1_table (f1 BIGINT, f2 CHAR(10))
209 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci
210 ENGINE = <engine_type>;
211 DROP USER 'testuser1'@'localhost';
212 CREATE USER 'testuser1'@'localhost';
213 SELECT * FROM information_schema.views
214 WHERE table_name LIKE 't1_%';
215 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION
216 CREATE VIEW test.t1_view AS SELECT DISTINCT f1 FROM test.t1_table;
217 SELECT * FROM information_schema.views
218 WHERE table_name LIKE 't1_%';
219 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME VIEW_DEFINITION CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE CHARACTER_SET_CLIENT COLLATION_CONNECTION
220 def test t1_view select distinct `test`.`t1_table`.`f1` AS `f1` from `test`.`t1_table` NONE NO root@localhost DEFINER utf8mb4 utf8mb4_0900_ai_ci
221 SELECT table_name,definer FROM information_schema.views
222 WHERE table_name = 't1_view';
223 TABLE_NAME DEFINER
224 t1_view root@localhost
225 ALTER DEFINER = 'testuser1'@'localhost' VIEW test.t1_view AS
226 SELECT DISTINCT f1 FROM test.t1_table;
227 SELECT table_name,definer,security_type FROM information_schema.views
228 WHERE table_name LIKE 't1_%';
229 TABLE_NAME DEFINER SECURITY_TYPE
230 t1_view testuser1@localhost DEFINER
231 ALTER DEFINER = 'root'@'localhost' SQL SECURITY INVOKER VIEW test.t1_view AS
232 SELECT f1 FROM test.t1_table WITH LOCAL CHECK OPTION;
233 SELECT table_name,definer,security_type FROM information_schema.views
234 WHERE table_name LIKE 't1_%';
235 TABLE_NAME DEFINER SECURITY_TYPE
236 t1_view root@localhost INVOKER
237 SELECT table_schema,table_name FROM information_schema.views
238 WHERE table_name LIKE 't1_%'
239 ORDER BY table_schema,table_name;
240 TABLE_SCHEMA TABLE_NAME
241 test t1_view
242 RENAME TABLE test.t1_view TO db_datadict.t1_view;
243 ERROR HY000: Changing schema from 'test' to 'db_datadict' is not allowed.
244 DROP VIEW test.t1_view;
245 CREATE VIEW db_datadict.t1_view AS SELECT * FROM test.t1_table;
246 SELECT table_schema,table_name FROM information_schema.views
247 WHERE table_name LIKE 't1_%'
248 ORDER BY table_schema,table_name;
249 TABLE_SCHEMA TABLE_NAME
250 db_datadict t1_view
251 SELECT table_name FROM information_schema.views
252 WHERE table_name LIKE 't1_%'
253 ORDER BY table_name;
254 TABLE_NAME
255 t1_view
256 RENAME TABLE db_datadict.t1_view TO db_datadict.t1_viewx;
257 SELECT table_name FROM information_schema.views
258 WHERE table_name LIKE 't1_%'
259 ORDER BY table_name;
260 TABLE_NAME
261 t1_viewx
262 SELECT table_name FROM information_schema.views
263 WHERE table_name LIKE 't1_%'
264 ORDER BY table_name;
265 TABLE_NAME
266 t1_viewx
267 DROP VIEW db_datadict.t1_viewx;
268 SELECT table_name FROM information_schema.views
269 WHERE table_name LIKE 't1_%'
270 ORDER BY table_name;
271 TABLE_NAME
272 CREATE VIEW db_datadict.t1_view AS SELECT * FROM test.t1_table;
273 SELECT table_name FROM information_schema.views
274 WHERE table_name LIKE 't1_%'
275 ORDER BY table_name;
276 TABLE_NAME
277 t1_view
278 DROP TABLE test.t1_table;
279 SELECT table_name FROM information_schema.views
280 WHERE table_name LIKE 't1_%'
281 ORDER BY table_name;
282 TABLE_NAME
283 t1_view
284 CREATE TABLE test.t1_table (f1 BIGINT, f2 CHAR(10))
285 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci COMMENT = 'Initial Comment'
286 ENGINE = <engine_type>;
287 SELECT table_name FROM information_schema.views
288 WHERE table_name LIKE 't1_%'
289 ORDER BY table_name;
290 TABLE_NAME
291 t1_view
292 DROP DATABASE db_datadict;
293 SELECT table_name FROM information_schema.views
294 WHERE table_name LIKE 't1_%'
295 ORDER BY table_name;
296 TABLE_NAME
297 DROP USER 'testuser1'@'localhost';
298 DROP TABLE test.t1_table;
299 ########################################################################
300 # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
301 # DDL on INFORMATION_SCHEMA table are not supported
302 ########################################################################
303 DROP DATABASE IF EXISTS db_datadict;
304 CREATE DATABASE db_datadict;
305 CREATE VIEW db_datadict.v1 AS SELECT 1;
306 INSERT INTO information_schema.views
307 SELECT * FROM information_schema.views;
308 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
309 INSERT INTO information_schema.views(table_schema, table_name)
310 VALUES ('db2', 'v2');
311 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
312 UPDATE information_schema.views SET table_schema = 'test'
313 WHERE table_name = 't1';
314 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
315 DELETE FROM information_schema.views WHERE table_name = 't1';
316 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
317 TRUNCATE information_schema.views;
318 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
319 CREATE INDEX my_idx_on_views ON information_schema.views(table_schema);
320 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
321 ALTER TABLE information_schema.views DROP PRIMARY KEY;
322 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
323 ALTER TABLE information_schema.views ADD f1 INT;
324 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
325 DROP TABLE information_schema.views;
326 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
327 ALTER TABLE information_schema.views RENAME db_datadict.views;
328 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
329 ALTER TABLE information_schema.views RENAME information_schema.xviews;
330 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
331 DROP DATABASE db_datadict;