1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test expressions with constant comparisons", "[filter]") {
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 integers(a INTEGER, b INTEGER)"));
14 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (2, 12)"));
15
16 // Test constant comparisons
17 // equality
18 result = con.Query("SELECT * FROM integers WHERE 2=2");
19 REQUIRE(CHECK_COLUMN(result, 0, {2}));
20 REQUIRE(CHECK_COLUMN(result, 1, {12}));
21 result = con.Query("SELECT * FROM integers WHERE 2=3");
22 REQUIRE(CHECK_COLUMN(result, 0, {}));
23 // inequality
24 result = con.Query("SELECT * FROM integers WHERE 2<>3");
25 REQUIRE(CHECK_COLUMN(result, 0, {2}));
26 REQUIRE(CHECK_COLUMN(result, 1, {12}));
27 result = con.Query("SELECT * FROM integers WHERE 2<>2");
28 REQUIRE(CHECK_COLUMN(result, 0, {}));
29 // greater than
30 result = con.Query("SELECT * FROM integers WHERE 2>1");
31 REQUIRE(CHECK_COLUMN(result, 0, {2}));
32 REQUIRE(CHECK_COLUMN(result, 1, {12}));
33 result = con.Query("SELECT * FROM integers WHERE 2>2");
34 REQUIRE(CHECK_COLUMN(result, 0, {}));
35 // greater than equals
36 result = con.Query("SELECT * FROM integers WHERE 2>=2");
37 REQUIRE(CHECK_COLUMN(result, 0, {2}));
38 REQUIRE(CHECK_COLUMN(result, 1, {12}));
39 result = con.Query("SELECT * FROM integers WHERE 2>=3");
40 REQUIRE(CHECK_COLUMN(result, 0, {}));
41 // less than
42 result = con.Query("SELECT * FROM integers WHERE 2<3");
43 REQUIRE(CHECK_COLUMN(result, 0, {2}));
44 REQUIRE(CHECK_COLUMN(result, 1, {12}));
45 result = con.Query("SELECT * FROM integers WHERE 2<2");
46 REQUIRE(CHECK_COLUMN(result, 0, {}));
47 // less than equals
48 result = con.Query("SELECT * FROM integers WHERE 2<=2");
49 REQUIRE(CHECK_COLUMN(result, 0, {2}));
50 REQUIRE(CHECK_COLUMN(result, 1, {12}));
51 result = con.Query("SELECT * FROM integers WHERE 2<=1");
52 REQUIRE(CHECK_COLUMN(result, 0, {}));
53
54 // Test comparisons with NULL
55 result = con.Query("SELECT a=NULL FROM integers");
56 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
57 result = con.Query("SELECT NULL=a FROM integers");
58 REQUIRE(CHECK_COLUMN(result, 0, {Value()}));
59}
60
61TEST_CASE("More complex constant expressions", "[filter]") {
62 unique_ptr<QueryResult> result;
63 DuckDB db(nullptr);
64 Connection con(db);
65 con.EnableQueryVerification();
66
67 REQUIRE_NO_FAIL(con.Query("CREATE TABLE integers(a INTEGER, b INTEGER)"));
68 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (2, 12)"));
69
70 // Test constant comparisons
71 // IN expressions
72 result = con.Query("SELECT * FROM integers WHERE 2 IN (2, 3, 4, 5)");
73 REQUIRE(CHECK_COLUMN(result, 0, {2}));
74 REQUIRE(CHECK_COLUMN(result, 1, {12}));
75 result = con.Query("SELECT * FROM integers WHERE 2 NOT IN (2, 3, 4, 5)");
76 REQUIRE(CHECK_COLUMN(result, 0, {}));
77 result = con.Query("SELECT * FROM integers WHERE 2 IN (((1*2)+(1*0))*1, 3, 4, 5)");
78 REQUIRE(CHECK_COLUMN(result, 0, {2}));
79 REQUIRE(CHECK_COLUMN(result, 1, {12}));
80 result = con.Query("SELECT * FROM integers WHERE 2 IN ((1+1)*2, 3, 4, 5)");
81 REQUIRE(CHECK_COLUMN(result, 0, {}));
82 // CASE expressions
83 result = con.Query("SELECT CASE WHEN 1 THEN 13 ELSE 12 END;");
84 REQUIRE(CHECK_COLUMN(result, 0, {13}));
85 result = con.Query("SELECT * FROM integers WHERE CASE WHEN 2=2 THEN true ELSE false END;");
86 REQUIRE(CHECK_COLUMN(result, 0, {2}));
87 REQUIRE(CHECK_COLUMN(result, 1, {12}));
88 result = con.Query("SELECT * FROM integers WHERE CASE WHEN 2=3 THEN true ELSE false END;");
89 REQUIRE(CHECK_COLUMN(result, 0, {}));
90}
91