nathanb9 opened a new issue, #25893:
URL: https://github.com/apache/datafusion/issues/25893
### Describe the bug
A recursive CTE silently casts the recursive term's output to the static
term's types, even when the cast loses data (for example `Float64` → `Int64`).
The query returns wrong values. If the stop condition depends on the lost part,
the query never terminates.
`LogicalPlanBuilder::to_recursive_query` calls
`coerce_plan_expr_for_schema(recursive_term, static_schema)`, which casts to
any castable type (added for #9794).
### To Reproduce
Tested on `main` (1d9be2e), default dialect, `datafusion-cli`:
```sql
-- Wrong result: expected n = 1, 1.5, 2, 2.5
WITH RECURSIVE r AS (
SELECT 1 AS n, 0 AS i
UNION ALL
SELECT n + 0.5, i + 1 FROM r WHERE i < 3
) SELECT n, arrow_typeof(n) FROM r ORDER BY i;
-- 1 Int64 / 1 Int64 / 1 Int64 / 1 Int64
```
```sql
-- Never terminates: n is truncated from 1.5 back to 1, so n < 2 stays true
WITH RECURSIVE r AS (
SELECT 1 AS n
UNION ALL
SELECT n + 0.5 FROM r WHERE n < 2
) SELECT count(*) FROM r;
```
`EXPLAIN` shows the recursive term as `CAST(CAST(n AS Float64) + 0.5 AS
Int64)`.
### Expected behavior
An error at planning time, as PostgreSQL does:
```
ERROR: recursive query "r" column 1 has type integer in non-recursive term
but type numeric overall
HINT: Cast the output of the non-recursive term to the correct type.
```
### Additional context
Suggested fix: in `to_recursive_query`, return a planning error when the
cast from the recursive type to the static type can lose data — float/decimal →
integer, and changes of type category such as `Utf8` → `Int64`. Keep the
current cast for integer → integer so the existing #9794 test (`a::bigint + 2`
into `Int32`) still passes. The error message should tell the user to cast the
non-recursive term.
Alternative: widen both terms to their common type, as `UNION` does. That
requires planning the recursive term again against the widened work-table
schema.
--
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]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
