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

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_expressions_in_limit_syntax TO true;
----
COMPLETE 0

query I
SELECT generate_series FROM generate_series(1, 100) ORDER BY generate_series LIMIT 5;
----
1
2
3
4
5

query I
SELECT generate_series FROM generate_series(1, 100) ORDER BY generate_series FETCH FIRST 5 ROWS ONLY;
----
1
2
3
4
5

query I
SELECT generate_series FROM generate_series(1, 100) ORDER BY generate_series FETCH FIRST ROW ONLY;
----
1

query I
SELECT generate_series FROM generate_series(1, 100) ORDER BY generate_series OFFSET 3 ROWS FETCH NEXT ROW ONLY;
----
4

statement error db error: ERROR: multiple LIMIT/FETCH clauses not allowed
SELECT generate_series FROM generate_series(1, 100) FETCH NEXT ROW ONLY LIMIT 3;

statement error db error: ERROR: multiple LIMIT/FETCH clauses not allowed
SELECT generate_series FROM generate_series(1, 100) LIMIT 3 FETCH NEXT ROW ONLY;

query I
SELECT generate_series FROM generate_series(1, 100) FETCH NEXT 1 + 1 ROWS ONLY;
----
1
2

query I
SELECT generate_series FROM generate_series(1, 100) ORDER BY generate_series FETCH FIRST (1 + 1) ROWS ONLY;
----
1
2

statement ok
CREATE TABLE t (k INT PRIMARY KEY, v INT, w INT)

statement ok
INSERT INTO t VALUES (1, 1, 1), (2, -4, 8), (3, 9, 27), (4, -16, 94), (5, 25, 125), (6, -36, 216)

# Verify we don't incorrectly impose a hard limit at the index scan level.
query III
SELECT * FROM t WHERE v > -20 AND w > 30 ORDER BY v LIMIT 2
----
4  -16  94
5  25   125

query II
SELECT k, v FROM t ORDER BY k LIMIT 5
----
1  1
2  -4
3  9
4  -16
5  25

query II
SELECT k, v FROM t ORDER BY k OFFSET 5
----
6  -36

# TODO(benesch): support this.
query error Expected end of statement, found number
SELECT k, v FROM t ORDER BY v LIMIT (1+4) OFFSET 1
----
4  -16
2  -4
1  1
3  9
5  25

# TODO(benesch): support this.
query error Expected end of statement, found number
SELECT k, v FROM t ORDER BY v DESC LIMIT (1+4) OFFSET 1
----
3  9
1  1
2  -4
4  -16
6  -36

query R
SELECT sum(w) FROM t GROUP BY k, v ORDER BY v DESC LIMIT 10
----
125
27
1
8
94
216

skipif postgresql # Cockroach preserves sort ordering when not required to.
query I
SELECT k FROM (SELECT k, v FROM t ORDER BY v LIMIT 4)
----
6
4
2
1

skipif postgresql # Cockroach preserves sort ordering when not required to.
query I
SELECT k FROM (SELECT k, v, w FROM t ORDER BY v LIMIT 4)
----
6
4
2
1

# Use expression for LIMIT/OFFSET value.
# TODO(benesch): support this.

query error Expected end of statement, found number
SELECT k, v FROM t ORDER BY k LIMIT length(pg_typeof(123))
----
1  1
2  -4
3  9

query error Expected end of statement, found number
SELECT k, v FROM t ORDER BY k LIMIT length(pg_typeof(123)) OFFSET length(pg_typeof(123))-2
----
2  -4
3  9
4  -16

query error Expected end of statement, found number
SELECT k, v FROM t ORDER BY k OFFSET (SELECT count(*)-3 FROM t)
----
4  -16
5  25
6  -36

query error Expected end of statement, found number
SELECT k, v FROM t ORDER BY k LIMIT (SELECT count(*)-3 FROM t) OFFSET (SELECT count(*)-5 FROM t)
----
2  -4
3  9
4  -16