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 lateral joins", "[subquery]") {
10 unique_ptr<QueryResult> result;
11 DuckDB db(nullptr);
12 Connection con(db);
13 con.EnableQueryVerification();
14
15 return;
16 // not supported yet
17
18 REQUIRE_NO_FAIL(con.Query("CREATE TABLE students(id INTEGER, name VARCHAR, major VARCHAR, year INTEGER)"));
19 REQUIRE_NO_FAIL(
20 con.Query("CREATE TABLE exams(sid INTEGER, course VARCHAR, curriculum VARCHAR, grade INTEGER, year INTEGER)"));
21
22 REQUIRE_NO_FAIL(con.Query("INSERT INTO students VALUES (1, 'Mark', 'CS', 2017)"));
23 REQUIRE_NO_FAIL(con.Query("INSERT INTO students VALUES (2, 'Dirk', 'CS', 2017)"));
24 REQUIRE_NO_FAIL(con.Query("INSERT INTO exams VALUES (1, 'Database Systems', 'CS', 10, 2015)"));
25 REQUIRE_NO_FAIL(con.Query("INSERT INTO exams VALUES (1, 'Graphics', 'CS', 9, 2016)"));
26 REQUIRE_NO_FAIL(con.Query("INSERT INTO exams VALUES (2, 'Database Systems', 'CS', 7, 2015)"));
27 REQUIRE_NO_FAIL(con.Query("INSERT INTO exams VALUES (2, 'Graphics', 'CS', 7, 2016)"));
28
29 // // lateral join with explicit LATERAL added
30 // result = con.Query("SELECT name, total FROM students LEFT JOIN LATERAL (SELECT SUM(grade) AS total FROM exams
31 // WHERE exams.sid=students.id) grades ON true ORDER BY total DESC;"); REQUIRE(CHECK_COLUMN(result, 0, {"Mark",
32 // "Dirk"})); REQUIRE(CHECK_COLUMN(result, 1, {19, 14}));
33
34 // lateral join without explicit LATERAL
35 result = con.Query("SELECT name, total FROM students, (SELECT SUM(grade) AS total FROM exams WHERE "
36 "exams.sid=students.id) grades ORDER BY total DESC;");
37 REQUIRE(CHECK_COLUMN(result, 0, {"Mark", "Dirk"}));
38 REQUIRE(CHECK_COLUMN(result, 1, {19, 14}));
39}
40