Debian Codegraph @

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

raw
1 CREATE SCHEMA xtest;
2 CREATE TABLE xtest.xtable (first INT, second JSON);
3 CREATE USER xuser;
4
5 Create view (bad select)
6 ------------------------
7 Got expected error:
8 Mysqlx.Error {
9 severity: ERROR
10 code: 5120
11 msg: "Invalid projection target name"
12 sql_state: "HY000"
13 }
14
15
16 Create view (default)
17 ---------------------
18 Mysqlx.Ok {
19 }
20
21 View Create View character_set_client collation_connection
22 xview CREATE ALGORITHM=UNDEFINED DEFINER=`x_root`@`localhost` SQL SECURITY DEFINER VIEW `xtest`.`xview` AS select `xtest`.`xtable`.`first` AS `one` from `xtest`.`xtable` utf8mb4 utf8mb4_0900_ai_ci
23 0 rows affected
24
25 Create view (collection)
26 ------------------------
27 Mysqlx.Ok {
28 }
29
30 View Create View character_set_client collation_connection
31 xdoc CREATE ALGORITHM=UNDEFINED DEFINER=`x_root`@`localhost` SQL SECURITY DEFINER VIEW `xtest`.`xdoc` AS select `xtest`.`xtable`.`second` AS `doc` from `xtest`.`xtable` utf8mb4 utf8mb4_0900_ai_ci
32 0 rows affected
33
34 List objects
35 ------------
36 name type
37 xdoc COLLECTION_VIEW
38 xtable TABLE
39 xview VIEW
40 command ok
41
42 Create view (algorithm=undefined)
43 ---------------------------------
44 Mysqlx.Ok {
45 }
46
47 View Create View character_set_client collation_connection
48 au CREATE ALGORITHM=UNDEFINED DEFINER=`x_root`@`localhost` SQL SECURITY DEFINER VIEW `xtest`.`au` AS select `xtest`.`xtable`.`second` AS `doc` from `xtest`.`xtable` utf8mb4 utf8mb4_0900_ai_ci
49 0 rows affected
50
51 Create view (algorithm=merge)
52 -----------------------------
53 Mysqlx.Ok {
54 }
55
56 View Create View character_set_client collation_connection
57 am CREATE ALGORITHM=MERGE DEFINER=`x_root`@`localhost` SQL SECURITY DEFINER VIEW `xtest`.`am` AS select `xtest`.`xtable`.`second` AS `doc` from `xtest`.`xtable` utf8mb4 utf8mb4_0900_ai_ci
58 0 rows affected
59
60 Create view (algorithm=temptable)
61 ---------------------------------
62 Mysqlx.Ok {
63 }
64
65 View Create View character_set_client collation_connection
66 at CREATE ALGORITHM=TEMPTABLE DEFINER=`x_root`@`localhost` SQL SECURITY DEFINER VIEW `xtest`.`at` AS select `xtest`.`xtable`.`second` AS `doc` from `xtest`.`xtable` utf8mb4 utf8mb4_0900_ai_ci
67 0 rows affected
68
69 Create view (definer=xuser)
70 ---------------------------
71 Mysqlx.Ok {
72 }
73
74 View Create View character_set_client collation_connection
75 du CREATE ALGORITHM=UNDEFINED DEFINER=`xuser`@`%` SQL SECURITY DEFINER VIEW `xtest`.`du` AS select `xtest`.`xtable`.`second` AS `doc` from `xtest`.`xtable` utf8mb4 utf8mb4_0900_ai_ci
76 0 rows affected
77
78 Create view (security=invoker)
79 ------------------------------
80 Mysqlx.Ok {
81 }
82
83 View Create View character_set_client collation_connection
84 si CREATE ALGORITHM=UNDEFINED DEFINER=`x_root`@`localhost` SQL SECURITY INVOKER VIEW `xtest`.`si` AS select `xtest`.`xtable`.`second` AS `doc` from `xtest`.`xtable` utf8mb4 utf8mb4_0900_ai_ci
85 0 rows affected
86
87 Create view (security=definer)
88 ------------------------------
89 Mysqlx.Ok {
90 }
91
92 View Create View character_set_client collation_connection
93 sd CREATE ALGORITHM=UNDEFINED DEFINER=`x_root`@`localhost` SQL SECURITY DEFINER VIEW `xtest`.`sd` AS select `xtest`.`xtable`.`second` AS `doc` from `xtest`.`xtable` utf8mb4 utf8mb4_0900_ai_ci
94 0 rows affected
95
96 Create view (check=local)
97 -------------------------
98 Mysqlx.Ok {
99 }
100
101 View Create View character_set_client collation_connection
102 cl CREATE ALGORITHM=UNDEFINED DEFINER=`x_root`@`localhost` SQL SECURITY DEFINER VIEW `xtest`.`cl` AS select `xtest`.`xtable`.`second` AS `doc` from `xtest`.`xtable` WITH LOCAL CHECK OPTION utf8mb4 utf8mb4_0900_ai_ci
103 0 rows affected
104
105 Create view (check=cascaded)
106 ----------------------------
107 Mysqlx.Ok {
108 }
109
110 View Create View character_set_client collation_connection
111 cc CREATE ALGORITHM=UNDEFINED DEFINER=`x_root`@`localhost` SQL SECURITY DEFINER VIEW `xtest`.`cc` AS select `xtest`.`xtable`.`second` AS `doc` from `xtest`.`xtable` WITH CASCADED CHECK OPTION utf8mb4 utf8mb4_0900_ai_ci
112 0 rows affected
113
114 Create view (column)
115 --------------------
116 Mysqlx.Ok {
117 }
118
119 View Create View character_set_client collation_connection
120 co CREATE ALGORITHM=UNDEFINED DEFINER=`x_root`@`localhost` SQL SECURITY DEFINER VIEW `xtest`.`co` (`third`) AS select `xtest`.`xtable`.`second` AS `doc` from `xtest`.`xtable` utf8mb4 utf8mb4_0900_ai_ci
121 0 rows affected
122
123 Create view (too many columns)
124 ------------------------------
125 Mysqlx.Error {
126 severity: ERROR
127 code: 1353
128 msg: "In definition of view, derived table or common table expression, SELECT list and column names list have different column counts"
129 sql_state: "HY000"
130 }
131
132
133 Replace view (already exists)
134 -----------------------------
135 Mysqlx.Error {
136 severity: ERROR
137 code: 1050
138 msg: "Table \'co\' already exists"
139 sql_state: "42S01"
140 }
141
142 View Create View character_set_client collation_connection
143 co CREATE ALGORITHM=UNDEFINED DEFINER=`x_root`@`localhost` SQL SECURITY DEFINER VIEW `xtest`.`co` (`third`) AS select `xtest`.`xtable`.`second` AS `doc` from `xtest`.`xtable` utf8mb4 utf8mb4_0900_ai_ci
144 0 rows affected
145
146 Replace view
147 ------------
148 Mysqlx.Ok {
149 }
150
151 View Create View character_set_client collation_connection
152 co CREATE ALGORITHM=UNDEFINED DEFINER=`x_root`@`localhost` SQL SECURITY DEFINER VIEW `xtest`.`co` AS select `xtest`.`xtable`.`second` AS `fifth` from `xtest`.`xtable` utf8mb4 utf8mb4_0900_ai_ci
153 0 rows affected
154
155 Inserting data to collection_view
156 ---------------------------------
157
158 2 rows affected
159 Records: 2 Duplicates: 0 Warnings: 0
160
161 Selecting all data from collection_view
162 ---------------------------------------
163 doc
164 {"_id": "one", "name": "Adam"}
165 {"_id": "two", "name": "Barnaba"}
166 command ok
167
168 Updating one member of collection_view
169 --------------------------------------
170
171 1 rows affected
172 Rows matched: 1 Changed: 1 Warnings: 0
173 doc
174 {"_id": "one", "name": "Charles"}
175 {"_id": "two", "name": "Barnaba"}
176 0 rows affected
177
178 Deleting one row of collection_view
179 -----------------------------------
180
181 1 rows affected
182 doc
183 {"_id": "one", "name": "Charles"}
184 0 rows affected
185
186 Updating whole doc of collection_view
187 -------------------------------------
188
189 1 rows affected
190 Rows matched: 1 Changed: 1 Warnings: 0
191 doc
192 "{\"_id\":\"three\", \"name\":\"David\"}"
193 0 rows affected
194 Variable_name Value
195 Mysqlx_crud_create_view 15
196 0 rows affected
197 Mysqlx.Ok {
198 msg: "bye!"
199 }
200 ok
201 include/assert.inc [Global status of 'Mysqlx_crud_create_view']
202 DROP USER xuser;