1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test DISTINCT keyword", "[distinct]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11 con.EnableQueryVerification();
12
13 con.Query("CREATE TABLE test (a INTEGER, b INTEGER);");
14 con.Query("INSERT INTO test VALUES (11, 22), (13, 22), (11, 21), (11, 22)");
15
16 result = con.Query("SELECT DISTINCT a, b FROM test ORDER BY a, b");
17 REQUIRE(CHECK_COLUMN(result, 0, {11, 11, 13}));
18 REQUIRE(CHECK_COLUMN(result, 1, {21, 22, 22}));
19
20 // FIXME: this doesn't work because "test.a" is different from "a" in the ORDER BY
21 // result = con.Query("SELECT DISTINCT test.a, b FROM test ORDER BY a, b");
22
23 result = con.Query("SELECT DISTINCT a FROM test ORDER BY a");
24 REQUIRE(CHECK_COLUMN(result, 0, {11, 13}));
25
26 result = con.Query("SELECT DISTINCT b FROM test ORDER BY b");
27 REQUIRE(CHECK_COLUMN(result, 0, {21, 22}));
28
29 result = con.Query("SELECT DISTINCT a, SUM(B) FROM test GROUP BY a ORDER BY a");
30 REQUIRE(CHECK_COLUMN(result, 0, {11, 13}));
31 REQUIRE(CHECK_COLUMN(result, 1, {65, 22}));
32
33 result = con.Query("SELECT DISTINCT MAX(b) FROM test GROUP BY a");
34 REQUIRE(CHECK_COLUMN(result, 0, {22}));
35
36 result = con.Query("SELECT DISTINCT CASE WHEN a > 11 THEN 11 ELSE a END FROM test");
37 REQUIRE(CHECK_COLUMN(result, 0, {11}));
38}
39
40TEST_CASE("Test DISTINCT and ORDER BY", "[distinct]") {
41 unique_ptr<QueryResult> result;
42 DuckDB db(nullptr);
43 Connection con(db);
44 con.EnableQueryVerification();
45
46 con.Query("CREATE TABLE integers(i INTEGER);");
47 con.Query("INSERT INTO integers VALUES (1), (2), (3)");
48
49 result = con.Query("SELECT DISTINCT i%2 FROM integers ORDER BY 1");
50 REQUIRE(CHECK_COLUMN(result, 0, {0, 1}));
51
52 // controversial: Postgres fails here with the error "with SELECT DISTINCT columns from ORDER BY must appear in the
53 // SELECT clause" but SQLite succeeds
54 // we also succeed here, even though it can give unintuitive results
55 // this is transformed into SELECT DISTINCT(1) i % 2, i
56 result = con.Query("SELECT DISTINCT i % 2 FROM integers WHERE i<3 ORDER BY i");
57 REQUIRE(CHECK_COLUMN(result, 0, {1, 0}));
58 result = con.Query("SELECT DISTINCT ON (1) i % 2, i FROM integers WHERE i<3 ORDER BY i");
59 REQUIRE(CHECK_COLUMN(result, 0, {1, 0}));
60 REQUIRE(CHECK_COLUMN(result, 1, {1, 2}));
61
62 // binding of DISTINCT with column names
63 result = con.Query("SELECT DISTINCT integers.i FROM integers ORDER BY i DESC");
64 REQUIRE(CHECK_COLUMN(result, 0, {3, 2, 1}));
65 result = con.Query("SELECT DISTINCT i FROM integers ORDER BY integers.i DESC");
66 REQUIRE(CHECK_COLUMN(result, 0, {3, 2, 1}));
67 result = con.Query("SELECT DISTINCT integers.i FROM integers ORDER BY integers.i DESC");
68 REQUIRE(CHECK_COLUMN(result, 0, {3, 2, 1}));
69}
70
71TEST_CASE("Test DISTINCT ON", "[distinct]") {
72 unique_ptr<QueryResult> result;
73 DuckDB db(nullptr);
74 Connection con(db);
75 con.EnableQueryVerification();
76
77 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER, j INTEGER, k INTEGER);"));
78 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (2, 3, 5), (4, 5, 6), (2, 7, 6)"));
79
80 result = con.Query("SELECT DISTINCT ON (i) i, j FROM integers WHERE i <> 2");
81 REQUIRE(CHECK_COLUMN(result, 0, {4}));
82 REQUIRE(CHECK_COLUMN(result, 1, {5}));
83
84 result = con.Query("SELECT DISTINCT ON (1) i, j FROM integers ORDER BY i");
85 REQUIRE(CHECK_COLUMN(result, 0, {2, 4}));
86 REQUIRE(CHECK_COLUMN(result, 1, {3, 5}));
87
88 result = con.Query("SELECT DISTINCT ON (1) i, j FROM integers ORDER BY i LIMIT 1");
89 REQUIRE(CHECK_COLUMN(result, 0, {2}));
90 REQUIRE(CHECK_COLUMN(result, 1, {3}));
91
92 result = con.Query("SELECT DISTINCT ON (1) i, j FROM integers ORDER BY i LIMIT 1 OFFSET 1");
93 REQUIRE(CHECK_COLUMN(result, 0, {4}));
94 REQUIRE(CHECK_COLUMN(result, 1, {5}));
95
96 result = con.Query("SELECT DISTINCT ON (2) i, j FROM integers ORDER BY 2");
97 REQUIRE(CHECK_COLUMN(result, 0, {2, 4, 2}));
98 REQUIRE(CHECK_COLUMN(result, 1, {3, 5, 7}));
99
100 result = con.Query("SELECT DISTINCT ON (2) j, k FROM integers ORDER BY 2");
101 REQUIRE(CHECK_COLUMN(result, 0, {3, 5}));
102 REQUIRE(CHECK_COLUMN(result, 1, {5, 6}));
103
104 result = con.Query("SELECT DISTINCT ON (3) i, j, k FROM integers ORDER BY 2");
105 REQUIRE(CHECK_COLUMN(result, 0, {2, 4}));
106 REQUIRE(CHECK_COLUMN(result, 1, {3, 5}));
107 REQUIRE(CHECK_COLUMN(result, 2, {5, 6}));
108
109 result = con.Query("SELECT DISTINCT ON (3) i, j, k FROM integers ORDER BY 3");
110 REQUIRE(CHECK_COLUMN(result, 0, {2, 4}));
111 REQUIRE(CHECK_COLUMN(result, 1, {3, 5}));
112 REQUIRE(CHECK_COLUMN(result, 2, {5, 6}));
113
114 result = con.Query("SELECT DISTINCT ON (2) j, (SELECT i FROM integers) FROM integers ORDER BY 2");
115 REQUIRE(CHECK_COLUMN(result, 0, {3}));
116 REQUIRE(CHECK_COLUMN(result, 1, {2}));
117
118 result = con.Query(
119 "SELECT DISTINCT ON (2) j, (SELECT DISTINCT ON (i) i FROM integers ORDER BY 1) FROM integers ORDER BY 2");
120 REQUIRE(CHECK_COLUMN(result, 0, {3}));
121 REQUIRE(CHECK_COLUMN(result, 1, {2}));
122
123 result = con.Query("SELECT DISTINCT ON (i) i, j FROM integers ORDER BY j");
124 REQUIRE(CHECK_COLUMN(result, 0, {2, 4}));
125 REQUIRE(CHECK_COLUMN(result, 1, {3, 5}));
126
127 result = con.Query("SELECT * FROM (SELECT DISTINCT ON (i) i, j FROM integers) tbl1 WHERE i <> 2");
128 REQUIRE(CHECK_COLUMN(result, 0, {4}));
129 REQUIRE(CHECK_COLUMN(result, 1, {5}));
130
131 // order by a column that does not exist in the SELECT clause
132 result = con.Query("SELECT DISTINCT ON (i) i, j FROM integers ORDER BY k");
133 REQUIRE(CHECK_COLUMN(result, 0, {2, 4}));
134 REQUIRE(CHECK_COLUMN(result, 1, {3, 5}));
135 // equivalent to this, but without projecting the k
136 result = con.Query("SELECT DISTINCT ON (i) i, j, k FROM integers ORDER BY k");
137 REQUIRE(CHECK_COLUMN(result, 0, {2, 4}));
138 REQUIRE(CHECK_COLUMN(result, 1, {3, 5}));
139 REQUIRE(CHECK_COLUMN(result, 2, {5, 6}));
140
141 // binding of DISTINCT ON with different column names
142 result = con.Query("SELECT DISTINCT ON (integers.i) i, j FROM integers ORDER BY 1, 2");
143 REQUIRE(CHECK_COLUMN(result, 0, {2, 4}));
144 REQUIRE(CHECK_COLUMN(result, 1, {3, 5}));
145 result = con.Query("SELECT DISTINCT ON (i) integers.i, integers.j FROM integers ORDER BY 1, 2");
146 REQUIRE(CHECK_COLUMN(result, 0, {2, 4}));
147 REQUIRE(CHECK_COLUMN(result, 1, {3, 5}));
148 result = con.Query("SELECT DISTINCT ON (integers.i) integers.i, integers.j FROM integers ORDER BY i, j");
149 REQUIRE(CHECK_COLUMN(result, 0, {2, 4}));
150 REQUIRE(CHECK_COLUMN(result, 1, {3, 5}));
151
152 // out of bounds
153 REQUIRE_FAIL(con.Query("SELECT DISTINCT ON (2) i FROM integers"));
154}
155