AST path queries and typed traversal (follow-up to #2620)

[minleejae]

Continuing the design discussion from PR #2620, following manticore-projects' request to move it to Discussions. The original PR preserves the implementation and full comment history. The API examples below are proposals, not an agreed public API.

The PR proposed CCJSqlParserUtil.parseOrderByElements(String) to turn standalone sort specifications into List<OrderByElement>, so consumers could inspect expressions and sort options without wrapping the fragment in a SELECT. We put that work on hold to explore a general AST querying mechanism, especially for deeply nested queries.

manticore-projects shared a prototype that retrieves the WHERE expression of the second UNION branch in the third WITH item:

Expression w = AstPath.get(root,
    "/withItemsList[2]/select/selects[1]/where", Expression.class);

The prototype also supports selection by CTE name (/withItemsList[name='w2']/select/selects[1]/where) and descendant/type selection (//SetOperationList/selects[1]/where). The original AstPath.zip is available for reference.

The main design points raised so far are:

• Path stability. Positional selectors depend on ordering, while name predicates and descendant/type matching tolerate some structural changes. Public AST property changes could still break paths. fudianchn suggested making node types, field predicates and descendant relationships the primary semantics, with indices as optional narrowing operators.
• Validation. The prototype discussion raised the lack of a schema for checking paths, with JAXB-generated schemas as one possibility. An alternative proposal is to compile paths into typed matchers and reject unknown node/property names at compilation time. The validation approach and compatibility guarantees remain open.
• Traversal and sibling relationships. The matcher layer could expose a typed API such as findAll(PlainSelect.class, predicate), with paths compiling to that same layer. Default recursive traversal was proposed to reduce omissions caused by individual visitors failing to descend, such as those reported in [BUG] JSQLParser 5.4-SNAPSHOT : all RDBMS : TablesNamesFinder silently misses tables in many SELECT and DML clauses (INTO, GROUP BY, QUALIFY, WINDOW, ORDER BY, LIMIT, DISTINCT ON, LATERAL VIEW, PIVOT XML, SETTINGS) #2516. Following/preceding sibling selection is another capability to design; it was proposed as a matcher extension without changing the SQL grammar.
• Structural search and name resolution. manticore-projects distinguished structural table matches in JSQLParser from resolution against actual database metadata in JSQLTranspiler. fudianchn clarified that statement-local CTE binding was the intended scope-aware behavior, without guaranteeing that remaining references identify actual database tables. The traversal/scope contract still needs definition.
• AST model improvements. The earlier exchange distinguished generic querying from adding structured AST fields. Column attributes, temporal expressions, DROP INDEX owner tables and base-type access were examples raised in the PR. Those improvements can be handled separately; this discussion can focus on selection and traversal.

There is also an earlier XPath discussion (#1626) with JSQLFormatter examples that may inform this work.

Which minimal traversal/matching API and path compatibility contract should we settle first? In particular, should we develop the prototype as a path language over a shared typed traversal layer?