Debian Codegraph @

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

raw
1 ALTER DATABASE test CHARACTER SET latin1 COLLATE latin1_swedish_ci;
2 select * from json_table('[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]', '$[*]' COLUMNS( a INT PATH '$.a')) as tt;
3 a
4 1
5 2
6 select * from JSON_TABLE( '[ {"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a', NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
7 a b
8 1 11
9 1 111
10 2 22
11 2 222
12 3 NULL
13 SELECT * FROM JSON_TABLE( '[ {"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a', NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'), NESTED PATH '$.b[*]' COLUMNS (c INT PATH '$') ) ) jt;
14 a b c
15 1 11 NULL
16 1 111 NULL
17 1 NULL 11
18 1 NULL 111
19 2 22 NULL
20 2 222 NULL
21 2 NULL 22
22 2 NULL 222
23 3 NULL NULL
24 create table t1 (id varchar(5), json varchar(1024));
25 insert into t1 values ('j1', '[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]');
26 insert into t1 values ('j2', '[{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}]');
27 select id, json, a from t1, json_table(t1.json, '$[*]' COLUMNS(js_id FOR ORDINALITY, a INT PATH '$.a')) as tt;
28 id json a
29 j1 [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}] 1
30 j1 [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}] 2
31 j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 3
32 j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 4
33 j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 5
34 select * from t1, JSON_TABLE(t1.json, '$[*]' COLUMNS(js_id FOR ORDINALITY, a INT PATH '$.a', NESTED PATH '$.b[*]' COLUMNS (l_js_id FOR ORDINALITY, b INT PATH '$'))) as jt;
35 id json js_id a l_js_id b
36 j1 [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}] 1 1 1 11
37 j1 [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}] 1 1 2 111
38 j1 [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}] 2 2 1 22
39 j1 [{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}] 2 2 2 222
40 j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 1 3 1 11
41 j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 1 3 2 111
42 j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 2 4 1 22
43 j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 2 4 2 222
44 j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 3 5 1 22
45 j2 [{"a": 3, "b": [11,111]}, {"a": 4, "b": [22,222]}, {"a": 5, "b": [22,222]}] 3 5 2 222
46 select * from t1, JSON_TABLE(t1.no_field, '$[*]' COLUMNS(js_id FOR ORDINALITY, a INT PATH '$.a', NESTED PATH '$.b[*]' COLUMNS (l_js_id FOR ORDINALITY, b INT PATH '$'))) as jt;
47 ERROR 42S22: Unknown column 't1.no_field' in 'JSON_TABLE'
48 select * from t1, JSON_TABLE(t1.no_field, '$[*]' COLUMNS(js_id FOR ORDINALITY, a INT PATH '$.a', NESTED PATH '$.b[*]' COLUMNS (l_js_id FOR ORDINALITY, a INT PATH '$'))) as jt;
49 ERROR 42S21: Duplicate column name 'a'
50 DROP TABLE t1;
51 create table t1 (item_name varchar(32), item_props varchar(1024));
52 insert into t1 values ('Laptop', '{"color": "black", "price": 1000}');
53 insert into t1 values ('Jeans', '{"color": "blue", "price": 50}');
54 select * from t1 left join json_table(t1.item_props,'$' columns( color varchar(100) path '$.color')) as T on 1;
55 item_name item_props color
56 Laptop {"color": "black", "price": 1000} black
57 Jeans {"color": "blue", "price": 50} blue
58 select * from t1 right join json_table(t1.item_props,'$' columns( color varchar(100) path '$.color')) as T on 1;
59 ERROR 42S22: Unknown column 't1.item_props' in 'JSON_TABLE'
60 DROP TABLE t1;
61 select * from JSON_TABLE( '[ {"xa": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default 101 on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
62 a b
63 101 11
64 101 111
65 2 22
66 2 222
67 3 NULL
68 select * from JSON_TABLE( '[ {"xa": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default 202 on error, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
69 a b
70 NULL 11
71 NULL 111
72 2 22
73 2 222
74 3 NULL
75 select * from JSON_TABLE( '[ {"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
76 a b
77 NULL 11
78 NULL 111
79 2 22
80 2 222
81 3 NULL
82 select * from JSON_TABLE( '[ {"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '202' on error default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
83 a b
84 202 11
85 202 111
86 2 22
87 2 222
88 3 NULL
89 select * from JSON_TABLE( '[{"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3} xx YY]', '$[*]' COLUMNS( a INT PATH '$.a' default '202' on error default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
90 ERROR HY000: Syntax error in JSON text in argument 1 to function 'JSON_TABLE' at position 65
91 select * from JSON_TABLE( '[{"a": [1, 2], "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' error on error default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt;
92 ERROR HY000: Can't store an array or an object in the scalar column 'a' of JSON_TABLE 'jt'.
93 select * from json_table('{"a":0}',"$" columns(a decimal(1,1) path '$.a')) foo;
94 a
95 0.0
96 connect con1,localhost,root,,;
97 select a from json_table('{"a":0}',"$" columns(a for ordinality)) foo;
98 a
99 1
100 connection default;
101 disconnect con1;
102 create table t1 (
103 color varchar(32),
104 price int
105 );
106 insert into t1 values ("red", 100), ("blue", 50);
107 insert into t1 select * from t1;
108 insert into t1 select * from t1;
109 set @save_optimizer_switch=@@optimizer_switch;
110 set optimizer_switch='firstmatch=off';
111 select * from
112 json_table('[{"color": "blue", "price": 50},
113 {"color": "red", "price": 100}]',
114 '$[*]' columns( color varchar(100) path '$.color',
115 price text path '$.price'
116 )
117 ) as T
118 where
119 T.color in (select color from t1 where t1.price=T.price);
120 color price
121 blue 50
122 red 100
123 set @@optimizer_switch=@save_optimizer_switch;
124 drop table t1;
125 select * from
126 json_table(' [ {"color": "blue", "sizes": [1,2,3,4], "prices" : [10,20]},
127 {"color": "red", "sizes": [10,11,12,13,14], "prices" : [100,200,300]} ]',
128 '$[*]' columns(
129 color varchar(4) path '$.color',
130 seq0 for ordinality,
131 nested path '$.sizes[*]'
132 columns (seq1 for ordinality,
133 size int path '$'),
134 nested path '$.prices[*]'
135 columns (seq2 for ordinality,
136 price int path '$')
137 )
138 ) as T;
139 color seq0 seq1 size seq2 price
140 blue 1 1 1 NULL NULL
141 blue 1 2 2 NULL NULL
142 blue 1 3 3 NULL NULL
143 blue 1 4 4 NULL NULL
144 blue 1 NULL NULL 1 10
145 blue 1 NULL NULL 2 20
146 red 2 1 10 NULL NULL
147 red 2 2 11 NULL NULL
148 red 2 3 12 NULL NULL
149 red 2 4 13 NULL NULL
150 red 2 5 14 NULL NULL
151 red 2 NULL NULL 1 100
152 red 2 NULL NULL 2 200
153 red 2 NULL NULL 3 300
154 select * from json_table('[{"color": "blue", "price": 50},
155 {"color": "red", "price": 100},
156 {"color": "rojo", "price": 10.0},
157 {"color": "blanco", "price": 11.0}]',
158 '$[*]' columns( color varchar(100) path '$.color',
159 price text path '$.price', seq for ordinality)) as T order by color desc;
160 color price seq
161 rojo 10.0 3
162 red 100 2
163 blue 50 1
164 blanco 11.0 4
165 create view v as select * from json_table('{"as":"b", "x":123}',"$" columns(a varchar(8) path '$.a' default '-' on empty, x int path '$.x')) x;
166 select * from v;
167 a x
168 - 123
169 show create table v;
170 View Create View character_set_client collation_connection
171 v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `x`.`a` AS `a`,`x`.`x` AS `x` from JSON_TABLE('{"as":"b", "x":123}', '$' COLUMNS (`a` varchar(8) PATH '$.a' DEFAULT '-' ON EMPTY, `x` int(11) PATH '$.x')) `x` latin1 latin1_swedish_ci
172 drop view v;
173 select * from json_table('{"as":"b", "x":123}',
174 "$" columns(a varchar(8) path '$.a' default '-' on empty null on error null on empty, x int path '$.x')) x;
175 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'null on empty, x int path '$.x')) x' at line 2
176 select * from json_table('{"a":"foo","b":"bar"}', '$'
177 columns (v varchar(20) path '$.*')) as jt;
178 v
179 NULL
180 select * from json_table('{"a":"foo","b":"bar"}', '$'
181 columns (v varchar(20) path '$.*' default '-' on error)) as jt;
182 v
183 -
184 select * from json_table('{"b":"bar"}', '$'
185 columns (v varchar(20) path '$.*' default '-' on error)) as jt;
186 v
187 bar
188 create table t1 (a varchar(100));
189 insert into t1 values ('1');
190 select * from t1 as T, json_table(T.a, '$[*]' columns(color varchar(100) path '$.nonexistent', seq for ordinality)) as T;
191 ERROR 42000: Not unique table/alias: 'T'
192 drop table t1;
193 prepare s from 'select * from
194 json_table(?,
195 \'$[*]\' columns( color varchar(100) path \'$.color\',
196 price text path \'$.price\',
197 seq for ordinality)) as T
198 order by color desc; ';
199 execute s using '[{"color": "red", "price":1}, {"color":"brown", "price":2}]';
200 color price seq
201 red 1 1
202 brown 2 2
203 deallocate prepare s;
204 create view v2 as select * from json_table('[{"co\\\\lor": "blue", "price": 50}]', '$[*]' columns( color varchar(100) path '$.co\\\\lor') ) as T;
205 select * from v2;
206 color
207 blue
208 drop view v2;
209 explain format=json select * from
210 json_table('[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]', '$[*]' COLUMNS( a INT PATH '$.a')) as tt;
211 EXPLAIN
212 {
213 "query_block": {
214 "select_id": 1,
215 "cost": "COST_REPLACED",
216 "nested_loop": [
217 {
218 "table": {
219 "table_name": "tt",
220 "access_type": "ALL",
221 "loops": 1,
222 "rows": 40,
223 "cost": "COST_REPLACED",
224 "filtered": 100,
225 "table_function": "json_table"
226 }
227 }
228 ]
229 }
230 }
231 explain select * from
232 json_table('[{"a": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}]', '$[*]' COLUMNS( a INT PATH '$.a')) as tt;
233 id select_type table type possible_keys key key_len ref rows Extra
234 1 SIMPLE tt ALL NULL NULL NULL NULL 40 Table function: json_table
235 create view v1 as select * from
236 json_table('[{"color": "blue", "price": 50}]',
237 '$[*]' columns(color text path '$.nonexistent',
238 seq for ordinality)) as `ALIAS NOT QUOTED`;
239 select * from v1;
240 color seq
241 NULL 1
242 drop view v1;
243 create view v1 as select * from
244 json_table('[{"color": "blue", "price": 50},
245 {"color": "red", "price": 100}]',
246 '$[*]' columns(
247 color text path "$.QUOTES \" HERE \"",
248 color1 text path '$.QUOTES " HERE "',
249 color2 text path "$.QUOTES ' HERE '",
250 seq for ordinality)) as T;
251 select * from v1;
252 color color1 color2 seq
253 NULL NULL NULL 1
254 NULL NULL NULL 2
255 drop view v1;
256 CREATE TABLE t1 (x INT);
257 INSERT INTO t1 VALUES (1), (2), (3);
258 SELECT t1.x*2 m, jt.* FROM t1,
259 JSON_TABLE(m, '$[*]' COLUMNS (i INT PATH '$')) jt;
260 ERROR 42S22: Unknown column 'm' in 'JSON_TABLE'
261 DROP TABLE t1;
262 select *
263 from
264 json_table(JS3.size, '$' columns (size INT PATH '$.size')) as JS1,
265 json_table(JS1.size, '$' columns (size INT PATH '$.size')) as JS2,
266 json_table(JS1.size, '$' columns (size INT PATH '$.size')) as JS3 where 1;
267 ERROR 42S22: Unknown column 'JS3.size' in 'JSON_TABLE'
268 create table t1 (json varchar(100) character set utf8);
269 insert into t1 values ('{"value":"АБВ"}');
270 create table tj1 as
271 select T.value
272 from t1, json_table(t1.json, '$' columns (value varchar(32) PATH '$.value')) T;
273 show create table tj1;
274 Table Create Table
275 tj1 CREATE TABLE `tj1` (
276 `value` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL
277 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
278 drop table t1;
279 drop table tj1;
280 CREATE TABLE t1(id INT, f1 JSON);
281 INSERT INTO t1 VALUES
282 (1, '{\"1\": 1}'),
283 (2, '{\"1\": 2}'),
284 (3, '{\"1\": 3}'),
285 (4, '{\"1\": 4}'),
286 (5, '{\"1\": 5}'),
287 (6, '{\"1\": 6}');
288 ANALYZE TABLE t1;
289 Table Op Msg_type Msg_text
290 test.t1 analyze status Engine-independent statistics collected
291 test.t1 analyze Warning Engine-independent statistics are not collected for column 'f1'
292 test.t1 analyze status OK
293 SELECT * FROM JSON_TABLE(tt3.f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl STRAIGHT_JOIN t1 AS tt3;
294 ERROR 42S22: Unknown column 'tt3.f1' in 'JSON_TABLE'
295 SELECT * FROM t1 as jj1,
296 (SELECT tt2.*
297 FROM
298 t1 as tt2,
299 JSON_TABLE(tt3.f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl
300 STRAIGHT_JOIN
301 t1 AS tt3
302 ) dt
303 ORDER BY 1,3 LIMIT 10;
304 ERROR 42S22: Unknown column 'tt3.f1' in 'JSON_TABLE'
305 drop table t1;
306 select collation(x) from
307 JSON_TABLE('["abc"]', '$[*]' COLUMNS (x VARCHAR(10) CHARSET latin1 PATH '$')) tbl;
308 collation(x)
309 latin1_swedish_ci
310 SELECT * FROM JSON_TABLE('{"x":1, "y":2}', _utf8mb4'$' COLUMNS (NESTED PATH _utf8mb4'$.x'
311 COLUMNS(y INT PATH _utf8mb4'$.y' DEFAULT _utf8mb4'1' ON EMPTY DEFAULT _utf8mb4'2' ON ERROR))) jt;
312 y
313 1
314 select * from json_table(
315 '{"name":"t-shirt", "colors": ["yellow", "blue"],"sizes": ["small", "medium", "large"]}',
316 '$' columns(name varchar(32) path '$.name',
317 nested path '$.colors[*]' columns (
318 color varchar(32) path '$',
319 nested path '$.sizes[*]' columns (
320 size varchar(32) path '$'
321 )))) as t;
322 name color size
323 t-shirt yellow NULL
324 t-shirt blue NULL
325 SELECT x, length(x) FROM
326 JSON_TABLE('{}', '$' COLUMNS (x VARCHAR(10) PATH '$.x' DEFAULT 'abcdefg' ON EMPTY)) jt;
327 x length(x)
328 abcdefg 7
329 select * from
330 json_table('[{"a":"aa"}, {"b":null}]', '$[*]'
331 columns (col1 int path '$.b' default '456' on empty)) as tt;
332 col1
333 456
334 NULL
335 select * from
336 json_table('[{"a":"aa"}, {"b":true}]', '$[*]'
337 columns (col1 int path '$.b' default '456' on empty)) as tt;
338 col1
339 456
340 1
341 select * from
342 json_table('[{"a":"aa"}, {"b":false}]', '$[*]'
343 columns (col1 int path '$.b' default '456' on empty)) as tt;
344 col1
345 456
346 0
347 select * from
348 json_table('[{"a":"aa"}, {"b":null}]', '$[*]'
349 columns (col1 varchar(100) path '$.b' default '456' on empty)) as tt;
350 col1
351 456
352 NULL
353 select * from
354 json_table('[{"a":"aa"}, {"b":true}]', '$[*]'
355 columns (col1 varchar(100) path '$.b' default '456' on empty)) as tt;
356 col1
357 456
358 true
359 select * from
360 json_table('[{"a":"aa"}, {"b":false}]', '$[*]'
361 columns (col1 varchar(100) path '$.b' default '456' on empty)) as tt;
362 col1
363 456
364 false
365 select * from
366 json_table( '[{"a":"asd"}, {"a":123}, {"a":[]}, {"a":{}} ]', '$[*]'
367 columns (id for ordinality,
368 intcol int path '$.a' default '1234' on empty default '5678' on error)
369 ) as tt;
370 id intcol
371 1 0
372 2 123
373 3 5678
374 4 5678
375 Warnings:
376 Warning 1366 Incorrect integer value: 'asd' for column `*any*`.`json_table`.`intcol` at row 1
377 SELECT COUNT(*) FROM JSON_TABLE('[1, 2]', '$[*]' COLUMNS( I INT PATH '$')) tt;
378 COUNT(*)
379 2
380 create table t1 (a int);
381 insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
382 create table t2 (js json, b int);
383 insert into t2 select '[1,2,3]',A.a from t1 A, t1 B;
384 explain select * from t1,
385 (select * from t2, json_table(t2.js, '$[*]' columns (o for ordinality)) as jt) as TT2
386 where 1;
387 id select_type table type possible_keys key key_len ref rows Extra
388 1 SIMPLE t1 ALL NULL NULL NULL NULL 10
389 1 SIMPLE t2 ALL NULL NULL NULL NULL 100 Using join buffer (flat, BNL join)
390 1 SIMPLE jt ALL NULL NULL NULL NULL 40 Table function: json_table
391 drop table t1, t2;
392 CREATE TABLE t1 (x INT);
393 INSERT INTO t1 VALUES (1);
394 CREATE TABLE t2 (j JSON);
395 INSERT INTO t2 (j) VALUES ('[1,2,3]');
396 SELECT * FROM t1 RIGHT JOIN
397 (SELECT o FROM t2, JSON_TABLE(j, '$[*]' COLUMNS (o FOR ORDINALITY)) AS jt) AS t3 ON (t3.o = t1.x);
398 x o
399 1 1
400 NULL 2
401 NULL 3
402 DROP TABLE t1, t2;
403 create table t20 (a int not null);
404 create table t21 (a int not null primary key, js varchar(100));
405 insert into t20 values (1),(2);
406 insert into t21 values (1, '{"a":100}');
407 explain select t20.a, jt1.ab
408 from t20 left join t21 on t20.a=t21.a
409 join JSON_TABLE(t21.js,'$' COLUMNS (ab INT PATH '$.a')) AS jt1;
410 id select_type table type possible_keys key key_len ref rows Extra
411 1 SIMPLE t20 ALL NULL NULL NULL NULL 2
412 1 SIMPLE t21 eq_ref PRIMARY PRIMARY 4 test.t20.a 1
413 1 SIMPLE jt1 ALL NULL NULL NULL NULL 40 Table function: json_table
414 drop table t20, t21;
415 select * from
416 json_table(
417 '[
418 {"name": "X",
419 "colors":["blue"], "sizes": [1,2,3,4], "prices" : [10,20]},
420 {"name": "Y",
421 "colors":["red"], "sizes": [10,11], "prices" : [100,200,300]}
422 ]',
423 '$[*]' columns
424 (
425 seq0 for ordinality,
426 name varchar(4) path '$.name',
427 nested path '$.colors[*]' columns (
428 seq1 for ordinality,
429 color text path '$'
430 ),
431 nested path '$.sizes[*]' columns (
432 seq2 for ordinality,
433 size int path '$'
434 ),
435 nested path '$.prices[*]' columns (
436 seq3 for ordinality,
437 price int path '$'
438 )
439 )
440 ) as T order by seq0, name;
441 seq0 name seq1 color seq2 size seq3 price
442 1 X NULL NULL NULL NULL 1 10
443 1 X NULL NULL NULL NULL 2 20
444 1 X NULL NULL 1 1 NULL NULL
445 1 X NULL NULL 2 2 NULL NULL
446 1 X NULL NULL 3 3 NULL NULL
447 1 X NULL NULL 4 4 NULL NULL
448 1 X 1 blue NULL NULL NULL NULL
449 2 Y NULL NULL NULL NULL 1 100
450 2 Y NULL NULL NULL NULL 2 200
451 2 Y NULL NULL NULL NULL 3 300
452 2 Y NULL NULL 1 10 NULL NULL
453 2 Y NULL NULL 2 11 NULL NULL
454 2 Y 1 red NULL NULL NULL NULL
455 select * from json_table('[]', '$' COLUMNS(x FOR ORDINALITY));
456 ERROR HY000: Every table function must have an alias.
457 select min(x) from json_table('[]', '$' COLUMNS(x FOR ORDINALITY)) a;
458 min(x)
459 1
460 #
461 # Test for the problem with
462 # - Cross-outer-join dependency
463 # - dead-end join prefix
464 # - join order pruning
465 #
466 create table t20 (a int not null);
467 create table t21 (a int not null primary key, js varchar(100));
468 insert into t20 select seq from seq_1_to_100;
469 insert into t21 select a, '{"a":100}' from t20;
470 create table t31(a int);
471 create table t32(b int);
472 insert into t31 values (1);
473 insert into t32 values (1);
474 explain
475 select
476 t20.a, jt1.ab
477 from
478 t20
479 left join t21 on t20.a=t21.a
480 join
481 (t31 left join (t32 join JSON_TABLE(t21.js,'$' COLUMNS (ab INT PATH '$.a')) AS jt1) on t31.a<3);
482 id select_type table type possible_keys key key_len ref rows Extra
483 1 SIMPLE t31 system NULL NULL NULL NULL 1
484 1 SIMPLE t20 ALL NULL NULL NULL NULL 100
485 1 SIMPLE t21 eq_ref PRIMARY PRIMARY 4 test.t20.a 1
486 1 SIMPLE t32 ALL NULL NULL NULL NULL 1 Using where
487 1 SIMPLE jt1 ALL NULL NULL NULL NULL 40 Table function: json_table
488 drop table t20,t21,t31,t32;
489 #
490 # MDEV-25142: JSON_TABLE: CREATE VIEW involving EXISTS PATH ends up with invalid frm
491 #
492 drop view if exists v1;
493 CREATE VIEW v1 AS SELECT * FROM JSON_TABLE('[]', '$' COLUMNS (f INT EXISTS PATH '$')) a ;
494 show create view v1;
495 View Create View character_set_client collation_connection
496 v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `a`.`f` AS `f` from JSON_TABLE('[]', '$' COLUMNS (`f` int(11) EXISTS PATH '$')) `a` latin1 latin1_swedish_ci
497 drop view v1;
498 #
499 # MDEV-25145: JSON_TABLE: Assertion `fixed == 1' failed in Item_load_file::val_str on 2nd execution of PS
500 #
501 PREPARE stmt FROM "SELECT * FROM (SELECT * FROM JSON_TABLE(LOAD_FILE('x'), '$' COLUMNS (a FOR ORDINALITY)) AS t) AS sq";
502 EXECUTE stmt;
503 a
504 EXECUTE stmt;
505 a
506 #
507 # MDEV-JSON_TABLE: Server crashes in handler::print_error / hton_name upon ERROR ON EMPTY
508 #
509 SELECT a, b FROM JSON_TABLE('[]', '$' COLUMNS (a FOR ORDINALITY, b INT PATH '$[*]' ERROR ON EMPTY)) AS t ORDER BY a;
510 ERROR HY000: Field 'b' can't be set for JSON_TABLE 't'.
511 #
512 # MDEV-25151 JSON_TABLE: Unexpectedly padded values in a PATH column.
513 #
514 SET @old_character_set_connection= @@character_set_connection;
515 SET @@character_set_connection= utf8;
516 select hex(a), b from json_table('["foo","bar"]','$[*]' columns (a char(3) path '$', b for ordinality)) t;
517 hex(a) b
518 666F6F 1
519 626172 2
520 SET @@character_set_connection= @old_character_set_connection;
521 #
522 # MDEV-25183 JSON_TABLE: CREATE VIEW involving NESTED PATH ends up with invalid frm
523 #
524 CREATE VIEW v AS SELECT * FROM JSON_TABLE('{}', '$' COLUMNS(NESTED PATH '$**.*' COLUMNS(a FOR ORDINALITY), b VARCHAR(8) PATH '$')) AS jt;
525 SHOW CREATE VIEW v;
526 View Create View character_set_client collation_connection
527 v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `jt`.`a` AS `a`,`jt`.`b` AS `b` from JSON_TABLE('{}', '$' COLUMNS (NESTED PATH '$**.*' COLUMNS (`a` FOR ORDINALITY), `b` varchar(8) PATH '$')) `jt` latin1 latin1_swedish_ci
528 SELECT * FROM v;
529 a b
530 NULL NULL
531 DROP VIEW v;
532 #
533 # MDEV-25178 JSON_TABLE: ASAN use-after-poison in my_fill_8bit / Json_table_column::On_response::respond
534 #
535 SELECT * FROM JSON_TABLE('{}', '$' COLUMNS(a CHAR(100) PATH '$' DEFAULT "0" ON ERROR)) AS jt;
536 a
537 0
538 #
539 # MDEV-25188 JSON_TABLE: ASAN use-after-poison in Field_long::reset / Table_function_json_table::setup or malloc(): invalid size.
540 #
541 SELECT * FROM JSON_TABLE(CONVERT('{"x":1}' USING utf8mb4), '$' COLUMNS(a INT PATH '$', b CHAR(64) PATH '$.*', c INT EXISTS PATH '$**.*')) AS jt;
542 a b c
543 NULL 1 1
544 #
545 # 25192 JSON_TABLE: ASAN use-after-poison in field_conv_memcpy / Create_tmp_table::finalize upon query with derived table.
546 #
547 SET NAMES utf8;
548 SELECT * FROM ( SELECT * FROM JSON_TABLE('{}', '$' COLUMNS( a BINARY(12) PATH '$.*', b VARCHAR(40) PATH '$[*]', c VARCHAR(8) PATH '$**.*')) AS jt ) AS sq;
549 a b c
550 NULL NULL NULL
551 SET NAMES latin1;
552 #
553 # MDEV-25189 JSON_TABLE: Assertion `l_offset >= 0 && table->s->rec_buff_length - l_offset > 0' failed upon CREATE .. SELECT.
554 #
555 SET NAMES utf8;
556 CREATE TABLE t1 AS SELECT * FROM JSON_TABLE('{}', '$' COLUMNS(a CHAR(16) PATH '$.*', b TIMESTAMP PATH '$**.*')) AS jt;
557 DROP TABLE t1;
558 SET NAMES latin1;
559 #
560 # MDEV-25230 SON_TABLE: CREATE VIEW with 2nd level NESTED PATH ends up with invalid frm, Assertion `m_status == DA_ERROR || m_status == DA_OK || m_status == DA_OK_BULK' failed.
561 #
562 CREATE VIEW v AS SELECT * FROM JSON_TABLE('{}', '$' COLUMNS(NESTED PATH '$' COLUMNS(NESTED PATH '$.*' COLUMNS(o FOR ORDINALITY)))) AS jt;
563 SELECT * FROM v;
564 o
565 NULL
566 SHOW CREATE VIEW v;
567 View Create View character_set_client collation_connection
568 v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `jt`.`o` AS `o` from JSON_TABLE('{}', '$' COLUMNS (NESTED PATH '$.*' COLUMNS (NESTED PATH '$.*' COLUMNS (`o` FOR ORDINALITY)))) `jt` latin1 latin1_swedish_ci
569 DROP VIEW v;
570 #
571 # MDEV-25229 JSON_TABLE: Server crashes in hton_name upon MATCH .. AGAINST.
572 #
573 SELECT val, MATCH(val) AGAINST( 'MariaDB') FROM JSON_TABLE('{"db":"xx"}', '$' COLUMNS(val VARCHAR(32) PATH '$**.*')) AS jt;
574 ERROR HY000: The storage engine JSON_TABLE function doesn't support FULLTEXT indexes
575 #
576 # MDEV-25138 JSON_TABLE: A space between JSON_TABLE and opening bracket causes syntax error
577 #
578 select * from json_table ('{}', '$' COLUMNS(x FOR ORDINALITY)) a;
579 x
580 1
581 create table json_table(id int);
582 insert into json_table values (1), (2), (3);
583 select * from json_table;
584 id
585 1
586 2
587 3
588 drop table json_table;
589 #
590 # MDEV-25146 JSON_TABLE: Non-descriptive + wrong error messages upon trying to store array or object.
591 #
592 select a from json_table('[[]]', '$' columns(a char(8) path '$' error on error)) t;
593 ERROR HY000: Can't store an array or an object in the scalar column 'a' of JSON_TABLE 't'.
594 show warnings;
595 Level Code Message
596 Error 4178 Can't store an array or an object in the scalar column 'a' of JSON_TABLE 't'.
597 #
598 # MDEV-JSON_TABLE: CREATE TABLE ignores NULL ON ERROR (implicit or explicit) and fails.
599 #
600 CREATE TABLE t1 AS SELECT * FROM JSON_TABLE('{"x":1}', '$' COLUMNS(f DATE PATH '$.*')) AS jt;
601 Warnings:
602 Warning 1265 Data truncated for column 'f' at row 1
603 SELECT * FROM t1;
604 f
605 0000-00-00
606 DROP TABLE t1;
607 #
608 # MDEV-25254: JSON_TABLE: Inconsistent name resolution with right joins
609 #
610 CREATE TABLE t1 (a INT);
611 SELECT * FROM t1 RIGHT JOIN JSON_TABLE(t1.a,'$' COLUMNS(o FOR ORDINALITY)) jt ON TRUE;
612 ERROR 42S22: Unknown column 't1.a' in 'JSON_TABLE'
613 CREATE VIEW v AS
614 SELECT * FROM t1 RIGHT JOIN JSON_TABLE(t1.a,'$' COLUMNS(o FOR ORDINALITY)) jt ON TRUE;
615 ERROR 42S22: Unknown column 't1.a' in 'JSON_TABLE'
616 insert into t1 values (1),(2),(3);
617 SELECT * FROM t1 RIGHT JOIN JSON_TABLE(t1.a,'$' COLUMNS(o FOR ORDINALITY)) jt ON TRUE;
618 ERROR 42S22: Unknown column 't1.a' in 'JSON_TABLE'
619 drop table t1;
620 #
621 # MDEV-25202: JSON_TABLE: Early table reference leads to unexpected result set, server crash
622 #
623 CREATE TABLE t1 (o INT);
624 INSERT INTO t1 VALUES (1),(2);
625 CREATE TABLE t2 (a INT);
626 INSERT INTO t2 VALUES (3),(4);
627 SELECT * FROM JSON_TABLE(a, '$' COLUMNS(o FOR ORDINALITY)) AS jt1 NATURAL JOIN t1 JOIN t2;
628 ERROR 42S22: Unknown column 'a' in 'JSON_TABLE'
629 SELECT * FROM JSON_TABLE(a, '$' COLUMNS(o FOR ORDINALITY)) AS jt1 NATURAL JOIN t1 STRAIGHT_JOIN t2;
630 ERROR 42S22: Unknown column 'a' in 'JSON_TABLE'
631 drop table t1,t2;
632 # Now, try a JSON_TABLE that has a subquery that has an outside reference:
633 create table t1(a int, js varchar(32));
634 create table t2(a varchar(100));
635 insert into t2 values('');
636 explain
637 select *
638 from
639 t1 left join
640 json_table(concat('',js),
641 '$' columns ( color varchar(32) path '$.color')
642 ) as JT on 1;
643 id select_type table type possible_keys key key_len ref rows Extra
644 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
645 explain
646 select *
647 from
648 t1 right join
649 json_table(concat('',js),
650 '$' columns ( color varchar(32) path '$.color')
651 ) as JT on 1;
652 ERROR 42S22: Unknown column 'js' in 'JSON_TABLE'
653 explain
654 select *
655 from
656 t1 left join
657 json_table((select concat(a,js) from t2),
658 '$' columns ( color varchar(32) path '$.color')
659 ) as JT on 1;
660 id select_type table type possible_keys key key_len ref rows Extra
661 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
662 2 SUBQUERY t2 system NULL NULL NULL NULL 1
663 explain
664 select *
665 from
666 t1 right join
667 json_table((select concat(a,js) from t2),
668 '$' columns ( color varchar(32) path '$.color')
669 ) as JT on 1;
670 ERROR 42S22: Unknown column 'js' in 'SELECT'
671 drop table t1,t2;
672 #
673 # Now, a testcase with JSON_TABLEs inside NATURAL JOIN
674 #
675 create table t1 (a int, b int);
676 create table t2 (a int, c int);
677 select * from
678 t1,
679 ( t2
680 natural join
681 (
682 json_table(JT2.d, '$' COLUMNS (d for ordinality)) as JT
683 natural join
684 json_table(JT.d, '$' COLUMNS (d for ordinality)) as JT2
685 )
686 );
687 ERROR 42S22: Unknown column 'JT2.d' in 'JSON_TABLE'
688 drop table t1, t2;
689 #
690 # MDEV-25352: JSON_TABLE: Inconsistent name resolution and ER_VIEW_INVALID ...
691 # (Just the testcase)
692 #
693 CREATE TABLE t1 (a INT, b VARCHAR(8));
694 INSERT INTO t1 VALUES (1,'{}'),(2,'[]');
695 CREATE TABLE t2 (a INT);
696 INSERT INTO t2 VALUES (2),(3);
697 SELECT t1.*
698 FROM
699 t1 NATURAL JOIN t2
700 RIGHT JOIN
701 JSON_TABLE (t1.b, '$' COLUMNS(o FOR ORDINALITY)) AS jt ON (t1.a = jt.o)
702 WHERE t1.a = 1;
703 ERROR 42S22: Unknown column 't1.b' in 'JSON_TABLE'
704 CREATE OR REPLACE VIEW v AS
705 SELECT t1.* FROM t1 NATURAL JOIN t2 RIGHT JOIN JSON_TABLE (t1.b, '$' COLUMNS(o FOR ORDINALITY)) AS jt ON (t1.a = jt.o) WHERE t1.a = 1;
706 ERROR 42S22: Unknown column 't1.b' in 'JSON_TABLE'
707 drop table t1,t2;
708 #
709 # MDEV-25256: JSON_TABLE: Error ER_VIEW_INVALID upon running query via view
710 #
711 SELECT * FROM
712 JSON_TABLE('[]', '$' COLUMNS(a TEXT PATH '$[*]')) AS jt1
713 RIGHT JOIN JSON_TABLE(jt1.a, '$' COLUMNS(o2 FOR ORDINALITY)) AS jt2
714 ON(1)
715 RIGHT JOIN JSON_TABLE('[]', '$' COLUMNS(o3 FOR ORDINALITY)) AS jt3
716 ON(1)
717 WHERE 0;
718 ERROR 42S22: Unknown column 'jt1.a' in 'JSON_TABLE'
719 #
720 # MDEV-25346: JSON_TABLE: Server crashes in Item_field::fix_outer_field upon subquery with unknown column
721 #
722 CREATE TABLE t1 (a INT);
723 CREATE TABLE t2 (b INT);
724 SELECT * FROM ( SELECT * FROM t1 JOIN t2 ON (b IN(SELECT x FROM (SELECT 1 AS c) AS sq1))) AS sq2;
725 ERROR 42S22: Unknown column 'x' in 'SELECT'
726 DROP TABLE t1, t2;
727 #
728 # Another testcase
729 #
730 create table t1 (item_name varchar(32), item_props varchar(1024));
731 insert into t1 values ('Jeans', '{"color": ["green", "brown"], "price": 50}');
732 insert into t1 values ('Shirt', '{"color": ["blue", "white"], "price": 20}');
733 insert into t1 values ('Jeans', '{"color": ["black"], "price": 60}');
734 insert into t1 values ('Jeans', '{"color": ["gray"], "price": 60}');
735 insert into t1 values ('Laptop', '{"color": ["black"], "price": 1000}');
736 insert into t1 values ('Shirt', '{"color": ["black"], "price": 20}');
737 select
738 t.item_name,
739 jt.*
740 from
741 (select
742 t1.item_name,
743 concat(
744 concat(
745 concat(
746 "{\"color\": ",
747 concat(
748 concat("[\"",
749 group_concat( jt.color separator "\", \"")
750 ),
751 "\"]")
752 ),','
753 ),
754 concat(concat("\"price\": ",jt.price),'}')
755 ) as item_props
756 from
757 t1,
758 json_table(
759 t1.item_props,
760 '$' columns (
761 nested path '$.color[*]' columns (color varchar(32) path '$'),
762 price int path '$.price')
763 ) as jt
764 group by
765 t1.item_name, jt.price
766 ) as t,
767 json_table(t.item_props,
768 '$' columns (
769 nested path '$.color[*]' columns (color varchar(32) path '$'),
770 price int path '$.price')
771 ) as jt
772 order by
773 t.item_name, jt.price, jt.color;
774 item_name color price
775 Jeans brown 50
776 Jeans green 50
777 Jeans black 60
778 Jeans gray 60
779 Laptop black 1000
780 Shirt black 20
781 Shirt blue 20
782 Shirt white 20
783 drop table t1;
784 #
785 # MDEV-25380: JSON_TABLE: Assertion `join->best_read < double(1.797...) fails
786 #
787 CREATE TABLE t1 (a INT, b TEXT);
788 INSERT INTO t1 VALUES (1,'{}'),(2,'[]');
789 explain
790 SELECT *
791 FROM t1
792 WHERE
793 EXISTS(SELECT *
794 FROM JSON_TABLE(b, '$' COLUMNS(o FOR ORDINALITY)) AS jt
795 WHERE jt.o = t1.a);
796 id select_type table type possible_keys key key_len ref rows Extra
797 1 PRIMARY t1 ALL NULL NULL NULL NULL 2
798 1 PRIMARY jt ALL NULL NULL NULL NULL 40 Table function: json_table; Using where; FirstMatch(t1)
799 drop table t1;
800 #
801 # MDEV-25381: JSON_TABLE: ER_WRONG_OUTER_JOIN upon query with LEFT and RIGHT joins and view
802 #
803 CREATE TABLE t1 (a INT);
804 INSERT INTO t1 VALUES (1),(2);
805 CREATE TABLE t2 (b INT, c TEXT);
806 INSERT INTO t2 VALUES (1,'{}'),(2,'[]');
807 CREATE VIEW v2 AS SELECT * FROM t2;
808 SELECT *
809 FROM
810 t1 RIGHT JOIN
811 t2 AS tt
812 LEFT JOIN
813 JSON_TABLE(tt.c, '$' COLUMNS(o FOR ORDINALITY)) AS jt
814 ON tt.b = jt.o
815 ON t1.a = tt.b;
816 a b c o
817 1 1 {} 1
818 2 2 [] NULL
819 SELECT *
820 FROM
821 t1 RIGHT JOIN
822 v2 AS tt
823 LEFT JOIN
824 JSON_TABLE(tt.c, '$' COLUMNS(o FOR ORDINALITY)) AS jt
825 ON tt.b = jt.o
826 ON t1.a = tt.b;
827 a b c o
828 1 1 {} 1
829 2 2 [] NULL
830 SELECT *
831 FROM
832 t1 RIGHT JOIN
833 v2 AS tt
834 LEFT JOIN
835 JSON_TABLE(CONCAT(tt.c,''), '$' COLUMNS(o FOR ORDINALITY)) AS jt
836 ON tt.b = jt.o
837 ON t1.a = tt.b;
838 a b c o
839 1 1 {} 1
840 2 2 [] NULL
841 prepare s from
842 "SELECT *
843 FROM
844 t1 RIGHT JOIN
845 v2 AS tt
846 LEFT JOIN
847 JSON_TABLE(CONCAT(tt.c,''), '$' COLUMNS(o FOR ORDINALITY)) AS jt
848 ON tt.b = jt.o
849 ON t1.a = tt.b";
850 execute s;
851 a b c o
852 1 1 {} 1
853 2 2 [] NULL
854 execute s;
855 a b c o
856 1 1 {} 1
857 2 2 [] NULL
858 DROP VIEW v2;
859 DROP TABLE t1, t2;
860 #
861 # MDEV-25259 JSON_TABLE: Illegal mix of collations upon executing query with combination of charsets via view.
862 #
863 CREATE VIEW v AS
864 SELECT * FROM JSON_TABLE(CONVERT('[]' USING dec8),
865 '$' COLUMNS(b VARCHAR(8) CHARSET utf8 PATH '$')) AS jt2
866 WHERE (CONVERT('[]' USING cp1256) = b);
867 SELECT * FROM v;
868 b
869 DROP VIEW v;
870 #
871 # MDEV-25397: JSON_TABLE: Unexpected ER_MIX_OF_GROUP_FUNC_AND_FIELDS upon query with JOIN
872 #
873 set @save_sql_mode= @@sql_mode;
874 SET sql_mode='ONLY_FULL_GROUP_BY';
875 CREATE TABLE t1 (a TEXT);
876 SELECT SUM(o) FROM t1 JOIN JSON_TABLE(t1.a, '$' COLUMNS(o FOR ORDINALITY)) jt;
877 SUM(o)
878 NULL
879 set sql_mode=@save_sql_mode;
880 drop table t1;
881 #
882 # MDEV-25379 JSON_TABLE: ERROR ON clauses are ignored if a column is not on select list.
883 #
884 SELECT * FROM JSON_TABLE ('{}', '$' COLUMNS(a INT PATH '$.*' ERROR ON EMPTY, o FOR ORDINALITY)) AS jt;
885 ERROR HY000: Field 'a' can't be set for JSON_TABLE 'jt'.
886 SELECT o FROM JSON_TABLE ('{}', '$' COLUMNS(a INT PATH '$.*' ERROR ON EMPTY, o FOR ORDINALITY)) AS jt;
887 ERROR HY000: Field 'a' can't be set for JSON_TABLE 'jt'.
888 SELECT COUNT(*) FROM JSON_TABLE ('{}', '$' COLUMNS(a INT PATH '$.*' ERROR ON EMPTY, o FOR ORDINALITY)) AS jt;
889 ERROR HY000: Field 'a' can't be set for JSON_TABLE 'jt'.
890 #
891 # MDEV-25408 JSON_TABLE: AddressSanitizer CHECK failed in Binary_string::realloc_raw.
892 #
893 SELECT x, COUNT(*) FROM JSON_TABLE( '{}', '$' COLUMNS(
894 a BIT(14) PATH '$', b CHAR(16) PATH '$', c INT PATH '$[0]', d INT PATH '$[1]', e INT PATH '$[2]',
895 f INT PATH '$[3]', g INT PATH '$[4]', h INT PATH '$[5]', i INT PATH '$[6]', j INT PATH '$[7]',
896 x TEXT PATH '$[9]')) AS jt GROUP BY x;
897 x COUNT(*)
898 NULL 1
899 #
900 # MDEV-25408 JSON_TABLE: AddressSanitizer CHECK failed in Binary_string::realloc_raw.
901 #
902 SELECT * FROM JSON_TABLE('{}', '$' COLUMNS(
903 a TEXT EXISTS PATH '$', b VARCHAR(40) PATH '$', c BIT(60) PATH '$', d VARCHAR(60) PATH '$', e BIT(62) PATH '$',
904 f FOR ORDINALITY, g INT PATH '$', h VARCHAR(36) PATH '$', i DATE PATH '$', j CHAR(4) PATH '$'
905 )) AS jt;
906 a b c d e f g h i j
907 1 NULL NULL NULL NULL 1 NULL NULL NULL NULL
908 #
909 # MDEV-25373 JSON_TABLE: Illegal mix of collations upon executing PS once, or SP/function twice.
910 #
911 SELECT * FROM JSON_TABLE (CONVERT('[1,2]' USING koi8u), '$[*]' COLUMNS(a CHAR(8) PATH '$')) AS jt1 NATURAL JOIN JSON_TABLE (CONVERT('[2,3]' USING eucjpms), '$[*]' COLUMNS(a CHAR(8) PATH '$')) AS jt2;
912 a
913 2
914 PREPARE stmt1 FROM "
915 SELECT * FROM JSON_TABLE (CONVERT('[1,2]' USING koi8u), '$[*]' COLUMNS(a CHAR(8) PATH '$')) AS jt1 NATURAL JOIN JSON_TABLE (CONVERT('[2,3]' USING eucjpms), '$[*]' COLUMNS(a CHAR(8) PATH '$')) AS jt2;
916 ";
917 EXECUTE stmt1;
918 a
919 2
920 DEALLOCATE PREPARE stmt1;
921 #
922 # MDEV-25149 JSON_TABLE: Inconsistency in implicit data type conversion.
923 #
924 select * from json_table( '[{"a":"asd"}, {"a":123}, {"a":[]}, {"a":{}} ]', '$[*]'
925 columns ( id for ordinality,
926 intcol int path '$.a' default '1234' on empty default '5678' on error)
927 ) as tt;
928 id intcol
929 1 0
930 2 123
931 3 5678
932 4 5678
933 Warnings:
934 Warning 1366 Incorrect integer value: 'asd' for column `*any*`.`json_table`.`intcol` at row 1
935 #
936 # MDEV-25377 JSON_TABLE: Wrong value with implicit conversion.
937 #
938 select * from json_table('{"a":"foo", "b":1, "c":1000}', '$.*' columns(converted tinyint path '$', original text path '$')) as jt;
939 converted original
940 0 foo
941 1 1
942 127 1000
943 Warnings:
944 Warning 1366 Incorrect integer value: 'foo' for column `*any*`.`json_table`.`converted` at row 1
945 Warning 1264 Out of range value for column 'converted' at row 3
946 select * from json_table('{"a":"foo", "b":1, "c":1000}', '$.*' columns(converted tinyint path '$', original text path '$')) as jt order by converted;
947 converted original
948 0 foo
949 1 1
950 127 1000
951 Warnings:
952 Warning 1366 Incorrect integer value: 'foo' for column `*any*`.`json_table`.`converted` at row 1
953 Warning 1264 Out of range value for column 'converted' at row 1
954 Warning 1366 Incorrect integer value: 'foo' for column `*any*`.`json_table`.`converted` at row 1
955 Warning 1264 Out of range value for column 'converted' at row 3
956 select * from json_table('{"a":"foo", "b":1, "c":1000}', '$.*' columns(converted tinyint path '$', original text path '$')) as jt order by original;
957 converted original
958 1 1
959 127 1000
960 0 foo
961 Warnings:
962 Warning 1264 Out of range value for column 'converted' at row 2
963 Warning 1366 Incorrect integer value: 'foo' for column `*any*`.`json_table`.`converted` at row 3
964 select * from
965 json_table('[{"color": "blue", "price": { "high": 10, "low": 5}},
966 {"color": "white", "price": "pretty low"},
967 {"color": "yellow", "price": 256.20},
968 {"color": "red", "price": { "high": 20, "low": 8}}]',
969 '$[*]' columns(color varchar(100) path '$.color',
970 price json path '$.price'
971 )
972 ) as T;
973 color price
974 blue { "high": 10, "low": 5}
975 white "pretty low"
976 yellow 256.20
977 red { "high": 20, "low": 8}
978 #
979 # MDEV-27696 Json table columns accept redundant COLLATE syntax
980 #
981 SELECT * FROM json_table('[{"name":"str"}]', '$[*]'
982 COLUMNS (
983 name BLOB COLLATE `binary` PATH '$.name'
984 )
985 ) AS jt;
986 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COLLATE `binary` PATH '$.name'
987 )
988 ) AS jt' at line 3
989 SELECT * FROM json_table('[{"name":"str"}]', '$[*]'
990 COLUMNS (
991 name VARCHAR(10) COLLATE latin1_bin COLLATE latin1_swedish_ci PATH '$.name'
992 )
993 ) AS jt;
994 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COLLATE latin1_swedish_ci PATH '$.name'
995 )
996 ) AS jt' at line 3
997 SELECT * FROM json_table('[{"name":"str"}]', '$[*]'
998 COLUMNS (
999 name VARCHAR(10) BINARY COLLATE utf8_czech_ci path '$.name'
1000 )
1001 ) AS jt;
1002 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'COLLATE utf8_czech_ci path '$.name'
1003 )
1004 ) AS jt' at line 3
1005 #
1006 # MDEV-27690 Crash on `CHARACTER SET csname COLLATE DEFAULT` in column definition
1007 #
1008 SELECT * FROM json_table('[{"name":"Jeans"}]', '$[*]'
1009 COLUMNS(
1010 name VARCHAR(10) CHARACTER SET latin1 COLLATE DEFAULT PATH '$.name'
1011 )
1012 ) AS jt;
1013 name
1014 Jeans
1015 #
1016 # MDEV-28480: Assertion `0' failed in Item_row::illegal_method_call
1017 # on SELECT FROM JSON_TABLE
1018 #
1019 SELECT 1 FROM JSON_TABLE (row(1,2), '$' COLUMNS (o FOR ORDINALITY)) AS j;
1020 ERROR 21000: Operand should contain 1 column(s)
1021 #
1022 # MDEV-30623 JSON_TABLE in subquery not correctly marked as correlated
1023 # update_correlated_cache() fails to take JSON_TABLE functions in
1024 # subqueries into account.
1025 #
1026 create table t1(c json);
1027 insert into t1 values ('[{"x":"1"},{"x":"2"}]'),
1028 ('[{"x":"10"},{"x":"20"}]'),
1029 ('[{"x":"100"},{"x":"200"}]');
1030 select c,
1031 (SELECT sum(x) FROM json_table(c, "$[*]" columns( x int path "$.x"))
1032 AS jt) as SUBQ
1033 from t1;
1034 c SUBQ
1035 [{"x":"1"},{"x":"2"}] 3
1036 [{"x":"10"},{"x":"20"}] 30
1037 [{"x":"100"},{"x":"200"}] 300
1038 explain select c,
1039 (SELECT sum(x) FROM json_table(c, "$[*]" columns( x int path "$.x"))
1040 AS jt) as SUBQ
1041 from t1;
1042 id select_type table type possible_keys key key_len ref rows Extra
1043 1 PRIMARY t1 ALL NULL NULL NULL NULL 3
1044 2 DEPENDENT SUBQUERY jt ALL NULL NULL NULL NULL 40 Table function: json_table
1045 drop table t1;
1046 #
1047 # MDEV-25822: JSON_TABLE: default values should allow non-string literals
1048 #
1049 select * from json_table('{"a": "b"}', '$' columns(col1 varchar(32) path '$.fooo' default 0.5 on empty)) as T;
1050 col1
1051 0.5
1052 select * from json_table('{"a": "b"}', '$' columns(col1 varchar(32) path '$.fooo' default 5 on empty)) as T;
1053 col1
1054 5
1055 select * from json_table('{"a": "b"}', '$' columns(col1 varchar(32) path '$.fooo' default 'asdf' on empty)) as T;
1056 col1
1057 asdf
1058 select * from json_table('{"a": "b"}', '$' columns(col1 varchar(32) path '$.fooo' default -0.5 on empty)) as T;
1059 col1
1060 -0.5
1061 select * from json_table('{"a": "b"}', '$' columns(col1 varchar(32) path '$.fooo' default 18446744073709551615 on empty)) as T;
1062 col1
1063 18446744073709551615
1064 select * from json_table('{"a": "b"}', '$' columns(col1 varchar(32) path '$.fooo' default DATE '2021-01-01' on empty)) as T;
1065 col1
1066 2021-01-01
1067 create view v as select * from json_table('{"a": "b"}', '$' columns(col1 varchar(32) path '$.fooo' default 5 on empty)) as T;
1068 select * from v;
1069 col1
1070 5
1071 show create view v;
1072 View Create View character_set_client collation_connection
1073 v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `T`.`col1` AS `col1` from JSON_TABLE('{"a": "b"}', '$' COLUMNS (`col1` varchar(32) PATH '$.fooo' DEFAULT 5 ON EMPTY)) `T` latin1 latin1_swedish_ci
1074 drop view v;
1075 create view v as select * from json_table('{"a": "b"}', '$' columns(col1 varchar(32) path '$.fooo' default 18446744073709551615 on empty)) as T;
1076 select * from v;
1077 col1
1078 18446744073709551615
1079 show create view v;
1080 View Create View character_set_client collation_connection
1081 v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `T`.`col1` AS `col1` from JSON_TABLE('{"a": "b"}', '$' COLUMNS (`col1` varchar(32) PATH '$.fooo' DEFAULT 18446744073709551615 ON EMPTY)) `T` latin1 latin1_swedish_ci
1082 drop view v;
1083 create view v as select * from json_table('{"a": "b"}', '$' columns(col1 varchar(32) path '$.fooo' default 'asdf' on empty)) as T;
1084 select * from v;
1085 col1
1086 asdf
1087 show create view v;
1088 View Create View character_set_client collation_connection
1089 v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `T`.`col1` AS `col1` from JSON_TABLE('{"a": "b"}', '$' COLUMNS (`col1` varchar(32) PATH '$.fooo' DEFAULT 'asdf' ON EMPTY)) `T` latin1 latin1_swedish_ci
1090 drop view v;
1091 # End of 10.6 tests
1092 #
1093 # MDEV-27743 Remove Lex::charset
1094 #
1095 SELECT collation(name)
1096 FROM json_table('[{"name":"Jeans"}]', '$[*]'
1097 COLUMNS(
1098 name VARCHAR(10) PATH '$.name'
1099 )
1100 ) AS jt;
1101 collation(name)
1102 utf8mb4_general_ci
1103 SELECT collation(name)
1104 FROM json_table('[{"name":"Jeans"}]', '$[*]'
1105 COLUMNS(
1106 name VARCHAR(10) COLLATE DEFAULT PATH '$.name'
1107 )
1108 ) AS jt;
1109 collation(name)
1110 utf8mb4_uca1400_ai_ci
1111 SELECT collation(name)
1112 FROM json_table('[{"name":"Jeans"}]', '$[*]'
1113 COLUMNS(
1114 name VARCHAR(10) BINARY PATH '$.name'
1115 )
1116 ) AS jt;
1117 collation(name)
1118 utf8mb4_bin
1119 CREATE VIEW v1 AS
1120 SELECT *
1121 FROM json_table('[{"name":"Jeans"}]', '$[*]'
1122 COLUMNS(
1123 name VARCHAR(10) PATH '$.name'
1124 )
1125 ) AS jt;
1126 SHOW CREATE VIEW v1;
1127 View Create View character_set_client collation_connection
1128 v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `jt`.`name` AS `name` from JSON_TABLE('[{"name":"Jeans"}]', '$[*]' COLUMNS (`name` varchar(10) PATH '$.name')) `jt` latin1 latin1_swedish_ci
1129 SELECT collation(name) FROM v1;
1130 collation(name)
1131 utf8mb4_general_ci
1132 DROP VIEW v1;
1133 CREATE VIEW v1 AS
1134 SELECT *
1135 FROM json_table('[{"name":"Jeans"}]', '$[*]'
1136 COLUMNS(
1137 name VARCHAR(10) COLLATE DEFAULT PATH '$.name'
1138 )
1139 ) AS jt;
1140 SHOW CREATE VIEW v1;
1141 View Create View character_set_client collation_connection
1142 v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `jt`.`name` AS `name` from JSON_TABLE('[{"name":"Jeans"}]', '$[*]' COLUMNS (`name` varchar(10) PATH '$.name')) `jt` latin1 latin1_swedish_ci
1143 SELECT collation(name) FROM v1;
1144 collation(name)
1145 utf8mb4_general_ci
1146 DROP VIEW v1;
1147 CREATE VIEW v1 AS
1148 SELECT *
1149 FROM json_table('[{"name":"Jeans"}]', '$[*]'
1150 COLUMNS(
1151 name VARCHAR(10) BINARY PATH '$.name'
1152 )
1153 ) AS jt;
1154 SHOW CREATE VIEW v1;
1155 View Create View character_set_client collation_connection
1156 v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `jt`.`name` AS `name` from JSON_TABLE('[{"name":"Jeans"}]', '$[*]' COLUMNS (`name` varchar(10) CHARSET utf8mb4 COLLATE utf8mb4_bin PATH '$.name')) `jt` latin1 latin1_swedish_ci
1157 SELECT collation(name) FROM v1;
1158 collation(name)
1159 utf8mb4_bin
1160 DROP VIEW v1;
1161 #
1162 # MDEV-28319: Assertion `cur_step->type & JSON_PATH_KEY' failed in json_find_path
1163 #
1164 SELECT * FROM JSON_TABLE('{"foo":{"bar":1},"qux":2}', '$' COLUMNS(c1 VARCHAR(8) PATH '$[0]', c2 CHAR(8) PATH '$.*.x')) AS js;
1165 c1 c2
1166 NULL NULL
1167 #
1168 # MDEV-29446 Change SHOW CREATE TABLE to display default collations
1169 #
1170 CREATE VIEW v1 AS
1171 SELECT * FROM
1172 JSON_TABLE('[{"name":"Laptop"}]', '$[*]'
1173 COLUMNS
1174 (
1175 name VARCHAR(10) CHARACTER SET latin1 PATH '$.name')
1176 ) AS jt;
1177 SHOW CREATE VIEW v1;
1178 View Create View character_set_client collation_connection
1179 v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `jt`.`name` AS `name` from JSON_TABLE('[{"name":"Laptop"}]', '$[*]' COLUMNS (`name` varchar(10) CHARSET latin1 COLLATE latin1_swedish_ci PATH '$.name')) `jt` latin1 latin1_swedish_ci
1180 DROP VIEW v1;
1181 CREATE VIEW v1 AS
1182 SELECT * FROM
1183 JSON_TABLE('[{"name":"Laptop"}]', '$[*]'
1184 COLUMNS
1185 (
1186 name VARCHAR(10) CHARACTER SET utf8mb3 PATH '$.name')
1187 ) AS jt;
1188 SHOW CREATE VIEW v1;
1189 View Create View character_set_client collation_connection
1190 v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `jt`.`name` AS `name` from JSON_TABLE('[{"name":"Laptop"}]', '$[*]' COLUMNS (`name` varchar(10) CHARSET utf8mb3 COLLATE utf8mb3_uca1400_ai_ci PATH '$.name')) `jt` latin1 latin1_swedish_ci
1191 DROP VIEW v1;
1192 CREATE VIEW v1 AS
1193 SELECT * FROM
1194 JSON_TABLE('[{"name":"Laptop"}]', '$[*]'
1195 COLUMNS
1196 (
1197 name VARCHAR(10) CHARACTER SET BINARY PATH '$.name')
1198 ) AS jt;
1199 SHOW CREATE VIEW v1;
1200 View Create View character_set_client collation_connection
1201 v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `jt`.`name` AS `name` from JSON_TABLE('[{"name":"Laptop"}]', '$[*]' COLUMNS (`name` varbinary(10) PATH '$.name')) `jt` latin1 latin1_swedish_ci
1202 DROP VIEW v1;
1203 CREATE VIEW v1 AS
1204 SELECT * FROM
1205 JSON_TABLE('[{"name":"Laptop"}]', '$[*]'
1206 COLUMNS
1207 (
1208 name ENUM('Laptop') CHARACTER SET BINARY PATH '$.name')
1209 ) AS jt;
1210 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ENUM('Laptop') CHARACTER SET BINARY PATH '$.name')
1211 ) AS jt' at line 6
1212 # End of 10.9 tests
1213 #
1214 # MDEV-27898 CREATE VIEW AS SELECT FROM JSON_TABLE column requires global privileges
1215 #
1216 # Beginning of 10.11 tests
1217 create view v1 as (select * from
1218 json_table('[{"a":"1"}]', '$[*]' columns(a int path '$.a') ) as jt);
1219 create user u1@localhost;
1220 grant ALL on test.* to u1@localhost;
1221 connect con1,localhost,u1,,test;
1222 create view v2 as
1223 (select * from json_table('[{"a":"1"}]', '$[*]' columns(a int path '$.a') ) as jt);
1224 disconnect con1;
1225 connection default;
1226 DROP VIEW v2;
1227 DROP VIEW v1;
1228 DROP USER u1@localhost;
1229 #
1230 # MDEV-34081: View containing JSON_TABLE does not return JSON
1231 #
1232 CREATE OR REPLACE VIEW test_view AS SELECT * FROM JSON_TABLE('
1233 [
1234 {
1235 "caption": "First Element",
1236 "value": 1
1237 },
1238 {
1239 "caption": "Second Element",
1240 "value": 2
1241 }
1242 ]
1243 ', '$[*]' COLUMNS(
1244 caption VARCHAR(200) PATH '$.caption',
1245 whole_block JSON PATH '$')) t;
1246 SELECT * FROM test_view;
1247 caption whole_block
1248 First Element {
1249 "caption": "First Element",
1250 "value": 1
1251 }
1252 Second Element {
1253 "caption": "Second Element",
1254 "value": 2
1255 }
1256 SELECT * FROM JSON_TABLE('
1257 [
1258 {
1259 "caption": "First Element",
1260 "value": 1
1261 },
1262 {
1263 "caption": "Second Element",
1264 "value": 2
1265 }
1266 ]
1267 ', '$[*]' COLUMNS(
1268 caption VARCHAR(200) PATH '$.caption',
1269 whole_block JSON PATH '$')) t;
1270 caption whole_block
1271 First Element {
1272 "caption": "First Element",
1273 "value": 1
1274 }
1275 Second Element {
1276 "caption": "Second Element",
1277 "value": 2
1278 }
1279 DROP VIEW test_view;
1280 # End of 10.11 test
1281 #
1282 # MDEV-29390: Improve coverage for UPDATE and DELETE statements in MTR test suites
1283 #
1284 # Multi-update with JSON_TABLE
1285 create table t1 ( name varchar(10),
1286 color varchar(10),
1287 price decimal(8,2),
1288 instock BOOLEAN);
1289 insert into t1 values ("Laptop", "black", 20000, 1);
1290 insert into t1 values ("Jacket", "brown", 5000, 1);
1291 insert into t1 values ("Jeans", "blue", 5000, 1);
1292 select * from t1;
1293 name color price instock
1294 Laptop black 20000.00 1
1295 Jacket brown 5000.00 1
1296 Jeans blue 5000.00 1
1297 set @json='
1298 [
1299 {"name":"Laptop", "color":"black", "price":"1000", "ordered":"3"},
1300 {"name":"Jeans", "color":"blue", "ordered":"0"},
1301 {"name":"Phone", "color":"red", "ordered":"0"}
1302 ]';
1303 select * from json_table(@json, '$[*]'
1304 columns(
1305 name varchar(10) path '$.name',
1306 color varchar(10) path '$.color',
1307 price decimal(8,2) path '$.price',
1308 ordered boolean path '$.ordered' )
1309 ) as jt;
1310 name color price ordered
1311 Laptop black 1000.00 3
1312 Jeans blue NULL 0
1313 Phone red NULL 0
1314 explain update t1, JSON_TABLE(@json,'$[*]'
1315 COLUMNS (
1316 name varchar(10) path '$.name',
1317 color varchar(10) path '$.color',
1318 price decimal(8,2) path '$.price',
1319 ordered boolean path '$.ordered'
1320 )) AS jt1
1321 SET t1.instock=0 where t1.name=jt1.name and jt1.ordered=3;
1322 id select_type table type possible_keys key key_len ref rows Extra
1323 1 SIMPLE t1 ALL NULL NULL NULL NULL 3
1324 1 SIMPLE jt1 ALL NULL NULL NULL NULL 40 Table function: json_table; Using where
1325 update t1, JSON_TABLE(@json,'$[*]'
1326 COLUMNS (
1327 name varchar(10) path '$.name',
1328 color varchar(10) path '$.color',
1329 price decimal(8,2) path '$.price',
1330 ordered boolean path '$.ordered'
1331 )) AS jt1
1332 SET t1.instock=0 where t1.name=jt1.name and jt1.ordered=2;
1333 select * from t1;
1334 name color price instock
1335 Laptop black 20000.00 1
1336 Jacket brown 5000.00 1
1337 Jeans blue 5000.00 1
1338 explain update t1
1339 SET t1.instock=2 where t1.name in (
1340 select jt1.name from json_table(@json, '$[*]'
1341 columns(
1342 name varchar(10) path '$.name',
1343 color varchar(10) path '$.color',
1344 price decimal(8,2) path '$.price',
1345 ordered boolean path '$.ordered' )
1346 ) as jt1);
1347 id select_type table type possible_keys key key_len ref rows Extra
1348 1 PRIMARY t1 ALL NULL NULL NULL NULL 3
1349 1 PRIMARY jt1 ALL NULL NULL NULL NULL 40 Table function: json_table; Using where; FirstMatch(t1)
1350 update t1
1351 SET t1.instock=2 where t1.name in (
1352 select jt1.name from json_table(@json, '$[*]'
1353 columns(
1354 name varchar(10) path '$.name',
1355 color varchar(10) path '$.color',
1356 price decimal(8,2) path '$.price',
1357 ordered boolean path '$.ordered' )
1358 ) as jt1);
1359 select * from t1;
1360 name color price instock
1361 Laptop black 20000.00 2
1362 Jacket brown 5000.00 1
1363 Jeans blue 5000.00 2
1364 update t1, JSON_TABLE(@json,'$[*]'
1365 COLUMNS (
1366 name varchar(10) path '$.name',
1367 color varchar(10) path '$.color',
1368 price decimal(8,2) path '$.price',
1369 ordered boolean path '$.ordered'
1370 )) AS jt1
1371 SET t1.instock=0, jt1.ordered=1 where t1.name=jt1.name;
1372 ERROR HY000: The target table jt1 of the UPDATE is not updatable
1373 select * from t1;
1374 name color price instock
1375 Laptop black 20000.00 2
1376 Jacket brown 5000.00 1
1377 Jeans blue 5000.00 2
1378 drop table t1;
1379 # End of 11.0 tests
1380 ALTER DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_ai_ci;