[Bug] When executing a recursive query (WITH RECURSIVE) in Apache Doris, the SQL fails to parse and throws a syntax error.

[babijisa]

Search before asking

• I had searched in the issues and found no similar issues.

Version

Release 4.1.1

What's Wrong?

When executing a recursive query (WITH RECURSIVE) in Apache Doris, the SQL fails to parse and throws a syntax error.

Error message:

SQL Error [1105] [HY000]: errCode = 2, detailMessage =
no viable alternative at input 'WITH RECURSIVE search_tree'(line 1, pos 91)

📋 Steps to Reproduce

1. Create Table
   CREATE TABLE tree
   (
   id INT,
   parent_id INT,
   data VARCHAR(100)
   )
   DUPLICATE KEY (id)
   DISTRIBUTED BY HASH(id) BUCKETS 1
   PROPERTIES ('replication_num' = '1');
2. Insert Data
   INSERT INTO tree VALUES
   (0, NULL, 'ROOT'),
   (1, 0, 'Child_1'),
   (2, 0, 'Child_2'),
   (3, 1, 'Child_1_1');
3. Execute Recursive Query
   WITH RECURSIVE search_tree AS (
   SELECT id, parent_id, data
   FROM tree t
   WHERE t.id = 0
   UNION ALL
   SELECT t.id, t.parent_id, t.data
   FROM tree t, search_tree st
   WHERE t.parent_id = st.id
   )
   SELECT * FROM search_tree ORDER BY id;

What You Expected?

The query should execute successfully and return the hierarchical data:

0 NULL ROOT
1 0 Child_1
2 0 Child_2
3 1 Child_1_1

How to Reproduce?

No response

Anything Else?

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[zclllyybb]

Breakwater-GitHub-Analysis-Slot: slot_81980156cf4b
This content is generated by AI for reference only.

Initial assessment:

The SQL shape in this report is expected to be accepted by Doris 4.1.1 when it reaches the normal Nereids parser path. In the 4.1.1 source, DorisParser.g4 defines CTE parsing as WITH RECURSIVE? aliasQuery ..., and RECURSIVE is a lexer token. There is also a 4.1.1 regression suite, regression-test/suites/rec_cte_p0/rec_cte_from_ck_doc/rec_cte_from_ck_doc.groovy, that contains the same tree table and WITH RECURSIVE search_tree AS (...) SELECT * FROM search_tree ORDER BY id example from this issue.

So the current evidence does not point to a general "Doris 4.1.1 cannot parse WITH RECURSIVE" gap. The reported error is still valid to investigate, but it likely depends on the actual session/build/client path rather than only on the SQL text shown here.

Code-level notes:

• Recursive CTE execution is gated by enable_nereids_distribute_planner; in 4.1.1 it defaults to true, and if disabled the analyzer should raise please set enable_nereids_distribute_planner=true to use RECURSIVE CTE, not the parser error shown here.
• A related multi-FE/proxy recursive-CTE issue was fixed after the 4.1.1 tag by PR [fix](fe) Fix NereidsCoordinator not created in proxy flow due to missing parsedStatement on StatementContext #64363 / branch-4.1 backport branch-4.1: [fix](fe) Fix NereidsCoordinator not created in proxy flow due to missing parsedStatement on StatementContext #64363 #64437, but that issue produced coordinator/planning failures after parsing, not a no viable alternative at input 'WITH RECURSIVE ...' parser failure. It is only directly relevant if this query was sent through a follower-FE forwarding path.
• The error position shown in the report, line 1, pos 91, is suspicious for the standalone query because WITH would be at the start of the statement. That suggests there may be preceding SQL text, a client-side multi-statement handling issue, a SQL rewrite/conversion layer, or a build/session mismatch not included in the issue body.

Suggested maintainer/user follow-up:

1. Please provide the exact SELECT VERSION() result and, if possible, the FE build hash.
2. Please run and share:

   SHOW VARIABLES LIKE 'enable_nereids_distribute_planner';
   SHOW VARIABLES LIKE 'sql_dialect';

3. Please confirm whether the failing query was submitted as a single standalone statement, or in the same client request/script as the DDL/INSERT statements.
4. Please confirm whether the cluster has multiple FEs and whether the request was sent to a follower FE.
5. Please attach the FE log around the failed query, including the full SQL text received by FE if available.

For a quick workaround check, run the recursive query alone after:

SET enable_nereids_distribute_planner = true;

If it still fails as a standalone query on an official 4.1.1 build, then this should be treated as a parser-routing/session bug, because the 4.1.1 grammar and regression cases already cover this syntax.

[babijisa]

I have followed the official documentation example (https://doris.apache.org/zh-CN/docs/4.x/query-data/cte?_highlight=search_tree#%E7%A4%BA%E4%BE%8B) and executed the query exactly as described. However, I am still encountering the same error as previously reported.

Please find the relevant screenshot attached below for reference.