# Copyright 2015 - 2019 The Cockroach Authors. All rights reserved. # Copyright Materialize, Inc. and contributors. All rights reserved. # # Use of this software is governed by the Business Source License # included in the LICENSE file at the root of this repository. # # As of the Change Date specified in that file, in accordance with # the Business Source License, use of this software will be governed # by the Apache License, Version 2.0. # # This file is derived from the logic test suite in CockroachDB. The # original file was retrieved on June 10, 2019 from: # # https://github.com/cockroachdb/cockroach/blob/d2f7fbf5dd1fc1a099bbad790a2e1f7c60a66cc3/pkg/sql/logictest/testdata/logic_test/select_index # # The original source code is subject to the terms of the Apache # 2.0 license, a copy of which can be found in the LICENSE file at the # root of this repository. mode cockroach simple conn=mz_system,user=mz_system ALTER SYSTEM SET unsafe_enable_table_keys = true ---- COMPLETE 0 statement ok CREATE TABLE t ( a INT PRIMARY KEY, b INT, c INT ) statement ok INSERT INTO t VALUES (1, 2, 3), (3, 4, 5), (5, 6, 7) query I rowsort SELECT a FROM t WHERE a < 4.0 ---- 1 3 query I SELECT b FROM t WHERE c > 4.0 AND a < 4 ---- 4 statement ok CREATE TABLE ab ( s STRING, i INT ) statement ok INSERT INTO ab VALUES ('a', 1), ('b', 1), ('c', 1) query IT rowsort SELECT i, s FROM ab WHERE (i, s) < (1, 'c') ---- 1 a 1 b statement ok CREATE INDEX baz ON ab (i, s) query IT rowsort SELECT i, s FROM ab@baz WHERE (i, s) < (1, 'c') ---- 1 a 1 b # Issue materialize#14426: verify we don't have an internal filter that contains "a IN ()" # (which causes an error in DistSQL due to expression serialization). statement ok CREATE TABLE tab0( k INT PRIMARY KEY, a INT, b INT ) query I SELECT k FROM tab0 WHERE (a IN (6) AND a > 6) OR b >= 4 ---- # Regression tests for materialize#12022 statement ok CREATE TABLE t12022 ( c1 INT, c2 BOOL, ); statement ok INSERT INTO t12022 VALUES (1, NULL), (1, false), (1, true), (2, NULL), (2, false), (2, true); query IB SELECT * FROM t12022@i WHERE (c1, c2) > (1, NULL) ORDER BY (c1, c2); ---- 2 NULL 2 false 2 true query IB SELECT * FROM t12022@i WHERE (c1, c2) > (1, false) ORDER BY (c1, c2); ---- 1 true 2 NULL 2 false 2 true query IB SELECT * FROM t12022@i WHERE (c1, c2) > (1, true) ORDER BY (c1, c2); ---- 2 NULL 2 false 2 true query IB SELECT * FROM t12022@i WHERE (c1, c2) < (2, NULL) ORDER BY (c1, c2); ---- 1 NULL 1 false 1 true query IB SELECT * FROM t12022@i WHERE (c1, c2) < (2, false) ORDER BY (c1, c2); ---- 1 NULL 1 false 1 true query IB SELECT * FROM t12022@i WHERE (c1, c2) < (2, true) ORDER BY (c1, c2); ---- 1 NULL 1 false 1 true 2 false # Regression test for materialize#20035. statement ok CREATE TABLE favorites ( id INT NOT NULL DEFAULT unique_rowid(), resource_type STRING(30) NOT NULL, resource_key STRING(255) NOT NULL, device_group STRING(30) NOT NULL, customerid INT NOT NULL, jurisdiction STRING(2) NOT NULL, brand STRING(255) NOT NULL, created_ts TIMESTAMP NULL, guid_id STRING(100) NOT NULL, locale STRING(10) NOT NULL DEFAULT NULL, ) statement ok INSERT INTO favorites (customerid, guid_id, resource_type, device_group, jurisdiction, brand, locale, resource_key) VALUES (1, '1', 'GAME', 'web', 'MT', 'xxx', 'en_GB', 'tp'), (2, '2', 'GAME', 'web', 'MT', 'xxx', 'en_GB', 'ts'), (3, '3', 'GAME', 'web', 'MT', 'xxx', 'en_GB', 'ts1'), (4, '4', 'GAME', 'web', 'MT', 'xxx', 'en_GB', 'ts2'), (5, '5', 'GAME', 'web', 'MT', 'xxx', 'en_GB', 'ts3'), (6, '6', 'GAME', 'web', 'MT', 'xxx', 'en_GB', 'ts4') query TI rowsort SELECT resource_key, count(resource_key) total FROM favorites f1 WHERE f1.jurisdiction = 'MT' AND f1.brand = 'xxx' AND f1.resource_type = 'GAME' AND f1.device_group = 'web' AND f1.locale = 'en_GB' AND f1.resource_key IN ('ts', 'ts2', 'ts3') GROUP BY resource_key ORDER BY total DESC ---- ts 1 ts2 1 ts3 1 statement ok CREATE TABLE abcd ( a INT, b INT, c INT, d INT, ) # Regression tests for materialize#20362 (IS NULL handling). statement ok INSERT INTO abcd VALUES (NULL, NULL, NULL), (NULL, NULL, 1), (NULL, NULL, 5), (NULL, NULL, 10), (NULL, 1, NULL), (NULL, 1, 1), (NULL, 1, 5), (NULL, 1, 10), (NULL, 5, NULL), (NULL, 5, 1), (NULL, 5, 5), (NULL, 5, 10), (NULL, 10, NULL), (NULL, 10, 1), (NULL, 10, 5), (NULL, 10, 10), (1, NULL, NULL), (1, NULL, 1), (1, NULL, 5), (1, NULL, 10), (1, 1, NULL), (1, 1, 1), (1, 1, 5), (1, 1, 10), (1, 5, NULL), (1, 5, 1), (1, 5, 5), (1, 5, 10), (1, 10, NULL), (1, 10, 1), (1, 10, 5), (1, 10, 10) query IIII rowsort SELECT * FROM abcd@abcd WHERE a IS NULL AND b > 5 ---- NULL 10 NULL NULL NULL 10 1 NULL NULL 10 5 NULL NULL 10 10 NULL query IIII rowsort SELECT * FROM abcd@abcd WHERE a IS NULL AND b < 5 ---- NULL 1 NULL NULL NULL 1 1 NULL NULL 1 5 NULL NULL 1 10 NULL query IIII partialsort(1,2) SELECT * FROM abcd@abcd WHERE a IS NULL ORDER BY b ---- NULL NULL NULL NULL NULL NULL 1 NULL NULL NULL 5 NULL NULL NULL 10 NULL NULL 1 NULL NULL NULL 1 1 NULL NULL 1 5 NULL NULL 1 10 NULL NULL 5 NULL NULL NULL 5 1 NULL NULL 5 5 NULL NULL 5 10 NULL NULL 10 NULL NULL NULL 10 1 NULL NULL 10 5 NULL NULL 10 10 NULL query IIII SELECT * FROM abcd@abcd WHERE a = 1 AND b IS NULL AND c > 0 AND c < 10 ORDER BY c ---- 1 NULL 1 NULL 1 NULL 5 NULL # Regression test for materialize#21831. statement ok CREATE TABLE str (k INT PRIMARY KEY, v STRING) statement ok INSERT INTO str VALUES (1, 'A'), (4, 'AB'), (2, 'ABC'), (5, 'ABCD'), (3, 'ABCDEZ'), (9, 'ABD') query IT rowsort SELECT k, v FROM str WHERE v LIKE 'ABC%' ---- 2 ABC 5 ABCD 3 ABCDEZ query IT rowsort SELECT k, v FROM str WHERE v LIKE 'ABC%Z' ---- 3 ABCDEZ query IT rowsort SELECT k, v FROM str WHERE v SIMILAR TO 'ABC_*' ---- 2 ABC 5 ABCD 3 ABCDEZ # Regression tests for materialize#22670. statement ok CREATE TABLE xy (x INT, y INT) statement ok CREATE INDEX xy_idx ON xy (x, y) statement ok INSERT INTO xy VALUES (NULL, NULL), (1, NULL), (NULL, 1), (1, 1) query II rowsort SELECT * FROM xy WHERE x IN (NULL, 1, 2) ---- 1 NULL 1 1 statement ok CREATE TABLE ef (e INT, f INT) statement ok INSERT INTO ef VALUES (NULL, 1), (1, 1) query I rowsort SELECT e FROM ef WHERE f > 0 AND f < 2 ORDER BY f ---- NULL 1 query II SELECT * FROM xy WHERE (x, y) IN ((NULL, NULL), (1, NULL), (NULL, 1), (1, 1), (1, 2)) ---- 1 1 # Test index constraints for IS (NOT) TRUE/FALSE. statement ok CREATE TABLE bool1 ( a BOOL, ); INSERT INTO bool1 VALUES (NULL), (TRUE), (FALSE) query B SELECT * FROM bool1 WHERE a IS NULL ---- NULL query B rowsort SELECT * FROM bool1 WHERE a IS NOT NULL ---- false true query B SELECT * FROM bool1 WHERE a IS TRUE ---- true query B rowsort SELECT * FROM bool1 WHERE a IS NOT TRUE ---- NULL false query B SELECT * FROM bool1 WHERE a IS FALSE ---- false query B rowsort SELECT * FROM bool1 WHERE a IS NOT FALSE ---- NULL true statement ok CREATE TABLE bool2 ( a BOOL NOT NULL, ); INSERT INTO bool2 VALUES (TRUE), (FALSE) query B SELECT * FROM bool2 WHERE a IS NULL ---- query B rowsort SELECT * FROM bool2 WHERE a IS NOT NULL ---- false true query B SELECT * FROM bool2 WHERE a IS TRUE ---- true query B SELECT * FROM bool2 WHERE a IS NOT TRUE ---- false query B SELECT * FROM bool2 WHERE a IS FALSE ---- false query B SELECT * FROM bool2 WHERE a IS NOT FALSE ---- true # Test index constraints for IS (NOT) DISTINCT FROM on an integer column. statement ok CREATE TABLE int ( a INT, ); INSERT INTO int VALUES (NULL), (0), (1), (2) query I SELECT * FROM int WHERE a IS NOT DISTINCT FROM 2 ---- 2 query I rowsort SELECT * FROM int WHERE a IS DISTINCT FROM 2 ---- NULL 0 1 # ------------------------------------------------------------------------------ # Non-covering index # ------------------------------------------------------------------------------ statement ok CREATE TABLE noncover ( a INT PRIMARY KEY, b INT, c INT, d INT, ) statement ok INSERT INTO noncover VALUES (1, 2, 3, 4), (5, 6, 7, 8) query IIII SELECT * FROM noncover WHERE b = 2 ---- 1 2 3 4 query IIII SET tracing=on, kv; SELECT * FROM noncover WHERE b = 2; SET tracing=off ---- 1 2 3 4 # Verify that the index join span created doesn't include any potential child # interleaved tables. We look only for spans with the primary prefix to avoid # inconsistency between the fakedist and local test configurations. query T rowsort SELECT message FROM [SHOW KV TRACE FOR SESSION] WHERE message LIKE 'Scan /Table/65/1%' ---- Scan /Table/65/1/1{-/#} # Subset of output columns, not including tested column. query II SELECT a, d FROM noncover WHERE b=2 ---- 1 4 # Subset of output columns, not including tested column or order by column. query I SELECT a FROM noncover WHERE b=2 ORDER BY c DESC ---- 1 # Regression: panic when projecting non-covered column in sorted index join. query III SELECT a, b, d FROM noncover WHERE b=2 ORDER BY b ---- 1 2 4 # Use non-covered column in filtered and sorted index join. query II SELECT a, b FROM noncover WHERE b=2 AND d>3 ORDER BY b ---- 1 2 query IIII SELECT * FROM noncover WHERE c = 7 ---- 5 6 7 8 query IIII SELECT * FROM noncover WHERE c > 0 ORDER BY c DESC ---- 5 6 7 8 1 2 3 4 query IIII SELECT * FROM noncover WHERE c > 0 AND d = 8 ---- 5 6 7 8 # Contradiction query IIII SELECT * FROM noncover WHERE b = 5 AND b <> 5 ---- # Contradiction with remainder filter query IIII SELECT * FROM noncover WHERE b = 5 AND b <> 5 AND d>100 ---- # ------------------------------------------------------------------------------ # These tests verify that while we are joining an index with the table, we # evaluate what parts of the filter we can using the columns in the index # to avoid unnecessary lookups in the table. # ------------------------------------------------------------------------------ statement ok CREATE TABLE t2 ( a INT PRIMARY KEY, b INT, c INT, s STRING, INDEX bc (b, c), FAMILY (a), FAMILY (b), FAMILY (c), FAMILY (s) ) statement ok INSERT INTO t2 VALUES (1, 1, 1, '11'), (2, 1, 2, '12'), (3, 1, 3, '13'), (4, 2, 1, '21'), (5, 2, 2, '22'), (6, 2, 3, '23'), (7, 3, 1, '31'), (8, 3, 2, '32'), (9, 3, 3, '33') query I rowsort SELECT a FROM t2 WHERE b = 2 OR ((b BETWEEN 2 AND 1) AND ((s != 'a') OR (s = 'a'))) ---- 4 5 6 statement ok CREATE TABLE t3 (k INT PRIMARY KEY, v INT, w INT, INDEX v(v)) statement ok INSERT INTO t3 VALUES (10, 50, 1), (30, 40, 2), (50, 30, 3), (70, 20, 4), (90, 10, 5), (110, 0, 6), (130, -10, 7) query I SELECT w FROM t3 WHERE v > 0 AND v < 100 ORDER BY v ---- 5 4 3 2 1 statement ok CREATE TABLE tab1 ( pk INTEGER NOT NULL, col0 INTEGER NULL, col1 FLOAT NULL, col2 STRING NULL, col3 INTEGER NULL, col4 FLOAT NULL, col5 STRING NULL, CONSTRAINT "primary" PRIMARY KEY (pk ASC), INDEX idx_tab1_0 (col0 ASC), INDEX idx_tab1_1 (col1 ASC), INDEX idx_tab1_3 (col3 ASC), INDEX idx_tab1_4 (col4 ASC), FAMILY "primary" (pk, col0, col1, col2, col3, col4, col5) ) statement ok INSERT INTO tab1(pk, col0, col3) VALUES (1, 65, 65), (2, 87, 87), (3, 70, 70), (4, 88, 88), (5, 69, 69), (6, 72, 72), (7, 82, 82) query II SELECT pk, col0 FROM tab1 WHERE (col3 BETWEEN 66 AND 87) ORDER BY 1 DESC ---- 7 82 6 72 5 69 3 70 2 87 # Use a unique index with a nullable column. Rows with a NULL value for that # column will have the PK columns added to the key, whereas rows with a non-NULL # value will not. Ensure that when the index is used, it returns all rows. statement ok CREATE TABLE abc (a INT, b INT, c INT, PRIMARY KEY(a, b), UNIQUE INDEX c (c)) statement ok INSERT INTO abc (a, b, c) VALUES (0, 1, NULL); INSERT INTO abc (a, b, c) VALUES (0, 2, NULL); INSERT INTO abc (a, b, c) VALUES (1, 1, NULL); INSERT INTO abc (a, b, c) VALUES (1, 2, NULL); INSERT INTO abc (a, b, c) VALUES (2, 1, 1); INSERT INTO abc (a, b, c) VALUES (2, 2, 2); query III rowsort SELECT * FROM abc WHERE (c IS NULL OR c=2) AND a>0 ---- 1 1 NULL 1 2 NULL 2 2 2