Debian Codegraph @

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

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