Debian Codegraph @

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

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