# Use at least 16k page size for this test. With smaller page sizes,
# the maximum key length is too small for strings returned by
# JSON_VALUE with an implicit RETURNING clause (which returns
# CHAR(512) with utf8mb4 encoding).
--source include/have_innodb_min_16k.inc

--echo #
--echo # WL#12228: Add JSON_VALUE function
--echo #

# Enable display of metadata to verify that the RETURNING clause is respected.
--enable_metadata

SELECT JSON_VALUE('{"data": "asdf"}', '$.data') v;
SELECT JSON_VALUE('{"data": 123.45}', '$.data') v;
SELECT JSON_VALUE('{"data": "asdf"}', '$.data' RETURNING CHAR) v;
SELECT JSON_VALUE('{"data": 123.45}', '$.data' RETURNING CHAR) v;
SELECT JSON_VALUE('{"data": 123.45}', '$.data' RETURNING DECIMAL) v;
SELECT JSON_VALUE('{"data": 123.45}', '$.data' RETURNING DECIMAL(6,4)) v;
SELECT JSON_VALUE('{"data": "asdf"}', '$.data' RETURNING BINARY(4)) v;
SELECT JSON_VALUE('{"data": "asdf"}', '$.data' RETURNING BINARY) v;

# PS protocol gives different metadata for the "Max length" column for
# integer and floating-point types.
--disable_ps_protocol
SELECT JSON_VALUE('{"data": 123.45}', '$.data' RETURNING FLOAT) v;
SELECT JSON_VALUE('{"data": 123.45}', '$.data' RETURNING DOUBLE) v;
SELECT JSON_VALUE('{"data": 123.45}', '$.data' RETURNING UNSIGNED) v;
SELECT JSON_VALUE('{"data": -123.45}','$.data' RETURNING UNSIGNED) v;
SELECT JSON_VALUE('{"data": 123.45}', '$.data' RETURNING SIGNED) v;
SELECT JSON_VALUE('{"data": -123.45}', '$.data' RETURNING SIGNED) v;
SELECT JSON_VALUE('{"data": 1999}', '$.data' RETURNING YEAR) v;
SELECT JSON_VALUE('{"data": -1999}', '$.data' RETURNING YEAR) v;
SELECT JSON_VALUE(JSON_OBJECT('data', 1.0e0/3), '$.data' RETURNING FLOAT) v;
SELECT JSON_VALUE(JSON_OBJECT('data', 1.0e0/3), '$.data' RETURNING DOUBLE) v;
--enable_ps_protocol

# For character types, if no character set or collation is specified,
# it should use the default character set and collation of the JSON
# data type.
SELECT COLLATION(JSON_VALUE('{"x": "y"}', '$.x')) AS col1,
       COLLATION(JSON_VALUE('{"x": "y"}', '$.x' RETURNING CHAR(10))) AS col2,
       COLLATION(JSON_VALUE('{"x": "y"}',
                            '$.x' RETURNING CHAR(10) CHARSET utf8mb4)) AS col3;

SELECT JSON_VALUE('{"data": "2019-01-01"}', '$.data' RETURNING DATE);
SELECT JSON_VALUE(JSON_OBJECT('data', CAST( DATE'2019-01-01' AS JSON)),
  '$.data' RETURNING DATE);

# PS protocol gives different metadata for the "Max length" column for
# TIME and DATETIME.
--disable_ps_protocol
SELECT JSON_VALUE('{"data": "11:11:11"}', '$.data' RETURNING TIME);
SELECT JSON_VALUE(JSON_OBJECT('data', CAST( TIME'11:11:11' AS JSON)),
  '$.data' RETURNING TIME);

SELECT JSON_VALUE('{"data": "2019-01-01 11:11:11"}',
  '$.data' RETURNING DATETIME);
SELECT JSON_VALUE(
  JSON_OBJECT('data', CAST(TIMESTAMP'2019-01-01 11:11:11' AS JSON)),
  '$.data' RETURNING DATETIME);
--enable_ps_protocol

SELECT JSON_VALUE('{"data": 123.45}', '$.data' RETURNING CHAR(10));
SELECT JSON_VALUE('{"data": "1234567890"}', '$.data' RETURNING CHAR(10));
SELECT JSON_VALUE('{"data": "12345678901"}', '$.data' RETURNING CHAR(10));
SELECT JSON_VALUE('{"data": "1234567890"}',
  '$.data' RETURNING CHAR(10) CHARSET utf8mb4);
SELECT JSON_VALUE('{"data": "123"}', '$.data' RETURNING JSON);
--disable_metadata

--error ER_PARSE_ERROR
SELECT JSON_VALUE('{}', NULL);
--error ER_PARSE_ERROR
SELECT JSON_VALUE('{}', '$.x' DEFAULT NULL ON EMPTY);
--error ER_PARSE_ERROR
SELECT JSON_VALUE('{}', '$.x' DEFAULT NULL ON ERROR);
--error ER_INVALID_TYPE_FOR_JSON
SELECT JSON_VALUE(1, '$');
--error ER_INVALID_JSON_CHARSET
SELECT JSON_VALUE(CAST('[]' AS BINARY), '$');

SELECT JSON_VALUE('{"data": [123]}', '$.data' RETURNING JSON) v;
SELECT JSON_VALUE('{"data": {"val":123}}', '$.data' RETURNING JSON) v;
SELECT JSON_VALUE('[1,2,3]', '$') v;
SELECT JSON_VALUE('{"a":"b"}', '$') v;
--error ER_TOO_BIG_PRECISION
SELECT JSON_VALUE('1', '$' RETURNING TIME(1000));

SELECT JSON_VALUE('{"data": [123]}', '$.data' RETURNING SIGNED) v;
SELECT JSON_VALUE('{"data": {"x":"y"}}', '$.data' RETURNING SIGNED) v;
SELECT JSON_VALUE('{"data": [123]}',
                  '$.data' RETURNING SIGNED DEFAULT 456 ON ERROR) v;

--echo # Wildcards are OK if they match a single value.
SELECT JSON_VALUE('["abc"]', '$[*]') v1,
       JSON_VALUE('[1]', '$[*]' RETURNING SIGNED) v2,
       JSON_VALUE('[1]', '$[*]' RETURNING UNSIGNED) v3,
       JSON_VALUE('["2020-01-01"]', '$[*]' RETURNING DATE) v4,
       JSON_VALUE('["12:00:00"]', '$[*]' RETURNING TIME) v5,
       JSON_VALUE('["2020-01-01 12:00:00"]', '$[*]' RETURNING DATETIME) v6,
       JSON_VALUE('[1]', '$[*]' RETURNING DECIMAL) v7,
       JSON_VALUE('[{}]', '$[*]' RETURNING JSON) v8,
       JSON_VALUE('[1]', '$[*]' RETURNING FLOAT) v9,
       JSON_VALUE('[1]', '$[*]' RETURNING DOUBLE) v10;

--echo # Paths matching multiple values should invoke ON ERROR.
SELECT JSON_VALUE('["abc", "def"]', '$[*]') v1,
       JSON_VALUE('[1, 2]', '$[*]' RETURNING SIGNED) v2,
       JSON_VALUE('[1, 2]', '$[*]' RETURNING UNSIGNED) v3,
       JSON_VALUE('["2020-01-01", "2020-01-02"]', '$[*]' RETURNING DATE) v4,
       JSON_VALUE('["12:00:00", "13:00:00"]', '$[*]' RETURNING TIME) v5,
       JSON_VALUE('["2020-01-01 12:00:00", "2020-01-01 12:00:00"]',
                  '$[*]' RETURNING DATETIME) v6,
       JSON_VALUE('[1, 2]', '$[*]' RETURNING DECIMAL) v7,
       JSON_VALUE('[{}, []]', '$[*]' RETURNING JSON) v8,
       JSON_VALUE('[1, 2]', '$[*]' RETURNING FLOAT) v9,
       JSON_VALUE('[1, 2]', '$[*]' RETURNING DOUBLE) v10;
SELECT JSON_VALUE('["abc", "def"]', '$[*]' DEFAULT 'error' ON ERROR) v1,
       JSON_VALUE('[1, 2]', '$[*]' RETURNING SIGNED DEFAULT 9 ON ERROR) v2,
       JSON_VALUE('[1, 2]', '$[*]' RETURNING UNSIGNED DEFAULT 9 ON ERROR) v3,
       JSON_VALUE('["2020-01-01", "2020-01-02"]',
                  '$[*]' RETURNING DATE DEFAULT DATE'1999-12-31' ON ERROR) v4,
       JSON_VALUE('["12:00:00", "13:00:00"]',
                  '$[*]' RETURNING TIME DEFAULT TIME'10:20:30' ON ERROR) v5,
       JSON_VALUE('["2020-01-01 12:00:00", "2020-01-01 12:00:00"]',
                  '$[*]' RETURNING DATETIME
                  DEFAULT '1999-12-31 10:20:30' ON ERROR) v6,
       JSON_VALUE('[1, 2]', '$[*]' RETURNING DECIMAL DEFAULT 9 ON ERROR) v7,
       JSON_VALUE('[{}, []]',
                  '$[*]' RETURNING JSON DEFAULT '"error"' ON ERROR) v8,
       JSON_VALUE('[1, 2]', '$[*]' RETURNING FLOAT DEFAULT 9 ON ERROR) v9,
       JSON_VALUE('[1, 2]', '$[*]' RETURNING DOUBLE DEFAULT 9 ON ERROR) v10;
