Debian Codegraph @

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

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