inverted_index

statement ok
CREATE TABLE t (
  a INT PRIMARY KEY,
  b INT,
  c INT,
  FAMILY (a),
  FAMILY (b)
)

statement ok
INSERT INTO t VALUES (1,1,1)

statement ok
CREATE INDEX foo ON t (b)

statement error column b of type int is not allowed as the last column in an inverted index\nHINT: see the documentation for more information about inverted indexes
CREATE INVERTED INDEX foo_inv ON t(b)

statement error column b of type int is not allowed as the last column in an inverted index\nHINT: see the documentation for more information about inverted indexes
CREATE INDEX foo_inv2 ON t USING GIN (b)

statement error pq: inverted indexes can't be unique
CREATE UNIQUE INVERTED INDEX foo_inv ON t(b)

statement ok
CREATE TABLE c (
  id INT PRIMARY KEY,
  foo JSON,
  "bAr" JSON,
  "qUuX" JSON,
  INVERTED INDEX (foo),
  INVERTED INDEX ("bAr"),
  FAMILY "primary" (id, foo, "bAr", "qUuX")
)

query TT
SHOW CREATE TABLE c
----
c  CREATE TABLE public.c (
   id INT8 NOT NULL,
   foo JSONB NULL,
   "bAr" JSONB NULL,
   "qUuX" JSONB NULL,
   CONSTRAINT "primary" PRIMARY KEY (id ASC),
   INVERTED INDEX c_foo_idx (foo),
   INVERTED INDEX "c_bAr_idx" ("bAr"),
   FAMILY "primary" (id, foo, "bAr", "qUuX")
)

# Regression test for #42944: make sure that mixed-case columns can be
# inverted indexed.
statement ok
CREATE INVERTED INDEX ON c("qUuX")

statement error column foo of type jsonb is only allowed as the last column in an inverted index\nHINT: see the documentation for more information about inverted indexes
CREATE TABLE d (
  id INT PRIMARY KEY,
  foo JSONB,
  bar JSONB,
  INVERTED INDEX (foo, bar)
)

statement error column foo of type int is not allowed as the last column in an inverted index\nHINT: see the documentation for more information about inverted indexes
CREATE TABLE d (
  id INT PRIMARY KEY,
  foo INT,
  INVERTED INDEX (foo)
)

statement ok
CREATE TABLE t1 (id1 INT PRIMARY KEY, id2 INT, id3 INT);

statement error pq: inverted indexes don't support interleaved tables
CREATE INDEX c on t1 USING GIN (id2)
   STORING (id1,id3)
   INTERLEAVE in PARENT t1 (id2);

statement error pq: inverted indexes don't support stored columns
CREATE INDEX c on t1 USING GIN (id2) STORING (id1,id3);

statement error pq: inverted indexes don't support stored columns
CREATE INVERTED INDEX c on t1 (id2) STORING (id1,id3);

statement error pq: inverted indexes can't be unique
CREATE UNIQUE INDEX foo_inv2 ON t USING GIN (b)

statement ok
CREATE TABLE d (
  a INT PRIMARY KEY,
  b JSONB
)

statement ok
CREATE INVERTED INDEX foo_inv ON d(b)

statement ok
SHOW INDEX FROM d

statement ok
INSERT INTO d VALUES(1, '{"a": "b"}')

statement ok
INSERT INTO d VALUES(2, '[1,2,3,4, "foo"]')

statement ok
INSERT INTO d VALUES(3, '{"a": {"b": "c"}}')

statement ok
INSERT INTO d VALUES(4, '{"a": {"b": [1]}}')

statement ok
INSERT INTO d VALUES(5, '{"a": {"b": [1, [2]]}}')

statement ok
INSERT INTO d VALUES(6, '{"a": {"b": [[2]]}}')

statement ok
INSERT INTO d VALUES(7, '{"a": "b", "c": "d"}')

statement ok
INSERT INTO d VALUES(8, '{"a": {"b":true}}')

statement ok
INSERT INTO d VALUES(9, '{"a": {"b":false}}')

statement ok
INSERT INTO d VALUES(10, '"a"')

statement ok
INSERT INTO d VALUES(11, 'null')

statement ok
INSERT INTO d VALUES(12, 'true')

statement ok
INSERT INTO d VALUES(13, 'false')

statement ok
INSERT INTO d VALUES(14, '1')

statement ok
INSERT INTO d VALUES(15, '1.23')

statement ok
INSERT INTO d VALUES(16, '[{"a": {"b": [1, [2]]}}, "d"]')

statement ok
INSERT INTO d VALUES(17, '{}')

statement ok
INSERT INTO d VALUES(18, '[]')

statement ok
INSERT INTO d VALUES (29,  NULL)

statement ok
INSERT INTO d VALUES (30,  '{"a": []}')

statement ok
INSERT INTO d VALUES (31,  '{"a": {"b": "c", "d": "e"}, "f": "g"}')

query IT
SELECT * from d where b @> NULL ORDER BY a;
----

query IT
SELECT * from d where b @> (NULL::JSONB) ORDER BY a;
----

