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("MonetDB Test: leftjoin.Bug-3981.sql", "[monetdb]") {
10 unique_ptr<QueryResult> result;
11 DuckDB db(nullptr);
12 Connection con(db);
13 con.EnableQueryVerification();
14
15 result = con.Query(
16 "SELECT * FROM ( SELECT 'apple' as fruit UNION ALL SELECT 'banana' ) a JOIN ( SELECT 'apple' as fruit UNION "
17 "ALL SELECT 'banana' ) b ON a.fruit=b.fruit LEFT JOIN ( SELECT 1 as isyellow ) c ON b.fruit='banana' ORDER BY "
18 "1, 2, 3;");
19 REQUIRE(CHECK_COLUMN(result, 0, {"apple", "banana"}));
20 REQUIRE(CHECK_COLUMN(result, 1, {"apple", "banana"}));
21 REQUIRE(CHECK_COLUMN(result, 2, {Value(), 1}));
22}
23
24TEST_CASE("MonetDB Test: null_matches_in_outer.Bug-6398.sql", "[monetdb]") {
25 unique_ptr<QueryResult> result;
26 DuckDB db(nullptr);
27 Connection con(db);
28 con.EnableQueryVerification();
29
30 REQUIRE_NO_FAIL(con.Query("create table \"E\" (\"intCol\" bigint, \"stringCol\" varchar);"));
31
32 REQUIRE_NO_FAIL(con.Query("insert into \"E\" values (0, 'zero');"));
33 REQUIRE_NO_FAIL(con.Query("insert into \"E\" values (1, 'one');"));
34 REQUIRE_NO_FAIL(con.Query("insert into \"E\" values (2, 'two');"));
35 REQUIRE_NO_FAIL(con.Query("insert into \"E\" values (null, null);"));
36
37 REQUIRE_NO_FAIL(con.Query("create table \"I\" (\"intCol\" bigint, \"stringCol\" varchar);"));
38
39 REQUIRE_NO_FAIL(con.Query("insert into \"I\" values (2, 'due');"));
40 REQUIRE_NO_FAIL(con.Query("insert into \"I\" values (4, 'quattro');"));
41 REQUIRE_NO_FAIL(con.Query("insert into \"I\" values (null, 'this is not null')"));
42
43 result = con.Query("select * from \"E\" left outer join \"I\" on \"E\".\"intCol\" = \"I\".\"intCol\" or "
44 "(\"E\".\"intCol\" is null and \"I\".\"intCol\" is null) ORDER BY 1;");
45 REQUIRE(CHECK_COLUMN(result, 0, {Value(), 0, 1, 2}));
46 REQUIRE(CHECK_COLUMN(result, 1, {Value(), "zero", "one", "two"}));
47 REQUIRE(CHECK_COLUMN(result, 2, {Value(), Value(), Value(), 2}));
48 REQUIRE(CHECK_COLUMN(result, 3, {"this is not null", Value(), Value(), "due"}));
49}
50
51TEST_CASE("MonetDB Test: outerjoin_project.Bug-3725.sql", "[monetdb]") {
52 unique_ptr<QueryResult> result;
53 DuckDB db(nullptr);
54 Connection con(db);
55 con.EnableQueryVerification();
56
57 REQUIRE_NO_FAIL(con.Query("create table a (a integer);"));
58 REQUIRE_NO_FAIL(con.Query("create table b (a integer);"));
59 REQUIRE_NO_FAIL(con.Query("insert into a values (1);"));
60
61 result = con.Query("select * from a left join (select a, 20 from b) as x using (a);");
62 REQUIRE(CHECK_COLUMN(result, 0, {1}));
63 REQUIRE(CHECK_COLUMN(result, 1, {Value()}));
64}
65