1#include "catch.hpp"
2#include "duckdb/common/file_system.hpp"
3#include "dbgen.hpp"
4#include "test_helpers.hpp"
5
6using namespace duckdb;
7using namespace std;
8
9TEST_CASE("Test not equals join", "[join]") {
10 unique_ptr<QueryResult> result;
11 DuckDB db(nullptr);
12 Connection con(db);
13 con.EnableQueryVerification();
14
15 REQUIRE_NO_FAIL(con.Query("create table a (i integer)"));
16 REQUIRE_NO_FAIL(con.Query(
17 "insert into a values "
18 "('28579'),('16098'),('25281'),('28877'),('18048'),('26820'),('26971'),('22812'),('11757'),('21851'),('27752'),"
19 "('28354'),('29843'),('28828'),('16668'),('20534'),('28222'),('24244'),('28877'),('20150'),('23451'),('23683'),"
20 "('20419'),('28048'),('24244'),('28605'),('25752'),('24466'),('26557'),('16098'),('29454'),('24854'),('13298'),"
21 "('29584'),('13394'),('24843'),('22477'),('14593'),('24244'),('28722'),('25124'),('16668'),('26787'),('28877'),"
22 "('27752'),('28482'),('24408'),('25752'),('24136'),('28222'),('17683'),('24244'),('19275'),('21087'),('26594'),"
23 "('22293'),('25281'),('12898'),('23451'),('12898'),('21757'),('20965'),('25709'),('26614'),('10399'),('28773'),"
24 "('11933'),('29584'),('29003'),('26871'),('17746'),('24092'),('26192'),('19310'),('10965'),('29275'),('20191'),"
25 "('29101'),('28059'),('29584'),('20399'),('24338'),('26192'),('25124'),('28605'),('13003'),('16668'),('23511'),"
26 "('26534'),('24107')"));
27
28 REQUIRE_NO_FAIL(con.Query("create table b (j integer)"));
29 REQUIRE_NO_FAIL(con.Query("insert into b values "
30 "('31904'),('31904'),('31904'),('31904'),('35709'),('31904'),('31904'),('35709'),('31904'"
31 "),('31904'),('31904'),('31904')"));
32
33 result = con.Query("select count(*) from a,b where i <> j");
34 REQUIRE(CHECK_COLUMN(result, 0, {1080}));
35}
36
37TEST_CASE("Test less than join", "[join][.]") {
38 unique_ptr<QueryResult> result;
39 DuckDB db(nullptr);
40 Connection con(db);
41 con.EnableQueryVerification();
42
43 REQUIRE_NO_FAIL(con.Query("create table a (i integer)"));
44 for (idx_t i = 0; i < 2000; i++) {
45 REQUIRE_NO_FAIL(con.Query("insert into a values ($1)", (int32_t)i + 1));
46 }
47
48 result = con.Query("select count(*) from a, (SELECT 2000 AS j) b where i < j");
49 REQUIRE(CHECK_COLUMN(result, 0, {1999}));
50
51 result = con.Query("select count(*) from a, (SELECT 2000 AS j) b where i <= j");
52 REQUIRE(CHECK_COLUMN(result, 0, {2000}));
53
54 result = con.Query("select count(*) from a, (SELECT 1 AS j) b where i > j");
55 REQUIRE(CHECK_COLUMN(result, 0, {1999}));
56
57 result = con.Query("select count(*) from a, (SELECT 1 AS j) b where i >= j");
58 REQUIRE(CHECK_COLUMN(result, 0, {2000}));
59}
60
61TEST_CASE("Test joins with different types", "[join]") {
62 unique_ptr<QueryResult> result;
63 DuckDB db(nullptr);
64 Connection con(db);
65 con.EnableQueryVerification();
66
67 // numeric types
68 vector<string> numeric_types = {"tinyint", "smallint", "integer", "bigint", "real", "double"};
69 for (auto &type : numeric_types) {
70 REQUIRE_NO_FAIL(con.Query("begin transaction"));
71 REQUIRE_NO_FAIL(con.Query("create table a (i " + type + ")"));
72 for (idx_t i = 0; i < 100; i++) {
73 REQUIRE_NO_FAIL(con.Query("insert into a values ($1)", (int32_t)i + 1));
74 }
75 // range joins
76 result = con.Query("select count(*), sum(i) from a, (SELECT 100::" + type + " AS j) b where i < j");
77 REQUIRE(CHECK_COLUMN(result, 0, {99}));
78 REQUIRE(CHECK_COLUMN(result, 1, {4950}));
79 result = con.Query("select count(*) from a, (SELECT 100::" + type + " AS j) b where i <= j");
80 REQUIRE(CHECK_COLUMN(result, 0, {100}));
81 result = con.Query("select count(*) from a, (SELECT 1::" + type + " AS j) b where i > j");
82 REQUIRE(CHECK_COLUMN(result, 0, {99}));
83 result = con.Query("select count(*) from a, (SELECT 1::" + type + " AS j) b where i >= j");
84 REQUIRE(CHECK_COLUMN(result, 0, {100}));
85 // inequality join
86 result = con.Query("select count(*) from a, (SELECT 1::" + type + " AS j) b where i <> j");
87 REQUIRE(CHECK_COLUMN(result, 0, {99}));
88 // equality join
89 result = con.Query("select count(*) from a, (SELECT 1::" + type + " AS j) b where i = j");
90 REQUIRE(CHECK_COLUMN(result, 0, {1}));
91 // no results on one side
92 result = con.Query("select count(*) from a, (SELECT 1::" + type + " AS j) b where i > j AND i>1000");
93 REQUIRE(CHECK_COLUMN(result, 0, {0}));
94 result = con.Query("select count(*) from a, (SELECT 1::" + type + " AS j) b where i <> j AND i>1000");
95 REQUIRE(CHECK_COLUMN(result, 0, {0}));
96 result = con.Query("select count(*) from a, (SELECT 1::" + type + " AS j) b where i = j AND i>1000");
97 REQUIRE(CHECK_COLUMN(result, 0, {0}));
98
99 REQUIRE_NO_FAIL(con.Query("rollback"));
100 }
101 // strings
102 REQUIRE_NO_FAIL(con.Query("begin transaction"));
103 REQUIRE_NO_FAIL(con.Query("create table a (i VARCHAR)"));
104 REQUIRE_NO_FAIL(con.Query("insert into a values ('a'), ('b'), ('c'), ('d'), ('e'), ('f')"));
105
106 // range joins
107 result = con.Query("select count(*) from a, (SELECT 'f' AS j) b where i < j");
108 REQUIRE(CHECK_COLUMN(result, 0, {5}));
109 result = con.Query("select count(*) from a, (SELECT 'f' AS j) b where i <= j");
110 REQUIRE(CHECK_COLUMN(result, 0, {6}));
111 result = con.Query("select count(*) from a, (SELECT 'a' AS j) b where i > j");
112 REQUIRE(CHECK_COLUMN(result, 0, {5}));
113 result = con.Query("select count(*) from a, (SELECT 'a' AS j) b where i >= j");
114 REQUIRE(CHECK_COLUMN(result, 0, {6}));
115 result = con.Query("select count(*) from a, (SELECT 'a' AS j) b where i <> j");
116 REQUIRE(CHECK_COLUMN(result, 0, {5}));
117 result = con.Query("select count(*) from a, (SELECT 'a' AS j) b where i = j");
118 REQUIRE(CHECK_COLUMN(result, 0, {1}));
119
120 REQUIRE_NO_FAIL(con.Query("rollback"));
121}
122
123TEST_CASE("Test mark join with different types", "[join]") {
124 unique_ptr<QueryResult> result;
125 DuckDB db(nullptr);
126 Connection con(db);
127 con.EnableQueryVerification();
128
129 // numeric types
130 vector<string> numeric_types = {"tinyint", "smallint", "integer", "bigint", "real", "double"};
131 for (auto &type : numeric_types) {
132 REQUIRE_NO_FAIL(con.Query("begin transaction"));
133 REQUIRE_NO_FAIL(con.Query("create table a (i " + type + ")"));
134 std::vector<int32_t> values;
135 for (idx_t i = 0; i < 100; i++) {
136 values.push_back(i + 1);
137 }
138 std::random_shuffle(values.begin(), values.end());
139 for (idx_t i = 0; i < values.size(); i++) {
140 REQUIRE_NO_FAIL(con.Query("insert into a values ($1)", values[i]));
141 }
142
143 // range joins
144 result = con.Query("select count(*) from a WHERE i > ANY((SELECT 1::" + type + "))");
145 REQUIRE(CHECK_COLUMN(result, 0, {99}));
146 result = con.Query("select count(*) from a WHERE i >= ANY((SELECT 1::" + type + "))");
147 REQUIRE(CHECK_COLUMN(result, 0, {100}));
148 result = con.Query("select count(*) from a WHERE i < ANY((SELECT 100::" + type + "))");
149 REQUIRE(CHECK_COLUMN(result, 0, {99}));
150 result = con.Query("select count(*) from a WHERE i <= ANY((SELECT 100::" + type + "))");
151 REQUIRE(CHECK_COLUMN(result, 0, {100}));
152 result = con.Query("select count(*) from a WHERE i = ANY((SELECT 1::" + type + "))");
153 REQUIRE(CHECK_COLUMN(result, 0, {1}));
154 result = con.Query("select count(*) from a WHERE i <> ANY((SELECT 1::" + type + "))");
155 REQUIRE(CHECK_COLUMN(result, 0, {99}));
156
157 // now with a filter
158 result = con.Query("select count(*) from (select * from a where i % 2 = 0) a WHERE i > ANY((SELECT 2::" + type +
159 "))");
160 REQUIRE(CHECK_COLUMN(result, 0, {49}));
161 result = con.Query(
162 "select count(*) from (select * from a where i % 2 = 0) a WHERE i >= ANY((SELECT 2::" + type + "))");
163 REQUIRE(CHECK_COLUMN(result, 0, {50}));
164 result = con.Query(
165 "select count(*) from (select * from a where i % 2 = 0) a WHERE i < ANY((SELECT 100::" + type + "))");
166 REQUIRE(CHECK_COLUMN(result, 0, {49}));
167 result = con.Query(
168 "select count(*) from (select * from a where i % 2 = 0) a WHERE i <= ANY((SELECT 100::" + type + "))");
169 REQUIRE(CHECK_COLUMN(result, 0, {50}));
170 result = con.Query("select * from (select * from a where i % 2 = 0) a WHERE i = ANY((SELECT 2::" + type + "))");
171 REQUIRE(CHECK_COLUMN(result, 0, {2}));
172 result = con.Query(
173 "select count(*) from (select * from a where i % 2 = 0) a WHERE i <> ANY((SELECT 2::" + type + "))");
174 REQUIRE(CHECK_COLUMN(result, 0, {49}));
175
176 // now select the actual values, instead of only the count
177 result = con.Query("select * from (select * from a where i % 2 = 0) a WHERE i <= ANY((SELECT 10::" + type +
178 ")) ORDER BY 1");
179 REQUIRE(CHECK_COLUMN(result, 0, {2, 4, 6, 8, 10}));
180 result = con.Query("select * from (select * from a where i % 2 = 0) a WHERE i >= ANY((SELECT 90::" + type +
181 ")) ORDER BY 1");
182 REQUIRE(CHECK_COLUMN(result, 0, {90, 92, 94, 96, 98, 100}));
183 result = con.Query("select * from (select * from a where i > 90) a WHERE i <> ANY((SELECT 96::" + type +
184 ")) ORDER BY 1");
185 REQUIRE(CHECK_COLUMN(result, 0, {91, 92, 93, 94, 95, 97, 98, 99, 100}));
186
187 REQUIRE_NO_FAIL(con.Query("rollback"));
188 }
189 // strings
190 REQUIRE_NO_FAIL(con.Query("begin transaction"));
191 REQUIRE_NO_FAIL(con.Query("create table a (i VARCHAR)"));
192 REQUIRE_NO_FAIL(con.Query("insert into a values ('a'), ('b'), ('c'), ('d'), ('e'), ('f')"));
193
194 // range joins
195 result = con.Query("select count(*) from a WHERE i < ANY((SELECT 'f'))");
196 REQUIRE(CHECK_COLUMN(result, 0, {5}));
197 result = con.Query("select count(*) from a WHERE i <= ANY((SELECT 'f' AS j))");
198 REQUIRE(CHECK_COLUMN(result, 0, {6}));
199 result = con.Query("select count(*) from a WHERE i > ANY((SELECT 'a'))");
200 REQUIRE(CHECK_COLUMN(result, 0, {5}));
201 result = con.Query("select count(*) from a WHERE i >= ANY((SELECT 'a'))");
202 REQUIRE(CHECK_COLUMN(result, 0, {6}));
203 result = con.Query("select count(*) from a WHERE i <> ANY((SELECT 'a'))");
204 REQUIRE(CHECK_COLUMN(result, 0, {5}));
205 result = con.Query("select count(*) from a WHERE i = ANY((SELECT 'a'))");
206 REQUIRE(CHECK_COLUMN(result, 0, {1}));
207
208 REQUIRE_NO_FAIL(con.Query("rollback"));
209}
210