1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test case insensitive collation", "[collate]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11
12 REQUIRE_NO_FAIL(con.Query("CREATE TABLE collate_test(s VARCHAR COLLATE NOCASE)"));
13 REQUIRE_NO_FAIL(con.Query("INSERT INTO collate_test VALUES ('hello'), ('WoRlD'), ('world'), ('Mühleisen')"));
14
15 // collate in equality
16 result = con.Query("SELECT * FROM collate_test WHERE s='HeLlo'");
17 REQUIRE(CHECK_COLUMN(result, 0, {"hello"}));
18 result = con.Query("SELECT * FROM collate_test WHERE s='MÜhleisen'");
19 REQUIRE(CHECK_COLUMN(result, 0, {"Mühleisen"}));
20 result = con.Query("SELECT * FROM collate_test WHERE s='world'");
21 REQUIRE(CHECK_COLUMN(result, 0, {"WoRlD", "world"}));
22
23 // join with collation
24 REQUIRE_NO_FAIL(con.Query("CREATE TABLE collate_join_table(s VARCHAR, i INTEGER)"));
25 REQUIRE_NO_FAIL(con.Query("INSERT INTO collate_join_table VALUES ('HeLlO', 1), ('mÜHLEISEN', 3)"));
26
27 result = con.Query("SELECT collate_test.s, collate_join_table.s, i FROM collate_test JOIN collate_join_table ON "
28 "(collate_test.s=collate_join_table.s) ORDER BY i");
29 REQUIRE(CHECK_COLUMN(result, 0, {"hello", "Mühleisen"}));
30 REQUIRE(CHECK_COLUMN(result, 1, {"HeLlO", "mÜHLEISEN"}));
31 REQUIRE(CHECK_COLUMN(result, 2, {1, 3}));
32
33 // ORDER BY with collation
34 REQUIRE_NO_FAIL(con.Query("DROP TABLE collate_test"));
35 REQUIRE_NO_FAIL(con.Query("CREATE TABLE collate_test(s VARCHAR COLLATE NOCASE)"));
36 REQUIRE_NO_FAIL(con.Query("INSERT INTO collate_test VALUES ('Hallo'), ('ham'), ('HELLO'), ('hElp')"));
37
38 result = con.Query("SELECT * FROM collate_test ORDER BY s");
39 REQUIRE(CHECK_COLUMN(result, 0, {"Hallo", "ham", "HELLO", "hElp"}));
40
41 // DISTINCT with collation
42 REQUIRE_NO_FAIL(con.Query("DROP TABLE collate_test"));
43 REQUIRE_NO_FAIL(con.Query("CREATE TABLE collate_test(s VARCHAR COLLATE NOCASE)"));
44 REQUIRE_NO_FAIL(con.Query("INSERT INTO collate_test VALUES ('Hallo'), ('hallo')"));
45
46 result = con.Query("SELECT DISTINCT s FROM collate_test");
47 REQUIRE(CHECK_COLUMN(result, 0, {"Hallo"}));
48
49 // LIKE with collation: not yet supported
50 // REQUIRE_NO_FAIL(con.Query("DROP TABLE collate_test"));
51 // REQUIRE_NO_FAIL(con.Query("CREATE TABLE collate_test(s VARCHAR COLLATE NOCASE)"));
52 // REQUIRE_NO_FAIL(con.Query("INSERT INTO collate_test VALUES ('Hallo'), ('hallo')"));
53
54 // result = con.Query("SELECT * FROM collate_test WHERE s LIKE 'h%'");
55 // REQUIRE(CHECK_COLUMN(result, 0, {"Hallo", "hallo"}));
56 // result = con.Query("SELECT * FROM collate_test WHERE s LIKE 'HA%'");
57 // REQUIRE(CHECK_COLUMN(result, 0, {"Hallo", "hallo"}));
58}
59
60TEST_CASE("Test accent insensitive collation", "[collate]") {
61 unique_ptr<QueryResult> result;
62 DuckDB db(nullptr);
63 Connection con(db);
64
65 REQUIRE_NO_FAIL(con.Query("CREATE TABLE collate_test(s VARCHAR COLLATE NOACCENT)"));
66 REQUIRE_NO_FAIL(con.Query("INSERT INTO collate_test VALUES ('Mühleisen'), ('Hëllö')"));
67
68 // collate in equality
69 result = con.Query("SELECT * FROM collate_test WHERE s='Muhleisen'");
70 REQUIRE(CHECK_COLUMN(result, 0, {"Mühleisen"}));
71 result = con.Query("SELECT * FROM collate_test WHERE s='mühleisen'");
72 REQUIRE(CHECK_COLUMN(result, 0, {}));
73 result = con.Query("SELECT * FROM collate_test WHERE s='Hello'");
74 REQUIRE(CHECK_COLUMN(result, 0, {"Hëllö"}));
75
76 // join with collation
77 REQUIRE_NO_FAIL(con.Query("CREATE TABLE collate_join_table(s VARCHAR, i INTEGER)"));
78 REQUIRE_NO_FAIL(con.Query("INSERT INTO collate_join_table VALUES ('Hello', 1), ('Muhleisen', 3)"));
79
80 result = con.Query("SELECT collate_test.s, collate_join_table.s, i FROM collate_test JOIN collate_join_table ON "
81 "(collate_test.s=collate_join_table.s) ORDER BY 1");
82 REQUIRE(CHECK_COLUMN(result, 0, {"Hëllö", "Mühleisen"}));
83 REQUIRE(CHECK_COLUMN(result, 1, {"Hello", "Muhleisen"}));
84 REQUIRE(CHECK_COLUMN(result, 2, {1, 3}));
85
86 // ORDER BY with collation
87 REQUIRE_NO_FAIL(con.Query("DROP TABLE collate_test"));
88 REQUIRE_NO_FAIL(con.Query("CREATE TABLE collate_test(s VARCHAR COLLATE NOACCENT)"));
89 REQUIRE_NO_FAIL(con.Query("INSERT INTO collate_test VALUES ('Hällo'), ('Hallo'), ('Hello')"));
90
91 result = con.Query("SELECT * FROM collate_test ORDER BY s");
92 REQUIRE(CHECK_COLUMN(result, 0, {"Hällo", "Hallo", "Hello"}));
93
94 // DISTINCT with collation
95 REQUIRE_NO_FAIL(con.Query("DROP TABLE collate_test"));
96 REQUIRE_NO_FAIL(con.Query("CREATE TABLE collate_test(s VARCHAR COLLATE NOACCENT)"));
97 REQUIRE_NO_FAIL(con.Query("INSERT INTO collate_test VALUES ('Hällo'), ('Hallo')"));
98
99 result = con.Query("SELECT DISTINCT s FROM collate_test");
100 REQUIRE(CHECK_COLUMN(result, 0, {"Hällo"}));
101
102 // LIKE with collation: not yet supported
103 // REQUIRE_NO_FAIL(con.Query("DROP TABLE collate_test"));
104 // REQUIRE_NO_FAIL(con.Query("CREATE TABLE collate_test(s VARCHAR COLLATE NOACCENT)"));
105 // REQUIRE_NO_FAIL(con.Query("INSERT INTO collate_test VALUES ('Hällo'), ('Hallö')"));
106
107 // result = con.Query("SELECT * FROM collate_test WHERE s LIKE '%a%'");
108 // REQUIRE(CHECK_COLUMN(result, 0, {"Hällo", "Hallö"}));
109 // result = con.Query("SELECT * FROM collate_test WHERE s LIKE '%_ö'");
110 // REQUIRE(CHECK_COLUMN(result, 0, {"Hällo", "Hallö"}));
111 // result = con.Query("SELECT * FROM collate_test WHERE s LIKE '%_ó'");
112 // REQUIRE(CHECK_COLUMN(result, 0, {"Hällo", "Hallö"}));
113}
114
115TEST_CASE("Test combined collations", "[collate]") {
116 unique_ptr<QueryResult> result;
117 DuckDB db(nullptr);
118 Connection con(db);
119
120 REQUIRE_NO_FAIL(con.Query("CREATE TABLE collate_test(s VARCHAR COLLATE NOACCENT.NOCASE)"));
121 REQUIRE_NO_FAIL(con.Query("INSERT INTO collate_test VALUES ('Mühleisen'), ('Hëllö')"));
122
123 // collate in equality
124 result = con.Query("SELECT * FROM collate_test WHERE s='Muhleisen'");
125 REQUIRE(CHECK_COLUMN(result, 0, {"Mühleisen"}));
126 result = con.Query("SELECT * FROM collate_test WHERE s='muhleisen'");
127 REQUIRE(CHECK_COLUMN(result, 0, {"Mühleisen"}));
128 result = con.Query("SELECT * FROM collate_test WHERE s='hEllô'");
129 REQUIRE(CHECK_COLUMN(result, 0, {"Hëllö"}));
130}
131
132TEST_CASE("Test COLLATE in individual expressions", "[collate]") {
133 unique_ptr<QueryResult> result;
134 DuckDB db(nullptr);
135 Connection con(db);
136
137 REQUIRE_NO_FAIL(con.Query("CREATE TABLE collate_test(s VARCHAR)"));
138 REQUIRE_NO_FAIL(con.Query("INSERT INTO collate_test VALUES ('hEllO'), ('WöRlD'), ('wozld')"));
139
140 // collate in equality
141 result = con.Query("SELECT 'hëllo' COLLATE NOACCENT='hello'");
142 REQUIRE(CHECK_COLUMN(result, 0, {true}));
143 result = con.Query("SELECT * FROM collate_test WHERE s='hello'");
144 REQUIRE(CHECK_COLUMN(result, 0, {}));
145 result = con.Query("SELECT * FROM collate_test WHERE s='hello' COLLATE NOCASE");
146 REQUIRE(CHECK_COLUMN(result, 0, {"hEllO"}));
147 result = con.Query("SELECT * FROM collate_test WHERE s COLLATE NOCASE='hello'");
148 REQUIRE(CHECK_COLUMN(result, 0, {"hEllO"}));
149
150 // conflict in collate statements results in an error
151 REQUIRE_FAIL(con.Query("SELECT * FROM collate_test WHERE s COLLATE NOCASE='hello' COLLATE NOACCENT"));
152
153 // we can also do this in the order
154 result = con.Query("SELECT * FROM collate_test ORDER BY s COLLATE NOCASE");
155 REQUIRE(CHECK_COLUMN(result, 0, {"hEllO", "wozld", "WöRlD"}));
156 result = con.Query("SELECT * FROM collate_test ORDER BY s COLLATE NOCASE.NOACCENT");
157 REQUIRE(CHECK_COLUMN(result, 0, {"hEllO", "WöRlD", "wozld"}));
158}
159
160TEST_CASE("Test default collations", "[collate]") {
161 unique_ptr<QueryResult> result;
162 DBConfig config;
163 config.collation = "NOCASE";
164 DuckDB db(nullptr, &config);
165 Connection con(db);
166
167 REQUIRE_NO_FAIL(con.Query("CREATE TABLE collate_test(s VARCHAR)"));
168 REQUIRE_NO_FAIL(con.Query("INSERT INTO collate_test VALUES ('hEllO'), ('WöRlD'), ('wozld')"));
169
170 // collate in equality
171 result = con.Query("SELECT COUNT(*) FROM collate_test WHERE 'BlA'='bLa'");
172 REQUIRE(CHECK_COLUMN(result, 0, {3}));
173 result = con.Query("SELECT * FROM collate_test WHERE s='hello'");
174 REQUIRE(CHECK_COLUMN(result, 0, {"hEllO"}));
175
176 // collate in order
177 result = con.Query("SELECT * FROM collate_test ORDER BY s");
178 REQUIRE(CHECK_COLUMN(result, 0, {"hEllO", "wozld", "WöRlD"}));
179
180 // switch default collate using pragma
181 REQUIRE_NO_FAIL(con.Query("PRAGMA default_collation='NOCASE.NOACCENT'"));
182
183 result = con.Query("SELECT * FROM collate_test ORDER BY s");
184 REQUIRE(CHECK_COLUMN(result, 0, {"hEllO", "WöRlD", "wozld"}));
185}
186
187TEST_CASE("Get list of collations", "[collate]") {
188 unique_ptr<QueryResult> result;
189 DuckDB db(nullptr);
190 Connection con(db);
191
192 result = con.Query("PRAGMA collations");
193 REQUIRE(CHECK_COLUMN(result, 0, {"noaccent", "nocase"}));
194
195 REQUIRE_FAIL(con.Query("PRAGMA collations=3"));
196}
197
198TEST_CASE("Test unsupported collations", "[collate]") {
199 unique_ptr<QueryResult> result;
200 DuckDB db(nullptr);
201 Connection con(db);
202
203 REQUIRE_FAIL(con.Query("CREATE TABLE collate_test(s VARCHAR COLLATE blabla)"));
204 REQUIRE_FAIL(con.Query("CREATE TABLE collate_test(s VARCHAR COLLATE NOACCENT.NOACCENT)"));
205 REQUIRE_FAIL(con.Query("CREATE TABLE collate_test(s VARCHAR COLLATE 1)"));
206 REQUIRE_FAIL(con.Query("CREATE TABLE collate_test(s VARCHAR COLLATE 'hello')"));
207
208 REQUIRE_FAIL(con.Query("PRAGMA default_collation='blabla'"));
209}
210