query IT
SELECT * from d where b @>'{"a": "b"}' ORDER BY a;
----
1  {"a": "b"}
7  {"a": "b", "c": "d"}

query IT
SELECT * from d where b @> '{"a": {"b": [1]}}' ORDER BY a;
----
4  {"a": {"b": [1]}}
5  {"a": {"b": [1, [2]]}}

query IT
SELECT * from d where b @> '{"a": {"b": [[2]]}}' ORDER BY a;
----
5  {"a": {"b": [1, [2]]}}
6  {"a": {"b": [[2]]}}

query IT
SELECT * from d where b @> '{"a": {"b": true}}' ORDER BY a;
----
8  {"a": {"b": true}}

query IT
SELECT * from d where b @> '{"a": {"b": [[2]]}}' ORDER BY a;
----
5  {"a": {"b": [1, [2]]}}
6  {"a": {"b": [[2]]}}

query IT
SELECT * from d where b @>'[1]' ORDER BY a;
----
2  [1, 2, 3, 4, "foo"]

query IT
SELECT * from d where b @>'[{"a": {"b": [1]}}]' ORDER BY a;
----
16  [{"a": {"b": [1, [2]]}}, "d"]

statement ok
DELETE from d WHERE a=1;

query IT
SELECT * from d where b @>'{"a": "b"}' ORDER BY a;
----
7  {"a": "b", "c": "d"}

statement ok
PREPARE query (STRING, STRING) AS SELECT * from d where b->$1 = $2 ORDER BY a

query IT
EXECUTE query ('a', '"b"')
----
7  {"a": "b", "c": "d"}

statement ok
DELETE from d WHERE a=6;

query IT
SELECT * from d where b @> '{"a": {"b": [[2]]}}' ORDER BY a;
----
5  {"a": {"b": [1, [2]]}}

query IT
SELECT * from d where b @> '"a"' ORDER BY a;
----
10  "a"

query IT
SELECT * from d where b @> 'null' ORDER BY a;
----
11  null

query IT
SELECT * from d where b @> 'true' ORDER BY a;
----
12  true

query IT
SELECT * from d where b @> 'false' ORDER BY a;
----
13  false

query IT
SELECT * from d where b @> '1' ORDER BY a;
----
2   [1, 2, 3, 4, "foo"]
14  1

query IT
SELECT * from d where b @> '1.23' ORDER BY a;
----
15  1.23

query IT
SELECT * from d where b @> '{}' ORDER BY a;
----
3   {"a": {"b": "c"}}
4   {"a": {"b": [1]}}
5   {"a": {"b": [1, [2]]}}
7   {"a": "b", "c": "d"}
8   {"a": {"b": true}}
9   {"a": {"b": false}}
17  {}
30  {"a": []}
31  {"a": {"b": "c", "d": "e"}, "f": "g"}

query IT
SELECT * from d where b @> '[]' ORDER BY a;
----
2   [1, 2, 3, 4, "foo"]
16  [{"a": {"b": [1, [2]]}}, "d"]
18  []

statement ok
INSERT INTO d VALUES (19, '["a", "a"]')

query IT
SELECT * from d where b @> '["a"]' ORDER BY a;
----
19  ["a", "a"]

statement ok
INSERT INTO d VALUES (20, '[{"a": "a"}, {"a": "a"}]')

query IT
SELECT * from d where b @> '[{"a": "a"}]' ORDER BY a;
----
20  [{"a": "a"}, {"a": "a"}]

statement ok
INSERT INTO d VALUES (21,  '[[[["a"]]], [[["a"]]]]')

query IT
SELECT * from d where b @> '[[[["a"]]]]' ORDER BY a;
----
21  [[[["a"]]], [[["a"]]]]

statement ok
INSERT INTO d VALUES (22,  '[1,2,3,1]')

query IT
SELECT * from d where b @> '[[[["a"]]]]' ORDER BY a;
----
21  [[[["a"]]], [[["a"]]]]

query IT
SELECT * from d where b->'a' = '"b"'
----
7  {"a": "b", "c": "d"}

statement ok
INSERT INTO d VALUES (23,  '{"a": 123.123}')

statement ok
INSERT INTO d VALUES (24,  '{"a": 123.123000}')

query IT
SELECT * from d where b @> '{"a": 123.123}' ORDER BY a;
----
23  {"a": 123.123}
24  {"a": 123.123000}

query IT
SELECT * from d where b @> '{"a": 123.123000}' ORDER BY a;
----
23  {"a": 123.123}
24  {"a": 123.123000}

statement ok
INSERT INTO d VALUES (25,  '{"a": [{}]}')

statement ok
INSERT INTO d VALUES (26,  '[[], {}]')

query IT
SELECT * from d where b @> '{"a": [{}]}' ORDER BY a;
----
25  {"a": [{}]}


query IT
SELECT * from d where b @> '{"a": []}' ORDER BY a;
----
25  {"a": [{}]}
30  {"a": []}

query IT
SELECT * from d where b @> '[{}]' ORDER BY a;
----
16  [{"a": {"b": [1, [2]]}}, "d"]
20  [{"a": "a"}, {"a": "a"}]
26  [[], {}]

