1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test queries involving Common SubExpressions", "[cse]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11 con.EnableQueryVerification();
12
13 REQUIRE_NO_FAIL(con.Query("create table test(a integer);"));
14 REQUIRE_NO_FAIL(con.Query("insert into test values (42);"));
15
16 // single CSE
17 result = con.Query("SELECT (a*2)+(a*2) FROM test");
18 REQUIRE(CHECK_COLUMN(result, 0, {168}));
19 // multiple CSE
20 result = con.Query("SELECT (a*2)+(a*2)+(a*2)+(a*2)+(a*2) FROM test");
21 REQUIRE(CHECK_COLUMN(result, 0, {420}));
22
23 // CSE in WHERE clause
24 result = con.Query("SELECT * FROM test WHERE ((a*2)+(a*2))>100");
25 REQUIRE(CHECK_COLUMN(result, 0, {42}));
26 // multiple CSE in WHERE clause
27 result = con.Query("SELECT * FROM test WHERE ((a*2)+(a*2)+(a*2)+(a*2)+(a*2))>400");
28 REQUIRE(CHECK_COLUMN(result, 0, {42}));
29}
30
31TEST_CASE("Test queries involving Common SubExpressions and Strings and NULL values", "[cse]") {
32 unique_ptr<QueryResult> result;
33 DuckDB db(nullptr);
34 Connection con(db);
35 con.EnableQueryVerification();
36
37 REQUIRE_NO_FAIL(con.Query("create table test(a VARCHAR);"));
38 REQUIRE_NO_FAIL(con.Query("insert into test values ('hello'), ('world'), (NULL);"));
39
40 // single CSE in projection
41 result = con.Query("SELECT substring(a, 1, 3)=substring(a, 1, 3) FROM test ORDER BY 1");
42 REQUIRE(CHECK_COLUMN(result, 0, {Value(), true, true}));
43
44 // now with GROUP BY clause
45 result = con.Query("SELECT substring(a, 1, 3)=substring(a, 1, 3) AS b FROM test GROUP BY b ORDER BY b");
46 // result->Print();
47 REQUIRE(CHECK_COLUMN(result, 0, {Value(), true}));
48}
49