Materialize/test/sqllogictest/cockroach/select_index_span_ranges.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_span_ranges
#
# 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

# This test verifies that we correctly perform an index join when the KV
# batches span ranges. This is testing that SQL disables batch limits for index
# join; otherwise it can get out of order results from KV that it can't handle.

kv-batch-size 10

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

statement ok
INSERT INTO t VALUES
(1, 0, 99, 0),
(2, 0, 80, 0),
(3, 0, 90, 0),
(4, 0, 10, 0),
(5, 0, 20, 0),
(6, 0, 85, 0),
(7, 0, 91, 0),
(8, 0, 12, 0),
(9, 0, 91, 0),
(10, 0, 11, 0),
(11, 0, 12, 0),
(12, 0, 88, 0),
(13, 0, 13, 0)

# Split the table across multiple ranges.
statement ok
ALTER TABLE t SPLIT AT VALUES (2)

statement ok
ALTER TABLE t SPLIT AT VALUES (3)

statement ok
ALTER TABLE t SPLIT AT VALUES (5)

statement ok
ALTER TABLE t SPLIT AT VALUES (8)

statement ok
ALTER INDEX t@c SPLIT AT VALUES (90)

statement ok
ALTER INDEX c SPLIT AT VALUES (10)

query IIII partialsort(3)
SELECT * FROM t@c WHERE (c >= 80) ORDER BY c
----
2   0  80  0
6   0  85  0
12  0  88  0
3   0  90  0
7   0  91  0
9   0  91  0
1   0  99  0