Materialize/test/sqllogictest/cockroach/order_by.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/order_by
#
# 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 BOOLEAN
)

statement ok
INSERT INTO t VALUES (1, 9, true), (2, 8, false), (3, 7, NULL)

# Note: Result differs from Cockroach but matches Postgres.
query B
SELECT c FROM t ORDER BY c
----
false
true
NULL

# The following test ensures that the "rowsort" directive
# in TestLogic does its work properly.
query B rowsort
SELECT c FROM t ORDER BY c
----
false
NULL
true

# Note: Result differs from Cockroach but matches Postgres.
query B
SELECT c FROM t ORDER BY c DESC
----
NULL
true
false

query II
SELECT a, b FROM t ORDER BY b
----
3 7
2 8
1 9

query II
SELECT a, b FROM t ORDER BY b DESC
----
1 9
2 8
3 7

query I
SELECT a FROM t ORDER BY 1 DESC
----
3
2
1

query II
SELECT a, b FROM t ORDER BY b DESC LIMIT 2
----
1 9
2 8

query BI
SELECT DISTINCT c, b FROM t ORDER BY b DESC LIMIT 2
----
true   9
false  8

query II
SELECT a AS foo, b FROM t ORDER BY foo DESC
----
3 7
2 8
1 9

# Check that ambiguous references to renders are properly reported.
query error column reference "foo" is ambiguous
SELECT a AS foo, b AS foo FROM t ORDER BY foo

# Check that no ambiguity is reported if the ORDER BY name refers
# to two or more equivalent renders (special case in SQL92).
query II
SELECT a AS foo, (a) AS foo FROM t ORDER BY foo LIMIT 1
----
1 1

query II
SELECT a AS "foo.bar", b FROM t ORDER BY "foo.bar" DESC
----
3 7
2 8
1 9

query II
SELECT a AS foo, b FROM t ORDER BY a DESC
----
3 7
2 8
1 9

query I
SELECT b FROM t ORDER BY a DESC
----
7
8
9

statement ok
INSERT INTO t VALUES (4, 7), (5, 7)

query II
SELECT a, b FROM t WHERE b = 7 ORDER BY b, a
----
3 7
4 7
5 7

query II
SELECT a, b FROM t ORDER BY b, a DESC
----
5 7
4 7
3 7
2 8
1 9

query III
SELECT a, b, a+b AS ab FROM t WHERE b = 7 ORDER BY ab DESC, a
----
5 7 12
4 7 11
3 7 10

query I
SELECT a FROM t ORDER BY a+b DESC, a
----
5
4
1
2
3

query I
SELECT a FROM t ORDER BY (((a)))
----
1
2
3
4
5

query I
(((SELECT a FROM t))) ORDER BY a DESC LIMIT 4
----
5
4
3
2

query I
(((SELECT a FROM t ORDER BY a DESC LIMIT 4)))
----
5
4
3
2

query error pgcode 42601 multiple ORDER BY clauses not allowed
((SELECT a FROM t ORDER BY a)) ORDER BY a

query error CASE types integer and boolean cannot be matched
SELECT CASE a WHEN 1 THEN b ELSE c END as val FROM t ORDER BY val

query error pgcode 42P10 column reference 0 in ORDER BY clause is out of range \(1 - 3\)
SELECT * FROM t ORDER BY 0

query error column "foo" does not exist
SELECT * FROM t ORDER BY foo

query error column "a.b" does not exist
SELECT a FROM t ORDER BY a.b

query IT
SELECT generate_series, ARRAY[generate_series] FROM generate_series(1, 1) ORDER BY 1
----
1 {1}

query IT
SELECT generate_series, ARRAY[generate_series] FROM generate_series(1, 1) ORDER BY generate_series
----
1 {1}

query IT
SELECT generate_series, ARRAY[generate_series] FROM generate_series(1, 1) ORDER BY -generate_series
----
1 {1}

statement ok
CREATE TABLE abc (
  a INT,
  b INT,
  c INT,
  d VARCHAR,
  PRIMARY KEY (a, b, c)
)

statement ok
INSERT INTO abc VALUES (1, 2, 3, 'one'), (4, 5, 6, 'Two')

query T
SELECT d FROM abc ORDER BY lower(d)
----
one
Two

query I
SELECT a FROM abc ORDER BY a DESC
----
4
1

query I
SELECT a FROM abc ORDER BY a DESC LIMIT 1
----
4

query I
SELECT a FROM abc ORDER BY a DESC OFFSET 1
----
1

statement ok
CREATE TABLE bar (id INT PRIMARY KEY, baz STRING)

statement ok
INSERT INTO bar VALUES (0, NULL), (1, NULL)

# Here rowsort is needed because the ORDER BY clause does not guarantee any
# relative ordering between rows where baz is NULL. As we see above, because
# this is a unique index, the ordering `+baz,+id` is deemed equivalent to just
# `+baz`.
query IT rowsort
SELECT * FROM bar ORDER BY baz, id
----
0 NULL
1 NULL

