1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
|
#
# MDEV-9144 JSON data type
#
create or replace table t1(a json);
show create table t1;
--error ER_PARSE_ERROR
create or replace table t1(a json character set utf8);
create or replace table t1(a json default '{a:1}');
show create table t1;
create or replace table t1(a json not null check (json_valid(a)));
show create table t1;
insert t1 values ('[]');
--error ER_CONSTRAINT_FAILED
insert t1 values ('a');
create or replace table t1(a json not null);
show create table t1;
insert t1 values ('[]');
--error ER_CONSTRAINT_FAILED
insert t1 values ('a');
set timestamp=unix_timestamp('2010:11:12 13:14:15');
create or replace table t1(a json default(json_object('now', now())));
show create table t1;
insert t1 values ();
select * from t1;
drop table t1;
create table t1 (t json) as select json_quote('foo') as t;
create table t2 (a json) as select json_quote('foo') as t;
create table t3 like t1;
select * from t1;
show create table t1;
show create table t2;
show create table t3;
drop table t1,t2,t3;
create table t1 (t json check (length(t) > 0));
show create table t1;
drop table t1;
create table t1 (t text) engine=myisam;
insert into t1 values ("{}"),("");
--error ER_CONSTRAINT_FAILED
create table t2 (t json) select t from t1;
--error ER_NO_SUCH_TABLE
select * from t2;
drop table t1;
create or replace table t1(a json default(json_object('now', 1)) check (json_valid(a)));
insert into t1 values ();
insert into t1 values ("{}");
--error ER_CONSTRAINT_FAILED
insert into t1 values ("xxx");
select * from t1;
show create table t1;
drop table t1;
--error ER_PARSE_ERROR
select cast('{a:1}' as text);
--error ER_PARSE_ERROR
select cast('{a:1}' as json);
--echo #
--echo # Start of 10.5 tests
--echo #
--echo #
--echo # MDEV-17832 Protocol: extensions for Pluggable types and JSON, GEOMETRY
--echo #
SET NAMES utf8;
CREATE TABLE t1 (
js0 JSON,
js1 TEXT CHECK (JSON_VALID(js1)),
js2 TEXT CHECK (LENGTH(js2) > 0 AND JSON_VALID(js2)),
js3 TEXT CHECK (LENGTH(js2) > 0 OR JSON_VALID(js2))
) CHARACTER SET utf8;
--disable_view_protocol
--disable_ps_protocol
--enable_metadata
SELECT * FROM t1;
SELECT js0, JSON_COMPACT(js0), JSON_COMPACT('{}') FROM t1;
--disable_metadata
--enable_ps_protocol
--enable_view_protocol
DROP TABLE t1;
--echo #
--echo # MDEV-27361 Hybrid functions with JSON arguments do not send format metadata
--echo #
CREATE TABLE t1 (a JSON);
INSERT INTO t1 VALUES ('{"a":"b"}');
--disable_view_protocol
--disable_ps_protocol
--enable_metadata
SELECT a, JSON_COMPACT(a), COALESCE(a) FROM t1;
SELECT JSON_ARRAYAGG(1), JSON_ARRAYAGG(a) FROM t1;
SELECT JSON_OBJECTAGG('a','b'), JSON_OBJECTAGG('a',a) FROM t1;
--disable_metadata
--disable_ps_protocol
--enable_view_protocol
DROP TABLE t1;
--echo #
--echo # MDEV-27018 IF and COALESCE lose "json" property
--echo #
--disable_view_protocol
--disable_ps_protocol
--enable_metadata
SELECT json_object('a', (SELECT json_objectagg(b, c) FROM (SELECT 'b','c') d)) AS j FROM DUAL;
--disable_metadata
--disable_ps_protocol
--enable_view_protocol
--echo #
--echo # MDEV-26506 Over-quoted JSON when combining JSON_ARRAYAGG with JSON_OBJECT
--echo #
--echo # maintain JSON property through internal temporary tables
create table t1 (a varchar(30));
insert into t1 values ('root');
select json_object('attr2',o) from (select a, json_arrayagg(json_object('attr1', a)) as o from t1) u;
drop table t1;
create view v1 as select json_object(_latin1 'a', _latin1'b') as v1_json;
select v1_json from v1;
select json_arrayagg(v1_json) from v1;
drop view v1;
--echo #
--echo # End of 10.5 tests
--echo #
|
