1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7// The tests in this file are taken from https://www.manuelrigger.at/dbms-bugs/
8TEST_CASE("Test queries found by Rigger that cause problems in other systems", "[rigger]") {
9 unique_ptr<QueryResult> result;
10 DuckDB db(nullptr);
11 Connection con(db);
12 con.EnableQueryVerification();
13
14 // TiDB
15 SECTION("#5 A predicate column1 = -column2 incorrectly evaluates to false for 0 values") {
16 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 FLOAT);"));
17 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1(c0 FLOAT);"));
18 REQUIRE_NO_FAIL(con.Query("INSERT INTO t1(c0) VALUES (0);"));
19 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (0);"));
20 result = con.Query("SELECT t1.c0 FROM t1, t0 WHERE t0.c0=-t1.c0;");
21 REQUIRE(CHECK_COLUMN(result, 0, {0}));
22 }
23 SECTION("#6 Join on tables with redundant indexes causes a server panic") {
24 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT PRIMARY KEY);"));
25 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1(c0 INT PRIMARY KEY);"));
26 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i0 ON t1(c0);"));
27 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i1 ON t0(c0);"));
28 REQUIRE_NO_FAIL(con.Query("SELECT * FROM t0, t1 WHERE t1.c0=t0.c0;"));
29 }
30 SECTION("#7 Incorrect result for LEFT JOIN and NULL values") {
31 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(t0 INT UNIQUE);"));
32 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1(c0 FLOAT);"));
33 REQUIRE_NO_FAIL(con.Query("INSERT INTO t1(c0) VALUES (0);"));
34 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(t0) VALUES (NULL), (NULL);"));
35 result = con.Query("SELECT t1.c0 FROM t1 LEFT JOIN t0 ON 1;");
36 REQUIRE(CHECK_COLUMN(result, 0, {0, 0}));
37 }
38 SECTION("#8 Query with RIGHT JOIN causes a server panic") {
39 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT);"));
40 REQUIRE_NO_FAIL(con.Query("CREATE VIEW v0(c0) AS SELECT 0 FROM t0 ORDER BY -t0.c0;"));
41 REQUIRE_NO_FAIL(con.Query("SELECT * FROM v0 RIGHT JOIN t0 ON false;"));
42 }
43 // SQLite
44 SECTION("#15 './' LIKE './' does not match") {
45 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 VARCHAR UNIQUE);"));
46 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES ('./');"));
47 result = con.Query("SELECT * FROM t0 WHERE t0.c0 LIKE './';");
48 REQUIRE(CHECK_COLUMN(result, 0, {"./"}));
49 }
50 SECTION("#22 REAL rounding seems to depend on FROM clause") {
51 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0 (c0 VARCHAR);"));
52 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1 (c1 REAL);"));
53 REQUIRE_NO_FAIL(con.Query("INSERT INTO t1(c1) VALUES (8366271098608253588);"));
54 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES ('a');"));
55 result = con.Query("SELECT * FROM t1 WHERE (t1.c1 = CAST(8366271098608253588 AS REAL));");
56 REQUIRE(CHECK_COLUMN(result, 0, {Value::FLOAT(8366271098608253588)}));
57 result = con.Query("SELECT * FROM t0, t1 WHERE (t1.c1 = CAST(8366271098608253588 AS REAL));");
58 REQUIRE(CHECK_COLUMN(result, 0, {"a"}));
59 REQUIRE(CHECK_COLUMN(result, 1, {Value::FLOAT(8366271098608253588)}));
60 result = con.Query("SELECT * FROM t0, t1 WHERE (t1.c1 >= CAST(8366271098608253588 AS REAL) AND t1.c1 <= "
61 "CAST(8366271098608253588 AS REAL));");
62 REQUIRE(CHECK_COLUMN(result, 0, {"a"}));
63 REQUIRE(CHECK_COLUMN(result, 1, {Value::FLOAT(8366271098608253588)}));
64 }
65 SECTION("#24 Query results in a SEGFAULT") {
66 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0 (c0 INT, c1 INT, PRIMARY KEY (c0, c1));"));
67 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1 (c0 INT);"));
68 REQUIRE_NO_FAIL(con.Query("INSERT INTO t1 VALUES (2);"));
69 result = con.Query("SELECT * FROM t0, t1 WHERE (t0.c1 >= 1 OR t0.c1 < 1) AND t0.c0 IN (1, t1.c0) ORDER BY 1;");
70 REQUIRE(CHECK_COLUMN(result, 0, {}));
71 }
72 SECTION("#26 Nested boolean formula with IN operator computes an incorrect result") {
73 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 VARCHAR);"));
74 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES ('val');"));
75 result = con.Query("SELECT * FROM t0 WHERE (((0 <> FALSE) OR NOT (0 = FALSE OR (t0.c0 IN (-1)))) = 0);");
76 REQUIRE(CHECK_COLUMN(result, 0, {"val"}));
77 }
78 SECTION("#57 Null pointer dereference caused by window functions in result-set of EXISTS(SELECT ...)") {
79 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INTEGER);"));
80 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (0);"));
81 result = con.Query("SELECT * FROM t0 WHERE EXISTS (SELECT MIN(c0) OVER (), CUME_DIST() OVER () FROM t0);");
82 REQUIRE(CHECK_COLUMN(result, 0, {0}));
83 result = con.Query(
84 "SELECT * FROM t0 WHERE EXISTS (SELECT MIN(c0) OVER (), CUME_DIST() OVER () FROM t0) BETWEEN 1 AND 1;");
85 REQUIRE(CHECK_COLUMN(result, 0, {0}));
86 }
87 SECTION("#61 DISTINCT malfunctions for IS NULL") {
88 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0 (c0 INTEGER, c1 INTEGER NOT NULL DEFAULT 1, c2 VARCHAR);"));
89 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c2) VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), "
90 "(NULL), (NULL), (NULL), (NULL);"));
91 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c2) VALUES ('a');"));
92 result = con.Query("SELECT DISTINCT * FROM t0 WHERE t0.c0 IS NULL ORDER BY 1, 2, 3;");
93 REQUIRE(CHECK_COLUMN(result, 0, {Value(), Value()}));
94 REQUIRE(CHECK_COLUMN(result, 1, {1, 1}));
95 REQUIRE(CHECK_COLUMN(result, 2, {Value(), "a"}));
96 }
97 // CockroachDB
98 SECTION("#1 Internal error for NATURAL JOIN on INT and INT4 column for VECTORIZE=experimental_on") {
99 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT UNIQUE);"));
100 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1(c0 INT4 UNIQUE);"));
101 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES(0);"));
102 REQUIRE_NO_FAIL(con.Query("INSERT INTO t1(c0) VALUES(0);"));
103 result = con.Query("SELECT * FROM t0 NATURAL JOIN t1;");
104 REQUIRE(CHECK_COLUMN(result, 0, {0}));
105 REQUIRE(CHECK_COLUMN(result, 1, {0}));
106 }
107 SECTION("#1 Internal error for NATURAL JOIN on INT and INT4 column for VECTORIZE=experimental_on") {
108 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 VARCHAR UNIQUE);"));
109 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0 (c0) VALUES ('\\a');"));
110 result = con.Query("SELECT * FROM t0 WHERE c0 LIKE '\\a';");
111 REQUIRE(CHECK_COLUMN(result, 0, {"\\a"}));
112 }
113 SECTION("#4 Incorrect result for IS NULL query on VIEW using SELECT DISTINCT") {
114 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT UNIQUE);"));
115 REQUIRE_NO_FAIL(con.Query("CREATE VIEW v0(c0) AS SELECT DISTINCT t0.c0 FROM t0;"));
116 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0 (c0) VALUES (NULL), (NULL);"));
117 result = con.Query("SELECT * FROM v0 WHERE v0.c0 IS NULL;");
118 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
119 }
120 SECTION("#9 NATURAL JOIN fails with \"duplicate column name\" on view") {
121 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT);"));
122 REQUIRE_NO_FAIL(con.Query("CREATE VIEW v0(c0, c1) AS SELECT DISTINCT c0, c0 FROM t0;"));
123 result = con.Query("SELECT * FROM v0 NATURAL JOIN t0;");
124 REQUIRE(CHECK_COLUMN(result, 0, {}));
125 }
126}
127
128TEST_CASE("Tests found by Rigger", "[rigger]") {
129 unique_ptr<QueryResult> result;
130 DuckDB db(nullptr);
131 Connection con(db);
132 con.EnableQueryVerification();
133 SECTION("489") {
134 // A predicate NOT(NULL OR TRUE) unexpectedly evaluates to TRUE
135 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT);"));
136 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (0);"));
137 result = con.Query("SELECT * FROM t0 WHERE NOT(NULL OR TRUE);");
138 REQUIRE(CHECK_COLUMN(result, 0, {}));
139 result = con.Query("SELECT NULL OR TRUE;");
140 REQUIRE(CHECK_COLUMN(result, 0, {true}));
141 result = con.Query("SELECT NOT(NULL OR TRUE);");
142 REQUIRE(CHECK_COLUMN(result, 0, {false}));
143 }
144 SECTION("490") {
145 // A comparison column=column unexpectedly evaluates to TRUE for column=NULL
146 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT);"));
147 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (NULL);"));
148 result = con.Query("SELECT * FROM t0 WHERE c0 = c0;");
149 REQUIRE(CHECK_COLUMN(result, 0, {}));
150 }
151 SECTION("491") {
152 // PRAGMA table_info provides no output
153 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT);"));
154 result = con.Query("PRAGMA table_info('t0');");
155 REQUIRE(CHECK_COLUMN(result, 0, {0}));
156 REQUIRE(CHECK_COLUMN(result, 1, {"c0"}));
157 REQUIRE(CHECK_COLUMN(result, 2, {"INTEGER"}));
158 REQUIRE(CHECK_COLUMN(result, 3, {false}));
159 REQUIRE(CHECK_COLUMN(result, 4, {Value()}));
160 REQUIRE(CHECK_COLUMN(result, 5, {false}));
161 result = con.Query("SELECT * FROM pragma_table_info('t0');");
162 REQUIRE(CHECK_COLUMN(result, 0, {0}));
163 REQUIRE(CHECK_COLUMN(result, 1, {"c0"}));
164 REQUIRE(CHECK_COLUMN(result, 2, {"INTEGER"}));
165 REQUIRE(CHECK_COLUMN(result, 3, {false}));
166 REQUIRE(CHECK_COLUMN(result, 4, {Value()}));
167 REQUIRE(CHECK_COLUMN(result, 5, {false}));
168 }
169 SECTION("493") {
170 // SIMILAR TO results in an "Unknown error -1
171 result = con.Query("SELECT '' SIMILAR TO '';");
172 REQUIRE(CHECK_COLUMN(result, 0, {true}));
173 }
174 SECTION("495") {
175 // Comparison on UNIQUE NUMERIC column causes a query to omit a row in the result set
176 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 NUMERIC UNIQUE);"));
177 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (1163404482), (0), (488566);"));
178 result = con.Query("SELECT * FROM t0 WHERE c0 > 0.1 ORDER BY 1;");
179 REQUIRE(CHECK_COLUMN(result, 0, {488566, 1163404482}));
180 result = con.Query("SELECT * FROM t0 WHERE c0 >= 0.1 ORDER BY 1;");
181 REQUIRE(CHECK_COLUMN(result, 0, {488566, 1163404482}));
182 result = con.Query("SELECT * FROM t0 WHERE 0.1 < c0 ORDER BY 1;");
183 REQUIRE(CHECK_COLUMN(result, 0, {488566, 1163404482}));
184 result = con.Query("SELECT * FROM t0 WHERE 0.1 <= c0 ORDER BY 1;");
185 REQUIRE(CHECK_COLUMN(result, 0, {488566, 1163404482}));
186 }
187 SECTION("497") {
188 // Comparison of two boolean columns in different tables results in an error "Not implemented: Unimplemented
189 // type for sort"
190 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 BOOL);"));
191 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1(c0 BOOL);"));
192 REQUIRE_NO_FAIL(con.Query("INSERT INTO t1(c0) VALUES (0);"));
193 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (0);"));
194 result = con.Query("SELECT t0.c0 FROM t0, t1 WHERE t1.c0 < t0.c0;");
195 REQUIRE(CHECK_COLUMN(result, 0, {}));
196 }
197 SECTION("503") {
198 // RIGHT JOIN with a predicate that compares two integer columns results in an "Unhandled type" error
199 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT);"));
200 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1(c0 INT);"));
201 result = con.Query("SELECT * FROM t0 RIGHT JOIN t1 ON t0.c0!=t1.c0;");
202 REQUIRE(CHECK_COLUMN(result, 0, {}));
203 REQUIRE(CHECK_COLUMN(result, 1, {}));
204 }
205 SECTION("504") {
206 // INSERT results in an error "Not implemented: Cannot create data from this type"
207 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 BOOLEAN, c1 INT, PRIMARY KEY(c0, c1));"));
208 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c1, c0) VALUES (0, 0);"));
209 result = con.Query("SELECT * FROM t0;");
210 REQUIRE(CHECK_COLUMN(result, 0, {false}));
211 REQUIRE(CHECK_COLUMN(result, 1, {0}));
212 }
213 SECTION("505") {
214 // A RIGHT JOIN unexpectedly fetches rows
215 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT);"));
216 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1(c1 BOOLEAN);"));
217 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (1);"));
218 result = con.Query("SELECT * FROM t0 RIGHT JOIN t1 on true;");
219 REQUIRE(CHECK_COLUMN(result, 0, {}));
220 REQUIRE(CHECK_COLUMN(result, 1, {}));
221 }
222 SECTION("506") {
223 // Query results in an error "INTERNAL: Failed to bind column reference "c0" [5.0] (bindings: [6.0])"
224 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT);"));
225 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1(c0 INT);"));
226 REQUIRE_NO_FAIL(con.Query("SELECT * FROM t1 JOIN t0 ON t1.c0 < t1.c0 - t0.c0 WHERE t0.c0 <= t1.c0;"));
227 REQUIRE_NO_FAIL(con.Query("SELECT * FROM t1 JOIN t0 ON t0.c0 + t1.c0 < t1.c0 - t0.c0;"));
228 }
229 SECTION("507") {
230 // Creating an empty table results in a crash
231 REQUIRE_FAIL(con.Query("CREATE TABLE t0();"));
232 }
233 SECTION("508") {
234 // LEFT JOIN on column with NULL value results in a segmentation fault
235 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT);"));
236 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1(c0 INT);"));
237 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (0);"));
238 REQUIRE_NO_FAIL(con.Query("INSERT INTO t1(c0) VALUES (NULL);"));
239 result = con.Query("SELECT * FROM t1 LEFT JOIN t0 ON t0.c0=t1.c0;");
240 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
241 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
242 }
243 SECTION("510") {
244 // SIMILAR TO results in an incorrect result
245 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT);"));
246 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (-10);"));
247 result = con.Query("SELECT '-10' SIMILAR TO '0';");
248 REQUIRE(CHECK_COLUMN(result, 0, {false}));
249 result = con.Query("SELECT t0.c0 SIMILAR TO 0 FROM t0;");
250 REQUIRE(CHECK_COLUMN(result, 0, {false}));
251 result = con.Query("SELECT t0.c0 NOT SIMILAR TO 0 FROM t0;");
252 REQUIRE(CHECK_COLUMN(result, 0, {true}));
253 result = con.Query("SELECT * FROM t0 WHERE t0.c0 NOT SIMILAR TO 0;");
254 REQUIRE(CHECK_COLUMN(result, 0, {-10}));
255 }
256 SECTION("513") {
257 // LEFT JOIN with comparison on integer columns results in "Not implemented: Unimplemented type for nested loop
258 // join!"
259 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT);"));
260 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1(c0 INT);"));
261 REQUIRE_NO_FAIL(con.Query("INSERT INTO t1(c0) VALUES (0);"));
262 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (0);"));
263 result = con.Query("SELECT * FROM t0 LEFT JOIN t1 ON t0.c0 <= t1.c0;");
264 REQUIRE(CHECK_COLUMN(result, 0, {0}));
265 REQUIRE(CHECK_COLUMN(result, 1, {0}));
266 }
267 SECTION("514") {
268 // Incorrect result after an INSERT violates a UNIQUE constraint
269 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT);"));
270 REQUIRE_NO_FAIL(con.Query("CREATE UNIQUE INDEX i0 ON t0(c0);"));
271 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (1);"));
272 result = con.Query("SELECT * FROM t0 WHERE t0.c0 = 1;");
273 REQUIRE(CHECK_COLUMN(result, 0, {1}));
274 REQUIRE_FAIL(con.Query("INSERT INTO t0(c0) VALUES (1);"));
275 result = con.Query("SELECT * FROM t0 WHERE t0.c0 = 1;");
276 REQUIRE(CHECK_COLUMN(result, 0, {1}));
277
278 // verify correct behavior here too when we have multiple nodes
279 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (2);"));
280 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (3);"));
281 REQUIRE_FAIL(con.Query("INSERT INTO t0(c0) VALUES (2);"));
282 result = con.Query("SELECT * FROM t0 WHERE t0.c0 = 2;");
283 REQUIRE(CHECK_COLUMN(result, 0, {2}));
284 }
285 SECTION("515") {
286 // Query with a negative shift predicate yields an incorrect result
287 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT);"));
288 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1(c0 INT8, c1 DOUBLE);"));
289 REQUIRE_NO_FAIL(con.Query("INSERT INTO t1(c0) VALUES (0);"));
290 REQUIRE_NO_FAIL(con.Query("INSERT INTO t1(c1, c0) VALUES (1, 1);"));
291 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0 VALUES (0);"));
292 result = con.Query("SELECT * FROM t1 JOIN t0 ON t1.c1 WHERE NOT (t1.c0<<-1);");
293 REQUIRE(CHECK_COLUMN(result, 0, {1}));
294 REQUIRE(CHECK_COLUMN(result, 1, {1}));
295 REQUIRE(CHECK_COLUMN(result, 2, {0}));
296 result = con.Query("SELECT * FROM t1 JOIN t0 ON t1.c1 WHERE (t1.c0<<-1);");
297 REQUIRE(CHECK_COLUMN(result, 0, {}));
298 REQUIRE(CHECK_COLUMN(result, 1, {}));
299 REQUIRE(CHECK_COLUMN(result, 2, {}));
300 result = con.Query("SELECT NOT (t1.c0<<-1) FROM t1;");
301 REQUIRE(CHECK_COLUMN(result, 0, {true, true}));
302 }
303 SECTION("516") {
304 // Query with comparison on boolean column results in "Invalid type: Invalid Type [BOOL]: Invalid type for
305 // index"
306 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 BOOL UNIQUE);"));
307 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (0);"));
308 result = con.Query("SELECT * FROM t0 WHERE t0.c0 = true;");
309 REQUIRE(CHECK_COLUMN(result, 0, {}));
310 }
311 SECTION("517") {
312 // Query with an AND predicate, NOT and comparison yields an incorrect result
313 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT);"));
314 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1(c0 INT);"));
315 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (0);"));
316 REQUIRE_NO_FAIL(con.Query("INSERT INTO t1(c0) VALUES (0);"));
317 result = con.Query("SELECT * FROM t1, t0 WHERE NOT ((t1.c0 AND t0.c0) < 0);");
318 REQUIRE(CHECK_COLUMN(result, 0, {0}));
319 REQUIRE(CHECK_COLUMN(result, 0, {0}));
320 result = con.Query("SELECT * FROM t1, t0 WHERE ((t1.c0 AND t0.c0) < 0);");
321 REQUIRE(CHECK_COLUMN(result, 0, {}));
322 REQUIRE(CHECK_COLUMN(result, 0, {}));
323 }
324 SECTION("518") {
325 // Query using the LN() function does not terminate
326 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT);"));
327 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1(c0 INT);"));
328 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (0);"));
329 REQUIRE_NO_FAIL(con.Query("INSERT INTO t1(c0) VALUES (0), (0), (1), (-1);"));
330 result = con.Query("SELECT LN(t1.c0) FROM t0, t1 WHERE LN(t1.c0) < t0.c0;");
331 REQUIRE(CHECK_COLUMN(result, 0, {}));
332 result = con.Query("SELECT t1.c0, LN(t1.c0) FROM t1 ORDER BY t1.c0;");
333 REQUIRE(CHECK_COLUMN(result, 0, {-1, 0, 0, 1}));
334 REQUIRE(CHECK_COLUMN(result, 1, {Value(), Value(), Value(), 0}));
335 }
336 SECTION("521") {
337 // ROUND() evaluates to -nan
338 result = con.Query("SELECT ROUND(0.1, 1000);");
339 REQUIRE(CHECK_COLUMN(result, 0, {0.1}));
340 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT);"));
341 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (0);"));
342 result = con.Query("SELECT * FROM t0 WHERE t0.c0 > ROUND(0.1, 1000);");
343 REQUIRE(CHECK_COLUMN(result, 0, {}));
344 result = con.Query("SELECT * FROM t0 WHERE t0.c0 <= ROUND(0.1, 1000);");
345 REQUIRE(CHECK_COLUMN(result, 0, {0}));
346 }
347 SECTION("522") {
348 // Casting a large number to REAL and multiplying it with zero results in -nan
349 REQUIRE_FAIL(con.Query("SELECT 1e100::real*0;"));
350 }
351 SECTION("523") {
352 // The trigonometric functions can result in -nan
353 REQUIRE_FAIL(con.Query("SELECT SIN(1e1000);"));
354 }
355 SECTION("525") {
356 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT);"));
357 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1(c0 FLOAT);"));
358 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (1), (0);"));
359 REQUIRE_NO_FAIL(con.Query("INSERT INTO t1(c0) VALUES (1);"));
360 result = con.Query("SELECT t1.c0 FROM t1 JOIN t0 ON t1.c0 IN (t0.c0) WHERE t1.c0<=t0.c0;");
361 REQUIRE(CHECK_COLUMN(result, 0, {1.0}));
362 }
363 SECTION("526") {
364 // Query that uses the CONCAT() function and OR expression crashes
365 // FIXME: #398
366 con.DisableQueryVerification();
367 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 REAL);"));
368 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1(c0 INT2);"));
369 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t2(c0 INT);"));
370 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0 VALUES (-1);"));
371 REQUIRE_NO_FAIL(con.Query("INSERT INTO t1 VALUES (0);"));
372 REQUIRE_NO_FAIL(con.Query("INSERT INTO t2 VALUES (0), (0);"));
373 result = con.Query("SELECT * FROM t1, t2, t0 WHERE CONCAT(t1.c0) OR t0.c0;");
374 REQUIRE(CHECK_COLUMN(result, 0, {0, 0}));
375 REQUIRE(CHECK_COLUMN(result, 1, {0, 0}));
376 REQUIRE(CHECK_COLUMN(result, 2, {-1.0, -1.0}));
377 }
378 SECTION("527") {
379 // Query with JOIN and WHERE condition unexpectedly fetches a value not present in the table
380 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT);"));
381 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1(c0 INT);"));
382 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0 VALUES (0);"));
383 REQUIRE_NO_FAIL(con.Query("INSERT INTO t1 VALUES (1), (1);"));
384 result = con.Query("SELECT t0.c0 FROM t0 JOIN t1 ON t0.c0=(t1.c0 IS NULL) WHERE t0.c0 NOT IN (t1.c0);");
385 REQUIRE(CHECK_COLUMN(result, 0, {0, 0}));
386 result = con.Query("SELECT t0.c0 FROM t0 JOIN t1 ON t0.c0=(t1.c0 IS NULL);");
387 REQUIRE(CHECK_COLUMN(result, 0, {0, 0}));
388 }
389 SECTION("528") {
390 // Incorrect result for query that uses REGEXP_MATCHES()
391 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 VARCHAR);"));
392 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (0.1);"));
393 result = con.Query("SELECT * FROM t0 WHERE REGEXP_MATCHES(t0.c0, '1');");
394 REQUIRE(CHECK_COLUMN(result, 0, {"0.1"}));
395 result = con.Query("SELECT * FROM t0 WHERE NOT REGEXP_MATCHES(t0.c0, '1');");
396 REQUIRE(CHECK_COLUMN(result, 0, {}));
397 result = con.Query("SELECT REGEXP_MATCHES(t0.c0, '1') FROM t0;");
398 REQUIRE(CHECK_COLUMN(result, 0, {true}));
399
400 result = con.Query("SELECT * FROM t0 WHERE REGEXP_FULL_MATCH(t0.c0, '1');");
401 REQUIRE(CHECK_COLUMN(result, 0, {}));
402 result = con.Query("SELECT * FROM t0 WHERE NOT REGEXP_FULL_MATCH(t0.c0, '1');");
403 REQUIRE(CHECK_COLUMN(result, 0, {"0.1"}));
404 result = con.Query("SELECT REGEXP_FULL_MATCH(t0.c0, '1') FROM t0;");
405 REQUIRE(CHECK_COLUMN(result, 0, {false}));
406 }
407 SECTION("531") {
408 // SELECT on DATE column with a large negative value results in a "double free or corruption"
409 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 DATE);"));
410 REQUIRE_FAIL(con.Query("INSERT INTO t0 VALUES (-10000000);"));
411 REQUIRE_FAIL(con.Query("SELECT (-10000000)::DATE;"));
412 }
413 SECTION("533") {
414 // Overflow when casting from REAL to INT results in "Invalid TypeId -1"
415 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 REAL);"));
416 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (10000000000);"));
417 REQUIRE_FAIL(con.Query("SELECT t0.c0 ::INT FROM t0;"));
418 }
419 SECTION("534") {
420 // UPDATE causes subsequent query to segfault
421 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 VARCHAR, c1 DOUBLE UNIQUE);"));
422 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (NULL);"));
423 REQUIRE_NO_FAIL(con.Query("UPDATE t0 SET c0=0;"));
424 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0, c1) VALUES (0, 0);"));
425 result = con.Query("SELECT * FROM t0 WHERE 1 > c1;");
426 REQUIRE(CHECK_COLUMN(result, 0, {"0"}));
427 REQUIRE(CHECK_COLUMN(result, 1, {0}));
428 }
429 SECTION("535") {
430 // GROUP BY clause results in non-deterministic result
431 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 NUMERIC);"));
432 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (-515965088);"));
433 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (1), (-5.15965088E8);"));
434 REQUIRE_NO_FAIL(con.Query("CREATE INDEX i0 ON t0(c0);"));
435 result = con.Query("SELECT t0.c0 FROM t0 GROUP BY t0.c0, REVERSE(t0.c0) ORDER BY 1;");
436 REQUIRE(CHECK_COLUMN(result, 0, {-515965088, 1}));
437 }
438 SECTION("536") {
439 // Nondeterministic clause when using an UNION query and HAVING clause
440 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT);"));
441 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1(c0 VARCHAR);"));
442 REQUIRE_NO_FAIL(con.Query("INSERT INTO t1 VALUES (0.9201898334673894), (0);"));
443 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0 VALUES (0);"));
444 result = con.Query("SELECT * FROM t0, t1 GROUP BY t0.c0, t1.c0 HAVING t1.c0!=MAX(t1.c0);");
445 REQUIRE(CHECK_COLUMN(result, 0, {}));
446 result = con.Query("SELECT * FROM t0, t1 GROUP BY t0.c0, t1.c0 HAVING t1.c0!=MAX(t1.c0) UNION ALL SELECT * "
447 "FROM t0, t1 GROUP BY t0.c0, t1.c0 HAVING NOT t1.c0>MAX(t1.c0) ORDER BY 1, 2;");
448 REQUIRE(CHECK_COLUMN(result, 0, {0, 0}));
449 REQUIRE(CHECK_COLUMN(result, 1, {"0", "0.9201898334673894"}));
450 }
451 SECTION("537") {
452 // Fetching from table and view results in a crash
453 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT);"));
454 REQUIRE_NO_FAIL(con.Query("CREATE VIEW v0 AS SELECT 0, 1 FROM t0 ORDER BY t0.c0;"));
455 result = con.Query("SELECT t0.c0 FROM t0, v0;");
456 REQUIRE(CHECK_COLUMN(result, 0, {}));
457 }
458 SECTION("538") {
459 // Incorrect result for predicate with shift on a BIGINT column
460 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 BIGINT);"));
461 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (-1);"));
462 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (0);"));
463 result = con.Query("SELECT t0.c0 AND (t0.c0<<64) FROM t0;");
464 REQUIRE(CHECK_COLUMN(result, 0, {false, false}));
465
466 result = con.Query("SELECT * FROM t0 WHERE t0.c0 AND (t0.c0<<64);");
467 REQUIRE(CHECK_COLUMN(result, 0, {}));
468 }
469 SECTION("540") {
470 // Nested MAX() results in nondeterministic result or double free
471 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 VARCHAR);"));
472 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0 VALUES ('aaaaaaaaaaaa');"));
473 result = con.Query("SELECT MAX(agg0) FROM (SELECT MAX(t0.c0) AS agg0 FROM t0) as s0;");
474 REQUIRE(CHECK_COLUMN(result, 0, {"aaaaaaaaaaaa"}));
475 }
476 SECTION("543") {
477 // Unexpected result for SUM() upon overflow
478 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c1 BIGINT);"));
479 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c1) VALUES (2);"));
480 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c1) VALUES (9223372036854775807);"));
481 result = con.Query("SELECT SUM(t0.c1) FROM t0;");
482 REQUIRE(CHECK_COLUMN(result, 0, {9223372036854775809.0}));
483
484 REQUIRE_NO_FAIL(con.Query("DROP TABLE t0;"));
485
486 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c1 BIGINT);"));
487 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c1) VALUES (1);"));
488 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c1) VALUES (9223372036854775807);"));
489 result = con.Query("SELECT SUM(t0.c1) FROM t0;");
490 REQUIRE(CHECK_COLUMN(result, 0, {9223372036854775808.0}));
491 }
492 SECTION("544") {
493 // SELECT on view with text constant in ORDER BY crashes
494 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT);"));
495 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (0);"));
496 REQUIRE_NO_FAIL(con.Query("CREATE VIEW v0(c0) AS SELECT 1 FROM t0;"));
497 result = con.Query("SELECT * FROM v0 ORDER BY 'a';");
498 REQUIRE(CHECK_COLUMN(result, 0, {1}));
499 }
500 SECTION("547") {
501 // Query with SIMILAR TO results in "Assertion `strlen(dataptr) == length' failed"
502 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT);"));
503 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0 VALUES (0);"));
504 result = con.Query("SELECT * FROM t0 WHERE t0.c0 SIMILAR TO '.';");
505 REQUIRE(CHECK_COLUMN(result, 0, {"0"}));
506 }
507 SECTION("549") {
508 // Nested CASE expression results in Assertion `other.auxiliary->type == VectorBufferType::STRING_BUFFER' failed
509 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT);"));
510 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0 VALUES (NULL), (0), (1); "));
511 REQUIRE_FAIL(con.Query("SELECT * FROM t0 WHERE CASE WHEN c0 THEN 0 ELSE CASE '0.1' WHEN c0 THEN '' END END;"));
512 result = con.Query("SELECT CASE WHEN c0 THEN 0 ELSE (CASE '0' WHEN c0 THEN '0.6' END) END FROM t0;");
513 REQUIRE(CHECK_COLUMN(result, 0, {Value(), "0.6", "0"}));
514 }
515 SECTION("552") {
516 // RIGHT JOIN results in Assertion `filter->expressions.size() == 1'
517 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT);"));
518 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1(c0 INT);"));
519 result = con.Query("SELECT * FROM t0 RIGHT JOIN t1 ON 0 WHERE t0.c0 OR t1.c0 BETWEEN t0.c0 AND 1;");
520 REQUIRE(CHECK_COLUMN(result, 0, {}));
521 }
522 SECTION("560") {
523 // Incorrect result for SUM() and negative number
524 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0 (c0 INT);"));
525 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0 VALUES (0);"));
526 result = con.Query("SELECT SUM(-1) FROM t0;");
527 REQUIRE(CHECK_COLUMN(result, 0, {-1}));
528 }
529 SECTION("562") {
530 // SELECT with CASE expression causes an assertion failure "Assertion `!entry.first->Equals(&expr)' failed"
531 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT);"));
532 REQUIRE_FAIL(
533 con.Query("SELECT * FROM t0 GROUP BY -4.40304405E8 ORDER BY (CASE 1 WHEN 0 THEN 0 ELSE -440304405 END);"));
534 result =
535 con.Query("SELECT 1 FROM t0 GROUP BY -4.40304405E8 ORDER BY (CASE 1 WHEN 0 THEN 0 ELSE -440304405 END);");
536 REQUIRE(CHECK_COLUMN(result, 0, {}));
537 }
538 SECTION("567") {
539 // REVERSE() on special character results in "Assertion `strcmp(dataptr, normalized) == 0' failed."
540 result = con.Query("SELECT REVERSE('S̈a︍');");
541 REQUIRE(CHECK_COLUMN(result, 0, {"a︍S̈"}));
542 }
543 SECTION("571") {
544 // Incorrect result for BETWEEN query that casts column to boolean #571
545 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT);"));
546 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0 VALUES (-1);"));
547 result = con.Query("SELECT t0.c0 FROM t0 WHERE NOT (0 BETWEEN 0 AND t0.c0::BOOL);");
548 REQUIRE(CHECK_COLUMN(result, 0, {}));
549 }
550 SECTION("579") {
551 // Updated value in column is not visible in a SELECT
552 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 VARCHAR, c1 VARCHAR);"));
553 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES(0), ('');"));
554 REQUIRE_NO_FAIL(con.Query("UPDATE t0 SET c1 = 1;"));
555 // -- expected: {1}, actual: {''}
556 result = con.Query("SELECT t0.c1 FROM t0 WHERE '' = t0.c0;");
557 REQUIRE(CHECK_COLUMN(result, 0, {"1"}));
558 }
559 SECTION("580") {
560 // SUBSTRING with an invalid start position causes a segmentation fault #580
561 result = con.Query("SELECT SUBSTRING(0, 3, 0)");
562 REQUIRE(CHECK_COLUMN(result, 0, {""}));
563 }
564 SECTION("581") {
565 // DISTINCT malfunctions for BOOLEAN
566 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 BOOLEAN);"));
567 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0 VALUES (NULL), (false);"));
568 result = con.Query("SELECT DISTINCT t0.c0 FROM t0 ORDER BY 1;");
569 REQUIRE(CHECK_COLUMN(result, 0, {Value(), false}));
570 }
571 SECTION("583") {
572 // Comparing a string with a boolean yields an incorrect result after UPDATE
573 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 VARCHAR);"));
574 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (0);"));
575 REQUIRE_NO_FAIL(con.Query("UPDATE t0 SET c0=0;"));
576 REQUIRE_NO_FAIL(con.Query("UPDATE t0 SET c0=true;"));
577 // -- expected: {true}, actual: {}
578 result = con.Query("SELECT * FROM t0 WHERE t0.c0 = true;");
579 REQUIRE(CHECK_COLUMN(result, 0, {"true"}));
580 }
581 SECTION("584") {
582 // A select with BETWEEN and VARCHAR cast results in an incorrect result
583 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INTEGER);"));
584 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (-2);"));
585 result = con.Query("SELECT t0.c0 FROM t0 WHERE -1 BETWEEN t0.c0::VARCHAR AND 1;");
586 REQUIRE(CHECK_COLUMN(result, 0, {-2}));
587 }
588 SECTION("585") {
589 // Predicate checking for an empty string yields an incorrect result
590 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 VARCHAR);"));
591 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (''), (0)"));
592 result = con.Query("SELECT * FROM t0 WHERE t0.c0 = ''; ");
593 // -- expected: {''}, actual: {}
594 REQUIRE(CHECK_COLUMN(result, 0, {""}));
595 }
596 SECTION("586") {
597 // NOACCENT.NOCASE comparison with a special character results in a segmentation fault
598 result = con.Query("SELECT ''='Ʇ';");
599 REQUIRE(CHECK_COLUMN(result, 0, {false}));
600 result = con.Query("SELECT '' COLLATE NOACCENT.NOCASE='Ʇ';");
601 REQUIRE(CHECK_COLUMN(result, 0, {false}));
602 }
603 SECTION("587") {
604 // A negative DATE results in a "double free or corruption" crash
605 result = con.Query("SELECT (DATE '-10000-01-01')::VARCHAR;");
606 REQUIRE(CHECK_COLUMN(result, 0, {"10000-01-01 (BC)"}));
607 }
608 SECTION("588") {
609 // Query with complex ORDER BY causes an incorrect rowid value
610 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT);"));
611 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0 VALUES (1), (0), (1);"));
612 result = con.Query(
613 "SELECT t0.rowid FROM t0 WHERE t0.rowid ORDER BY CASE ((t0.c0) ::BOOL) WHEN 1 THEN t0.rowid END;");
614 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
615 }
616 SECTION("589") {
617 // Creating an index on rowid results in an internal error "Failed to bind column reference"
618 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT);"));
619 REQUIRE_FAIL(con.Query("CREATE INDEX i0 ON t0(rowid, c0);"));
620 }
621 SECTION("590") {
622 // Comparison with a DATE yields an incorrect result
623 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 VARCHAR);"));
624 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (DATE '2000-01-02');"));
625 result = con.Query("SELECT * FROM t0 WHERE DATE '2000-01-01' < t0.c0;");
626 REQUIRE(CHECK_COLUMN(result, 0, {"2000-01-02"}));
627 }
628 SECTION("591") {
629 // Subtracting a large integer from a DATE results in a "double free or corruption"
630 result = con.Query("SELECT (- 41756167 + '1969-12-11 032657' ::DATE)::VARCHAR;");
631 REQUIRE(CHECK_COLUMN(result, 0, {"112356-06-10 (BC)"}));
632 }
633 SECTION("592") {
634 // Expression with LIKE and comparison causes an assertion failure
635 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 VARCHAR);"));
636 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0 VALUES (0);"));
637 result = con.Query("SELECT * FROM t0 WHERE c0 LIKE '' AND c0 < true;");
638 REQUIRE(CHECK_COLUMN(result, 0, {}));
639 }
640 SECTION("596") {
641 // STDDEV_POP unexpectedly does not fetch any rows
642 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 DOUBLE);"));
643 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES(1E200), (0);"));
644 REQUIRE_FAIL(con.Query("SELECT STDDEV_POP(c0) FROM t0;"));
645 }
646 SECTION("599") {
647 // UPDATE results in crash or ASan error
648 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT, c1 VARCHAR);"));
649 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0 VALUES (0, 0), (NULL, 0);"));
650 REQUIRE_NO_FAIL(con.Query("UPDATE t0 SET c1 = c0;"));
651 result = con.Query("SELECT * FROM t0 ORDER BY 1");
652 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 0}));
653 REQUIRE(CHECK_COLUMN(result, 1, {Value(), "0"}));
654 }
655 SECTION("602") {
656 // GROUP BY does not take COLLATE into account
657 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 VARCHAR COLLATE NOCASE);"));
658 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES ('a'), ('A');"));
659 result = con.Query("SELECT t0.c0 FROM t0 GROUP BY t0.c0;");
660 REQUIRE(CHECK_COLUMN(result, 0, {"a"}));
661 }
662 SECTION("603") {
663 // BETWEEN with COLLATE NOACCENT.NOCASE expression results in a segfault/ASan failure
664 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 DATE, c1 VARCHAR);"));
665 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (NULL), ('2000-01-01');"));
666 result = con.Query("SELECT * FROM t0 WHERE 'a' BETWEEN c0 AND c1 COLLATE NOACCENT.NOCASE;");
667 REQUIRE(CHECK_COLUMN(result, 0, {}));
668 }
669 SECTION("609") {
670 // Incorrect result for MIN() on expression involving rowid
671 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT, c1 INT);"));
672 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), "
673 "(0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (NULL), (NULL);"));
674 REQUIRE_NO_FAIL(con.Query("CREATE INDEX b ON t0(c1);"));
675 REQUIRE_NO_FAIL(con.Query("UPDATE t0 SET c1 = NULL;"));
676 result = con.Query("SELECT MIN(100000000000000000<<t0.rowid) FROM t0;");
677 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(-9223372036854775807LL - 1)}));
678 result = con.Query("SELECT MIN(100000000000000000<<t0.rowid) FROM t0 WHERE NOT c0;");
679 REQUIRE(CHECK_COLUMN(result, 0, {Value::BIGINT(-8802109549835190272LL)}));
680 }
681 SECTION("618") {
682 // Failed ALTER COLUMN results in a "Transaction conflict" error that cannot be aborted
683 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 DATE);"));
684 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0 VALUES (DATE '2000-01-01');"));
685 REQUIRE_FAIL(con.Query("ALTER TABLE t0 ALTER COLUMN c0 SET DATA TYPE INT;"));
686 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0 VALUES (DEFAULT);"));
687 }
688 SECTION("619") {
689 // Query on altered table results in a segmentation fault
690 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT UNIQUE, c1 DATE);"));
691 REQUIRE_NO_FAIL(con.Query("ALTER TABLE t0 ALTER c1 TYPE INT;"));
692 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES(-1);"));
693
694 result = con.Query("SELECT * FROM t0 WHERE c0 < 0;");
695 REQUIRE(CHECK_COLUMN(result, 0, {-1}));
696 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
697 }
698 SECTION("622") {
699 // UPDATE on altered table results in an error "Could not find node in column segment tree"
700 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 TIMESTAMP);"));
701 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0 VALUES(NULL);"));
702 REQUIRE_NO_FAIL(con.Query("DELETE FROM t0;"));
703 REQUIRE_NO_FAIL(con.Query("ALTER TABLE t0 ALTER c0 TYPE DATE;"));
704 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0 VALUES(NULL);"));
705 REQUIRE_NO_FAIL(con.Query("UPDATE t0 SET c0 = '1969-12-18'; "));
706 }
707 SECTION("624") {
708 // ALTER TABLE results in an assertion failure "Assertion `expr.return_type == vector.type' failed"
709 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT, c1 VARCHAR);"));
710 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c1) VALUES(NULL);"));
711 REQUIRE_NO_FAIL(con.Query("ALTER TABLE t0 ALTER c1 TYPE TIMESTAMP;"));
712 }
713 SECTION("625") {
714 // DROP column results in an assertion failure unique.index < base.columns.size()
715 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT, c INT UNIQUE);"));
716 // we don't support this case yet
717 REQUIRE_FAIL(con.Query("ALTER TABLE t0 DROP c0;"));
718 // check that unique constraint still works
719 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0 (c) VALUES (1);"));
720 REQUIRE_FAIL(con.Query("INSERT INTO t0 (c) VALUES (1);"));
721 }
722 SECTION("628") {
723 // DROP column results in an assertion failure unique.index < base.columns.size()
724 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT, c1 INT UNIQUE);"));
725 REQUIRE_FAIL(con.Query("ALTER TABLE t0 DROP c1;"));
726 }
727 SECTION("629") {
728 // ALTER TYPE with USING results in an assertion failure "types.size() > 0"
729 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT);"));
730 REQUIRE_NO_FAIL(con.Query("ALTER TABLE t0 ALTER c0 TYPE VARCHAR USING ''; "));
731 }
732 SECTION("629") {
733 // ALTER TYPE with USING results in an assertion failure "types.size() > 0"
734 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 INT);"));
735 REQUIRE_NO_FAIL(con.Query("ALTER TABLE t0 ALTER c0 TYPE VARCHAR USING ''; "));
736 }
737 SECTION("633") {
738 // Query using LEFT() results in a segmentation fault
739 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t0(c0 BOOL);"));
740 REQUIRE_NO_FAIL(con.Query("INSERT INTO t0(c0) VALUES (NULL);"));
741 result = con.Query("SELECT LEFT(t0.c0, -1) FROM t0;");
742 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
743 result = con.Query("SELECT RIGHT(t0.c0, -1) FROM t0;");
744 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
745 }
746}
747