1#include "catch.hpp"
2#include "test_helpers.hpp"
3
4using namespace duckdb;
5using namespace std;
6
7TEST_CASE("Table subquery", "[subquery]") {
8 unique_ptr<QueryResult> result;
9 DuckDB db(nullptr);
10 Connection con(db);
11 con.EnableQueryVerification();
12
13 REQUIRE_NO_FAIL(con.Query("CREATE TABLE test (i INTEGER, j INTEGER)"));
14 REQUIRE_NO_FAIL(con.Query("INSERT INTO test VALUES (3, 4), (4, 5), (5, 6);"));
15
16 result = con.Query("SELECT * FROM (SELECT i, j AS d FROM test ORDER BY i) AS b;");
17 REQUIRE(CHECK_COLUMN(result, 0, {3, 4, 5}));
18 REQUIRE(CHECK_COLUMN(result, 1, {4, 5, 6}));
19
20 // check column names for simple projections and aliases
21 result = con.Query("SELECT b.d FROM (SELECT i * 2 + j AS d FROM test) AS b;");
22 REQUIRE(CHECK_COLUMN(result, 0, {10, 13, 16}));
23
24 // join with subqueries
25 result = con.Query("SELECT a.i,a.j,b.r,b.j FROM (SELECT i, j FROM test) AS a "
26 "INNER JOIN (SELECT i+1 AS r,j FROM test) AS b ON a.i=b.r ORDER BY 1;");
27 REQUIRE(CHECK_COLUMN(result, 0, {4, 5}));
28 REQUIRE(CHECK_COLUMN(result, 1, {5, 6}));
29 REQUIRE(CHECK_COLUMN(result, 2, {4, 5}));
30 REQUIRE(CHECK_COLUMN(result, 3, {4, 5}));
31
32 // check that * is in the correct order
33 result = con.Query("SELECT * FROM (SELECT i, j FROM test) AS a, (SELECT "
34 "i+1 AS r,j FROM test) AS b, test WHERE a.i=b.r AND test.j=a.i ORDER BY 1;");
35 REQUIRE(CHECK_COLUMN(result, 0, {4, 5}));
36 REQUIRE(CHECK_COLUMN(result, 1, {5, 6}));
37 REQUIRE(CHECK_COLUMN(result, 2, {4, 5}));
38 REQUIRE(CHECK_COLUMN(result, 3, {4, 5}));
39 REQUIRE(CHECK_COLUMN(result, 4, {3, 4}));
40 REQUIRE(CHECK_COLUMN(result, 5, {4, 5}));
41
42 // subquery group cols are visible
43 result = con.Query("select sum(x) from (select i as x from test group by i) sq;");
44 REQUIRE(CHECK_COLUMN(result, 0, {12}));
45
46 // subquery group aliases are visible
47 result = con.Query("select sum(x) from (select i+1 as x from test group by x) sq;");
48 REQUIRE(CHECK_COLUMN(result, 0, {15}));
49}
50
51TEST_CASE("Nested table subquery", "[subquery]") {
52 unique_ptr<QueryResult> result;
53 DuckDB db(nullptr);
54 Connection con(db);
55 con.EnableQueryVerification();
56
57 con.Query("CREATE TABLE test (i INTEGER, j INTEGER)");
58 con.Query("INSERT INTO test VALUES (3, 4), (4, 5), (5, 6);");
59
60 result = con.Query("SELECT * FROM (SELECT i, j FROM (SELECT j AS i, i AS j FROM (SELECT j "
61 "AS i, i AS j FROM test) AS a) AS a) AS a, (SELECT i+1 AS r,j FROM "
62 "test) AS b, test WHERE a.i=b.r AND test.j=a.i ORDER BY 1;");
63 REQUIRE(CHECK_COLUMN(result, 0, {4, 5}));
64 REQUIRE(CHECK_COLUMN(result, 1, {5, 6}));
65 REQUIRE(CHECK_COLUMN(result, 2, {4, 5}));
66 REQUIRE(CHECK_COLUMN(result, 3, {4, 5}));
67 REQUIRE(CHECK_COLUMN(result, 4, {3, 4}));
68 REQUIRE(CHECK_COLUMN(result, 5, {4, 5}));
69
70 const int NESTING_LEVELS = 100;
71
72 // 100 nesting levels
73 string query = "SELECT i FROM ";
74 for (size_t i = 0; i < NESTING_LEVELS; i++) {
75 query += "(SELECT i + 1 AS i FROM ";
76 }
77 query += "test";
78 for (size_t i = 0; i < NESTING_LEVELS; i++) {
79 query += ") AS a";
80 }
81 query += ";";
82 result = con.Query(query.c_str());
83 REQUIRE(CHECK_COLUMN(result, 0, {3 + NESTING_LEVELS, 4 + NESTING_LEVELS, 5 + NESTING_LEVELS}));
84}
85