Debian Codegraph @

mysql-test/suite/x/t/crud_find_doc_groupby.test

raw
1 ## Doc: Find with group by clause
2 ## BUG#22950240
3 ## BUG#23738896
4
5 --source include/xplugin_preamble.inc
6 --source include/xplugin_create_user.inc
7
8 CREATE SCHEMA xtest DEFAULT CHARSET 'utf8mb4';
9
10
11 ## Test starts here
12 --write_file $MYSQL_TMP_DIR/crud_find_doc_groupby.xpl
13 -->quiet
14
15 -->stmtadmin create_collection {"schema":"xtest","name":"xcoll"}
16 -->recvresult
17
18 INSERT INTO xtest.xcoll (doc) VALUES
19 ('{"_id": 1, "age": 1, "name": "foo"}'),
20 ('{"_id": 2, "age": 2, "name": "bar"}'),
21 ('{"_id": 3, "age": 3, "name": "baz", "date": {"day": 20, "month": "Apr"}} '),
22 ('{"_id": 4, "age": 7, "name": "foo"}'),
23 ('{"_id": 5, "age": 17, "name": "buz"}');
24
25 -->echo group by name
26 Mysqlx.Crud.Find {
27 collection { name: "xcoll" schema: "xtest" }
28 data_model: DOCUMENT
29 projection {
30 alias: "name"
31 source {
32 type: IDENT
33 identifier {
34 document_path {
35 type: MEMBER
36 value: "name"
37 }
38 }
39 }
40 }
41 grouping {
42 type: IDENT
43 identifier {
44 document_path {
45 type: MEMBER
46 value: "name"
47 }
48 }
49 }
50 }
51 -->recvresult
52
53 -->echo group by name and count
54 Mysqlx.Crud.Find {
55 collection { name: "xcoll" schema: "xtest" }
56 data_model: DOCUMENT
57 projection {
58 alias: "name"
59 source {
60 type: IDENT
61 identifier {
62 document_path {
63 type: MEMBER
64 value: "name"
65 }
66 }
67 }
68 }
69 projection {
70 alias: "count"
71 source {
72 type: FUNC_CALL
73 function_call {
74 name {
75 name: "count"
76 }
77 param {
78 type: OPERATOR operator {
79 name: "*"
80 }
81 }
82 }
83 }
84 }
85 grouping {
86 type: IDENT
87 identifier {
88 document_path {
89 type: MEMBER
90 value: "name"
91 }
92 }
93 }
94 }
95 -->recvresult
96
97 -->echo group by name, age and count
98 Mysqlx.Crud.Find {
99 collection { name: "xcoll" schema: "xtest" }
100 data_model: DOCUMENT
101 projection {
102 alias: "name"
103 source {
104 type: IDENT
105 identifier {
106 document_path {
107 type: MEMBER
108 value: "name"
109 }
110 }
111 }
112 }
113 projection {
114 alias: "age"
115 source {
116 type: IDENT
117 identifier {
118 document_path {
119 type: MEMBER
120 value: "age"
121 }
122 }
123 }
124 }
125 projection {
126 alias: "count"
127 source {
128 type: FUNC_CALL
129 function_call {
130 name {
131 name: "count"
132 }
133 param {
134 type: OPERATOR operator {
135 name: "*"
136 }
137 }
138 }
139 }
140 }
141 grouping {
142 type: IDENT
143 identifier {
144 document_path {
145 type: MEMBER
146 value: "name"
147 }
148 }
149 }
150 grouping {
151 type: IDENT
152 identifier {
153 document_path {
154 type: MEMBER
155 value: "age"
156 }
157 }
158 }
159 }
160 -->recvresult
161
162 -->echo no projection - error expected
163 Mysqlx.Crud.Find {
164 collection { name: "xcoll" schema: "xtest" }
165 data_model: DOCUMENT
166 grouping {
167 type: IDENT
168 identifier {
169 document_path {
170 type: MEMBER
171 value: "name"
172 }
173 }
174 }
175 }
176 -->expecterror ER_X_BAD_PROJECTION
177 -->recvresult
178
179 -->echo group by name, count name and criteria on count
180 Mysqlx.Crud.Find {
181 collection { name: "xcoll" schema: "xtest" }
182 data_model: DOCUMENT
183 projection {
184 alias: "name"
185 source {
186 type: IDENT
187 identifier {
188 document_path {
189 type: MEMBER
190 value: "name"
191 }
192 }
193 }
194 }
195 projection {
196 alias: "count"
197 source {
198 type: FUNC_CALL
199 function_call {
200 name {
201 name: "count"
202 }
203 param {
204 type: OPERATOR operator {
205 name: "*"
206 }
207 }
208 }
209 }
210 }
211 grouping {
212 type: IDENT
213 identifier {
214 document_path {
215 type: MEMBER
216 value: "name"
217 }
218 }
219 }
220 grouping_criteria {
221 type: OPERATOR
222 operator {
223 name: ">"
224 param {
225 type: IDENT
226 identifier {
227 document_path {
228 type: MEMBER
229 value: "count"
230 }
231 }
232 }
233 param {
234 type: LITERAL
235 literal {
236 type: V_UINT
237 v_unsigned_int: 1
238 }
239 }
240 }
241 }
242 }
243 -->expecterror ER_X_EXPR_BAD_VALUE
244 -->recvresult
245
246
247 -->echo group by name, criteria on max(age)
248 Mysqlx.Crud.Find {
249 collection { name: "xcoll" schema: "xtest" }
250 data_model: DOCUMENT
251 projection {
252 source {
253 type: IDENT
254 identifier {
255 document_path {
256 type: MEMBER
257 value: "name"
258 }
259 }
260 }
261 alias: "my_name"
262 }
263 projection {
264 source {
265 type: FUNC_CALL
266 function_call {
267 name {
268 name: "max"
269 }
270 param {
271 type: IDENT
272 identifier {
273 document_path {
274 type: MEMBER
275 value: "age"
276 }
277 }
278 }
279 }
280 }
281 alias: "my_age"
282 }
283 order {
284 expr {
285 type: IDENT
286 identifier {
287 name: "my_age"
288 }
289 }
290 }
291 grouping {
292 type: IDENT
293 identifier {
294 document_path {
295 type: MEMBER
296 value: "name"
297 }
298 }
299 }
300 grouping_criteria {
301 type: OPERATOR
302 operator {
303 name: ">"
304 param {
305 type: FUNC_CALL
306 function_call {
307 name {
308 name: "max"
309 }
310 param {
311 type: IDENT
312 identifier {
313 document_path {
314 type: MEMBER
315 value: "age"
316 }
317 }
318 }
319 }
320 }
321 param {
322 type: LITERAL
323 literal {
324 type: V_UINT
325 v_unsigned_int: 5
326 }
327 }
328 }
329 }
330 }
331 -->recvresult
332 EOF
333
334 --exec $MYSQLXTEST -ux_root --password='' --file=$MYSQL_TMP_DIR/crud_find_doc_groupby.xpl 2>&1
335
336
337 ## Cleanup
338 --source ../include/xplugin_cleanup.inc
339
340
341
342