Repository navigation
[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
Description
AI disclosure: this issue was prepared with AI coding agents, reviewed and revised line by line by me.
Failing SQL Feature:
TablesNamesFinder silently misses tables that only appear inside SELECT clauses it never traverses. SelectVisitorAdapter covers most of these clauses, TablesNamesFinder does not. All shapes below parse fine on master e7167cc7 (5.4-SNAPSHOT) and return an incomplete table set:
| Clause | Example | Returned | Missed |
|---|---|---|---|
| INTO target | SELECT * INTO t1 FROM t2 |
[t2] |
t1 |
| INTO TEMP | SELECT * FROM t2 INTO TEMP t1 |
[t2] |
t1 |
| GROUP BY | SELECT a FROM t GROUP BY a, (SELECT b FROM u) |
[t] |
u |
| GROUPING SETS | SELECT a FROM t GROUP BY GROUPING SETS ((a, (SELECT b FROM u)), (c)) |
[t] |
u |
| QUALIFY | SELECT * FROM t QUALIFY (SELECT b FROM u) = 1 |
[t] |
u |
| WINDOW | SELECT * FROM t WINDOW w AS (PARTITION BY (SELECT b FROM u)) |
[t] |
u |
| ORDER BY | SELECT a FROM t ORDER BY a, (SELECT b FROM u) |
[t] |
u |
| LIMIT / LIMIT BY / OFFSET / FETCH | SELECT a FROM t LIMIT (SELECT b FROM u) etc. |
[t] |
u |
| DISTINCT ON | SELECT DISTINCT ON ((SELECT b FROM u)) a FROM t |
[t] |
u |
| LATERAL VIEW | SELECT * FROM t LATERAL VIEW explode(array((SELECT b FROM u))) v AS x |
[t] |
u |
| SETTINGS | SELECT a FROM t SETTINGS x = (SELECT b FROM u) |
[t] |
u |
| PIVOT XML IN subquery | SELECT * FROM t PIVOT XML (SUM(x) FOR y IN (SELECT z FROM u)) |
[t] |
u |
| ParenthesedSelect top clauses | (SELECT a FROM t) ORDER BY (SELECT b FROM u) LIMIT (SELECT c FROM w) |
[t] |
u, w |
| SetOperationList top clauses | SELECT a FROM t UNION SELECT b FROM u ORDER BY (SELECT c FROM w) LIMIT (SELECT d FROM v) |
[t, u] |
w, v |
| VALUES top clauses | VALUES (1) ORDER BY (SELECT a FROM t) LIMIT (SELECT b FROM u) |
[] |
t, u |
| DELETE / UPDATE ORDER BY + LIMIT | DELETE FROM t WHERE a = 1 ORDER BY (SELECT b FROM u) LIMIT 2 |
[t] |
u |
Control, still correct today: SELECT * FROM t JOIN j ON t.id = j.id WHERE x IN (SELECT y FROM s) -> [s, t, j].
SQL Example:
// JSqlParser 5.4-SNAPSHOT (master e7167cc7)
TablesNamesFinder.findTables("SELECT a FROM t GROUP BY a, (SELECT b FROM u)");
// actual: [t]
// expected: [t, u]Software Information:
- JSqlParser version: 5.4-SNAPSHOT (master e7167cc)
- Database: all (parser-level, dialect-independent)
- Also verified on the 5.3 release jar: the shapes 5.3 parses (INTO, GROUP BY, ORDER BY subqueries) miss the same tables;
LIMIT (SELECT ...)does not parse in 5.3
Tips:
Root cause: visit(PlainSelect), visit(ParenthesedSelect), visit(SetOperationList), visit(Values), visit(Delete), visit(Update) and visit(Table) traverse only part of the Expression- and Table-bearing children; the Select base clauses (ORDER BY, LIMIT, LIMIT BY, OFFSET, FETCH) are never traversed, and PivotVisitor is not implemented, so PIVOT / UNPIVOT / PIVOT XML contents are unreachable through dispatch.
Fix direction: reuse the ExpressionVisitor / FromItemVisitor default helpers (visitOrderBy, visitLimit, visit(GroupByElement), visitUpdateSets, visitPreferringClause, visitTables, ...) exactly as SelectVisitorAdapter does, and implement PivotVisitor (same mechanism as MergeOperationVisitor / PipeOperatorVisitor in #2479).
Continues the family fixed by #2478 / #2479 (piped queries, DML side clauses, analytic clauses).
Activity
[-][BUG] JSQLParser 5.4-SNAPSHOT : all RDBMS : TablesNamesFinder silently misses tables in many SELECT clauses (INTO, GROUP BY, QUALIFY, WINDOW, ORDER BY, LIMIT, DISTINCT ON, LATERAL VIEW, PIVOT XML, SETTINGS)[/-][+][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)[/+]