Repository navigation
Add additional subquery tests to slt tests #20240
Description
Is your feature request related to a problem or challenge?
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:
- Porting them over to the datafusion repo (with a link back to their origin)
- 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
Activity
alamb commentedon Feb 9, 2026
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 commentedon Feb 9, 2026
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 commentedon Feb 9, 2026
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 commentedon Feb 9, 2026
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.
Add additional subquery sqllogictest coverage
Add additional subquery sqllogictest coverage