statement ok
drop table if exists t

statement ok
CREATE TABLE t (  a INT PRIMARY KEY,  b INT,  c BOOLEAN NULL)

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

query I
SELECT c FROM t ORDER BY c
----
false
true
null

query I
SELECT c FROM t ORDER BY c
----
false
true
null

query I
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

# TODO: Order By <column index>
# 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 II
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

statement error 1065
SELECT a AS foo, b AS foo FROM t ORDER BY foo

query II
SELECT a AS foo, (a) AS foo FROM t ORDER BY foo LIMIT 1
----
1 1

onlyif mysql
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(a,b) 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

# TODO: Order By <column index>
# query I
# (((SELECT a FROM t))) ORDER BY a DESC LIMIT 4
# ----
# 5
# 4
# 3
# 2

statement error 1005
((SELECT a FROM t ORDER BY a)) ORDER BY a

statement error
SELECT CASE a WHEN 1 THEN b ELSE c END as val FROM t ORDER BY val

statement error
SELECT * FROM t ORDER BY 0

query III
SELECT * FROM t ORDER BY true,a
----
1 9 true
2 8 false
3 7 null
4 7 null
5 7 null

query III
SELECT * FROM t ORDER BY "a"
----
1 9 true
2 8 false
3 7 null
4 7 null
5 7 null

query III
SELECT * FROM t ORDER BY 2.5,a
----
1 9 true
2 8 false
3 7 null
4 7 null
5 7 null

statement error
SELECT * FROM t ORDER BY foo

statement error
SELECT a FROM t ORDER BY a.b

statement ok
drop table if exists abc

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

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

# TODO: lower()
# 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
drop table if exists bar

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

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
drop table if exists abcd

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)

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

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

statement ok
drop table if exists store

statement ok
CREATE TABLE store (  id INT PRIMARY KEY, baz STRING NULL, extra INT)

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

query ITI
SELECT * FROM store ORDER BY baz, extra
----
1  null  5
0  null  10

statement ok
drop table if exists xy

statement ok
CREATE TABLE xy(id INT PRIMARY KEY, x INT NULL, y INT NULL)

statement ok
INSERT INTO xy VALUES (0, 2, NULL), (1, NULL, 6), (2, 2, 5), (3, 4, 8)

query IT
SELECT x, y FROM xy ORDER BY y NULLS FIRST
----
2 null
2 5
null 6
4 8

query II
SELECT x, y FROM xy ORDER BY y NULLS LAST
----
2 5
null 6
4 8
2 null

query IT
SELECT x, y FROM xy ORDER BY y DESC NULLS FIRST
----
2 null
4 8
null 6
2 5

query II
SELECT x, y FROM xy ORDER BY y DESC NULLS LAST
----
4 8
null 6
2 5
2 null

query II
SELECT x, y FROM xy ORDER BY y NULLS LAST
----
2 5
null 6
4 8
2 null

statement ok
INSERT INTO xy VALUES (4, NULL, NULL)

query TI
SELECT x, y FROM xy ORDER BY x NULLS FIRST, y NULLS LAST
----
null 6
null null
2 5
2 null
4 8

query IT
SELECT x, y FROM xy ORDER BY x NULLS LAST, y DESC NULLS FIRST
----
2 null
2 5
4 8
null null
null 6

query II
SELECT x, y FROM xy ORDER BY x, y
----
2 5
2 null
4 8
null 6
null null

query IT
SELECT x, y FROM xy ORDER BY x, y DESC NULLS FIRST
----
2 null
2 5
4 8
null null
null 6

query IT
SELECT x, y FROM xy ORDER BY x NULLS LAST, y DESC NULLS FIRST
----
2 null
2 5
4 8
null null
null 6

query TT
SELECT x, y FROM xy ORDER BY x NULLS FIRST, y DESC NULLS LAST
----
null 6
null null
2 5
2 null
4 8

query TT
SELECT x, y FROM xy ORDER BY x NULLS FIRST, y DESC
----
null 6
null null
2 5
2 null
4 8

query TI
SELECT x, y FROM xy ORDER BY x NULLS FIRST, y DESC NULLS FIRST
----
null null
null 6
2 null
2 5
4 8