order_by

# LogicTest: local local-opt fakedist fakedist-opt fakedist-metadata fakedist-disk

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)

query B
SELECT c FROM t ORDER BY c
----
NULL
false
true

# 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

query B
SELECT c FROM t ORDER BY c DESC
----
true
false
NULL

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 ORDER BY "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 expected c to be of type int, found type bool
SELECT CASE a WHEN 1 THEN b ELSE c END as val FROM t ORDER BY val

query error pgcode 42P10 ORDER BY position 0 is not in select list
SELECT * FROM t ORDER BY 0

query error pgcode 42601 non-integer constant in ORDER BY: true
SELECT * FROM t ORDER BY true

query error pgcode 42601 non-integer constant in ORDER BY: 'a'
SELECT * FROM t ORDER BY 'a'

query error pgcode 42601 non-integer constant in ORDER BY: 2\.5
SELECT * FROM t ORDER BY 2.5

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

query error no data source matches prefix: a
SELECT a FROM t ORDER BY a.b

query error can't order by column type int\[\]
SELECT generate_series FROM generate_series(1, 100) ORDER BY ARRAY[generate_series]

query error can't order by column type int\[\]
SELECT ARRAY[generate_series] FROM generate_series(1, 100) ORDER BY ARRAY[generate_series]

query error can't order by column type int\[\]
SELECT ARRAY[generate_series] FROM generate_series(1, 100) ORDER BY 1

query error can't order by column type int\[\]
SELECT ARRAY[generate_series] AS a FROM generate_series(1, 100) ORDER BY a

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),
  UNIQUE INDEX bc (b, c),
  INDEX ba (b, a),
  FAMILY (a, b, c),
  FAMILY (d)
)

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, UNIQUE INDEX i_bar (baz))

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,
  INDEX abc (a, b, c)
)

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')

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,
    UNIQUE INDEX i_store (baz) STORING (extra)
)

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

statement ok
CREATE TABLE kv(k INT PRIMARY KEY, v INT); 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 #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