github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/zigzag_join (about)

     1  # LogicTest: fakedist
     2  
     3  # ------------------------------------------------------------------------------
     4  # Zigzag join tests on non-inverted indexes.
     5  # ------------------------------------------------------------------------------
     6  
     7  statement ok
     8  CREATE TABLE a (n INT PRIMARY KEY, a INT, b INT, c STRING, INDEX a_idx(a), INDEX b_idx(b));
     9  INSERT INTO a SELECT a,a,a%3,'foo' FROM generate_series(1,10) AS g(a) ;
    10  SET enable_zigzag_join = true
    11  
    12  query III rowsort
    13  SELECT n,a,b FROM a WHERE a = 4 AND b = 1
    14  ----
    15  4  4  1
    16  
    17  query III rowsort
    18  SELECT n,a,b FROM a WHERE a = 5 AND b = 2
    19  ----
    20  5  5  2
    21  
    22  query IIIT rowsort
    23  SELECT * FROM a WHERE a = 4 AND b = 1
    24  ----
    25  4  4  1  foo
    26  
    27  query IIIT rowsort
    28  SELECT * FROM a WHERE a = 4 AND b = 2
    29  ----
    30  
    31  query IIIT rowsort
    32  SELECT * FROM a WHERE a = 5 AND b = 2 AND c = 'foo'
    33  ----
    34  5  5  2  foo
    35  
    36  # Turn off zigzag joins and verify output.
    37  statement ok
    38  SET enable_zigzag_join = false
    39  
    40  query III rowsort
    41  SELECT n,a,b FROM a WHERE a = 4 AND b = 1
    42  ----
    43  4  4  1
    44  
    45  query III rowsort
    46  SELECT n,a,b FROM a WHERE a = 5 AND b = 2
    47  ----
    48  5  5  2
    49  
    50  statement ok
    51  SET enable_zigzag_join = true
    52  
    53  # Regression test for 42164 ("invalid indexIdx" error).
    54  statement ok
    55  DROP INDEX a@a_idx;
    56  DROP INDEX a@b_idx;
    57  CREATE INDEX c_idx ON a(c);
    58  CREATE INDEX a_idx_2 ON a(a);
    59  CREATE INDEX b_idx_2 ON a(b);
    60  SELECT n,a,b FROM a WHERE a = 4 AND b = 1;
    61  
    62  # Regression test for 48003 ("non-values node passed as fixed value to zigzag
    63  # join" error).
    64  statement ok
    65  SELECT n FROM a WHERE b = 1 AND (((a < 1) AND (a > 1)) OR (a >= 2 AND a <= 2))
    66  
    67  # ------------------------------------------------------------------------------
    68  # Zigzag join tests on inverted indexes.
    69  # ------------------------------------------------------------------------------
    70  
    71  statement ok
    72  CREATE TABLE d (
    73    a INT PRIMARY KEY,
    74    b JSONB
    75  )
    76  
    77  statement ok
    78  CREATE INVERTED INDEX foo_inv ON d(b)
    79  
    80  statement ok
    81  SHOW INDEX FROM d
    82  
    83  statement ok
    84  INSERT INTO d VALUES(1, '{"a": "b"}')
    85  
    86  statement ok
    87  INSERT INTO d VALUES(2, '[1,2,3,4, "foo"]')
    88  
    89  statement ok
    90  INSERT INTO d VALUES(3, '{"a": {"b": "c"}}')
    91  
    92  statement ok
    93  INSERT INTO d VALUES(4, '{"a": {"b": [1]}}')
    94  
    95  statement ok
    96  INSERT INTO d VALUES(5, '{"a": {"b": [1, [2]]}}')
    97  
    98  statement ok
    99  INSERT INTO d VALUES(6, '{"a": {"b": [[2]]}}')
   100  
   101  statement ok
   102  INSERT INTO d VALUES(7, '{"a": "b", "c": "d"}')
   103  
   104  statement ok
   105  INSERT INTO d VALUES(8, '{"a": {"b":true}}')
   106  
   107  statement ok
   108  INSERT INTO d VALUES(9, '{"a": {"b":false}}')
   109  
   110  statement ok
   111  INSERT INTO d VALUES(10, '"a"')
   112  
   113  statement ok
   114  INSERT INTO d VALUES(11, 'null')
   115  
   116  statement ok
   117  INSERT INTO d VALUES(12, 'true')
   118  
   119  statement ok
   120  INSERT INTO d VALUES(13, 'false')
   121  
   122  statement ok
   123  INSERT INTO d VALUES(14, '1')
   124  
   125  statement ok
   126  INSERT INTO d VALUES(15, '1.23')
   127  
   128  statement ok
   129  INSERT INTO d VALUES(16, '[{"a": {"b": [1, [2]]}}, "d"]')
   130  
   131  statement ok
   132  INSERT INTO d VALUES(17, '{}')
   133  
   134  statement ok
   135  INSERT INTO d VALUES(18, '[]')
   136  
   137  statement ok
   138  INSERT INTO d VALUES (29,  NULL)
   139  
   140  statement ok
   141  INSERT INTO d VALUES (30,  '{"a": []}')
   142  
   143  statement ok
   144  INSERT INTO d VALUES (31,  '{"a": {"b": "c", "d": "e"}, "f": "g"}')
   145  
   146  ## Multi-path contains queries with zigzag joins enabled.
   147  
   148  query IT
   149  SELECT * from d where b @> '{"a": {"b": "c"}, "f": "g"}'
   150  ----
   151  31  {"a": {"b": "c", "d": "e"}, "f": "g"}
   152  
   153  query IT
   154  SELECT * from d where b @> '{"a": {"b": "c", "d": "e"}, "f": "g"}'
   155  ----
   156  31  {"a": {"b": "c", "d": "e"}, "f": "g"}
   157  
   158  query IT
   159  SELECT * from d where b @> '{"c": "d", "a": "b"}'
   160  ----
   161  7  {"a": "b", "c": "d"}
   162  
   163  query IT
   164  SELECT * from d where b @> '{"c": "d", "a": "b", "f": "g"}'
   165  ----
   166  
   167  query IT
   168  SELECT * from d where b @> '{"a": "b", "c": "e"}'
   169  ----
   170  
   171  query IT
   172  SELECT * from d where b @> '{"a": "e", "c": "d"}'
   173  ----
   174  
   175  query IT
   176  SELECT * from d where b @> '["d", {"a": {"b": [1]}}]'
   177  ----
   178  16  [{"a": {"b": [1, [2]]}}, "d"]
   179  
   180  query IT
   181  SELECT * from d where b @> '["d", {"a": {"b": [[2]]}}]'
   182  ----
   183  16  [{"a": {"b": [1, [2]]}}, "d"]
   184  
   185  query IT
   186  SELECT * from d where b @> '[{"a": {"b": [[2]]}}, "d"]'
   187  ----
   188  16  [{"a": {"b": [1, [2]]}}, "d"]