Debian Codegraph @

mysql-test/suite/x/r/crud_view_sundries.result

raw
1 CREATE SCHEMA xtest;
2 CREATE TABLE xtest.tab_a (first INT, second JSON);
3 INSERT INTO xtest.tab_a (first, second) VALUES (1, '{"_id":"one", "name":"Adam"}' );
4 CREATE USER xuser;
5
6 Create view on document table created using "Mysqlx.Sql.StmtExecute"
7 --------------------------------------------------------------------
8
9 command ok
10
11 0 rows affected
12
13 0 rows affected
14 name type
15 coll_a COLLECTION
16 tab_a TABLE
17 view_a VIEW
18 view_b COLLECTION_VIEW
19 command ok
20 View Create View character_set_client collation_connection
21 view_a CREATE ALGORITHM=UNDEFINED DEFINER=`x_root`@`localhost` SQL SECURITY DEFINER VIEW `xtest`.`view_a` AS select `xtest`.`coll_a`.`doc` AS `doc`,`xtest`.`coll_a`.`_id` AS `_id`,`xtest`.`coll_a`.`_json_schema` AS `_json_schema` from `xtest`.`coll_a` utf8mb4 utf8mb4_0900_ai_ci
22 0 rows affected
23 View Create View character_set_client collation_connection
24 view_b CREATE ALGORITHM=UNDEFINED DEFINER=`x_root`@`localhost` SQL SECURITY DEFINER VIEW `xtest`.`view_b` AS select `xtest`.`coll_a`.`doc` AS `doc` from `xtest`.`coll_a` utf8mb4 utf8mb4_0900_ai_ci
25 0 rows affected
26
27 Create view based on another view
28 ---------------------------------
29 Mysqlx.Ok {
30 }
31
32 View Create View character_set_client collation_connection
33 view_c CREATE ALGORITHM=TEMPTABLE DEFINER=`x_root`@`localhost` SQL SECURITY DEFINER VIEW `xtest`.`view_c` AS select `xtest`.`view_a`.`doc` AS `doc` from `xtest`.`view_a` utf8mb4 utf8mb4_0900_ai_ci
34 0 rows affected
35
36 Error when try modify view which is not updatable.
37 --------------------------------------------------
38 Got expected error:
39 Mysqlx.Error {
40 severity: ERROR
41 code: 1368
42 msg: "CHECK OPTION on non-updatable view \'xtest.view_c\'"
43 sql_state: "HY000"
44 }
45
46
47 Run select query on view.
48 -------------------------
49 Mysqlx.Ok {
50 }
51
52 doc
53 {"_id": "one", "name": "Adam"}
54 0 rows affected
55 doc
56 {"_id": "one", "name": "Adam"}
57 command ok
58
59 Error on find if base table alteration invalidates the view.
60 ------------------------------------------------------------
61
62 0 rows affected
63 Records: 0 Duplicates: 0 Warnings: 0
64 Got expected error:
65 Mysqlx.Error {
66 severity: ERROR
67 code: 1356
68 msg: "View \'xtest.view_d\' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them"
69 sql_state: "HY000"
70 }
71
72
73 Error on create view for non privilege user
74 -------------------------------------------
75 connecting...
76 active session is now 'xuser_session'
77 Got expected error:
78 Mysqlx.Error {
79 severity: ERROR
80 code: 1142
81 msg: "CREATE VIEW command denied to user \'xuser\'@\'localhost\' for table \'view_e\'"
82 sql_state: "42000"
83 }
84
85 closing session xuser_session
86 Mysqlx.Ok {
87 msg: "bye!"
88 }
89 switched to session default
90
91 Check view details added in information_schema.views
92 ----------------------------------------------------
93 name type
94 coll_a COLLECTION
95 tab_a TABLE
96 view_a VIEW
97 view_b COLLECTION_VIEW
98 view_c COLLECTION_VIEW
99 view_d VIEW
100 command ok
101 TABLE_NAME CHECK_OPTION IS_UPDATABLE DEFINER SECURITY_TYPE VIEW_DEFINITION
102 view_a NONE YES x_root@localhost DEFINER select `xtest`.`coll_a`.`doc` AS `doc`,`xtest`.`coll_a`.`_id` AS `_id`,`xtest`.`coll_a`.`_json_schema` AS `_json_schema` from `xtest`.`coll_a`
103 view_b NONE YES x_root@localhost DEFINER select `xtest`.`coll_a`.`doc` AS `doc` from `xtest`.`coll_a`
104 view_c NONE NO x_root@localhost DEFINER select `xtest`.`view_a`.`doc` AS `doc` from `xtest`.`view_a`
105 view_d NONE YES x_root@localhost DEFINER select `xtest`.`tab_a`.`second` AS `doc` from `xtest`.`tab_a`
106 0 rows affected
107 Mysqlx.Ok {
108 msg: "bye!"
109 }
110 ok
111 DROP USER xuser;