--error ER_MULTIPLE_JSON_VALUES
SELECT JSON_VALUE('["abc", "def"]', '$[*]' ERROR ON ERROR) v;
--error ER_MULTIPLE_JSON_VALUES
SELECT JSON_VALUE('[1, 2]', '$[*]' RETURNING SIGNED ERROR ON ERROR) v;
--error ER_MULTIPLE_JSON_VALUES
SELECT JSON_VALUE('[1, 2]', '$[*]' RETURNING UNSIGNED ERROR ON ERROR) v;
--error ER_MULTIPLE_JSON_VALUES
SELECT JSON_VALUE('["2020-01-01", "2020-01-02"]',
                  '$[*]' RETURNING DATE ERROR ON ERROR) v;
--error ER_MULTIPLE_JSON_VALUES
SELECT JSON_VALUE('["12:00:00", "13:00:00"]',
                  '$[*]' RETURNING TIME ERROR ON ERROR) v;
--error ER_MULTIPLE_JSON_VALUES
SELECT JSON_VALUE('["2020-01-01 12:00:00", "2020-01-01 12:00:00"]',
                  '$[*]' RETURNING DATETIME ERROR ON ERROR) v;
--error ER_MULTIPLE_JSON_VALUES
SELECT JSON_VALUE('[1, 2]', '$[*]' RETURNING DECIMAL ERROR ON ERROR) v;
--error ER_MULTIPLE_JSON_VALUES
SELECT JSON_VALUE('[{}, []]', '$[*]' RETURNING JSON ERROR ON ERROR) v;
--error ER_MULTIPLE_JSON_VALUES
SELECT JSON_VALUE('[1, 2]', '$[*]' RETURNING FLOAT ERROR ON ERROR) v;
--error ER_MULTIPLE_JSON_VALUES
SELECT JSON_VALUE('[1, 2]', '$[*]' RETURNING DOUBLE ERROR ON ERROR) v;

--echo # Max length check should use char length, not byte length.
SELECT JSON_VALUE('"æøå"', '$' RETURNING CHAR(3)) v;
SELECT JSON_VALUE('"æøåa"', '$' RETURNING CHAR(3) DEFAULT 'ÆØÅ' ON ERROR) v;

--echo # Charset conversion errors should activate the ON ERROR clause.
SELECT JSON_VALUE('"æøå"', '$' RETURNING CHAR(10) CHARSET ascii
                               DEFAULT 'error' ON ERROR) v;

# Allow NULL for all target types.
SELECT JSON_VALUE(NULL, '$.x') v1,
       JSON_VALUE(NULL, '$.x' RETURNING SIGNED) v2,
       JSON_VALUE(NULL, '$.x' RETURNING UNSIGNED) v3,
       JSON_VALUE(NULL, '$.x' RETURNING DATE) v4,
       JSON_VALUE(NULL, '$.x' RETURNING TIME) v5,
       JSON_VALUE(NULL, '$.x' RETURNING DATETIME) v6,
       JSON_VALUE(NULL, '$.x' RETURNING DECIMAL) v7,
       JSON_VALUE(NULL, '$.x' RETURNING JSON) v8,
       JSON_VALUE(NULL, '$.x' RETURNING FLOAT) v9,
       JSON_VALUE(NULL, '$.x' RETURNING DOUBLE) v10,
       JSON_VALUE(NULL, '$.x' RETURNING YEAR) v11;

# Check JSON parse errors for all target types.
SELECT JSON_VALUE('not json', '$.x') v1,
       JSON_VALUE('not json', '$.x' RETURNING SIGNED) v2,
       JSON_VALUE('not json', '$.x' RETURNING UNSIGNED) v3,
       JSON_VALUE('not json', '$.x' RETURNING DATE) v4,
       JSON_VALUE('not json', '$.x' RETURNING TIME) v5,
       JSON_VALUE('not json', '$.x' RETURNING DATETIME) v6,
       JSON_VALUE('not json', '$.x' RETURNING DECIMAL) v7,
       JSON_VALUE('not json', '$.x' RETURNING JSON) v8,
       JSON_VALUE('not json', '$.x' RETURNING FLOAT) v9,
       JSON_VALUE('not json', '$.x' RETURNING DOUBLE) v10,
       JSON_VALUE('not json', '$.x' RETURNING YEAR) v11;
SELECT JSON_VALUE('not json', '$.x') v1,
       JSON_VALUE('not json', '$.x' RETURNING SIGNED NULL ON ERROR) v2,
       JSON_VALUE('not json', '$.x' RETURNING UNSIGNED NULL ON ERROR) v3,
       JSON_VALUE('not json', '$.x' RETURNING DATE NULL ON ERROR) v4,
       JSON_VALUE('not json', '$.x' RETURNING TIME NULL ON ERROR) v5,
       JSON_VALUE('not json', '$.x' RETURNING DATETIME NULL ON ERROR) v6,
       JSON_VALUE('not json', '$.x' RETURNING DECIMAL NULL ON ERROR) v7,
       JSON_VALUE('not json', '$.x' RETURNING JSON NULL ON ERROR) v8,
       JSON_VALUE('not json', '$.x' RETURNING FLOAT NULL ON ERROR) v9,
       JSON_VALUE('not json', '$.x' RETURNING DOUBLE NULL ON ERROR) v10,
       JSON_VALUE('not json', '$.x' RETURNING YEAR NULL ON ERROR) v11;
SELECT JSON_VALUE('not json', '$.x' DEFAULT 'error' ON ERROR) v1,
       JSON_VALUE('not json', '$.x' RETURNING SIGNED DEFAULT 123 ON ERROR) v2,
       JSON_VALUE('not json', '$.x' RETURNING UNSIGNED DEFAULT 123 ON ERROR) v3,
       JSON_VALUE('not json', '$.x' RETURNING DATE
                                    DEFAULT '2020-01-01' ON ERROR) v4,
       JSON_VALUE('not json', '$.x' RETURNING TIME
                                    DEFAULT '10:11:12' ON ERROR) v5,
       JSON_VALUE('not json', '$.x' RETURNING DATETIME
                                    DEFAULT '2020-01-01 12:13:14' ON ERROR) v6,
       JSON_VALUE('not json', '$.x' RETURNING DECIMAL DEFAULT 123 ON ERROR) v7,
       JSON_VALUE('not json', '$.x' RETURNING JSON
                                    DEFAULT '[1,2,3]' ON ERROR) v8,
       JSON_VALUE('not json', '$.x' RETURNING FLOAT DEFAULT 123 ON ERROR) v9,
       JSON_VALUE('not json', '$.x' RETURNING DOUBLE DEFAULT 123 ON ERROR) v10,
       JSON_VALUE('not json', '$.x' RETURNING YEAR DEFAULT 1988 ON ERROR) v11;
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT JSON_VALUE('not json', '$.x' ERROR ON EMPTY ERROR ON ERROR) v;
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT JSON_VALUE('not json', '$.x' RETURNING SIGNED
                                    ERROR ON EMPTY ERROR ON ERROR) v;
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT JSON_VALUE('not json', '$.x' RETURNING UNSIGNED
                                    ERROR ON EMPTY ERROR ON ERROR) v;
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT JSON_VALUE('not json', '$.x' RETURNING DATE
                                    ERROR ON EMPTY ERROR ON ERROR) v;
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT JSON_VALUE('not json', '$.x' RETURNING TIME
                                    ERROR ON EMPTY ERROR ON ERROR) v;
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT JSON_VALUE('not json', '$.x' RETURNING DATETIME
                                    ERROR ON EMPTY ERROR ON ERROR) v;
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT JSON_VALUE('not json', '$.x' RETURNING DECIMAL
                                    ERROR ON EMPTY ERROR ON ERROR) v;
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT JSON_VALUE('not json', '$.x' RETURNING JSON
                                    ERROR ON EMPTY ERROR ON ERROR) v;
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT JSON_VALUE('not json', '$.x' RETURNING FLOAT
                                    ERROR ON EMPTY ERROR ON ERROR) v;
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT JSON_VALUE('not json', '$.x' RETURNING DOUBLE
                                    ERROR ON EMPTY ERROR ON ERROR) v;
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT JSON_VALUE('not json', '$.x' RETURNING YEAR
                                    ERROR ON EMPTY ERROR ON ERROR) v;

# If the syntax error is raised in a nested function call, the ON
# ERROR clause is not invoked. It is only invoked when it is
# JSON_VALUE itself that cannot parse the string.
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT JSON_VALUE(CAST(JSON_EXTRACT('not json', '$') AS CHAR), '$') v;

# If the document is too deep for the parser, raise an error instead
# of invoking the ON ERROR clause.
--error ER_JSON_DOCUMENT_TOO_DEEP
SELECT JSON_VALUE(REPEAT('[', 200), '$') v;

# Verify that JSON_VALUE expressions are correctly represented in VIEWs.
CREATE VIEW v AS
  SELECT JSON_VALUE('{"don''t":"panic"}', '$."don''t"') AS x,
         JSON_VALUE('""', '$' RETURNING CHAR(10)
                              DEFAULT 'it''s empty' ON EMPTY
                              DEFAULT 'it''s wrong' ON ERROR) AS y,
         JSON_VALUE('""', '$' NULL ON EMPTY ERROR ON ERROR) AS z,
         JSON_VALUE('""', '$' ERROR ON EMPTY NULL ON ERROR) AS w;
