order_by

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

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 ITT
EXPLAIN SELECT a, b FROM t ORDER BY b
----
0 sort +b
1 scan t@primary -

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

query ITT
EXPLAIN SELECT a, b FROM t ORDER BY b DESC
----
0 sort -b
1 scan t@primary -

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

query ITT
EXPLAIN SELECT a, b FROM t ORDER BY b LIMIT 2
----
0 limit count: 2
1 sort  +b (top 2)
2 scan  t@primary -

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

query ITT
EXPLAIN SELECT DISTINCT a FROM t ORDER BY b LIMIT 2
----
0 limit    count: 2
1 distinct
2 sort     +b (iterative)
3 scan     t@primary -

query I
SELECT DISTINCT a FROM t ORDER BY b DESC LIMIT 2
----
1
2

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

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

query ITT
EXPLAIN SELECT b FROM t ORDER BY a DESC
----
0 nosort -a
1 revscan t@primary -

query ITT
EXPLAIN SELECT b FROM t ORDER BY a DESC, b ASC
----
0 nosort -a,+b
1 revscan t@primary -

query ITT
EXPLAIN SELECT b FROM t ORDER BY a DESC, b DESC
----
0 nosort -a,-b
1 revscan t@primary -

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 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 ORDER BY position 0 is not in select list
SELECT * FROM t ORDER BY 0

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

query error non-integer constant in ORDER BY: 1 \+ 2
SELECT * FROM t ORDER BY 1+2

query error column name "foo" not found
SELECT * FROM t ORDER BY foo

query error source name "a" not found in FROM clause
SELECT a FROM t ORDER BY a.b

# Check that the sort key reuses the existing render.
query ITTTT
EXPLAIN(VERBOSE) SELECT b+2 FROM t ORDER BY b+2
----
0   select                                              ("b + 2")   +"b + 2"
1   sort            +"b + 2"                            ("b + 2")   +"b + 2"
2   render/filter   from (test.t.a, test.t.b, test.t.c) ("b + 2")
3   scan            t@primary -                         (a, b, c)   +a,unique

# Check that the sort picks up a renamed render properly.
query ITTTT
EXPLAIN(VERBOSE) SELECT b+2 AS y FROM t ORDER BY y
----
0   select                                              (y)   +y
1   sort            +y                                  (y)   +y
2   render/filter   from (test.t.a, test.t.b, test.t.c) (y)
3   scan            t@primary -                         (a, b, c)   +a,unique

# Check that the sort reuses a render behind a rename properly.
query ITTTT
EXPLAIN(VERBOSE) SELECT b+2 AS y FROM t ORDER BY b+2
----
0   select                                              (y)   +y
1   sort            +y                                  (y)   +y
2   render/filter   from (test.t.a, test.t.b, test.t.c) (y)
3   scan            t@primary -                         (a, b, c)   +a,unique

