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 join on aggregates", "[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 integers(i INTEGER)"));
16 REQUIRE_NO_FAIL(con.Query("INSERT INTO integers VALUES (1), (2), (3), (NULL)"));
17
18 result = con.Query(
19 "SELECT * FROM (SELECT SUM(i) AS x FROM integers) a, (SELECT SUM(i) AS x FROM integers) b WHERE a.x=b.x;");
20 REQUIRE(CHECK_COLUMN(result, 0, {6}));
21 REQUIRE(CHECK_COLUMN(result, 1, {6}));
22}
23
24TEST_CASE("Test join on aggregates with GROUP BY", "[join]") {
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 groups(i INTEGER, j INTEGER)"));
31 REQUIRE_NO_FAIL(con.Query("INSERT INTO groups VALUES (1, 1), (2, 1), (3, 2), (NULL, 2)"));
32
33 result =
34 con.Query("SELECT a.j,a.x,a.y,b.y FROM (SELECT j, MIN(i) AS y, SUM(i) AS x FROM groups GROUP BY j) a, (SELECT "
35 "j, MIN(i) AS y, SUM(i) AS x FROM groups GROUP BY j) b WHERE a.j=b.j AND a.x=b.x ORDER BY a.j;");
36 REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
37 REQUIRE(CHECK_COLUMN(result, 1, {3, 3}));
38 REQUIRE(CHECK_COLUMN(result, 2, {1, 3}));
39 REQUIRE(CHECK_COLUMN(result, 3, {1, 3}));
40}
41