You've already forked mariadb-columnstore-engine
mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-10-31 18:30:33 +03:00
9fe37d5919
This patch is the columnstore-part of the task. Columnstore wanted to have previous 32 depth, so this patch aims at keeping the compatibility.
158 lines
2.8 KiB
Plaintext
158 lines
2.8 KiB
Plaintext
DROP DATABASE IF EXISTS json_valid_db;
|
|
CREATE DATABASE json_valid_db;
|
|
USE json_valid_db;
|
|
# ----------------------------------------------------------------------
|
|
# Test of JSON_VALID function.
|
|
# ----------------------------------------------------------------------
|
|
#
|
|
# String literal - valid JSON
|
|
#
|
|
CREATE TABLE t1(l LONGTEXT) ENGINE = COLUMNSTORE;
|
|
INSERT INTO t1 VALUES ('123');
|
|
INSERT INTO t1 VALUES ('-123');
|
|
INSERT INTO t1 VALUES ('5000000000');
|
|
INSERT INTO t1 VALUES ('-5000000000');
|
|
INSERT INTO t1 VALUES ('1.23');
|
|
INSERT INTO t1 VALUES ('"123"');
|
|
INSERT INTO t1 VALUES ('true');
|
|
INSERT INTO t1 VALUES ('false');
|
|
INSERT INTO t1 VALUES ('null');
|
|
INSERT INTO t1 VALUES ('{"address": "Trondheim"}');
|
|
INSERT INTO t1 VALUES (JSON_OBJECT());
|
|
INSERT INTO t1 VALUES (JSON_OBJECT(1, 2));
|
|
INSERT INTO t1 VALUES (JSON_ARRAY());
|
|
INSERT INTO t1 VALUES (JSON_ARRAY(1, 2));
|
|
SELECT
|
|
l AS raw,
|
|
JSON_VALID(l) AS is_valid,
|
|
JSON_VALID(JSON_COMPACT(l)) AS compact
|
|
FROM
|
|
t1;
|
|
raw is_valid compact
|
|
123 1 1
|
|
-123 1 1
|
|
5000000000 1 1
|
|
-5000000000 1 1
|
|
1.23 1 1
|
|
"123" 1 1
|
|
true 1 1
|
|
false 1 1
|
|
null 1 1
|
|
{"address": "Trondheim"} 1 1
|
|
{} 1 1
|
|
{"1": 2} 1 1
|
|
[] 1 1
|
|
[1, 2] 1 1
|
|
#
|
|
# String literal - invalid JSON
|
|
#
|
|
TRUNCATE t1;
|
|
INSERT INTO t1 VALUES ('12 3');
|
|
INSERT INTO t1 VALUES ('{key:value}');
|
|
INSERT INTO t1 VALUES ('{key:value');
|
|
INSERT INTO t1 VALUES ('[1,2,]');
|
|
INSERT INTO t1 VALUES ('[1,2');
|
|
SELECT
|
|
l AS raw,
|
|
JSON_VALID(l) AS is_valid,
|
|
JSON_VALID(JSON_COMPACT(l)) AS compact
|
|
FROM
|
|
t1;
|
|
raw is_valid compact
|
|
12 3 0 NULL
|
|
{key:value} 0 NULL
|
|
{key:value 0 NULL
|
|
[1,2,] 0 NULL
|
|
[1,2 0 NULL
|
|
#
|
|
# String literal - not in UTF-8
|
|
#
|
|
TRUNCATE t1;
|
|
SET NAMES 'ascii';
|
|
INSERT INTO t1 VALUES ('123');
|
|
SELECT
|
|
l AS raw,
|
|
JSON_VALID(l) AS is_valid,
|
|
JSON_VALID(JSON_COMPACT(l)) AS compact
|
|
FROM
|
|
t1;
|
|
raw is_valid compact
|
|
123 1 1
|
|
SET NAMES 'utf8';
|
|
#
|
|
# Bare NULL
|
|
#
|
|
TRUNCATE t1;
|
|
INSERT INTO t1 VALUES (NULL);
|
|
SELECT
|
|
JSON_VALID(l)
|
|
FROM
|
|
t1;
|
|
JSON_VALID(l)
|
|
NULL
|
|
#
|
|
# Function result - string
|
|
#
|
|
TRUNCATE t1;
|
|
INSERT INTO t1 VALUES (UPPER('"abc"'));
|
|
SELECT
|
|
JSON_VALID(l)
|
|
FROM
|
|
t1;
|
|
JSON_VALID(l)
|
|
1
|
|
#
|
|
# Function result - string not in UTF-8
|
|
#
|
|
TRUNCATE t1;
|
|
SET NAMES 'latin1';
|
|
INSERT INTO t1 VALUES (UPPER('"abc"'));
|
|
SELECT
|
|
JSON_VALID(l)
|
|
FROM
|
|
t1;
|
|
JSON_VALID(l)
|
|
1
|
|
SET NAMES 'utf8';
|
|
#
|
|
# Function result - date, not valid as JSON without CAST
|
|
#
|
|
TRUNCATE t1;
|
|
INSERT INTO t1 VALUES (CAST('2015-01-15' AS DATE));
|
|
SELECT
|
|
JSON_VALID(l)
|
|
FROM
|
|
t1;
|
|
JSON_VALID(l)
|
|
0
|
|
#
|
|
# The date string doesn't parse as JSON text, so wrong:
|
|
#
|
|
TRUNCATE t1;
|
|
INSERT INTO t1 VALUES (
|
|
CAST(
|
|
CAST('2015-01-15' AS DATE) AS CHAR CHARACTER SET 'utf8'
|
|
)
|
|
);
|
|
SELECT
|
|
JSON_VALID(l)
|
|
FROM
|
|
t1;
|
|
JSON_VALID(l)
|
|
0
|
|
#
|
|
# Function result - NULL
|
|
#
|
|
TRUNCATE t1;
|
|
INSERT INTO t1 VALUES (UPPER(NULL));
|
|
INSERT INTO t1 VALUES (UPPER(CAST(NULL AS CHAR)));
|
|
SELECT
|
|
JSON_VALID(l)
|
|
FROM
|
|
t1;
|
|
JSON_VALID(l)
|
|
NULL
|
|
NULL
|
|
DROP TABLE t1;
|
|
DROP DATABASE json_valid_db;
|