Add additional subquery tests to slt tests

[alamb]

Is your feature request related to a problem or challenge?

• Part of [EPIC] More Subquery support #5483

We are adding support (slowly) for more sophisticated types of subqueries. Part of that is to ensure we have sufficient test coverage.

Describe the solution you'd like

I would like to make sure we have adequate coverage for subqueries (perhaps by porting) the tests from the duckdb repo to the Datafusion repo

I expect some non trivial number of tests to fail at first

It is important to first evaluate the existing subquery coverage rather than just add many new tests

Describe alternatives you've considered

Here are some examples of correlated subquery tests from the duckdb repo

• https://github.com/duckdb/duckdb/blob/main/test/sql/subquery/scalar/test_nested_correlated_subquery.test_slow
• https://github.com/duckdb/duckdb/blob/main/test/sql/subquery/scalar/test_complex_nested_correlated_subquery.test
• https://github.com/duckdb/duckdb/blob/main/test/sql/subquery/scalar/nested_subquery_window.test
• https://github.com/duckdb/duckdb/blob/main/test/sql/subquery/table/test_nested_table_subquery.test_slow
• https://github.com/duckdb/duckdb/blob/main/test/sql/subquery/complex/nested_correlated_list.test_slow
• https://github.com/duckdb/duckdb/blob/main/test/sql/subquery/complex/nested_unnest_subquery.test
• https://github.com/duckdb/duckdb/blob/main/test/sql/subquery/scalar/test_scalar_subquery.test
• https://github.com/duckdb/duckdb/blob/main/test/sql/subquery/scalar/test_scalar_subquery_cte.test

I suggest for each of these tests:

1. Porting them over to the datafusion repo (with a link back to their origin)
2. Any tests that now fail to run, add in comments the expected results (from duckdb)

For example, if this query doesn't work

query II
SELECT i, (SELECT (SELECT (SELECT (SELECT 42+i1.i)++i1.i)+42+i1.i)+42+i1.i) AS j FROM integers i1 ORDER BY i;
----
NULL	NULL
1	130
2	134
3	138

Let's add something like

# Expected Results
# reference https://github.com/duckdb/duckdb/blob/324ff8c45f43478e09b6168f75a291b8d43ec3ee/test/sql/subquery/scalar/test_nested_correlated_subquery.test_slow#L29-L32
# ----
# NULL	NULL
# 1	130
# 2	134
# 3	138
errror query II
SELECT i, (SELECT (SELECT (SELECT (SELECT 42+i1.i)++i1.i)+42+i1.i)+42+i1.i) AS j FROM integers i1 ORDER BY i;
----
Can't plan query...

Additional context

No response

[alamb]

BTW i can also copy/paste the above description into a tool like claude or codex. If someone else wants to take this ticket that would be great, but a key part will be to verify the work of the AI tool before submitting a PR

[Tushar7012]

Hey @alamb ,
From what I understand, this issue is mainly about improving test coverage rather than adding new engine functionality.

The goal seems to be to strengthen the SQLLogicTest (SLT) suite by adding additional subquery test cases, especially around scenarios that may be under-covered today (such as nested subqueries, scalar subqueries, and other edge cases).

Adding these tests should help lock in existing behavior and prevent regressions as subquery handling continues to evolve. I’m happy to work on identifying missing cases and contributing additional SLT tests in the appropriate test files.

[alamb]

Thanks @Tushar7012 -- you have it right -- I view this as a step towards more fully featured subquery functionality, and the first thing to do is to get some more test coverage in place so we know the current state of where we are

[Tushar7012]

Thanks for the confirmation! Agreed — the goal here is to first expand test coverage to clearly understand the current state of subquery support. I’ll focus on getting the additional sqllogictest cases in place and validated locally before proposing anything further. This should give us a solid baseline to build on.