1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Test basic comparison statements", "[comparison]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11 con.EnableQueryVerification();
12
13 // = and == are the same
14 result = con.Query("SELECT 1 == 1, 1 = 1, 1 == 0, 1 = 0, 1 == NULL");
15 REQUIRE(CHECK_COLUMN(result, 0, {true}));
16 REQUIRE(CHECK_COLUMN(result, 1, {true}));
17 REQUIRE(CHECK_COLUMN(result, 2, {false}));
18 REQUIRE(CHECK_COLUMN(result, 3, {false}));
19 REQUIRE(CHECK_COLUMN(result, 4, {Value()}));
20
21 // != and <> are the same
22 result = con.Query("SELECT 1 <> 1, 1 != 1, 1 <> 0, 1 != 0, 1 <> NULL");
23 REQUIRE(CHECK_COLUMN(result, 0, {false}));
24 REQUIRE(CHECK_COLUMN(result, 1, {false}));
25 REQUIRE(CHECK_COLUMN(result, 2, {true}));
26 REQUIRE(CHECK_COLUMN(result, 3, {true}));
27 REQUIRE(CHECK_COLUMN(result, 4, {Value()}));
28}
29
30TEST_CASE("Test strcmp() to ensure platform sanity", "[comparison]") {
31 int res;
32 res = strcmp("ZZZ", "ZZZ");
33 REQUIRE(res == 0);
34
35 res = strcmp("ZZZ", "HXR");
36 REQUIRE(res > 0);
37
38 res = strcmp("ZZZ", "NUT");
39 REQUIRE(res > 0);
40
41 res = strcmp("HXR", "ZZZ");
42 REQUIRE(res < 0);
43
44 res = strcmp("HXR", "HXR");
45 REQUIRE(res == 0);
46
47 res = strcmp("HXR", "NUT");
48 REQUIRE(res < 0);
49
50 res = strcmp("NUT", "ZZZ");
51 REQUIRE(res < 0);
52
53 res = strcmp("NUT", "HXR");
54 REQUIRE(res > 0);
55
56 res = strcmp("NUT", "NUT");
57 REQUIRE(res == 0);
58
59 Value zzz("ZZZ");
60 Value hxr("HXR");
61 Value nut("NUT");
62
63 REQUIRE_FALSE(zzz > zzz);
64 REQUIRE(zzz > hxr);
65 REQUIRE(zzz > nut);
66
67 REQUIRE(zzz >= zzz);
68 REQUIRE(zzz >= hxr);
69 REQUIRE(zzz >= nut);
70
71 REQUIRE(zzz <= zzz);
72 REQUIRE_FALSE(zzz <= hxr);
73 REQUIRE_FALSE(zzz <= nut);
74
75 REQUIRE(zzz == zzz);
76 REQUIRE_FALSE(zzz == hxr);
77 REQUIRE_FALSE(zzz == nut);
78
79 REQUIRE_FALSE(zzz != zzz);
80 REQUIRE(zzz != hxr);
81 REQUIRE(zzz != nut);
82
83 REQUIRE_FALSE(hxr > zzz);
84 REQUIRE_FALSE(hxr > hxr);
85 REQUIRE_FALSE(hxr > nut);
86
87 REQUIRE_FALSE(hxr >= zzz);
88 REQUIRE(hxr >= hxr);
89 REQUIRE_FALSE(hxr >= nut);
90
91 REQUIRE(hxr <= zzz);
92 REQUIRE(hxr <= hxr);
93 REQUIRE(hxr <= nut);
94
95 REQUIRE_FALSE(hxr == zzz);
96 REQUIRE(hxr == hxr);
97 REQUIRE_FALSE(hxr == nut);
98
99 REQUIRE(hxr != zzz);
100 REQUIRE_FALSE(hxr != hxr);
101 REQUIRE(hxr != nut);
102
103 REQUIRE_FALSE(nut > zzz);
104 REQUIRE(nut > hxr);
105 REQUIRE_FALSE(nut > nut);
106
107 REQUIRE_FALSE(nut >= zzz);
108 REQUIRE(nut >= hxr);
109 REQUIRE(nut >= nut);
110
111 REQUIRE(nut <= zzz);
112 REQUIRE_FALSE(nut <= hxr);
113 REQUIRE(nut <= nut);
114
115 REQUIRE_FALSE(nut == zzz);
116 REQUIRE_FALSE(nut == hxr);
117 REQUIRE(nut == nut);
118
119 REQUIRE(nut != zzz);
120 REQUIRE(nut != hxr);
121 REQUIRE_FALSE(nut != nut);
122}
123
124TEST_CASE("Test auto casting of comparison statements", "[comparison]") {
125 unique_ptr<QueryResult> result;
126 DuckDB db(nullptr);
127 Connection con(db);
128 con.EnableQueryVerification();
129
130 // string <> number comparisons should result in the string being cast to a number
131 REQUIRE_FAIL(con.Query("select ('abc' between 20 and True);"));
132 REQUIRE_FAIL(con.Query("select 'abc' > 10"));
133 REQUIRE_FAIL(con.Query("select 20.0 = 'abc'"));
134
135 // 1000 > 20
136 result = con.Query("select '1000' > 20");
137 REQUIRE(CHECK_COLUMN(result, 0, {true}));
138 // ... but '1000' < '20'!
139 result = con.Query("select '1000' > '20'");
140 REQUIRE(CHECK_COLUMN(result, 0, {false}));
141
142 result = con.Query("select ('abc' between '20' and 'true');");
143 REQUIRE(CHECK_COLUMN(result, 0, {true}));
144}
145