SHOW CREATE VIEW v;
SELECT * FROM v;
DROP VIEW v;

# JSON_VALUE is a reserved word in the standard. It is non-reserved in
# MySQL for now.
CREATE TABLE json_value(json_value JSON);
SELECT JSON_VALUE(json_value, '$.a') AS json_value FROM json_value;
DROP TABLE json_value;

# RETURNING is a non-reserved word both in the standard and in MySQL.
CREATE TABLE returning(returning JSON);
SELECT JSON_VALUE(returning, '$.a' RETURNING CHAR) AS returning FROM returning;
DROP TABLE returning;

--echo # Test ON EMPTY clause
SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING SIGNED) v;
SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING SIGNED NULL ON EMPTY) v;
--error ER_MISSING_JSON_VALUE
SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING SIGNED ERROR ON EMPTY);
SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING SIGNED
  DEFAULT 456 ON EMPTY);
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING SIGNED
  DEFAULT '[456]' ON EMPTY);
--error ER_PARSE_ERROR
SELECT JSON_VALUE('{"data": 123}',
                  '$.num' RETURNING SIGNED NULL ON EMPTY NULL ON EMPTY);
SELECT JSON_VALUE('{}', '$.x' RETURNING CHAR(10) CHARSET utf16
                              DEFAULT 'abc' ON EMPTY) v;
SELECT JSON_VALUE('{}', '$.x' RETURNING CHAR(10) CHARSET ascii
                              DEFAULT 'abc' ON EMPTY) v;
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('{}', '$.x' RETURNING CHAR(10) CHARSET ascii
                              DEFAULT 'æøå' ON EMPTY) v;

--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING SIGNED
  DEFAULT 'asdf' ON EMPTY);
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING UNSIGNED
  DEFAULT 'asdf' ON EMPTY);
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING CHAR(2)
  DEFAULT 'asdf' ON EMPTY);
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING FLOAT
  DEFAULT 'asdf' ON EMPTY);
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING DOUBLE
  DEFAULT 'asdf' ON EMPTY);
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING DATE
  DEFAULT 'asdf' ON EMPTY);
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING TIME
  DEFAULT 'asdf' ON EMPTY);
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING DATETIME
  DEFAULT 'asdf' ON EMPTY);
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING DECIMAL
  DEFAULT 'asdf' ON EMPTY);
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{"data": 123}', '$.num' RETURNING YEAR
  DEFAULT 'asdf' ON EMPTY);
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT JSON_VALUE('""', '$' RETURNING JSON DEFAULT 'not JSON' ON EMPTY);

--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{"data": 123}', '$.data' RETURNING SIGNED
  DEFAULT 'asdf' ON ERROR);
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{"data": 123}', '$.data' RETURNING UNSIGNED
  DEFAULT 'asdf' ON ERROR);
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('{"data": 123}', '$.data' RETURNING CHAR(2)
  DEFAULT 'asdf' ON ERROR);
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{"data": 123}', '$.data' RETURNING FLOAT
  DEFAULT 'asdf' ON ERROR);
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{"data": 123}', '$.data' RETURNING DOUBLE
  DEFAULT 'asdf' ON ERROR);
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{"data": 123}', '$.data' RETURNING DATE
  DEFAULT 'asdf' ON ERROR);
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{"data": 123}', '$.data' RETURNING TIME
  DEFAULT 'asdf' ON ERROR);
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{"data": 123}', '$.data' RETURNING DATETIME
  DEFAULT 'asdf' ON ERROR);
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{"data": 123}', '$.data' RETURNING DECIMAL
  DEFAULT 'asdf' ON ERROR);
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{"data": 123}', '$.data' RETURNING YEAR
  DEFAULT 'asdf' ON ERROR);
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT JSON_VALUE('""', '$' RETURNING JSON DEFAULT 'not JSON' ON ERROR);

--echo # Test ON ERROR clause
SELECT JSON_VALUE('{"data": "asd"}', '$.data' RETURNING SIGNED);
SELECT JSON_VALUE('{"data": "asd"}', '$.data' RETURNING SIGNED NULL ON ERROR);
--error ER_PARSE_ERROR
SELECT JSON_VALUE('{"data": "asd"}',
                  '$.data' RETURNING SIGNED NULL ON ERROR NULL ON ERROR);
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT JSON_VALUE('{"data": 123', '$.data' RETURNING SIGNED ERROR ON ERROR);
--error ER_INVALID_JSON_PATH
SELECT JSON_VALUE('{"data": 123}', '.data' RETURNING SIGNED ERROR ON ERROR);
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{"data": "asd"}', '$.data'
  RETURNING SIGNED DEFAULT '{"aaa":321}' ON ERROR);

SELECT JSON_VALUE('{"data": "asd"}', '$.data'
  RETURNING SIGNED DEFAULT 321 ON ERROR);
SELECT JSON_VALUE('{"data": "123.45"}', '$.data' RETURNING SIGNED);
SELECT JSON_VALUE('{"data": "99999999999999999999999999999"}', '$.data'
  RETURNING SIGNED);
SELECT JSON_VALUE('{"data": "-99999"}', '$.data' RETURNING UNSIGNED);
SELECT JSON_VALUE('{"data": "99999999999999999999999999999"}', '$.data'
  RETURNING CHAR(10));
SELECT JSON_VALUE('"abc"', '$' RETURNING DATE DEFAULT '2000-01-02' ON ERROR);
SELECT JSON_VALUE('"abc"', '$' RETURNING DATE
                               DEFAULT DATE'2000-01-02' ON ERROR);
SELECT JSON_VALUE('"abc"', '$' RETURNING TIME DEFAULT '12:13:14' ON ERROR);
SELECT JSON_VALUE('"abc"', '$' RETURNING TIME DEFAULT TIME'12:13:14' ON ERROR);
SELECT JSON_VALUE('"abc"', '$' RETURNING DATETIME
                               DEFAULT '2000-01-02 12:13:14' ON ERROR);
SELECT JSON_VALUE('"abc"', '$' RETURNING DATETIME
                               DEFAULT TIMESTAMP'2000-01-02 12:13:14' ON ERROR);
SELECT JSON_VALUE('"abc"', '$' RETURNING CHAR(1) DEFAULT 'x' ON ERROR);
SELECT JSON_VALUE('"abc"', '$' RETURNING CHAR(1) CHARSET utf16
                               DEFAULT 'x' ON ERROR) v;
SELECT JSON_VALUE('"abc"', '$' RETURNING CHAR(1) CHARSET ascii
                               DEFAULT 'a' ON ERROR) v;
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('"abc"', '$' RETURNING CHAR(1) CHARSET ascii
                               DEFAULT 'æ' ON ERROR) v;
SELECT JSON_VALUE('"abc"', '$' RETURNING DECIMAL DEFAULT 123 ON ERROR);
SELECT JSON_VALUE('"abc"', '$' RETURNING FLOAT DEFAULT 123 ON ERROR);
SELECT JSON_VALUE('"abc"', '$' RETURNING DOUBLE DEFAULT 123 ON ERROR);
SELECT JSON_VALUE('"abc"', '$' RETURNING YEAR DEFAULT 1988 ON ERROR);
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('"abc"', '$' RETURNING YEAR DEFAULT 1 ON ERROR);
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('"abc"', '$' RETURNING YEAR DEFAULT 2270 ON ERROR);
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('"abc"', '$' RETURNING YEAR DEFAULT -1 ON ERROR);

--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('"abc"', '$' RETURNING SIGNED ERROR ON ERROR);
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('-1', '$' RETURNING UNSIGNED ERROR ON ERROR);
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('"abc"', '$' RETURNING DATE ERROR ON ERROR);
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('"abc"', '$' RETURNING TIME ERROR ON ERROR);
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('"abc"', '$' RETURNING DATETIME ERROR ON ERROR);
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('"abc"', '$' RETURNING CHAR(1) ERROR ON ERROR);
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('"abc"', '$' RETURNING DECIMAL ERROR ON ERROR);
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('"abc"', '$' RETURNING FLOAT ERROR ON ERROR);
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('"abc"', '$' RETURNING DOUBLE ERROR ON ERROR);
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('"abc"', '$' RETURNING YEAR ERROR ON ERROR);

SELECT JSON_VALUE('"abc"', '$' RETURNING SIGNED NULL ON ERROR) v1,
       JSON_VALUE('-1', '$' RETURNING UNSIGNED NULL ON ERROR) v2,
       JSON_VALUE('"abc"', '$' RETURNING DATE NULL ON ERROR) v3,
       JSON_VALUE('"abc"', '$' RETURNING TIME NULL ON ERROR) v4,
       JSON_VALUE('"abc"', '$' RETURNING DATETIME NULL ON ERROR) v5,
       JSON_VALUE('"abc"', '$' RETURNING CHAR(1) NULL ON ERROR) v6,
       JSON_VALUE('"abc"', '$' RETURNING DECIMAL NULL ON ERROR) v7,
       JSON_VALUE('"abc"', '$' RETURNING FLOAT NULL ON ERROR) v8,
       JSON_VALUE('"abc"', '$' RETURNING DOUBLE NULL ON ERROR) v9,
       JSON_VALUE('"abc"', '$' RETURNING YEAR NULL ON ERROR) v10;

