Debian Codegraph @

mysql-test/suite/json/t/json_prep_stmts.test

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