Debian Codegraph @

mysql-test/suite/json/r/json_prep_stmts.result

raw
1 This is used to demonstrate the use of JSON parameter values with
2 SQL prepared statements.
3 create table t1(j json);
4 set @int=123;
5 set @dec=3.14;
6 set @flt=3.14E1;
7 set @intstr='123';
8 set @quotint='"123"';
9 set @decstr='3.14';
10 set @fltstr='3.14E1';
11 set @strstr='xyz';
12 set @quotstr='"xyz"';
13 set @ts='2010-12-01 18:44:25';
14 set @null=null;
15 set @json='{"int" : 123, "dec" : 3.14, "flt" : 3.14E1, "str" : "xyz", "array" : [1, 2, 4]}';
16 Parameters are treated slightly different when they are resolved as being
17 of type JSON compared to when they are CAST as type JSON. The following
18 two prepared statements are used to demonstrate this:
19 prepare ps_insert from 'insert into t1 values (?)';
20 prepare ps_insert_cast from 'insert into t1 values (cast(? as json))';
21 The ps_insert statement fails with integer, decimal, float and datetime
22 values because Field_json::store() is not implemented for such values.
23 Integer parameter: With CAST, the integer value is converted to a
24 scalar JSON value, without cast the parameter is rejected as invald JSON.
25 execute ps_insert_cast using @int;
26 prepare ps_insert from 'insert into t1 values (?)';
27 execute ps_insert using @int;
28 ERROR 22032: Invalid JSON text: "not a JSON text, may need CAST" at position 0 in value for column 't1.j'.
29 Decimal parameter: With CAST, the decimal value is converted to a
30 scalar JSON value, without cast the parameter is rejected as invald JSON.
31 execute ps_insert_cast using @dec;
32 prepare ps_insert from 'insert into t1 values (?)';
33 execute ps_insert using @dec;
34 ERROR 22032: Invalid JSON text: "not a JSON text, may need CAST" at position 0 in value for column 't1.j'.
35 Float parameter: With CAST, the float value is converted to a
36 scalar JSON value, without cast the parameter is rejected as invald JSON.
37 execute ps_insert_cast using @flt;
38 prepare ps_insert from 'insert into t1 values (?)';
39 execute ps_insert using @flt;
40 ERROR 22032: Invalid JSON text: "not a JSON text, may need CAST" at position 0 in value for column 't1.j'.
41 String parameter containing an integer: Both statements accept the
42 parameter as a scalar JSON value.
43 execute ps_insert_cast using @intstr;
44 prepare ps_insert from 'insert into t1 values (?)';
45 execute ps_insert using @intstr;
46 String parameter containing a decimal: Both statements accept the
47 parameter as a scalar JSON value.
48 execute ps_insert_cast using @decstr;
49 prepare ps_insert from 'insert into t1 values (?)';
50 execute ps_insert using @decstr;
51 String parameter containing a float: Both statements accept the
52 parameter as a scalar JSON value.
53 execute ps_insert_cast using @fltstr;
54 prepare ps_insert from 'insert into t1 values (?)';
55 execute ps_insert using @fltstr;
56 String parameter containing a non-quoted string: None of the statements
57 will recognize this:
58 execute ps_insert_cast using @strstr;
59 ERROR 22032: Invalid JSON text in argument 1 to function cast_as_json: "Invalid value." at position 0.
60 prepare ps_insert from 'insert into t1 values (?)';
61 execute ps_insert using @strstr;
62 ERROR 22032: Invalid JSON text: "Invalid value." at position 0 in value for column 't1.j'.
63 String parameter containing a quoted string: Both statements accept
64 the parameter as a scalar JSON value.
65 execute ps_insert_cast using @quotstr;
66 prepare ps_insert from 'insert into t1 values (?)';
67 execute ps_insert using @quotstr;
68 String parameter containing a timestamp literal: None of the statements
69 will recognize this:
70 execute ps_insert_cast using @ts;
71 ERROR 22032: Invalid JSON text in argument 1 to function cast_as_json: "The document root must not be followed by other values." at position 4.
72 prepare ps_insert from 'insert into t1 values (?)';
73 execute ps_insert using @ts;
74 ERROR 22032: Invalid JSON text: "The document root must not be followed by other values." at position 4 in value for column 't1.j'.
75 Parameter that is a NULL value: accepted with both statements:
76 execute ps_insert_cast using @null;
77 prepare ps_insert from 'insert into t1 values (?)';
78 execute ps_insert using @null;
79 String parameter that is a JSON object: accepted with both statements:
80 execute ps_insert_cast using @json;
81 prepare ps_insert from 'insert into t1 values (?)';
82 execute ps_insert using @json;
83 DEALLOCATE PREPARE ps_insert_cast;
84 DEALLOCATE PREPARE ps_insert;
85 SELECT * FROM t1;
86 j
87 123
88 3.14
89 31.4
90 123
91 123
92 3.14
93 3.14
94 31.4
95 31.4
96 "xyz"
97 "xyz"
98 NULL
99 NULL
100 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
101 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
102 Compare JSON expressions with JSON parameters.
103 First using regular syntax.
104 prepare ps_get_int from "select * from t1 where json_extract(j, '$.int') = ?";
105 prepare ps_get_dec from "select * from t1 where json_extract(j, '$.dec') = ?";
106 prepare ps_get_flt from "select * from t1 where json_extract(j, '$.flt') = ?";
107 prepare ps_get_str from "select * from t1 where json_extract(j, '$.str') = ?";
108 execute ps_get_int using @int;
109 j
110 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
111 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
112 execute ps_get_int using @intstr;
113 j
114 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
115 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
116 execute ps_get_dec using @dec;
117 j
118 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
119 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
120 execute ps_get_dec using @decstr;
121 j
122 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
123 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
124 execute ps_get_flt using @flt;
125 j
126 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
127 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
128 execute ps_get_flt using @fltstr;
129 j
130 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
131 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
132 execute ps_get_str using @strstr;
133 j
134 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
135 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
136 execute ps_get_str using @quotstr;
137 j
138 DEALLOCATE PREPARE ps_get_int;
139 DEALLOCATE PREPARE ps_get_flt;
140 DEALLOCATE PREPARE ps_get_dec;
141 DEALLOCATE PREPARE ps_get_str;
142 Repeat comparison operations using "->" operator. Results should be same.
143 prepare ps_get_int from "select * from t1 where j->'$.int' = ?";
144 prepare ps_get_dec from "select * from t1 where j->'$.dec' = ?";
145 prepare ps_get_flt from "select * from t1 where j->'$.flt' = ?";
146 prepare ps_get_str from "select * from t1 where j->'$.str' = ?";
147 execute ps_get_int using @int;
148 j
149 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
150 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
151 execute ps_get_int using @intstr;
152 j
153 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
154 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
155 execute ps_get_dec using @dec;
156 j
157 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
158 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
159 execute ps_get_dec using @decstr;
160 j
161 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
162 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
163 execute ps_get_flt using @flt;
164 j
165 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
166 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
167 execute ps_get_flt using @fltstr;
168 j
169 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
170 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
171 execute ps_get_str using @intstr;
172 j
173 execute ps_get_str using @strstr;
174 j
175 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
176 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
177 execute ps_get_str using @quotstr;
178 j
179 DEALLOCATE PREPARE ps_get_int;
180 DEALLOCATE PREPARE ps_get_flt;
181 DEALLOCATE PREPARE ps_get_dec;
182 DEALLOCATE PREPARE ps_get_str;
183 Repeat comparison operations using "->>" operator, ie return an unquoted
184 value from json_extract(). All executions succeed, since the value from
185 json_extract() is converted to a character string, which can be compared
186 to the parameter value. However, using @quotstr finds no rows.
187 prepare ps_get_int from "select * from t1 where j->>'$.int' = ?";
188 prepare ps_get_dec from "select * from t1 where j->>'$.dec' = ?";
189 prepare ps_get_flt from "select * from t1 where j->>'$.flt' = ?";
190 prepare ps_get_str from "select * from t1 where j->>'$.str' = ?";
191 execute ps_get_int using @int;
192 j
193 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
194 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
195 execute ps_get_int using @intstr;
196 j
197 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
198 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
199 execute ps_get_dec using @dec;
200 j
201 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
202 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
203 execute ps_get_dec using @decstr;
204 j
205 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
206 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
207 execute ps_get_flt using @flt;
208 j
209 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
210 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
211 execute ps_get_flt using @fltstr;
212 j
213 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
214 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
215 execute ps_get_str using @strstr;
216 j
217 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
218 {"dec": 3.14, "flt": 31.4, "int": 123, "str": "xyz", "array": [1, 2, 4]}
219 execute ps_get_str using @quotstr;
220 j
221 DEALLOCATE PREPARE ps_get_int;
222 DEALLOCATE PREPARE ps_get_flt;
223 DEALLOCATE PREPARE ps_get_dec;
224 DEALLOCATE PREPARE ps_get_str;
225 DROP TABLE t1;
226 # Bug#31433168: Infinite recursion when evaluating parameter
227 PREPARE ps FROM 'SELECT CAST(? AS JSON)';
228 EXECUTE ps USING @undefined_variable;
229 CAST(? AS JSON)
230 NULL
231 SET @var = '{"a" : 1}';
232 EXECUTE ps USING @var;
233 CAST(? AS JSON)
234 {"a": 1}
235 EXECUTE ps USING @undefined_variable;
236 CAST(? AS JSON)
237 NULL
238 DEALLOCATE PREPARE ps;
239 # Bug#31406724: Crash in sql_scalar_to_json after many recursive calls
240 CREATE TABLE t(a INTEGER);
241 INSERT INTO t VALUES (0),(1),(2);
242 PREPARE s FROM 'SELECT * FROM t WHERE JSON_LENGTH(?, a BETWEEN ? AND ?)';
243 EXECUTE s USING @undefined_variable, @undefined_variable, @undefined_variable;
244 a
245 DEALLOCATE PREPARE s;
246 DROP TABLE t;
247 # Bug#31667405: Incorrect prepared statement outcome with numeric strings in JSON
248 PREPARE ps FROM 'SELECT CAST(''"123"'' AS JSON) = JSON_EXTRACT(?, ''$'')';
249 EXECUTE ps USING @intstr;
250 CAST('"123"' AS JSON) = JSON_EXTRACT(?, '$')
251 0
252 EXECUTE ps USING @quotint;
253 CAST('"123"' AS JSON) = JSON_EXTRACT(?, '$')
254 1
255 DEALLOCATE PREPARE ps;
256 PREPARE ps FROM 'SELECT CAST(''"123"'' AS JSON) IN (?, ?)';
257 EXECUTE ps USING @intstr, @intstr;
258 CAST('"123"' AS JSON) IN (?, ?)
259 1
260 EXECUTE ps USING @quotint, @quotint;
261 CAST('"123"' AS JSON) IN (?, ?)
262 0
263 DEALLOCATE PREPARE ps;