query IT
SELECT * from d where b @> '[[]]' ORDER BY a;
----
21  [[[["a"]]], [[["a"]]]]
26  [[], {}]

statement ok
INSERT INTO d VALUES (27,  '[true, false, null, 1.23, "a"]')

query IT
SELECT * from d where b @> 'true' ORDER BY a;
----
12  true
27  [true, false, null, 1.23, "a"]

query IT
SELECT * from d where b @> 'false' ORDER BY a;
----
13  false
27  [true, false, null, 1.23, "a"]

query IT
SELECT * from d where b @> '1.23' ORDER BY a;
----
15  1.23
27  [true, false, null, 1.23, "a"]

query IT
SELECT * from d where b @> '"a"' ORDER BY a;
----
10  "a"
19  ["a", "a"]
27  [true, false, null, 1.23, "a"]

query IT
SELECT * from d where b IS NULL;
----
29  NULL

query IT
SELECT * from d where b = NULL;
----

query IT
SELECT * from d where b @> NULL;
----

query IT
SELECT * from d where b @> 'null' ORDER BY a;
----
11  null
27  [true, false, null, 1.23, "a"]

query IT
SELECT * from d where b @> '{"a": {}}' ORDER BY a;
----
3   {"a": {"b": "c"}}
4   {"a": {"b": [1]}}
5   {"a": {"b": [1, [2]]}}
8   {"a": {"b": true}}
9   {"a": {"b": false}}
31  {"a": {"b": "c", "d": "e"}, "f": "g"}

query IT
SELECT * from d where b @> '{"a": []}' ORDER BY a;
----
25  {"a": [{}]}
30  {"a": []}

## Multi-path contains queries

query IT
SELECT * from d where b @> '{"a": {"b": "c"}, "f": "g"}'
----
31  {"a": {"b": "c", "d": "e"}, "f": "g"}

query IT
SELECT * from d where b @> '{"a": {"b": "c", "d": "e"}, "f": "g"}'
----
31  {"a": {"b": "c", "d": "e"}, "f": "g"}

query IT
SELECT * from d where b @> '{"c": "d", "a": "b"}'
----
7  {"a": "b", "c": "d"}

query IT
SELECT * from d where b @> '{"c": "d", "a": "b", "f": "g"}'
----

query IT
SELECT * from d where b @> '{"a": "b", "c": "e"}'
----

query IT
SELECT * from d where b @> '{"a": "e", "c": "d"}'
----

query IT
SELECT * from d where b @> '["d", {"a": {"b": [1]}}]'
----
16  [{"a": {"b": [1, [2]]}}, "d"]

query IT
SELECT * from d where b @> '["d", {"a": {"b": [[2]]}}]'
----
16  [{"a": {"b": [1, [2]]}}, "d"]

query IT
SELECT * from d where b @> '[{"a": {"b": [[2]]}}, "d"]'
----
16  [{"a": {"b": [1, [2]]}}, "d"]

# Filter with a fully-specified array.
query IT
SELECT * FROM d WHERE b @> '[1, 2]' ORDER BY a
----
2   [1, 2, 3, 4, "foo"]
22  [1, 2, 3, 1]

# Combine predicates with AND. Should have the same rows as b @> '[1, 2]'.
query I
SELECT a FROM d WHERE b @> '[1]' AND b @> '[2]' ORDER BY a
----
2
22

statement ok
INSERT INTO d VALUES (32, '[[1, 2]]');
INSERT INTO d VALUES (33, '[[1], [2]]')

# Filter with a nested array. This index expression is not tight.
query IT
SELECT * FROM d WHERE b @> '[[1, 2]]' ORDER BY a
----
32  [[1, 2]]

statement ok
CREATE TABLE users (
  profile_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  last_updated TIMESTAMP DEFAULT now(),
  user_profile JSONB
);

statement ok
INSERT INTO users (user_profile) VALUES  ('{"first_name": "Lola", "last_name": "Dog", "location": "NYC", "online" : true, "friends" : 547}'),
                                         ('{"first_name": "Ernie", "status": "Looking for treats", "location" : "Brooklyn"}');

statement ok
CREATE INVERTED INDEX dogs on users(user_profile);

statement error index "dogs" is inverted and cannot be used for this query
SELECT count(*) FROM users@dogs

query T
SELECT user_profile from users where user_profile @> '{"first_name":"Lola"}';
----
{"first_name": "Lola", "friends": 547, "last_name": "Dog", "location": "NYC", "online": true}

query T
SELECT user_profile from users where user_profile @> '{"first_name":"Ernie"}';
----
 {"first_name": "Ernie", "location": "Brooklyn", "status": "Looking for treats"}

statement ok
CREATE TABLE update_test (i INT PRIMARY KEY, j JSONB, INVERTED INDEX(j));

statement ok
INSERT INTO update_test VALUES (1, '0');

query IT
SELECT * from update_test WHERE j @> '0';
----
1 0

statement ok
UPDATE update_test SET j = '{"a":"b", "c":"d"}' WHERE i = 1;

