Debian Codegraph @

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

raw
1 ALTER DATABASE test CHARACTER SET latin1 COLLATE latin1_swedish_ci;
2 #
3 # WL#8867: Add JSON_TABLE table function
4 #
5 select * from
6 json_table(
7 '[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
8 '$[*]' columns (id for ordinality,
9 jpath varchar(100) path '\$.a',
10 jexst int exists path '\$.b')
11 ) as tt;
12 id jpath jexst
13 1 3 0
14 2 2 0
15 3 NULL 1
16 4 0 0
17 select * from
18 json_table(
19 '[{"x":"3"},{"a":2},{"b":1},{"a":0}]',
20 '$[*]' columns (id for ordinality,
21 jpath varchar(100) path '$.a' default '[99]' on error,
22 jexst int exists path '$.b')
23 ) as tt;
24 id jpath jexst
25 1 NULL 0
26 2 2 0
27 3 NULL 1
28 4 0 0
29 select * from
30 json_table(
31 '[{"x":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
32 '$[*]' columns (id for ordinality,
33 jpath varchar(100) path '$.a'
34 default '33' on empty
35 default '66' on error,
36 jsn_path json path '\$.a' default '{"x":33}' on empty,
37 jexst int exists path '\$.b')
38 ) as tt;
39 id jpath jsn_path jexst
40 1 33 {"x":33} 0
41 2 2 2 0
42 3 33 {"x":33} 1
43 4 0 0 0
44 5 66 [1,2] 0
45 select * from
46 json_table(
47 '[{"a":"3"},{"a":2},{"b":1},{"a":0.33},{"a":"asd"}]',
48 '$[*]' columns (id for ordinality,
49 jpath_i int path '$.a'
50 default '33' on empty
51 default '66' on error,
52 jpath_r real path '$.a'
53 default '33.3' on empty
54 default '77.7' on error,
55 jsn_path json path '$.a' default '{"x":33}' on empty,
56 jexst int exists path '$.b')
57 ) as tt;
58 id jpath_i jpath_r jsn_path jexst
59 1 3 3 "3" 0
60 2 2 2 2 0
61 3 33 33.3 {"x":33} 1
62 4 0 0.33 0.33 0
63 5 0 0 "asd" 0
64 Warnings:
65 Warning 1366 Incorrect integer value: 'asd' for column `*any*`.`json_table`.`jpath_i` at row 5
66 Warning 1366 Incorrect double value: 'asd' for column `*any*`.`json_table`.`jpath_r` at row 5
67 select * from
68 json_table(
69 '[{"x":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
70 '$[*]' columns (id for ordinality,
71 jpath varchar(100) path '$.a'
72 default '33' on empty
73 default '66' on error,
74 jsn_path json path '$.a' default '{"x":33}' on empty,
75 jexst int exists path '$.b')
76 ) as tt;
77 id jpath jsn_path jexst
78 1 33 {"x":33} 0
79 2 2 2 0
80 3 33 {"x":33} 1
81 4 0 0 0
82 5 66 [1,2] 0
83 select * from
84 json_table(
85 '[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
86 '$[*]' columns (id for ordinality,
87 jpath varchar(100) path '$.a',
88 json_path json path '$.a',
89 jexst int exists path '$.b')
90 ) as tt;
91 id jpath json_path jexst
92 1 3 "3" 0
93 2 2 2 0
94 3 NULL NULL 1
95 4 0 0 0
96 select * from
97 json_table(
98 '[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
99 '$[*]' columns (id for ordinality,
100 jpath varchar(100) path '$.a',
101 json_path json path '$.a',
102 jexst int exists path '$.b')
103 ) as tt
104 where id = 3;
105 id jpath json_path jexst
106 3 NULL NULL 1
107 select * from
108 json_table(
109 '[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
110 '$[*]' columns (id for ordinality,
111 jpath varchar(100) path '$.a' error on empty,
112 jexst int exists path '$.b')
113 ) as tt;
114 ERROR HY000: Field 'jpath' can't be set for JSON_TABLE 'tt'.
115 select * from
116 json_table(
117 '[{"a":"3"},{"a":2},{"a":1},{"a":[0,1]}]',
118 '$[*]' columns (id for ordinality,
119 jpath varchar(100) path '$.a' error on error,
120 jexst int exists path '$.b')
121 ) as tt;
122 ERROR HY000: Can't store an array or an object in the scalar column 'jpath' of JSON_TABLE 'tt'.
123 select * from
124 json_table(
125 '!#@$!@#$',
126 '$[*]' columns (id for ordinality,
127 jpath varchar(100) path '$.a',
128 jexst int exists path '$.b')
129 ) as tt;
130 ERROR HY000: Syntax error in JSON text in argument 1 to function 'JSON_TABLE' at position 1
131 select * from
132 json_table(
133 '[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
134 '$[*]' columns (id for ordinality,
135 id for ordinality)
136 ) as tt;
137 ERROR 42S21: Duplicate column name 'id'
138 select * from
139 json_table(
140 '[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
141 '$[*]' columns (id for ordinality,
142 _id for ordinality)
143 ) as tt;
144 id _id
145 1 1
146 2 2
147 3 3
148 4 4
149 select * from
150 json_table(
151 '[
152 {"a":"3", "n": { "l": 1}},
153 {"a":2, "n": { "l": 1}},
154 {"b":1, "n": { "l": 1}},
155 {"a":0, "n": { "l": 1}}
156 ]',
157 '$[*]' columns (
158 id for ordinality,
159 jpath varchar(100) path '$.a',
160 jexst int exists path '$.b',
161 nested path '$.n' columns (
162 id_n for ordinality,
163 jpath_n varchar(50) path '$.l')
164 )
165 ) as tt;
166 id jpath jexst id_n jpath_n
167 1 3 0 1 1
168 2 2 0 1 1
169 3 NULL 1 1 1
170 4 0 0 1 1
171 explain format=json
172 select * from
173 json_table(
174 '[
175 {"a":"3", "n": { "l": 1}},
176 {"a":2, "n": { "l": 1}},
177 {"b":1, "n": { "l": 1}},
178 {"a":0, "n": { "l": 1}}
179 ]',
180 '$[*]' columns (
181 id for ordinality,
182 jpath varchar(100) path '$.a',
183 jexst int exists path '$.b',
184 nested path '$.n' columns (
185 id_n for ordinality,
186 jpath_n varchar(50) path '$.l')
187 )
188 ) as tt;
189 EXPLAIN
190 {
191 "query_block": {
192 "select_id": 1,
193 "cost": "COST_REPLACED",
194 "nested_loop": [
195 {
196 "table": {
197 "table_name": "tt",
198 "access_type": "ALL",
199 "loops": 1,
200 "rows": 40,
201 "cost": "COST_REPLACED",
202 "filtered": 100,
203 "table_function": "json_table"
204 }
205 }
206 ]
207 }
208 }
209 select * from
210 json_table(
211 '[
212 {"a":2, "n": [{ "l": 1}, {"l": 11}]},
213 {"a":1, "n": [{ "l": 2}, {"l": 22}]}
214 ]',
215 '$[*]' columns (
216 id for ordinality,
217 jpath varchar(50) path '$.a',
218 nested path '$.n[*]' columns (
219 id_n for ordinality,
220 jpath_n varchar(50) path '$.l'),
221 nested path '$.n[*]' columns (
222 id_m for ordinality,
223 jpath_m varchar(50) path '$.l')
224 )
225 ) as tt;
226 id jpath id_n jpath_n id_m jpath_m
227 1 2 1 1 NULL NULL
228 1 2 2 11 NULL NULL
229 1 2 NULL NULL 1 1
230 1 2 NULL NULL 2 11
231 2 1 1 2 NULL NULL
232 2 1 2 22 NULL NULL
233 2 1 NULL NULL 1 2
234 2 1 NULL NULL 2 22
235 select * from json_table(
236 '[
237 {"a":"3", "n": ["b","a","c"]},
238 {"a":2, "n": [1,2]},
239 {"b":1, "n": ["zzz"]},
240 {"a":0, "n": [0.1, 0.02]}
241 ]',
242 '$[*]' columns (
243 id for ordinality,
244 jpath varchar(100) path '$.a',
245 jexst int exists path '$.b',
246 nested path '$.n[*]' columns (
247 id_n for ordinality,
248 jpath_n varchar(50) path '$')
249 )
250 ) as tt;
251 id jpath jexst id_n jpath_n
252 1 3 0 1 b
253 1 3 0 2 a
254 1 3 0 3 c
255 2 2 0 1 1
256 2 2 0 2 2
257 3 NULL 1 1 zzz
258 4 0 0 1 0.1
259 4 0 0 2 0.02
260 select * from json_table(
261 '[
262 {"a":"3", "n": ["b","a","c"]},
263 {"a":2, "n": [1,2]},
264 {"b":1, "n": ["zzz"]},
265 {"a":0, "n": [0.1, 0.02]}
266 ]',
267 '$[*]' columns (
268 id for ordinality,
269 jpath varchar(100) path '$.a',
270 jexst int exists path '$.b',
271 nested path '$.n[*]' columns (
272 id_n1 for ordinality,
273 jpath_n1 varchar(50) path '$') ,
274 nested path '$.n[*]' columns (
275 id_n2 for ordinality,
276 jpath_n2 varchar(50) path '$')
277 )
278 ) as tt;
279 id jpath jexst id_n1 jpath_n1 id_n2 jpath_n2
280 1 3 0 1 b NULL NULL
281 1 3 0 2 a NULL NULL
282 1 3 0 3 c NULL NULL
283 1 3 0 NULL NULL 1 b
284 1 3 0 NULL NULL 2 a
285 1 3 0 NULL NULL 3 c
286 2 2 0 1 1 NULL NULL
287 2 2 0 2 2 NULL NULL
288 2 2 0 NULL NULL 1 1
289 2 2 0 NULL NULL 2 2
290 3 NULL 1 1 zzz NULL NULL
291 3 NULL 1 NULL NULL 1 zzz
292 4 0 0 1 0.1 NULL NULL
293 4 0 0 2 0.02 NULL NULL
294 4 0 0 NULL NULL 1 0.1
295 4 0 0 NULL NULL 2 0.02
296 select * from json_table(
297 '[
298 {"a":"3", "n": [ {"ll":["b1","b2","b3"]}, {"ll": ["a1","a2"]},
299 {"ll":["c"]} ]},
300 {"a":2, "n": [{"ll":[1,11,111]},{"ll":[2]}]},
301 {"b":1, "n": [{"ll":["zzz"]}]},
302 {"a":0, "n": [{"ll":[0.1,0.01]}, {"ll":[0.02,0.002,0.0002]}]}
303 ]',
304 '$[*]' columns (
305 id1 for ordinality,
306 jpath varchar(100) path '$.a',
307 jexst int exists path '$.b',
308 nested path '$.n[*]' columns (
309 id2 for ordinality,
310 nested path '$.ll[*]' columns (
311 id3 for ordinality,
312 jpath_3 varchar(50) path '$')
313 ),
314 nested path '$.n[*]' columns (
315 id4 for ordinality,
316 jpath_4 json path '$')
317 )
318 ) as tt;
319 id1 jpath jexst id2 id3 jpath_3 id4 jpath_4
320 1 3 0 1 1 b1 NULL NULL
321 1 3 0 1 2 b2 NULL NULL
322 1 3 0 1 3 b3 NULL NULL
323 1 3 0 2 1 a1 NULL NULL
324 1 3 0 2 2 a2 NULL NULL
325 1 3 0 3 1 c NULL NULL
326 1 3 0 NULL NULL NULL 1 {"ll":["b1","b2","b3"]}
327 1 3 0 NULL NULL NULL 2 {"ll": ["a1","a2"]}
328 1 3 0 NULL NULL NULL 3 {"ll":["c"]}
329 2 2 0 1 1 1 NULL NULL
330 2 2 0 1 2 11 NULL NULL
331 2 2 0 1 3 111 NULL NULL
332 2 2 0 2 1 2 NULL NULL
333 2 2 0 NULL NULL NULL 1 {"ll":[1,11,111]}
334 2 2 0 NULL NULL NULL 2 {"ll":[2]}
335 3 NULL 1 1 1 zzz NULL NULL
336 3 NULL 1 NULL NULL NULL 1 {"ll":["zzz"]}
337 4 0 0 1 1 0.1 NULL NULL
338 4 0 0 1 2 0.01 NULL NULL
339 4 0 0 2 1 0.02 NULL NULL
340 4 0 0 2 2 0.002 NULL NULL
341 4 0 0 2 3 0.0002 NULL NULL
342 4 0 0 NULL NULL NULL 1 {"ll":[0.1,0.01]}
343 4 0 0 NULL NULL NULL 2 {"ll":[0.02,0.002,0.0002]}
344 ord should be 1,1,1,2, which tells that first two values of 'l' are
345 from the same object, and next two are from different objects
346 SELECT *
347 FROM JSON_TABLE(
348 '[{"a": "a_val",
349 "b": [
350 {"c": "c_val",
351 "l": [1,2]}
352 ]
353 }, {"a": "a_val",
354 "b": [
355 {"c": "c_val",
356 "l": [11]},
357 {"c": "c_val",
358 "l": [22]}
359 ]
360 }]',
361 '$[*]' COLUMNS (
362 apath VARCHAR(10) PATH '$.a',
363 NESTED PATH '$.b[*]' COLUMNS (
364 bpath VARCHAR(10) PATH '$.c',
365 ord FOR ORDINALITY,
366 NESTED PATH '$.l[*]' COLUMNS (
367 lpath varchar(10) PATH '$'
368 )
369 )
370 )) as jt;
371 apath bpath ord lpath
372 a_val c_val 1 1
373 a_val c_val 1 2
374 a_val c_val 1 11
375 a_val c_val 2 22
376 CREATE TABLE jt( i JSON );
377 SELECT * FROM jt, JSON_TABLE(jt.i, '$' COLUMNS (a INT PATH '$')) AS tt
378 WHERE a=1;
379 i a
380 EXPLAIN SELECT * FROM jt, JSON_TABLE(jt.i, '$' COLUMNS (a INT PATH '$')) AS tt
381 WHERE a=1;
382 id select_type table type possible_keys key key_len ref rows Extra
383 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
384 SELECT * FROM (
385 SELECT * FROM jt, JSON_TABLE(jt.i, '$' COLUMNS (a INT PATH '$')) AS tt
386 WHERE a=1) AS ttt;
387 i a
388 EXPLAIN SELECT * FROM (
389 SELECT * FROM jt, JSON_TABLE(jt.i, '$' COLUMNS (a INT PATH '$')) AS tt
390 WHERE a=1) AS ttt;
391 id select_type table type possible_keys key key_len ref rows Extra
392 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
393 DROP TABLE jt;
394 CREATE VIEW v AS
395 SELECT * FROM JSON_TABLE('[1,2,3]',
396 '$[*]' COLUMNS (num INT PATH '$.a'
397 DEFAULT '123' ON EMPTY
398 DEFAULT '456' ON ERROR)) AS jt;
399 SELECT * FROM v;
400 num
401 123
402 123
403 123
404 SHOW CREATE VIEW v;
405 View Create View character_set_client collation_connection
406 v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `jt`.`num` AS `num` from JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (`num` int(11) PATH '$.a' DEFAULT '123' ON EMPTY DEFAULT '456' ON ERROR)) `jt` latin1 latin1_swedish_ci
407 DROP VIEW v;
408 SELECT * FROM JSON_TABLE('"asdf"',
409 '$' COLUMNS (a INT PATH '$' ERROR ON ERROR)) AS jt;
410 a
411 0
412 Warnings:
413 Warning 1366 Incorrect integer value: 'asdf' for column `*any*`.`json_table`.`a` at row 1
414 SELECT * FROM
415 JSON_TABLE('[{"a":1},{"a":2}]',
416 '$' COLUMNS (a INT PATH '$[*].a' ERROR ON ERROR)) AS jt;
417 ERROR HY000: Can't store multiple matches of the path in the column 'a' of JSON_TABLE 'jt'.
418 SELECT * FROM
419 JSON_TABLE('[{"a":1},{"a":2}]',
420 '$' COLUMNS (a JSON PATH '$[*].a' ERROR ON ERROR)) AS jt;
421 ERROR HY000: Can't store multiple matches of the path in the column 'a' of JSON_TABLE 'jt'.
422 SELECT * FROM
423 JSON_TABLE('123.456', '$' COLUMNS (a DECIMAL(2,1) PATH '$' ERROR ON ERROR)) AS jt;
424 a
425 9.9
426 Warnings:
427 Warning 1264 Out of range value for column 'a' at row 1
428 SELECT * FROM
429 JSON_TABLE('123.456', '$' COLUMNS (a DECIMAL(2,1) PATH '$')) AS jt;
430 a
431 9.9
432 Warnings:
433 Warning 1264 Out of range value for column 'a' at row 1
434 SELECT * FROM
435 JSON_TABLE('{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{"a":1,"b":{}}}}}}}}}}}}}}}}}}}',
436 '$' COLUMNS (i0 INT PATH '$.a',
437 NESTED PATH '$.b' COLUMNS (i1 INT PATH '$.a',
438 NESTED PATH '$.b' COLUMNS (i2 INT PATH '$.a',
439 NESTED PATH '$.b' COLUMNS (i3 INT PATH '$.a',
440 NESTED PATH '$.b' COLUMNS (i4 INT PATH '$.a',
441 NESTED PATH '$.b' COLUMNS (i5 INT PATH '$.a',
442 NESTED PATH '$.b' COLUMNS (i6 INT PATH '$.a',
443 NESTED PATH '$.b' COLUMNS (i7 INT PATH '$.a',
444 NESTED PATH '$.b' COLUMNS (i8 INT PATH '$.a',
445 NESTED PATH '$.b' COLUMNS (i9 INT PATH '$.a',
446 NESTED PATH '$.b' COLUMNS (i10 INT PATH '$.a',
447 NESTED PATH '$.b' COLUMNS (i11 INT PATH '$.a',
448 NESTED PATH '$.b' COLUMNS (i12 INT PATH '$.a',
449 NESTED PATH '$.b' COLUMNS (i13 INT PATH '$.a',
450 NESTED PATH '$.b' COLUMNS (i14 INT PATH '$.a',
451 NESTED PATH '$.b' COLUMNS (i15 INT PATH '$.a',
452 NESTED PATH '$.b' COLUMNS (i16 INT PATH '$.a',
453 NESTED PATH '$.b' COLUMNS (i17 INT PATH '$.a',
454 NESTED PATH '$.b' COLUMNS (i18 INT PATH '$.a',
455 NESTED PATH '$.b' COLUMNS (i19 INT PATH '$.a',
456 NESTED PATH '$.b' COLUMNS (i20 INT PATH '$.a'
457 )))))))))))))))))))))) jt;
458 i0 i1 i2 i3 i4 i5 i6 i7 i8 i9 i10 i11 i12 i13 i14 i15 i16 i17 i18 i19 i20
459 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 NULL NULL NULL
460 CREATE TABLE t1(id int, jd JSON);
461 INSERT INTO t1 values (1, '[1,3,5]'),(2,'[2,4,6]');
462 SELECT id, jt.* FROM t1,
463 JSON_TABLE(jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
464 val INT PATH '$')) AS jt;
465 id jid val
466 1 1 1
467 1 2 3
468 1 3 5
469 2 1 2
470 2 2 4
471 2 3 6
472 SELECT /*+ JOIN_ORDER(jt, t1) */ id, jt.*
473 FROM t1,
474 JSON_TABLE(jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
475 val INT PATH '$')) AS jt;
476 id jid val
477 1 1 1
478 1 2 3
479 1 3 5
480 2 1 2
481 2 2 4
482 2 3 6
483 EXPLAIN SELECT /*+ JOIN_ORDER(jt, t1) */ id, jt.*
484 FROM t1,
485 JSON_TABLE(jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
486 val INT PATH '$')) AS jt;
487 id select_type table type possible_keys key key_len ref rows Extra
488 1 SIMPLE t1 ALL NULL NULL NULL NULL 2
489 1 SIMPLE jt ALL NULL NULL NULL NULL 40 Table function: json_table
490 SELECT /*+ JOIN_ORDER(t2,jt) */ t1.id, t2.id, jt.*
491 FROM t1,
492 JSON_TABLE(t1.jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
493 val INT PATH '$')) AS jt,
494 t1 AS t2;
495 id id jid val
496 1 1 1 1
497 1 1 2 3
498 1 1 3 5
499 1 2 1 1
500 1 2 2 3
501 1 2 3 5
502 2 1 1 2
503 2 1 2 4
504 2 1 3 6
505 2 2 1 2
506 2 2 2 4
507 2 2 3 6
508 EXPLAIN SELECT t1.id, t2.id, jt.*
509 FROM t1,
510 JSON_TABLE(t1.jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
511 val INT PATH '$')) AS jt,
512 t1 AS t2;
513 id select_type table type possible_keys key key_len ref rows Extra
514 1 SIMPLE t1 ALL NULL NULL NULL NULL 2
515 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
516 1 SIMPLE jt ALL NULL NULL NULL NULL 40 Table function: json_table
517 EXPLAIN SELECT /*+ JOIN_ORDER(t2,jt) */ t1.id, t2.id, jt.*
518 FROM t1,
519 JSON_TABLE(t1.jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
520 val INT PATH '$')) AS jt,
521 t1 AS t2;
522 id select_type table type possible_keys key key_len ref rows Extra
523 1 SIMPLE t1 ALL NULL NULL NULL NULL 2
524 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using join buffer (flat, BNL join)
525 1 SIMPLE jt ALL NULL NULL NULL NULL 40 Table function: json_table
526 SELECT * FROM t1 WHERE id IN
527 (SELECT * FROM JSON_TABLE('[1,2]', '$[*]' COLUMNS
528 (id INT PATH '$')) AS jt);
529 id jd
530 1 [1,3,5]
531 2 [2,4,6]
532 EXPLAIN SELECT * FROM t1 WHERE id IN
533 (SELECT * FROM JSON_TABLE('[1,2]', '$[*]' COLUMNS
534 (id INT PATH '$')) AS jt);
535 id select_type table type possible_keys key key_len ref rows Extra
536 1 PRIMARY t1 ALL NULL NULL NULL NULL 2
537 1 PRIMARY jt ALL NULL NULL NULL NULL 40 Table function: json_table; Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
538 DROP TABLE t1;
539 SELECT * FROM JSON_TABLE('"asdf"', '$' COLUMNS(
540 tm TIME PATH '$',
541 dt DATE PATH '$',
542 i INT PATH '$',
543 f FLOAT PATH '$',
544 d DECIMAL PATH '$')) AS jt;
545 tm dt i f d
546 00:00:00 0000-00-00 0 0 0
547 Warnings:
548 Warning 1265 Data truncated for column 'tm' at row 1
549 Warning 1265 Data truncated for column 'dt' at row 1
550 Warning 1366 Incorrect integer value: 'asdf' for column `*any*`.`json_table`.`i` at row 1
551 Warning 1366 Incorrect double value: 'asdf' for column `*any*`.`json_table`.`f` at row 1
552 Warning 1366 Incorrect decimal value: 'asdf' for column `*any*`.`json_table`.`d` at row 1
553 SELECT * FROM
554 JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT NULL ON EMPTY)) jt;
555 x
556 NULL
557 SELECT * FROM
558 JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT NULL ON ERROR)) jt;
559 x
560 NULL
561 SELECT * FROM
562 JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT 0 ON EMPTY)) jt;
563 x
564 0
565 SELECT * FROM
566 JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT 0 ON ERROR)) jt;
567 x
568 NULL
569 SELECT * FROM
570 JSON_TABLE('{}', '$' COLUMNS (x DATE
571 PATH '$.x'
572 DEFAULT DATE'2020-01-01' ON EMPTY)) jt;
573 x
574 2020-01-01
575 SELECT * FROM
576 JSON_TABLE('{}', '$' COLUMNS (x DATE
577 PATH '$.x'
578 DEFAULT DATE'2020-01-01' ON ERROR)) jt;
579 x
580 NULL
581 #
582 # Bug#25413069: SIG11 IN CHECK_COLUMN_GRANT_IN_TABLE_REF
583 #
584 SELECT a FROM JSON_TABLE(abc, '$[*]' COLUMNS ( a int path '$.a')) AS jt;
585 ERROR 42S22: Unknown column 'abc' in 'JSON_TABLE'
586 #
587 # Bug#25420680: ASSERTION `THD->IS_ERROR()' FAILED IN SQL/SQL_SELECT.CC
588 #
589 SELECT * FROM JSON_TABLE('{"a":"2017-11-1"}',
590 '$' COLUMNS (jpath DATE PATH '$.a')) AS jt;
591 jpath
592 2017-11-01
593 #
594 # Bug#25413826: ASSERTION `TABLE_LIST->ALIAS' FAILED
595 #
596 SELECT * FROM JSON_TABLE('[{"a": 1, "b": 2}]',
597 '$[*]' COLUMNS ( a int path '$.b'));
598 ERROR HY000: Every table function must have an alias.
599 #
600 # Bug#25421464: ASSERTION `!STRCMP(TABLE_REF->TABLE_NAME, ...
601 #
602 CREATE VIEW v1 AS
603 SELECT * FROM JSON_TABLE('[{"a": 1, "b": 2}]',
604 '$[*]' COLUMNS ( a INT PATH '$.b')) AS jt;
605 SELECT * FROM v1;
606 a
607 2
608 SHOW CREATE VIEW v1;
609 View Create View character_set_client collation_connection
610 v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `jt`.`a` AS `a` from JSON_TABLE('[{"a": 1, "b": 2}]', '$[*]' COLUMNS (`a` int(11) PATH '$.b')) `jt` latin1 latin1_swedish_ci
611 DROP VIEW v1;
612 #
613 # Bug#25427457: ASSERTION `!((*REG_FIELD)->FLAGS & 16)'
614 #
615 SELECT * FROM JSON_TABLE('{"a":"1"}',
616 '$' COLUMNS (jpath JSON PATH '$.a',
617 o FOR ORDINALITY)) AS jt
618 WHERE o = 1;
619 jpath o
620 "1" 1
621 #
622 # Bug#25427982: ASSERTION `DERIVED' FAILED IN SQL/TABLE.H
623 #
624 SELECT je,o FROM JSON_TABLE('{"a":"1"}',
625 '$' COLUMNS (o FOR ORDINALITY,
626 je BIGINT EXISTS PATH '$.a')) AS jt
627 GROUP BY je;
628 je o
629 1 1
630 SELECT je,COUNT(o) FROM JSON_TABLE('{"a":"1"}',
631 '$' COLUMNS (o FOR ORDINALITY,
632 je BIGINT EXISTS PATH '$.a')) AS jt
633 GROUP BY je;
634 je COUNT(o)
635 1 1
636 #
637 # Bug#25413194: ASSERTION `!(WANT_PRIVILEGE & ~(GRANT->WANT_PRIVILEGE
638 #
639 CREATE TABLE t1 (j JSON);
640 SELECT * FROM t1,JSON_TABLE(t1.j, '$[*]' COLUMNS ( a int path '$.b')) AS jt;
641 j a
642 DROP TABLE t1;
643 #
644 # Bug#25460537:SIG 11 IN NEXT_FAST AT SQL/SQL_LIST.H
645 #
646 PREPARE STMT FROM
647 "SELECT * FROM JSON_TABLE(
648 \'[{\"a\":\"3\"},{\"a\":2},{\"b\":1},{\"a\":0}]\',
649 \'$[*]\' COLUMNS (id
650 FOR ORDINALITY,
651 jpath VARCHAR(100) PATH \'$.a\',
652 jexst INT EXISTS PATH \'$.b\')
653 ) as tt";
654 EXECUTE STMT;
655 id jpath jexst
656 1 3 0
657 2 2 0
658 3 NULL 1
659 4 0 0
660 EXECUTE STMT;
661 id jpath jexst
662 1 3 0
663 2 2 0
664 3 NULL 1
665 4 0 0
666 DEALLOCATE PREPARE stmt;
667 #
668 # Bug#25522353: SIG 11 IN JOIN::MAKE_JOIN_PLAN | SQL/SQL_OPTIMIZER.CC
669 #
670 CREATE TABLE t1 (id INT, jc JSON);
671 SELECT * FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
672 (id FOR ORDINALITY)) as jt ON t1.jc=jt.id;
673 ERROR 42S22: Unknown column 't1.jc' in 'JSON_TABLE'
674 SELECT * FROM JSON_TABLE(t1.jc, '$' COLUMNS
675 (id FOR ORDINALITY)) as jt LEFT JOIN t1 ON t1.jc=jt.id;
676 ERROR 42S22: Unknown column 't1.jc' in 'JSON_TABLE'
677 SELECT * FROM JSON_TABLE(t1.jc, '$' COLUMNS
678 (id FOR ORDINALITY)) as jt RIGHT JOIN t1 ON t1.jc=jt.id;
679 id id jc
680 SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
681 (id FOR ORDINALITY)) as jt ON t1.jc=jt.id;
682 id jc id
683 EXPLAIN SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
684 (id FOR ORDINALITY)) as jt ON t1.jc=jt.id;
685 id select_type table type possible_keys key key_len ref rows Extra
686 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
687 SELECT * FROM t1 t1o RIGHT JOIN t1 ON t1o.id=t1.id
688 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
689 (id FOR ORDINALITY)) as jt ON t1.jc=jt.id;
690 id jc id jc id
691 EXPLAIN SELECT * FROM t1 t1o RIGHT JOIN t1 ON t1o.id=t1.id
692 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
693 (id FOR ORDINALITY)) as jt ON t1.jc=jt.id;
694 id select_type table type possible_keys key key_len ref rows Extra
695 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
696 SELECT * FROM t1 AS t1o RIGHT JOIN
697 (t1 AS t1i JOIN JSON_TABLE(t1o.jc, '$' COLUMNS
698 (id FOR ORDINALITY)) as jt ON t1i.jc=jt.id)
699 ON t1o.id=t1i.id;
700 ERROR 42S22: Unknown column 't1o.jc' in 'JSON_TABLE'
701 SELECT * FROM t1 AS t1o RIGHT JOIN
702 (t1 AS t1i RIGHT JOIN JSON_TABLE(t1o.jc, '$' COLUMNS
703 (id FOR ORDINALITY)) as jt ON t1i.jc=jt.id)
704 ON t1o.id=t1i.id;
705 ERROR 42S22: Unknown column 't1o.jc' in 'JSON_TABLE'
706 WITH qn AS
707 (SELECT jt.* FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
708 (id FOR ORDINALITY)) as jt ON t1.jc=jt.id)
709 SELECT * from qn;
710 ERROR 42S22: Unknown column 't1.jc' in 'JSON_TABLE'
711 WITH qn AS
712 (SELECT 1 UNION
713 SELECT jt.id FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
714 (id FOR ORDINALITY)) as jt ON t1.jc=jt.id)
715 SELECT * from qn;
716 ERROR 42S22: Unknown column 't1.jc' in 'JSON_TABLE'
717 SELECT * FROM t1 AS t1o RIGHT JOIN
718 (t1 AS t1i JOIN JSON_TABLE(t1o.jc, '$' COLUMNS
719 (id FOR ORDINALITY)) as jt ON t1i.jc=jt.id)
720 ON t1o.id=t1i.id;
721 ERROR 42S22: Unknown column 't1o.jc' in 'JSON_TABLE'
722 SELECT * FROM t1 AS t1o RIGHT JOIN
723 (t1 AS t1i RIGHT JOIN JSON_TABLE(t1o.jc, '$' COLUMNS
724 (id FOR ORDINALITY)) as jt ON t1i.jc=jt.id)
725 ON t1o.id=t1i.id;
726 ERROR 42S22: Unknown column 't1o.jc' in 'JSON_TABLE'
727 INSERT INTO t1 VALUES(1,"1"),(2,"4"),(3,"3");
728 ANALYZE TABLE t1;
729 Table Op Msg_type Msg_text
730 test.t1 analyze status Engine-independent statistics collected
731 test.t1 analyze Warning Engine-independent statistics are not collected for column 'jc'
732 test.t1 analyze status OK
733 SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
734 (id INT PATH '$')) as jt ON t1.id=jt.id;
735 id jc id
736 1 1 1
737 2 4 NULL
738 3 3 3
739 EXPLAIN SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
740 (id INT PATH '$')) as jt ON t1.id=jt.id;
741 id select_type table type possible_keys key key_len ref rows Extra
742 1 SIMPLE t1 ALL NULL NULL NULL NULL 3
743 1 SIMPLE jt ALL NULL NULL NULL NULL 40 Table function: json_table; Using where
744 SELECT * FROM t1
745 LEFT JOIN
746 JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1.jc=jt.id
747 RIGHT JOIN
748 JSON_TABLE(jt.id, '$' COLUMNS (id FOR ORDINALITY)) as jt1 ON jt.id=jt1.id;
749 ERROR 42S22: Unknown column 'jt.id' in 'JSON_TABLE'
750 DROP TABLE t1;
751 #
752 # Bug#25525409: ASSERTION `TABLE_LIST->TABLE' FAILED IN SQL/SQL_BASE.CC
753 #
754 SELECT * FROM JSON_TABLE( ( SELECT a ) , '$.*' COLUMNS (col1 FOR ORDINALITY) )
755 AS alias1;
756 ERROR 42S22: Unknown column 'a' in 'SELECT'
757 SELECT * FROM JSON_TABLE( ( SELECT 1 ) , '$.*' COLUMNS (col1 FOR ORDINALITY) )
758 AS alias1;
759 col1
760 SELECT * FROM JSON_TABLE( ( SUM(1) ) , '$.*' COLUMNS (col1 FOR ORDINALITY) )
761 AS alias1;
762 ERROR HY000: Invalid use of group function
763 #
764 # Bug# #25472875: ERROR SHOULD BE THROWN FOR INCORRECT VALUES
765 #
766 SELECT *
767 FROM JSON_TABLE('{"a":"1993-01-01"}',
768 '$' COLUMNS (jp DATE PATH '$.b' DEFAULT '1000' ON EMPTY))
769 AS jt;
770 jp
771 0000-00-00
772 Warnings:
773 Warning 1265 Data truncated for column 'jp' at row 1
774 #
775 # Bug#25532429: INVALID JSON ERROR NOT THROWN WITH EMPTY TABLES JOIN
776 #
777 CREATE TABLE t1(j JSON);
778 SELECT * FROM t1,
779 JSON_TABLE( 'dqwfjqjf' , '$[*]' COLUMNS (col5 FOR ORDINALITY) ) AS alias7;
780 j col5
781 DROP TABLE t1;
782 #
783 # Bug#25540370: SIG 11 IN SHOW_SQL_TYPE|SQL/SQL_SHOW.CC:7063
784 #
785 SELECT * FROM
786 JSON_TABLE('[3.14159]',
787 '$[*]' COLUMNS (col18 CHAR(70) PATH '$')
788 ) AS alias2;
789 col18
790 3.14159
791 # Too short field causes truncation, error and triggers ON ERROR clause
792 SELECT * FROM
793 JSON_TABLE('["3.14159"]',
794 '$[*]' COLUMNS (col18 CHAR(6) PATH '$')
795 ) AS alias2;
796 col18
797 3.1415
798 Warnings:
799 Warning 1265 Data truncated for column 'col18' at row 1
800 #Truncated space doesn't trigger ON ERROR
801 SELECT * FROM
802 JSON_TABLE('["3.14159 "]',
803 '$[*]' COLUMNS (col18 CHAR(7) PATH '$')
804 ) AS alias2;
805 col18
806 3.14159
807 SELECT * FROM
808 JSON_TABLE('[3.14159]',
809 '$[*]' COLUMNS (col18 CHAR(255) PATH '$')
810 ) AS alias2;
811 col18
812 3.14159
813 SELECT * FROM
814 JSON_TABLE('[3.14159]',
815 '$[*]' COLUMNS (col18 VARCHAR(70) PATH '$')
816 ) AS alias2;
817 col18
818 3.14159
819 SELECT * FROM
820 JSON_TABLE('[3.14159]',
821 '$[*]' COLUMNS (col18 VARCHAR(255) PATH '$')
822 ) AS alias2;
823 col18
824 3.14159
825 SELECT * FROM
826 JSON_TABLE('[3.14159]',
827 '$[*]' COLUMNS (col18 FLOAT PATH '$')
828 ) AS alias2;
829 col18
830 3.14159
831 SELECT * FROM
832 JSON_TABLE('[3.14159]',
833 '$[*]' COLUMNS (col18 DOUBLE PATH '$')
834 ) AS alias2;
835 col18
836 3.14159
837 SELECT * FROM
838 JSON_TABLE('[3.14159]',
839 '$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '$')
840 ) AS alias2;
841 col18
842 0.999
843 Warnings:
844 Warning 1264 Out of range value for column 'col18' at row 1
845 SELECT * FROM
846 JSON_TABLE('[3.14159]',
847 '$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '$' ERROR ON ERROR)
848 ) AS alias2;
849 col18
850 0.999
851 Warnings:
852 Warning 1264 Out of range value for column 'col18' at row 1
853 SELECT * FROM
854 JSON_TABLE('[0.9]',
855 '$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '$')
856 ) AS alias2;
857 col18
858 0.900
859 SELECT * FROM
860 JSON_TABLE('["asdf","ghjk"]',
861 '$[*]' COLUMNS (col18 DECIMAL(4,3) PATH '$'
862 DEFAULT "3.14159" ON ERROR)
863 ) AS alias2;
864 col18
865 0.000
866 0.000
867 Warnings:
868 Warning 1366 Incorrect decimal value: 'asdf' for column `*any*`.`json_table`.`col18` at row 1
869 Warning 1366 Incorrect decimal value: 'ghjk' for column `*any*`.`json_table`.`col18` at row 2
870 CREATE TABLE t1(jd JSON);
871 INSERT INTO t1 VALUES('["asdf"]'),('["ghjk"]');
872 SELECT * FROM t1,
873 JSON_TABLE(jd,
874 '$[*]' COLUMNS (col18 DECIMAL(4,3) PATH '$'
875 DEFAULT "3.14159" ON ERROR)
876 ) AS alias2;
877 jd col18
878 ["asdf"] 0.000
879 ["ghjk"] 0.000
880 Warnings:
881 Warning 1366 Incorrect decimal value: 'asdf' for column `*any*`.`json_table`.`col18` at row 1
882 Warning 1366 Incorrect decimal value: 'ghjk' for column `*any*`.`json_table`.`col18` at row 1
883 DROP TABLE t1;
884 #
885 # Bug#25540027: SIG 11 IN FIND_FIELD_IN_TABLE | SQL/SQL_BASE.CC
886 #
887 CREATE TABLE t1(c1 JSON);
888 UPDATE t1, JSON_TABLE(t1.c1,'$[*]' COLUMNS (a INT PATH '$.a')) AS jt1
889 SET jt1.a=1;
890 ERROR HY000: The target table jt1 of the UPDATE is not updatable
891 DELETE JSON_TABLE(t1.c1,'$[*]' COLUMNS (a INT PATH '$.a')) AS jt1
892 FROM t1;
893 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(t1.c1,'$[*]' COLUMNS (a INT PATH '$.a')) AS jt1
894 FROM t1' at line 1
895 DELETE t1, JSON_TABLE(t1.c1,'$[*]' COLUMNS (a INT PATH '$.a')) AS jt1
896 USING t1;
897 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(t1.c1,'$[*]' COLUMNS (a INT PATH '$.a')) AS jt1
898 USING t1' at line 1
899 DROP TABLE t1;
900 #
901 # Bug#25547244: ASSERTION `!TABLE || (!TABLE->READ_SET || BITMAP_IS_SET(
902 #
903 CREATE TABLE t1(i INT);
904 INSERT INTO t1 VALUES(1);
905 DROP TABLE t1;
906 #
907 # Bug#25540675: ASSERTION `!COL->CHILD_JDS->PRODUCING_RECORDS' FAILED
908 #
909 CREATE TABLE j1(j JSON);
910 INSERT INTO j1 VALUES('[1,2,3]'),('[1,2,4]');
911 SELECT * FROM j1,
912 JSON_TABLE( JSON_OBJECT('key1', j) ,
913 '$.*' COLUMNS (NESTED PATH '$.*' COLUMNS (col11 FOR ORDINALITY))) AS alias2;
914 j col11
915 [1,2,3] NULL
916 [1,2,4] NULL
917 DROP TABLE j1;
918 #
919 # Bug#25584335: SIG 11 IN TABLE_LIST::FETCH_NUMBER_OF_ROWS
920 #
921 CREATE TABLE t1(i INT);
922 PREPARE stmt FROM "SELECT alias1.i AS field1 FROM (
923 t1 AS alias1,
924 (SELECT * FROM
925 JSON_TABLE('[1,2,3]' ,
926 '$[*]' COLUMNS (`col_varchar` FOR ORDINALITY)) AS SQ1_alias1
927 ) AS alias2 )";
928 EXECUTE stmt;
929 field1
930 DEALLOCATE PREPARE stmt;
931 DROP TABLE t1;
932 #
933 # Bug#25604048: COLUMN NAMES WITH SAME 33-CHAR PREFIX ARE EQUAL
934 #
935 SELECT * FROM JSON_TABLE( '{"key1": "test"}' , '$' COLUMNS(
936 column_name_is_thirty_four_or_more VARCHAR(17) PATH '$.key1',
937 column_name_is_thirty_four_or_more_yes_indeed VARCHAR(17) PATH '$.key1'
938 ) ) AS alias1;
939 column_name_is_thirty_four_or_more column_name_is_thirty_four_or_more_yes_indeed
940 test test
941 SELECT * FROM JSON_TABLE( '{"key1": "test"}' , '$' COLUMNS(
942 `column_name_is_thirty_four_or_more ` VARCHAR(17) PATH '$.key1'
943 ) ) AS alias1;
944 column_name_is_thirty_four_or_more
945 test
946 #
947 # Bug#25604404: JSON_TABLE MORE RESTRICTIVE WITH IDENTIFIERS THAN
948 # CREATE TABLE
949 #
950 SELECT * FROM JSON_TABLE( '[1, 2]', '$' COLUMNS(
951 one INT PATH '$[0]', two INT PATH '$[1]'
952 )) AS jt;
953 one two
954 1 2
955 #
956 # Bug#25588450: SIG 6 IN JSON_WRAPPER::SEEK|SQL/JSON_DOM.CC
957 #
958 CREATE TABLE t1(c VARCHAR(10)) ENGINE=MEMORY;
959 INSERT INTO t1 VALUES('fiheife');
960 SELECT * FROM `t1` AS alias1, JSON_TABLE( `c` , '$[*]' COLUMNS (jtcol1 JSON
961 PATH '$.*')) AS alias2 WHERE jtcol1 <= 'kjfh';
962 ERROR HY000: Syntax error in JSON text in argument 1 to function 'JSON_TABLE' at position 1
963 DROP TABLE t1;
964 #
965 # Bug#25587754: ASSERTION `FIXED == 0 || BASIC_CONST_ITEM()' FAILED
966 #
967 PREPARE stmt FROM
968 "SELECT * FROM JSON_TABLE( '[1,2]', '$[*]'
969 COLUMNS (jtcol1 JSON PATH '$.*')) AS alias2";
970 EXECUTE stmt;
971 jtcol1
972 NULL
973 NULL
974 DEALLOCATE PREPARE stmt;
975 #
976 # Bug#25584593: UNABLE TO USE JSON_TABLE() ON TEXT/BLOB JSON DATA
977 #
978 SELECT * FROM JSON_TABLE(NULL, '$.k' COLUMNS (id FOR ORDINALITY)) AS aLias;
979 id
980 SELECT * FROM JSON_TABLE(@myjson, '$.k' COLUMNS (id FOR ORDINALITY)) AS alias;
981 id
982 SET @myjson = '{"k": 42}';
983 SELECT * FROM JSON_TABLE(@myjson, '$.k' COLUMNS (id FOR ORDINALITY)) AS alias;
984 id
985 1
986 CREATE TABLE t1(
987 txt TEXT, ty TINYTEXT, tm MEDIUMTEXT, tl LONGTEXT);
988 INSERT INTO t1 values (
989 '{"k": "text"}','{"k": "tinytext"}','{"k": "mediumtext"}','{"k": "longtext"}');
990 SELECT alias.* FROM t1, JSON_TABLE(t1.txt,'$.k' COLUMNS (id VARCHAR(10) PATH '$')) AS alias;
991 id
992 text
993 SELECT alias.* FROM t1, JSON_TABLE(t1.ty, '$.k' COLUMNS (id VARCHAR(10) PATH '$')) AS alias;
994 id
995 tinytext
996 SELECT alias.* FROM t1, JSON_TABLE(t1.tm, '$.k' COLUMNS (id VARCHAR(10) PATH '$')) AS alias;
997 id
998 mediumtext
999 SELECT alias.* FROM t1, JSON_TABLE(t1.tl, '$.k' COLUMNS (id VARCHAR(10) PATH '$')) AS alias;
1000 id
1001 longtext
1002 SELECT alias.* FROM t1, JSON_TABLE(t1.txt,'$.k' COLUMNS (id TEXT PATH '$')) AS alias;
1003 id
1004 text
1005 SELECT alias.* FROM t1, JSON_TABLE(t1.txt,'$.k' COLUMNS (id TINYTEXT PATH '$')) AS alias;
1006 id
1007 text
1008 SELECT alias.* FROM t1, JSON_TABLE(t1.txt,'$.k' COLUMNS (id MEDIUMTEXT PATH '$')) AS alias;
1009 id
1010 text
1011 SELECT alias.* FROM t1, JSON_TABLE(t1.txt,'$.k' COLUMNS (id LONGTEXT PATH '$')) AS alias;
1012 id
1013 text
1014 SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb BLOB PATH '$')) as alias;
1015 blb
1016 asd123
1017 EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb BLOB PATH '$')) as alias;
1018 id select_type table type possible_keys key key_len ref rows Extra
1019 1 SIMPLE alias ALL NULL NULL NULL NULL 40 Table function: json_table
1020 EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb TINYBLOB PATH '$')) as alias;
1021 id select_type table type possible_keys key key_len ref rows Extra
1022 1 SIMPLE alias ALL NULL NULL NULL NULL 40 Table function: json_table
1023 EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb MEDIUMBLOB PATH '$')) as alias;
1024 id select_type table type possible_keys key key_len ref rows Extra
1025 1 SIMPLE alias ALL NULL NULL NULL NULL 40 Table function: json_table
1026 EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb LONGBLOB PATH '$')) as alias;
1027 id select_type table type possible_keys key key_len ref rows Extra
1028 1 SIMPLE alias ALL NULL NULL NULL NULL 40 Table function: json_table
1029 EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb TEXT PATH '$')) as alias;
1030 id select_type table type possible_keys key key_len ref rows Extra
1031 1 SIMPLE alias ALL NULL NULL NULL NULL 40 Table function: json_table
1032 EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb TINYTEXT PATH '$')) as alias;
1033 id select_type table type possible_keys key key_len ref rows Extra
1034 1 SIMPLE alias ALL NULL NULL NULL NULL 40 Table function: json_table
1035 EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb MEDIUMTEXT PATH '$')) as alias;
1036 id select_type table type possible_keys key key_len ref rows Extra
1037 1 SIMPLE alias ALL NULL NULL NULL NULL 40 Table function: json_table
1038 EXPLAIN SELECT alias.* FROM JSON_TABLE('"asd123"', '$' COLUMNS (blb LONGTEXT PATH '$')) as alias;
1039 id select_type table type possible_keys key key_len ref rows Extra
1040 1 SIMPLE alias ALL NULL NULL NULL NULL 40 Table function: json_table
1041 SELECT * FROM
1042 (SELECT CAST(blb AS JSON) jf FROM
1043 JSON_TABLE('"asd123"', '$' COLUMNS (blb BLOB PATH '$')) AS jti) AS dt,
1044 JSON_TABLE(jf, '$' COLUMNS (blb BLOB PATH '$')) AS jto;
1045 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'JSON) jf FROM
1046 JSON_TABLE('"asd123"', '$' COLUMNS (blb BLOB PATH '$')) AS jti)...' at line 2
1047 DROP TABLE t1;
1048 #
1049 # Bug#26500384: ASSERT FAILURE IN QUERY WITH WINDOW FUNCTION AND
1050 # JSON_TABLE
1051 #
1052 CREATE TABLE t (x INT);
1053 INSERT INTO t VALUES (1), (2), (3);
1054 SELECT MAX(t.x) OVER () m, jt.* FROM t,
1055 JSON_TABLE(JSON_ARRAY(m), '$[*]' COLUMNS (i INT PATH '$')) jt;
1056 ERROR 42S22: Unknown column 'm' in 'JSON_TABLE'
1057 DROP TABLE t;
1058 #
1059 # Bug#26583283: ASSERTION `!THD->IS_ERROR()' FAILED IN SQL_RESOLVER.CC
1060 #
1061 EXPLAIN SELECT * FROM JSON_TABLE('null', '$' COLUMNS(AA DECIMAL PATH '$')) tt;
1062 id select_type table type possible_keys key key_len ref rows Extra
1063 1 SIMPLE tt ALL NULL NULL NULL NULL 40 Table function: json_table
1064 CREATE VIEW v1 AS SELECT * FROM
1065 JSON_TABLE( 'null', '$' COLUMNS (c1 DECIMAL PATH '$' ) ) AS jt;
1066 SELECT * FROM v1;
1067 c1
1068 NULL
1069 EXPLAIN SELECT * FROM v1;
1070 id select_type table type possible_keys key key_len ref rows Extra
1071 1 SIMPLE jt ALL NULL NULL NULL NULL 40 Table function: json_table
1072 DROP VIEW v1;
1073 #
1074 # Bug#25822408: ASSERTION `!COL->CHILD_JDS->PRODUCING_RECORDS' FAILED
1075 #
1076 PREPARE stmt FROM "SELECT * FROM
1077 JSON_TABLE('{\"a\":1}','$' COLUMNS (c1 CHAR(20) PATH '$.b' ERROR ON EMPTY)) jt";
1078 EXECUTE stmt;
1079 ERROR HY000: Field 'c1' can't be set for JSON_TABLE 'jt'.
1080 EXECUTE stmt;
1081 ERROR HY000: Field 'c1' can't be set for JSON_TABLE 'jt'.
1082 #
1083 # Bug#25594571: CRASH AT ITEM::CONST_ITEM|SQL/ITEM.H
1084 #
1085 CREATE TABLE t1 (i INT);
1086 INSERT INTO t1 VALUES(1),(2),(3),(4),(5),(6),(7);
1087 PREPARE stmt FROM "SELECT * FROM t1 AS alias1 LEFT JOIN t1 AS alias2
1088 LEFT JOIN JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (i FOR ORDINALITY )) AS
1089 alias3 ON alias2 . `i` = alias3 . `i` ON alias1 . `i` = alias2 . `i`";
1090 EXECUTE stmt;
1091 i i i
1092 1 1 1
1093 2 2 2
1094 3 3 3
1095 4 4 NULL
1096 5 5 NULL
1097 6 6 NULL
1098 7 7 NULL
1099 EXECUTE stmt;
1100 i i i
1101 1 1 1
1102 2 2 2
1103 3 3 3
1104 4 4 NULL
1105 5 5 NULL
1106 6 6 NULL
1107 7 7 NULL
1108 DROP TABLE t1;
1109 #
1110 # Bug#26648617: ASSERTION `IS_VIEW_OR_DERIVED() &&
1111 # USES_MATERIALIZATION()' FAILED.
1112 #
1113 CREATE TABLE t1 (
1114 col_varchar_key varchar(1) DEFAULT NULL
1115 ) ENGINE=InnoDB;
1116 INSERT INTO t1 VALUES(1),(4);
1117 SELECT * FROM t1 WHERE col_varchar_key NOT IN (
1118 SELECT col_varchar_key FROM JSON_TABLE(
1119 '[{"col_key": 1},{"col_key": 2}]', "$[*]" COLUMNS
1120 (col_varchar_key VARCHAR(10) PATH "$.col_key")) AS innr1);
1121 col_varchar_key
1122 4
1123 EXPLAIN SELECT * FROM t1 WHERE col_varchar_key NOT IN (
1124 SELECT col_varchar_key FROM JSON_TABLE(
1125 '[{"col_key": 1},{"col_key": 2}]', "$[*]" COLUMNS
1126 (col_varchar_key VARCHAR(10) PATH "$.col_key")) AS innr1);
1127 id select_type table type possible_keys key key_len ref rows Extra
1128 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
1129 2 DEPENDENT SUBQUERY innr1 ALL NULL NULL NULL NULL 40 Table function: json_table; Using where
1130 SELECT * FROM t1 WHERE col_varchar_key IN (
1131 SELECT col_varchar_key FROM JSON_TABLE(
1132 '[{"col_key": 1},{"col_key": 2}]', "$[*]" COLUMNS
1133 (col_varchar_key VARCHAR(10) PATH "$.col_key")) AS innr1);
1134 col_varchar_key
1135 1
1136 EXPLAIN SELECT * FROM t1 WHERE col_varchar_key IN (
1137 SELECT col_varchar_key FROM JSON_TABLE(
1138 '[{"col_key": 1},{"col_key": 2}]', "$[*]" COLUMNS
1139 (col_varchar_key VARCHAR(10) PATH "$.col_key")) AS innr1);
1140 id select_type table type possible_keys key key_len ref rows Extra
1141 1 PRIMARY t1 ALL NULL NULL NULL NULL 2
1142 1 PRIMARY innr1 ALL NULL NULL NULL NULL 40 Table function: json_table; Using where; FirstMatch(t1); Using join buffer (flat, BNL join)
1143 DROP TABLE t1;
1144 #
1145 # Bug#26711551: WL8867:CONDITIONAL JUMP IN JSON_TABLE_COLUMN::CLEANUP
1146 #
1147 CREATE TABLE t(x int, y int);
1148 INSERT INTO t(x) VALUES (1);
1149 UPDATE t t1, JSON_TABLE('[2]', '$[*]' COLUMNS (x INT PATH '$')) t2
1150 SET t1.y = t2.x;
1151 SELECT * FROM t;
1152 x y
1153 1 2
1154 DROP TABLE t;
1155 #
1156 # Bug#26679671: SIG 11 IN JSON_BINARY::PARSE_BINARY()
1157 #
1158 CREATE TABLE t1(id INT, f1 JSON);
1159 INSERT INTO t1 VALUES (1, '{\"1\": 1}'), (2, '{\"1\": 2}'), (3, '{\"1\": 3}'),
1160 (4, '{\"1\": 4}'), (5, '{\"1\": 5}'), (6, '{\"1\": 6}');
1161 ANALYZE TABLE t1;
1162 Table Op Msg_type Msg_text
1163 test.t1 analyze status Engine-independent statistics collected
1164 test.t1 analyze Warning Engine-independent statistics are not collected for column 'f1'
1165 test.t1 analyze status OK
1166 ANALYZE TABLE t1;
1167 Table Op Msg_type Msg_text
1168 test.t1 analyze status Engine-independent statistics collected
1169 test.t1 analyze Warning Engine-independent statistics are not collected for column 'f1'
1170 test.t1 analyze status Table is already up to date
1171 SELECT * FROM t1 as jj1,
1172 (SELECT tt2.* FROM t1 as tt2,
1173 JSON_TABLE(f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl) dt;
1174 id f1 id f1
1175 1 {"1": 1} 1 {"1": 1}
1176 1 {"1": 1} 2 {"1": 2}
1177 1 {"1": 1} 3 {"1": 3}
1178 1 {"1": 1} 4 {"1": 4}
1179 1 {"1": 1} 5 {"1": 5}
1180 1 {"1": 1} 6 {"1": 6}
1181 2 {"1": 2} 1 {"1": 1}
1182 2 {"1": 2} 2 {"1": 2}
1183 2 {"1": 2} 3 {"1": 3}
1184 2 {"1": 2} 4 {"1": 4}
1185 2 {"1": 2} 5 {"1": 5}
1186 2 {"1": 2} 6 {"1": 6}
1187 3 {"1": 3} 1 {"1": 1}
1188 3 {"1": 3} 2 {"1": 2}
1189 3 {"1": 3} 3 {"1": 3}
1190 3 {"1": 3} 4 {"1": 4}
1191 3 {"1": 3} 5 {"1": 5}
1192 3 {"1": 3} 6 {"1": 6}
1193 4 {"1": 4} 1 {"1": 1}
1194 4 {"1": 4} 2 {"1": 2}
1195 4 {"1": 4} 3 {"1": 3}
1196 4 {"1": 4} 4 {"1": 4}
1197 4 {"1": 4} 5 {"1": 5}
1198 4 {"1": 4} 6 {"1": 6}
1199 5 {"1": 5} 1 {"1": 1}
1200 5 {"1": 5} 2 {"1": 2}
1201 5 {"1": 5} 3 {"1": 3}
1202 5 {"1": 5} 4 {"1": 4}
1203 5 {"1": 5} 5 {"1": 5}
1204 5 {"1": 5} 6 {"1": 6}
1205 6 {"1": 6} 1 {"1": 1}
1206 6 {"1": 6} 2 {"1": 2}
1207 6 {"1": 6} 3 {"1": 3}
1208 6 {"1": 6} 4 {"1": 4}
1209 6 {"1": 6} 5 {"1": 5}
1210 6 {"1": 6} 6 {"1": 6}
1211 EXPLAIN SELECT * FROM t1 as jj1,
1212 (SELECT tt2.* FROM t1 as tt2,
1213 JSON_TABLE(f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl) dt;
1214 id select_type table type possible_keys key key_len ref rows Extra
1215 1 SIMPLE jj1 ALL NULL NULL NULL NULL 6
1216 1 SIMPLE tt2 ALL NULL NULL NULL NULL 6 Using join buffer (flat, BNL join)
1217 1 SIMPLE tbl ALL NULL NULL NULL NULL 40 Table function: json_table
1218 SELECT * FROM t1 as jj1,
1219 (SELECT tt2.* FROM t1 as tt2,
1220 JSON_TABLE(f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl STRAIGHT_JOIN
1221 t1 AS tt3) dt ORDER BY 1,3 LIMIT 10;
1222 id f1 id f1
1223 1 {"1": 1} 1 {"1": 1}
1224 1 {"1": 1} 1 {"1": 1}
1225 1 {"1": 1} 1 {"1": 1}
1226 1 {"1": 1} 1 {"1": 1}
1227 1 {"1": 1} 1 {"1": 1}
1228 1 {"1": 1} 1 {"1": 1}
1229 1 {"1": 1} 2 {"1": 2}
1230 1 {"1": 1} 2 {"1": 2}
1231 1 {"1": 1} 2 {"1": 2}
1232 1 {"1": 1} 2 {"1": 2}
1233 EXPLAIN SELECT * FROM t1 as jj1,
1234 (SELECT tt2.* FROM t1 as tt2,
1235 JSON_TABLE(f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl STRAIGHT_JOIN
1236 t1 AS tt3) dt ORDER BY 1,3 LIMIT 11;
1237 id select_type table type possible_keys key key_len ref rows Extra
1238 1 SIMPLE jj1 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort
1239 1 SIMPLE tt2 ALL NULL NULL NULL NULL 6 Using join buffer (flat, BNL join)
1240 1 SIMPLE tbl ALL NULL NULL NULL NULL 40 Table function: json_table
1241 1 SIMPLE tt3 ALL NULL NULL NULL NULL 6 Using join buffer (flat, BNL join)
1242 SELECT * FROM t1 WHERE id IN
1243 (SELECT id FROM t1 as tt2,
1244 JSON_TABLE(f1, "$" COLUMNS (jf FOR ORDINALITY)) AS tbl);
1245 id f1
1246 1 {"1": 1}
1247 2 {"1": 2}
1248 3 {"1": 3}
1249 4 {"1": 4}
1250 5 {"1": 5}
1251 6 {"1": 6}
1252 EXPLAIN SELECT * FROM t1 WHERE id IN
1253 (SELECT id FROM t1 as tt2,
1254 JSON_TABLE(f1, "$" COLUMNS (jf FOR ORDINALITY)) AS tbl);
1255 id select_type table type possible_keys key key_len ref rows Extra
1256 1 PRIMARY t1 ALL NULL NULL NULL NULL 6
1257 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1
1258 2 MATERIALIZED tt2 ALL NULL NULL NULL NULL 6
1259 2 MATERIALIZED tbl ALL NULL NULL NULL NULL 40 Table function: json_table
1260 DROP TABLE t1;
1261 #
1262 # Bug#26760811: WL#8867: MEMORY LEAK REPORTED BY ASAN AND VALGRIND
1263 #
1264 CREATE TABLE t (j JSON);
1265 INSERT INTO t VALUES
1266 ('[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17]'),
1267 ('[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17]');
1268 SELECT COUNT(*) FROM t, JSON_TABLE(j, '$[*]' COLUMNS (i INT PATH '$')) AS jt;
1269 COUNT(*)
1270 34
1271 PREPARE ps FROM
1272 'SELECT COUNT(*) FROM t, JSON_TABLE(j, ''$[*]'' COLUMNS (i INT PATH ''$'')) AS jt';
1273 EXECUTE ps;
1274 COUNT(*)
1275 34
1276 EXECUTE ps;
1277 COUNT(*)
1278 34
1279 DROP PREPARE ps;
1280 DROP TABLE t;
1281 #
1282 # Bug #26781759: NON-UNIQUE ALIAS ERROR NOT BEING THROWN
1283 #
1284 SELECT * FROM JSON_TABLE(NULL, '$' COLUMNS(j1 FOR ORDINALITY)) AS jalias,
1285 JSON_TABLE(NULL, '$' COLUMNS(j1 FOR ORDINALITY)) AS jalias;
1286 ERROR 42000: Not unique table/alias: 'jalias'
1287 #
1288 # Bug#27152428 JSON_TABLE + PREPARED STATEMENT + VIEW HAS PROBLEM IN DURING RESOLUTION
1289 #
1290 CREATE TABLE t1 (a INT, b INT);
1291 CREATE VIEW v2 AS SELECT * FROM t1 LIMIT 2;
1292 DROP TABLE t1;
1293 DROP VIEW v2;
1294 #
1295 # Bug#27189940: CREATE VIEW FAILS ON JSON_TABLE() IN SCHEMA-LESS CONNECTIONS
1296 # BUG#27217897: JSON_TABLE() FAILS IF NO DATABASE SELECTED
1297 #
1298 connect conn1,localhost,root,,*NO-ONE*;
1299 connection conn1;
1300 CREATE VIEW test.v AS SELECT * FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$[0]')) AS jt;
1301 SELECT * FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$[0]')) AS jt;
1302 num
1303 1
1304 2
1305 3
1306 connection default;
1307 disconnect conn1;
1308 use test;
1309 SHOW CREATE VIEW test.v;
1310 View Create View character_set_client collation_connection
1311 v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `jt`.`num` AS `num` from JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (`num` int(11) PATH '$[0]')) `jt` latin1 latin1_swedish_ci
1312 SELECT * FROM test.v;
1313 num
1314 1
1315 2
1316 3
1317 DROP VIEW test.v;
1318 #
1319 # Bug#27729112 JSON_TABLE SHOULD DECODE BASE64-ENCODED STRINGS
1320 #
1321 SELECT v
1322 FROM JSON_TABLE(JSON_OBJECT('foo', _binary'bar'), '$'
1323 COLUMNS(v VARCHAR(255) PATH '$.foo')) tbl;
1324 v
1325 bar
1326 #
1327 # Bug#28254268: JSON_TABLE() FUNCTION REJECTS SELECT PERMISSIONS
1328 #
1329 CREATE DATABASE db2;
1330 USE db2;
1331 CREATE TABLE t1 (c JSON);
1332 INSERT INTO t1 VALUES('[1,2,3]');
1333 CREATE USER user1@localhost;
1334 GRANT SELECT ON db2.t1 TO user1@localhost;
1335 connect conn1,localhost,user1,,;
1336 connection conn1;
1337 USE db2;
1338 SELECT t1.c FROM t1;
1339 c
1340 [1,2,3]
1341 SELECT jt.* FROM t1, JSON_TABLE(t1.c, '$[*]' COLUMNS (num INT PATH '$[0]'))
1342 AS jt;
1343 num
1344 1
1345 2
1346 3
1347 disconnect conn1;
1348 connection default;
1349 DROP USER user1@localhost;
1350 DROP DATABASE db2;
1351 #
1352 # Bug#27856835 JSON_TABLE RETURNS WRONG DATATYPE WHEN INT-VALUE IS GRATER
1353 # THAN (2^31-1)
1354 #
1355 SELECT id FROM JSON_TABLE('[{"id":"9223372036854775807"}]', '$[*]' COLUMNS
1356 (id BIGINT PATH '$.id')) AS json;
1357 id
1358 9223372036854775807
1359 # As we currently have no way of telling if a JSON string value is
1360 # signed or unsigned, this value will overflow.
1361 SELECT id FROM JSON_TABLE('[{"id":"9223372036854775808"}]', '$[*]' COLUMNS
1362 (id BIGINT PATH '$.id')) AS json;
1363 id
1364 9223372036854775807
1365 Warnings:
1366 Warning 1264 Out of range value for column 'id' at row 1
1367 # Here the JSON value is a NUMERIC value, and we thus know if the value
1368 # is signed or unsigned.
1369 SELECT id FROM JSON_TABLE('[{"id":9223372036854775808}]', '$[*]' COLUMNS
1370 (id BIGINT PATH '$.id')) AS json;
1371 id
1372 9223372036854775807
1373 Warnings:
1374 Warning 1264 Out of range value for column 'id' at row 1
1375 # If we tell the JSON table column to be unsigned, we get to store the
1376 # full value correctly.
1377 SELECT id FROM JSON_TABLE('[{"id":"9223372036854775808"}]', '$[*]' COLUMNS
1378 (id BIGINT UNSIGNED PATH '$.id')) AS json;
1379 id
1380 9223372036854775808
1381 SELECT id FROM JSON_TABLE('[{"id":"2147483648"}]', '$[*]' COLUMNS
1382 (id INT UNSIGNED PATH '$.id')) AS json;
1383 id
1384 2147483648
1385 # Check that we preserve the signedness of the columns.
1386 USE test;
1387 CREATE TABLE t1 AS SELECT id, value FROM
1388 JSON_TABLE('[{"id":9223372036854775808, "value":9223372036854775807}]',
1389 '$[*]' COLUMNS (id BIGINT UNSIGNED PATH '$.id',
1390 value BIGINT PATH '$.value'))
1391 AS json;
1392 SHOW CREATE TABLE t1;
1393 Table Create Table
1394 t1 CREATE TABLE `t1` (
1395 `id` bigint(20) unsigned DEFAULT NULL,
1396 `value` bigint(20) DEFAULT NULL
1397 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
1398 DROP TABLE t1;
1399 #
1400 # Bug#28255453: VIEW USING JSON_TABLE FAILS IF NO SCHEMA IS SELECTED
1401 #
1402 connect conn1,localhost,root,,*NO-ONE*;
1403 CREATE VIEW test.v AS SELECT * FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$[0]')) AS jt;
1404 SELECT * FROM test.v;
1405 num
1406 1
1407 2
1408 3
1409 DROP VIEW test.v;
1410 # Check that a user with access to the schema 'foo' can do a SELECT with
1411 # a JSON_TABLE function.
1412 CREATE SCHEMA foo;
1413 CREATE USER foo@localhost;
1414 GRANT EXECUTE ON foo.* TO foo@localhost;
1415 connect con1,localhost,foo,,foo;
1416 SELECT 1 FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$.a')) AS jt;
1417 1
1418 1
1419 1
1420 1
1421 connection default;
1422 disconnect con1;
1423 DROP USER foo@localhost;
1424 DROP SCHEMA foo;
1425 #
1426 # Bug#27923406 ERROR 1142 (42000) WHEN USING JSON_TABLE
1427 #
1428 CREATE SCHEMA my_schema;
1429 CREATE USER foo@localhost;
1430 GRANT EXECUTE ON my_schema.* TO foo@localhost;
1431 connect con1,localhost,foo,,my_schema;
1432 SELECT
1433 *
1434 FROM
1435 JSON_TABLE(
1436 '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',
1437 "$[*]" COLUMNS(
1438 xval VARCHAR(100) PATH "$.x",
1439 yval VARCHAR(100) PATH "$.y"
1440 )
1441 ) AS jt1;
1442 xval yval
1443 2 8
1444 3 7
1445 4 6
1446 connection default;
1447 disconnect con1;
1448 DROP USER foo@localhost;
1449 DROP SCHEMA my_schema;
1450 #
1451 # Bug#28538315: JSON_TABLE() COLUMN TYPES DON'T SUPPORT COLLATE CLAUSE
1452 #
1453 CREATE TABLE t1 SELECT *
1454 FROM JSON_TABLE('"test"', '$' COLUMNS(x VARCHAR(10)
1455 CHARSET utf8mb4
1456 PATH '$')) AS jt1;
1457 SHOW CREATE TABLE t1;
1458 Table Create Table
1459 t1 CREATE TABLE `t1` (
1460 `x` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci DEFAULT NULL
1461 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
1462 DROP TABLE t1;
1463 #
1464 # Bug#28643862 JSON_TABLE'S "COLUMNS" CLAUSE USES
1465 # GLOBAL.CHARACTER_SET_RESULTS DEFAULT CHARSET
1466 #
1467 SET @@SESSION.character_set_connection = ascii;
1468 CREATE TABLE t1 SELECT a.col
1469 FROM JSON_TABLE('"test"', '$' COLUMNS(col VARCHAR(10) PATH '$')) AS a;
1470 SHOW CREATE TABLE t1;
1471 Table Create Table
1472 t1 CREATE TABLE `t1` (
1473 `col` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL
1474 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
1475 SET @@SESSION.collation_connection = latin1_bin;
1476 CREATE TABLE t2 SELECT a.col
1477 FROM JSON_TABLE('"test"', '$' COLUMNS(col VARCHAR(10) PATH '$')) AS a;
1478 SHOW CREATE TABLE t2;
1479 Table Create Table
1480 t2 CREATE TABLE `t2` (
1481 `col` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL
1482 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
1483 DROP TABLE t1, t2;
1484 SET @@SESSION.character_set_connection = DEFAULT;
1485 #
1486 # Bug#28851656: JSON_TABLE RETURN "UNKNOWN DATABASE ''" FROM A FUNCTION
1487 #
1488 CREATE FUNCTION FN_COUNT_ROWS(X JSON)
1489 RETURNS INT DETERMINISTIC
1490 RETURN (
1491 SELECT COUNT(*) FROM JSON_TABLE( X, '$[*]' COLUMNS( I INT PATH '$')) der
1492 );
1493 SELECT FN_COUNT_ROWS('[1, 2]') CNT;
1494 CNT
1495 2
1496 SELECT FN_COUNT_ROWS('[1, 2, 3]') CNT;
1497 CNT
1498 3
1499 SELECT FN_COUNT_ROWS('[1, 2, 3, 4]') CNT;
1500 CNT
1501 4
1502 DROP FUNCTION FN_COUNT_ROWS;
1503 #
1504 # Bug#30310265: VIEWS LOSE THE CHARACTER SET OF JSON_TABLE'S
1505 # PATH ARGUMENTS
1506 #
1507 CREATE VIEW v1 AS
1508 SELECT * FROM JSON_TABLE('{"å":{"å":1}}', '$.å' COLUMNS (x INT PATH '$.å')) t;
1509 SELECT * FROM v1;
1510 x
1511 SET NAMES latin1;
1512 SELECT * FROM v1;
1513 x
1514 SET NAMES latin1;
1515 DROP VIEW v1;
1516 CREATE VIEW v2 AS
1517 SELECT * FROM JSON_TABLE('{}', '$' COLUMNS (
1518 x VARCHAR(10) PATH '$.a' DEFAULT '"isn''t here"' ON EMPTY)
1519 ) t;
1520 DROP VIEW v2;
1521 #
1522 # Bug#30382156: STORED PROCEDURE, JSON_TABLE AND "CONST" JOIN TYPE
1523 #
1524 CREATE TABLE t (id INT PRIMARY KEY, j JSON);
1525 INSERT INTO t VALUES (1, '{"x":1}');
1526 CREATE PROCEDURE p()
1527 SELECT * FROM t, JSON_TABLE(j, '$' COLUMNS (x INT PATH '$.x')) jt
1528 WHERE id = 1;
1529 CALL p();
1530 id j x
1531 1 {"x":1} 1
1532 CALL p();
1533 id j x
1534 1 {"x":1} 1
1535 CALL p();
1536 id j x
1537 1 {"x":1} 1
1538 DROP PROCEDURE p;
1539 DROP TABLE t;
1540 #
1541 # WL#13512: Deprecate ON ERROR before ON EMPTY in JSON_TABLE syntax
1542 #
1543 SELECT * FROM
1544 JSON_TABLE('{}', '$' COLUMNS (x VARCHAR(10) PATH '$.x'
1545 DEFAULT '"a"' ON ERROR DEFAULT '"b"' ON EMPTY)) jt;
1546 x
1547 "b"
1548 SELECT * FROM
1549 JSON_TABLE('{}', '$' COLUMNS (x VARCHAR(10) PATH '$.x'
1550 NULL ON ERROR NULL ON EMPTY)) jt;
1551 x
1552 NULL
1553 SELECT * FROM
1554 JSON_TABLE('{"x":"c"}', '$' COLUMNS (x VARCHAR(10) PATH '$.x'
1555 ERROR ON ERROR ERROR ON EMPTY)) jt;
1556 x
1557 c
1558 #
1559 # Bug#30628330: INSERT WITH JSON_TABLE FAILS DUE TO WARNING INVOKED
1560 # WITH ON ERROR CLAUSE
1561 #
1562 CREATE TABLE t(id INT, a TINYINT, b VARCHAR(3), c DATE, d DECIMAL);
1563 INSERT INTO t SELECT * FROM
1564 JSON_TABLE(JSON_ARRAY(JSON_OBJECT('a', 1, 'b', 'abc'),
1565 JSON_OBJECT('a', 2, 'b', 'abcd'),
1566 JSON_OBJECT('a', 1000, 'b', 'xyz'),
1567 JSON_OBJECT('c', TIME'12:00:00', 'd', 1e308)),
1568 '$[*]' COLUMNS (id FOR ORDINALITY,
1569 a TINYINT PATH '$.a' DEFAULT '111' ON ERROR,
1570 b VARCHAR(3) PATH '$.b' DEFAULT '"ERR"' ON ERROR,
1571 c DATE PATH '$.c' DEFAULT '"2001-01-01"' ON ERROR,
1572 d DECIMAL PATH '$.c' DEFAULT '999' ON ERROR)
1573 ) AS jt;
1574 Warnings:
1575 Warning 1265 Data truncated for column 'b' at row 2
1576 Warning 1264 Out of range value for column 'a' at row 3
1577 Warning 1265 Data truncated for column 'd' at row 4
1578 SELECT * FROM t ORDER BY id;
1579 id a b c d
1580 1 1 abc NULL NULL
1581 2 2 abc NULL NULL
1582 3 127 xyz NULL NULL
1583 4 NULL NULL 2012-00-00 12
1584 DROP TABLE t;
1585 #
1586 # Bug#30263373: INCORRECT OUTPUT FROM TABLE_FUNCTION_JSON::PRINT()
1587 #
1588 CREATE VIEW v AS SELECT * FROM
1589 JSON_TABLE('[123]', '$[*]' COLUMNS (`name with space 1` INT PATH '$',
1590 `name with space 2` FOR ORDINALITY)) jt;
1591 SELECT * FROM v;
1592 name with space 1 name with space 2
1593 123 1
1594 DROP VIEW v;
1595 CREATE VIEW v AS SELECT HEX(x), HEX(y) FROM
1596 JSON_TABLE('["abc"]', '$[*]' COLUMNS (x BINARY(10) PATH '$',
1597 y VARBINARY(10) PATH '$')) jt;
1598 SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
1599 WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';
1600 VIEW_DEFINITION
1601 select hex(`jt`.`x`) AS `HEX(x)`,hex(`jt`.`y`) AS `HEX(y)` from JSON_TABLE('["abc"]', '$[*]' COLUMNS (`x` binary(10) PATH '$', `y` varbinary(10) PATH '$')) `jt`
1602 SELECT * FROM v;
1603 HEX(x) HEX(y)
1604 61626300000000000000 616263
1605 DROP VIEW v;
1606 CREATE VIEW v AS SELECT * FROM
1607 JSON_TABLE('[123]', '$[*]' COLUMNS(ti TINYINT PATH '$',
1608 si SMALLINT PATH '$',
1609 mi MEDIUMINT PATH '$',
1610 i INT PATH '$',
1611 bi BIGINT PATH '$',
1612 tiu TINYINT UNSIGNED PATH '$',
1613 siu SMALLINT UNSIGNED PATH '$',
1614 miu MEDIUMINT UNSIGNED PATH '$',
1615 iu INT UNSIGNED PATH '$',
1616 biu BIGINT UNSIGNED PATH '$')) jt;
1617 SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
1618 WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';
1619 VIEW_DEFINITION
1620 select `jt`.`ti` AS `ti`,`jt`.`si` AS `si`,`jt`.`mi` AS `mi`,`jt`.`i` AS `i`,`jt`.`bi` AS `bi`,`jt`.`tiu` AS `tiu`,`jt`.`siu` AS `siu`,`jt`.`miu` AS `miu`,`jt`.`iu` AS `iu`,`jt`.`biu` AS `biu` from JSON_TABLE('[123]', '$[*]' COLUMNS (`ti` tinyint(4) PATH '$', `si` smallint(6) PATH '$', `mi` mediumint(9) PATH '$', `i` int(11) PATH '$', `bi` bigint(20) PATH '$', `tiu` tinyint(3) unsigned PATH '$', `siu` smallint(5) unsigned PATH '$', `miu` mediumint(8) unsigned PATH '$', `iu` int(10) unsigned PATH '$', `biu` bigint(20) unsigned PATH '$')) `jt`
1621 SELECT * FROM v;
1622 ti si mi i bi tiu siu miu iu biu
1623 123 123 123 123 123 123 123 123 123 123
1624 DROP VIEW v;
1625 #
1626 # Bug#31345503 JSON_TABLE SHOULD RETURN SQL NULL FOR JSON NULL
1627 #
1628 SELECT *
1629 FROM
1630 JSON_TABLE(
1631 '[
1632 {"c1": null,
1633 "c2": [{"c": "c_val", "l": [1,2]}],
1634 "c3": null},
1635 {"c1": true,
1636 "c2": [{"c": "c_val","l": [11]}, {"c": "c_val", "l": [false]}],
1637 "c3": true},
1638 {"c1": false,
1639 "c2": [{"c": null,"l": [true]}, {"c": "c_val", "l": [null]}],
1640 "c3": false}
1641 ]',
1642 '$[*]' COLUMNS(
1643 top_ord FOR ORDINALITY,
1644 c1path VARCHAR(10) PATH '$.c1' ERROR ON ERROR,
1645 NESTED PATH '$.c2[*]' COLUMNS (
1646 c2path VARCHAR(10) PATH '$.c' ERROR ON ERROR,
1647 ord FOR ORDINALITY,
1648 NESTED PATH '$.l[*]' COLUMNS (lpath_c VARCHAR(10) PATH '$' ERROR ON ERROR,
1649 lpath_i INT PATH '$' ERROR ON ERROR)
1650 ),
1651 c3path INT PATH '$.c3' ERROR ON ERROR
1652 )
1653 ) as jt;
1654 top_ord c1path c2path ord lpath_c lpath_i c3path
1655 1 NULL c_val 1 1 1 NULL
1656 1 NULL c_val 1 2 2 NULL
1657 2 true c_val 1 11 11 1
1658 2 true c_val 2 false 0 1
1659 3 false NULL 1 true 1 0
1660 3 false c_val 2 NULL NULL 0
1661 #
1662 # Bug #31327187 UBSAN: JSON_TABLE: NULL POINTER PASSED AS ARGUMENT 2,
1663 # WHICH IS DECLARED TO NEVER BE NULL
1664 #
1665 SELECT HEX(a) FROM JSON_TABLE(
1666 '[{"E":{"e":true,"~":1,"S3":"sTa"},"r":3,"":6.7},"",6.5]',
1667 '$'
1668 COLUMNS(a BINARY(5) PATH '$[1]' NULL ON EMPTY)
1669 ) e;
1670 HEX(a)
1671 0000000000
1672 ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;