You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "cetra3 (via GitHub)" <gi...@apache.org> on 2026/05/25 04:50:41 UTC
[I] PREPARE placeholder type coercion fails when plain queries work fine [datafusion]
cetra3 opened a new issue, #22506:
URL: https://github.com/apache/datafusion/issues/22506
### Describe the bug
Discovered this one while playing around with prepared statements. The type coercion of placeholders is different between a `PREPARE` statement and the normal query.
I.e,
The standard select query:
```sql
SELECT $1 = 'x', $1 = s FROM t
```
And
The prepared statement:
```sql
PREPARE p AS SELECT $1 = 'x', $1 = s FROM t
```
Both diverge in their placeholder resolution, and can fail in strange ways
### To Reproduce
This only appears when the types are potentially different. I.e, I saw it when the type on the table was `Utf8View` but literal strings are `Utf8`.
I did manage to replicated it using the datafusion cli.
I.e, first create a table definition
```sql
CREATE TABLE t(s VARCHAR) AS VALUES ('a');
```
Do the raw query (works fine):
```sql
SELECT $1 = 'x', $1 = s FROM t;
```
Prepare this query:
```sql
PREPARE p AS SELECT $1 = 'x', $1 = s FROM t;
```
This will error with:
```
Error during planning: Expected parameter of type Utf8, got Utf8View: Conflicting types for id $1
```
### Expected behavior
I'd expect this to work fine
### Additional context
_No response_
--
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@datafusion.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] PREPARE placeholder type coercion fails when plain queries work fine [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #22506:
URL: https://github.com/apache/datafusion/issues/22506#issuecomment-4652607536
> I think we should go for a postgres like behaviour here, i.e. fail at prepare time.
As long as people won't experience a regression (as in queries that used to work start failing) following Postgres behavior seems reasonable to me
--
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@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] PREPARE placeholder type coercion fails when plain queries work fine [datafusion]
Posted by "truffle-dev (via GitHub)" <gi...@apache.org>.
truffle-dev commented on issue #22506:
URL: https://github.com/apache/datafusion/issues/22506#issuecomment-4653545114
> I think it is also against the API policy we have
Fair point. The API health doc names value-coercion explicitly and one query that used to work and now fails at PREPARE is the breaking change the doc draws the line at. The "narrow surface" framing was the wrong frame.
On the cast-on-substitute reframe: cetra3's case fails at PREPARE before any value is supplied, so I want to make sure I'm reading your shape right. Is it roughly (a) keep strict-equality in `get_parameter_fields` but store per-occurrence expected types so each site can cast its bound value independently at EXECUTE, or (b) something at PREPARE I'm not seeing? Per-occurrence types at EXECUTE handles cetra3's Utf8/Utf8View cleanly via the lossless cast, the cross-family case surfaces as a runtime cast error when the value doesn't fit either side, and PREPARE never fails for queries that PREPARE today.
--
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@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] PREPARE placeholder type coercion fails when plain queries work fine [datafusion]
Posted by "RatulDawar (via GitHub)" <gi...@apache.org>.
RatulDawar commented on issue #22506:
URL: https://github.com/apache/datafusion/issues/22506#issuecomment-4536267764
@alamb would it make sense to use comparison_coercion in get_parameter_fields() when merging placeholder types, instead of strict equality? That would unify cases like Utf8 + Utf8View → Utf8View, matching what normal queries already do at comparison sites, while still erroring on truly incompatible types.
--
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@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] PREPARE placeholder type coercion fails when plain queries work fine [datafusion]
Posted by "RatulDawar (via GitHub)" <gi...@apache.org>.
RatulDawar commented on issue #22506:
URL: https://github.com/apache/datafusion/issues/22506#issuecomment-4642472459
@alamb what do you think about this ?
--
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@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] PREPARE placeholder type coercion fails when plain queries work fine [datafusion]
Posted by "truffle-dev (via GitHub)" <gi...@apache.org>.
truffle-dev commented on issue #22506:
URL: https://github.com/apache/datafusion/issues/22506#issuecomment-4652996998
> As long as people won't experience a regression (as in queries that used to work start failing) following Postgres behavior seems reasonable to me
The regression surface is narrow but specific: queries where the same `$N` appears in both a string-context and a numeric-context, or any other incompatible-family pair. Under `comparison_coercion` today those PREPARE successfully because `string_numeric_coercion` returns `Some(Int32)` for `(Int32, Utf8)` (the second-to-last `or_else` arm in `comparison_coercion`). Under Postgres-shape they would PREPARE-fail.
Concrete shape that would change behavior:
```sql
CREATE TABLE users(name VARCHAR, age INT);
PREPARE q AS SELECT $1 = name, $1 = age FROM users;
-- comparison_coercion path: Some(Int32) → PREPARE OK, may fail at EXECUTE on bad rows
-- Postgres-shape path: operator-not-found at PREPARE
```
The surface should be rare in practice because parameter-using clients (JDBC, libpq drivers, sqlx etc.) typically generate queries with one consistent type per placeholder. One useful sanity check before flipping the policy: grep `PREPARE` across `datafusion/sqllogictest/test_files` for cross-family placeholder uses; if none of those break under the stricter merge, the regression risk is contained to user code outside the test suite. The string + temporal pair (`string_temporal_coercion`) is the other arm worth checking since it has the same permissive shape.
--
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@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] PREPARE placeholder type coercion fails when plain queries work fine [datafusion]
Posted by "cetra3 (via GitHub)" <gi...@apache.org>.
cetra3 commented on issue #22506:
URL: https://github.com/apache/datafusion/issues/22506#issuecomment-4560871739
The reason this fails with prepared statements is that this is failing in `get_parameter_fields` which tries to create a hashmap:
https://github.com/apache/datafusion/blob/3f501f4b9e6bf8616556fbf6c832da7e364680a4/datafusion/expr/src/logical_plan/plan.rs#L1622-L1654
The "normal" code doesn't need to create a hashmap and so I think treats each param expr instance as separate. I.e, you can have one be `Utf8` and one be `Utf8View`
Claude had some code that would "widen" the type in the hash map with `type_union_resolution` but I don't know if that is the right approach here (maybe it is!):
```rust
fn widen_parameter_field(prev: &FieldRef, new: &FieldRef) -> Option<FieldRef> {
if !prev.metadata().is_empty() || !new.metadata().is_empty() {
return None;
}
let common =
type_union_resolution(&[prev.data_type().clone(), new.data_type().clone()])?;
Some(Arc::new(Field::new(
prev.name(),
common,
prev.is_nullable() || new.is_nullable(),
)))
```
--
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@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] PREPARE placeholder type coercion fails when plain queries work fine [datafusion]
Posted by "truffle-dev (via GitHub)" <gi...@apache.org>.
truffle-dev commented on issue #22506:
URL: https://github.com/apache/datafusion/issues/22506#issuecomment-4582934307
Did some empirical poking at Postgres 16.13 and DuckDB v1.4.1 since the question was what other systems do.
### Postgres: first-occurrence anchors, implicit cast everywhere else
Postgres walks the AST, picks the type of `$N`'s first concrete-context occurrence, then requires every other occurrence to find an implicit cast or operator that lets the anchored type interact with the other side. If no such operator exists, PREPARE fails at planning time.
**In-family widening (string types):**
```sql
PREPARE qA AS SELECT $1 = s /* varchar */, $1 = x /* text */ FROM t, t_text;
PREPARE qB AS SELECT $1 = x /* text */, $1 = s /* varchar */ FROM t_text, t;
SELECT parameter_types FROM pg_prepared_statements WHERE name IN ('qA','qB');
-- both: {text}
```
varchar and text are binary-coercible, so both orderings resolve to `{text}`. Looks like "widening" but it's anchor + cast where the cast is no-op.
**Numeric widening, order-dependent:**
```sql
PREPARE qC AS SELECT $1 = a.n /* int */, $1 = b.n /* bigint */; -- {integer}
PREPARE qD AS SELECT $1 = b.n /* bigint */, $1 = a.n /* int */; -- {bigint}
```
First occurrence anchors; second occurrence gets the implicit int/bigint cast.
**Cross-family conflict (RatulDawar's example):**
```sql
PREPARE q AS SELECT $1 = s FROM t JOIN t2 ON t2.id = $1;
-- ERROR: operator does not exist: integer = character varying
```
Anchors to varchar from `$1 = s`, then `t2.id = $1` needs `integer = character varying` operator. None exists. PREPARE fails at parse time, not EXECUTE.
**Pure UNKNOWN defaults to text:**
```sql
PREPARE q AS SELECT $1; -- {text}
PREPARE q AS SELECT $1 = NULL FROM t; -- {text}
```
### DuckDB: defer the conflict to EXECUTE
```sql
PREPARE q AS SELECT $1 = s /* varchar */ FROM t JOIN t2 ON t2.id = $1;
EXECUTE q(1);
-- Conversion Error: Could not convert string 'a' to INT32 when casting from source column s
```
DuckDB picks int at PREPARE time and only errors when casting varchar row data to int. Less predictable for the user, since PREPARE looks fine and the failure mode depends on the row contents.
### How this maps onto the issue
@cetra3's failing case (`$1 = 'x'` Utf8 and `$1 = s` Utf8View) parallels postgres's varchar+text test. Postgres resolves to the wider string type for both orderings, which is the `comparison_coercion`-equivalent result. @RatulDawar's instinct is correct for the in-family case.
@RatulDawar's conflict example (`$1 = s` varchar joined with `t2.id = $1` int) is the cross-family case. Postgres errors at PREPARE. DuckDB defers it to EXECUTE. Either is defensible. Which one datafusion picks depends on whether you want the same coercion rules at the placeholder site as at every other binary-comparison site (see the recommendation below).
### Subtlety on the explicit-cast escape hatch
One thing worth knowing when documenting the user-side workaround: in postgres, casting only the parameter is not sufficient when the column type itself is incompatible.
```sql
PREPARE q AS SELECT $1::int = s FROM t JOIN t2 ON t2.id = $1::int;
-- ERROR: operator does not exist: integer = character varying
```
`s` is still varchar and `int = varchar` has no implicit operator, so the cast on `$1` alone does not save the comparison. Users have to either cast the column side too (`s::int`) or cast `t2.id` to varchar so the parameter can stay in the varchar family. Worth surfacing in whatever error hint datafusion emits if it adopts the error-at-plan model.
### Suggested shape for the fix
For the immediate case in this issue, replacing the strict-equality merge in [`get_parameter_fields`](https://github.com/apache/datafusion/blob/2e7b8e1b2d3cd800823b3813f2f7efdde0d21af8/datafusion/expr/src/logical_plan/plan.rs#L1626-L1658) with [`comparison_coercion`](https://github.com/apache/datafusion/blob/2e7b8e1b2d3cd800823b3813f2f7efdde0d21af8/datafusion/expr-common/src/type_coercion/binary.rs#L924-L941) looks like the smallest change that fixes cetra3's example and stays consistent with how datafusion treats binary-comparison contexts elsewhere.
That gets the Utf8/Utf8View widening for free (via `string_coercion`), the decimal/integer widening for free (via `binary_numeric_coercion`), and degrades to `None` only when no coercion path exists in either direction.
The RatulDawar concern about Int + Utf8/Utf8View is worth pulling apart from the placeholder issue. Today `comparison_coercion` is permissive about string-to-numeric pairs (the [`string_numeric_coercion` arm](https://github.com/apache/datafusion/blob/2e7b8e1b2d3cd800823b3813f2f7efdde0d21af8/datafusion/expr-common/src/type_coercion/binary.rs#L936)), so `comparison_coercion(Int32, Utf8View)` returns `Some(Int32)` rather than `None`. Using it for placeholder merge therefore gives DuckDB-shaped behavior in that case: PREPARE succeeds, then EXECUTE either succeeds or fails depending on whether row data parses as Int32. If the team wants postgres-shaped errors at PREPARE for cross-family conflicts, that is a separate question about `comparison_coercion`'s string-to-numeric policy across the whole project, not just at the placeholder site.
So as a layered call: ship the `comparison_coercion` swap to unblock the in-family case in this issue, and treat the strictness question as a follow-up that probably wants a wider RFC since it touches every binary-comparison site, not just placeholders.
For the "$N has zero concrete contexts" tail case (e.g. `SELECT $1;`), postgres defaults to text. Defaulting to `Utf8` would match.
--
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@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] PREPARE placeholder type coercion fails when plain queries work fine [datafusion]
Posted by "RatulDawar (via GitHub)" <gi...@apache.org>.
RatulDawar commented on issue #22506:
URL: https://github.com/apache/datafusion/issues/22506#issuecomment-4567358693
I think that for Utf8 and Utf8View a clear merge winner is Utf8View but for cases like Int32 and Utf8/Utf8View we should consider returnning an error instead.
Because cases like this:
```
CREATE TABLE t(s VARCHAR) AS VALUES ('a');
CREATE TABLE t2(id INT) AS VALUES (1);
SELECT $1 = s FROM t JOIN t2 ON t2.id = $1;
```
Not single type Int32 or Utf8View would be good.
Let me know what are your thoughts @cetra3
--
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@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] PREPARE placeholder type coercion fails when plain queries work fine [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #22506:
URL: https://github.com/apache/datafusion/issues/22506#issuecomment-4556933958
> [@alamb](https://github.com/alamb) would it make sense to use comparison_coercion in get_parameter_fields() when merging placeholder types, instead of strict equality? That would unify cases like Utf8 + Utf8View → Utf8View, matching what normal queries already do at comparison sites, while still erroring on truly incompatible types.
Yes @RatulDawar -- applying the same coercion logic sounds right to me
I am not sure if `comparison_coercion` is the right function though -- I vaguely remember that there was special code that tried to determine the type of a placeholder (which isn't known, and isn't the same as type coercsion) -- it might be good to investigate why the behavior is different in the cases above before trying to code up some fix
--
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@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] PREPARE placeholder type coercion fails when plain queries work fine [datafusion]
Posted by "Niketion (via GitHub)" <gi...@apache.org>.
Niketion commented on issue #22506:
URL: https://github.com/apache/datafusion/issues/22506#issuecomment-5395277951
I traced the execution path a bit further. execute_prepared already casts the supplied scalar once to the type stored in PreparedPlan, while replace_params_with_values ignores each placeholder occurrence’s expected type.
That makes per-occurrence casting a viable alternative to merging everything through comparison_coercion, which fixes Utf8/Utf8View but still coerces string/numeric cases to the numeric type.
Would you prefer the minimal comparison_coercion fix, or per-occurrence casting?
I can add tests for both occurrence orders, string/numeric cases, metadata, and untyped placeholders.
@RatulDawar, are you still working on this, or would you be happy for me to take it over?
--
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@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] PREPARE placeholder type coercion fails when plain queries work fine [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #22506:
URL: https://github.com/apache/datafusion/issues/22506#issuecomment-4653070010
> The surface should be rare in practice because parameter-using clients (JDBC, libpq drivers, sqlx etc.) typically generate queries with one consistent type per placeholder.
Well, even if they are rare, I think anyone affected would not be happy.
I think it is also against the API policy we have https://datafusion.apache.org/contributor-guide/api-health.html#what-is-the-public-sql-api-and-what-is-a-breaking-sql-change
I think we need to figure out what we are solving here
For example, the error @cetra3 reports above doesn't seem to really be about parameter resolution.
> Error during planning: Expected parameter of type Utf8, got Utf8View: Conflicting types for id $1
It seems to me like the issue is that the parameter value supplied (Utf8) wasn't the same as expected (Utf8View), even though there is a lossless cast that could convert the types.
perhaps we just need a call to `ScalarValue::cast_to` in the right place when substituting parameters 🤔
--
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@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] PREPARE placeholder type coercion fails when plain queries work fine [datafusion]
Posted by "RatulDawar (via GitHub)" <gi...@apache.org>.
RatulDawar commented on issue #22506:
URL: https://github.com/apache/datafusion/issues/22506#issuecomment-4536069476
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@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] PREPARE placeholder type coercion fails when plain queries work fine [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #22506:
URL: https://github.com/apache/datafusion/issues/22506#issuecomment-4579533419
I think we should research what other systems do in this case and follow suite (like what does postgres do?)
The user can always add expicit casts and it is also possible I think to provide the types of the bind parameters expclitiy 🤔
```sql
SELECT $1::int = s FROM t JOIN t2 ON t2.id = $1::int;
```
--
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@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] PREPARE placeholder type coercion fails when plain queries work fine [datafusion]
Posted by "RatulDawar (via GitHub)" <gi...@apache.org>.
RatulDawar commented on issue #22506:
URL: https://github.com/apache/datafusion/issues/22506#issuecomment-4583093651
I think we should go for a postgres like behaviour here, i.e. fail at prepare time.
--
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@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org