SELECT JSON_VALUE('"abc"', '$' RETURNING SIGNED) v1,
       JSON_VALUE('-1', '$' RETURNING UNSIGNED) v2,
       JSON_VALUE('"abc"', '$' RETURNING DATE) v3,
       JSON_VALUE('"abc"', '$' RETURNING TIME) v4,
       JSON_VALUE('"abc"', '$' RETURNING DATETIME) v5,
       JSON_VALUE('"abc"', '$' RETURNING CHAR(1)) v6,
       JSON_VALUE('"abc"', '$' RETURNING DECIMAL) v7,
       JSON_VALUE('"abc"', '$' RETURNING FLOAT) v8,
       JSON_VALUE('"abc"', '$' RETURNING DOUBLE) v9,
       JSON_VALUE('"abc"', '$' RETURNING YEAR) v10;

--echo # Test ON EMPTY in combination with ON ERROR.
SELECT JSON_VALUE('{"data": "asd"}',
                  '$.data' RETURNING SIGNED NULL ON EMPTY NULL ON ERROR);
--error ER_PARSE_ERROR
SELECT JSON_VALUE('{"data": "asd"}',
                  '$.data' RETURNING SIGNED NULL ON ERROR NULL ON EMPTY);
--sorted_result
SELECT j, JSON_VALUE(j, '$.data' RETURNING SIGNED DEFAULT 111 ON EMPTY
                                                  DEFAULT 222 ON ERROR) AS jv
  FROM (SELECT '{"data": "asd"}' AS j UNION ALL SELECT '{"num": 123}') t1;
--error ER_INVALID_JSON_TEXT_IN_PARAM
SELECT JSON_VALUE('not json', '$' RETURNING JSON ERROR ON EMPTY ERROR ON ERROR);

--echo # Returning array types is not supported.
--error ER_PARSE_ERROR
SELECT JSON_VALUE('{"data": "asd"}', '$.data' RETURNING SIGNED ARRAY);

--echo # Test defaults within range.
SELECT JSON_VALUE('{}', '$.a' RETURNING SIGNED DEFAULT -123 ON EMPTY) a,
       JSON_VALUE('{}', '$.a' RETURNING SIGNED DEFAULT '-123' ON EMPTY) b,
       JSON_VALUE('{}', '$.a' RETURNING UNSIGNED DEFAULT 123 ON EMPTY) c,
       JSON_VALUE('{}', '$.a' RETURNING UNSIGNED DEFAULT '123' ON EMPTY) d,
       JSON_VALUE('{}', '$.a' RETURNING DATE
                              DEFAULT DATE'2020-04-05' ON EMPTY) e,
       JSON_VALUE('{}', '$.a' RETURNING DATE
                              DEFAULT '2020-04-05' ON EMPTY) f,
       JSON_VALUE('{}', '$.a' RETURNING TIME(6)
                              DEFAULT TIME'09:10:11.22' ON EMPTY) g,
       JSON_VALUE('{}', '$.a' RETURNING TIME(6)
                              DEFAULT '09:10:11.22' ON EMPTY) h,
       JSON_VALUE('{}', '$.a' RETURNING DATETIME(6)
                              DEFAULT '2020-04-05 09:10:11.22' ON EMPTY) i,
       JSON_VALUE('{}', '$.a'
                  RETURNING DATETIME(6)
                  DEFAULT TIMESTAMP'2020-04-05 09:10:11.22' ON EMPTY) j,
       JSON_VALUE('{}', '$.a' DEFAULT 'empty' ON EMPTY) k,
       JSON_VALUE('{}', '$.a' RETURNING DECIMAL(5, 3) DEFAULT 1.23 ON EMPTY) l,
       JSON_VALUE('{}', '$.a' RETURNING DECIMAL(5, 3)
                              DEFAULT '1.23' ON EMPTY) m,
       JSON_VALUE('{}', '$.a' RETURNING JSON DEFAULT '"empty"' ON EMPTY) n,
       JSON_VALUE('{}', '$.a' RETURNING FLOAT DEFAULT -1234.5 ON EMPTY) o,
       JSON_VALUE('{}', '$.a' RETURNING FLOAT DEFAULT '-1234.5' ON EMPTY) p,
       JSON_VALUE('{}', '$.a' RETURNING DOUBLE DEFAULT -1e308 ON EMPTY) q,
       JSON_VALUE('{}', '$.a' RETURNING DOUBLE DEFAULT '-1e308' ON EMPTY) r,
       JSON_VALUE('{}', '$.a' RETURNING YEAR DEFAULT 1901 ON EMPTY) s,
       JSON_VALUE('{}', '$.a' RETURNING YEAR DEFAULT '2155' ON EMPTY) t;

--echo # Test defaults out of range.
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('{}', '$.a' RETURNING SIGNED DEFAULT 9223372036854775808 ON EMPTY) v;
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('{}', '$.a' RETURNING SIGNED DEFAULT '9223372036854775808' ON EMPTY) v;
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('{}', '$.a' RETURNING UNSIGNED DEFAULT -1 ON EMPTY) v;
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('{}', '$.a' RETURNING UNSIGNED DEFAULT '-1' ON EMPTY) v;
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{}', '$.a' RETURNING DATE DEFAULT 'abc' ON EMPTY) v;
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{}', '$.a' RETURNING TIME DEFAULT 'abc' ON EMPTY) v;
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('{}', '$.a' RETURNING TIME(2)
                              DEFAULT TIME'12:13:14.123' ON EMPTY) v;
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('{}', '$.a' RETURNING TIME(2)
                              DEFAULT '12:13:14.123' ON EMPTY) v;
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{}', '$.a' RETURNING DATETIME DEFAULT 'abc' ON EMPTY) v;
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('{}', '$.a'
  RETURNING DATETIME(2) DEFAULT TIMESTAMP'2000-01-02 12:13:14.123' ON EMPTY) v;
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('{}', '$.a' RETURNING DATETIME(2)
                              DEFAULT '2000-01-02 12:13:14.123' ON EMPTY) v;
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('{}', '$.a' RETURNING CHAR(1) DEFAULT 'empty' ON EMPTY) v;
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('{}', '$.a' RETURNING DECIMAL(1, 0) DEFAULT 12 ON EMPTY) v;
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('{}', '$.a' RETURNING DECIMAL(1, 0) DEFAULT '12' ON EMPTY) v;
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('{}', '$.a' RETURNING DECIMAL(5, 1) DEFAULT 1.23 ON EMPTY) v;
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('{}', '$.a' RETURNING DECIMAL(5, 1) DEFAULT '1.23' ON EMPTY) v;
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('{}', '$.a' RETURNING FLOAT DEFAULT 1e308 ON EMPTY) v;
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('{}', '$.a' RETURNING FLOAT DEFAULT '1e308' ON EMPTY) v;
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{}', '$.a' RETURNING DOUBLE DEFAULT 'xyz' ON EMPTY) v;
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('{}', '$.a' RETURNING YEAR DEFAULT '-1990' ON EMPTY) v;
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('{}', '$.a' RETURNING YEAR DEFAULT -1990 ON EMPTY) v;
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{}', '$.a' RETURNING YEAR DEFAULT 'xyz' ON EMPTY) v;

# Zero dates and invalid dates should not be accepted in the DEFAULT
# values, even if strict mode, NO_ZERO_DATE and NO_ZERO_IN_DATE are
# disabled, and ALLOW_INVALID_DATES is enabled.
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '0000-00-00' ON EMPTY);
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '0000-00-00' ON ERROR);
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '2000-01-00' ON EMPTY);
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '2000-00-01' ON ERROR);
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '2004-02-31' ON EMPTY);
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '2004-02-31' ON ERROR);
SET @saved_sql_mode = @@sql_mode;
SET sql_mode = 'ALLOW_INVALID_DATES';
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '0000-00-00' ON EMPTY);
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '0000-00-00' ON ERROR);
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '2000-01-00' ON EMPTY);
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '2000-00-01' ON ERROR);
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '2004-02-31' ON EMPTY);
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{}', '$' RETURNING DATE DEFAULT '2004-02-31' ON ERROR);
SET sql_mode = @saved_sql_mode;

# Verify that the ON ERROR clause (by default NULL ON ERROR) kicks in
# also when accessing the result of JSON_VALUE as another type than
# the one in the RETURNING clause.

# Use integers as strings.
SELECT JSON_VALUE('{"a":"b"}', '$.a' RETURNING SIGNED) AS c1,
       REPEAT(JSON_VALUE('{"a":"b"}', '$.a' RETURNING SIGNED), 10) AS c2,
       JSON_VALUE('{"a":"b"}', '$.a' RETURNING UNSIGNED) AS c3,
       REPEAT(JSON_VALUE('{"a":"b"}', '$.a' RETURNING UNSIGNED), 10) AS c4;

# Use a DECIMAL as a string.
SELECT JSON_VALUE('{"a":"b"}', '$.a' RETURNING DECIMAL(5, 2)) AS c1,
       REPEAT(JSON_VALUE('{"a":"b"}', '$.a' RETURNING DECIMAL(5, 2)), 10) AS c2;

