1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Single PRIMARY KEY constraint", "[constraints]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11
12 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER PRIMARY KEY, j INTEGER)"));
13
14 // insert two conflicting pairs at the same time
15 REQUIRE_FAIL(con.Query("INSERT INTO integers VALUES (3, 4), (3, 5)"));
16
17 // insert unique values
18 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (3, 4), (2, 5)"));
19
20 result = con.Query("SELECT * FROM integers");
21 REQUIRE(CHECK_COLUMN(result, 0, {3, 2}));
22 REQUIRE(CHECK_COLUMN(result, 1, {4, 5}));
23
24 // insert a duplicate value as part of a chain of values
25 REQUIRE_FAIL(con.Query("INSERT INTO integers VALUES (6, 6), (3, 4);"));
26
27 // now insert just the first value
28 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (6, 6);"));
29
30 result = con.Query("SELECT * FROM integers");
31 REQUIRE(CHECK_COLUMN(result, 0, {3, 2, 6}));
32 REQUIRE(CHECK_COLUMN(result, 1, {4, 5, 6}));
33
34 // insert NULL value in PRIMARY KEY is not allowed
35 REQUIRE_FAIL(con.Query("INSERT INTO integers VALUES (NULL, 4);"));
36
37 // update NULL is also not allowed
38 REQUIRE_FAIL(con.Query("UPDATE integers SET i=NULL;"));
39
40 // insert the same value from multiple connections
41 // NOTE: this tests current behavior
42 // this can potentially change in the future
43 Connection con2(db);
44
45 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
46 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION"));
47
48 // insert from first connection succeeds
49 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (7, 8);"));
50 // insert from second connection also succeeds
51 REQUIRE_NO_FAIL(con2.Query("INSERT INTO integers VALUES (7, 33);"));
52
53 // now committing the first transaction works
54 REQUIRE_NO_FAIL(con.Query("COMMIT"));
55 // but the second transaction results in a conflict
56 REQUIRE_FAIL(con2.Query("COMMIT"));
57}
58
59TEST_CASE("Multiple PRIMARY KEY constraint", "[constraints]") {
60 unique_ptr<QueryResult> result;
61 DuckDB db(nullptr);
62 Connection con(db);
63
64 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER, j VARCHAR, PRIMARY KEY(i, j))"));
65
66 // insert unique values
67 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (3, 'hello'), (3, 'world')"));
68
69 result = con.Query("SELECT * FROM integers");
70 REQUIRE(CHECK_COLUMN(result, 0, {3, 3}));
71 REQUIRE(CHECK_COLUMN(result, 1, {"hello", "world"}));
72
73 // insert a duplicate value as part of a chain of values
74 REQUIRE_FAIL(con.Query("INSERT INTO integers VALUES (6, 'bla'), (3, 'hello');"));
75 // now insert just the first value
76 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (6, 'bla');"));
77
78 result = con.Query("SELECT * FROM integers");
79 REQUIRE(CHECK_COLUMN(result, 0, {3, 3, 6}));
80 REQUIRE(CHECK_COLUMN(result, 1, {"hello", "world", "bla"}));
81}
82
83TEST_CASE("PRIMARY KEY and transactions", "[constraints]") {
84 unique_ptr<QueryResult> result;
85 DuckDB db(nullptr);
86 Connection con(db);
87
88 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER PRIMARY KEY)"));
89
90 // rollback
91 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
92 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1);"));
93 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
94
95 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1);"));
96
97 result = con.Query("SELECT * FROM integers");
98 REQUIRE(CHECK_COLUMN(result, 0, {1}));
99}
100
101TEST_CASE("PRIMARY KEY and update/delete", "[constraints]") {
102 unique_ptr<QueryResult> result;
103 DuckDB db(nullptr);
104 Connection con(db);
105
106 // create a table
107 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER PRIMARY KEY, b INTEGER);"));
108 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 1), (12, 2), (13, 3)"));
109 // this update affects a non-primary key column, should just work
110 REQUIRE_NO_FAIL(con.Query("UPDATE test SET b=2 WHERE b=3;"));
111 //! Set only the first key higher, should not work as this introduces a duplicate key!
112 REQUIRE_FAIL(con.Query("UPDATE test SET a=a+1 WHERE b=1;"));
113 //! Set all keys to 4, results in a conflict!
114 REQUIRE_FAIL(con.Query("UPDATE test SET a=4;"));
115
116 result = con.Query("SELECT * FROM test;");
117 REQUIRE(CHECK_COLUMN(result, 0, {11, 12, 13}));
118 REQUIRE(CHECK_COLUMN(result, 1, {1, 2, 2}));
119
120 // delete and insert the same value should just work
121 REQUIRE_NO_FAIL(con.Query("DELETE FROM test WHERE a=11"));
122 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 1);"));
123
124 // insert a duplicate should fail
125 REQUIRE_FAIL(con.Query("INSERT INTO test VALUES (11, 1);"));
126
127 // update one key
128 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a=4 WHERE b=1;"));
129
130 result = con.Query("SELECT * FROM test ORDER BY a;");
131 REQUIRE(CHECK_COLUMN(result, 0, {4, 12, 13}));
132 REQUIRE(CHECK_COLUMN(result, 1, {1, 2, 2}));
133
134 // set a column to NULL should fail
135 REQUIRE_FAIL(con.Query("UPDATE test SET a=NULL WHERE b=1;"));
136 REQUIRE_FAIL(con.Query("UPDATE test SET a=NULL;"));
137}
138
139TEST_CASE("PRIMARY KEY and update/delete on multiple columns", "[constraints]") {
140 unique_ptr<QueryResult> result;
141 DuckDB db(nullptr);
142 Connection con(db);
143
144 //! create a table
145 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b VARCHAR, PRIMARY KEY(a, b));"));
146 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 'hello'), (12, 'world'), (13, 'blablabla')"));
147
148 //! update one of the columns, should work as it does not introduce duplicates
149 REQUIRE_NO_FAIL(con.Query("UPDATE test SET b='pandas';"));
150 result = con.Query("SELECT * FROM test ORDER BY a;");
151 REQUIRE(CHECK_COLUMN(result, 0, {11, 12, 13}));
152 REQUIRE(CHECK_COLUMN(result, 1, {Value("pandas"), Value("pandas"), Value("pandas")}));
153 //! Set every key one higher, should also work without conflicts
154 REQUIRE_FAIL(con.Query("UPDATE test SET a=a+1;"));
155
156 result = con.Query("SELECT * FROM test ORDER BY a;");
157 REQUIRE(CHECK_COLUMN(result, 0, {11, 12, 13}));
158 REQUIRE(CHECK_COLUMN(result, 1, {Value("pandas"), Value("pandas"), Value("pandas")}));
159
160 //! Set only the first key higher, should not work as this introduces a duplicate key!
161 REQUIRE_FAIL(con.Query("UPDATE test SET a=13 WHERE a=12;"));
162
163 //! Set all keys to 4, results in a conflict!
164 REQUIRE_FAIL(con.Query("UPDATE test SET a=4;"));
165 result = con.Query("SELECT * FROM test ORDER BY a;");
166 REQUIRE(CHECK_COLUMN(result, 0, {11, 12, 13}));
167 REQUIRE(CHECK_COLUMN(result, 1, {Value("pandas"), Value("pandas"), Value("pandas")}));
168
169 //! delete and insert the same value should just work
170 REQUIRE_NO_FAIL(con.Query("DELETE FROM test WHERE a=12"));
171 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (12, 'pandas');"));
172
173 //! insert a duplicate should fail
174 REQUIRE_FAIL(con.Query("INSERT INTO test VALUES (12, 'pandas');"));
175
176 //! update one key
177 REQUIRE_NO_FAIL(con.Query("DELETE FROM test WHERE a=12"));
178 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (4, 'pandas');"));
179 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a=4 WHERE a=12;"));
180
181 result = con.Query("SELECT * FROM test ORDER BY a;");
182 REQUIRE(CHECK_COLUMN(result, 0, {4, 11, 13}));
183 REQUIRE(CHECK_COLUMN(result, 1, {Value("pandas"), Value("pandas"), Value("pandas")}));
184
185 //! set a column to NULL should fail
186 REQUIRE_FAIL(con.Query("UPDATE test SET b=NULL WHERE a=13;"));
187}
188
189TEST_CASE("PRIMARY KEY prefix stress test multiple columns", "[constraints]") {
190 unique_ptr<QueryResult> result;
191 DuckDB db(nullptr);
192 Connection con(db);
193
194 //! create a table
195 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b VARCHAR, PRIMARY KEY(a, b));"));
196
197 //! Insert 300 values
198 for (idx_t idx = 0; idx < 300; idx++) {
199 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (" + to_string(idx) + ", 'hello_" + to_string(idx) + "')"));
200 }
201
202 //! Inserting same values should fail
203 for (idx_t idx = 0; idx < 300; idx++) {
204 REQUIRE_FAIL(con.Query("INSERT INTO test VALUES (" + to_string(idx) + ", 'hello_" + to_string(idx) + "')"));
205 }
206
207 //! Update integer a on 1000 should work since there are no duplicates
208 REQUIRE_NO_FAIL(con.Query("UPDATE test SET a=a+1000;"));
209
210 //! Now inserting same 1000 values should work
211 for (idx_t idx = 0; idx < 300; idx++) {
212 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (" + to_string(idx) + ", 'hello_" + to_string(idx) + "')"));
213 }
214
215 //! This update should fail and stress test the deletes on hello_ prefixes
216 REQUIRE_FAIL(con.Query("UPDATE test SET a=a+1000;"));
217
218 //! Should fail for same reason as above, just checking element per element to see if no one is escaping
219 for (idx_t idx = 0; idx < 300; idx++) {
220 REQUIRE_FAIL(
221 con.Query("INSERT INTO test VALUES (" + to_string(idx + 1000) + ", 'hello_" + to_string(idx) + "')"));
222 }
223}
224
225TEST_CASE("PRIMARY KEY and update/delete in the same transaction", "[constraints]") {
226 unique_ptr<QueryResult> result;
227 DuckDB db(nullptr);
228 Connection con(db);
229
230 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER PRIMARY KEY)"));
231
232 // rollback
233 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
234 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1);"));
235 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET i=33;"));
236 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
237
238 // insert the same values again
239 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1);"));
240 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (33);"));
241
242 result = con.Query("SELECT * FROM integers ORDER BY i");
243 REQUIRE(CHECK_COLUMN(result, 0, {1, 33}));
244
245 // update and then insert
246 REQUIRE_NO_FAIL(con.Query("DROP TABLE integers"));
247 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER PRIMARY KEY)"));
248 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1);"));
249 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET i=33;"));
250 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1);"));
251
252 result = con.Query("SELECT * FROM integers ORDER BY i");
253 REQUIRE(CHECK_COLUMN(result, 0, {1, 33}));
254}
255
256TEST_CASE("Test appending the same value many times to a primary key column", "[constraints]") {
257 unique_ptr<QueryResult> result;
258 DuckDB db(nullptr);
259 Connection con(db);
260
261 con.Query("CREATE TABLE integers(i INTEGER PRIMARY KEY)");
262 // insert a bunch of values into the index and query the index
263 for (int32_t val = 0; val < 100; val++) {
264 result = con.Query("SELECT COUNT(*) FROM integers WHERE i = " + to_string(val));
265 REQUIRE(CHECK_COLUMN(result, 0, {0}));
266
267 con.Query("INSERT INTO integers VALUES ($1)", val);
268
269 result = con.Query("SELECT COUNT(*) FROM integers WHERE i = " + to_string(val));
270 REQUIRE(CHECK_COLUMN(result, 0, {1}));
271 }
272 for (int32_t val = 0; val < 100; val++) {
273 result = con.Query("SELECT COUNT(*) FROM integers WHERE i + i = " + to_string(val) + "+" + to_string(val));
274 REQUIRE(CHECK_COLUMN(result, 0, {1}));
275 result = con.Query("SELECT COUNT(*) FROM integers WHERE i = " + to_string(val));
276 REQUIRE(CHECK_COLUMN(result, 0, {1}));
277 }
278 // now insert the same values, this should fail this time
279 for (int32_t it = 0; it < 10; it++) {
280 int32_t val = 64;
281 result = con.Query("SELECT COUNT(*) FROM integers WHERE i + i = 64+" + to_string(val));
282 REQUIRE(CHECK_COLUMN(result, 0, {1}));
283 result = con.Query("SELECT COUNT(*) FROM integers WHERE i = " + to_string(val));
284 REQUIRE(CHECK_COLUMN(result, 0, {1}));
285 result = con.Query("INSERT INTO integers VALUES ($1)", val);
286 REQUIRE_FAIL(result);
287 }
288
289 // now test that the counts are correct
290 result = con.Query("SELECT COUNT(*), COUNT(DISTINCT i) FROM integers");
291 REQUIRE(CHECK_COLUMN(result, 0, {100}));
292 REQUIRE(CHECK_COLUMN(result, 1, {100}));
293}
294
295TEST_CASE("PRIMARY KEY and concurency conflicts", "[constraints]") {
296 unique_ptr<QueryResult> result;
297 DuckDB db(nullptr);
298 Connection con(db), con2(db);
299
300 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER PRIMARY KEY)"));
301
302 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3)"));
303
304 // con starts a transaction and modifies the second value
305 REQUIRE_NO_FAIL(con.Query("BEGIN TRANSACTION"));
306 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET i=4 WHERE i=2"));
307
308 // con2 can't update the second value
309 REQUIRE_FAIL(con2.Query("UPDATE integers SET i=4 WHERE i=2"));
310 REQUIRE_FAIL(con2.Query("UPDATE integers SET i=5 WHERE i=2"));
311 // nor can it delete it
312 REQUIRE_FAIL(con2.Query("DELETE FROM integers WHERE i=2"));
313
314 // we tried to set i=5 in con2 but it failed, we can set it in con1 now though
315 REQUIRE_NO_FAIL(con.Query("UPDATE integers SET i=5 WHERE i=3"));
316 // rollback con1
317 REQUIRE_NO_FAIL(con.Query("ROLLBACK"));
318
319 // now we can perform the changes in con2
320 REQUIRE_NO_FAIL(con2.Query("BEGIN TRANSACTION"));
321 REQUIRE_NO_FAIL(con2.Query("UPDATE integers SET i=4 WHERE i=2"));
322 REQUIRE_NO_FAIL(con2.Query("UPDATE integers SET i=5 WHERE i=3"));
323
324 // check the results, con1 still gets the old results
325 result = con.Query("SELECT * FROM integers ORDER BY i");
326 REQUIRE(CHECK_COLUMN(result, 0, {1, 2, 3}));
327 result = con2.Query("SELECT * FROM integers ORDER BY i");
328 REQUIRE(CHECK_COLUMN(result, 0, {1, 4, 5}));
329
330 // now commit
331 REQUIRE_NO_FAIL(con2.Query("COMMIT"));
332
333 // check the results again, both get the same (new) results now
334 result = con.Query("SELECT * FROM integers ORDER BY i");
335 REQUIRE(CHECK_COLUMN(result, 0, {1, 4, 5}));
336 result = con2.Query("SELECT * FROM integers ORDER BY i");
337 REQUIRE(CHECK_COLUMN(result, 0, {1, 4, 5}));
338}
339
340TEST_CASE("ART FP String Constraint", "[constraints]") {
341 unique_ptr<QueryResult> result;
342 DuckDB db(nullptr);
343 Connection con(db);
344
345 REQUIRE_NO_FAIL(con.Query("CREATE TABLE numbers(i varchar PRIMARY KEY, j INTEGER)"));
346
347 //! insert two conflicting pairs at the same time
348 REQUIRE_FAIL(con.Query("INSERT INTO numbers VALUES ('1', 4), ('1', 5)"));
349
350 //! insert unique values
351 REQUIRE_NO_FAIL(con.Query("INSERT INTO numbers VALUES ('1', 4), ('2', 5)"));
352
353 result = con.Query("SELECT * FROM numbers");
354 REQUIRE(CHECK_COLUMN(result, 0, {"1", "2"}));
355 REQUIRE(CHECK_COLUMN(result, 1, {4, 5}));
356
357 // //! insert a duplicate value as part of a chain of values
358 REQUIRE_FAIL(con.Query("INSERT INTO numbers VALUES ('6', 6), ('1', 4);"));
359 //
360 // //! now insert just the first value
361 REQUIRE_NO_FAIL(con.Query("INSERT INTO numbers VALUES ('6', 6);"));
362 //
363 result = con.Query("SELECT * FROM numbers");
364 REQUIRE(CHECK_COLUMN(result, 0, {"1", "2", "6"}));
365 REQUIRE(CHECK_COLUMN(result, 1, {4, 5, 6}));
366 //! insert NULL value in PRIMARY KEY is not allowed
367 REQUIRE_FAIL(con.Query("INSERT INTO numbers VALUES (NULL, 4);"));
368 //! update NULL is also not allowed
369 REQUIRE_FAIL(con.Query("UPDATE numbers SET i=NULL;"));
370}
371
372TEST_CASE("PRIMARY KEY constraint on more than two columns", "[constraints]") {
373 unique_ptr<QueryResult> result;
374 DuckDB db(nullptr);
375 Connection con(db);
376
377 REQUIRE_NO_FAIL(con.Query(
378 "CREATE TABLE numbers(a integer, b integer, c integer, d integer, e integer, PRIMARY KEY(a,b,c,d,e))"));
379
380 //! insert two conflicting pairs at the same time
381 REQUIRE_FAIL(con.Query("INSERT INTO numbers VALUES (1,1,1,1,1), (1,1,1,1,1)"));
382
383 //! insert unique values
384 REQUIRE_NO_FAIL(con.Query("INSERT INTO numbers VALUES (1,1,1,1,1),(1,2,1,1,1),(1,1,2,1,1),(2,2,2,2,2)"));
385
386 //! insert a duplicate value as part of a chain of values
387 REQUIRE_FAIL(con.Query("INSERT INTO numbers VALUES (1,1,1,1,1),(1,1,1,1,4);"));
388
389 //! now insert just the second value
390 REQUIRE_NO_FAIL(con.Query("INSERT INTO numbers VALUES (1,1,1,1,4);"));
391
392 //! this should fail since will cause a duplicate
393 REQUIRE_FAIL(con.Query("UPDATE numbers SET c=1 WHERE c=2"));
394}
395
396TEST_CASE("PRIMARY KEY constraint that only covers a subset of the columns", "[constraints]") {
397 unique_ptr<QueryResult> result;
398 DuckDB db(nullptr);
399 Connection con(db);
400
401 REQUIRE_NO_FAIL(
402 con.Query("CREATE TABLE numbers(a integer, b integer, c integer, d integer, e integer, PRIMARY KEY(a,b))"));
403
404 //! insert two conflicting pairs at the same time
405 REQUIRE_FAIL(con.Query("INSERT INTO numbers VALUES (1,1,1,1,1), (1,1,1,1,1)"));
406
407 //! insert unique values
408 REQUIRE_NO_FAIL(con.Query("INSERT INTO numbers VALUES (1,1,1,1,1),(1,2,1,1,1),(2,1,2,1,1),(2,2,2,2,2)"));
409
410 //! insert a duplicate value as part of a chain of values
411 REQUIRE_FAIL(con.Query("INSERT INTO numbers VALUES (1,1,1,1,1),(1,5,1,1,4);"));
412
413 //! now insert just the second value
414 REQUIRE_NO_FAIL(con.Query("INSERT INTO numbers VALUES (1,5,1,1,4);"));
415
416 //! this should work since is not part of primary key
417 REQUIRE_NO_FAIL(con.Query("UPDATE numbers SET c=1 WHERE c=2"));
418
419 //! this should fail since is will cause a duplicate
420 REQUIRE_FAIL(con.Query("UPDATE numbers SET b=1 WHERE b=2"));
421
422 //! this should work since it won't cause a duplicate
423 REQUIRE_NO_FAIL(con.Query("UPDATE numbers SET b=3 WHERE b=2"));
424}
425
426TEST_CASE("PRIMARY KEY constraint on multiple string columns with overlapping values", "[constraints]") {
427 unique_ptr<QueryResult> result;
428 DuckDB db(nullptr);
429 Connection con(db);
430
431 REQUIRE_NO_FAIL(con.Query("CREATE TABLE tst(a varchar, b varchar,PRIMARY KEY(a,b))"));
432
433 //! insert two conflicting pairs at the same time
434 REQUIRE_FAIL(con.Query("INSERT INTO tst VALUES ('hell', 'hello'), ('hell','hello')"));
435
436 //! insert unique values
437 REQUIRE_NO_FAIL(
438 con.Query("INSERT INTO tst VALUES ('hell', 'hello'), ('hello','hell'), ('hel','hell'), ('hell','hel')"));
439
440 //! insert a duplicate value as part of a chain of values
441 REQUIRE_FAIL(con.Query("INSERT INTO tst VALUES ('hell', 'hello'),('hel', 'hello');"));
442
443 //! now insert just the second value
444 REQUIRE_NO_FAIL(con.Query("INSERT INTO tst VALUES ('hel', 'hello');"));
445
446 //! this should fail since is will cause a duplicate
447 REQUIRE_FAIL(con.Query("UPDATE tst SET b='hello' WHERE b='hel' "));
448
449 //! this should work since it won't cause a duplicate
450 REQUIRE_NO_FAIL(con.Query("UPDATE tst SET b='hell' WHERE b='hel'"));
451}
452
453TEST_CASE("Multi-column boolean PRIMARY KEY constraint", "[constraints]") {
454 unique_ptr<QueryResult> result;
455 DuckDB db(nullptr);
456 Connection con(db);
457
458 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(i INTEGER, j BOOLEAN, PRIMARY KEY(i, j))"));
459 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1, false), (1, true), (2, false)"));
460 // duplicate value!
461 REQUIRE_FAIL(con.Query("INSERT INTO integers VALUES (1, false)"));
462 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (2, true)"));
463
464 result = con.Query("SELECT * FROM integers ORDER BY 1, 2");
465 REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 2, 2}));
466 REQUIRE(CHECK_COLUMN(result, 1, {false, true, false, true}));
467}
468