1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
|
# name: test/sql/order/test_order_by_exceptions.test
# description: Test ORDER BY exceptions
# group: [order]
statement ok
PRAGMA enable_verification
statement ok
CREATE TABLE test (a INTEGER, b INTEGER);
statement ok
INSERT INTO test VALUES (11, 22), (12, 21), (13, 22);
# ORDER BY index out of range
statement error
SELECT a FROM test ORDER BY 2
----
<REGEX>:Binder Error:.*term out of range.*
# ORDER BY constant returns an error
# CONTROVERSIAL: works in SQLite but not in Postgres
statement error
SELECT a FROM test ORDER BY 'hello', a
----
ORDER BY non-integer literal has no effect
statement ok
SET order_by_non_integer_literal=true
query I
SELECT a FROM test ORDER BY 'hello', a
----
11
12
13
query II
SELECT a AS k, b FROM test UNION SELECT a, b AS k FROM test ORDER BY k
----
11 22
12 21
13 22
query II
SELECT a AS k, b FROM test UNION SELECT a AS k, b FROM test ORDER BY k
----
11 22
12 21
13 22
# ambiguous reference in union parameter
statement error
SELECT a % 2, b FROM test UNION SELECT b, a % 2 AS k ORDER BY a % 2
----
<REGEX>:Binder Error:.*column.*not found.*
# but works if not ambiguous
query II
SELECT a % 2, b FROM test UNION SELECT a % 2 AS k, b FROM test ORDER BY a % 2
----
0 21
1 22
# out of range order also happens for unions
statement error
SELECT a % 2, b FROM test UNION SELECT a % 2 AS k, b FROM test ORDER BY 3
----
<REGEX>:Binder Error:.*out of range.*
statement error
SELECT a % 2, b FROM test UNION SELECT a % 2 AS k, b FROM test ORDER BY -1
----
<REGEX>:Binder Error:.*out of range.*
# and union itself fails if amount of entries is wrong
statement error
SELECT a % 2, b FROM test UNION SELECT a % 2 AS k FROM test ORDER BY -1
----
<REGEX>:Binder Error:.*Set operations can only apply.*
|
