Debian Codegraph @

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

raw
1 ## generated
2 ## Test query with resultset
3
4 ## Preamble
5
6 ## SETUP DB
7 --source include/xplugin_preamble.inc
8 --source include/xplugin_create_user.inc
9 CREATE DATABASE xtest;
10 USE xtest;
11 CREATE TABLE `scalar_types`(`number_uint` INTEGER UNSIGNED DEFAULT 10, `number_sint` INTEGER DEFAULT -10, `value_bool` BOOLEAN DEFAULT 0, `value_string` VARCHAR(255) default NULL, `number_float` FLOAT DEFAULT 1.1, `number_double` DOUBLE DEFAULT 2.2, `value_blob` TINYBLOB DEFAULT NULL);
12
13 --write_file $MYSQL_TMP_DIR/stmtexecute_insert.xpl
14 Mysqlx.Sql.StmtExecute {
15 stmt: "INSERT INTO `xtest`.`scalar_types`(`number_sint`, `number_uint`, `value_string`) VALUES(0, ?, 'First inserted row'),(?, 1,'Second inserted row'), (-1, 0,?)"
16 args {
17 type: SCALAR
18 scalar {
19 type: V_UINT
20 v_unsigned_int: 1
21 }
22 }
23 args {
24 type: SCALAR
25 scalar {
26 type: V_SINT
27 v_signed_int: -1
28 }
29 }
30 args {
31 type: SCALAR
32 scalar {
33 type: V_STRING
34 v_string {
35 value: "\"Thrid\" inserted 'row'"
36 }
37 }
38 }
39 }
40 -->recvresult
41
42 Mysqlx.Sql.StmtExecute {
43 stmt: "INSERT INTO `xtest`.`scalar_types`(`number_float`, `number_double`, `value_blob`) VALUES(0.1, ?, 'First inserted blob'),(?, 200.01,'Second inserted blob'), (10.11, 2.2, ?), (111, 222, ?)"
44 args {
45 type: SCALAR
46 scalar {
47 type: V_DOUBLE
48 v_double: 200.01
49 }
50 }
51 args {
52 type: SCALAR
53 scalar {
54 type: V_DOUBLE
55 v_double: 10.11
56 }
57 }
58 args {
59 type: SCALAR
60 scalar {
61 type: V_STRING
62 v_string {
63 value: "\"Thrid\" inserted 'blob'"
64 }
65 }
66 }
67 args {
68 type: SCALAR
69 scalar {
70 type: V_NULL
71 }
72 }
73 }
74 -->recvresult
75 EOF
76
77 --write_file $MYSQL_TMP_DIR/stmtexecute_select.xpl
78 -->echo Filter data by unsigned int
79 Mysqlx.Sql.StmtExecute {
80 stmt: "SELECT * FROM `xtest`.`scalar_types` WHERE `number_uint`=?"
81 args {
82 type: SCALAR
83 scalar {
84 type: V_UINT
85 v_unsigned_int: 1
86 }
87 }
88 }
89 -->recvresult
90
91 -->echo Filter data by signed int
92 Mysqlx.Sql.StmtExecute {
93 stmt: "SELECT * FROM `xtest`.`scalar_types` WHERE `number_sint`=?"
94 args {
95 type: SCALAR
96 scalar {
97 type: V_SINT
98 v_signed_int: -1
99 }
100 }
101 }
102 -->recvresult
103
104 -->echo Filter data by blob
105 Mysqlx.Sql.StmtExecute {
106 stmt: "SELECT * FROM `xtest`.`scalar_types` WHERE `value_blob` LIKE ?"
107 args {
108 type: SCALAR
109 scalar {
110 type: V_STRING
111 v_string {
112 value: "%blob%"
113 }
114 }
115 }
116 }
117 -->recvresult
118
119 -->echo Filter empty data
120 Mysqlx.Sql.StmtExecute {
121 stmt: "SELECT * FROM `xtest`.`scalar_types` WHERE `value_string` IS ?"
122 args {
123 type: SCALAR
124 scalar {
125 type: V_NULL
126 }
127 }
128 }
129 -->recvresult
130
131 -->echo Filter data by string pattern
132 Mysqlx.Sql.StmtExecute {
133 stmt: "SELECT * FROM `xtest`.`scalar_types` WHERE `value_string` LIKE ?"
134 args {
135 type: SCALAR
136 scalar {
137 type: V_STRING
138 v_string {
139 value: "%inserted 'row%"
140 }
141 }
142 }
143 }
144 -->recvresult
145
146 -->echo Filter data by double
147 Mysqlx.Sql.StmtExecute {
148 stmt: "SELECT * FROM `xtest`.`scalar_types` WHERE `number_double` = ?"
149 args {
150 type: SCALAR
151 scalar {
152 type: V_DOUBLE
153 v_double: 2.2
154 }
155 }
156 }
157 -->recvresult
158
159 -->echo Append string as new column
160 Mysqlx.Sql.StmtExecute {
161 stmt: "SELECT `number_sint`,? FROM `xtest`.`scalar_types` LIMIT 1"
162 args {
163 type: SCALAR
164 scalar {
165 type: V_STRING
166 v_string {
167 value: "number_sint"
168 }
169 }
170 }
171 }
172 -->recvresult
173
174 -->echo Use compact_metadata=true/false
175 Mysqlx.Sql.StmtExecute {
176 stmt: "SELECT `number_sint`,? FROM `xtest`.`scalar_types` LIMIT 1"
177 args {
178 type: SCALAR
179 scalar {
180 type: V_STRING
181 v_string {
182 value: "number_sint"
183 }
184 }
185 }
186 compact_metadata : true
187 }
188 -->recvuntil Mysqlx.Sql.StmtExecuteOk
189
190 Mysqlx.Sql.StmtExecute {
191 stmt: "SELECT `number_sint`,? FROM `xtest`.`scalar_types` LIMIT 1"
192 args {
193 type: SCALAR
194 scalar {
195 type: V_STRING
196 v_string {
197 value: "number_sint"
198 }
199 }
200 }
201 compact_metadata : false
202 }
203 -->recvuntil Mysqlx.Sql.StmtExecuteOk
204 EOF
205
206
207 --write_file $MYSQL_TMP_DIR/stmtexecute_invalid.xpl
208 -->echo Too many args
209 Mysqlx.Sql.StmtExecute {
210 stmt: "SELECT * FROM `xtest`.`scalar_types`"
211 args {
212 type: SCALAR
213 scalar {
214 type: V_STRING
215 v_string {
216 value: "%inserted 'row%"
217 }
218 }
219 }
220 }
221 -->recvtype Mysqlx.Error
222
223 -->sql
224 USE `xtest`;
225 -->endsql
226
227 -->echo String as identifier
228 Mysqlx.Sql.StmtExecute {
229 stmt: "SELECT * FROM ?"
230 args {
231 type: SCALAR
232 scalar {
233 type: V_STRING
234 v_string {
235 value: "scalar_types"
236 }
237 }
238 }
239 }
240 -->recvtype Mysqlx.Error
241
242 EOF
243
244
245 --write_file $MYSQL_TMP_DIR/stmtexecute_bool_value.xpl
246 -->quiet
247
248 -->macro Assert_return_value %EXPECT% %ARG% %STMT%
249 Mysqlx.Sql.StmtExecute {
250 stmt: %STMT%
251 args {
252 type: SCALAR
253 scalar {
254 type: V_BOOL
255 v_bool: %ARG%
256 }
257 }
258 namespace: 'sql'
259 }
260 -->recvtovar %VARIABLE_VALUE% result
261 -->echo Verify [%STMT% for '?=%ARG%' returns '%EXPECT%']
262 -->assert_eq %EXPECT% %VARIABLE_VALUE%
263 -->endmacro
264
265
266 -->title -Check boolean value as argument for placeholder (document scenario)
267
268 -->stmtadmin create_collection {"schema":"xtest","name":"booleans"}
269 -->recvresult
270
271 Mysqlx.Crud.Insert {
272 collection {
273 name: "booleans"
274 schema: "xtest"
275 }
276 data_model: DOCUMENT
277 row {
278 field {
279 type: LITERAL
280 literal {
281 type: V_STRING
282 v_string {
283 value: '{"_id":"001", "active":true, "name":"foo"}'
284 }
285 }
286 }
287 }
288 row {
289 field {
290 type: LITERAL
291 literal {
292 type: V_STRING
293 v_string {
294 value: '{"_id":"002", "active":false, "name":"bar"}'
295 }
296 }
297 }
298 }
299 }
300 -->recvresult
301
302 -->callmacro Assert_return_value foo true "SELECT doc->>'$.name' as result FROM xtest.booleans WHERE doc->'$.active' = ?"
303 -->callmacro Assert_return_value bar false "SELECT doc->>'$.name' as result FROM xtest.booleans WHERE doc->'$.active' = ?"
304
305
306 -->title -Check boolean value as argument for placeholder (classic scenario)
307 -->sql
308 CREATE TABLE xtest.classic(name varchar(32), active BOOLEAN);
309 INSERT INTO xtest.classic(name, active) VALUES('foo', true),('bar', false);
310 -->endsql
311
312 -->callmacro Assert_return_value foo true "SELECT `name` as result FROM xtest.classic WHERE `active` = ?"
313 -->callmacro Assert_return_value bar false "SELECT `name` as result FROM xtest.classic WHERE `active` = ?"
314 EOF
315
316 ## Test starts here
317 --exec $MYSQLXTEST -ux_root --password='' --file=$MYSQL_TMP_DIR/stmtexecute_insert.xpl 2>&1
318
319 SELECT * FROM `xtest`.`scalar_types`;
320
321 --exec $MYSQLXTEST -ux_root --password='' --file=$MYSQL_TMP_DIR/stmtexecute_select.xpl 2>&1
322
323 --exec $MYSQLXTEST -ux_root --password='' --file=$MYSQL_TMP_DIR/stmtexecute_invalid.xpl 2>&1
324
325 --exec $MYSQLXTEST -ux_root --password='' --file=$MYSQL_TMP_DIR/stmtexecute_bool_value.xpl 2>&1
326
327 ## Cleanup
328 --source ../include/xplugin_cleanup.inc