Repository navigation
No running through SubQueries #69
Description
I have the problem that with SubQueries, the traversing stops.
var ast = new Parser().ParseSql(sql, new PostgreSqlDialect(), new ParserOptions()
{
RecursionLimit = 20000
});
var visitor = new PostgresVisitor(allowedType);
ast.Visit(visitor);Here an example sql query:
SELECT *
FROM ds_customers c
LEFT JOIN LATERAL (
SELECT *
FROM ds_orders o
WHERE o.customer_id = c.id
ORDER BY o.order_date DESC
LIMIT 3
) recent_orders ON true;I am trying to collect all the tables called up. I have built a hotfix that checks if there is a SubQuery and runs it again. I can use it for reading. I also have a “ReplaceVisitor” which replaces table names. I can't get that to work. It would be great if the SubQuery could simply continue to be used normally like the main query. Am I doing something wrong here?
public override ControlFlow PreVisitTableFactor(TableFactor relation)
{
// Handle TableFactor.Derived (for LATERAL JOINs and subqueries)
if (relation is TableFactor.Derived derived)
{
Console.WriteLine($"[VISITOR] Found TableFactor.Derived - Lateral: {derived.Lateral}");
if (derived.SubQuery != null)
{
Console.WriteLine($"[VISITOR] Derived has SubQuery - will let base visitor handle it");
Console.WriteLine($"[VISITOR] SubQuery type: {derived.SubQuery.GetType().Name}");
Console.WriteLine($"[VISITOR] SubQuery sql: {derived.SubQuery.ToSql()}");
PostgresVisitor visitor = new PostgresVisitor(AllowedType);
var ast = new Parser().ParseSql(derived.SubQuery.ToSql(), new PostgreSqlDialect(), new ParserOptions()
{
RecursionLimit = 20000
});
ast.Visit(this);
return ControlFlow.Continue;
}
// Let the base visitor handle the traversal
return base.PreVisitTableFactor(relation);
}
if (!(relation is TableFactor.Table))
return base.PreVisitTableFactor(relation);
var table = relation.AsTable();
Console.WriteLine($"[VISITOR] Found TableFactor.Table - Name: {table?.Name}");
if (table != null)
{
// logic for table
}
return base.PreVisitTableFactor(relation);
}Without the derived.SubQuery part, the SubQuery is never run.
Activity
mattfennerom commentedon Aug 14, 2025
We also experienced the same thing.
We found quite a number of paths which don't get visited. Is there a reason for this? or is it just an oversight?
If there is no reason, would you accept a PR to fix it?
178f19eb047f commentedon Aug 28, 2025
Hi 👋, just adding some extra details we found whilst working around this issue that may help others or even help fixing this issue. We're keen to help fix this issue more broadly but want to make sure we take the right approach for this library. We ended up working around the issue for now by implementing our own visitor that visits the all types in the AST with reflection ignoring the
VisitAttributeinstead of manually calling into known types that aren't being visited as we'd expect.Looking briefly into the rust library this project was ported from I'd expect the whole AST to be traversed. They have a test that uses (
Pivot) which is one of the other types we noticed not being fully visited. We also ran a simple query containing a subquery (to useDerived) to see what the rust library did.Details here for the Pivot test
Based off running
VisitorTests.Visitor_Visits_Sql_PartsThe rust test https://github.com/apache/datafusion-sqlparser-rs/blob/main/src/ast/visitor.rs#L871
Results from visitor with a simple query containing a subquery
Query:
SELECT * FROM (SELECT * FROM t1) AS s1C# result
Rust result
Before implementing a new visitor we looked to find all the types that could be failing to traverse deeper into the AST. For this we used the code in
IElement.GetVisitableChildPropertiesand compared it to getting all theIElementproperties on all of theIElementtypes. BothDerivedandPivotappear in the list so we suspect a majority of them have a similar issue.List of types and properties
We also came across
PivotValueSourcewhich only implementsIWriteSqlso even if it did have theVisitattributeVisitChildreninIElementwould skip if since it doesn't implementIElement. So it would be good to confirm if this is intentended or not.Hopefully this helps out 😄
FranciscoRdslm commentedon Oct 17, 2025
Same issue for us, we need to visit all expressions in the SQL and the (Pre/Post)VisitExpression methods are not being called for expressions either in subqueries or function arguments.
Visitor breaking on nesting makes it kind of useless for our use case.