Debian Codegraph @

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

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