query IT
SELECT * from update_test WHERE j @> '0';
----

query IT
SELECT * from update_test WHERE j @> '{"a":"b"}';
----
1 {"a": "b", "c": "d"}

statement ok
INSERT INTO update_test VALUES (2, '{"longKey1":"longValue1", "longKey2":"longValue2"}');

statement ok
UPDATE update_test SET j = ('"shortValue"') WHERE i = 2;

query IT
SELECT * from update_test where j @> '"shortValue"';
----
2 "shortValue"

query IT
SELECT * from update_test where j @> '{"longKey1":"longValue1"}}';
----

query IT
SELECT * from update_test where j @> '{"longKey2":"longValue2"}}';
----

statement ok
UPDATE update_test SET (i, j) = (10, '{"longKey1":"longValue1", "longKey2":"longValue2"}') WHERE i = 2;

statement ok
UPDATE update_test SET j = '{"a":"b", "a":"b"}' WHERE i = 1;

statement ok
UPDATE update_test SET (i, j) = (2, '["a", "a"]') WHERE i = 10;

statement ok
INSERT INTO update_test VALUES (3, '["a", "b", "c"]');

query IT
SELECT * from update_test where j @> '["a"]' ORDER BY i;
----
2 ["a", "a"]
3 ["a", "b", "c"]

statement ok
UPDATE update_test SET j = '["b", "c", "e"]' WHERE i = 3;

query IT
SELECT * from update_test where j @> '["a"]' ORDER BY i;
----
2 ["a", "a"]

query IT
SELECT * from update_test where j @> '["b"]' ORDER BY i;
----
3 ["b", "c", "e"]


statement ok
INSERT INTO update_test VALUES (4, '["a", "b"]');

statement ok
UPDATE update_test SET j = '["b", "a"]' WHERE i = 4;

query IT
SELECT * from update_test where j @> '["a"]' ORDER BY i;
----
2 ["a", "a"]
4 ["b", "a"]

query IT
SELECT * from update_test where j @> '["b"]' ORDER BY i;
----
3 ["b", "c", "e"]
4 ["b", "a"]

statement ok
UPSERT INTO update_test VALUES (4, '["a", "b"]');

query IT
SELECT * from update_test where j @> '["a"]' ORDER BY i;
----
2 ["a", "a"]
4 ["a", "b"]

query IT
SELECT * from update_test where j @> '["b"]' ORDER BY i;
----
3 ["b", "c", "e"]
4 ["a", "b"]


statement ok
UPSERT INTO update_test VALUES (3, '["c", "e", "f"]');

query IT
SELECT * from update_test where j @> '["c"]' ORDER BY i;
----
3  ["c", "e", "f"]

statement ok
CREATE TABLE del_cascade_test (
  delete_cascade INT NOT NULL REFERENCES update_test ON DELETE CASCADE
 ,j JSONB
 ,INVERTED INDEX(j)
);


statement ok
CREATE TABLE update_cascade_test (
 update_cascade INT NOT NULL REFERENCES update_test ON UPDATE CASCADE
 ,j JSONB
 ,INVERTED INDEX(j)
);

statement ok
INSERT INTO del_cascade_test(delete_cascade, j) VALUES (1, '["a", "b"]'), (2, '{"a":"b", "c":"d"}'), (3, '["b", "c"]')


query IT
SELECT * from del_cascade_test ORDER BY delete_cascade;
----
1  ["a", "b"]
2  {"a": "b", "c": "d"}
3  ["b", "c"]

statement ok
DELETE FROM update_test where j @> '["c"]'

query IT
SELECT * from del_cascade_test ORDER BY delete_cascade;
----
1  ["a", "b"]
2  {"a": "b", "c": "d"}

query IT
SELECT * from del_cascade_test ORDER BY delete_cascade;
----
1  ["a", "b"]
2  {"a": "b", "c": "d"}

statement ok
INSERT INTO update_test VALUES (3, '["a", "b", "c"]');

statement ok
INSERT INTO update_cascade_test(update_cascade, j) VALUES (1, '["a", "b"]'), (2, '{"a":"b", "c":"d"}'), (3, '["b", "c"]')

query IT
SELECT * from update_cascade_test ORDER BY update_cascade;
----
1  ["a", "b"]
2  {"a": "b", "c": "d"}
3  ["b", "c"]

statement error pq: update on table "update_test" violates foreign key constraint "fk_delete_cascade_ref_update_test" on table "del_cascade_test"\nDETAIL: Key \(i\)=\(1\) is still referenced from table "del_cascade_test"\.
UPDATE update_test SET (i,j)  = (5, '{"a":"b", "a":"b"}') WHERE i = 1;

statement ok
DROP TABLE del_cascade_test

statement ok
UPDATE update_test SET (i,j)  = (5, '{"a":"b", "a":"b"}') WHERE i = 1;

query IT
SELECT * from update_cascade_test ORDER BY update_cascade;
----
2  {"a": "b", "c": "d"}
3  ["b", "c"]
5  ["a", "b"]

