Debian Codegraph @

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

raw
1 CREATE DATABASE xtest;
2 USE xtest;
3 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);
4 send Mysqlx.Sql.StmtExecute {
5 stmt: "INSERT INTO `xtest`.`scalar_types`(`number_sint`, `number_uint`, `value_string`) VALUES(0, ?, \'First inserted row\'),(?, 1,\'Second inserted row\'), (-1, 0,?)"
6 args {
7 type: SCALAR
8 scalar {
9 type: V_UINT
10 v_unsigned_int: 1
11 }
12 }
13 args {
14 type: SCALAR
15 scalar {
16 type: V_SINT
17 v_signed_int: -1
18 }
19 }
20 args {
21 type: SCALAR
22 scalar {
23 type: V_STRING
24 v_string {
25 value: "\"Thrid\" inserted \'row\'"
26 }
27 }
28 }
29 }
30
31
32 3 rows affected
33 Records: 3 Duplicates: 0 Warnings: 0
34 send Mysqlx.Sql.StmtExecute {
35 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, ?)"
36 args {
37 type: SCALAR
38 scalar {
39 type: V_DOUBLE
40 v_double: 200.01
41 }
42 }
43 args {
44 type: SCALAR
45 scalar {
46 type: V_DOUBLE
47 v_double: 10.11
48 }
49 }
50 args {
51 type: SCALAR
52 scalar {
53 type: V_STRING
54 v_string {
55 value: "\"Thrid\" inserted \'blob\'"
56 }
57 }
58 }
59 args {
60 type: SCALAR
61 scalar {
62 type: V_NULL
63 }
64 }
65 }
66
67
68 4 rows affected
69 Records: 4 Duplicates: 0 Warnings: 0
70 Mysqlx.Ok {
71 msg: "bye!"
72 }
73 ok
74 SELECT * FROM `xtest`.`scalar_types`;
75 number_uint number_sint value_bool value_string number_float number_double value_blob
76 1 0 0 First inserted row 1.1 2.2 NULL
77 1 -1 0 Second inserted row 1.1 2.2 NULL
78 0 -1 0 "Thrid" inserted 'row' 1.1 2.2 NULL
79 10 -10 0 NULL 0.1 200.01 First inserted blob
80 10 -10 0 NULL 10.11 200.01 Second inserted blob
81 10 -10 0 NULL 10.11 2.2 "Thrid" inserted 'blob'
82 10 -10 0 NULL 111 222 NULL
83 Filter data by unsigned int
84 send Mysqlx.Sql.StmtExecute {
85 stmt: "SELECT * FROM `xtest`.`scalar_types` WHERE `number_uint`=?"
86 args {
87 type: SCALAR
88 scalar {
89 type: V_UINT
90 v_unsigned_int: 1
91 }
92 }
93 }
94
95 number_uint number_sint value_bool value_string number_float number_double value_blob
96 1 0 0 First inserted row 1.1 2.2 null
97 1 -1 0 Second inserted row 1.1 2.2 null
98 0 rows affected
99 Filter data by signed int
100 send Mysqlx.Sql.StmtExecute {
101 stmt: "SELECT * FROM `xtest`.`scalar_types` WHERE `number_sint`=?"
102 args {
103 type: SCALAR
104 scalar {
105 type: V_SINT
106 v_signed_int: -1
107 }
108 }
109 }
110
111 number_uint number_sint value_bool value_string number_float number_double value_blob
112 1 -1 0 Second inserted row 1.1 2.2 null
113 0 -1 0 "Thrid" inserted 'row' 1.1 2.2 null
114 0 rows affected
115 Filter data by blob
116 send Mysqlx.Sql.StmtExecute {
117 stmt: "SELECT * FROM `xtest`.`scalar_types` WHERE `value_blob` LIKE ?"
118 args {
119 type: SCALAR
120 scalar {
121 type: V_STRING
122 v_string {
123 value: "%blob%"
124 }
125 }
126 }
127 }
128
129 number_uint number_sint value_bool value_string number_float number_double value_blob
130 10 -10 0 null 0.1 200.01 First inserted blob
131 10 -10 0 null 10.11 200.01 Second inserted blob
132 10 -10 0 null 10.11 2.2 "Thrid" inserted 'blob'
133 0 rows affected
134 Filter empty data
135 send Mysqlx.Sql.StmtExecute {
136 stmt: "SELECT * FROM `xtest`.`scalar_types` WHERE `value_string` IS ?"
137 args {
138 type: SCALAR
139 scalar {
140 type: V_NULL
141 }
142 }
143 }
144
145 number_uint number_sint value_bool value_string number_float number_double value_blob
146 10 -10 0 null 0.1 200.01 First inserted blob
147 10 -10 0 null 10.11 200.01 Second inserted blob
148 10 -10 0 null 10.11 2.2 "Thrid" inserted 'blob'
149 10 -10 0 null 111 222 null
150 0 rows affected
151 Filter data by string pattern
152 send Mysqlx.Sql.StmtExecute {
153 stmt: "SELECT * FROM `xtest`.`scalar_types` WHERE `value_string` LIKE ?"
154 args {
155 type: SCALAR
156 scalar {
157 type: V_STRING
158 v_string {
159 value: "%inserted \'row%"
160 }
161 }
162 }
163 }
164
165 number_uint number_sint value_bool value_string number_float number_double value_blob
166 0 -1 0 "Thrid" inserted 'row' 1.1 2.2 null
167 0 rows affected
168 Filter data by double
169 send Mysqlx.Sql.StmtExecute {
170 stmt: "SELECT * FROM `xtest`.`scalar_types` WHERE `number_double` = ?"
171 args {
172 type: SCALAR
173 scalar {
174 type: V_DOUBLE
175 v_double: 2.2
176 }
177 }
178 }
179
180 number_uint number_sint value_bool value_string number_float number_double value_blob
181 1 0 0 First inserted row 1.1 2.2 null
182 1 -1 0 Second inserted row 1.1 2.2 null
183 0 -1 0 "Thrid" inserted 'row' 1.1 2.2 null
184 10 -10 0 null 10.11 2.2 "Thrid" inserted 'blob'
185 0 rows affected
186 Append string as new column
187 send Mysqlx.Sql.StmtExecute {
188 stmt: "SELECT `number_sint`,? FROM `xtest`.`scalar_types` LIMIT 1"
189 args {
190 type: SCALAR
191 scalar {
192 type: V_STRING
193 v_string {
194 value: "number_sint"
195 }
196 }
197 }
198 }
199
200 number_sint number_sint
201 0 number_sint
202 0 rows affected
203 Use compact_metadata=true/false
204 send Mysqlx.Sql.StmtExecute {
205 stmt: "SELECT `number_sint`,? FROM `xtest`.`scalar_types` LIMIT 1"
206 args {
207 type: SCALAR
208 scalar {
209 type: V_STRING
210 v_string {
211 value: "number_sint"
212 }
213 }
214 }
215 compact_metadata: true
216 }
217
218 Mysqlx.Resultset.ColumnMetaData {
219 type: SINT
220 length: 11
221 }
222
223 Mysqlx.Resultset.ColumnMetaData {
224 type: BYTES
225 collation: 255
226 length: 44
227 flags: 16
228 }
229
230 Mysqlx.Resultset.Row {
231 field: "\000"
232 field: "number_sint\000"
233 }
234
235 Mysqlx.Resultset.FetchDone {
236 }
237
238 Mysqlx.Notice.Frame {
239 type: 3
240 scope: LOCAL
241 payload: "Mysqlx.Notice.SessionStateChanged { param: ROWS_AFFECTED\nvalue {\n type: V_UINT\n v_unsigned_int: 0\n}\n }"
242 }
243
244 Mysqlx.Sql.StmtExecuteOk {
245 }
246
247 send Mysqlx.Sql.StmtExecute {
248 stmt: "SELECT `number_sint`,? FROM `xtest`.`scalar_types` LIMIT 1"
249 args {
250 type: SCALAR
251 scalar {
252 type: V_STRING
253 v_string {
254 value: "number_sint"
255 }
256 }
257 }
258 compact_metadata: false
259 }
260
261 Mysqlx.Resultset.ColumnMetaData {
262 type: SINT
263 name: "number_sint"
264 original_name: "number_sint"
265 table: "scalar_types"
266 original_table: "scalar_types"
267 schema: "xtest"
268 catalog: "def"
269 length: 11
270 }
271
272 Mysqlx.Resultset.ColumnMetaData {
273 type: BYTES
274 name: "number_sint"
275 original_name: ""
276 table: ""
277 original_table: ""
278 schema: ""
279 catalog: "def"
280 collation: 255
281 length: 44
282 flags: 16
283 }
284
285 Mysqlx.Resultset.Row {
286 field: "\000"
287 field: "number_sint\000"
288 }
289
290 Mysqlx.Resultset.FetchDone {
291 }
292
293 Mysqlx.Notice.Frame {
294 type: 3
295 scope: LOCAL
296 payload: "Mysqlx.Notice.SessionStateChanged { param: ROWS_AFFECTED\nvalue {\n type: V_UINT\n v_unsigned_int: 0\n}\n }"
297 }
298
299 Mysqlx.Sql.StmtExecuteOk {
300 }
301
302 Mysqlx.Ok {
303 msg: "bye!"
304 }
305 ok
306 Too many args
307 send Mysqlx.Sql.StmtExecute {
308 stmt: "SELECT * FROM `xtest`.`scalar_types`"
309 args {
310 type: SCALAR
311 scalar {
312 type: V_STRING
313 v_string {
314 value: "%inserted \'row%"
315 }
316 }
317 }
318 }
319
320 Mysqlx.Error {
321 severity: ERROR
322 code: 5015
323 msg: "Too many arguments"
324 sql_state: "HY000"
325 }
326
327 RUN USE `xtest`
328
329 0 rows affected
330 String as identifier
331 send Mysqlx.Sql.StmtExecute {
332 stmt: "SELECT * FROM ?"
333 args {
334 type: SCALAR
335 scalar {
336 type: V_STRING
337 v_string {
338 value: "scalar_types"
339 }
340 }
341 }
342 }
343
344 Mysqlx.Error {
345 severity: ERROR
346 code: 1064
347 msg: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'\'scalar_types\'\' at line 1"
348 sql_state: "42000"
349 }
350
351 Mysqlx.Ok {
352 msg: "bye!"
353 }
354 ok
355
356 Check boolean value as argument for placeholder (document scenario)
357 -------------------------------------------------------------------
358
359 command ok
360
361 2 rows affected
362 Records: 2 Duplicates: 0 Warnings: 0
363 Verify ["SELECT doc->>'$.name' as result FROM xtest.booleans WHERE doc->'$.active' = ?" for '?=true' returns 'foo']
364 Verify ["SELECT doc->>'$.name' as result FROM xtest.booleans WHERE doc->'$.active' = ?" for '?=false' returns 'bar']
365
366 Check boolean value as argument for placeholder (classic scenario)
367 ------------------------------------------------------------------
368
369 0 rows affected
370
371 2 rows affected
372 Records: 2 Duplicates: 0 Warnings: 0
373 Verify ["SELECT `name` as result FROM xtest.classic WHERE `active` = ?" for '?=true' returns 'foo']
374 Verify ["SELECT `name` as result FROM xtest.classic WHERE `active` = ?" for '?=false' returns 'bar']
375 Mysqlx.Ok {
376 msg: "bye!"
377 }
378 ok