`WITH RECURSIVE` clause may define non-recursive CTEs

[jonahgao]

Describe the bug

DataFusion did not recognize this scenario and treated them all as recursive CTEs, which led to incorrect results.

This is a reasonable usage. Users can define multiple CTEs using a WITH clause, one of them might be recursive, while others are not.

WITH RECURSIVE
non_recursive_cte AS (
  SELECT 1
),
recursive_cte AS (
  SELECT 1 AS a UNION ALL SELECT a+2 FROM recursive_cte WHERE a < 3
)
SELECT * FROM non_recursive_cte, recursive_cte;

To Reproduce

Run the following query in CLI:

DataFusion CLI v36.0.0
❯ WITH RECURSIVE cte AS (
    SELECT a FROM (VALUES(1)) AS t(a) WHERE a > 2
    UNION ALL
    SELECT 2
) SELECT * FROM cte;
0 rows in set. Query took 0.016 seconds.

Expected behavior

The result should be similar to PostgreSQL, with one row instead of zero rows.

psql=> WITH RECURSIVE cte AS (
    SELECT a FROM (VALUES(1)) AS t(a) WHERE a > 2
    UNION ALL
    SELECT 2
) SELECT * FROM cte;
 a
---
 2
(1 row)

Additional context

The following is documentation from BigQuery. https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#recursive_keyword

A WITH clause can optionally include the RECURSIVE keyword, which does two things:

• Enables recursion in the WITH clause. If this keyword is not present, you can only include non-recursive common table expressions (CTEs). If this keyword is present, you can use both recursive and non-recursive CTEs.
• Changes the visibility of CTEs in the WITH clause. If this keyword is not present, a CTE is only visible to CTEs defined after it in the WITH clause. If this keyword is present, a CTE is visible to all CTEs in the WITH clause where it was defined.

[jonahgao]

take