# Test that inverted index validation correctly handles NULL values on creation (#38714)

statement ok
CREATE TABLE table_with_nulls (a JSON)

statement ok
INSERT INTO table_with_nulls VALUES (NULL)

statement ok
CREATE INVERTED INDEX ON table_with_nulls (a)

statement ok
DROP TABLE table_with_nulls

statement ok
DROP TABLE c

subtest json_fetch_val

statement ok
CREATE TABLE f (
  k INT PRIMARY KEY,
  j JSON,
  INVERTED INDEX i (j)
)

statement ok
INSERT INTO f VALUES
  (0, '{"a": 1}'),
  (1, '{"a": 10}'),
  (2, '{"b": 2}'),
  (3, '{"b": 2, "a": 1}'),
  (4, '{"a": 1, "c": 3}'),
  (5, '{"a": [1, 2]}'),
  (6, '{"a": {"b": 1}}'),
  (7, '{"a": {"b": 1, "d": 2}}'),
  (8, '{"a": {"d": 2}}'),
  (9, '{"a": {"b": [1, 2]}}'),
  (10, '{"a": {"b": {"c": 1}}}'),
  (11, '{"a": {"b": {"c": 1, "d": 2}}}}'),
  (12, '{"a": {"b": {"d": 2}}}}'),
  (13, '{"a": {"b": {"c": [1, 2]}}}'),
  (14, '{"a": {"b": {"c": [1, 2, 3]}}}'),
  (15, '{"a": []}'),
  (16, '{"a": {}}}'),
  (17, '{"a": {"b": "c"}}'),
  (18, '{"a": {"b": ["c", "d", "e"]}}'),
  (19, '{"a": ["b", "c", "d", "e"]}'),
  (20, '{"a": ["b", "e", "c", "d"]}'),
  (21, '{"z": {"a": "b", "c": "d"}}'),
  (22, '{"z": {"a": "b", "c": "d", "e": "f"}}'),
  (23, '{"a": "b", "x": ["c", "d", "e"]}}'),
  (24, '{"a": "b", "c": [{"d": 1}, {"e": 2}]}}'),
  (25, '{"a": {"b": "c", "d": "e"}}'),
  (26, '{"a": {"b": "c"}, "d": "e"}'),
  (27, '[1, 2, {"b": "c"}]'),
  (28, '[{"a": {"b": "c"}}, "d", "e"]'),
  (29, '{"a": null}'),
  (30, '{"a": [1, 2, null]}')

query T
SELECT j FROM f@i WHERE j->'a' = '1' ORDER BY k
----
{"a": 1}
{"a": 1, "b": 2}
{"a": 1, "c": 3}

query T
SELECT j FROM f@i WHERE j->'a' = '1' OR j->'b' = '2' ORDER BY k
----
{"a": 1}
{"b": 2}
{"a": 1, "b": 2}
{"a": 1, "c": 3}

query T
SELECT j FROM f@i WHERE j->'a' = '1' OR j @> '{"b": 2}' ORDER BY k
----
{"a": 1}
{"b": 2}
{"a": 1, "b": 2}
{"a": 1, "c": 3}

query T
SELECT j FROM f@i WHERE j->'a'->'b' = '1' ORDER BY k
----
{"a": {"b": 1}}
{"a": {"b": 1, "d": 2}}

query T
SELECT j FROM f@i WHERE j->'a'->'b'->'c' = '1' ORDER BY k
----
{"a": {"b": {"c": 1}}}
{"a": {"b": {"c": 1, "d": 2}}}

query T
SELECT j FROM f@i WHERE j->'a' = '[]' ORDER BY k
----
{"a": []}

query T
SELECT j FROM f@i WHERE j->'a' = '{}' ORDER BY k
----
{"a": {}}

query T
SELECT j FROM f@i WHERE j->'a' = '["b"]' ORDER BY k
----

query T
SELECT j FROM f@i WHERE j->'a' = '"b"' ORDER BY k
----
{"a": "b", "x": ["c", "d", "e"]}
{"a": "b", "c": [{"d": 1}, {"e": 2}]}

query T
SELECT j FROM f@i WHERE j->'a' = '{"b": "c"}' ORDER BY k
----
{"a": {"b": "c"}}
{"a": {"b": "c"}, "d": "e"}

query T
SELECT j FROM f@i WHERE j->'a'->'b'->'c' = '[1, 2]' ORDER BY k
----
{"a": {"b": {"c": [1, 2]}}}

query T
SELECT j FROM f@i WHERE j->'z' = '{"a": "b", "c": "d"}' ORDER BY k
----
{"z": {"a": "b", "c": "d"}}

query T
SELECT j FROM f@i WHERE j->'a' = '["b", "c", "d", "e"]' ORDER BY k
----
{"a": ["b", "c", "d", "e"]}

query T
SELECT j FROM f@i WHERE j->'a' = '["b", "c", "d", "e"]' OR  j->'a' = '["b", "e", "c", "d"]' ORDER BY k
----
{"a": ["b", "c", "d", "e"]}
{"a": ["b", "e", "c", "d"]}

