Materialize/test/sqllogictest/cockroach/select_index_flags.slt

# 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_flags
#
# 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.

# not supported yet
halt

mode cockroach

statement ok
CREATE TABLE abcd (
  a INT PRIMARY KEY,
  b INT,
  c INT,
  d INT,
  INDEX b (b),
  INDEX cd (c,d),
  UNIQUE INDEX bcd (b,c,d)
)

statement ok
INSERT INTO abcd VALUES (10, 11, 12, 13), (20, 21, 22, 23), (30, 31, 32, 33), (40, 41, 42, 43)

# No hint
query IIII rowsort
SELECT * FROM abcd WHERE a >= 20 AND a <= 30
----
20 21 22 23
30 31 32 33

# Force primary
query IIII rowsort
SELECT * FROM abcd@primary WHERE a >= 20 AND a <= 30
----
20 21 22 23
30 31 32 33

# Force primary, reverse scan.
query IIII rowsort
SELECT * FROM abcd@{FORCE_INDEX=primary,DESC} WHERE a >= 20 AND a <= 30
----
20 21 22 23
30 31 32 33

# Force index b
query IIII rowsort
SELECT * FROM abcd@b WHERE a >= 20 AND a <= 30
----
20 21 22 23
30 31 32 33

# Force index b, reverse scan.
query IIII rowsort
SELECT * FROM abcd@{FORCE_INDEX=b,DESC} WHERE a >= 20 AND a <= 30
----
20 21 22 23
30 31 32 33

# Force index cd
query IIII rowsort
SELECT * FROM abcd@cd WHERE a >= 20 AND a <= 30
----
20 21 22 23
30 31 32 33

# Force index bcd
query IIII rowsort
SELECT * FROM abcd@bcd WHERE a >= 20 AND a <= 30
----
20 21 22 23
30 31 32 33

# Force index b (covering)
query I rowsort
SELECT b FROM abcd@b WHERE a >= 20 AND a <= 30
----
21
31

# Force index b (non-covering due to WHERE clause)
query I rowsort
SELECT b FROM abcd@b WHERE c >= 20 AND c <= 30
----
21

# No hint, should be using index cd
query II rowsort
SELECT c, d FROM abcd WHERE c >= 20 AND c < 40
----
22 23
32 33

# Force primary index
query II rowsort
SELECT c, d FROM abcd@primary WHERE c >= 20 AND c < 40
----
22 23
32 33

# Force index b
query II rowsort
SELECT c, d FROM abcd@b WHERE c >= 20 AND c < 40
----
22 23
32 33

query error index \"badidx\" not found
SELECT * FROM abcd@badidx

query error index \"badidx\" not found
SELECT * FROM abcd@{FORCE_INDEX=badidx}