[I] `WITH RECURSIVE` clause may define non-recursive CTEs [arrow-datafusion]

["jonahgao (via GitHub)" <gi...@apache.org>]

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: github-unsubscribe@arrow.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] `WITH RECURSIVE` clause may define non-recursive CTEs [arrow-datafusion]

["alamb (via GitHub)" <gi...@apache.org>]

alamb closed issue #9804: `WITH RECURSIVE` clause may define non-recursive CTEs
URL: https://github.com/apache/arrow-datafusion/issues/9804


-- 
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: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] `WITH RECURSIVE` clause may define non-recursive CTEs [arrow-datafusion]

["jonahgao (via GitHub)" <gi...@apache.org>]

jonahgao commented on issue #9804:
URL: https://github.com/apache/arrow-datafusion/issues/9804#issuecomment-2019280069

   take


-- 
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: github-unsubscribe@arrow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org