query T
SELECT j FROM f@i WHERE j->'a' = '{"b": ["c", "d", "e"]}' ORDER BY k
----
{"a": {"b": ["c", "d", "e"]}}

query T
SELECT j FROM f@i WHERE j->'a'->'b' = '["c", "d", "e"]' ORDER BY k
----
{"a": {"b": ["c", "d", "e"]}}

query T
SELECT j FROM f@i WHERE j->'z'->'c' = '"d"' ORDER BY k
----
{"z": {"a": "b", "c": "d"}}
{"z": {"a": "b", "c": "d", "e": "f"}}

query T
SELECT j FROM f@i WHERE j->'z' = '{"c": "d"}' ORDER BY k
----

query T
SELECT j FROM f@i WHERE j->'a' = '"b"' AND j->'c' = '[{"d": 1}]' ORDER BY k
----

query T
SELECT j FROM f@i WHERE j->'a' = '"b"' AND j->'c' = '[{"d": 1}, {"e": 2}]' ORDER BY k
----
{"a": "b", "c": [{"d": 1}, {"e": 2}]}

query T
SELECT j FROM f@i WHERE j->'a' @> '"b"' ORDER BY k
----
{"a": ["b", "c", "d", "e"]}
{"a": ["b", "e", "c", "d"]}
{"a": "b", "x": ["c", "d", "e"]}
{"a": "b", "c": [{"d": 1}, {"e": 2}]}

query T
SELECT j FROM f@i WHERE j->'a'->'b' @> '1' ORDER BY k
----
{"a": {"b": 1}}
{"a": {"b": 1, "d": 2}}
{"a": {"b": [1, 2]}}

query T
SELECT j FROM f@i WHERE j->'a'->'b' @> '[1, 2]' ORDER BY k
----
{"a": {"b": [1, 2]}}

query T
SELECT j FROM f@i WHERE j->'a' <@ '"b"' ORDER BY k
----
{"a": "b", "x": ["c", "d", "e"]}
{"a": "b", "c": [{"d": 1}, {"e": 2}]}

query T
SELECT j FROM f@i WHERE j->'a'->'b' <@ '1' ORDER BY k
----
{"a": {"b": 1}}
{"a": {"b": 1, "d": 2}}

query T
SELECT j FROM f@i WHERE j->'a'->'b' <@ '[1, 2]' ORDER BY k
----
{"a": {"b": 1}}
{"a": {"b": 1, "d": 2}}
{"a": {"b": [1, 2]}}

query T
SELECT j FROM f@i WHERE j->'a' @> '"b"' AND j->'a' @> '["c"]' ORDER BY k
----
{"a": ["b", "c", "d", "e"]}
{"a": ["b", "e", "c", "d"]}

query T
SELECT j FROM f@i WHERE j->'a' @> '[1, 2]' OR j->'a'->'b' @> '[1, 2]' ORDER BY k
----
{"a": [1, 2]}
{"a": {"b": [1, 2]}}
{"a": [1, 2, null]}

query T
SELECT j FROM f@i WHERE j->'a' <@ '{"b": [1, 2]}' AND j->'a'->'b' <@ '[1, 2]' ORDER BY k
----
{"a": {"b": [1, 2]}}

query T
SELECT j FROM f@i WHERE j->'a' <@ '["b"]' OR j->'a'->'b' <@ '[1, 2]' ORDER BY k
----
{"a": {"b": 1}}
{"a": {"b": 1, "d": 2}}
{"a": {"b": [1, 2]}}
{"a": []}
{"a": "b", "x": ["c", "d", "e"]}
{"a": "b", "c": [{"d": 1}, {"e": 2}]}

query T
SELECT j FROM f@i WHERE j->'a' @> '"b"' AND j->'a' <@ '["b", "c", "d", "e"]' ORDER BY k
----
{"a": []}
{"a": ["b", "c", "d", "e"]}
{"a": ["b", "e", "c", "d"]}
{"a": "b", "x": ["c", "d", "e"]}
{"a": "b", "c": [{"d": 1}, {"e": 2}]}

query T
SELECT j FROM f@i WHERE j->'a' @> '"b"' OR j->'a'->'b' <@ '[1, 2]' ORDER BY k
----
{"a": {"b": 1}}
{"a": {"b": 1, "d": 2}}
{"a": {"b": [1, 2]}}
{"a": ["b", "c", "d", "e"]}
{"a": ["b", "e", "c", "d"]}
{"a": "b", "x": ["c", "d", "e"]}
{"a": "b", "c": [{"d": 1}, {"e": 2}]}

query T
SELECT j FROM f@i WHERE j->'a' @> 'null' ORDER BY k
----
{"a": null}
{"a": [1, 2, null]}

query T
SELECT j FROM f@i WHERE j->'a' <@ 'null' ORDER BY k
----
{"a": null}

subtest arrays

statement ok
CREATE TABLE c (
  id INT PRIMARY KEY,
  foo INT[],
  bar STRING[],
  INVERTED INDEX (foo),
  FAMILY "primary" (id, foo, bar)
)

