alan910127 commented on PR #15110:
URL: https://github.com/apache/datafusion/pull/15110#issuecomment-2714725299
> As in only do the rewrite if the sequence
>
> - cast to int
> - cast (back) to string
>
> results in the exact same string as went int
@alamb I found that this approach is different from how postgres and duckdb
handle this situation:
- `SELECT * FROM t WHERE a = '0123'` - they will cast the '0123' to an
integer and it will match, so 1 row is returned.
- `SELECT * FROM t WHERE cast(a AS string) = '0123'` - they will not cast
the '0123', and this is a string comparison, so no rows are returned.
<details>
<summary>postgres</summary>
```sql
db=# create table t as select 123 a;
SELECT 1
db=# select * from t where a = '0123';
a
-----
123
(1 row)
db=# select * from t where cast(a AS text) = '0123';
a
---
(0 rows)
```
</details>
<details>
<summary>duckdb</summary>
```sql
D create table t as select 123 a;
D select * from t where a = '0123';
┌───────┐
│ a │
│ int32 │
├───────┤
│ 123 │
└───────┘
D select * from t where cast(a AS string) = '0123';
┌────────┐
│ a │
│ int32 │
├────────┤
│ 0 rows │
└────────┘
```
</details>
I'm not sure if it's only possible to handle it when the `cast` is created
(type coercion?)
--
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-unsubscr...@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org
For additional commands, e-mail: github-h...@datafusion.apache.org