# Use floting-point values as strings.
SELECT JSON_VALUE('{"a":"b"}', '$.a' RETURNING FLOAT) AS c1,
       REPEAT(JSON_VALUE('{"a":"b"}', '$.a' RETURNING FLOAT), 10) AS c2,
       JSON_VALUE('{"a":"b"}', '$.a' RETURNING DOUBLE) AS c3,
       REPEAT(JSON_VALUE('{"a":"b"}', '$.a' RETURNING DOUBLE), 10) AS c4;

# Use a CHAR(1) as a number.
SELECT JSON_VALUE('{"a":"1234"}', '$.a' RETURNING CHAR(1)) AS c1,
       JSON_VALUE('{"a":"1234"}', '$.a' RETURNING CHAR(1)) + 1 AS c2,
       REPEAT('x', JSON_VALUE('{"a":"1234"}', '$.a' RETURNING CHAR(1))) AS c3;

# Use temporal values as strings.
SELECT JSON_VALUE('{"a":"b"}', '$.a' RETURNING DATE) AS c1,
       REPEAT(JSON_VALUE('{"a":"b"}', '$.a' RETURNING DATE), 10) AS c2,
       JSON_VALUE('{"a":"b"}', '$.a' RETURNING TIME) AS c3,
       REPEAT(JSON_VALUE('{"a":"b"}', '$.a' RETURNING TIME), 10) AS c4,
       JSON_VALUE('{"a":"b"}', '$.a' RETURNING DATETIME) AS c5,
       REPEAT(JSON_VALUE('{"a":"b"}', '$.a' RETURNING DATETIME), 10) AS c6;

#Use year as strings.
SELECT JSON_VALUE('{"a":"b"}', '$.a' RETURNING YEAR) AS c1,
       REPEAT(JSON_VALUE('{"a":"b"}', '$.a' RETURNING YEAR), 10) AS c2;

# Use a JSON value as a number.
SELECT JSON_VALUE('{"a":"b"}', '$.a' RETURNING JSON) AS c1,
       JSON_VALUE('{"a":"b"}', '$.a' RETURNING JSON) + 1 AS c2;

# Test conversions to DOUBLE.
SELECT JSON_VALUE('1', '$' RETURNING SIGNED) + 1.0e0;
SELECT JSON_VALUE('1', '$' RETURNING UNSIGNED) + 1.0e0;
SELECT JSON_VALUE('"2000-01-02"', '$' RETURNING DATE) + 1.0e0;
SELECT JSON_VALUE('"12:13:14.123456"', '$' RETURNING TIME) + 1.0e0;
SELECT JSON_VALUE('"2000-01-02 12:13:14.123456"', '$' RETURNING DATETIME) + 1.0e0;
SELECT JSON_VALUE('"1"', '$') + 1.0e0;
SELECT JSON_VALUE('1.2', '$' RETURNING DECIMAL(2, 1)) + 1.0e0;
SELECT JSON_VALUE('1', '$' RETURNING JSON) + 1.0e0;
SELECT JSON_VALUE('1.2', '$' RETURNING FLOAT) + 1.0e0;
SELECT JSON_VALUE('1988', '$' RETURNING YEAR) + 1.0e0;

# Test conversions to DECIMAL.
SELECT JSON_VALUE('123', '$' RETURNING SIGNED) DIV 2;
SELECT JSON_VALUE('123', '$' RETURNING UNSIGNED) DIV 2;
SELECT JSON_VALUE('"2000-01-02"', '$' RETURNING DATE) DIV 2;
SELECT JSON_VALUE('"12:13:14.123456"', '$' RETURNING TIME) DIV 2;
SELECT JSON_VALUE('"2000-01-02 12:13:14.123456"', '$' RETURNING DATETIME) DIV 2;
SELECT JSON_VALUE('"123"', '$') DIV 2;
SELECT JSON_VALUE('123', '$' RETURNING JSON) DIV 2;
SELECT JSON_VALUE('12.3', '$' RETURNING FLOAT) DIV 2;
SELECT JSON_VALUE('12.3', '$' RETURNING DOUBLE) DIV 2;
SELECT CAST(JSON_VALUE('123', '$' RETURNING SIGNED) AS DECIMAL);
SELECT CAST(JSON_VALUE('123', '$' RETURNING UNSIGNED) AS DECIMAL);
SELECT CAST(JSON_VALUE('1988', '$' RETURNING YEAR) AS DECIMAL);

# Test conversions to string.
SELECT CONCAT('x', JSON_VALUE('1', '$' RETURNING SIGNED));
SELECT CONCAT('x', JSON_VALUE('1', '$' RETURNING UNSIGNED));
SELECT CONCAT('x', JSON_VALUE('"2000-01-02"', '$' RETURNING DATE));
SELECT CONCAT('x', JSON_VALUE('"12:13:14.123456"', '$' RETURNING TIME));
SELECT CONCAT('x', JSON_VALUE('"2000-01-02 12:13:14.123456"', '$' RETURNING DATETIME));
SELECT CONCAT('x', JSON_VALUE('"1"', '$'));
SELECT CONCAT('x', JSON_VALUE('1.2', '$' RETURNING DECIMAL(2, 1)));
SELECT CONCAT('x', JSON_VALUE('1', '$' RETURNING JSON));
SELECT CONCAT('x', JSON_VALUE('1.2', '$' RETURNING FLOAT));
SELECT CONCAT('x', JSON_VALUE('1.2', '$' RETURNING DOUBLE));
SELECT CONCAT('x', JSON_VALUE('1988', '$' RETURNING YEAR));

# Test conversions to DATE.
SELECT DATE_ADD(JSON_VALUE('20000102', '$' RETURNING SIGNED), INTERVAL 1 DAY);
SELECT DATE_ADD(JSON_VALUE('20000102', '$' RETURNING UNSIGNED), INTERVAL 1 DAY);
SET TIMESTAMP=UNIX_TIMESTAMP('2019-01-01 12:00:00');
SELECT CAST(JSON_VALUE('"12:13:14"', '$' RETURNING TIME) AS DATE);
SET TIMESTAMP=DEFAULT;
SELECT DATE_ADD(JSON_VALUE('"2000-01-02"', '$'), INTERVAL 1 DAY);
SELECT DATE_ADD(JSON_VALUE('20000102', '$' RETURNING DECIMAL), INTERVAL 1 DAY);
SELECT DATE_ADD(JSON_VALUE('"2000-01-02"', '$' RETURNING JSON), INTERVAL 1 DAY);
SELECT DATE_ADD(JSON_VALUE('20000102', '$' RETURNING FLOAT), INTERVAL 1 DAY);
SELECT DATE_ADD(JSON_VALUE('20000102', '$' RETURNING DOUBLE), INTERVAL 1 DAY);
SELECT DATE_ADD(JSON_VALUE('1988', '$' RETURNING YEAR), INTERVAL 1 DAY);

# Test conversions to TIME.
SELECT TIME_TO_SEC(JSON_VALUE('121314', '$' RETURNING SIGNED));
SELECT TIME_TO_SEC(JSON_VALUE('121314', '$' RETURNING UNSIGNED));
SELECT TIME_TO_SEC(JSON_VALUE('"2000-01-02"', '$' RETURNING DATE));
SELECT TIME_TO_SEC(JSON_VALUE('"2000-01-02 12:13:14"', '$' RETURNING DATETIME));
SELECT TIME_TO_SEC(JSON_VALUE('"12:13:14"', '$'));
SELECT TIME_TO_SEC(JSON_VALUE('121314', '$' RETURNING DECIMAL));
SELECT TIME_TO_SEC(JSON_VALUE('"12:13:14"', '$' RETURNING JSON));
SELECT TIME_TO_SEC(JSON_VALUE('121314', '$' RETURNING FLOAT));
SELECT TIME_TO_SEC(JSON_VALUE('121314', '$' RETURNING DOUBLE));
SELECT TIME_TO_SEC(JSON_VALUE('2030', '$' RETURNING YEAR));

# Test conversions to DATETIME.
SELECT TIMESTAMPADD(MINUTE, 1, JSON_VALUE('20000102121314',
                                          '$' RETURNING SIGNED));
SELECT TIMESTAMPADD(MINUTE, 1, JSON_VALUE('20000102121314',
                                          '$' RETURNING UNSIGNED));
SELECT TIMESTAMPADD(MINUTE, 1, JSON_VALUE('"2000-01-02"', '$' RETURNING DATE));
SET TIMESTAMP=UNIX_TIMESTAMP('2019-01-01 12:00:00');
SELECT CAST(JSON_VALUE('"12:13:14"', '$' RETURNING TIME) AS DATETIME);
SET TIMESTAMP=DEFAULT;
SELECT TIMESTAMPADD(MINUTE, 1, JSON_VALUE('"2000-01-02 12:13:14"', '$'));
SELECT TIMESTAMPADD(MINUTE, 1, JSON_VALUE('20000102121314',
                                          '$' RETURNING DECIMAL(14, 0)));
--echo # Warns because 20000102121314 gets truncated to fit in a FLOAT,
--echo # and is not a valid timestamp after truncation.
SELECT TIMESTAMPADD(MINUTE, 1, JSON_VALUE('20000102121314',
                                          '$' RETURNING FLOAT));
SELECT TIMESTAMPADD(MINUTE, 1, JSON_VALUE('20000102121314',
                                          '$' RETURNING DOUBLE));
SELECT TIMESTAMPADD(MINUTE, 1, JSON_VALUE('1988', '$' RETURNING YEAR));

