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
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
|
# name: test/sql/order/test_nulls_first.test
# description: Test NULLS FIRST/NULLS LAST
# group: [order]
statement ok
SET default_null_order='nulls_first';
statement ok
PRAGMA enable_verification
statement ok
CREATE TABLE integers(i INTEGER)
statement ok
INSERT INTO integers VALUES (1), (NULL)
# default is NULLS FIRST
query I
SELECT * FROM integers ORDER BY i
----
NULL
1
query I
SELECT * FROM integers ORDER BY i NULLS FIRST
----
NULL
1
query I
SELECT * FROM integers ORDER BY i NULLS LAST
----
1
NULL
query II
SELECT 10 AS j, i FROM integers ORDER BY j, i NULLS LAST
----
10 1
10 NULL
# multiple columns with a mix
statement ok
CREATE TABLE test(i INTEGER, j INTEGER)
statement ok
INSERT INTO test VALUES (1, 1), (NULL, 1), (1, NULL)
query II
SELECT * FROM test ORDER BY i NULLS FIRST, j NULLS LAST
----
NULL 1
1 1
1 NULL
query II
SELECT * FROM test ORDER BY i NULLS FIRST, j NULLS FIRST
----
NULL 1
1 NULL
1 1
query II
SELECT * FROM test ORDER BY i NULLS LAST, j NULLS FIRST
----
1 NULL
1 1
NULL 1
# now in window functions
query III
SELECT i, j, row_number() OVER (PARTITION BY i ORDER BY j NULLS FIRST) FROM test ORDER BY i NULLS FIRST, j NULLS FIRST;
----
NULL 1 1
1 NULL 1
1 1 2
query III
SELECT i, j, row_number() OVER (PARTITION BY i ORDER BY j NULLS LAST) FROM test ORDER BY i NULLS FIRST, j NULLS FIRST;
----
NULL 1 1
1 NULL 2
1 1 1
# TOP N
query II
SELECT * FROM test ORDER BY i NULLS FIRST, j NULLS LAST LIMIT 2
----
NULL 1
1 1
query II
SELECT * FROM test ORDER BY i NULLS LAST, j NULLS LAST LIMIT 2
----
1 1
1 NULL
# Test NULLS FIRST/NULLS LAST PRAGMA
# default is NULLS FIRST
query I
SELECT * FROM integers ORDER BY i
----
NULL
1
# changed default now
statement ok
PRAGMA default_null_order='NULLS LAST'
query I
SELECT * FROM integers ORDER BY i
----
1
NULL
statement ok
PRAGMA default_null_order='NULLS FIRST'
statement error
PRAGMA default_null_order())
----
<REGEX>:Parser Error.*syntax error.*
statement error
PRAGMA default_null_order='UNKNOWN'
----
<REGEX>:Parser Error.*Unrecognized parameter for option.*
statement error
PRAGMA default_null_order=UNKNOWN)
----
<REGEX>:Parser Error.*syntax error.*
statement error
PRAGMA default_null_order=3)
----
<REGEX>:Parser Error.*syntax error.*
|
