message: More subquery tests
diff: @@ -69,3 +69,40 @@ TEST_CASE("Test subqueries", "[subqueries]") {
  REQUIRE(CHECK_COLUMN(result, 0, {11, 13}));
  REQUIRE(CHECK_COLUMN(result, 1, {22, 22}));
  }
  +
  +TEST_CASE("Test correlated subqueries in WHERE clause", "[subqueries]") {
  + unique_ptr<DuckDBResult> result;
  + DuckDB db(nullptr);
  + DuckDBConnection con(db);
  +
  + REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (id INTEGER, b INTEGER);"));
  + REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (1, 22)"));
  + REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (1, 21)"));
  + REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (2, 22)"));
  +
  + result = con.Query("SELECT * FROM test WHERE b=(SELECT MIN(b) FROM test AS a WHERE a.id=test.id)");
  + REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
  + REQUIRE(CHECK_COLUMN(result, 1, {21, 22}));
  +}
  +
  +
  +TEST_CASE("Joins in subqueries", "[subqueries]") {
  + unique_ptr<DuckDBResult> result;
  + DuckDB db(nullptr);
  + DuckDBConnection con(db);
  +
  + REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (id INTEGER, test_value INTEGER);"));
  + REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (1, 22)"));
  + REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (1, 21)"));
  + REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (2, 22)"));
  +
  + REQUIRE_NO_FAIL(con.Query("CREATE TABLE test2 (id INTEGER, test2_value INTEGER);"));
  + REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (1, 44)"));
  + REQUIRE_NO_FAIL(con.Query("INSERT INTO test2 VALUES (2, 42)"));
  +
  + result = con.Query("SELECT * FROM test, test2 WHERE test.id=test2.id AND test_value*test2_value=(SELECT MIN(test_value*test2_value) FROM test AS a, test2 WHERE a.id=test.id AND a.id=test2.id)");
  + REQUIRE(CHECK_COLUMN(result, 0, {1, 2}));
  + REQUIRE(CHECK_COLUMN(result, 1, {21, 22}));
  + REQUIRE(CHECK_COLUMN(result, 2, {1, 2}));
  + REQUIRE(CHECK_COLUMN(result, 3, {44, 42}));
  +}
  