# Test conversions to SIGNED.
SELECT CAST(JSON_VALUE('1234', '$' RETURNING UNSIGNED) AS SIGNED);
SELECT CAST(JSON_VALUE('"2000-01-02"', '$' RETURNING DATE) AS SIGNED);
SELECT CAST(JSON_VALUE('"12:13:14"', '$' RETURNING TIME) AS SIGNED);
SELECT CAST(JSON_VALUE('"2000-01-02 12:13:14"', '$' RETURNING DATETIME)
            AS SIGNED);
SELECT CAST(JSON_VALUE('"1234"', '$') AS SIGNED);
SELECT CAST(JSON_VALUE('1234', '$' RETURNING DECIMAL) AS SIGNED);
SELECT CAST(JSON_VALUE('1234', '$' RETURNING JSON) AS SIGNED);
SELECT CAST(JSON_VALUE('1234', '$' RETURNING FLOAT) AS SIGNED);
SELECT CAST(JSON_VALUE('1234', '$' RETURNING DOUBLE) AS SIGNED);
SELECT CAST(JSON_VALUE('1988', '$' RETURNING YEAR) AS SIGNED);
SELECT CAST(JSON_VALUE('43', '$' RETURNING YEAR) AS SIGNED);
SELECT CAST(JSON_VALUE('88', '$' RETURNING YEAR) AS SIGNED);
SELECT REPEAT('x', JSON_VALUE('10', '$' RETURNING JSON));

# Test conversions to UNSIGNED.
SELECT CAST(JSON_VALUE('1234', '$' RETURNING SIGNED) AS UNSIGNED);
SELECT CAST(JSON_VALUE('"2000-01-02"', '$' RETURNING DATE) AS UNSIGNED);
SELECT CAST(JSON_VALUE('"12:13:14"', '$' RETURNING TIME) AS UNSIGNED);
SELECT CAST(JSON_VALUE('"2000-01-02 12:13:14"', '$' RETURNING DATETIME)
            AS UNSIGNED);
SELECT CAST(JSON_VALUE('"1234"', '$') AS UNSIGNED);
SELECT CAST(JSON_VALUE('1234', '$' RETURNING DECIMAL) AS UNSIGNED);
SELECT CAST(JSON_VALUE('1234', '$' RETURNING JSON) AS UNSIGNED);
SELECT CAST(JSON_VALUE('1234', '$' RETURNING FLOAT) AS UNSIGNED);
SELECT CAST(JSON_VALUE('1234', '$' RETURNING DOUBLE) AS UNSIGNED);
SELECT CAST(JSON_VALUE('1988', '$' RETURNING YEAR) AS UNSIGNED);

--echo # SIGNED ranges.
SELECT JSON_VALUE('9223372036854775807', '$' RETURNING SIGNED) AS max_val,
       JSON_VALUE('9223372036854775808', '$' RETURNING SIGNED) AS plus_one,
       JSON_VALUE('-9223372036854775808', '$' RETURNING SIGNED) AS min_val,
       JSON_VALUE('-9223372036854775809', '$' RETURNING SIGNED) AS minus_one;
SELECT JSON_VALUE('"9223372036854775807"', '$' RETURNING SIGNED) AS max_val,
       JSON_VALUE('"9223372036854775808"', '$' RETURNING SIGNED) AS plus_one,
       JSON_VALUE('"-9223372036854775808"', '$' RETURNING SIGNED) AS min_val,
       JSON_VALUE('"-9223372036854775809"', '$' RETURNING SIGNED) AS minus_one;
SELECT JSON_VALUE('{}', '$.x' RETURNING SIGNED
                              DEFAULT '9223372036854775807' ON EMPTY) AS a,
       JSON_VALUE('{}', '$.x' RETURNING SIGNED
                              DEFAULT 9223372036854775807 ON EMPTY) AS b,
       JSON_VALUE('{}', '$.x' RETURNING SIGNED
                              DEFAULT '-9223372036854775808' ON EMPTY) AS c,
       JSON_VALUE('{}', '$.x' RETURNING SIGNED
                              DEFAULT -9223372036854775808 ON EMPTY) AS d;
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('{}', '$.x' RETURNING SIGNED
                              DEFAULT '9223372036854775808' ON EMPTY);
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('{}', '$.x' RETURNING SIGNED
                              DEFAULT 9223372036854775808 ON EMPTY);
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('{}', '$.x' RETURNING SIGNED
                              DEFAULT '-9223372036854775809' ON EMPTY);
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('{}', '$.x' RETURNING SIGNED
                              DEFAULT -9223372036854775809 ON EMPTY);

--echo # UNSIGNED ranges.
SELECT JSON_VALUE('18446744073709551615', '$' RETURNING UNSIGNED) AS max_val,
       JSON_VALUE('18446744073709551616', '$' RETURNING UNSIGNED) AS plus_one,
       JSON_VALUE('0', '$' RETURNING UNSIGNED) AS min_val,
       JSON_VALUE('-1', '$' RETURNING UNSIGNED) AS minus_one;
SELECT JSON_VALUE('"18446744073709551615"', '$' RETURNING UNSIGNED) AS max_val,
       JSON_VALUE('"18446744073709551616"', '$' RETURNING UNSIGNED) AS plus_one,
       JSON_VALUE('"0"', '$' RETURNING UNSIGNED) AS min_val,
       JSON_VALUE('"-1"', '$' RETURNING UNSIGNED) AS minus_one;
SELECT JSON_VALUE('{}', '$.x' RETURNING UNSIGNED
                              DEFAULT '18446744073709551615' ON EMPTY) AS a,
       JSON_VALUE('{}', '$.x' RETURNING UNSIGNED
                              DEFAULT 18446744073709551615 ON EMPTY) AS b,
       JSON_VALUE('{}', '$.x' RETURNING UNSIGNED
                              DEFAULT '0' ON EMPTY) AS c,
       JSON_VALUE('{}', '$.x' RETURNING UNSIGNED
                              DEFAULT 0 ON EMPTY) AS d;
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('{}', '$.x' RETURNING UNSIGNED
                              DEFAULT '18446744073709551616' ON EMPTY);
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('{}', '$.x' RETURNING UNSIGNED
                              DEFAULT 18446744073709551616 ON EMPTY);
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('{}', '$.x' RETURNING UNSIGNED
                              DEFAULT '-1' ON EMPTY);
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('{}', '$.x' RETURNING UNSIGNED
                              DEFAULT -1 ON EMPTY);

--echo # Check that the nullability of JSON_VALUE is correct.

--echo # Should be nullable because of implicit NULL ON EMPTY/NULL ON ERROR.
CREATE VIEW v AS SELECT JSON_VALUE('{}', '$.x') AS c1,
                        JSON_VALUE('{}', '$.x' DEFAULT '1' ON EMPTY) AS c2,
                        JSON_VALUE('{}', '$.x' DEFAULT '2' ON ERROR) AS c3;
DESCRIBE v;
DROP VIEW v;

--echo # Should be nullable because of explicit NULL ON EMPTY/NULL ON ERROR.
CREATE VIEW v AS
  SELECT JSON_VALUE('{}', '$.x' DEFAULT '1' ON EMPTY NULL ON ERROR) AS c1,
         JSON_VALUE('{}', '$.x' NULL ON EMPTY DEFAULT '2' ON ERROR) AS c2,
         JSON_VALUE('{}', '$.x' ERROR ON EMPTY NULL ON ERROR) AS c3,
         JSON_VALUE('{}', '$.x' NULL ON EMPTY ERROR ON ERROR) AS c4;
DESCRIBE v;
DROP VIEW v;

--echo # Should be nullable because the first argument is nullable.
CREATE VIEW v AS
  SELECT JSON_VALUE(NULL, '$.x' ERROR ON EMPTY ERROR ON ERROR) AS c1;
DESCRIBE v;
DROP VIEW v;

--echo # Should not be nullable.
CREATE VIEW v AS
  SELECT JSON_VALUE('{}', '$.x' DEFAULT '1' ON EMPTY DEFAULT '2' ON ERROR) c1,
         JSON_VALUE('{}', '$.x' ERROR ON EMPTY ERROR ON ERROR) c2;
DESCRIBE v;
DROP VIEW v;

--echo # Test index creation/usage
CREATE TABLE t1(j JSON, KEY i1((JSON_VALUE(j, '$' RETURNING SIGNED))));
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES('1'),('2'),('-3'),('-4');
ANALYZE TABLE t1;
--let $query = SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING SIGNED) = -3
--eval EXPLAIN $query
--eval $query
let $query = SELECT * FROM t1
  WHERE JSON_VALUE(j, '$' RETURNING SIGNED NULL ON EMPTY NULL ON ERROR) = -3;
--eval EXPLAIN $query
--eval $query
--echo # The index is not used if the JSON_VALUE expressions differ.
EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$') = -3;
EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING UNSIGNED) = -3;
EXPLAIN SELECT * FROM t1
  WHERE JSON_VALUE(j, '$' RETURNING SIGNED DEFAULT '4' ON EMPTY) = -3;
EXPLAIN SELECT * FROM t1
  WHERE JSON_VALUE(j, '$' RETURNING SIGNED ERROR ON ERROR) = -3;
DROP TABLE t1;

