--source ../include/have_columnstore.inc
--disable_warnings
DROP DATABASE IF EXISTS json_search_db;

--enable_warnings
CREATE DATABASE json_search_db;

USE json_search_db;

--echo # ----------------------------------------------------------------------
--echo # Test of JSON_SEARCH function.
--echo # ----------------------------------------------------------------------
--echo 
--echo # JSON_SEARCH with single path expression
--echo  
CREATE TABLE t1(j TEXT, f TEXT, v TEXT) ENGINE = COLUMNSTORE;

INSERT INTO
    t1
VALUES
    (
        '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]',
        'one',
        'abc'
    ),
    (
        '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]',
        'all',
        'abc'
    ),
    ('{"x": "\\""}', "one", '"'),
    ('{"x": "\\""}', "one", '\\"');

SELECT
    j AS json,
    f AS return_arg,
    v AS search_str,
    j AS json, f AS return_arg, JSON_SEARCH(j, f, v) AS result
FROM
    t1;

--echo 
--echo # JSON_SEARCH with path expression
--echo  
CREATE TABLE t2(j TEXT, f TEXT, v TEXT, e TEXT, p TEXT) ENGINE = COLUMNSTORE;

SET
    @j = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';

INSERT INTO
    t2
VALUES
    (@j, 'all', 'abc', NULL, '$[0]'),
    (@j, 'all', 'abc', NULL, '$[2]'),
    (@j, 'all', '10', NULL, '$[1]'),
    (@j, 'all', '10', NULL, '$[2]');

SELECT
    j AS json,
    f AS return_arg,
    v AS search_str,
    p AS path,
    JSON_SEARCH(j, f, v, NULL, p) AS result
FROM
    t2;

--echo 
--echo # JSON_SEARCH with escape char
--echo  

CREATE TABLE t3(j TEXT, f TEXT) ENGINE = COLUMNSTORE;
set @json = '[ "footbar", "foo%bar" ]';
INSERT INTO t3 VALUES(@json,'all');
SELECT j AS json, f AS return_arg, JSON_SEARCH(j, f, 'foo%bar' ) FROM t3;
SELECT j AS json, f AS return_arg, JSON_SEARCH(j, f, 'foo\%bar' ) FROM t3;
SELECT j AS json, f AS return_arg, JSON_SEARCH(j, f, 'foo|%bar', '|' ) FROM t3;
SELECT j AS json, f AS return_arg, JSON_SEARCH(j, f, 'foo|%bar', '|', '$[0]' ) FROM t3;
SELECT j AS json, f AS return_arg, JSON_SEARCH(j, f, 'foo|%bar', '|', '$[0]', '$[1]' ) FROM t3;
SELECT j AS json, f AS return_arg, JSON_SEARCH(j, f, 'foo|%bar', '|', '$[0]', '$[1]', '$[2]' ) FROM t3;

SELECT j AS json, f AS return_arg, JSON_SEARCH(j, f, 'foo\%bar', null ) FROM t3;
SELECT j AS json, f AS return_arg, JSON_SEARCH(j, f, 'foo\%bar', null, '$[0]' ) FROM t3;
SELECT j AS json, f AS return_arg, JSON_SEARCH(j, f, 'foo\%bar', null, '$[1]' ) FROM t3;
SELECT j AS json, f AS return_arg, JSON_SEARCH(j, f, 'foo|%bar', '|', '$[0]' ) FROM t3;
SELECT j AS json, f AS return_arg, JSON_SEARCH(j, f, 'foo|%bar', '|', '$[1]' ) FROM t3;

--echo 
--echo # JSON_SEARCH in case-sensitive
--echo  
CREATE TABLE t4(j TEXT, f TEXT) ENGINE = COLUMNSTORE;
INSERT INTO t4 VALUES('["abc", "ABC"]', 'all');
SELECT j AS json, f AS return_arg, JSON_SEARCH(j, f, 'aBC') FROM t4;
SELECT j AS json, f AS return_arg, JSON_SEARCH(j, f, 'abc') FROM t4;
SELECT j AS json, f AS return_arg, JSON_SEARCH(j, f, 'ABC') FROM t4;
drop TABLE t4;
drop TABLE t3;

DROP TABLE t2;

DROP TABLE t1;

DROP DATABASE json_search_db;