Debian Codegraph @

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

raw
1 #
2 # WL#8867: Add JSON_TABLE table function
3 #
4 select * from
5 json_table(
6 '[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
7 '$[*]' columns (id for ordinality,
8 jpath varchar(100) path '$.a',
9 jexst int exists path '$.b')
10 ) as tt;
11 id jpath jexst
12 1 3 0
13 2 2 0
14 3 NULL 1
15 4 0 0
16 explain select * from
17 json_table(
18 '[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
19 '$[*]' columns (id for ordinality,
20 jpath varchar(100) path '$.a',
21 jexst int exists path '$.b')
22 ) as tt;
23 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
24 1 SIMPLE tt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
25 Warnings:
26 Note 1003 /* select#1 */ select `tt`.`id` AS `id`,`tt`.`jpath` AS `jpath`,`tt`.`jexst` AS `jexst` from json_table('[{"a":"3"},{"a":2},{"b":1},{"a":0}]', '$[*]' columns (`id` for ordinality, `jpath` varchar(100) character set utf8mb4 path '$.a', `jexst` int exists path '$.b')) `tt`
27 explain format=tree select * from
28 json_table(
29 '[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
30 '$[*]' columns (id for ordinality,
31 jpath varchar(100) path '$.a',
32 jexst int exists path '$.b')
33 ) as tt;
34 EXPLAIN
35 -> Materialize table function
36
37 select * from
38 json_table(
39 '[{"x":"3"},{"a":2},{"b":1},{"a":0}]',
40 '$[*]' columns (id for ordinality,
41 jpath varchar(100) path '$.a' default '[99]' on error,
42 jexst int exists path '$.b')
43 ) as tt;
44 ERROR 42000: Invalid default value for 'jpath'
45 select * from
46 json_table(
47 '[{"x":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
48 '$[*]' columns (id for ordinality,
49 jpath varchar(100) path '$.a'
50 default '33' on empty
51 default '66' on error,
52 jsn_path json path '$.a' default '{"x":33}' on empty,
53 jexst int exists path '$.b')
54 ) as tt;
55 id jpath jsn_path jexst
56 1 33 {"x": 33} 0
57 2 2 2 0
58 3 33 {"x": 33} 1
59 4 0 0 0
60 5 66 [1, 2] 0
61 explain select * from
62 json_table(
63 '[{"x":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
64 '$[*]' columns (id for ordinality,
65 jpath varchar(100) path '$.a'
66 default '33' on empty
67 default '66' on error,
68 jsn_path json path '$.a' default '{"x":33}' on empty,
69 jexst int exists path '$.b')
70 ) as tt;
71 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
72 1 SIMPLE tt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
73 Warnings:
74 Note 1003 /* select#1 */ select `tt`.`id` AS `id`,`tt`.`jpath` AS `jpath`,`tt`.`jsn_path` AS `jsn_path`,`tt`.`jexst` AS `jexst` from json_table('[{"x":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]', '$[*]' columns (`id` for ordinality, `jpath` varchar(100) character set utf8mb4 path '$.a' default '33' on empty default '66' on error, `jsn_path` json path '$.a' default '{"x":33}' on empty, `jexst` int exists path '$.b')) `tt`
75 select * from
76 json_table(
77 '[{"a":"3"},{"a":2},{"b":1},{"a":0.33},{"a":"asd"}]',
78 '$[*]' columns (id for ordinality,
79 jpath_i int path '$.a'
80 default '33' on empty
81 default '66' on error,
82 jpath_r real path '$.a'
83 default '33.3' on empty
84 default '77.7' on error,
85 jsn_path json path '$.a' default '{"x":33}' on empty,
86 jexst int exists path '$.b')
87 ) as tt;
88 id jpath_i jpath_r jsn_path jexst
89 1 3 3 "3" 0
90 2 2 2 2 0
91 3 33 33.3 {"x": 33} 1
92 4 0 0.33 0.33 0
93 5 66 77.7 "asd" 0
94 explain select * from
95 json_table(
96 '[{"a":"3"},{"a":2},{"b":1},{"a":0.33},{"a":"asd"}]',
97 '$[*]' columns (id for ordinality,
98 jpath_i int path '$.a'
99 default '33' on empty
100 default '66' on error,
101 jpath_r real path '$.a'
102 default '33.3' on empty
103 default '77.7' on error,
104 jsn_path json path '$.a' default '{"x":33}' on empty,
105 jexst int exists path '$.b')
106 ) as tt;
107 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
108 1 SIMPLE tt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
109 Warnings:
110 Note 1003 /* select#1 */ select `tt`.`id` AS `id`,`tt`.`jpath_i` AS `jpath_i`,`tt`.`jpath_r` AS `jpath_r`,`tt`.`jsn_path` AS `jsn_path`,`tt`.`jexst` AS `jexst` from json_table('[{"a":"3"},{"a":2},{"b":1},{"a":0.33},{"a":"asd"}]', '$[*]' columns (`id` for ordinality, `jpath_i` int path '$.a' default '33' on empty default '66' on error, `jpath_r` double path '$.a' default '33.3' on empty default '77.7' on error, `jsn_path` json path '$.a' default '{"x":33}' on empty, `jexst` int exists path '$.b')) `tt`
111 select * from
112 json_table(
113 '[{"x":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
114 '$[*]' columns (id for ordinality,
115 jpath varchar(100) path '$.a'
116 default '33' on empty
117 default '66' on error,
118 jsn_path json path '$.a' default '{"x":33}' on empty,
119 jexst int exists path '$.b')
120 ) as tt;
121 id jpath jsn_path jexst
122 1 33 {"x": 33} 0
123 2 2 2 0
124 3 33 {"x": 33} 1
125 4 0 0 0
126 5 66 [1, 2] 0
127 explain select * from
128 json_table(
129 '[{"x":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
130 '$[*]' columns (id for ordinality,
131 jpath varchar(100) path '$.a'
132 default '33' on empty
133 default '66' on error,
134 jsn_path json path '$.a' default '{"x":33}' on empty,
135 jexst int exists path '$.b')
136 ) as tt;
137 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
138 1 SIMPLE tt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
139 Warnings:
140 Note 1003 /* select#1 */ select `tt`.`id` AS `id`,`tt`.`jpath` AS `jpath`,`tt`.`jsn_path` AS `jsn_path`,`tt`.`jexst` AS `jexst` from json_table('[{"x":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]', '$[*]' columns (`id` for ordinality, `jpath` varchar(100) character set utf8mb4 path '$.a' default '33' on empty default '66' on error, `jsn_path` json path '$.a' default '{"x":33}' on empty, `jexst` int exists path '$.b')) `tt`
141 select * from
142 json_table(
143 '[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
144 '$[*]' columns (id for ordinality,
145 jpath varchar(100) path '$.a',
146 json_path json path '$.a',
147 jexst int exists path '$.b')
148 ) as tt;
149 id jpath json_path jexst
150 1 3 "3" 0
151 2 2 2 0
152 3 NULL NULL 1
153 4 0 0 0
154 select * from
155 json_table(
156 '[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
157 '$[*]' columns (id for ordinality,
158 jpath varchar(100) path '$.a',
159 json_path json path '$.a',
160 jexst int exists path '$.b')
161 ) as tt
162 where id = 3;
163 id jpath json_path jexst
164 3 NULL NULL 1
165 explain select * from
166 json_table(
167 '[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
168 '$[*]' columns (id for ordinality,
169 jpath varchar(100) path '$.a',
170 json_path json path '$.a',
171 jexst int exists path '$.b')
172 ) as tt
173 where id = 3;
174 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
175 1 SIMPLE tt NULL ref <auto_key0> <auto_key0> 9 const 1 100.00 Table function: json_table; Using temporary
176 Warnings:
177 Note 1003 /* select#1 */ select `tt`.`id` AS `id`,`tt`.`jpath` AS `jpath`,`tt`.`json_path` AS `json_path`,`tt`.`jexst` AS `jexst` from json_table('[{"a":"3"},{"a":2},{"b":1},{"a":0}]', '$[*]' columns (`id` for ordinality, `jpath` varchar(100) character set utf8mb4 path '$.a', `json_path` json path '$.a', `jexst` int exists path '$.b')) `tt` where (`tt`.`id` = 3)
178 select * from
179 json_table(
180 '[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
181 '$[*]' columns (id for ordinality,
182 jpath varchar(100) path '$.a' error on empty,
183 jexst int exists path '$.b')
184 ) as tt;
185 ERROR 22035: Missing value for JSON_TABLE column 'jpath'
186 select * from
187 json_table(
188 '[{"a":"3"},{"a":2},{"a":1},{"a":[0,1]}]',
189 '$[*]' columns (id for ordinality,
190 jpath varchar(100) path '$.a' error on error,
191 jexst int exists path '$.b')
192 ) as tt;
193 ERROR 2203F: Can't store an array or an object in the scalar JSON_TABLE column 'jpath'
194 select * from
195 json_table(
196 '!#@$!@#$',
197 '$[*]' columns (id for ordinality,
198 jpath varchar(100) path '$.a',
199 jexst int exists path '$.b')
200 ) as tt;
201 ERROR 22032: Invalid JSON text in argument 1 to function json_table: "Invalid value." at position 0.
202 select * from
203 json_table(
204 '[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
205 "!@#$!@#$" columns (id for ordinality,
206 jpath varchar(100) path '$.a',
207 jexst int exists path '$.b')
208 ) as tt;
209 ERROR 42000: Invalid JSON path expression. The error is around character position 1.
210 select * from
211 json_table(
212 '[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
213 '$[*]' columns (id for ordinality,
214 jpath varchar(100) path "!@#$!@#$",
215 jexst int exists path '$.b')
216 ) as tt;
217 ERROR 42000: Invalid JSON path expression. The error is around character position 1.
218 select * from
219 json_table(
220 '[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
221 '$[*]' columns (id for ordinality,
222 id for ordinality)
223 ) as tt;
224 ERROR 42S21: Duplicate column name 'id'
225 select * from
226 json_table(
227 '[{"a":"3"},{"a":2},{"b":1},{"a":0}]',
228 '$[*]' columns (id for ordinality,
229 _id for ordinality)
230 ) as tt;
231 id _id
232 1 1
233 2 2
234 3 3
235 4 4
236 select * from
237 json_table(
238 '[
239 {"a":"3", "n": { "l": 1}},
240 {"a":2, "n": { "l": 1}},
241 {"b":1, "n": { "l": 1}},
242 {"a":0, "n": { "l": 1}}
243 ]',
244 '$[*]' columns (
245 id for ordinality,
246 jpath varchar(100) path '$.a',
247 jexst int exists path '$.b',
248 nested path '$.n' columns (
249 id_n for ordinality,
250 jpath_n varchar(50) path '$.l')
251 )
252 ) as tt;
253 id jpath jexst id_n jpath_n
254 1 3 0 1 1
255 2 2 0 1 1
256 3 NULL 1 1 1
257 4 0 0 1 1
258 explain select * from
259 json_table(
260 '[
261 {"a":"3", "n": { "l": 1}},
262 {"a":2, "n": { "l": 1}},
263 {"b":1, "n": { "l": 1}},
264 {"a":0, "n": { "l": 1}}
265 ]',
266 '$[*]' columns (
267 id for ordinality,
268 jpath varchar(100) path '$.a',
269 jexst int exists path '$.b',
270 nested path '$.n' columns (
271 id_n for ordinality,
272 jpath_n varchar(50) path '$.l')
273 )
274 ) as tt;
275 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
276 1 SIMPLE tt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
277 Warnings:
278 Note 1003 /* select#1 */ select `tt`.`id` AS `id`,`tt`.`jpath` AS `jpath`,`tt`.`jexst` AS `jexst`,`tt`.`id_n` AS `id_n`,`tt`.`jpath_n` AS `jpath_n` from json_table('[\n {"a":"3", "n": { "l": 1}},\n {"a":2, "n": { "l": 1}},\n {"b":1, "n": { "l": 1}},\n {"a":0, "n": { "l": 1}}\n ]', '$[*]' columns (`id` for ordinality, `jpath` varchar(100) character set utf8mb4 path '$.a', `jexst` int exists path '$.b', nested path '$.n' columns (`id_n` for ordinality, `jpath_n` varchar(50) character set utf8mb4 path '$.l'))) `tt`
279 explain format=json select * from
280 json_table(
281 '[
282 {"a":"3", "n": { "l": 1}},
283 {"a":2, "n": { "l": 1}},
284 {"b":1, "n": { "l": 1}},
285 {"a":0, "n": { "l": 1}}
286 ]',
287 '$[*]' columns (
288 id for ordinality,
289 jpath varchar(100) path '$.a',
290 jexst int exists path '$.b',
291 nested path '$.n' columns (
292 id_n for ordinality,
293 jpath_n varchar(50) path '$.l')
294 )
295 ) as tt;
296 EXPLAIN
297 {
298 "query_block": {
299 "select_id": 1,
300 "cost_info": {
301 "query_cost": "2.72"
302 },
303 "table": {
304 "table_name": "tt",
305 "access_type": "ALL",
306 "rows_examined_per_scan": 2,
307 "rows_produced_per_join": 2,
308 "filtered": "100.00",
309 "table_function": "json_table",
310 "using_temporary_table": true,
311 "cost_info": {
312 "read_cost": "2.52",
313 "eval_cost": "0.20",
314 "prefix_cost": "2.73",
315 "data_read_per_join": "1K"
316 },
317 "used_columns": [
318 "id",
319 "jpath",
320 "jexst",
321 "id_n",
322 "jpath_n"
323 ]
324 }
325 }
326 }
327 Warnings:
328 Note 1003 /* select#1 */ select `tt`.`id` AS `id`,`tt`.`jpath` AS `jpath`,`tt`.`jexst` AS `jexst`,`tt`.`id_n` AS `id_n`,`tt`.`jpath_n` AS `jpath_n` from json_table('[\n {"a":"3", "n": { "l": 1}},\n {"a":2, "n": { "l": 1}},\n {"b":1, "n": { "l": 1}},\n {"a":0, "n": { "l": 1}}\n ]', '$[*]' columns (`id` for ordinality, `jpath` varchar(100) character set utf8mb4 path '$.a', `jexst` int exists path '$.b', nested path '$.n' columns (`id_n` for ordinality, `jpath_n` varchar(50) character set utf8mb4 path '$.l'))) `tt`
329 select * from
330 json_table(
331 '[
332 {"a":2, "n": [{ "l": 1}, {"l": 11}]},
333 {"a":1, "n": [{ "l": 2}, {"l": 22}]}
334 ]',
335 '$[*]' columns (
336 id for ordinality,
337 jpath varchar(50) path '$.a',
338 nested path '$.n[*]' columns (
339 id_n for ordinality,
340 jpath_n varchar(50) path '$.l'),
341 nested path '$.n[*]' columns (
342 id_m for ordinality,
343 jpath_m varchar(50) path '$.l')
344 )
345 ) as tt;
346 id jpath id_n jpath_n id_m jpath_m
347 1 2 1 1 NULL NULL
348 1 2 2 11 NULL NULL
349 1 2 NULL NULL 1 1
350 1 2 NULL NULL 2 11
351 2 1 1 2 NULL NULL
352 2 1 2 22 NULL NULL
353 2 1 NULL NULL 1 2
354 2 1 NULL NULL 2 22
355 explain select * from
356 json_table(
357 '[
358 {"a":2, "n": [{ "l": 1}, {"l": 11}]},
359 {"a":1, "n": [{ "l": 2}, {"l": 22}]}
360 ]',
361 '$[*]' columns (
362 id for ordinality,
363 jpath varchar(50) path '$.a',
364 nested path '$.n[*]' columns (
365 id_n for ordinality,
366 jpath_n varchar(50) path '$.l'),
367 nested path '$.n[*]' columns (
368 id_m for ordinality,
369 jpath_m varchar(50) path '$.l')
370 )
371 ) as tt;
372 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
373 1 SIMPLE tt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
374 Warnings:
375 Note 1003 /* select#1 */ select `tt`.`id` AS `id`,`tt`.`jpath` AS `jpath`,`tt`.`id_n` AS `id_n`,`tt`.`jpath_n` AS `jpath_n`,`tt`.`id_m` AS `id_m`,`tt`.`jpath_m` AS `jpath_m` from json_table('[\n {"a":2, "n": [{ "l": 1}, {"l": 11}]},\n {"a":1, "n": [{ "l": 2}, {"l": 22}]}\n ]', '$[*]' columns (`id` for ordinality, `jpath` varchar(50) character set utf8mb4 path '$.a', nested path '$.n[*]' columns (`id_n` for ordinality, `jpath_n` varchar(50) character set utf8mb4 path '$.l'), nested path '$.n[*]' columns (`id_m` for ordinality, `jpath_m` varchar(50) character set utf8mb4 path '$.l'))) `tt`
376 select * from json_table(
377 '[
378 {"a":"3", "n": ["b","a","c"]},
379 {"a":2, "n": [1,2]},
380 {"b":1, "n": ["zzz"]},
381 {"a":0, "n": [0.1, 0.02]}
382 ]',
383 '$[*]' columns (
384 id for ordinality,
385 jpath varchar(100) path '$.a',
386 jexst int exists path '$.b',
387 nested path '$.n[*]' columns (
388 id_n for ordinality,
389 jpath_n varchar(50) path '$')
390 )
391 ) as tt;
392 id jpath jexst id_n jpath_n
393 1 3 0 1 b
394 1 3 0 2 a
395 1 3 0 3 c
396 2 2 0 1 1
397 2 2 0 2 2
398 3 NULL 1 1 zzz
399 4 0 0 1 0.1
400 4 0 0 2 0.02
401 select * from json_table(
402 '[
403 {"a":"3", "n": ["b","a","c"]},
404 {"a":2, "n": [1,2]},
405 {"b":1, "n": ["zzz"]},
406 {"a":0, "n": [0.1, 0.02]}
407 ]',
408 '$[*]' columns (
409 id for ordinality,
410 jpath varchar(100) path '$.a',
411 jexst int exists path '$.b',
412 nested path '$.n[*]' columns (
413 id_n1 for ordinality,
414 jpath_n1 varchar(50) path '$') ,
415 nested path '$.n[*]' columns (
416 id_n2 for ordinality,
417 jpath_n2 varchar(50) path '$')
418 )
419 ) as tt;
420 id jpath jexst id_n1 jpath_n1 id_n2 jpath_n2
421 1 3 0 1 b NULL NULL
422 1 3 0 2 a NULL NULL
423 1 3 0 3 c NULL NULL
424 1 3 0 NULL NULL 1 b
425 1 3 0 NULL NULL 2 a
426 1 3 0 NULL NULL 3 c
427 2 2 0 1 1 NULL NULL
428 2 2 0 2 2 NULL NULL
429 2 2 0 NULL NULL 1 1
430 2 2 0 NULL NULL 2 2
431 3 NULL 1 1 zzz NULL NULL
432 3 NULL 1 NULL NULL 1 zzz
433 4 0 0 1 0.1 NULL NULL
434 4 0 0 2 0.02 NULL NULL
435 4 0 0 NULL NULL 1 0.1
436 4 0 0 NULL NULL 2 0.02
437 select * from json_table(
438 '[
439 {"a":"3", "n": [ {"ll":["b1","b2","b3"]}, {"ll": ["a1","a2"]},
440 {"ll":["c"]} ]},
441 {"a":2, "n": [{"ll":[1,11,111]},{"ll":[2]}]},
442 {"b":1, "n": [{"ll":["zzz"]}]},
443 {"a":0, "n": [{"ll":[0.1,0.01]}, {"ll":[0.02,0.002,0.0002]}]}
444 ]',
445 '$[*]' columns (
446 id1 for ordinality,
447 jpath varchar(100) path '$.a',
448 jexst int exists path '$.b',
449 nested path '$.n[*]' columns (
450 id2 for ordinality,
451 nested path '$.ll[*]' columns (
452 id3 for ordinality,
453 jpath_3 varchar(50) path '$')
454 ),
455 nested path '$.n[*]' columns (
456 id4 for ordinality,
457 jpath_4 json path '$')
458 )
459 ) as tt;
460 id1 jpath jexst id2 id3 jpath_3 id4 jpath_4
461 1 3 0 1 1 b1 NULL NULL
462 1 3 0 1 2 b2 NULL NULL
463 1 3 0 1 3 b3 NULL NULL
464 1 3 0 2 1 a1 NULL NULL
465 1 3 0 2 2 a2 NULL NULL
466 1 3 0 3 1 c NULL NULL
467 1 3 0 NULL NULL NULL 1 {"ll": ["b1", "b2", "b3"]}
468 1 3 0 NULL NULL NULL 2 {"ll": ["a1", "a2"]}
469 1 3 0 NULL NULL NULL 3 {"ll": ["c"]}
470 2 2 0 1 1 1 NULL NULL
471 2 2 0 1 2 11 NULL NULL
472 2 2 0 1 3 111 NULL NULL
473 2 2 0 2 1 2 NULL NULL
474 2 2 0 NULL NULL NULL 1 {"ll": [1, 11, 111]}
475 2 2 0 NULL NULL NULL 2 {"ll": [2]}
476 3 NULL 1 1 1 zzz NULL NULL
477 3 NULL 1 NULL NULL NULL 1 {"ll": ["zzz"]}
478 4 0 0 1 1 0.1 NULL NULL
479 4 0 0 1 2 0.01 NULL NULL
480 4 0 0 2 1 0.02 NULL NULL
481 4 0 0 2 2 0.002 NULL NULL
482 4 0 0 2 3 0.0002 NULL NULL
483 4 0 0 NULL NULL NULL 1 {"ll": [0.1, 0.01]}
484 4 0 0 NULL NULL NULL 2 {"ll": [0.02, 0.002, 0.0002]}
485 explain select * from json_table(
486 '[
487 {"a":"3", "n": [ {"ll":["b1","b2","b3"]}, {"ll": ["a1","a2"]},
488 {"ll":["c"]} ]},
489 {"a":2, "n": [{"ll":[1,11,111]},{"ll":[2]}]},
490 {"b":1, "n": [{"ll":["zzz"]}]},
491 {"a":0, "n": [{"ll":[0.1,0.01]}, {"ll":[0.02,0.002,0.0002]}]}
492 ]',
493 '$[*]' columns (
494 id1 for ordinality,
495 jpath varchar(100) path '$.a',
496 jexst int exists path '$.b',
497 nested path '$.n[*]' columns (
498 id2 for ordinality,
499 nested path '$.ll[*]' columns (
500 id3 for ordinality,
501 jpath_3 varchar(50) path '$')
502 ),
503 nested path '$.n[*]' columns (
504 id4 for ordinality,
505 jpath_4 json path '$')
506 )
507 ) as tt;
508 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
509 1 SIMPLE tt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
510 Warnings:
511 Note 1003 /* select#1 */ select `tt`.`id1` AS `id1`,`tt`.`jpath` AS `jpath`,`tt`.`jexst` AS `jexst`,`tt`.`id2` AS `id2`,`tt`.`id3` AS `id3`,`tt`.`jpath_3` AS `jpath_3`,`tt`.`id4` AS `id4`,`tt`.`jpath_4` AS `jpath_4` from json_table('[\n {"a":"3", "n": [ {"ll":["b1","b2","b3"]}, {"ll": ["a1","a2"]},\n {"ll":["c"]} ]},\n {"a":2, "n": [{"ll":[1,11,111]},{"ll":[2]}]},\n {"b":1, "n": [{"ll":["zzz"]}]},\n {"a":0, "n": [{"ll":[0.1,0.01]}, {"ll":[0.02,0.002,0.0002]}]}\n ]', '$[*]' columns (`id1` for ordinality, `jpath` varchar(100) character set utf8mb4 path '$.a', `jexst` int exists path '$.b', nested path '$.n[*]' columns (`id2` for ordinality, nested path '$.ll[*]' columns (`id3` for ordinality, `jpath_3` varchar(50) character set utf8mb4 path '$')), nested path '$.n[*]' columns (`id4` for ordinality, `jpath_4` json path '$'))) `tt`
512 ord should be 1,1,1,2, which tells that first two values of 'l' are
513 from the same object, and next two are from different objects
514 SELECT *
515 FROM JSON_TABLE(
516 '[{"a": "a_val",
517 "b": [
518 {"c": "c_val",
519 "l": [1,2]}
520 ]
521 }, {"a": "a_val",
522 "b": [
523 {"c": "c_val",
524 "l": [11]},
525 {"c": "c_val",
526 "l": [22]}
527 ]
528 }]',
529 '$[*]' COLUMNS (
530 apath VARCHAR(10) PATH '$.a',
531 NESTED PATH '$.b[*]' COLUMNS (
532 bpath VARCHAR(10) PATH '$.c',
533 ord FOR ORDINALITY,
534 NESTED PATH '$.l[*]' COLUMNS (
535 lpath varchar(10) PATH '$'
536 )
537 )
538 )) as jt;
539 apath bpath ord lpath
540 a_val c_val 1 1
541 a_val c_val 1 2
542 a_val c_val 1 11
543 a_val c_val 2 22
544 explain SELECT *
545 FROM JSON_TABLE(
546 '[{"a": "a_val",
547 "b": [
548 {"c": "c_val",
549 "l": [1,2]}
550 ]
551 }, {"a": "a_val",
552 "b": [
553 {"c": "c_val",
554 "l": [11]},
555 {"c": "c_val",
556 "l": [22]}
557 ]
558 }]',
559 '$[*]' COLUMNS (
560 apath VARCHAR(10) PATH '$.a',
561 NESTED PATH '$.b[*]' COLUMNS (
562 bpath VARCHAR(10) PATH '$.c',
563 ord FOR ORDINALITY,
564 NESTED PATH '$.l[*]' COLUMNS (
565 lpath varchar(10) PATH '$'
566 )
567 )
568 )) as jt;
569 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
570 1 SIMPLE jt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
571 Warnings:
572 Note 1003 /* select#1 */ select `jt`.`apath` AS `apath`,`jt`.`bpath` AS `bpath`,`jt`.`ord` AS `ord`,`jt`.`lpath` AS `lpath` from json_table('[{"a": "a_val",\n "b": [\n {"c": "c_val",\n "l": [1,2]}\n ]\n }, {"a": "a_val",\n "b": [\n {"c": "c_val",\n "l": [11]},\n {"c": "c_val",\n "l": [22]}\n ]\n }]', '$[*]' columns (`apath` varchar(10) character set utf8mb4 path '$.a', nested path '$.b[*]' columns (`bpath` varchar(10) character set utf8mb4 path '$.c', `ord` for ordinality, nested path '$.l[*]' columns (`lpath` varchar(10) character set utf8mb4 path '$')))) `jt`
573 CREATE TABLE jt( i JSON );
574 SELECT * FROM jt, JSON_TABLE(jt.i, '$' COLUMNS (a INT PATH '$')) AS tt
575 WHERE a=1;
576 i a
577 EXPLAIN SELECT * FROM jt, JSON_TABLE(jt.i, '$' COLUMNS (a INT PATH '$')) AS tt
578 WHERE a=1;
579 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
580 1 SIMPLE jt NULL ALL NULL NULL NULL NULL 1 100.00 NULL
581 1 SIMPLE tt NULL ref <auto_key0> <auto_key0> 5 const 1 100.00 Table function: json_table; Using temporary; Using index
582 Warnings:
583 Note 1003 /* select#1 */ select `test`.`jt`.`i` AS `i`,`tt`.`a` AS `a` from `test`.`jt` join json_table(`test`.`jt`.`i`, '$' columns (`a` int path '$')) `tt` where (`tt`.`a` = 1)
584 SELECT * FROM (
585 SELECT * FROM jt, JSON_TABLE(jt.i, '$' COLUMNS (a INT PATH '$')) AS tt
586 WHERE a=1) AS ttt;
587 i a
588 EXPLAIN SELECT * FROM (
589 SELECT * FROM jt, JSON_TABLE(jt.i, '$' COLUMNS (a INT PATH '$')) AS tt
590 WHERE a=1) AS ttt;
591 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
592 1 SIMPLE jt NULL ALL NULL NULL NULL NULL 1 100.00 NULL
593 1 SIMPLE tt NULL ref <auto_key0> <auto_key0> 5 const 1 100.00 Table function: json_table; Using temporary; Using index
594 Warnings:
595 Note 1003 /* select#1 */ select `test`.`jt`.`i` AS `i`,`tt`.`a` AS `a` from `test`.`jt` join json_table(`test`.`jt`.`i`, '$' columns (`a` int path '$')) `tt` where (`tt`.`a` = 1)
596 DROP TABLE jt;
597 SELECT * FROM JSON_TABLE(CAST(CAST("11:22:33" AS TIME) AS JSON),
598 '$' COLUMNS (dt DATE PATH '$')) as tt;
599 dt
600 NULL
601 SELECT * FROM JSON_TABLE(CAST(CAST("11:22:33" AS TIME) AS JSON),
602 '$' COLUMNS (dt TIME PATH '$')) as tt;
603 dt
604 11:22:33
605 SELECT * FROM JSON_TABLE(CAST(CAST("2001-02-03" AS DATE) AS JSON),
606 '$' COLUMNS (dt DATE PATH '$')) as tt;
607 dt
608 2001-02-03
609 CREATE VIEW v AS
610 SELECT * FROM JSON_TABLE('[1,2,3]',
611 '$[*]' COLUMNS (num INT PATH '$.a'
612 DEFAULT '123' ON EMPTY
613 DEFAULT '456' ON ERROR)) AS jt;
614 SELECT * FROM v;
615 num
616 123
617 123
618 123
619 SHOW CREATE VIEW v;
620 View Create View character_set_client collation_connection
621 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 path '$.a' default '123' on empty default '456' on error)) `jt` utf8mb4 utf8mb4_0900_ai_ci
622 DROP VIEW v;
623 SELECT * FROM JSON_TABLE('"asdf"',
624 '$' COLUMNS (a INT PATH '$' ERROR ON ERROR)) AS jt;
625 ERROR 22018: Invalid JSON value for CAST to INTEGER from column a at row 1
626 SELECT * FROM
627 JSON_TABLE('[{"a":1},{"a":2}]',
628 '$' COLUMNS (a INT PATH '$[*].a' ERROR ON ERROR)) AS jt;
629 ERROR 2203F: Can't store an array or an object in the scalar JSON_TABLE column 'a'
630 SELECT * FROM
631 JSON_TABLE('[{"a":1},{"a":2}]',
632 '$' COLUMNS (a JSON PATH '$[*].a' ERROR ON ERROR)) AS jt;
633 a
634 [1, 2]
635 SELECT * FROM
636 JSON_TABLE('123.456', '$' COLUMNS (a DECIMAL(2,1) PATH '$' ERROR ON ERROR)) AS jt;
637 ERROR 22003: Value is out of range for JSON_TABLE's column 'a'
638 SELECT * FROM
639 JSON_TABLE('123.456', '$' COLUMNS (a DECIMAL(2,1) PATH '$')) AS jt;
640 a
641 NULL
642 SELECT * FROM
643 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":{}}}}}}}}}}}}}}}}}}}',
644 '$' COLUMNS (i0 INT PATH '$.a',
645 NESTED PATH '$.b' COLUMNS (i1 INT PATH '$.a',
646 NESTED PATH '$.b' COLUMNS (i2 INT PATH '$.a',
647 NESTED PATH '$.b' COLUMNS (i3 INT PATH '$.a',
648 NESTED PATH '$.b' COLUMNS (i4 INT PATH '$.a',
649 NESTED PATH '$.b' COLUMNS (i5 INT PATH '$.a',
650 NESTED PATH '$.b' COLUMNS (i6 INT PATH '$.a',
651 NESTED PATH '$.b' COLUMNS (i7 INT PATH '$.a',
652 NESTED PATH '$.b' COLUMNS (i8 INT PATH '$.a',
653 NESTED PATH '$.b' COLUMNS (i9 INT PATH '$.a',
654 NESTED PATH '$.b' COLUMNS (i10 INT PATH '$.a',
655 NESTED PATH '$.b' COLUMNS (i11 INT PATH '$.a',
656 NESTED PATH '$.b' COLUMNS (i12 INT PATH '$.a',
657 NESTED PATH '$.b' COLUMNS (i13 INT PATH '$.a',
658 NESTED PATH '$.b' COLUMNS (i14 INT PATH '$.a',
659 NESTED PATH '$.b' COLUMNS (i15 INT PATH '$.a',
660 NESTED PATH '$.b' COLUMNS (i16 INT PATH '$.a',
661 NESTED PATH '$.b' COLUMNS (i17 INT PATH '$.a',
662 NESTED PATH '$.b' COLUMNS (i18 INT PATH '$.a',
663 NESTED PATH '$.b' COLUMNS (i19 INT PATH '$.a',
664 NESTED PATH '$.b' COLUMNS (i20 INT PATH '$.a'
665 )))))))))))))))))))))) jt;
666 ERROR 42000: More than supported 16 NESTED PATHs were found in JSON_TABLE 'jt'
667 CREATE TABLE t1(id int, jd JSON);
668 INSERT INTO t1 values (1, '[1,3,5]'),(2,'[2,4,6]');
669 SELECT id, jt.* FROM t1,
670 JSON_TABLE(jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
671 val INT PATH '$')) AS jt;
672 id jid val
673 1 1 1
674 1 2 3
675 1 3 5
676 2 1 2
677 2 2 4
678 2 3 6
679 SELECT /*+ JOIN_ORDER(jt, t1) */ id, jt.*
680 FROM t1,
681 JSON_TABLE(jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
682 val INT PATH '$')) AS jt;
683 id jid val
684 1 1 1
685 1 2 3
686 1 3 5
687 2 1 2
688 2 2 4
689 2 3 6
690 EXPLAIN SELECT /*+ JOIN_ORDER(jt, t1) */ id, jt.*
691 FROM t1,
692 JSON_TABLE(jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
693 val INT PATH '$')) AS jt;
694 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
695 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
696 1 SIMPLE jt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
697 Warnings:
698 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`jt`.`jid` AS `jid`,`jt`.`val` AS `val` from `test`.`t1` join json_table(`test`.`t1`.`jd`, '$[*]' columns (`jid` for ordinality, `val` int path '$')) `jt`
699 SELECT /*+ JOIN_ORDER(t2,jt) */ t1.id, t2.id, jt.*
700 FROM t1,
701 JSON_TABLE(t1.jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
702 val INT PATH '$')) AS jt,
703 t1 AS t2;
704 id id jid val
705 1 1 1 1
706 1 1 2 3
707 1 1 3 5
708 1 2 1 1
709 1 2 2 3
710 1 2 3 5
711 2 1 1 2
712 2 1 2 4
713 2 1 3 6
714 2 2 1 2
715 2 2 2 4
716 2 2 3 6
717 EXPLAIN SELECT t1.id, t2.id, jt.*
718 FROM t1,
719 JSON_TABLE(t1.jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
720 val INT PATH '$')) AS jt,
721 t1 AS t2;
722 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
723 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
724 1 SIMPLE jt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
725 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using join buffer (hash join)
726 Warnings:
727 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t2`.`id` AS `id`,`jt`.`jid` AS `jid`,`jt`.`val` AS `val` from `test`.`t1` join json_table(`test`.`t1`.`jd`, '$[*]' columns (`jid` for ordinality, `val` int path '$')) `jt` join `test`.`t1` `t2`
728 EXPLAIN SELECT /*+ JOIN_ORDER(t2,jt) */ t1.id, t2.id, jt.*
729 FROM t1,
730 JSON_TABLE(t1.jd, '$[*]' COLUMNS (jid FOR ORDINALITY,
731 val INT PATH '$')) AS jt,
732 t1 AS t2;
733 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
734 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
735 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL 2 100.00 Using join buffer (hash join)
736 1 SIMPLE jt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
737 Warnings:
738 Note 1003 /* select#1 */ select /*+ JOIN_ORDER(@`select#1` `t2`,`jt`) */ `test`.`t1`.`id` AS `id`,`test`.`t2`.`id` AS `id`,`jt`.`jid` AS `jid`,`jt`.`val` AS `val` from `test`.`t1` join json_table(`test`.`t1`.`jd`, '$[*]' columns (`jid` for ordinality, `val` int path '$')) `jt` join `test`.`t1` `t2`
739 SELECT * FROM t1 WHERE id IN
740 (SELECT * FROM JSON_TABLE('[1,2]', '$[*]' COLUMNS
741 (id INT PATH '$')) AS jt);
742 id jd
743 1 [1, 3, 5]
744 2 [2, 4, 6]
745 EXPLAIN SELECT * FROM t1 WHERE id IN
746 (SELECT * FROM JSON_TABLE('[1,2]', '$[*]' COLUMNS
747 (id INT PATH '$')) AS jt);
748 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
749 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
750 1 SIMPLE jt NULL ref <auto_key0> <auto_key0> 5 test.t1.id 2 100.00 Table function: json_table; Using temporary; Using index; FirstMatch(t1)
751 Warnings:
752 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`jd` AS `jd` from `test`.`t1` semi join (json_table('[1,2]', '$[*]' columns (`id` int path '$')) `jt`) where (`jt`.`id` = `test`.`t1`.`id`)
753 SELECT * FROM t1 WHERE id IN
754 (SELECT * FROM JSON_TABLE(t1.jd, '$[*]' COLUMNS
755 (id INT PATH '$')) AS jt);
756 id jd
757 1 [1, 3, 5]
758 2 [2, 4, 6]
759 EXPLAIN SELECT * FROM t1 WHERE id IN
760 (SELECT * FROM JSON_TABLE(t1.jd, '$[*]' COLUMNS
761 (id INT PATH '$')) AS jt);
762 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
763 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
764 1 SIMPLE jt NULL ref <auto_key0> <auto_key0> 5 test.t1.id 2 100.00 Table function: json_table; Using temporary; Using index; FirstMatch(t1)
765 Warnings:
766 Note 1276 Field or reference 'test.t1.jd' of SELECT #2 was resolved in SELECT #1
767 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`jd` AS `jd` from `test`.`t1` semi join (json_table(`test`.`t1`.`jd`, '$[*]' columns (`id` int path '$')) `jt`) where (`jt`.`id` = `test`.`t1`.`id`)
768 SELECT id, jt1.*, jt2.*
769 FROM t1, JSON_TABLE(jd, '$' COLUMNS (data1 JSON PATH '$')) AS jt1,
770 JSON_TABLE(data1, '$[*]' COLUMNS (id2 INT PATH '$')) AS jt2;
771 id data1 id2
772 1 [1, 3, 5] 1
773 1 [1, 3, 5] 3
774 1 [1, 3, 5] 5
775 2 [2, 4, 6] 2
776 2 [2, 4, 6] 4
777 2 [2, 4, 6] 6
778 EXPLAIN SELECT id, jt1.*, jt2.*
779 FROM t1, JSON_TABLE(jd, '$' COLUMNS (data1 JSON PATH '$')) AS jt1,
780 JSON_TABLE(data1, '$[*]' COLUMNS (id2 INT PATH '$')) AS jt2;
781 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
782 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 NULL
783 1 SIMPLE jt1 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
784 1 SIMPLE jt2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
785 Warnings:
786 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`jt1`.`data1` AS `data1`,`jt2`.`id2` AS `id2` from `test`.`t1` join json_table(`test`.`t1`.`jd`, '$' columns (`data1` json path '$')) `jt1` join json_table(`jt1`.`data1`, '$[*]' columns (`id2` int path '$')) `jt2`
787 DROP TABLE t1;
788 SELECT * FROM JSON_TABLE ('"asdf"', '$' COLUMNS(
789 tm TIME PATH '$',
790 dt DATE PATH '$',
791 i INT PATH '$',
792 f FLOAT PATH '$',
793 d DECIMAL PATH '$')) AS jt;
794 tm dt i f d
795 NULL NULL NULL NULL NULL
796 SELECT * FROM
797 JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT NULL ON EMPTY)) jt;
798 ERROR 42000: 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 'NULL ON EMPTY)) jt' at line 2
799 SELECT * FROM
800 JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT NULL ON ERROR)) jt;
801 ERROR 42000: 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 'NULL ON ERROR)) jt' at line 2
802 SELECT * FROM
803 JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT 0 ON EMPTY)) jt;
804 ERROR 42000: This version of MySQL doesn't yet support 'non-string DEFAULT value for a column in a JSON_TABLE expression'
805 SELECT * FROM
806 JSON_TABLE('{}', '$' COLUMNS (x INT PATH '$.x' DEFAULT 0 ON ERROR)) jt;
807 ERROR 42000: This version of MySQL doesn't yet support 'non-string DEFAULT value for a column in a JSON_TABLE expression'
808 SELECT * FROM
809 JSON_TABLE('{}', '$' COLUMNS (x DATE
810 PATH '$.x'
811 DEFAULT DATE'2020-01-01' ON EMPTY)) jt;
812 ERROR 42000: This version of MySQL doesn't yet support 'non-string DEFAULT value for a column in a JSON_TABLE expression'
813 SELECT * FROM
814 JSON_TABLE('{}', '$' COLUMNS (x DATE
815 PATH '$.x'
816 DEFAULT DATE'2020-01-01' ON ERROR)) jt;
817 ERROR 42000: This version of MySQL doesn't yet support 'non-string DEFAULT value for a column in a JSON_TABLE expression'
818 #
819 # Bug#25413069: SIG11 IN CHECK_COLUMN_GRANT_IN_TABLE_REF
820 #
821 SELECT a FROM JSON_TABLE(abc, '$[*]' COLUMNS ( a int path '$.a')) AS jt;
822 ERROR 42S22: Unknown column 'abc' in 'a table function argument'
823 #
824 # Bug#25420680: ASSERTION `THD->IS_ERROR()' FAILED IN SQL/SQL_SELECT.CC
825 #
826 SELECT * FROM JSON_TABLE('{"a":"2017-11-1"}',
827 '$' COLUMNS (jpath DATE PATH '$.a')) AS jt;
828 jpath
829 2017-11-01
830 #
831 # Bug#25413826: ASSERTION `TABLE_LIST->ALIAS' FAILED
832 #
833 SELECT * FROM JSON_TABLE('[{"a": 1, "b": 2}]',
834 '$[*]' COLUMNS ( a int path '$.b'));
835 ERROR 42000: Every table function must have an alias
836 #
837 # Bug#25421464: ASSERTION `!STRCMP(TABLE_REF->TABLE_NAME, ...
838 #
839 CREATE VIEW v1 AS
840 SELECT * FROM JSON_TABLE('[{"a": 1, "b": 2}]',
841 '$[*]' COLUMNS ( a INT PATH '$.b')) AS jt;
842 SELECT * FROM v1;
843 a
844 2
845 SHOW CREATE VIEW v1;
846 View Create View character_set_client collation_connection
847 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 path '$.b')) `jt` utf8mb4 utf8mb4_0900_ai_ci
848 DROP VIEW v1;
849 #
850 # Bug#25427457: ASSERTION `!((*REG_FIELD)->FLAGS & 16)'
851 #
852 SELECT * FROM JSON_TABLE('{"a":"1"}',
853 '$' COLUMNS (jpath JSON PATH '$.a',
854 o FOR ORDINALITY)) AS jt
855 WHERE o = 1;
856 jpath o
857 "1" 1
858 #
859 # Bug#25427982: ASSERTION `DERIVED' FAILED IN SQL/TABLE.H
860 #
861 SELECT je,o FROM JSON_TABLE('{"a":"1"}',
862 '$' COLUMNS (o FOR ORDINALITY,
863 je BIGINT EXISTS PATH '$.a')) AS jt
864 GROUP BY je;
865 ERROR 42000: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'jt.o' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
866 SELECT je,COUNT(o) FROM JSON_TABLE('{"a":"1"}',
867 '$' COLUMNS (o FOR ORDINALITY,
868 je BIGINT EXISTS PATH '$.a')) AS jt
869 GROUP BY je;
870 je COUNT(o)
871 1 1
872 #
873 # Bug#25413194: ASSERTION `!(WANT_PRIVILEGE & ~(GRANT->WANT_PRIVILEGE
874 #
875 CREATE TABLE t1 (j JSON);
876 SELECT * FROM t1,JSON_TABLE(t1.j, '$[*]' COLUMNS ( a int path '$.b')) AS jt;
877 j a
878 DROP TABLE t1;
879 #
880 # Bug#25460537:SIG 11 IN NEXT_FAST AT SQL/SQL_LIST.H
881 #
882 PREPARE STMT FROM
883 "SELECT * FROM JSON_TABLE(
884 \'[{\"a\":\"3\"},{\"a\":2},{\"b\":1},{\"a\":0}]\',
885 \'$[*]\' COLUMNS (id
886 FOR ORDINALITY,
887 jpath VARCHAR(100) PATH \'$.a\',
888 jexst INT EXISTS PATH \'$.b\')
889 ) as tt";
890 EXECUTE STMT;
891 id jpath jexst
892 1 3 0
893 2 2 0
894 3 NULL 1
895 4 0 0
896 EXECUTE STMT;
897 id jpath jexst
898 1 3 0
899 2 2 0
900 3 NULL 1
901 4 0 0
902 DEALLOCATE PREPARE stmt;
903 #
904 # Bug#25522353: SIG 11 IN JOIN::MAKE_JOIN_PLAN | SQL/SQL_OPTIMIZER.CC
905 #
906 CREATE TABLE t1 (id INT, jc JSON);
907 SELECT * FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
908 (id FOR ORDINALITY)) as jt ON t1.jc=jt.id;
909 ERROR 42S02: Unknown table 't1' in a table function argument
910 SELECT * FROM JSON_TABLE(t1.jc, '$' COLUMNS
911 (id FOR ORDINALITY)) as jt LEFT JOIN t1 ON t1.jc=jt.id;
912 ERROR 42S02: Unknown table 't1' in a table function argument
913 SELECT * FROM JSON_TABLE(t1.jc, '$' COLUMNS
914 (id FOR ORDINALITY)) as jt RIGHT JOIN t1 ON t1.jc=jt.id;
915 id id jc
916 SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
917 (id FOR ORDINALITY)) as jt ON t1.jc=jt.id;
918 id jc id
919 EXPLAIN SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
920 (id FOR ORDINALITY)) as jt ON t1.jc=jt.id;
921 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
922 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
923 1 SIMPLE jt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary; Using where
924 Warnings:
925 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`jc` AS `jc`,`jt`.`id` AS `id` from `test`.`t1` left join json_table(`test`.`t1`.`jc`, '$' columns (`id` for ordinality)) `jt` on((`test`.`t1`.`jc` = `jt`.`id`)) where true
926 SELECT * FROM t1 t1o RIGHT JOIN t1 ON t1o.id=t1.id
927 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
928 (id FOR ORDINALITY)) as jt ON t1.jc=jt.id;
929 id jc id jc id
930 EXPLAIN SELECT * FROM t1 t1o RIGHT JOIN t1 ON t1o.id=t1.id
931 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
932 (id FOR ORDINALITY)) as jt ON t1.jc=jt.id;
933 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
934 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 1 100.00 NULL
935 1 SIMPLE t1o NULL ALL NULL NULL NULL NULL 1 100.00 Using where; Using join buffer (hash join)
936 1 SIMPLE jt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary; Using where
937 Warnings:
938 Note 1003 /* select#1 */ select `test`.`t1o`.`id` AS `id`,`test`.`t1o`.`jc` AS `jc`,`test`.`t1`.`id` AS `id`,`test`.`t1`.`jc` AS `jc`,`jt`.`id` AS `id` from `test`.`t1` left join `test`.`t1` `t1o` on((`test`.`t1o`.`id` = `test`.`t1`.`id`)) left join json_table(`test`.`t1`.`jc`, '$' columns (`id` for ordinality)) `jt` on((`test`.`t1`.`jc` = `jt`.`id`)) where true
939 SELECT * FROM t1 AS t1o RIGHT JOIN
940 (t1 AS t1i JOIN JSON_TABLE(t1o.jc, '$' COLUMNS
941 (id FOR ORDINALITY)) as jt ON t1i.jc=jt.id)
942 ON t1o.id=t1i.id;
943 ERROR 42S22: Unknown column 't1o.jc' in 'a table function argument'
944 SELECT * FROM t1 AS t1o RIGHT JOIN
945 (t1 AS t1i RIGHT JOIN JSON_TABLE(t1o.jc, '$' COLUMNS
946 (id FOR ORDINALITY)) as jt ON t1i.jc=jt.id)
947 ON t1o.id=t1i.id;
948 ERROR 42S02: Unknown table 't1o' in a table function argument
949 WITH qn AS
950 (SELECT jt.* FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
951 (id FOR ORDINALITY)) as jt ON t1.jc=jt.id)
952 SELECT * from qn;
953 ERROR 42S02: Unknown table 't1' in a table function argument
954 WITH qn AS
955 (SELECT 1 UNION
956 SELECT jt.id FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
957 (id FOR ORDINALITY)) as jt ON t1.jc=jt.id)
958 SELECT * from qn;
959 ERROR 42S02: Unknown table 't1' in a table function argument
960 SELECT * FROM t1 AS t1o RIGHT JOIN
961 (t1 AS t1i JOIN JSON_TABLE(t1o.jc, '$' COLUMNS
962 (id FOR ORDINALITY)) as jt ON t1i.jc=jt.id)
963 ON t1o.id=t1i.id;
964 ERROR 42S22: Unknown column 't1o.jc' in 'a table function argument'
965 SELECT * FROM t1 AS t1o RIGHT JOIN
966 (t1 AS t1i RIGHT JOIN JSON_TABLE(t1o.jc, '$' COLUMNS
967 (id FOR ORDINALITY)) as jt ON t1i.jc=jt.id)
968 ON t1o.id=t1i.id;
969 ERROR 42S02: Unknown table 't1o' in a table function argument
970 INSERT INTO t1 VALUES(1,"1"),(2,"4"),(3,"3");
971 ANALYZE TABLE t1;
972 Table Op Msg_type Msg_text
973 test.t1 analyze status OK
974 SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
975 (id INT PATH '$')) as jt ON t1.id=jt.id;
976 id jc id
977 1 1 1
978 2 4 NULL
979 3 3 3
980 EXPLAIN SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS
981 (id INT PATH '$')) as jt ON t1.id=jt.id;
982 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
983 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 3 100.00 NULL
984 1 SIMPLE jt NULL ref <auto_key0> <auto_key0> 5 test.t1.id 2 100.00 Table function: json_table; Using temporary; Using index
985 Warnings:
986 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`jc` AS `jc`,`jt`.`id` AS `id` from `test`.`t1` left join json_table(`test`.`t1`.`jc`, '$' columns (`id` int path '$')) `jt` on((`jt`.`id` = `test`.`t1`.`id`)) where true
987 SELECT * FROM t1
988 LEFT JOIN
989 JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1.jc=jt.id
990 RIGHT JOIN
991 JSON_TABLE(jt.id, '$' COLUMNS (id FOR ORDINALITY)) as jt1 ON jt.id=jt1.id;
992 ERROR 42S02: Unknown table 'jt' in a table function argument
993 DROP TABLE t1;
994 set @save_mem_se= @@internal_tmp_mem_storage_engine;
995 set @@internal_tmp_mem_storage_engine=MEMORY;
996 set @save_heap_size= @@max_heap_table_size;
997 set @@max_heap_table_size= 16384;
998 FLUSH STATUS;
999 SELECT * FROM JSON_TABLE(
1000 '[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20]',
1001 '$[*]' COLUMNS (
1002 c1 CHAR(255) PATH '$',
1003 c2 CHAR(255) PATH '$',
1004 c3 CHAR(255) PATH '$',
1005 c4 CHAR(255) PATH '$',
1006 c5 CHAR(255) PATH '$',
1007 c6 CHAR(255) PATH '$',
1008 c7 CHAR(255) PATH '$',
1009 c8 CHAR(255) PATH '$')) AS jt;
1010 c1 c2 c3 c4 c5 c6 c7 c8
1011 1 1 1 1 1 1 1 1
1012 2 2 2 2 2 2 2 2
1013 3 3 3 3 3 3 3 3
1014 4 4 4 4 4 4 4 4
1015 5 5 5 5 5 5 5 5
1016 6 6 6 6 6 6 6 6
1017 7 7 7 7 7 7 7 7
1018 8 8 8 8 8 8 8 8
1019 9 9 9 9 9 9 9 9
1020 10 10 10 10 10 10 10 10
1021 11 11 11 11 11 11 11 11
1022 12 12 12 12 12 12 12 12
1023 13 13 13 13 13 13 13 13
1024 14 14 14 14 14 14 14 14
1025 15 15 15 15 15 15 15 15
1026 16 16 16 16 16 16 16 16
1027 17 17 17 17 17 17 17 17
1028 18 18 18 18 18 18 18 18
1029 19 19 19 19 19 19 19 19
1030 20 20 20 20 20 20 20 20
1031 SHOW STATUS LIKE '%tmp%';
1032 Variable_name Value
1033 Created_tmp_disk_tables 1
1034 Created_tmp_files 0
1035 Created_tmp_tables 1
1036 set @@max_heap_table_size= @save_heap_size;
1037 set @@internal_tmp_mem_storage_engine= @save_mem_se;
1038 #
1039 # Bug#25504063: EXPLAIN OF JSON_TABLE QUERY USES INTERNAL TEMP TABLES
1040 #
1041 FLUSH STATUS;
1042 SELECT * FROM
1043 JSON_TABLE(
1044 '[{"a":"3"}]',
1045 '$[*]' COLUMNS (id FOR ORDINALITY)
1046 ) AS tt;
1047 id
1048 1
1049 SHOW STATUS LIKE '%tmp%';
1050 Variable_name Value
1051 Created_tmp_disk_tables 0
1052 Created_tmp_files 0
1053 Created_tmp_tables 1
1054 #
1055 # Bug#25525409: ASSERTION `TABLE_LIST->TABLE' FAILED IN SQL/SQL_BASE.CC
1056 #
1057 SELECT * FROM JSON_TABLE ( ( SELECT a ) , '$.*' COLUMNS (col1 FOR ORDINALITY) )
1058 AS alias1;
1059 ERROR 42S22: Unknown column 'a' in 'field list'
1060 SELECT * FROM JSON_TABLE ( ( SELECT 1 ) , '$.*' COLUMNS (col1 FOR ORDINALITY) )
1061 AS alias1;
1062 ERROR HY000: Incorrect arguments to JSON_TABLE
1063 SELECT * FROM JSON_TABLE ( ( SUM(1) ) , '$.*' COLUMNS (col1 FOR ORDINALITY) )
1064 AS alias1;
1065 ERROR HY000: Invalid use of group function
1066 #
1067 # Bug# #25472875: ERROR SHOULD BE THROWN FOR INCORRECT VALUES
1068 #
1069 SELECT *
1070 FROM JSON_TABLE('{"a":"1993-01-01"}',
1071 '$' COLUMNS (jp DATE PATH '$.b' DEFAULT '1000' ON EMPTY))
1072 AS jt;
1073 ERROR 22003: Value is out of range for JSON_TABLE's column 'jp'
1074 #
1075 # Bug#25532429: INVALID JSON ERROR NOT THROWN WITH EMPTY TABLES JOIN
1076 #
1077 CREATE TABLE t1(j JSON);
1078 SELECT * FROM t1,
1079 JSON_TABLE( 'dqwfjqjf' , '$[*]' COLUMNS (col5 FOR ORDINALITY) ) AS alias7;
1080 ERROR 22032: Invalid JSON text in argument 1 to function json_table: "Invalid value." at position 0.
1081 DROP TABLE t1;
1082 #
1083 # Bug#25540370: SIG 11 IN SHOW_SQL_TYPE|SQL/SQL_SHOW.CC:7063
1084 #
1085 EXPLAIN SELECT * FROM
1086 JSON_TABLE ('[3.14159]',
1087 '$[*]' COLUMNS (col18 CHAR(70) PATH '$')
1088 ) AS alias2;
1089 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1090 1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
1091 Warnings:
1092 Note 1003 /* select#1 */ select `alias2`.`col18` AS `col18` from json_table('[3.14159]', '$[*]' columns (`col18` char(70) character set utf8mb4 path '$')) `alias2`
1093 SELECT * FROM
1094 JSON_TABLE ('[3.14159]',
1095 '$[*]' COLUMNS (col18 CHAR(70) PATH '$')
1096 ) AS alias2;
1097 col18
1098 3.14159
1099 # Too short field causes truncation, error and triggers ON ERROR clause
1100 EXPLAIN SELECT * FROM
1101 JSON_TABLE ('["3.14159"]',
1102 '$[*]' COLUMNS (col18 CHAR(6) PATH '$')
1103 ) AS alias2;
1104 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1105 1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
1106 Warnings:
1107 Note 1003 /* select#1 */ select `alias2`.`col18` AS `col18` from json_table('["3.14159"]', '$[*]' columns (`col18` char(6) character set utf8mb4 path '$')) `alias2`
1108 SELECT * FROM
1109 JSON_TABLE ('["3.14159"]',
1110 '$[*]' COLUMNS (col18 CHAR(6) PATH '$')
1111 ) AS alias2;
1112 col18
1113 NULL
1114 #Truncated space doesn't trigger ON ERROR
1115 EXPLAIN SELECT * FROM
1116 JSON_TABLE ('["3.14159 "]',
1117 '$[*]' COLUMNS (col18 CHAR(7) PATH '$')
1118 ) AS alias2;
1119 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1120 1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
1121 Warnings:
1122 Note 1003 /* select#1 */ select `alias2`.`col18` AS `col18` from json_table('["3.14159 "]', '$[*]' columns (`col18` char(7) character set utf8mb4 path '$')) `alias2`
1123 SELECT * FROM
1124 JSON_TABLE ('["3.14159 "]',
1125 '$[*]' COLUMNS (col18 CHAR(7) PATH '$')
1126 ) AS alias2;
1127 col18
1128 3.14159
1129 EXPLAIN SELECT * FROM
1130 JSON_TABLE ('[3.14159]',
1131 '$[*]' COLUMNS (col18 CHAR(255) PATH '$')
1132 ) AS alias2;
1133 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1134 1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
1135 Warnings:
1136 Note 1003 /* select#1 */ select `alias2`.`col18` AS `col18` from json_table('[3.14159]', '$[*]' columns (`col18` char(255) character set utf8mb4 path '$')) `alias2`
1137 SELECT * FROM
1138 JSON_TABLE ('[3.14159]',
1139 '$[*]' COLUMNS (col18 CHAR(255) PATH '$')
1140 ) AS alias2;
1141 col18
1142 3.14159
1143 EXPLAIN SELECT * FROM
1144 JSON_TABLE ('[3.14159]',
1145 '$[*]' COLUMNS (col18 VARCHAR(70) PATH '$')
1146 ) AS alias2;
1147 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1148 1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
1149 Warnings:
1150 Note 1003 /* select#1 */ select `alias2`.`col18` AS `col18` from json_table('[3.14159]', '$[*]' columns (`col18` varchar(70) character set utf8mb4 path '$')) `alias2`
1151 SELECT * FROM
1152 JSON_TABLE ('[3.14159]',
1153 '$[*]' COLUMNS (col18 VARCHAR(70) PATH '$')
1154 ) AS alias2;
1155 col18
1156 3.14159
1157 EXPLAIN SELECT * FROM
1158 JSON_TABLE ('[3.14159]',
1159 '$[*]' COLUMNS (col18 VARCHAR(255) PATH '$')
1160 ) AS alias2;
1161 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1162 1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
1163 Warnings:
1164 Note 1003 /* select#1 */ select `alias2`.`col18` AS `col18` from json_table('[3.14159]', '$[*]' columns (`col18` varchar(255) character set utf8mb4 path '$')) `alias2`
1165 SELECT * FROM
1166 JSON_TABLE ('[3.14159]',
1167 '$[*]' COLUMNS (col18 VARCHAR(255) PATH '$')
1168 ) AS alias2;
1169 col18
1170 3.14159
1171 EXPLAIN SELECT * FROM
1172 JSON_TABLE ('[3.14159]',
1173 '$[*]' COLUMNS (col18 FLOAT PATH '$')
1174 ) AS alias2;
1175 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1176 1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
1177 Warnings:
1178 Note 1003 /* select#1 */ select `alias2`.`col18` AS `col18` from json_table('[3.14159]', '$[*]' columns (`col18` float path '$')) `alias2`
1179 SELECT * FROM
1180 JSON_TABLE ('[3.14159]',
1181 '$[*]' COLUMNS (col18 FLOAT PATH '$')
1182 ) AS alias2;
1183 col18
1184 3.14159
1185 EXPLAIN SELECT * FROM
1186 JSON_TABLE ('[3.14159]',
1187 '$[*]' COLUMNS (col18 DOUBLE PATH '$')
1188 ) AS alias2;
1189 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1190 1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
1191 Warnings:
1192 Note 1003 /* select#1 */ select `alias2`.`col18` AS `col18` from json_table('[3.14159]', '$[*]' columns (`col18` double path '$')) `alias2`
1193 SELECT * FROM
1194 JSON_TABLE ('[3.14159]',
1195 '$[*]' COLUMNS (col18 DOUBLE PATH '$')
1196 ) AS alias2;
1197 col18
1198 3.14159
1199 EXPLAIN SELECT * FROM
1200 JSON_TABLE ('[3.14159]',
1201 '$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '$')
1202 ) AS alias2;
1203 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1204 1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
1205 Warnings:
1206 Note 1003 /* select#1 */ select `alias2`.`col18` AS `col18` from json_table('[3.14159]', '$[*]' columns (`col18` decimal(3,3) path '$')) `alias2`
1207 SELECT * FROM
1208 JSON_TABLE ('[3.14159]',
1209 '$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '$')
1210 ) AS alias2;
1211 col18
1212 NULL
1213 SELECT * FROM
1214 JSON_TABLE ('[3.14159]',
1215 '$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '$' ERROR ON ERROR)
1216 ) AS alias2;
1217 ERROR 22003: Value is out of range for JSON_TABLE's column 'col18'
1218 EXPLAIN SELECT * FROM
1219 JSON_TABLE ('[0.9]',
1220 '$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '$')
1221 ) AS alias2;
1222 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1223 1 SIMPLE alias2 NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
1224 Warnings:
1225 Note 1003 /* select#1 */ select `alias2`.`col18` AS `col18` from json_table('[0.9]', '$[*]' columns (`col18` decimal(3,3) path '$')) `alias2`
1226 SELECT * FROM
1227 JSON_TABLE ('[0.9]',
1228 '$[*]' COLUMNS (col18 DECIMAL(3,3) PATH '$')
1229 ) AS alias2;
1230 col18
1231 0.900
1232 SELECT * FROM
1233 JSON_TABLE ('["asdf","ghjk"]',
1234 '$[*]' COLUMNS (col18 DECIMAL(4,3) PATH '$'
1235 DEFAULT "3.14159" ON ERROR)
1236 ) AS alias2;
1237 col18
1238 3.142
1239 3.142
1240 Warnings:
1241 Note 1265 Data truncated for column 'col18' at row 1
1242 CREATE TABLE t1(jd JSON);
1243 INSERT INTO t1 VALUES('["asdf"]'),('["ghjk"]');
1244 SELECT * FROM t1,
1245 JSON_TABLE (jd,
1246 '$[*]' COLUMNS (col18 DECIMAL(4,3) PATH '$'
1247 DEFAULT "3.14159" ON ERROR)
1248 ) AS alias2;
1249 jd col18
1250 ["asdf"] 3.142
1251 ["ghjk"] 3.142
1252 Warnings:
1253 Note 1265 Data truncated for column 'col18' at row 1
1254 DROP TABLE t1;
1255 #
1256 # Bug#25540027: SIG 11 IN FIND_FIELD_IN_TABLE | SQL/SQL_BASE.CC
1257 #
1258 CREATE TABLE t1(c1 JSON);
1259 UPDATE t1, JSON_TABLE(t1.c1,'$[*]' COLUMNS (a INT PATH '$.a')) AS jt1
1260 SET jt1.a=1;
1261 ERROR HY000: The target table jt1 of the UPDATE is not updatable
1262 DELETE JSON_TABLE(t1.c1,'$[*]' COLUMNS (a INT PATH '$.a')) AS jt1
1263 FROM t1;
1264 ERROR 42000: 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 'JSON_TABLE(t1.c1,'$[*]' COLUMNS (a INT PATH '$.a')) AS jt1
1265 FROM t1' at line 1
1266 DELETE t1, JSON_TABLE(t1.c1,'$[*]' COLUMNS (a INT PATH '$.a')) AS jt1
1267 USING t1;
1268 ERROR 42000: 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 'JSON_TABLE(t1.c1,'$[*]' COLUMNS (a INT PATH '$.a')) AS jt1
1269 USING t1' at line 1
1270 DROP TABLE t1;
1271 #
1272 # Bug#25547244: ASSERTION `!TABLE || (!TABLE->READ_SET || BITMAP_IS_SET(
1273 #
1274 CREATE TABLE t1(i INT);
1275 INSERT INTO t1 VALUES(1);
1276 WITH cte_query AS
1277 (SELECT * FROM t1, JSON_TABLE ( JSON_OBJECT('ISSKF',i) ,
1278 '$[*]' COLUMNS (jtcol1 INT EXISTS PATH '$[*]') ) AS alias2)
1279 SELECT jtcol1 AS field1 FROM cte_query;
1280 field1
1281 DROP TABLE t1;
1282 #
1283 # Bug#25540675: ASSERTION `!COL->CHILD_JDS->PRODUCING_RECORDS' FAILED
1284 #
1285 CREATE TABLE j1(j JSON);
1286 INSERT INTO j1 VALUES('[1,2,3]'),('[1,2,4]');
1287 SELECT * FROM j1,
1288 JSON_TABLE ( JSON_OBJECT('key1', j) ,
1289 '$.*' COLUMNS (NESTED PATH '$.*' COLUMNS (col11 FOR ORDINALITY))) AS alias2;
1290 j col11
1291 [1, 2, 3] NULL
1292 [1, 2, 4] NULL
1293 DROP TABLE j1;
1294 #
1295 # Bug#25584335: SIG 11 IN TABLE_LIST::FETCH_NUMBER_OF_ROWS
1296 #
1297 CREATE TABLE t1(i INT);
1298 PREPARE stmt FROM "SELECT alias1.i AS field1 FROM (
1299 t1 AS alias1,
1300 (SELECT * FROM
1301 JSON_TABLE ('[1,2,3]' ,
1302 '$[*]' COLUMNS (`col_varchar` FOR ORDINALITY)) AS SQ1_alias1
1303 ) AS alias2 )";
1304 EXECUTE stmt;
1305 field1
1306 DEALLOCATE PREPARE stmt;
1307 DROP TABLE t1;
1308 #
1309 # Bug#25604048: COLUMN NAMES WITH SAME 33-CHAR PREFIX ARE EQUAL
1310 #
1311 SELECT * FROM JSON_TABLE( '{"key1": "test"}' , '$' COLUMNS(
1312 column_name_is_thirty_four_or_more VARCHAR(17) PATH '$.key1',
1313 column_name_is_thirty_four_or_more_yes_indeed VARCHAR(17) PATH '$.key1'
1314 ) ) AS alias1;
1315 column_name_is_thirty_four_or_more column_name_is_thirty_four_or_more_yes_indeed
1316 test test
1317 SELECT * FROM JSON_TABLE( '{"key1": "test"}' , '$' COLUMNS(
1318 `column_name_is_thirty_four_or_more ` VARCHAR(17) PATH '$.key1'
1319 ) ) AS alias1;
1320 ERROR 42000: Incorrect column name 'column_name_is_thirty_four_or_more '
1321 #
1322 # Bug#25604404: JSON_TABLE MORE RESTRICTIVE WITH IDENTIFIERS THAN
1323 # CREATE TABLE
1324 #
1325 SELECT * FROM JSON_TABLE( '[1, 2]', '$' COLUMNS(
1326 one INT PATH '$[0]', two INT PATH '$[1]'
1327 )) AS jt;
1328 one two
1329 1 2
1330 #
1331 # Bug#25588450: SIG 6 IN JSON_WRAPPER::SEEK|SQL/JSON_DOM.CC
1332 #
1333 CREATE TABLE t1(c VARCHAR(10)) ENGINE=MEMORY;
1334 INSERT INTO t1 VALUES('fiheife');
1335 SELECT * FROM `t1` AS alias1, JSON_TABLE ( `c` , '$[*]' COLUMNS (jtcol1 JSON
1336 PATH '$.*')) AS alias2 WHERE jtcol1 <= 'kjfh';
1337 ERROR 22032: Invalid JSON text in argument 1 to function json_table: "Invalid value." at position 1.
1338 DROP TABLE t1;
1339 #
1340 # Bug#25587754: ASSERTION `FIXED == 0 || BASIC_CONST_ITEM()' FAILED
1341 #
1342 PREPARE stmt FROM
1343 "SELECT * FROM JSON_TABLE ( '[1,2]', '$[*]'
1344 COLUMNS (jtcol1 JSON PATH '$.*')) AS alias2";
1345 EXECUTE stmt;
1346 jtcol1
1347 NULL
1348 NULL
1349 DEALLOCATE PREPARE stmt;
1350 #
1351 # Bug#25584593: UNABLE TO USE JSON_TABLE() ON TEXT/BLOB JSON DATA
1352 #
1353 SELECT * FROM JSON_TABLE (NULL, '$.k' COLUMNS (id FOR ORDINALITY)) AS aLias;
1354 id
1355 SELECT * FROM JSON_TABLE (@myjson, '$.k' COLUMNS (id FOR ORDINALITY)) AS alias;
1356 id
1357 SET @myjson = '{"k": 42}';
1358 SELECT * FROM JSON_TABLE (@myjson, '$.k' COLUMNS (id FOR ORDINALITY)) AS alias;
1359 id
1360 1
1361 CREATE TABLE t1(
1362 txt TEXT, ty TINYTEXT, tm MEDIUMTEXT, tl LONGTEXT);
1363 INSERT INTO t1 values (
1364 '{"k": "text"}','{"k": "tinytext"}','{"k": "mediumtext"}','{"k": "longtext"}');
1365 SELECT alias.* FROM t1, JSON_TABLE (t1.txt,'$.k' COLUMNS (id VARCHAR(10) PATH '$')) AS alias;
1366 id
1367 text
1368 SELECT alias.* FROM t1, JSON_TABLE (t1.ty, '$.k' COLUMNS (id VARCHAR(10) PATH '$')) AS alias;
1369 id
1370 tinytext
1371 SELECT alias.* FROM t1, JSON_TABLE (t1.tm, '$.k' COLUMNS (id VARCHAR(10) PATH '$')) AS alias;
1372 id
1373 mediumtext
1374 SELECT alias.* FROM t1, JSON_TABLE (t1.tl, '$.k' COLUMNS (id VARCHAR(10) PATH '$')) AS alias;
1375 id
1376 longtext
1377 SELECT alias.* FROM t1, JSON_TABLE (t1.txt,'$.k' COLUMNS (id TEXT PATH '$')) AS alias;
1378 id
1379 text
1380 SELECT alias.* FROM t1, JSON_TABLE (t1.txt,'$.k' COLUMNS (id TINYTEXT PATH '$')) AS alias;
1381 id
1382 text
1383 SELECT alias.* FROM t1, JSON_TABLE (t1.txt,'$.k' COLUMNS (id MEDIUMTEXT PATH '$')) AS alias;
1384 id
1385 text
1386 SELECT alias.* FROM t1, JSON_TABLE (t1.txt,'$.k' COLUMNS (id LONGTEXT PATH '$')) AS alias;
1387 id
1388 text
1389 SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb BLOB PATH '$')) as alias;
1390 blb
1391 asd123
1392 EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb BLOB PATH '$')) as alias;
1393 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1394 1 SIMPLE alias NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
1395 Warnings:
1396 Note 1003 /* select#1 */ select `alias`.`blb` AS `blb` from json_table('"asd123"', '$' columns (`blb` blob path '$')) `alias`
1397 EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb TINYBLOB PATH '$')) as alias;
1398 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1399 1 SIMPLE alias NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
1400 Warnings:
1401 Note 1003 /* select#1 */ select `alias`.`blb` AS `blb` from json_table('"asd123"', '$' columns (`blb` tinyblob path '$')) `alias`
1402 EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb MEDIUMBLOB PATH '$')) as alias;
1403 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1404 1 SIMPLE alias NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
1405 Warnings:
1406 Note 1003 /* select#1 */ select `alias`.`blb` AS `blb` from json_table('"asd123"', '$' columns (`blb` mediumblob path '$')) `alias`
1407 EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb LONGBLOB PATH '$')) as alias;
1408 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1409 1 SIMPLE alias NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
1410 Warnings:
1411 Note 1003 /* select#1 */ select `alias`.`blb` AS `blb` from json_table('"asd123"', '$' columns (`blb` longblob path '$')) `alias`
1412 EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb TEXT PATH '$')) as alias;
1413 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1414 1 SIMPLE alias NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
1415 Warnings:
1416 Note 1003 /* select#1 */ select `alias`.`blb` AS `blb` from json_table('"asd123"', '$' columns (`blb` text character set utf8mb4 path '$')) `alias`
1417 EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb TINYTEXT PATH '$')) as alias;
1418 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1419 1 SIMPLE alias NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
1420 Warnings:
1421 Note 1003 /* select#1 */ select `alias`.`blb` AS `blb` from json_table('"asd123"', '$' columns (`blb` tinytext character set utf8mb4 path '$')) `alias`
1422 EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb MEDIUMTEXT PATH '$')) as alias;
1423 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1424 1 SIMPLE alias NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
1425 Warnings:
1426 Note 1003 /* select#1 */ select `alias`.`blb` AS `blb` from json_table('"asd123"', '$' columns (`blb` mediumtext character set utf8mb4 path '$')) `alias`
1427 EXPLAIN SELECT alias.* FROM JSON_TABLE ('"asd123"', '$' COLUMNS (blb LONGTEXT PATH '$')) as alias;
1428 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1429 1 SIMPLE alias NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
1430 Warnings:
1431 Note 1003 /* select#1 */ select `alias`.`blb` AS `blb` from json_table('"asd123"', '$' columns (`blb` longtext character set utf8mb4 path '$')) `alias`
1432 SELECT * FROM
1433 (SELECT CAST(blb AS JSON) jf FROM
1434 JSON_TABLE ('"asd123"', '$' COLUMNS (blb BLOB PATH '$')) AS jti) AS dt,
1435 JSON_TABLE (jf, '$' COLUMNS (blb BLOB PATH '$')) AS jto;
1436 ERROR HY000: Incorrect arguments to JSON_TABLE
1437 DROP TABLE t1;
1438 #
1439 # Bug#26500384: ASSERT FAILURE IN QUERY WITH WINDOW FUNCTION AND
1440 # JSON_TABLE
1441 #
1442 CREATE TABLE t (x INT);
1443 INSERT INTO t VALUES (1), (2), (3);
1444 SELECT MAX(t.x) OVER () m, jt.* FROM t,
1445 JSON_TABLE(JSON_ARRAY(m), '$[*]' COLUMNS (i INT PATH '$')) jt;
1446 ERROR 42S22: Unknown column 'm' in 'a table function argument'
1447 DROP TABLE t;
1448 #
1449 # Bug#26583283: ASSERTION `!THD->IS_ERROR()' FAILED IN SQL_RESOLVER.CC
1450 #
1451 EXPLAIN SELECT * FROM JSON_TABLE('null', '$' COLUMNS(AA DECIMAL PATH '$')) tt;
1452 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1453 1 SIMPLE tt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
1454 Warnings:
1455 Note 1003 /* select#1 */ select `tt`.`AA` AS `AA` from json_table('null', '$' columns (`AA` decimal(10,0) path '$')) `tt`
1456 CREATE VIEW v1 AS SELECT * FROM
1457 JSON_TABLE ( 'null', '$' COLUMNS (c1 DECIMAL PATH '$' ) ) AS jt;
1458 SELECT * FROM v1;
1459 c1
1460 NULL
1461 EXPLAIN SELECT * FROM v1;
1462 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1463 1 SIMPLE jt NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
1464 Warnings:
1465 Note 1003 /* select#1 */ select `jt`.`c1` AS `c1` from json_table('null', '$' columns (`c1` decimal(10,0) path '$')) `jt`
1466 DROP VIEW v1;
1467 #
1468 # Bug#25822408: ASSERTION `!COL->CHILD_JDS->PRODUCING_RECORDS' FAILED
1469 #
1470 PREPARE stmt FROM "SELECT * FROM
1471 JSON_TABLE('{\"a\":1}','$' COLUMNS (c1 CHAR(20) PATH '$.b' ERROR ON EMPTY)) jt";
1472 EXECUTE stmt;
1473 ERROR 22035: Missing value for JSON_TABLE column 'c1'
1474 EXECUTE stmt;
1475 ERROR 22035: Missing value for JSON_TABLE column 'c1'
1476 #
1477 # Bug#25594571: CRASH AT ITEM::CONST_ITEM|SQL/ITEM.H
1478 #
1479 CREATE TABLE t1 (i INT);
1480 INSERT INTO t1 VALUES(1),(2),(3),(4),(5),(6),(7);
1481 PREPARE stmt FROM "SELECT * FROM t1 AS alias1 LEFT JOIN t1 AS alias2
1482 LEFT JOIN JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (i FOR ORDINALITY )) AS
1483 alias3 ON alias2 . `i` = alias3 . `i` ON alias1 . `i` = alias2 . `i`";
1484 EXECUTE stmt;
1485 i i i
1486 1 1 1
1487 2 2 2
1488 3 3 3
1489 4 4 NULL
1490 5 5 NULL
1491 6 6 NULL
1492 7 7 NULL
1493 EXECUTE stmt;
1494 i i i
1495 1 1 1
1496 2 2 2
1497 3 3 3
1498 4 4 NULL
1499 5 5 NULL
1500 6 6 NULL
1501 7 7 NULL
1502 DROP TABLE t1;
1503 #
1504 # Bug#26648617: ASSERTION `IS_VIEW_OR_DERIVED() &&
1505 # USES_MATERIALIZATION()' FAILED.
1506 #
1507 CREATE TABLE t1 (
1508 col_varchar_key varchar(1) DEFAULT NULL
1509 ) ENGINE=InnoDB;
1510 INSERT INTO t1 VALUES(1),(4);
1511 SELECT * FROM t1 WHERE col_varchar_key NOT IN (
1512 SELECT col_varchar_key FROM JSON_TABLE(
1513 '[{"col_key": 1},{"col_key": 2}]', "$[*]" COLUMNS
1514 (col_varchar_key VARCHAR(10) PATH "$.col_key")) AS innr1);
1515 col_varchar_key
1516 4
1517 EXPLAIN SELECT * FROM t1 WHERE col_varchar_key NOT IN (
1518 SELECT col_varchar_key FROM JSON_TABLE(
1519 '[{"col_key": 1},{"col_key": 2}]', "$[*]" COLUMNS
1520 (col_varchar_key VARCHAR(10) PATH "$.col_key")) AS innr1);
1521 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1522 1 PRIMARY t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
1523 2 DEPENDENT SUBQUERY innr1 NULL index_subquery <auto_key0> <auto_key0> 43 func 4 100.00 Table function: json_table; Using temporary; Using where; Using index; Full scan on NULL key
1524 Warnings:
1525 Note 1003 /* select#1 */ select `test`.`t1`.`col_varchar_key` AS `col_varchar_key` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`col_varchar_key`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`col_varchar_key`) in innr1 on <auto_key0> checking NULL where <if>(outer_field_is_not_null, ((<cache>(`test`.`t1`.`col_varchar_key`) = `innr1`.`col_varchar_key`) or (`innr1`.`col_varchar_key` is null)), true) having <if>(outer_field_is_not_null, <is_not_null_test>(`innr1`.`col_varchar_key`), true))) is false)
1526 SELECT * FROM t1 WHERE col_varchar_key IN (
1527 SELECT col_varchar_key FROM JSON_TABLE(
1528 '[{"col_key": 1},{"col_key": 2}]', "$[*]" COLUMNS
1529 (col_varchar_key VARCHAR(10) PATH "$.col_key")) AS innr1);
1530 col_varchar_key
1531 1
1532 EXPLAIN SELECT * FROM t1 WHERE col_varchar_key IN (
1533 SELECT col_varchar_key FROM JSON_TABLE(
1534 '[{"col_key": 1},{"col_key": 2}]', "$[*]" COLUMNS
1535 (col_varchar_key VARCHAR(10) PATH "$.col_key")) AS innr1);
1536 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1537 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where
1538 1 SIMPLE innr1 NULL ref <auto_key0> <auto_key0> 43 test.t1.col_varchar_key 2 100.00 Table function: json_table; Using temporary; Using where; Using index; FirstMatch(t1)
1539 Warnings:
1540 Note 1003 /* select#1 */ select `test`.`t1`.`col_varchar_key` AS `col_varchar_key` from `test`.`t1` semi join (json_table('[{"col_key": 1},{"col_key": 2}]', '$[*]' columns (`col_varchar_key` varchar(10) character set utf8mb4 path '$.col_key')) `innr1`) where (`test`.`t1`.`col_varchar_key` = `innr1`.`col_varchar_key`)
1541 DROP TABLE t1;
1542 #
1543 # Bug#26711551: WL8867:CONDITIONAL JUMP IN JSON_TABLE_COLUMN::CLEANUP
1544 #
1545 CREATE TABLE t(x int, y int);
1546 INSERT INTO t(x) VALUES (1);
1547 UPDATE t t1, JSON_TABLE('[2]', '$[*]' COLUMNS (x INT PATH '$')) t2
1548 SET t1.y = t2.x;
1549 SELECT * FROM t;
1550 x y
1551 1 2
1552 DROP TABLE t;
1553 #
1554 # Bug#26679671: SIG 11 IN JSON_BINARY::PARSE_BINARY()
1555 #
1556 CREATE TABLE t1(id INT, f1 JSON);
1557 INSERT INTO t1 VALUES (1, '{\"1\": 1}'), (2, '{\"1\": 2}'), (3, '{\"1\": 3}'),
1558 (4, '{\"1\": 4}'), (5, '{\"1\": 5}'), (6, '{\"1\": 6}');
1559 ANALYZE TABLE t1;
1560 Table Op Msg_type Msg_text
1561 test.t1 analyze status OK
1562 ANALYZE TABLE t1;
1563 Table Op Msg_type Msg_text
1564 test.t1 analyze status OK
1565 SELECT * FROM t1 as jj1,
1566 (SELECT tt2.* FROM t1 as tt2,
1567 JSON_TABLE (f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl) dt;
1568 id f1 id f1
1569 1 {"1": 1} 1 {"1": 1}
1570 1 {"1": 1} 2 {"1": 2}
1571 1 {"1": 1} 3 {"1": 3}
1572 1 {"1": 1} 4 {"1": 4}
1573 1 {"1": 1} 5 {"1": 5}
1574 1 {"1": 1} 6 {"1": 6}
1575 2 {"1": 2} 1 {"1": 1}
1576 2 {"1": 2} 2 {"1": 2}
1577 2 {"1": 2} 3 {"1": 3}
1578 2 {"1": 2} 4 {"1": 4}
1579 2 {"1": 2} 5 {"1": 5}
1580 2 {"1": 2} 6 {"1": 6}
1581 3 {"1": 3} 1 {"1": 1}
1582 3 {"1": 3} 2 {"1": 2}
1583 3 {"1": 3} 3 {"1": 3}
1584 3 {"1": 3} 4 {"1": 4}
1585 3 {"1": 3} 5 {"1": 5}
1586 3 {"1": 3} 6 {"1": 6}
1587 4 {"1": 4} 1 {"1": 1}
1588 4 {"1": 4} 2 {"1": 2}
1589 4 {"1": 4} 3 {"1": 3}
1590 4 {"1": 4} 4 {"1": 4}
1591 4 {"1": 4} 5 {"1": 5}
1592 4 {"1": 4} 6 {"1": 6}
1593 5 {"1": 5} 1 {"1": 1}
1594 5 {"1": 5} 2 {"1": 2}
1595 5 {"1": 5} 3 {"1": 3}
1596 5 {"1": 5} 4 {"1": 4}
1597 5 {"1": 5} 5 {"1": 5}
1598 5 {"1": 5} 6 {"1": 6}
1599 6 {"1": 6} 1 {"1": 1}
1600 6 {"1": 6} 2 {"1": 2}
1601 6 {"1": 6} 3 {"1": 3}
1602 6 {"1": 6} 4 {"1": 4}
1603 6 {"1": 6} 5 {"1": 5}
1604 6 {"1": 6} 6 {"1": 6}
1605 EXPLAIN SELECT * FROM t1 as jj1,
1606 (SELECT tt2.* FROM t1 as tt2,
1607 JSON_TABLE (f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl) dt;
1608 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1609 1 SIMPLE jj1 NULL ALL NULL NULL NULL NULL 6 100.00 NULL
1610 1 SIMPLE tt2 NULL ALL NULL NULL NULL NULL 6 100.00 Using join buffer (hash join)
1611 1 SIMPLE tbl NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
1612 Warnings:
1613 Note 1003 /* select#1 */ select `test`.`jj1`.`id` AS `id`,`test`.`jj1`.`f1` AS `f1`,`test`.`tt2`.`id` AS `id`,`test`.`tt2`.`f1` AS `f1` from `test`.`t1` `jj1` join `test`.`t1` `tt2` join json_table(`test`.`tt2`.`f1`, '$' columns (`id` for ordinality)) `tbl`
1614 SELECT * FROM t1 as jj1,
1615 (SELECT tt2.* FROM t1 as tt2,
1616 JSON_TABLE (f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl STRAIGHT_JOIN
1617 t1 AS tt3) dt ORDER BY 1,3 LIMIT 10;
1618 id f1 id f1
1619 1 {"1": 1} 1 {"1": 1}
1620 1 {"1": 1} 1 {"1": 1}
1621 1 {"1": 1} 1 {"1": 1}
1622 1 {"1": 1} 1 {"1": 1}
1623 1 {"1": 1} 1 {"1": 1}
1624 1 {"1": 1} 1 {"1": 1}
1625 1 {"1": 1} 2 {"1": 2}
1626 1 {"1": 1} 2 {"1": 2}
1627 1 {"1": 1} 2 {"1": 2}
1628 1 {"1": 1} 2 {"1": 2}
1629 EXPLAIN SELECT * FROM t1 as jj1,
1630 (SELECT tt2.* FROM t1 as tt2,
1631 JSON_TABLE (f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl STRAIGHT_JOIN
1632 t1 AS tt3) dt ORDER BY 1,3 LIMIT 11;
1633 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1634 1 SIMPLE jj1 NULL ALL NULL NULL NULL NULL 6 100.00 Using temporary; Using filesort
1635 1 SIMPLE tt2 NULL ALL NULL NULL NULL NULL 6 100.00 Using join buffer (hash join)
1636 1 SIMPLE tbl NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary
1637 1 SIMPLE tt3 NULL ALL NULL NULL NULL NULL 6 100.00 Using join buffer (hash join)
1638 Warnings:
1639 Note 1003 /* select#1 */ select `test`.`jj1`.`id` AS `id`,`test`.`jj1`.`f1` AS `f1`,`test`.`tt2`.`id` AS `id`,`test`.`tt2`.`f1` AS `f1` from `test`.`t1` `jj1` join `test`.`t1` `tt2` join json_table(`test`.`tt2`.`f1`, '$' columns (`id` for ordinality)) `tbl` straight_join `test`.`t1` `tt3` order by `test`.`jj1`.`id`,`test`.`tt2`.`id` limit 11
1640 SELECT * FROM t1 WHERE id IN
1641 (SELECT id FROM t1 as tt2,
1642 JSON_TABLE (f1, "$" COLUMNS (jf FOR ORDINALITY)) AS tbl);
1643 id f1
1644 1 {"1": 1}
1645 2 {"1": 2}
1646 3 {"1": 3}
1647 4 {"1": 4}
1648 5 {"1": 5}
1649 6 {"1": 6}
1650 EXPLAIN SELECT * FROM t1 WHERE id IN
1651 (SELECT id FROM t1 as tt2,
1652 JSON_TABLE (f1, "$" COLUMNS (jf FOR ORDINALITY)) AS tbl);
1653 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1654 1 SIMPLE tt2 NULL ALL NULL NULL NULL NULL 6 100.00 Start temporary
1655 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 6 16.67 Using where; Using join buffer (hash join)
1656 1 SIMPLE tbl NULL ALL NULL NULL NULL NULL 2 100.00 Table function: json_table; Using temporary; End temporary
1657 Warnings:
1658 Note 1003 /* select#1 */ select `test`.`t1`.`id` AS `id`,`test`.`t1`.`f1` AS `f1` from `test`.`t1` semi join (`test`.`t1` `tt2` join json_table(`test`.`tt2`.`f1`, '$' columns (`jf` for ordinality)) `tbl`) where (`test`.`t1`.`id` = `test`.`tt2`.`id`)
1659 DROP TABLE t1;
1660 #
1661 # Bug#26760811: WL#8867: MEMORY LEAK REPORTED BY ASAN AND VALGRIND
1662 #
1663 CREATE TABLE t (j JSON);
1664 INSERT INTO t VALUES
1665 ('[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17]'),
1666 ('[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17]');
1667 SELECT COUNT(*) FROM t, JSON_TABLE(j, '$[*]' COLUMNS (i INT PATH '$')) AS jt;
1668 COUNT(*)
1669 34
1670 PREPARE ps FROM
1671 'SELECT COUNT(*) FROM t, JSON_TABLE(j, ''$[*]'' COLUMNS (i INT PATH ''$'')) AS jt';
1672 EXECUTE ps;
1673 COUNT(*)
1674 34
1675 EXECUTE ps;
1676 COUNT(*)
1677 34
1678 DROP PREPARE ps;
1679 DROP TABLE t;
1680 #
1681 # Bug #26781759: NON-UNIQUE ALIAS ERROR NOT BEING THROWN
1682 #
1683 SELECT * FROM JSON_TABLE(NULL, '$' COLUMNS(j1 FOR ORDINALITY)) AS jalias,
1684 JSON_TABLE(NULL, '$' COLUMNS(j1 FOR ORDINALITY)) AS jalias;
1685 ERROR 42000: Not unique table/alias: 'jalias'
1686 #
1687 # Bug #26761470: WL#8867: JOIN::MAKE_JOIN_PLAN():
1688 # ASSERTION `SELECT_LEX->IS_RECURSIVE()' FAILED
1689 #
1690 CREATE TABLE t1 (x INT);
1691 INSERT INTO t1 VALUES (1);
1692 CREATE TABLE t2 (j JSON);
1693 INSERT INTO t2 (j) VALUES ('[1,2,3]');
1694 SELECT * FROM t1 RIGHT JOIN
1695 (SELECT o FROM t2, JSON_TABLE(j, '$[*]' COLUMNS (o FOR ORDINALITY)) AS jt) AS t3
1696 ON (t3.o = t1.x);
1697 x o
1698 1 1
1699 NULL 2
1700 NULL 3
1701 DROP TABLE t1, t2;
1702 #
1703 # Bug#27152428 JSON_TABLE + PREPARED STATEMENT + VIEW HAS PROBLEM IN DURING RESOLUTION
1704 #
1705 CREATE TABLE t1 (a INT, b INT);
1706 CREATE VIEW v2 AS SELECT * FROM t1 LIMIT 2;
1707 SELECT b
1708 FROM (SELECT * FROM v2) vq1,
1709 JSON_TABLE(CONCAT(vq1.b,'[{\"a\":\"3\"}]'),
1710 '$[*]' COLUMNS (id FOR ORDINALITY,
1711 jpath VARCHAR(100) PATH '$.a',
1712 JEXST INT EXISTS PATH '$.b')
1713 ) AS dt;
1714 b
1715 DROP TABLE t1;
1716 DROP VIEW v2;
1717 #
1718 # Bug#27189940: CREATE VIEW FAILS ON JSON_TABLE() IN SCHEMA-LESS CONNECTIONS
1719 # BUG#27217897: JSON_TABLE() FAILS IF NO DATABASE SELECTED
1720 #
1721 CREATE VIEW test.v AS SELECT * FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$[0]')) AS jt;
1722 SELECT * FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$[0]')) AS jt;
1723 num
1724 1
1725 2
1726 3
1727 use test;
1728 SHOW CREATE VIEW test.v;
1729 View Create View character_set_client collation_connection
1730 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 path '$[0]')) `jt` utf8mb4 utf8mb4_0900_ai_ci
1731 SELECT * FROM test.v;
1732 num
1733 1
1734 2
1735 3
1736 DROP VIEW test.v;
1737 #
1738 # Bug#27729112 JSON_TABLE SHOULD DECODE BASE64-ENCODED STRINGS
1739 #
1740 SELECT v
1741 FROM JSON_TABLE(JSON_OBJECT('foo', _binary'bar'), '$'
1742 COLUMNS(v VARCHAR(255) PATH '$.foo')) tbl;
1743 v
1744 bar
1745 CREATE TABLE t1 (col1 VARCHAR(255) CHARACTER SET ucs2,
1746 col2 VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs);
1747 Warnings:
1748 Warning 1287 'ucs2' is deprecated and will be removed in a future release. Please use utf8mb4 instead
1749 INSERT INTO t1 VALUES ("æ", "ハ"), ("å", "ø"), ("ø", "パ"), ("@", "バ");
1750 ANALYZE TABLE t1 UPDATE HISTOGRAM ON col1, col2;
1751 Table Op Msg_type Msg_text
1752 test.t1 histogram status Histogram statistics created for column 'col1'.
1753 test.t1 histogram status Histogram statistics created for column 'col2'.
1754 SELECT v value, c cumulfreq
1755 FROM INFORMATION_SCHEMA.COLUMN_STATISTICS,
1756 JSON_TABLE(histogram->'$.buckets', '$[*]'
1757 COLUMNS(v VARCHAR(255) CHARACTER SET ucs2 PATH '$[0]',
1758 c double PATH '$[1]')) hist
1759 WHERE column_name = "col1";
1760 value cumulfreq
1761 @ 0.25
1762 å 0.5
1763 æ 0.75
1764 ø 1
1765 Warnings:
1766 Warning 1287 'ucs2' is deprecated and will be removed in a future release. Please use utf8mb4 instead
1767 SELECT v value, c cumulfreq
1768 FROM INFORMATION_SCHEMA.COLUMN_STATISTICS,
1769 JSON_TABLE(histogram->'$.buckets', '$[*]'
1770 COLUMNS(v VARCHAR(255) CHARACTER SET utf8mb4 PATH '$[0]',
1771 c double PATH '$[1]')) hist
1772 WHERE column_name = "col2";
1773 value cumulfreq
1774 ø 0.25
1775 ハ 0.5
1776 バ 0.75
1777 パ 1
1778 DROP TABLE t1;
1779 #
1780 # Bug#28254268: JSON_TABLE() FUNCTION REJECTS SELECT PERMISSIONS
1781 #
1782 CREATE DATABASE db2;
1783 USE db2;
1784 CREATE TABLE t1 (c JSON);
1785 INSERT INTO t1 VALUES('[1,2,3]');
1786 CREATE USER user1@localhost;
1787 GRANT SELECT ON db2.t1 TO user1@localhost;
1788 USE db2;
1789 SELECT t1.c FROM t1;
1790 c
1791 [1, 2, 3]
1792 SELECT jt.* FROM t1, JSON_TABLE(t1.c, '$[*]' COLUMNS (num INT PATH '$[0]'))
1793 AS jt;
1794 num
1795 1
1796 2
1797 3
1798 DROP USER user1@localhost;
1799 DROP DATABASE db2;
1800 #
1801 # Bug#27856835 JSON_TABLE RETURNS WRONG DATATYPE WHEN INT-VALUE IS GRATER
1802 # THAN (2^31-1)
1803 #
1804 SELECT id FROM JSON_TABLE('[{"id":"9223372036854775807"}]', '$[*]' COLUMNS
1805 (id BIGINT PATH '$.id')) AS json;
1806 id
1807 9223372036854775807
1808 # As we currently have no way of telling if a JSON string value is
1809 # signed or unsigned, this value will overflow.
1810 SELECT id FROM JSON_TABLE('[{"id":"9223372036854775808"}]', '$[*]' COLUMNS
1811 (id BIGINT PATH '$.id')) AS json;
1812 id
1813 -9223372036854775808
1814 # Here the JSON value is a NUMERIC value, and we thus know if the value
1815 # is signed or unsigned.
1816 SELECT id FROM JSON_TABLE('[{"id":9223372036854775808}]', '$[*]' COLUMNS
1817 (id BIGINT PATH '$.id')) AS json;
1818 id
1819 NULL
1820 # If we tell the JSON table column to be unsigned, we get to store the
1821 # full value correctly.
1822 SELECT id FROM JSON_TABLE('[{"id":"9223372036854775808"}]', '$[*]' COLUMNS
1823 (id BIGINT UNSIGNED PATH '$.id')) AS json;
1824 id
1825 9223372036854775808
1826 SELECT id FROM JSON_TABLE('[{"id":"2147483648"}]', '$[*]' COLUMNS
1827 (id INT UNSIGNED PATH '$.id')) AS json;
1828 id
1829 2147483648
1830 # Check that we preserve the signedness of the columns.
1831 USE test;
1832 CREATE TABLE t1 AS SELECT id, value FROM
1833 JSON_TABLE('[{"id":9223372036854775808, "value":9223372036854775807}]',
1834 '$[*]' COLUMNS (id BIGINT UNSIGNED PATH '$.id',
1835 value BIGINT PATH '$.value'))
1836 AS json;
1837 SHOW CREATE TABLE t1;
1838 Table Create Table
1839 t1 CREATE TABLE `t1` (
1840 `id` bigint unsigned DEFAULT NULL,
1841 `value` bigint DEFAULT NULL
1842 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1843 DROP TABLE t1;
1844 #
1845 # Bug#28255453: VIEW USING JSON_TABLE FAILS IF NO SCHEMA IS SELECTED
1846 #
1847 CREATE VIEW test.v AS SELECT * FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$[0]')) AS jt;
1848 SELECT * FROM test.v;
1849 num
1850 1
1851 2
1852 3
1853 DROP VIEW test.v;
1854 # Check that a user without access to the schema 'foo' cannot query
1855 # a JSON_TABLE view in that schema.
1856 CREATE SCHEMA foo;
1857 CREATE VIEW foo.v AS SELECT * FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$[0]')) AS jt;
1858 CREATE USER foo@localhost;
1859 SELECT * FROM foo.v;
1860 ERROR 42000: SELECT command denied to user 'foo'@'localhost' for table 'v'
1861 DROP USER foo@localhost;
1862 DROP SCHEMA foo;
1863 # Check that a user with access to the schema 'foo' can do a SELECT with
1864 # a JSON_TABLE function.
1865 CREATE SCHEMA foo;
1866 CREATE USER foo@localhost;
1867 GRANT EXECUTE ON foo.* TO foo@localhost;
1868 SELECT 1 FROM JSON_TABLE('[1,2,3]', '$[*]' COLUMNS (num INT PATH '$.a')) AS jt;
1869 1
1870 1
1871 1
1872 1
1873 DROP USER foo@localhost;
1874 DROP SCHEMA foo;
1875 #
1876 # Bug#27923406 ERROR 1142 (42000) WHEN USING JSON_TABLE
1877 #
1878 CREATE SCHEMA my_schema;
1879 CREATE USER foo@localhost;
1880 GRANT EXECUTE ON my_schema.* TO foo@localhost;
1881 SELECT
1882 *
1883 FROM
1884 JSON_TABLE(
1885 '[{"x":2,"y":"8"},{"x":"3","y":"7"},{"x":"4","y":6}]',
1886 "$[*]" COLUMNS(
1887 xval VARCHAR(100) PATH "$.x",
1888 yval VARCHAR(100) PATH "$.y"
1889 )
1890 ) AS jt1;
1891 xval yval
1892 2 8
1893 3 7
1894 4 6
1895 DROP USER foo@localhost;
1896 DROP SCHEMA my_schema;
1897 #
1898 # Bug#28538315: JSON_TABLE() COLUMN TYPES DON'T SUPPORT COLLATE CLAUSE
1899 #
1900 CREATE TABLE t1 SELECT *
1901 FROM JSON_TABLE('"test"', '$' COLUMNS(x VARCHAR(10)
1902 CHARSET utf8mb4
1903 PATH '$')) AS jt1;
1904 SHOW CREATE TABLE t1;
1905 Table Create Table
1906 t1 CREATE TABLE `t1` (
1907 `x` varchar(10) DEFAULT NULL
1908 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1909 CREATE TABLE t2 SELECT *
1910 FROM JSON_TABLE('"test"', '$' COLUMNS(x VARCHAR(10)
1911 CHARSET utf8mb4 COLLATE utf8mb4_bin
1912 PATH '$')) AS jt1;
1913 SHOW CREATE TABLE t2;
1914 Table Create Table
1915 t2 CREATE TABLE `t2` (
1916 `x` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
1917 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1918 CREATE TABLE t3 AS SELECT *
1919 FROM JSON_TABLE('"a"', '$' COLUMNS (a VARCHAR(10)
1920 COLLATE ascii_bin
1921 PATH '$')) jt;
1922 SHOW CREATE TABLE t3;
1923 Table Create Table
1924 t3 CREATE TABLE `t3` (
1925 `a` varchar(10) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL
1926 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1927 DROP TABLE t1, t2, t3;
1928 #
1929 # Bug#28643862 JSON_TABLE'S "COLUMNS" CLAUSE USES
1930 # GLOBAL.CHARACTER_SET_RESULTS DEFAULT CHARSET
1931 #
1932 SET @@SESSION.character_set_connection = ascii;
1933 CREATE TABLE t1 SELECT a.col
1934 FROM JSON_TABLE('"test"', '$' COLUMNS(col VARCHAR(10) PATH '$')) AS a;
1935 SHOW CREATE TABLE t1;
1936 Table Create Table
1937 t1 CREATE TABLE `t1` (
1938 `col` varchar(10) CHARACTER SET ascii DEFAULT NULL
1939 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1940 SET @@SESSION.collation_connection = latin1_bin;
1941 CREATE TABLE t2 SELECT a.col
1942 FROM JSON_TABLE('"test"', '$' COLUMNS(col VARCHAR(10) PATH '$')) AS a;
1943 SHOW CREATE TABLE t2;
1944 Table Create Table
1945 t2 CREATE TABLE `t2` (
1946 `col` varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL
1947 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1948 DROP TABLE t1, t2;
1949 SET @@SESSION.character_set_connection = DEFAULT;
1950 #
1951 # Bug#28851656: JSON_TABLE RETURN "UNKNOWN DATABASE ''" FROM A FUNCTION
1952 #
1953 CREATE FUNCTION FN_COUNT_ROWS(X JSON)
1954 RETURNS INT DETERMINISTIC
1955 RETURN (
1956 SELECT COUNT(*) FROM JSON_TABLE( X, '$[*]' COLUMNS( I INT PATH '$')) der
1957 );
1958 SELECT FN_COUNT_ROWS('[1, 2]') CNT;
1959 CNT
1960 2
1961 SELECT FN_COUNT_ROWS('[1, 2, 3]') CNT;
1962 CNT
1963 3
1964 SELECT FN_COUNT_ROWS('[1, 2, 3, 4]') CNT;
1965 CNT
1966 4
1967 DROP FUNCTION FN_COUNT_ROWS;
1968 #
1969 # Bug#30310265: VIEWS LOSE THE CHARACTER SET OF JSON_TABLE'S
1970 # PATH ARGUMENTS
1971 #
1972 CREATE VIEW v1 AS
1973 SELECT * FROM JSON_TABLE('{"å":{"å":1}}', '$.å' COLUMNS (x INT PATH '$.å')) t;
1974 SELECT * FROM v1;
1975 x
1976 1
1977 SET NAMES latin1;
1978 SELECT * FROM v1;
1979 x
1980 1
1981 SET NAMES DEFAULT;
1982 DROP VIEW v1;
1983 CREATE VIEW v2 AS
1984 SELECT * FROM JSON_TABLE('{}', '$' COLUMNS (
1985 x VARCHAR(10) PATH '$.a' DEFAULT '"isn''t here"' ON EMPTY)
1986 ) t;
1987 SHOW CREATE VIEW v2;
1988 View Create View character_set_client collation_connection
1989 v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t`.`x` AS `x` from json_table('{}', '$' columns (`x` varchar(10) character set utf8mb4 path '$.a' default '"isn\'t here"' on empty)) `t` utf8mb4 utf8mb4_0900_ai_ci
1990 SELECT * FROM v2;
1991 x
1992 isn't here
1993 DROP VIEW v2;
1994 SELECT * FROM
1995 JSON_TABLE(JSON_OBJECT(),
1996 _utf8mb4'$' COLUMNS (NESTED PATH _utf8mb4'$.x' COLUMNS
1997 (y INT PATH _utf8mb4'$.y'
1998 DEFAULT _utf8mb4'1' ON EMPTY
1999 DEFAULT _utf8mb4'2' ON ERROR))) jt;
2000 y
2001 NULL
2002 #
2003 # Bug#30382156: STORED PROCEDURE, JSON_TABLE AND "CONST" JOIN TYPE
2004 #
2005 CREATE TABLE t (id INT PRIMARY KEY, j JSON);
2006 INSERT INTO t VALUES (1, '{"x":1}');
2007 CREATE PROCEDURE p()
2008 SELECT * FROM t, JSON_TABLE(j, '$' COLUMNS (x INT PATH '$.x')) jt
2009 WHERE id = 1;
2010 CALL p();
2011 id j x
2012 1 {"x": 1} 1
2013 CALL p();
2014 id j x
2015 1 {"x": 1} 1
2016 CALL p();
2017 id j x
2018 1 {"x": 1} 1
2019 DROP PROCEDURE p;
2020 DROP TABLE t;
2021 #
2022 # WL#13512: Deprecate ON ERROR before ON EMPTY in JSON_TABLE syntax
2023 #
2024 SELECT * FROM
2025 JSON_TABLE('{}', '$' COLUMNS (x VARCHAR(10) PATH '$.x'
2026 DEFAULT '"a"' ON ERROR DEFAULT '"b"' ON EMPTY)) jt;
2027 x
2028 b
2029 Warnings:
2030 Warning 1287 Specifying an ON EMPTY clause after the ON ERROR clause in a JSON_TABLE column definition is deprecated syntax and will be removed in a future release. Specify ON EMPTY before ON ERROR instead.
2031 SELECT * FROM
2032 JSON_TABLE('{}', '$' COLUMNS (x VARCHAR(10) PATH '$.x'
2033 NULL ON ERROR NULL ON EMPTY)) jt;
2034 x
2035 NULL
2036 Warnings:
2037 Warning 1287 Specifying an ON EMPTY clause after the ON ERROR clause in a JSON_TABLE column definition is deprecated syntax and will be removed in a future release. Specify ON EMPTY before ON ERROR instead.
2038 SELECT * FROM
2039 JSON_TABLE('{"x":"c"}', '$' COLUMNS (x VARCHAR(10) PATH '$.x'
2040 ERROR ON ERROR ERROR ON EMPTY)) jt;
2041 x
2042 c
2043 Warnings:
2044 Warning 1287 Specifying an ON EMPTY clause after the ON ERROR clause in a JSON_TABLE column definition is deprecated syntax and will be removed in a future release. Specify ON EMPTY before ON ERROR instead.
2045 #
2046 # Bug#30628330: INSERT WITH JSON_TABLE FAILS DUE TO WARNING INVOKED
2047 # WITH ON ERROR CLAUSE
2048 #
2049 CREATE TABLE t(id INT, a TINYINT, b VARCHAR(3), c DATE, d DECIMAL);
2050 INSERT INTO t SELECT * FROM
2051 JSON_TABLE(JSON_ARRAY(JSON_OBJECT('a', 1, 'b', 'abc'),
2052 JSON_OBJECT('a', 2, 'b', 'abcd'),
2053 JSON_OBJECT('a', 1000, 'b', 'xyz'),
2054 JSON_OBJECT('c', TIME'12:00:00', 'd', 1e308)),
2055 '$[*]' COLUMNS (id FOR ORDINALITY,
2056 a TINYINT PATH '$.a' DEFAULT '111' ON ERROR,
2057 b VARCHAR(3) PATH '$.b' DEFAULT '"ERR"' ON ERROR,
2058 c DATE PATH '$.c' DEFAULT '"2001-01-01"' ON ERROR,
2059 d DECIMAL PATH '$.c' DEFAULT '999' ON ERROR)
2060 ) AS jt;
2061 SELECT * FROM t ORDER BY id;
2062 id a b c d
2063 1 1 abc NULL NULL
2064 2 2 ERR NULL NULL
2065 3 111 xyz NULL NULL
2066 4 NULL NULL 2001-01-01 999
2067 DROP TABLE t;
2068 #
2069 # Bug#30263373: INCORRECT OUTPUT FROM TABLE_FUNCTION_JSON::PRINT()
2070 #
2071 CREATE VIEW v AS SELECT * FROM
2072 JSON_TABLE('[123]', '$[*]' COLUMNS (`name with space 1` INT PATH '$',
2073 `name with space 2` FOR ORDINALITY)) jt;
2074 SELECT * FROM v;
2075 name with space 1 name with space 2
2076 123 1
2077 DROP VIEW v;
2078 CREATE VIEW v AS SELECT HEX(x), HEX(y) FROM
2079 JSON_TABLE('["abc"]', '$[*]' COLUMNS (x BINARY(10) PATH '$',
2080 y VARBINARY(10) PATH '$')) jt;
2081 SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
2082 WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';
2083 VIEW_DEFINITION
2084 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`
2085 SELECT * FROM v;
2086 HEX(x) HEX(y)
2087 61626300000000000000 616263
2088 DROP VIEW v;
2089 CREATE VIEW v AS SELECT * FROM
2090 JSON_TABLE('[123]', '$[*]' COLUMNS(ti TINYINT PATH '$',
2091 si SMALLINT PATH '$',
2092 mi MEDIUMINT PATH '$',
2093 i INT PATH '$',
2094 bi BIGINT PATH '$',
2095 tiu TINYINT UNSIGNED PATH '$',
2096 siu SMALLINT UNSIGNED PATH '$',
2097 miu MEDIUMINT UNSIGNED PATH '$',
2098 iu INT UNSIGNED PATH '$',
2099 biu BIGINT UNSIGNED PATH '$')) jt;
2100 SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
2101 WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';
2102 VIEW_DEFINITION
2103 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 path '$', `si` smallint path '$', `mi` mediumint path '$', `i` int path '$', `bi` bigint path '$', `tiu` tinyint unsigned path '$', `siu` smallint unsigned path '$', `miu` mediumint unsigned path '$', `iu` int unsigned path '$', `biu` bigint unsigned path '$')) `jt`
2104 SELECT * FROM v;
2105 ti si mi i bi tiu siu miu iu biu
2106 123 123 123 123 123 123 123 123 123 123
2107 DROP VIEW v;
2108 CREATE VIEW v AS SELECT * FROM
2109 JSON_TABLE('[]', '$[*]' COLUMNS (g GEOMETRY PATH '$',
2110 ls LINESTRING PATH '$')) AS jt;
2111 SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
2112 WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';
2113 VIEW_DEFINITION
2114 select `jt`.`g` AS `g`,`jt`.`ls` AS `ls` from json_table('[]', '$[*]' columns (`g` geometry path '$', `ls` linestring path '$')) `jt`
2115 SELECT * FROM v;
2116 g ls
2117 DROP VIEW v;
2118 CREATE VIEW v AS SELECT * FROM
2119 JSON_TABLE('["abc"]', '$[*]' COLUMNS
2120 (x VARCHAR(10) CHARSET latin1 PATH '$',
2121 y VARCHAR(10) COLLATE utf8mb4_de_pb_0900_ai_ci PATH '$',
2122 z LONGTEXT COLLATE utf8mb4_bin PATH '$')) jt;
2123 SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS
2124 WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v';
2125 VIEW_DEFINITION
2126 select `jt`.`x` AS `x`,`jt`.`y` AS `y`,`jt`.`z` AS `z` from json_table('["abc"]', '$[*]' columns (`x` varchar(10) character set latin1 path '$', `y` varchar(10) character set utf8mb4 collate utf8mb4_de_pb_0900_ai_ci path '$', `z` longtext character set utf8mb4 collate utf8mb4_bin path '$')) `jt`
2127 SELECT x, y, z, COLLATION(x) c_x, COLLATION(y) c_y, COLLATION(z) c_z FROM v;
2128 x y z c_x c_y c_z
2129 abc abc abc latin1_swedish_ci utf8mb4_de_pb_0900_ai_ci utf8mb4_bin
2130 DROP VIEW v;
2131 #
2132 # Bug#31345503 JSON_TABLE SHOULD RETURN SQL NULL FOR JSON NULL
2133 #
2134 SELECT *
2135 FROM
2136 JSON_TABLE(
2137 '[
2138 {"c1": null,
2139 "c2": [{"c": "c_val", "l": [1,2]}],
2140 "c3": null},
2141 {"c1": true,
2142 "c2": [{"c": "c_val","l": [11]}, {"c": "c_val", "l": [false]}],
2143 "c3": true},
2144 {"c1": false,
2145 "c2": [{"c": null,"l": [true]}, {"c": "c_val", "l": [null]}],
2146 "c3": false}
2147 ]',
2148 '$[*]' COLUMNS(
2149 top_ord FOR ORDINALITY,
2150 c1path VARCHAR(10) PATH '$.c1' ERROR ON ERROR,
2151 NESTED PATH '$.c2[*]' COLUMNS (
2152 c2path VARCHAR(10) PATH '$.c' ERROR ON ERROR,
2153 ord FOR ORDINALITY,
2154 NESTED PATH '$.l[*]' COLUMNS (lpath_c VARCHAR(10) PATH '$' ERROR ON ERROR,
2155 lpath_i INT PATH '$' ERROR ON ERROR)
2156 ),
2157 c3path INT PATH '$.c3' ERROR ON ERROR
2158 )
2159 ) as jt;
2160 top_ord c1path c2path ord lpath_c lpath_i c3path
2161 1 NULL c_val 1 1 1 NULL
2162 1 NULL c_val 1 2 2 NULL
2163 2 true c_val 1 11 11 1
2164 2 true c_val 2 false 0 1
2165 3 false NULL 1 true 1 0
2166 3 false c_val 2 NULL NULL 0
2167 #
2168 # Bug #31327187 UBSAN: JSON_TABLE: NULL POINTER PASSED AS ARGUMENT 2,
2169 # WHICH IS DECLARED TO NEVER BE NULL
2170 #
2171 SELECT HEX(a) FROM JSON_TABLE(
2172 '[{"E":{"e":true,"~":1,"S3":"sTa"},"r":3,"":6.7},"",6.5]',
2173 '$'
2174 COLUMNS(a BINARY(5) PATH '$[1]' NULL ON EMPTY)
2175 ) e;
2176 HEX(a)
2177 0000000000
2178 #
2179 # Bug#32591074: JSON_TABLE'S CHECK FOR DUPLICATE COLUMN NAMES IS
2180 # CASE-SENSITIVE
2181 #
2182 SELECT * FROM
2183 JSON_TABLE('{}', '$' COLUMNS (o FOR ORDINALITY, O FOR ORDINALITY)) jt;
2184 ERROR 42S21: Duplicate column name 'o'
2185 #
2186 # Bug#33351497: JSON_TABLE function report duplicate column name
2187 #
2188 SELECT * FROM JSON_TABLE('[1, 2]', '$' COLUMNS (
2189 测试列aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa INT PATH '$[0]',
2190 测试列aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabb INT PATH '$[1]')
2191 ) AS jt;
2192 测试列aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 测试列aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaabb
2193 1 2
2194 #
2195 # Bug#33414235: Item_row::illegal_method_call const: Assertion `0' failed.
2196 #
2197 SELECT 1 FROM JSON_TABLE(ROW(1, 2), '$' COLUMNS (o FOR ORDINALITY)) AS jt;
2198 ERROR 21000: Operand should contain 1 column(s)