CREATE TABLE t1(j JSON, KEY i1((JSON_VALUE(j, '$' RETURNING UNSIGNED))));
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES('1'),('2'),('3'),('4');
ANALYZE TABLE t1;
--let $query = SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING UNSIGNED) = 3
--eval EXPLAIN $query
--eval $query
INSERT INTO t1 VALUES('-1'),('-2');
DROP TABLE t1;

CREATE TABLE t1(j JSON, KEY i1((JSON_VALUE(j, '$' RETURNING DECIMAL(3, 2)))));
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES('1.1'),('2.2'),('3.3'),('4.4');
ANALYZE TABLE t1;
let $query =
  SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING DECIMAL(3, 2)) = 3.3;
--eval EXPLAIN $query
--eval $query
--echo # The index is not used if the JSON_VALUE expressions differ.
EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING DECIMAL(3, 0)) = 3.3;
EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING DECIMAL(2, 2)) = 3.3;
EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING DECIMAL(4, 0)) = 3.3;
DROP TABLE t1;

CREATE TABLE t1(j JSON, KEY i1((JSON_VALUE(j, '$' RETURNING FLOAT))));
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES('1.1'),('2.2'),('3.3'),('4.4');
ANALYZE TABLE t1;
let $query =
  SELECT * FROM t1
    WHERE JSON_VALUE(j, '$' RETURNING FLOAT) = CAST(3.3 AS FLOAT);
--eval EXPLAIN $query
--eval $query
--echo # The index is not used if the JSON_VALUE expressions differ.
EXPLAIN SELECT * FROM t1
  WHERE JSON_VALUE(j, '$' RETURNING DOUBLE) = CAST(3.3 AS FLOAT);
DROP TABLE t1;

CREATE TABLE t1(j JSON, KEY i1((JSON_VALUE(j, '$' RETURNING DOUBLE))));
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES('1.1'),('2.2'),('3.3'),('4.4');
ANALYZE TABLE t1;
let $query =
  SELECT * FROM t1
    WHERE JSON_VALUE(j, '$' RETURNING DOUBLE) = CAST(3.3 AS DOUBLE);
--eval EXPLAIN $query
--eval $query
EXPLAIN SELECT * FROM t1
  WHERE JSON_VALUE(j, '$' RETURNING DOUBLE) = CAST(3.3 AS DOUBLE);
--echo # The index is not used if the JSON_VALUE expressions differ.
EXPLAIN SELECT * FROM t1
  WHERE JSON_VALUE(j, '$' RETURNING FLOAT) = CAST(3.3 AS DOUBLE);
DROP TABLE t1;

CREATE TABLE t1(j JSON, KEY i1((JSON_VALUE(j, '$' RETURNING CHAR(10)))));
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES('"asdf"'),('"dfgh"'),('"FghJ"'),('"fghj"'),('"hjkl"');
ANALYZE TABLE t1;
--let $query = SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING CHAR(10)) = 'fghj'
--eval EXPLAIN $query
--eval $query
--echo # The index is not used if the JSON_VALUE expressions differ.
EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING CHAR(11)) = 'fghj';
EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$') = 'fghj';
DROP TABLE t1;

CREATE TABLE t1(j JSON,
  KEY i1((JSON_VALUE(j, '$' RETURNING CHAR(10) CHARSET utf8mb4))));
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES('"asdf"'),('"dfgh"'),('"FghJ"'),('"fghj"'),('"hjkl"');
ANALYZE TABLE t1;
let $query =
  SELECT * FROM t1
    WHERE JSON_VALUE(j, '$' RETURNING CHAR(10) CHARSET utf8mb4) = 'fghj';
--eval EXPLAIN $query
--eval $query
--echo # The index is not used if the JSON_VALUE expressions differ.
EXPLAIN SELECT * FROM t1
  WHERE JSON_VALUE(j, '$' RETURNING CHAR(10) CHARSET latin1) = 'fghj';
DROP TABLE t1;

CREATE TABLE t1(j JSON, KEY i1((JSON_VALUE(j, '$'))));
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES('"asdf"'),('"dfgh"'),('"fghj"'),('"hjkl"');
ANALYZE TABLE t1;
--let $query = SELECT * FROM t1 WHERE JSON_VALUE(j, '$') = 'fghj'
--eval EXPLAIN $query
--eval $query
DROP TABLE t1;

CREATE TABLE t1(j JSON, KEY i1((JSON_VALUE(j, '$' RETURNING DATETIME))));
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES('"2019-01-01 11:11:11"'),('"2019-01-01 11:11:12"'),
  ('"2019-01-01 11:11:13"'),('"2019-01-01 11:11:14"');
ANALYZE TABLE t1;
let $query =
  SELECT * FROM t1 WHERE
    JSON_VALUE(j, '$' RETURNING DATETIME) = TIMESTAMP'2019-01-01 11:11:12';
--eval EXPLAIN $query
--eval $query
--echo # The index is not used if the JSON_VALUE expressions differ.
EXPLAIN SELECT * FROM t1
  WHERE JSON_VALUE(j, '$' RETURNING DATE) = TIMESTAMP'2019-01-01 11:11:12';
DROP TABLE t1;

CREATE TABLE t1(j JSON, KEY i1((JSON_VALUE(j, '$' RETURNING TIME))));
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES('"11:11:11"'),('"11:11:12"'),
  ('"11:11:13"'),('"11:11:14"');
ANALYZE TABLE t1;
let $query =
  SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING TIME) = TIME'11:11:12';
--eval EXPLAIN $query
--eval $query
DROP TABLE t1;

CREATE TABLE t1(j JSON, KEY i1((JSON_VALUE(j, '$' RETURNING DATE))));
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES('"2019-01-01"'),('"2019-01-02"'),
  ('"2019-01-03"'),('"2019-01-04"');
ANALYZE TABLE t1;
let $query =
  SELECT * FROM t1 WHERE JSON_VALUE(j, '$' RETURNING DATE) = DATE'2019-01-02';
--eval EXPLAIN $query
--eval $query
DROP TABLE t1;

# Verify that the null_value flag is reset between rows.
CREATE TABLE t(j JSON);
INSERT INTO t VALUES ('[1]'), (NULL), ('[2]'), (NULL), ('[3]');
--sorted_result
SELECT JSON_VALUE(j, '$[0]' RETURNING SIGNED) AS v FROM t;
DROP TABLE t;

# Verify that JSON_VALUE can be used in INSERT statements.
CREATE TABLE t1(id INT PRIMARY KEY, vc VARCHAR(100));
INSERT INTO t1 VALUES
  (1, '{"a":"a very long string"}'),
  (2, '{invalid json}'),
  (3, '{"a":"good"}'),
  (4, '{"b":"xyz"}');
CREATE TABLE t2(id INT PRIMARY KEY, vc VARCHAR(100));
INSERT INTO t2
  SELECT id, JSON_VALUE(vc, '$.a' RETURNING CHAR(10)
                                  DEFAULT 'empty' ON EMPTY
                                  DEFAULT 'error' ON ERROR)
    FROM t1;
SELECT * FROM t2 ORDER BY id;
DROP TABLE t1, t2;

--echo #
--echo # Bug#30141215: SIG6 IN ROW_UPD_SEC_INDEX_ENTRY_LOW() AT ROW/ROW0UPD.CC
--echo #
CREATE TABLE t (j JSON, KEY ((JSON_VALUE(j, '$[0]' RETURNING CHAR(1)))));
SELECT * FROM t;
INSERT INTO t VALUES ('["abcde"]');
# Used to hit an assertion.
DELETE FROM t;
DROP TABLE t;

--echo #
--echo # Bug#30084642: ERROR FROM MEMBER OF AFTER ADDING FUNCTIONAL INDEX
--echo #
CREATE TABLE t(j JSON, KEY ((JSON_VALUE(j, '$' RETURNING SIGNED))));
INSERT INTO t VALUES ('1'), ('2');
SELECT * FROM t WHERE 1 MEMBER OF (j->'$');
DROP TABLE t;

--echo #
--echo # Bug#30330513: SIG6 IN ROW_UPD_SEC_INDEX_ENTRY_LOW()
--echo #
CREATE TABLE t
  (j JSON, KEY ((JSON_VALUE(j, '$' RETURNING CHAR(10) CHARSET utf32))));
INSERT INTO t VALUES
  ('"foobar"'),
  ('"Peh4UsNdzmNQbUhbXSK7L0uY0SP1U5F1PvIDDqbaUpR2Gu8NTnXnUmsDy"');
DELETE FROM t;
DROP TABLE t;

--echo #
--echo # Bug#30315526: SIG6 IN ITEM::EVALUATE() AT SQL/ITEM.CC
--echo #
SELECT JSON_VALUE('"1990-12-29"', '$' RETURNING DATE) IS NULL AS c1,
       JSON_VALUE('"12:00:00"', '$' RETURNING TIME) IS NULL AS c2,
       JSON_VALUE('"1990-12-29 12:00:00"', '$' RETURNING TIME) IS NULL AS c3;