statement ok
CREATE TABLE abc (
  a INT,
  b INT,
  c INT,
  d CHAR,
  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 ITTT
EXPLAIN (DEBUG) SELECT * FROM abc ORDER BY a
----
0 /abc/primary/1/2/3   NULL  PARTIAL
0 /abc/primary/1/2/3/d 'one' ROW
1 /abc/primary/4/5/6   NULL  PARTIAL
1 /abc/primary/4/5/6/d 'Two' ROW

query ITT
EXPLAIN SELECT * FROM abc ORDER BY a
----
0 scan abc@primary -

query ITTT
EXPLAIN (DEBUG) SELECT a, b FROM abc ORDER BY b, a
----
0 /abc/ba/2/1/3 NULL ROW
1 /abc/ba/5/4/6 NULL ROW

query ITT
EXPLAIN SELECT a, b FROM abc ORDER BY b, a
----
0 scan abc@ba -

# The non-unique index ba includes column c (required to make the keys unique)
# so the results will already be sorted.
query ITT
EXPLAIN SELECT a, b, c FROM abc ORDER BY b, a, c
----
0 scan abc@ba -

# We use the WHERE condition to force the use of index ba.
query ITT
EXPLAIN SELECT a, b, c FROM abc WHERE b > 10 ORDER BY b, a, d
----
0 sort       +b,+a,+d
1 index-join
2 scan       abc@ba /11-
2 scan       abc@primary

# We cannot have rows with identical values for a,b,c so we don't need to
# sort for d.
query ITT
EXPLAIN SELECT a, b, c, d FROM abc WHERE b > 10 ORDER BY b, a, c, d
----
0 index-join
1 scan       abc@ba /11-
1 scan       abc@primary

query ITT
EXPLAIN SELECT a, b FROM abc ORDER BY b, c
----
0 nosort +b,+c
1 scan   abc@bc -

query ITT
EXPLAIN SELECT a, b FROM abc ORDER BY b, c, a
----
0 nosort +b,+c,+a
1 scan   abc@bc -

query ITT
EXPLAIN SELECT a, b FROM abc ORDER BY b, c, a DESC
----
0 nosort +b,+c,-a
1 scan   abc@bc -

query ITTT
EXPLAIN (DEBUG) SELECT b, c FROM abc ORDER BY b, c
----
0 /abc/bc/2/3 /1 ROW
1 /abc/bc/5/6 /4 ROW

query ITTT
EXPLAIN (DEBUG) SELECT a, b, c FROM abc ORDER BY b
----
0 /abc/bc/2/3 /1 ROW
1 /abc/bc/5/6 /4 ROW

query ITTT
EXPLAIN (DEBUG) SELECT a FROM abc ORDER BY a DESC
----
0 /abc/primary/4/5/6/d 'Two' PARTIAL
0 /abc/primary/4/5/6   NULL  ROW
1 /abc/primary/1/2/3/d 'one' PARTIAL
1 /abc/primary/1/2/3   NULL  ROW

query ITT
EXPLAIN SELECT a FROM abc ORDER BY a DESC
----
0 revscan abc@primary -

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

query ITT
EXPLAIN SELECT c FROM abc WHERE b = 2 ORDER BY c
----
0 scan abc@bc /2-/3

query ITT
EXPLAIN SELECT c FROM abc WHERE b = 2 ORDER BY c DESC
----
0 revscan abc@bc /2-/3

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

query IT
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)

# The following tests verify we recognize that sorting is not necessary
query ITT
EXPLAIN SELECT a, b, c FROM abcd@abc WHERE (a, b) = (1, 4) ORDER BY c
----
0 scan abcd@abc /1/4-/1/5

query ITT
EXPLAIN SELECT a, b, c FROM abcd@abc WHERE (a, b) = (1, 4) ORDER BY c, b, a
----
0 scan abcd@abc /1/4-/1/5

query ITT
EXPLAIN SELECT a, b, c FROM abcd@abc WHERE (a, b) = (1, 4) ORDER BY b, a, c
----
0 scan abcd@abc /1/4-/1/5

query ITT
EXPLAIN SELECT a, b, c FROM abcd@abc WHERE (a, b) = (1, 4) ORDER BY b, c, a
----
0 scan abcd@abc /1/4-/1/5

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

statement ok
INSERT INTO nan VALUES (1, 0/0), (2, -1), (3, 1), (4, 0/0);

query R
SELECT x FROM nan ORDER BY x;
----
NaN
NaN
-1
1

query ITTTT
EXPLAIN(VERBOSE) SELECT * FROM (SELECT * FROM (VALUES ('a'), ('b'), ('c')) AS c(x) ORDER BY x);
----
0  select                           (x)        +x
1  render/filter  from (""."".x)    (x)        +x
2  select                           (x)        +x
3  sort           +x                (x)        +x
4  render/filter  from ("".c.x)     (x)
5  select                           (column1)
6  values         1 column, 3 rows  (column1)