create or replace table t1(a json);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`a`))
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
create or replace table t1(a json character set utf8);
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 'character set utf8)' at line 1
create or replace table t1(a json default '{a:1}');
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '{a:1}' CHECK (json_valid(`a`))
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
create or replace table t1(a json not null check (json_valid(a)));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`a`))
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
insert t1 values ('[]');
insert t1 values ('a');
ERROR 23000: CONSTRAINT `t1.a` failed for `test`.`t1`
create or replace table t1(a json not null);
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`a`))
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
insert t1 values ('[]');
insert t1 values ('a');
ERROR 23000: CONSTRAINT `t1.a` failed for `test`.`t1`
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;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT json_object('now',current_timestamp()) CHECK (json_valid(`a`))
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
insert t1 values ();
select * from t1;
a
{"now": "2010-11-12 13:14:15"}
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;
t
"foo"
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `t` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`t`))
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
show create table t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `a` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`a`)),
  `t` varchar(38) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
show create table t3;
Table	Create Table
t3	CREATE TABLE `t3` (
  `t` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`t`))
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
drop table t1,t2,t3;
create table t1 (t json check (length(t) > 0));
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `t` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (octet_length(`t`) > 0)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
drop table t1;
create table t1 (t text) engine=myisam;
insert into t1 values ("{}"),("");
create table t2 (t json) select t from t1;
ERROR 23000: CONSTRAINT `t2.t` failed for `test`.`t2`
select * from t2;
ERROR 42S02: Table 'test.t2' doesn't exist
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 ("{}");
insert into t1 values ("xxx");
ERROR 23000: CONSTRAINT `t1.a` failed for `test`.`t1`
select * from t1;
a
{"now": 1}
{}
show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT json_object('now',1) CHECK (json_valid(`a`))
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
drop table t1;
select cast('{a:1}' as text);
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 'text)' at line 1
select cast('{a:1}' as json);
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 'json)' at line 1
#
# Start of 10.5 tests
#
#
# MDEV-17832 Protocol: extensions for Pluggable types and JSON, GEOMETRY
#
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;
SELECT * FROM t1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def	test	t1	t1	js0	js0	252 (format=json)	4294967295	0	Y	144	0	33
def	test	t1	t1	js1	js1	252 (format=json)	196605	0	Y	16	0	33
def	test	t1	t1	js2	js2	252 (format=json)	196605	0	Y	16	0	33
def	test	t1	t1	js3	js3	252	196605	0	Y	16	0	33
js0	js1	js2	js3
SELECT js0, JSON_COMPACT(js0), JSON_COMPACT('{}') FROM t1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def	test	t1	t1	js0	js0	252 (format=json)	4294967295	0	Y	144	0	33
def					JSON_COMPACT(js0)	251 (format=json)	4294967295	0	Y	128	0	33
def					JSON_COMPACT('{}')	253 (format=json)	6	0	Y	0	0	33
js0	JSON_COMPACT(js0)	JSON_COMPACT('{}')
DROP TABLE t1;
#
# MDEV-27361 Hybrid functions with JSON arguments do not send format metadata
#
CREATE TABLE t1 (a JSON);
INSERT INTO t1 VALUES ('{"a":"b"}');
SELECT a, JSON_COMPACT(a), COALESCE(a) FROM t1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def	test	t1	t1	a	a	252 (format=json)	4294967295	9	Y	144	0	33
def					JSON_COMPACT(a)	251 (format=json)	4294967295	9	Y	128	0	33
def					COALESCE(a)	251 (format=json)	4294967295	9	Y	128	39	33
a	JSON_COMPACT(a)	COALESCE(a)
{"a":"b"}	{"a":"b"}	{"a":"b"}
SELECT JSON_ARRAYAGG(1), JSON_ARRAYAGG(a) FROM t1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					JSON_ARRAYAGG(1)	252 (format=json)	9437184	3	Y	0	0	33
def					JSON_ARRAYAGG(a)	252 (format=json)	12582912	11	Y	128	0	33
JSON_ARRAYAGG(1)	JSON_ARRAYAGG(a)
[1]	[{"a":"b"}]
SELECT JSON_OBJECTAGG('a','b'), JSON_OBJECTAGG('a',a) FROM t1;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					JSON_OBJECTAGG('a','b')	252 (format=json)	9437184	9	Y	0	0	33
def					JSON_OBJECTAGG('a',a)	252 (format=json)	12582912	15	Y	128	0	33
JSON_OBJECTAGG('a','b')	JSON_OBJECTAGG('a',a)
{"a":"b"}	{"a":{"a":"b"}}
DROP TABLE t1;
#
# MDEV-27018 IF and COALESCE lose "json" property
#
SELECT json_object('a', (SELECT json_objectagg(b, c) FROM (SELECT 'b','c') d)) AS j FROM DUAL;
Catalog	Database	Table	Table_alias	Column	Column_alias	Type	Length	Max length	Is_null	Flags	Decimals	Charsetnr
def					j	250 (format=json)	9437283	16	Y	0	39	33
j
{"a": {"b":"c"}}
#
# MDEV-26506 Over-quoted JSON when combining JSON_ARRAYAGG with JSON_OBJECT
#
# 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;
json_object('attr2',o)
{"attr2": [{"attr1": "root"}]}
drop table t1;
create view v1 as select json_object(_latin1 'a', _latin1'b') as v1_json;
select v1_json from v1;
v1_json
{"a": "b"}
select json_arrayagg(v1_json) from v1;
json_arrayagg(v1_json)
[{"a": "b"}]
drop view v1;
#
# End of 10.5 tests
#