1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test ORDER BY keyword", "[order]") {
8 unique_ptr<MaterializedQueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11 con.EnableQueryVerification();
12
13 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER);"));
14 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 22), (12, 21), (13, 22);"));
15
16 // simple ORDER BY
17 result = con.Query("SELECT b FROM test ORDER BY a DESC;");
18 REQUIRE(CHECK_COLUMN(result, 0, {22, 21, 22}));
19 REQUIRE(result->types.size() == 1);
20
21 result = con.Query("SELECT a, b FROM test ORDER BY a;");
22 REQUIRE(CHECK_COLUMN(result, 0, {11, 12, 13}));
23 REQUIRE(CHECK_COLUMN(result, 1, {22, 21, 22}));
24
25 result = con.Query("SELECT a, b FROM test ORDER BY a DESC;");
26 REQUIRE(CHECK_COLUMN(result, 0, {13, 12, 11}));
27 REQUIRE(CHECK_COLUMN(result, 1, {22, 21, 22}));
28
29 // ORDER BY on multiple columns
30 result = con.Query("SELECT a, b FROM test ORDER BY b, a;");
31 REQUIRE(CHECK_COLUMN(result, 0, {12, 11, 13}));
32 REQUIRE(CHECK_COLUMN(result, 1, {21, 22, 22}));
33
34 // ORDER BY using select indices
35 result = con.Query("SELECT a, b FROM test ORDER BY 2, 1;");
36 REQUIRE(CHECK_COLUMN(result, 0, {12, 11, 13}));
37 REQUIRE(CHECK_COLUMN(result, 1, {21, 22, 22}));
38
39 result = con.Query("SELECT a, b FROM test ORDER BY b DESC, a;");
40 REQUIRE(CHECK_COLUMN(result, 0, {11, 13, 12}));
41 REQUIRE(CHECK_COLUMN(result, 1, {22, 22, 21}));
42
43 result = con.Query("SELECT a, b FROM test ORDER BY b, a DESC;");
44 REQUIRE(CHECK_COLUMN(result, 0, {12, 13, 11}));
45 REQUIRE(CHECK_COLUMN(result, 1, {21, 22, 22}));
46
47 // TOP N queries
48 result = con.Query("SELECT a, b FROM test ORDER BY b, a DESC LIMIT 1;");
49 REQUIRE(CHECK_COLUMN(result, 0, {12}));
50 REQUIRE(CHECK_COLUMN(result, 1, {21}));
51
52 // Offset
53 result = con.Query("SELECT a, b FROM test ORDER BY b, a DESC LIMIT 1 OFFSET 1;");
54 REQUIRE(CHECK_COLUMN(result, 0, {13}));
55 REQUIRE(CHECK_COLUMN(result, 1, {22}));
56
57 // Offset without limit
58 result = con.Query("SELECT a, b FROM test ORDER BY b, a DESC OFFSET 1;");
59 REQUIRE(CHECK_COLUMN(result, 0, {13, 11}));
60 REQUIRE(CHECK_COLUMN(result, 1, {22, 22}));
61
62 result = con.Query("SELECT a, b FROM test WHERE a < 13 ORDER BY b;");
63 REQUIRE(CHECK_COLUMN(result, 0, {12, 11}));
64 REQUIRE(CHECK_COLUMN(result, 1, {21, 22}));
65
66 result = con.Query("SELECT a, b FROM test WHERE a < 13 ORDER BY 2;");
67 REQUIRE(CHECK_COLUMN(result, 0, {12, 11}));
68 REQUIRE(CHECK_COLUMN(result, 1, {21, 22}));
69
70 result = con.Query("SELECT a, b FROM test WHERE a < 13 ORDER BY b DESC;");
71 REQUIRE(CHECK_COLUMN(result, 0, {11, 12}));
72 REQUIRE(CHECK_COLUMN(result, 1, {22, 21}));
73
74 result = con.Query("SELECT b, a FROM test WHERE a < 13 ORDER BY b DESC;");
75 REQUIRE(CHECK_COLUMN(result, 0, {22, 21}));
76 REQUIRE(CHECK_COLUMN(result, 1, {11, 12}));
77
78 // order by expression
79 result = con.Query("SELECT b % 2 AS f, SUM(a) FROM test GROUP BY f ORDER BY b % 2;");
80 REQUIRE(CHECK_COLUMN(result, 0, {0, 1}));
81 REQUIRE(CHECK_COLUMN(result, 1, {24, 12}));
82
83 // order by expression that is not in SELECT
84 result = con.Query("SELECT b % 2 AS f, a FROM test ORDER BY b % 4;");
85 REQUIRE(CHECK_COLUMN(result, 0, {1, 0, 0}));
86 REQUIRE(CHECK_COLUMN(result, 1, {12, 11, 13}));
87
88 // ORDER BY alias
89 result = con.Query("SELECT b % 2 AS f, SUM(a) FROM test GROUP BY f ORDER BY f;");
90 REQUIRE(CHECK_COLUMN(result, 0, {0, 1}));
91 REQUIRE(CHECK_COLUMN(result, 1, {24, 12}));
92
93 result = con.Query("SELECT b % 2 AS f, SUM(a) FROM test GROUP BY f ORDER BY 1;");
94 REQUIRE(CHECK_COLUMN(result, 0, {0, 1}));
95 REQUIRE(CHECK_COLUMN(result, 1, {24, 12}));
96
97 // ORDER BY after union
98 result = con.Query("SELECT a-10 AS k FROM test UNION SELECT a-10 AS l FROM test ORDER BY k;");
99 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
100
101 // ORDER BY on alias in right-most query
102 // CONTROVERSIAL: SQLite allows both "k" and "l" to be referenced here, Postgres and MonetDB give an error.
103 result = con.Query("SELECT a-10 AS k FROM test UNION SELECT a-10 AS l FROM test ORDER BY l;");
104 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
105
106 // computations with aliases are not allowed though
107 REQUIRE_FAIL(con.Query("SELECT a-10 AS k FROM test UNION SELECT a-10 AS l FROM test ORDER BY 1-k;"));
108
109 // but ordering on computation elements should work
110 result = con.Query("SELECT a-10 AS k FROM test UNION SELECT a-10 AS l FROM test ORDER BY a-10;");
111 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
112
113 result = con.Query("SELECT a-10 AS k FROM test UNION SELECT a-11 AS l FROM test ORDER BY a-11;");
114 REQUIRE(CHECK_COLUMN(result, 0, {0, 1, 2, 3}));
115}
116
117TEST_CASE("Test ORDER BY exceptions", "[order]") {
118 unique_ptr<QueryResult> result;
119 DuckDB db(nullptr);
120 Connection con(db);
121 con.EnableQueryVerification();
122
123 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER);"));
124 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 22), (12, 21), (13, 22);"));
125
126 // ORDER BY index out of range
127 REQUIRE_FAIL(con.Query("SELECT a FROM test ORDER BY 2"));
128
129 // ORDER BY constant works, but does nothing
130 // CONTROVERSIAL: works in SQLite but not in Postgres
131 result = con.Query("SELECT a FROM test ORDER BY 'hello', a");
132 REQUIRE(CHECK_COLUMN(result, 0, {11, 12, 13}));
133
134 // ambiguous reference in union alias
135 REQUIRE_FAIL(con.Query("SELECT a AS k, b FROM test UNION SELECT a, b AS k FROM test ORDER BY k"));
136
137 // but works if not ambiguous
138 result = con.Query("SELECT a AS k, b FROM test UNION SELECT a AS k, b FROM test ORDER BY k");
139 REQUIRE(CHECK_COLUMN(result, 0, {11, 12, 13}));
140 REQUIRE(CHECK_COLUMN(result, 1, {22, 21, 22}));
141
142 // ambiguous reference in union parameter
143 REQUIRE_FAIL(con.Query("SELECT a % 2, b FROM test UNION SELECT b, a % 2 AS k ORDER BY a % 2"));
144
145 // but works if not ambiguous
146 result = con.Query("SELECT a % 2, b FROM test UNION SELECT a % 2 AS k, b FROM test ORDER BY a % 2");
147 REQUIRE(CHECK_COLUMN(result, 0, {0, 1}));
148 REQUIRE(CHECK_COLUMN(result, 1, {21, 22}));
149
150 // out of range order also happens for unions
151 REQUIRE_FAIL(con.Query("SELECT a % 2, b FROM test UNION SELECT a % 2 AS k, b FROM test ORDER BY 3"));
152 REQUIRE_FAIL(con.Query("SELECT a % 2, b FROM test UNION SELECT a % 2 AS k, b FROM test ORDER BY -1"));
153
154 // and union itself fails if amount of entries is wrong
155 REQUIRE_FAIL(con.Query("SELECT a % 2, b FROM test UNION SELECT a % 2 AS k FROM test ORDER BY -1"));
156}
157
158TEST_CASE("Test ORDER BY with large table", "[order][.]") {
159 unique_ptr<MaterializedQueryResult> result;
160 DuckDB db(nullptr);
161 Connection con(db);
162
163 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER);"));
164 for (size_t i = 0; i < 10000; i++) {
165 con.Query("INSERT INTO test VALUES (" + to_string(10000 - i) + ")");
166 }
167 result = con.Query("SELECT * FROM test ORDER BY a");
168 for (size_t i = 0; i < 10000; i++) {
169 REQUIRE(result->GetValue<int32_t>(0, i) == i + 1);
170 }
171}
172
173TEST_CASE("Test Top N Optimization", "[order]") {
174 unique_ptr<MaterializedQueryResult> result;
175 DuckDB db(nullptr);
176 Connection con(db);
177 con.EnableQueryVerification();
178
179 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (b INTEGER);"));
180 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (22), (2), (7);"));
181
182 // Top N optimization
183 result = con.Query("SELECT b FROM test ORDER BY b DESC LIMIT 2;");
184 REQUIRE(CHECK_COLUMN(result, 0, {22, 7}));
185
186 // Top N optimization: works with OFFSET
187 result = con.Query("SELECT b FROM test ORDER BY b LIMIT 1 OFFSET 1;");
188 REQUIRE(CHECK_COLUMN(result, 0, {7}));
189
190 // Top N optimization: Limit greater than number of rows
191 result = con.Query("SELECT b FROM test ORDER BY b LIMIT 10 OFFSET 1;");
192 REQUIRE(CHECK_COLUMN(result, 0, {7, 22}));
193
194 // Top N optimization: Offset greater than total number of rows
195 result = con.Query("SELECT b FROM test ORDER BY b LIMIT 10 OFFSET 10;");
196 REQUIRE(CHECK_COLUMN(result, 0, {}));
197
198 // Top N optimization: doesn't apply for Offset without Limit
199 result = con.Query("SELECT b FROM test ORDER BY b OFFSET 10;");
200 REQUIRE(CHECK_COLUMN(result, 0, {}));
201}
202