statement ok
INSERT INTO c VALUES(0, NULL, NULL)

statement ok
INSERT INTO c VALUES(1, ARRAY[], ARRAY['foo', 'bar', 'baz'])

statement ok
CREATE INDEX ON c USING GIN (bar)

query TT
SHOW CREATE TABLE c
----
c  CREATE TABLE public.c (
   id INT8 NOT NULL,
   foo INT8[] NULL,
   bar STRING[] NULL,
   CONSTRAINT "primary" PRIMARY KEY (id ASC),
   INVERTED INDEX c_foo_idx (foo),
   INVERTED INDEX c_bar_idx (bar),
   FAMILY "primary" (id, foo, bar)
)

query ITT
SELECT * from c WHERE bar @> ARRAY['foo']
----
1  {}  {foo,bar,baz}

query ITT
SELECT * from c WHERE bar @> ARRAY['bar', 'baz']
----
1  {}  {foo,bar,baz}

query ITT
SELECT * from c WHERE bar @> ARRAY['bar', 'qux']
----

statement ok
INSERT INTO c VALUES(2, NULL, NULL)

statement ok
INSERT INTO c VALUES(3, ARRAY[0,1,NULL], ARRAY['a',NULL,'b',NULL])

statement ok
INSERT INTO c VALUES(4, ARRAY[1,2,3], ARRAY['b',NULL,'c'])

statement ok
INSERT INTO c VALUES(5, ARRAY[], ARRAY[NULL, NULL])

# Create a second inverted index on c, to test backfills.
statement ok
CREATE INVERTED INDEX ON c(foo)

statement ok
CREATE INVERTED INDEX ON c(bar)

query ITT
SELECT * FROM c WHERE foo @> ARRAY[0]
----
3  {0,1,NULL}  {a,NULL,b,NULL}

query error unsupported comparison operator
SELECT * FROM c WHERE foo @> 0

query ITT
SELECT * FROM c WHERE foo @> ARRAY[1] ORDER BY id
----
3  {0,1,NULL}  {a,NULL,b,NULL}
4  {1,2,3}     {b,NULL,c}

# This is expected, although it looks odd, because in SQL,
# ARRAY[NULL] @> ARRAY[NULL] returns false.
query ITT
SELECT * FROM c WHERE foo @> ARRAY[NULL]::INT[]
----

query ITT
SELECT * FROM c WHERE bar @> ARRAY['a']
----
3  {0,1,NULL}  {a,NULL,b,NULL}

query ITT
SELECT * FROM c WHERE bar @> ARRAY['b'] ORDER BY id
----
3  {0,1,NULL}  {a,NULL,b,NULL}
4  {1,2,3}     {b,NULL,c}

query ITT
SELECT * FROM c WHERE bar @> ARRAY['c']
----
4  {1,2,3}  {b,NULL,c}

query ITT
SELECT * FROM c WHERE bar @> ARRAY[]::TEXT[] ORDER BY id
----
1  {}          {foo,bar,baz}
3  {0,1,NULL}  {a,NULL,b,NULL}
4  {1,2,3}     {b,NULL,c}
5  {}          {NULL,NULL}

query ITT
SELECT * FROM c WHERE foo @> '{1, 2}' ORDER BY id
----
4  {1,2,3}  {b,NULL,c}

subtest contained_by_arrays

statement ok
CREATE TABLE cb (
  id INT PRIMARY KEY,
  numbers INT[],
  words STRING[],
  INVERTED INDEX n (numbers),
  INVERTED INDEX w (words)
)

statement ok
INSERT INTO cb VALUES
  (0, ARRAY[], ARRAY[]),
  (1, ARRAY[0], ARRAY[NULL]),
  (2, ARRAY[1], ARRAY['cat']),
  (3, ARRAY[0,1], ARRAY['mouse']),
  (4, ARRAY[NULL], ARRAY['cat', 'mouse']),
  (5, ARRAY[0,1,2], ARRAY['cat', NULL, 'mouse']),
  (6, ARRAY[3,4,5], ARRAY['rat']),
  (7, ARRAY[1,2,1], ARRAY['rat', NULL]),
  (8, ARRAY[0,1,NULL], ARRAY[''])

query T
SELECT numbers FROM cb@n WHERE numbers <@ ARRAY[]::INT[]
----
{}

query T
SELECT numbers FROM cb@n WHERE numbers <@ ARRAY[1]
----
{}
{1}

query T
SELECT numbers FROM cb@n WHERE numbers <@ ARRAY[0,1,2]
----
{}
{0}
{1}
{0,1}
{0,1,2}
{1,2,1}

query T
SELECT numbers FROM cb@n WHERE numbers <@ ARRAY[1,2,3]
----
{}
{1}
{1,2,1}

query T
SELECT numbers FROM cb@n WHERE numbers <@ ARRAY[0,1,NULL]
----
{}
{0}
{1}
{0,1}

query T
SELECT numbers FROM cb@n WHERE numbers <@ ARRAY[NULL]::INT[]
----
{}

query T
SELECT words FROM cb@w WHERE words <@ ARRAY[]::STRING[]
----
{}