--echo #
--echo # Bug#30320927: SIG6 IN MY_PACKED_TIME_MAKE() AT MYSYS/MY_TIME.CC
--echo #
SELECT JSON_VALUE('[]', '$[0]' RETURNING DATE)
         BETWEEN DATE'2019-01-01' AND DATE'2020-01-01' AS c1,
       JSON_VALUE('"abc"', '$' RETURNING DATE)
         BETWEEN DATE'2019-01-01' AND DATE'2020-01-01' AS c2,
       JSON_VALUE('[]', '$[0]' RETURNING TIME)
         BETWEEN TIME'11:00:00' AND TIME'12:00:00' AS c3,
       JSON_VALUE('"abc"', '$' RETURNING TIME)
         BETWEEN TIME'11:00:00' AND TIME'12:00:00' AS c4;

--echo #
--echo # Bug#30349041: SIG6 PRINT_CAST_TYPE() AT SQL/ITEM_JSON_FUNC.CC
--echo #
EXPLAIN SELECT JSON_VALUE('true', '$' RETURNING JSON) v;

--echo #
--echo # Bug#30631787: SIG6 IN SQL_CMD_DML::PREPARE() AT SQL/SQL_SELECT.CC
--echo #
--source include/turn_off_strict_mode.inc
--error ER_TRUNCATED_WRONG_VALUE
SELECT JSON_VALUE('{}', '$' RETURNING TIME DEFAULT '184' ON EMPTY);
--source include/restore_strict_mode.inc

--echo #
--echo # Bug#30633294: SIG11 IN STD::DEFAULT_DELETE() AT BITS/UNIQUE_PTR.H
--echo #
CREATE TABLE t(
  j JSON,
  gc VARCHAR(512) AS (JSON_VALUE(j, '$[*]' DEFAULT 'abc' ON EMPTY
                                           DEFAULT 'def' ON ERROR)));
SELECT * FROM t;
SELECT * FROM t;
SELECT * FROM t;
DROP TABLE t;

--echo #
--echo # Bug#30720910: JSON_VALUE() DOES NOT CHECK PRECISION FOR DECIMAL TYPE
--echo #
SELECT JSON_VALUE('[12345.67]', '$[0]' RETURNING DECIMAL(5, 2)) v;
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('[12345.67]', '$[0]' RETURNING DECIMAL(5, 2) ERROR ON ERROR);
--error ER_DATA_OUT_OF_RANGE
SELECT JSON_VALUE('{}', '$.x' RETURNING DECIMAL(5, 2) DEFAULT 12345.67 ON EMPTY);

--echo #
--echo # Bug#31168181 FILTERING USING JSON FIELD, IS TRUE DOES NOT WORK
--echo #

CREATE TABLE test (id INT, j JSON);
INSERT INTO test VALUES (1, '{"some_key": true}');
INSERT INTO test VALUES (2, '{"some_key": false}');
INSERT INTO test VALUES (3, '{"some_key": null}');
INSERT INTO test VALUES (4, '{"some_key": "true"}'); # a string, NOT a literal!

# The results are not always intuitive. We want to test that a warning
# is emitted when this happens. That should happen only for comparison against
# TRUE or FALSE. Not for IS NULL as there is risk of false positives.

SELECT j, (j->'$.some_key' IS TRUE) AS ist,
          (j->'$.some_key' IS FALSE) AS isf,
          (j->'$.some_key' IS NULL) AS isn
FROM test;

SELECT j FROM test
WHERE j->'$.some_key';

SELECT j FROM test
WHERE NOT j->'$.some_key';

# Test the workaround suggested by the warnings above; now results
# should be good:

SELECT j, (json_value(j, '$.some_key' RETURNING SIGNED) IS TRUE) AS ist,
          (json_value(j, '$.some_key' RETURNING SIGNED) IS FALSE) AS isf,
          (json_value(j, '$.some_key' RETURNING SIGNED) IS NULL) AS isn
FROM test;

SELECT j FROM test
WHERE json_value(j, '$.some_key' RETURNING SIGNED);

SELECT j FROM test
WHERE NOT json_value(j, '$.some_key' RETURNING SIGNED);

# Other cases of warning:

SELECT json_extract(json_object( "a",FALSE),'$.a') IS TRUE;

SELECT CAST(TRUE AS JSON) IS TRUE AS ist,
       CAST(TRUE AS JSON) IS FALSE AS isf;

SELECT CAST(FALSE AS JSON) IS TRUE AS ist,
       CAST(FALSE AS JSON) IS FALSE AS isf;

SELECT CAST(NULL AS JSON) IS TRUE AS ist,
       CAST(NULL AS JSON) IS FALSE AS isf;

SELECT CAST(123 AS JSON) IS TRUE AS ist,
       CAST(123 AS JSON) IS FALSE AS isf;

SELECT CAST('"ab"' AS JSON) IS TRUE AS ist,
       CAST('"ab"' AS JSON) IS FALSE AS isf;

DROP TABLE test;

--echo #
--echo # Bug#31336625 JSON_VALUE(EXPRESSION PATH RETURNING TYPE) SHOULD RETURN SQL NULL FOR JSON NULL
--echo #

# When we use ERROR ON ERROR below, it is to be sure that the expected NULL is
# not due to an error.

SELECT JSON_VALUE('null', '$' RETURNING DATE ERROR ON ERROR),
       JSON_VALUE('null', '$' RETURNING DATE),
       JSON_VALUE('null', '$' RETURNING DATE) IS NULL;

SELECT JSON_VALUE('null', '$' RETURNING SIGNED ERROR ON ERROR),
       JSON_VALUE('null', '$' RETURNING SIGNED),
       JSON_VALUE('null', '$' RETURNING SIGNED) IS NULL;

SELECT JSON_VALUE('null', '$' RETURNING CHAR ERROR ON ERROR),
       JSON_VALUE('null', '$' RETURNING CHAR),
       JSON_VALUE('null', '$' RETURNING CHAR) IS NULL;

SELECT JSON_VALUE(CAST('null' AS JSON), '$' RETURNING CHAR ERROR ON ERROR);
SELECT JSON_VALUE(CAST('null' AS JSON), '$' RETURNING SIGNED ERROR ON ERROR);

CREATE TABLE t(a JSON);
INSERT INTO t VALUES('[1,2,3,null]');
SELECT JSON_VALUE(a, '$[2]' RETURNING SIGNED),
       JSON_VALUE(a, '$[3]' RETURNING SIGNED ERROR ON ERROR),
       JSON_VALUE(a, '$[3]' RETURNING SIGNED) FROM t;
DELETE FROM t;
INSERT INTO t VALUES('{"k1":"xy", "k2":null}');
SELECT JSON_VALUE(a, '$.k1' RETURNING CHAR),
       JSON_VALUE(a, '$.k2' RETURNING CHAR ERROR ON ERROR),
       JSON_VALUE(a, '$.k2' RETURNING CHAR) FROM t;
DELETE FROM t;
INSERT INTO t VALUES('{"k1":"2020-05-12", "k2":null}');
SELECT JSON_VALUE(a, '$.k1' RETURNING DATE),
       JSON_VALUE(a, '$.k2' RETURNING DATE ERROR ON ERROR),
       JSON_VALUE(a, '$.k2' RETURNING DATE) FROM t;
DROP TABLE t;

# It's possible to store and retrieve 3-value boolean logic values:
SELECT JSON_VALUE(JSON_OBJECT('a', FALSE), '$.a' RETURNING SIGNED),
       JSON_VALUE(JSON_OBJECT('a', TRUE), '$.a' RETURNING SIGNED),
       JSON_VALUE(JSON_OBJECT('a', NULL), '$.a' RETURNING SIGNED ERROR ON ERROR),
       JSON_VALUE(JSON_OBJECT('a', NULL), '$.a' RETURNING SIGNED);

# As CHAR:
SELECT JSON_VALUE(JSON_OBJECT('a', 'value'), '$.a'),
       JSON_VALUE(JSON_OBJECT('a', NULL), '$.a');

--echo #
--echo # Bug#34554755: DISTINCT on FLOAT values gives duplicates
--echo #               with SQL_BIG_RESULT
--echo #

# These two integers map to the same FLOAT value, so the comparison
# should return TRUE.
SELECT JSON_VALUE('1111111111111111', '$' RETURNING FLOAT) =
       JSON_VALUE('1111111111111110', '$' RETURNING FLOAT) AS eq;

# Converting the FLOAT to other types should not keep the full DOUBLE precision.
SELECT CAST(JSON_VALUE('1111111111111111', '$' RETURNING FLOAT) AS CHAR) AS v;
SELECT CAST(JSON_VALUE('1111111111111111', '$' RETURNING FLOAT) AS SIGNED) AS v;
SELECT CAST(JSON_VALUE('1111111111111111', '$' RETURNING FLOAT)
            AS DECIMAL(20,2)) AS res;
SELECT CAST(JSON_VALUE('{}', '$.a' RETURNING FLOAT
                       DEFAULT 1111111111111111 ON EMPTY) AS CHAR) AS v;
SELECT CAST(JSON_VALUE('{}', '$.a' RETURNING FLOAT
                       DEFAULT 1111111111111111 ON EMPTY) AS SIGNED) AS v;
SELECT CAST(JSON_VALUE('{}', '$.a' RETURNING FLOAT
                       DEFAULT 1111111111111111 ON EMPTY)
            AS DECIMAL(20,2)) AS v;

--echo #
--echo # Bug#35206138: Assigning json_value output to user defined
--echo #               variable will return NULL or empty string if
--echo #               input is empty string
--echo #

SET @x = JSON_VALUE('{"x": ""}', '$.x');
# Used to return NULL. Should return an empty string.
SELECT @x;