You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "alamb (via GitHub)" <gi...@apache.org> on 2025/03/27 20:21:49 UTC
[I] Update ClickBench queries to avoid `to_timestamp_seconds` [datafusion]
alamb opened a new issue, #15465:
URL: https://github.com/apache/datafusion/issues/15465
### Is your feature request related to a problem or challenge?
For some reason the DataFusion version of the ClickBench queries use the `to_timestamp_seconds` function:
https://github.com/apache/datafusion/blob/46f4024577f4e13b79d8739849ecf51af5ddc7c2/benchmarks/queries/clickbench/queries.sql#L24
However that function does timestamp validation and potentially slows down queries and prevents other optimizations (for example what @adriangb is doing in https://github.com/apache/datafusion/pull/15301)
I checked and DuckDB simply uses `EventTime`
https://github.com/ClickHouse/ClickBench/blob/bdc6e32589c2785a66ccee98904a322c5e5d3f50/duckdb/queries.sql#L24C1-L25C1
As does `ClickHouse`
https://github.com/ClickHouse/ClickBench/blob/bdc6e32589c2785a66ccee98904a322c5e5d3f50/clickhouse/queries.sql#L24
### Describe the solution you'd like
Ideally the queries would be updated so they do not use to_timestamp_seconds
For example
```sql
SELECT * FROM 'hits.parquet' WHERE "URL" LIKE '%google%' ORDER BY "EventTime" LIMIT 10
```
### Describe alternatives you've considered
1. Update queries in this repo
2. Update the queries in the clickbench repository as well: https://github.com/ClickHouse/ClickBench/blob/bdc6e32589c2785a66ccee98904a322c5e5d3f50/datafusion/queries.sql
### 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] Update ClickBench queries to avoid `to_timestamp_seconds` [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #15465:
URL: https://github.com/apache/datafusion/issues/15465#issuecomment-2759430803
> > However that function does timestamp validation and potentially slows down queries and prevents other optimizations (for example what [@adriangb](https://github.com/adriangb) is doing in [#15301](https://github.com/apache/datafusion/pull/15301))
>
> I'm very curious about why this optimization is being blocked.
I think it is because we don't have the analysis to translate the `min(to_timestamp_seconds(column))` into a `column < [current topk min heap value]`
--
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] Update ClickBench queries to avoid `to_timestamp_seconds` [datafusion]
Posted by "acking-you (via GitHub)" <gi...@apache.org>.
acking-you commented on issue #15465:
URL: https://github.com/apache/datafusion/issues/15465#issuecomment-2761112439
> I think it is because we don't have the analysis to translate the min(to_timestamp_seconds(column)) into a column < [current topk min heap value]
1. So, should this situation be optimized as well?
2. I looked at the `EventTime` field in the hits dataset and found nothing special about it other than that it’s of type int64. I’m not quite sure why `ORDER BY to_timestamp_seconds("EventTime")` is used. I know this function converts the field to the `timestamp` type, but I don’t understand the significance of doing so.
--
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] Update ClickBench queries to avoid `to_timestamp_seconds` [datafusion]
Posted by "acking-you (via GitHub)" <gi...@apache.org>.
acking-you commented on issue #15465:
URL: https://github.com/apache/datafusion/issues/15465#issuecomment-2759412588
> However that function does timestamp validation and potentially slows down queries and prevents other optimizations (for example what @adriangb is doing in #15301)
I'm very curious about why this optimization is being blocked.
--
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] Update ClickBench queries to avoid `to_timestamp_seconds` [datafusion]
Posted by "acking-you (via GitHub)" <gi...@apache.org>.
acking-you commented on issue #15465:
URL: https://github.com/apache/datafusion/issues/15465#issuecomment-2759413205
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] Update ClickBench queries to avoid `to_timestamp_seconds` [datafusion]
Posted by "Dandandan (via GitHub)" <gi...@apache.org>.
Dandandan commented on issue #15465:
URL: https://github.com/apache/datafusion/issues/15465#issuecomment-2762989314
Another discrepancy I found in the queries is the "EventDate"::INT::DATE" casting. Is this something we could remove as well? Maybe would be good to look at all further that are applied to the queries and undo them if possible (or file issues when the planner fails to plan them).
--
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] Update ClickBench queries to avoid `to_timestamp_seconds` [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #15465:
URL: https://github.com/apache/datafusion/issues/15465#issuecomment-2767087855
> Another discrepancy I found in the queries is the "EventDate"::INT::DATE" casting. Is this something we could remove as well? Maybe would be good to look at all further that are applied to the queries and undo them if possible (or file issues when the planner fails to plan them).
- Filed https://github.com/apache/datafusion/issues/15509
--
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] Update ClickBench queries to avoid `to_timestamp_seconds` [datafusion]
Posted by "Dandandan (via GitHub)" <gi...@apache.org>.
Dandandan closed issue #15465: Update ClickBench queries to avoid `to_timestamp_seconds`
URL: https://github.com/apache/datafusion/issues/15465
--
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