1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test view creation", "[views]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11 con.EnableQueryVerification();
12
13 // create a table
14 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1(i INTEGER)"));
15 REQUIRE_NO_FAIL(con.Query("INSERT INTO t1 VALUES (41), (42), (43)"));
16 REQUIRE_NO_FAIL(con.Query("CREATE VIEW v1 AS SELECT i AS j FROM t1 WHERE i < 43"));
17 REQUIRE_FAIL(con.Query("CREATE VIEW v1 AS SELECT 'whatever'"));
18
19 result = con.Query("SELECT j FROM v1 WHERE j > 41");
20 REQUIRE(CHECK_COLUMN(result, 0, {42}));
21 REQUIRE_NO_FAIL(con.Query("DROP VIEW v1"));
22 REQUIRE_FAIL(con.Query("SELECT j FROM v1 WHERE j > 41"));
23
24 REQUIRE_NO_FAIL(con.Query("CREATE VIEW v1 AS SELECT 'whatever'"));
25 result = con.Query("SELECT * FROM v1");
26 REQUIRE(CHECK_COLUMN(result, 0, {"whatever"}));
27
28 REQUIRE_NO_FAIL(con.Query("CREATE OR REPLACE VIEW v1 AS SELECT 42"));
29 result = con.Query("SELECT * FROM v1");
30 REQUIRE(CHECK_COLUMN(result, 0, {42}));
31
32 REQUIRE_NO_FAIL(con.Query("DROP VIEW v1"));
33 REQUIRE_FAIL(con.Query("DROP VIEW v1"));
34 REQUIRE_NO_FAIL(con.Query("DROP VIEW IF EXISTS v1"));
35
36 REQUIRE_FAIL(con.Query("CREATE VIEW v1 AS SELECT * FROM dontexist"));
37}
38
39TEST_CASE("Test views with changing schema", "[views]") {
40 unique_ptr<QueryResult> result;
41 DuckDB db(nullptr);
42 Connection con(db);
43 con.EnableQueryVerification();
44
45 // create a table
46 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1(i INTEGER)"));
47 REQUIRE_NO_FAIL(con.Query("INSERT INTO t1 VALUES (41), (42), (43)"));
48 // create a view that queries that table
49 REQUIRE_NO_FAIL(con.Query("CREATE VIEW v1 AS SELECT * FROM t1"));
50
51 result = con.Query("SELECT * FROM v1");
52 REQUIRE(CHECK_COLUMN(result, 0, {41, 42, 43}));
53
54 // now drop the table and create a table that has a different schema
55 REQUIRE_NO_FAIL(con.Query("DROP TABLE t1"));
56 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1(i DATE)"));
57
58 // querying the view fails because the column types don't match the expected types
59 REQUIRE_FAIL(con.Query("SELECT * FROM v1"));
60
61 // now drop the table and create one that has extra columns
62 REQUIRE_NO_FAIL(con.Query("DROP TABLE t1"));
63 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1(i INTEGER, j INTEGER)"));
64
65 // again querying the view fails: there are extra columns present
66 REQUIRE_FAIL(con.Query("SELECT * FROM v1"));
67
68 // now drop the table and create one that has differently named columns
69 REQUIRE_NO_FAIL(con.Query("DROP TABLE t1"));
70 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1(k INTEGER)"));
71
72 // this is fine: types still match, and the original names will be applied as alias!
73 result = con.Query("SELECT i FROM v1");
74 REQUIRE(CHECK_COLUMN(result, 0, {}));
75
76 REQUIRE_NO_FAIL(con.Query("DROP TABLE t1"));
77 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1(i INTEGER)"));
78
79 // now we can query again!
80 result = con.Query("SELECT * FROM v1");
81 REQUIRE(CHECK_COLUMN(result, 0, {}));
82}
83
84TEST_CASE("Test deleting/updating views", "[views]") {
85 unique_ptr<QueryResult> result;
86 DuckDB db(nullptr);
87 Connection con(db);
88 con.EnableQueryVerification();
89
90 // create a table
91 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1(i INTEGER)"));
92 REQUIRE_NO_FAIL(con.Query("INSERT INTO t1 VALUES (41), (42), (43)"));
93 // create a view
94 REQUIRE_NO_FAIL(con.Query("CREATE VIEW v1 AS SELECT i AS j FROM t1 WHERE i < 43"));
95
96 // try to delete from the view
97 REQUIRE_FAIL(con.Query("DELETE FROM v1;"));
98 // try to update the view
99 REQUIRE_FAIL(con.Query("UPDATE v1 SET j=1;"));
100}
101
102TEST_CASE("Test view creation with alias", "[views]") {
103 unique_ptr<QueryResult> result;
104 DuckDB db(nullptr);
105 Connection con(db);
106 con.EnableQueryVerification();
107
108 // create a table
109 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1(i INTEGER)"));
110 REQUIRE_NO_FAIL(con.Query("INSERT INTO t1 VALUES (41), (42), (43)"));
111
112 // this should fail because there are more aliases for the view than columns in the query
113 REQUIRE_FAIL(con.Query("CREATE VIEW v1 (j, \"j2\") AS SELECT * FROM t1"));
114
115 REQUIRE_NO_FAIL(con.Query("CREATE VIEW v1 (j, \"j2\") AS SELECT i,i+1 FROM t1"));
116 result = con.Query("SELECT j, j2 FROM v1");
117 REQUIRE(CHECK_COLUMN(result, 0, {41, 42, 43}));
118 REQUIRE(CHECK_COLUMN(result, 1, {42, 43, 44}));
119 REQUIRE(result->types.size() == 2);
120 REQUIRE_NO_FAIL(con.Query("DROP VIEW v1"));
121
122 REQUIRE_NO_FAIL(con.Query("CREATE VIEW v1 (j, \"j2\") AS SELECT i,i+1, i+2 FROM t1"));
123 result = con.Query("SELECT j, j2 FROM v1");
124 REQUIRE(result->types.size() == 2);
125 REQUIRE(CHECK_COLUMN(result, 0, {41, 42, 43}));
126 REQUIRE(CHECK_COLUMN(result, 1, {42, 43, 44}));
127 REQUIRE_NO_FAIL(con.Query("DROP VIEW v1"));
128
129 REQUIRE_NO_FAIL(con.Query("CREATE VIEW v1 (j, \"j2\") AS SELECT i,i+1, i+2 as x FROM t1"));
130 result = con.Query("SELECT j, j2, x FROM v1");
131 REQUIRE(result->types.size() == 3);
132 REQUIRE(CHECK_COLUMN(result, 0, {41, 42, 43}));
133 REQUIRE(CHECK_COLUMN(result, 1, {42, 43, 44}));
134 REQUIRE(CHECK_COLUMN(result, 2, {43, 44, 45}));
135
136 REQUIRE_NO_FAIL(con.Query("DROP VIEW v1"));
137}
138
139TEST_CASE("Stacked views uh yeah", "[views]") {
140 unique_ptr<QueryResult> result;
141 DuckDB db(nullptr);
142 Connection con(db);
143 con.EnableQueryVerification();
144
145 // create a table
146 REQUIRE_NO_FAIL(con.Query("CREATE TABLE t1(i INTEGER)"));
147 REQUIRE_NO_FAIL(con.Query("INSERT INTO t1 VALUES (41), (42), (43), (44)"));
148
149 REQUIRE_NO_FAIL(con.Query("CREATE VIEW v1 (v1c1, v1c2) AS SELECT i,i+1 FROM t1 WHERE i > 41"));
150 REQUIRE_NO_FAIL(
151 con.Query("CREATE VIEW v2 (v2c1, v2c2, v2c3) AS SELECT v1c1, v1c2, v1c1+v1c2 FROM v1 WHERE v1c2 > 42"));
152 REQUIRE_NO_FAIL(con.Query("CREATE VIEW v3 (v3c1, v3c2) AS SELECT v2c1, v2c3 FROM v2 WHERE v2c1 > 43"));
153
154 result = con.Query("SELECT v3c2+1 FROM v3 WHERE v3c1 > 42");
155
156 REQUIRE(result->types.size() == 1);
157 REQUIRE(CHECK_COLUMN(result, 0, {90}));
158}
159