jonahgao opened a new issue, #9804:
URL: https://github.com/apache/arrow-datafusion/issues/9804
### Describe the bug
DataFusion did not recognize this scenario, treating it uniformly as
recursive CTEs, which resulted in 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.
```sql
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:
```sh
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.
```sh
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](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#recursive_cte)
common table expressions (CTEs). If this keyword is present, you can use both
recursive and
[non-recursive](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#simple_cte)
CTEs.
- [Changes the
visibility](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#cte_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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