query T
SELECT words FROM cb@w WHERE words <@ ARRAY['']::STRING[]
----
{}
{""}

query T
SELECT words FROM cb@w WHERE words <@ ARRAY[NULL]::STRING[]
----
{}


query T
SELECT words FROM cb@w WHERE words <@ ARRAY['cat']
----
{}
{cat}


query T
SELECT words FROM cb@w WHERE words <@ ARRAY['cat', 'mouse']
----
{}
{cat}
{mouse}
{cat,mouse}

query T
SELECT words FROM cb@w WHERE words <@ ARRAY['cat', 'mouse', NULL]
----
{}
{cat}
{mouse}
{cat,mouse}

query T
SELECT words FROM cb@w WHERE words <@ ARRAY[NULL, 'rat']
----
{}
{rat}

subtest contained_by_json

statement ok
CREATE TABLE cb2 (
  k INT PRIMARY KEY,
  j JSON,
  INVERTED INDEX i (j)
)

statement ok
INSERT INTO cb2 VALUES
  (0, '{}'),
  (1, '[]'),
  (2, '1'),
  (3, '"a"'),
  (4, 'true'),
  (5, 'null'),
  (6, '{"a": "b"}'),
  (7, '{"a": {"b": "c", "d": "e"}}'),
  (8, '{"a": {"b": "c"}, "d": "e"}'),
  (9, '{"a": {"b": [1, 2]}}'),
  (10, '{"a": {"b": {"c": [1, 2, 3]}}}'),
  (11, '{"a": {"b": {"c": {"d": "e"}}}}'),
  (12, '{"a": {"b": {"d": 2}}}}'),
  (13, '{"a": [{"b": {"c": [1, 2]}}]}'),
  (14, '{"b": {"c": [1, 2]}}'),
  (15, '{"a": []}'),
  (16, '{"a": {}}}'),
  (17, '{"a": [[2]]}'),
  (18, '[[2]]'),
  (19, '[1, 2]'),
  (20, '[1, 2, 3]'),
  (21, '[{}]'),
  (22, '[{"a": "b"}]'),
  (23, '[{"a": "b", "c": ["d", "e"]}]'),
  (24, '[[1], [2]]'),
  (25, '[[1, 2]]'),
  (26, '["a", "b", "b"]'),
  (27, '[1, 2, {"b": "c"}]'),
  (28, '[{"a": {"b": "c"}}, "d", "e"]'),
  (29, '{"a": {"d": null}}'),
  (30, '{"d": null}'),
  (31, '[null]'),
  (32, '[[], null]'),
  (33, '[null, []]'),
  (34, '[null, {}]'),
  (35, '[[null]]'),
  (36, '[1, 2, null]')

query T
SELECT j FROM cb2@i WHERE j <@ '{}' ORDER BY k
----
{}

query T
SELECT j FROM cb2@i WHERE j <@ '[]' ORDER BY k
----
[]

query T
SELECT j FROM cb2@i WHERE j <@ '1' ORDER BY k
----
1

query T
SELECT j FROM cb2@i WHERE j <@ '"a"' ORDER BY k
----
"a"

query T
SELECT j FROM cb2@i WHERE j <@ 'null' ORDER BY k
----
null

query T
SELECT j FROM cb2@i WHERE j <@ 'true' ORDER BY k
----
true

query T
SELECT j FROM cb2@i WHERE j <@ '{"a": "b"}' ORDER BY k
----
{}
{"a": "b"}

query T
SELECT j FROM cb2@i WHERE j <@ '{"a": [1]}' ORDER BY k
----
{}
{"a": []}

query T
SELECT j FROM cb2@i WHERE j <@ '[null, 1, 2, 3, "d"]' ORDER BY k
----
[]
1
null
[1, 2]
[1, 2, 3]
[null]
[1, 2, null]

query T
SELECT j FROM cb2@i WHERE j <@ '[[1, 2], null]' ORDER BY k
----
[]
null
[[2]]
[[1], [2]]
[[1, 2]]
[null]
[[], null]
[null, []]

query T
SELECT j FROM cb2@i WHERE j <@ '{"a": {"b": [1, 2], "d": null}}' ORDER BY k
----
{}
{"a": {"b": [1, 2]}}
{"a": {}}
{"a": {"d": null}}

query T
SELECT j FROM cb2@i WHERE j <@ '[[1, 2, {"hello": 3}], {"a": "b"}]' ORDER BY k
----
[]
[[2]]
[{}]
[{"a": "b"}]
[[1], [2]]
[[1, 2]]

query T
SELECT j FROM cb2@i WHERE j <@ '[{}, []]' ORDER BY k
----
[]
[{}]

query T
SELECT j FROM cb2@i WHERE j <@ '[{"a": "b"}, [1, 2]]' ORDER BY k
----
[]
[[2]]
[{}]
[{"a": "b"}]
[[1], [2]]
[[1, 2]]

query T
SELECT j FROM cb2@i WHERE j <@ '[[1], [2]]' ORDER BY k
----
[]
[[2]]
[[1], [2]]