8DiNmTWE

1CREATE TABLE IF NOT EXISTS any_vs_match (
2  id INTEGER,
3  col_a ARRAY(STRING),
4  col_b STRING INDEX USING FULLTEXT
5);
6
7DELETE FROM any_vs_match;
8
9INSERT INTO any_vs_match (id, col_a, col_b) VALUES (5, NULL, NULL);
10INSERT INTO any_vs_match (id, col_a, col_b) VALUES (1, [], '');
11INSERT INTO any_vs_match (id, col_a, col_b) VALUES (2, ['1', '2'], '1 2');
12INSERT INTO any_vs_match (id, col_a, col_b) VALUES (3, ['2', '3'], '2 3');
13INSERT INTO any_vs_match (id, col_a, col_b) VALUES (4, ['1', '3'], '1 3');
14
15REFRESH TABLE any_vs_match;
16
17SELECT COUNT(*) FROM any_vs_match;
18SELECT COUNT(*) FROM any_vs_match WHERE col_a IS NULL;
19SELECT COUNT(*) FROM any_vs_match WHERE col_b IS NULL;
20
21SELECT COUNT(*) FROM any_vs_match WHERE '1' = ANY(col_a);
22SELECT COUNT(*) FROM any_vs_match WHERE match(col_b, '1');
23
24SELECT COUNT(*) FROM any_vs_match WHERE NOT '1' = ANY(col_a);
25SELECT COUNT(*) FROM any_vs_match WHERE NOT match(col_b, '1');