Repository navigation
[BUG] JSqlParser 5.4: ParseException on recursive CTE selecting a NUMBER column #2880
Description
Failing SQL Feature:
JSqlParser 5.4 fails to parse the following WITH RECURSIVE query. The CTE selects issue columns from T_ISSUE, then uses UNION ALL and a self-reference to retrieve child rows.
The reported error is at a comma in the select list, immediately after the unqualified column name NUMBER. It expects a numeric or string literal at that point. This may indicate that NUMBER is being interpreted as part of an expression instead of a column identifier, but I have not confirmed the root cause.
SQL Example:
WITH RECURSIVE
cte_name AS (
SELECT
ID,
PARENT_ID,
NUMBER,
TITLE,
MODEL_TYPE_ID,
TYPE_CLASSIFY,
PRIORITY,
STATE,
DEMAND_CLASSIFY,
PROJECT_ID,
CREATED_USER,
CREATED_TIME
FROM
T_ISSUE
WHERE
ID IN ('xxx')
UNION ALL
SELECT
l.ID,
l.PARENT_ID,
l.NUMBER,
l.TITLE,
l.MODEL_TYPE_ID,
l.TYPE_CLASSIFY,
l.PRIORITY,
l.STATE,
l.DEMAND_CLASSIFY,
l.PROJECT_ID,
l.CREATED_USER,
l.CREATED_TIME
FROM
T_ISSUE l
JOIN cte_name r ON l.PARENT_ID = r.ID
)
SELECT
*
FROM
cte_nameActual behavior:
Parsing fails with the following exception (line and column refer to the original input formatting):
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered: <K_COMMA> / ",", at line 7, column 25, in lexical state DEFAULT.
Was expecting one of these terminals within expansion starting at 11417:9:
<S_DOUBLE> (inside 11417:9) ...
<S_LONG> (inside 11417:9) ...
<S_CHAR_LITERAL> (inside 11417:9) ...
Expected behavior:
The query should parse successfully, treating NUMBER and l.NUMBER as column references in their respective select lists.
Reproduction:
Save the SQL above as query.sql and parse it with JSqlParser 5.4:
import java.nio.file.Files;
import java.nio.file.Path;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
String sql = Files.readString(Path.of("query.sql"));
CCJSqlParserUtil.parse(sql);Software Information:
- JSqlParser version: 5.4
Could you check why this query is rejected in 5.4?
Activity
fudianchn commentedon Nov 2, 2026
I reproduced this on 5.4. A minimal reproducer is
SELECT NUMBER FROM T_ISSUE, so it is unrelated to the recursive CTE. The parser incorrectly treatsNUMBERas the start of a typed literal and expects a numeric or string literal after it.This was fixed in #2644. I tested your complete SQL against master at
7123d25a: it parses and round-trips successfully, withNUMBERin both SELECT branches represented as column references.You can use a build from the current master branch, which includes the fix. If you need to stay on 5.4, qualifying the first unqualified
NUMBERasT_ISSUE.NUMBERalso works; I verified that the complete SQL parses with this change.