statement ok
CREATE TABLE abcd (
  a INT PRIMARY KEY,
  b INT,
  c INT,
  d INT
)

statement ok
INSERT INTO abcd VALUES (1, 4, 2, 3), (2, 3, 4, 1), (3, 2, 1, 2), (4, 4, 1, 1)

# Verify that render expressions after sorts perform correctly. We need the
# rowsort as we're attempting to force a RENDER expression after the first
# ORDER BY, to ensure it renders correctly, but the outer query doesn't
# guarantee that it will preserve the order.

query I rowsort
SELECT a+b FROM (SELECT * FROM abcd ORDER BY d)
----
5
5
5
8

query I rowsort
SELECT b+d FROM (SELECT * FROM abcd ORDER BY a,d)
----
7
4
4
5

statement ok
CREATE TABLE nan (id INT PRIMARY KEY, x REAL)

statement ok
INSERT INTO nan VALUES (1, 'NaN'), (2, -1), (3, 1), (4, 'NaN')

# TODO(benesch): NaN sorts backwards in Materialize, it seems.
skipif postgresql
query R
SELECT x FROM nan ORDER BY x
----
NaN
NaN
-1
1

statement ok
CREATE TABLE blocks (
  block_id  INT,
  writer_id STRING,
  block_num INT,
  raw_bytes BYTES,
  PRIMARY KEY (block_id, writer_id, block_num)
)

# Test ORDER BY with STORING column.
statement ok
CREATE TABLE store (
    id INT PRIMARY KEY,
    baz STRING,
    extra INT
)

statement ok
INSERT INTO store VALUES (0, NULL, 10), (1, NULL, 5)

# Here rowsort is needed because a unique index still allows duplicate NULL
# values. It's not correct to sort on baz alone, even though it is "unique".
query ITI
SELECT * FROM store ORDER BY baz, extra
----
1  NULL  5
0  NULL  10

# ------------------------------------------------------------------------------
# ORDER BY INDEX test cases.
# ------------------------------------------------------------------------------
subtest order_by_index

# NOTE(benesch): this is Cockroach-specific syntax we're unlikely to support.
halt

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

statement ok
CREATE INDEX foo ON kv(v DESC)

# Check the extended syntax cannot be used in case of renames.
statement error no data source matches prefix: test.public.kv
SELECT * FROM kv AS a, kv AS b ORDER BY PRIMARY KEY kv

# The INDEX/PRIMARY syntax can only be used when the data source
# is a real table, not an alias.
#
statement error no data source matches prefix: test.public.kv
SELECT k FROM (SELECT @1, @1 FROM generate_series(1,10)) AS kv(k,v) ORDER BY PRIMARY KEY kv

statement error no data source matches prefix: test.public.kv
CREATE TABLE unrelated(x INT); SELECT * FROM unrelated ORDER BY PRIMARY KEY kv

# Check that prepare doesn't crash on ORDER BY PK clauses materialize#17312
statement ok
PREPARE a AS (TABLE kv) ORDER BY PRIMARY KEY kv

statement error ORDER BY INDEX in window definition is not supported
SELECT avg(k) OVER (ORDER BY PRIMARY KEY kv) FROM kv

statement ok
INSERT INTO kv VALUES (1, 1), (2, 1), (3, 1), (4, 1), (5, 1)

query I
SELECT k FROM kv ORDER BY INDEX kv@foo
----
1
2
3
4
5

statement ok
CREATE TABLE abc2 (
  a INT,
  b INT,
  c INT,
  PRIMARY KEY (a, b),
  UNIQUE INDEX bc (b, c),
  INDEX ba (b, a)
)

statement ok
INSERT INTO abc2 VALUES (2, 30, 400), (1, 30, 500), (3, 30, 300)

query III
SELECT a, b, c FROM abc2 ORDER BY PRIMARY KEY abc2
----
1  30  500
2  30  400
3  30  300

query III
SELECT a, b, c FROM abc2 ORDER BY PRIMARY KEY abc2 DESC
----
3  30  300
2  30  400
1  30  500

query III
SELECT a, b, c FROM abc2 ORDER BY INDEX abc2@bc
----
3  30  300
2  30  400
1  30  500

query III
SELECT a, b, c FROM abc2 ORDER BY INDEX abc2@bc DESC
----
1  30  500
2  30  400
3  30  300

query III
SELECT a, b, c FROM abc2 ORDER BY INDEX abc2@ba
----
1  30  500
2  30  400
3  30  300

query III
SELECT a, b, c FROM abc2 ORDER BY INDEX abc2@ba DESC
----
3  30  300
2  30  400
1  30  500

statement error relation \"x\" does not exist
SELECT a, b, c FROM abc2 AS x ORDER BY INDEX x@bc

statement error no data source matches prefix: test.public.abc2
SELECT a, b, c FROM abc2 AS x ORDER BY INDEX abc2@bc