1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test simple projection statements", "[simpleprojection]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11 con.EnableQueryVerification();
12
13 // create table
14 result = con.Query("CREATE TABLE a (i integer, j integer);");
15
16 // insertion: 1 affected row
17 result = con.Query("INSERT INTO a VALUES (42, 84);");
18 REQUIRE(CHECK_COLUMN(result, 0, {1}));
19
20 result = con.Query("SELECT * FROM a;");
21 REQUIRE(CHECK_COLUMN(result, 0, {42}));
22 REQUIRE(CHECK_COLUMN(result, 1, {84}));
23
24 // multiple insertions
25 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER);"));
26 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 22), (12, 21), (13, 22)"));
27
28 // multiple projections
29 result = con.Query("SELECT a, b FROM test;");
30 REQUIRE(CHECK_COLUMN(result, 0, {11, 12, 13}));
31 REQUIRE(CHECK_COLUMN(result, 1, {22, 21, 22}));
32
33 // basic expressions and filters
34 result = con.Query("SELECT a + 2, b FROM test WHERE a = 11;");
35 REQUIRE(CHECK_COLUMN(result, 0, {13}));
36 REQUIRE(CHECK_COLUMN(result, 1, {22}));
37
38 result = con.Query("SELECT a + 2, b FROM test WHERE a = 12;");
39 REQUIRE(CHECK_COLUMN(result, 0, {14}));
40 REQUIRE(CHECK_COLUMN(result, 1, {21}));
41
42 // casts
43 result = con.Query("SELECT cast(a AS VARCHAR) FROM test;");
44 REQUIRE(CHECK_COLUMN(result, 0, {"11", "12", "13"}));
45
46 result = con.Query("SELECT cast(cast(a AS VARCHAR) as INTEGER) FROM test;");
47 REQUIRE(CHECK_COLUMN(result, 0, {11, 12, 13}));
48}
49
50TEST_CASE("Test table star expressions", "[simpleprojection]") {
51 unique_ptr<QueryResult> result;
52 DuckDB db(nullptr);
53 Connection con(db);
54 con.EnableQueryVerification();
55
56 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (a INTEGER, b INTEGER)"));
57 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (11, 22), (12, 21), (13, 22)"));
58
59 result = con.Query("SELECT * FROM test");
60 REQUIRE(CHECK_COLUMN(result, 0, {11, 12, 13}));
61 REQUIRE(CHECK_COLUMN(result, 1, {22, 21, 22}));
62
63 result = con.Query("SELECT test.* FROM test");
64 REQUIRE(CHECK_COLUMN(result, 0, {11, 12, 13}));
65 REQUIRE(CHECK_COLUMN(result, 1, {22, 21, 22}));
66
67 result = con.Query("SELECT t.* FROM test t");
68 REQUIRE(CHECK_COLUMN(result, 0, {11, 12, 13}));
69 REQUIRE(CHECK_COLUMN(result, 1, {22, 21, 22}));
70
71 REQUIRE_FAIL(con.Query("SELECT test.* FROM test t"));
72 REQUIRE_FAIL(con.Query("SELECT xyz.* FROM test"));
73 REQUIRE_FAIL(con.Query("SELECT xyz.*"));
74
75 // issue 415
76 REQUIRE_NO_FAIL(con.Query("create table r4 (i int, j int)"));
77 REQUIRE_NO_FAIL(con.Query("insert into r4 (i, j) values (1,1), (1,2), (1,3), (1,4), (1,5)"));
78
79 result = con.Query("select t1.i, t1.j as a, t2.j as b from r4 t1 inner join r4 t2 using(i,j) ORDER BY a");
80 REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 1, 1, 1}));
81 REQUIRE(CHECK_COLUMN(result, 1, {1, 2, 3, 4, 5}));
82 REQUIRE(CHECK_COLUMN(result, 2, {1, 2, 3, 4, 5}));
83
84 result = con.Query(
85 "select t1.i, t1.j as a, t2.j as b from r4 t1 inner join r4 t2 on t1.i=t2.i and t1.j=t2.j ORDER BY a");
86 REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 1, 1, 1}));
87 REQUIRE(CHECK_COLUMN(result, 1, {1, 2, 3, 4, 5}));
88 REQUIRE(CHECK_COLUMN(result, 2, {1, 2, 3, 4, 5}));
89
90 result = con.Query("select t1.*, t2.j b from r4 t1 inner join r4 t2 using(i,j) ORDER BY t1.j");
91 REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 1, 1, 1}));
92 REQUIRE(CHECK_COLUMN(result, 1, {1, 2, 3, 4, 5}));
93 REQUIRE(CHECK_COLUMN(result, 2, {1, 2, 3, 4, 5}));
94
95 result = con.Query("select t1.*, t2.j b from r4 t1 inner join r4 t2 on t1.i=t2.i and t1.j=t2.j ORDER BY t1.j");
96 REQUIRE(CHECK_COLUMN(result, 0, {1, 1, 1, 1, 1}));
97 REQUIRE(CHECK_COLUMN(result, 1, {1, 2, 3, 4, 5}));
98 REQUIRE(CHECK_COLUMN(result, 2, {1, 2, 3, 4, 5}));
99}
100