1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test value list in selection", "[valuelist]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11 con.EnableQueryVerification();
12
13 // value list can be a top-level statement
14 result = con.Query("(VALUES (1, 3), (2, 4));");
15 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
16 REQUIRE(CHECK_COLUMN(result, 1, {3, 4}));
17
18 // nulls first and then integers
19 result = con.Query("SELECT * FROM (VALUES (NULL, NULL), (3, 4), (3, 7)) v1;");
20 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 3, 3}));
21 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 4, 7}));
22
23 // standard value list
24 result = con.Query("SELECT * FROM (VALUES (1, 2, 3), (1, 2, 3)) v1;");
25 REQUIRE(CHECK_COLUMN(result, 0, {1, 1}));
26 REQUIRE(CHECK_COLUMN(result, 1, {2, 2}));
27 REQUIRE(CHECK_COLUMN(result, 2, {3, 3}));
28
29 // value list with expressions
30 result = con.Query("SELECT * FROM (VALUES (1 + 1, 2, 3), (1 + 3, 2, 3)) v1;");
31 REQUIRE(CHECK_COLUMN(result, 0, {2, 4}));
32 REQUIRE(CHECK_COLUMN(result, 1, {2, 2}));
33 REQUIRE(CHECK_COLUMN(result, 2, {3, 3}));
34
35 // value list with subqueries
36 result = con.Query("SELECT * FROM (VALUES ((SELECT 42), 2, 3), (1 + 3,2,3)) v1;");
37 REQUIRE(CHECK_COLUMN(result, 0, {42, 4}));
38 REQUIRE(CHECK_COLUMN(result, 1, {2, 2}));
39 REQUIRE(CHECK_COLUMN(result, 2, {3, 3}));
40
41 // value list in insert
42 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER);"));
43 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (1, 2), (3, 4);"));
44
45 // value list with more complicated subqueries
46 result =
47 con.Query("SELECT * FROM (VALUES ((SELECT MIN(a) FROM test), 2, 3), ((SELECT MAX(b) FROM test), 2, 3)) v1;");
48 REQUIRE(CHECK_COLUMN(result, 0, {1, 4}));
49 REQUIRE(CHECK_COLUMN(result, 1, {2, 2}));
50 REQUIRE(CHECK_COLUMN(result, 2, {3, 3}));
51
52 // value list with different types
53 result = con.Query("SELECT * FROM (VALUES ('hello', 2), (1 + 3, '5'), (DATE '1992-09-20', 3)) v1;");
54 REQUIRE(CHECK_COLUMN(result, 0, {"hello", "4", "1992-09-20"}));
55 REQUIRE(CHECK_COLUMN(result, 1, {2, 5, 3}));
56
57 // value list with NULLs
58 result = con.Query("SELECT * FROM (VALUES (DATE '1992-09-20', 3), (NULL, NULL)) v1;");
59 REQUIRE(CHECK_COLUMN(result, 0, {Value::DATE(1992, 9, 20), Value()}));
60 REQUIRE(CHECK_COLUMN(result, 1, {3, Value()}));
61
62 // only NULLs
63 result = con.Query("SELECT * FROM (VALUES (NULL, NULL)) v1;");
64 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
65 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
66
67 // nulls first and then integers
68 result = con.Query("SELECT * FROM (VALUES (NULL, NULL), (3, 4)) v1;");
69 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 3}));
70 REQUIRE(CHECK_COLUMN(result, 1, {Value(), 4}));
71
72 // this does not work: type is chosen by first value
73 REQUIRE_FAIL(con.Query("SELECT * FROM (VALUES (3), ('hello')) v1;"));
74 // this also doesn't work: NULL defaults to integer type
75 REQUIRE_FAIL(con.Query("SELECT * FROM (VALUES (NULL), ('hello')) v1;"));
76 // unbalanced value list is not allowed
77 REQUIRE_FAIL(con.Query("SELECT * FROM (VALUES (1, 2, 3), (1,2)) v1;"));
78 // default in value list is not allowed
79 REQUIRE_FAIL(con.Query("SELECT * FROM (VALUES (DEFAULT, 2, 3), (1,2)) v1;"));
80
81 // VALUES list for INSERT
82 REQUIRE_NO_FAIL(con.Query("CREATE TABLE varchars(v VARCHAR);"));
83 REQUIRE_NO_FAIL(con.Query("INSERT INTO varchars VALUES (1), ('hello'), (DEFAULT);"));
84
85 result = con.Query("SELECT * FROM varchars ORDER BY 1");
86 REQUIRE(CHECK_COLUMN(result, 0, {Value(), "1", "hello"}));
87
88 // too many columns provided
89 REQUIRE_FAIL(con.Query("INSERT INTO varchars VALUES (1, 2), ('hello', 3), (DEFAULT, DEFAULT);"));
90 REQUIRE_FAIL(con.Query("INSERT INTO varchars (v) VALUES (1, 2), ('hello', 3), (DEFAULT, DEFAULT);"));
91 REQUIRE_FAIL(con.Query("INSERT INTO varchars (v) VALUES (1, 2), ('hello'), (DEFAULT, DEFAULT);"));
92 // operation on default not allowed
93 REQUIRE_FAIL(con.Query("INSERT INTO varchars (v) VALUES (DEFAULT IS NULL);"));
94}
95