You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@datafusion.apache.org by gi...@apache.org on 2026/04/17 06:46:37 UTC
(datafusion) 01/01: Support Date32/Date64 in unwrap_cast optimization (#21665)
This is an automated email from the ASF dual-hosted git repository.
github-bot pushed a commit to branch gh-readonly-queue/main/pr-21665-8229509a27b2e4e31033f77b1fafd3a1966c10e1
in repository https://gitbox.apache.org/repos/asf/datafusion.git
commit 769e21447c6430086eb15f69dc03a5c46c93949e
Author: Daniël Heres <da...@gmail.com>
AuthorDate: Fri Apr 17 08:46:21 2026 +0200
Support Date32/Date64 in unwrap_cast optimization (#21665)
## Which issue does this PR close?
N/A
## Rationale for this change
Filters like `WHERE EventDate >= '2013-07-01'` on a `UInt16` column
exposed as `Date32` via a view produce `CAST(CAST(EventDate AS Int32) AS
Date32) >= Date32("2013-07-01")` — 4 CAST operations per row. The
existing `unwrap_cast_in_comparison` optimizer can eliminate these but
didn't support `Date32`/`Date64`.
## What changes are included in this PR?
Add `Date32`/`Date64` to `is_supported_numeric_type` and the match arms
in `try_cast_numeric_literal`. They are treated like `Int32`/`Int64`
since that's their physical representation (days / ms since epoch).
A guard prevents Date↔Timestamp unwrapping, which is lossy (drops
time-of-day).
**ClickBench Q36-Q42 before:**
```
FilterExec: CAST(CAST(EventDate@0 AS Int32) AS Date32) >= 2013-07-01
AND CAST(CAST(EventDate@0 AS Int32) AS Date32) <= 2013-07-31
```
**After:**
```
FilterExec: EventDate@0 >= 15887 AND EventDate@0 <= 15917
```
## Are these changes tested?
Existing `casts` tests pass. ClickBench sqllogictest expectations
updated.
## Are there any user-facing changes?
No.
---------
Co-authored-by: Claude Opus 4.6 (1M context) <no...@anthropic.com>
Co-authored-by: Andrew Lamb <an...@nerdnetworks.org>
---
datafusion/expr-common/src/casts.rs | 68 +++++++++++++++++++++--
datafusion/sqllogictest/test_files/clickbench.slt | 56 +++++++++----------
2 files changed, 92 insertions(+), 32 deletions(-)
diff --git a/datafusion/expr-common/src/casts.rs b/datafusion/expr-common/src/casts.rs
index 75382b287f..dad589e4bf 100644
--- a/datafusion/expr-common/src/casts.rs
+++ b/datafusion/expr-common/src/casts.rs
@@ -58,7 +58,28 @@ pub fn is_supported_type(data_type: &DataType) -> bool {
|| is_supported_binary_type(data_type)
}
-/// Returns true if unwrap_cast_in_comparison support this numeric type
+fn is_date_type(data_type: &DataType) -> bool {
+ matches!(data_type, DataType::Date32 | DataType::Date64)
+}
+
+/// Returns true when unwrapping a date/timestamp cast could change comparison
+/// semantics.
+///
+/// A `Date` stores only a calendar day, while a `Timestamp` stores a specific
+/// instant or wall-clock time. `Timestamp -> Date` is lossy because it drops the
+/// time-of-day. `Date -> Timestamp` is also lossy in this optimizer context
+/// because there is no unique inverse: converting a date to a timestamp has to
+/// invent a time component such as midnight.
+///
+/// For example, `CAST(ts AS DATE) = DATE '2024-01-01'` means "any timestamp
+/// during that day", but unwrapping it to `ts = TIMESTAMP '2024-01-01
+/// 00:00:00'` matches only midnight.
+fn is_lossy_temporal_cast(from_type: &DataType, to_type: &DataType) -> bool {
+ (is_date_type(from_type) && to_type.is_temporal())
+ || (is_date_type(to_type) && from_type.is_temporal())
+}
+
+/// Returns true if unwrap_cast_in_comparison supports this numeric type
fn is_supported_numeric_type(data_type: &DataType) -> bool {
matches!(
data_type,
@@ -70,6 +91,8 @@ fn is_supported_numeric_type(data_type: &DataType) -> bool {
| DataType::Int16
| DataType::Int32
| DataType::Int64
+ | DataType::Date32
+ | DataType::Date64
| DataType::Decimal32(_, _)
| DataType::Decimal64(_, _)
| DataType::Decimal128(_, _)
@@ -107,6 +130,10 @@ fn try_cast_numeric_literal(
return None;
}
+ if is_lossy_temporal_cast(&lit_data_type, target_type) {
+ return None;
+ }
+
let mul = match target_type {
DataType::UInt8
| DataType::UInt16
@@ -115,7 +142,9 @@ fn try_cast_numeric_literal(
| DataType::Int8
| DataType::Int16
| DataType::Int32
- | DataType::Int64 => 1_i128,
+ | DataType::Int64
+ | DataType::Date32
+ | DataType::Date64 => 1_i128,
DataType::Timestamp(_, _) => 1_i128,
DataType::Decimal32(_, scale) => 10_i128.pow(*scale as u32),
DataType::Decimal64(_, scale) => 10_i128.pow(*scale as u32),
@@ -129,8 +158,8 @@ fn try_cast_numeric_literal(
DataType::UInt64 => (u64::MIN as i128, u64::MAX as i128),
DataType::Int8 => (i8::MIN as i128, i8::MAX as i128),
DataType::Int16 => (i16::MIN as i128, i16::MAX as i128),
- DataType::Int32 => (i32::MIN as i128, i32::MAX as i128),
- DataType::Int64 => (i64::MIN as i128, i64::MAX as i128),
+ DataType::Int32 | DataType::Date32 => (i32::MIN as i128, i32::MAX as i128),
+ DataType::Int64 | DataType::Date64 => (i64::MIN as i128, i64::MAX as i128),
DataType::Timestamp(_, _) => (i64::MIN as i128, i64::MAX as i128),
DataType::Decimal32(precision, _) => (
// Different precision for decimal32 can store different range of value.
@@ -164,6 +193,8 @@ fn try_cast_numeric_literal(
ScalarValue::UInt16(Some(v)) => (*v as i128).checked_mul(mul),
ScalarValue::UInt32(Some(v)) => (*v as i128).checked_mul(mul),
ScalarValue::UInt64(Some(v)) => (*v as i128).checked_mul(mul),
+ ScalarValue::Date32(Some(v)) => (*v as i128).checked_mul(mul),
+ ScalarValue::Date64(Some(v)) => (*v as i128).checked_mul(mul),
ScalarValue::TimestampSecond(Some(v), _) => (*v as i128).checked_mul(mul),
ScalarValue::TimestampMillisecond(Some(v), _) => (*v as i128).checked_mul(mul),
ScalarValue::TimestampMicrosecond(Some(v), _) => (*v as i128).checked_mul(mul),
@@ -241,6 +272,8 @@ fn try_cast_numeric_literal(
DataType::Int16 => ScalarValue::Int16(Some(value as i16)),
DataType::Int32 => ScalarValue::Int32(Some(value as i32)),
DataType::Int64 => ScalarValue::Int64(Some(value as i64)),
+ DataType::Date32 => ScalarValue::Date32(Some(value as i32)),
+ DataType::Date64 => ScalarValue::Date64(Some(value as i64)),
DataType::UInt8 => ScalarValue::UInt8(Some(value as u8)),
DataType::UInt16 => ScalarValue::UInt16(Some(value as u16)),
DataType::UInt32 => ScalarValue::UInt32(Some(value as u32)),
@@ -700,6 +733,33 @@ mod tests {
}
}
+ #[test]
+ fn test_try_cast_to_type_date_timestamp_lossy_not_allowed() {
+ expect_cast(
+ ScalarValue::Date32(Some(1)),
+ DataType::Timestamp(TimeUnit::Second, None),
+ ExpectedCast::NoValue,
+ );
+
+ expect_cast(
+ ScalarValue::Date64(Some(86_400_000)),
+ DataType::Timestamp(TimeUnit::Millisecond, None),
+ ExpectedCast::NoValue,
+ );
+
+ expect_cast(
+ ScalarValue::TimestampSecond(Some(86_400), None),
+ DataType::Date32,
+ ExpectedCast::NoValue,
+ );
+
+ expect_cast(
+ ScalarValue::TimestampMillisecond(Some(86_400_000), None),
+ DataType::Date64,
+ ExpectedCast::NoValue,
+ );
+ }
+
#[test]
fn test_try_cast_to_type_unsupported() {
// int64 to list
diff --git a/datafusion/sqllogictest/test_files/clickbench.slt b/datafusion/sqllogictest/test_files/clickbench.slt
index 314c3f9736..c79701e347 100644
--- a/datafusion/sqllogictest/test_files/clickbench.slt
+++ b/datafusion/sqllogictest/test_files/clickbench.slt
@@ -985,8 +985,8 @@ logical_plan
03)----Aggregate: groupBy=[[hits.URL]], aggr=[[count(Int64(1))]]
04)------SubqueryAlias: hits
05)--------Projection: hits_raw.URL
-06)----------Filter: hits_raw.CounterID = Int32(62) AND CAST(CAST(hits_raw.EventDate AS Int32) AS Date32) >= Date32("2013-07-01") AND CAST(CAST(hits_raw.EventDate AS Int32) AS Date32) <= Date32("2013-07-31") AND hits_raw.DontCountHits = Int16(0) AND hits_raw.IsRefresh = Int16(0) AND hits_raw.URL != Utf8View("")
-07)------------TableScan: hits_raw projection=[EventDate, CounterID, URL, IsRefresh, DontCountHits], partial_filters=[hits_raw.CounterID = Int32(62), CAST(CAST(hits_raw.EventDate AS Int32) AS Date32) >= Date32("2013-07-01"), CAST(CAST(hits_raw.EventDate AS Int32) AS Date32) <= Date32("2013-07-31"), hits_raw.DontCountHits = Int16(0), hits_raw.IsRefresh = Int16(0), hits_raw.URL != Utf8View("")]
+06)----------Filter: hits_raw.CounterID = Int32(62) AND hits_raw.EventDate >= UInt16(15887) AND hits_raw.EventDate <= UInt16(15917) AND hits_raw.DontCountHits = Int16(0) AND hits_raw.IsRefresh = Int16(0) AND hits_raw.URL != Utf8View("")
+07)------------TableScan: hits_raw projection=[EventDate, CounterID, URL, IsRefresh, DontCountHits], partial_filters=[hits_raw.CounterID = Int32(62), hits_raw.EventDate >= UInt16(15887), hits_raw.EventDate <= UInt16(15917), hits_raw.DontCountHits = Int16(0), hits_raw.IsRefresh = Int16(0), hits_raw.URL != Utf8View("")]
physical_plan
01)SortPreservingMergeExec: [pageviews@1 DESC], fetch=10
02)--SortExec: TopK(fetch=10), expr=[pageviews@1 DESC], preserve_partitioning=[true]
@@ -994,9 +994,9 @@ physical_plan
04)------AggregateExec: mode=FinalPartitioned, gby=[URL@0 as URL], aggr=[count(Int64(1))]
05)--------RepartitionExec: partitioning=Hash([URL@0], 4), input_partitions=4
06)----------AggregateExec: mode=Partial, gby=[URL@0 as URL], aggr=[count(Int64(1))]
-07)------------FilterExec: CounterID@1 = 62 AND CAST(CAST(EventDate@0 AS Int32) AS Date32) >= 2013-07-01 AND CAST(CAST(EventDate@0 AS Int32) AS Date32) <= 2013-07-31 AND DontCountHits@4 = 0 AND IsRefresh@3 = 0 AND URL@2 != , projection=[URL@2]
+07)------------FilterExec: CounterID@1 = 62 AND EventDate@0 >= 15887 AND EventDate@0 <= 15917 AND DontCountHits@4 = 0 AND IsRefresh@3 = 0 AND URL@2 != , projection=[URL@2]
08)--------------RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1
-09)----------------DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/core/tests/data/clickbench_hits_10.parquet]]}, projection=[EventDate, CounterID, URL, IsRefresh, DontCountHits], file_type=parquet, predicate=CounterID@6 = 62 AND CAST(CAST(EventDate@5 AS Int32) AS Date32) >= 2013-07-01 AND CAST(CAST(EventDate@5 AS Int32) AS Date32) <= 2013-07-31 AND DontCountHits@61 = 0 AND IsRefresh@15 = 0 AND URL@13 != , pruning_predicate=CounterID_null_count@2 != row_count@3 AND Coun [...]
+09)----------------DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/core/tests/data/clickbench_hits_10.parquet]]}, projection=[EventDate, CounterID, URL, IsRefresh, DontCountHits], file_type=parquet, predicate=CounterID@6 = 62 AND EventDate@5 >= 15887 AND EventDate@5 <= 15917 AND DontCountHits@61 = 0 AND IsRefresh@15 = 0 AND URL@13 != , pruning_predicate=CounterID_null_count@2 != row_count@3 AND CounterID_min@0 <= 62 AND 62 <= CounterID_max@1 AND EventDate_null_count@5 ! [...]
query TI
SELECT "URL", COUNT(*) AS PageViews FROM hits WHERE "CounterID" = 62 AND "EventDate" >= '2013-07-01' AND "EventDate" <= '2013-07-31' AND "DontCountHits" = 0 AND "IsRefresh" = 0 AND "URL" <> '' GROUP BY "URL" ORDER BY PageViews DESC LIMIT 10;
@@ -1012,8 +1012,8 @@ logical_plan
03)----Aggregate: groupBy=[[hits.Title]], aggr=[[count(Int64(1))]]
04)------SubqueryAlias: hits
05)--------Projection: hits_raw.Title
-06)----------Filter: hits_raw.CounterID = Int32(62) AND CAST(CAST(hits_raw.EventDate AS Int32) AS Date32) >= Date32("2013-07-01") AND CAST(CAST(hits_raw.EventDate AS Int32) AS Date32) <= Date32("2013-07-31") AND hits_raw.DontCountHits = Int16(0) AND hits_raw.IsRefresh = Int16(0) AND hits_raw.Title != Utf8View("")
-07)------------TableScan: hits_raw projection=[Title, EventDate, CounterID, IsRefresh, DontCountHits], partial_filters=[hits_raw.CounterID = Int32(62), CAST(CAST(hits_raw.EventDate AS Int32) AS Date32) >= Date32("2013-07-01"), CAST(CAST(hits_raw.EventDate AS Int32) AS Date32) <= Date32("2013-07-31"), hits_raw.DontCountHits = Int16(0), hits_raw.IsRefresh = Int16(0), hits_raw.Title != Utf8View("")]
+06)----------Filter: hits_raw.CounterID = Int32(62) AND hits_raw.EventDate >= UInt16(15887) AND hits_raw.EventDate <= UInt16(15917) AND hits_raw.DontCountHits = Int16(0) AND hits_raw.IsRefresh = Int16(0) AND hits_raw.Title != Utf8View("")
+07)------------TableScan: hits_raw projection=[Title, EventDate, CounterID, IsRefresh, DontCountHits], partial_filters=[hits_raw.CounterID = Int32(62), hits_raw.EventDate >= UInt16(15887), hits_raw.EventDate <= UInt16(15917), hits_raw.DontCountHits = Int16(0), hits_raw.IsRefresh = Int16(0), hits_raw.Title != Utf8View("")]
physical_plan
01)SortPreservingMergeExec: [pageviews@1 DESC], fetch=10
02)--SortExec: TopK(fetch=10), expr=[pageviews@1 DESC], preserve_partitioning=[true]
@@ -1021,9 +1021,9 @@ physical_plan
04)------AggregateExec: mode=FinalPartitioned, gby=[Title@0 as Title], aggr=[count(Int64(1))]
05)--------RepartitionExec: partitioning=Hash([Title@0], 4), input_partitions=4
06)----------AggregateExec: mode=Partial, gby=[Title@0 as Title], aggr=[count(Int64(1))]
-07)------------FilterExec: CounterID@2 = 62 AND CAST(CAST(EventDate@1 AS Int32) AS Date32) >= 2013-07-01 AND CAST(CAST(EventDate@1 AS Int32) AS Date32) <= 2013-07-31 AND DontCountHits@4 = 0 AND IsRefresh@3 = 0 AND Title@0 != , projection=[Title@0]
+07)------------FilterExec: CounterID@2 = 62 AND EventDate@1 >= 15887 AND EventDate@1 <= 15917 AND DontCountHits@4 = 0 AND IsRefresh@3 = 0 AND Title@0 != , projection=[Title@0]
08)--------------RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1
-09)----------------DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/core/tests/data/clickbench_hits_10.parquet]]}, projection=[Title, EventDate, CounterID, IsRefresh, DontCountHits], file_type=parquet, predicate=CounterID@6 = 62 AND CAST(CAST(EventDate@5 AS Int32) AS Date32) >= 2013-07-01 AND CAST(CAST(EventDate@5 AS Int32) AS Date32) <= 2013-07-31 AND DontCountHits@61 = 0 AND IsRefresh@15 = 0 AND Title@2 != , pruning_predicate=CounterID_null_count@2 != row_count@3 AND C [...]
+09)----------------DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/core/tests/data/clickbench_hits_10.parquet]]}, projection=[Title, EventDate, CounterID, IsRefresh, DontCountHits], file_type=parquet, predicate=CounterID@6 = 62 AND EventDate@5 >= 15887 AND EventDate@5 <= 15917 AND DontCountHits@61 = 0 AND IsRefresh@15 = 0 AND Title@2 != , pruning_predicate=CounterID_null_count@2 != row_count@3 AND CounterID_min@0 <= 62 AND 62 <= CounterID_max@1 AND EventDate_null_count@ [...]
query TI
SELECT "Title", COUNT(*) AS PageViews FROM hits WHERE "CounterID" = 62 AND "EventDate" >= '2013-07-01' AND "EventDate" <= '2013-07-31' AND "DontCountHits" = 0 AND "IsRefresh" = 0 AND "Title" <> '' GROUP BY "Title" ORDER BY PageViews DESC LIMIT 10;
@@ -1040,8 +1040,8 @@ logical_plan
04)------Aggregate: groupBy=[[hits.URL]], aggr=[[count(Int64(1))]]
05)--------SubqueryAlias: hits
06)----------Projection: hits_raw.URL
-07)------------Filter: hits_raw.CounterID = Int32(62) AND CAST(CAST(hits_raw.EventDate AS Int32) AS Date32) >= Date32("2013-07-01") AND CAST(CAST(hits_raw.EventDate AS Int32) AS Date32) <= Date32("2013-07-31") AND hits_raw.IsRefresh = Int16(0) AND hits_raw.IsLink != Int16(0) AND hits_raw.IsDownload = Int16(0)
-08)--------------TableScan: hits_raw projection=[EventDate, CounterID, URL, IsRefresh, IsLink, IsDownload], partial_filters=[hits_raw.CounterID = Int32(62), CAST(CAST(hits_raw.EventDate AS Int32) AS Date32) >= Date32("2013-07-01"), CAST(CAST(hits_raw.EventDate AS Int32) AS Date32) <= Date32("2013-07-31"), hits_raw.IsRefresh = Int16(0), hits_raw.IsLink != Int16(0), hits_raw.IsDownload = Int16(0)]
+07)------------Filter: hits_raw.CounterID = Int32(62) AND hits_raw.EventDate >= UInt16(15887) AND hits_raw.EventDate <= UInt16(15917) AND hits_raw.IsRefresh = Int16(0) AND hits_raw.IsLink != Int16(0) AND hits_raw.IsDownload = Int16(0)
+08)--------------TableScan: hits_raw projection=[EventDate, CounterID, URL, IsRefresh, IsLink, IsDownload], partial_filters=[hits_raw.CounterID = Int32(62), hits_raw.EventDate >= UInt16(15887), hits_raw.EventDate <= UInt16(15917), hits_raw.IsRefresh = Int16(0), hits_raw.IsLink != Int16(0), hits_raw.IsDownload = Int16(0)]
physical_plan
01)GlobalLimitExec: skip=1000, fetch=10
02)--SortPreservingMergeExec: [pageviews@1 DESC], fetch=1010
@@ -1050,9 +1050,9 @@ physical_plan
05)--------AggregateExec: mode=FinalPartitioned, gby=[URL@0 as URL], aggr=[count(Int64(1))]
06)----------RepartitionExec: partitioning=Hash([URL@0], 4), input_partitions=4
07)------------AggregateExec: mode=Partial, gby=[URL@0 as URL], aggr=[count(Int64(1))]
-08)--------------FilterExec: CounterID@1 = 62 AND CAST(CAST(EventDate@0 AS Int32) AS Date32) >= 2013-07-01 AND CAST(CAST(EventDate@0 AS Int32) AS Date32) <= 2013-07-31 AND IsRefresh@3 = 0 AND IsLink@4 != 0 AND IsDownload@5 = 0, projection=[URL@2]
+08)--------------FilterExec: CounterID@1 = 62 AND EventDate@0 >= 15887 AND EventDate@0 <= 15917 AND IsRefresh@3 = 0 AND IsLink@4 != 0 AND IsDownload@5 = 0, projection=[URL@2]
09)----------------RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1
-10)------------------DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/core/tests/data/clickbench_hits_10.parquet]]}, projection=[EventDate, CounterID, URL, IsRefresh, IsLink, IsDownload], file_type=parquet, predicate=CounterID@6 = 62 AND CAST(CAST(EventDate@5 AS Int32) AS Date32) >= 2013-07-01 AND CAST(CAST(EventDate@5 AS Int32) AS Date32) <= 2013-07-31 AND IsRefresh@15 = 0 AND IsLink@52 != 0 AND IsDownload@53 = 0, pruning_predicate=CounterID_null_count@2 != row_count@3 [...]
+10)------------------DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/core/tests/data/clickbench_hits_10.parquet]]}, projection=[EventDate, CounterID, URL, IsRefresh, IsLink, IsDownload], file_type=parquet, predicate=CounterID@6 = 62 AND EventDate@5 >= 15887 AND EventDate@5 <= 15917 AND IsRefresh@15 = 0 AND IsLink@52 != 0 AND IsDownload@53 = 0, pruning_predicate=CounterID_null_count@2 != row_count@3 AND CounterID_min@0 <= 62 AND 62 <= CounterID_max@1 AND EventDate_null_c [...]
query TI
SELECT "URL", COUNT(*) AS PageViews FROM hits WHERE "CounterID" = 62 AND "EventDate" >= '2013-07-01' AND "EventDate" <= '2013-07-31' AND "IsRefresh" = 0 AND "IsLink" <> 0 AND "IsDownload" = 0 GROUP BY "URL" ORDER BY PageViews DESC LIMIT 10 OFFSET 1000;
@@ -1069,8 +1069,8 @@ logical_plan
04)------Aggregate: groupBy=[[hits.TraficSourceID, hits.SearchEngineID, hits.AdvEngineID, CASE WHEN hits.SearchEngineID = Int16(0) AND hits.AdvEngineID = Int16(0) THEN hits.Referer ELSE Utf8View("") END AS CASE WHEN hits.SearchEngineID = Int64(0) AND hits.AdvEngineID = Int64(0) THEN hits.Referer ELSE Utf8("") END, hits.URL]], aggr=[[count(Int64(1))]]
05)--------SubqueryAlias: hits
06)----------Projection: hits_raw.URL, hits_raw.Referer, hits_raw.TraficSourceID, hits_raw.SearchEngineID, hits_raw.AdvEngineID
-07)------------Filter: hits_raw.CounterID = Int32(62) AND CAST(CAST(hits_raw.EventDate AS Int32) AS Date32) >= Date32("2013-07-01") AND CAST(CAST(hits_raw.EventDate AS Int32) AS Date32) <= Date32("2013-07-31") AND hits_raw.IsRefresh = Int16(0)
-08)--------------TableScan: hits_raw projection=[EventDate, CounterID, URL, Referer, IsRefresh, TraficSourceID, SearchEngineID, AdvEngineID], partial_filters=[hits_raw.CounterID = Int32(62), CAST(CAST(hits_raw.EventDate AS Int32) AS Date32) >= Date32("2013-07-01"), CAST(CAST(hits_raw.EventDate AS Int32) AS Date32) <= Date32("2013-07-31"), hits_raw.IsRefresh = Int16(0)]
+07)------------Filter: hits_raw.CounterID = Int32(62) AND hits_raw.EventDate >= UInt16(15887) AND hits_raw.EventDate <= UInt16(15917) AND hits_raw.IsRefresh = Int16(0)
+08)--------------TableScan: hits_raw projection=[EventDate, CounterID, URL, Referer, IsRefresh, TraficSourceID, SearchEngineID, AdvEngineID], partial_filters=[hits_raw.CounterID = Int32(62), hits_raw.EventDate >= UInt16(15887), hits_raw.EventDate <= UInt16(15917), hits_raw.IsRefresh = Int16(0)]
physical_plan
01)GlobalLimitExec: skip=1000, fetch=10
02)--SortPreservingMergeExec: [pageviews@5 DESC], fetch=1010
@@ -1079,9 +1079,9 @@ physical_plan
05)--------AggregateExec: mode=FinalPartitioned, gby=[TraficSourceID@0 as TraficSourceID, SearchEngineID@1 as SearchEngineID, AdvEngineID@2 as AdvEngineID, CASE WHEN hits.SearchEngineID = Int64(0) AND hits.AdvEngineID = Int64(0) THEN hits.Referer ELSE Utf8("") END@3 as CASE WHEN hits.SearchEngineID = Int64(0) AND hits.AdvEngineID = Int64(0) THEN hits.Referer ELSE Utf8("") END, URL@4 as URL], aggr=[count(Int64(1))]
06)----------RepartitionExec: partitioning=Hash([TraficSourceID@0, SearchEngineID@1, AdvEngineID@2, CASE WHEN hits.SearchEngineID = Int64(0) AND hits.AdvEngineID = Int64(0) THEN hits.Referer ELSE Utf8("") END@3, URL@4], 4), input_partitions=4
07)------------AggregateExec: mode=Partial, gby=[TraficSourceID@2 as TraficSourceID, SearchEngineID@3 as SearchEngineID, AdvEngineID@4 as AdvEngineID, CASE WHEN SearchEngineID@3 = 0 AND AdvEngineID@4 = 0 THEN Referer@1 ELSE END as CASE WHEN hits.SearchEngineID = Int64(0) AND hits.AdvEngineID = Int64(0) THEN hits.Referer ELSE Utf8("") END, URL@0 as URL], aggr=[count(Int64(1))]
-08)--------------FilterExec: CounterID@1 = 62 AND CAST(CAST(EventDate@0 AS Int32) AS Date32) >= 2013-07-01 AND CAST(CAST(EventDate@0 AS Int32) AS Date32) <= 2013-07-31 AND IsRefresh@4 = 0, projection=[URL@2, Referer@3, TraficSourceID@5, SearchEngineID@6, AdvEngineID@7]
+08)--------------FilterExec: CounterID@1 = 62 AND EventDate@0 >= 15887 AND EventDate@0 <= 15917 AND IsRefresh@4 = 0, projection=[URL@2, Referer@3, TraficSourceID@5, SearchEngineID@6, AdvEngineID@7]
09)----------------RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1
-10)------------------DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/core/tests/data/clickbench_hits_10.parquet]]}, projection=[EventDate, CounterID, URL, Referer, IsRefresh, TraficSourceID, SearchEngineID, AdvEngineID], file_type=parquet, predicate=CounterID@6 = 62 AND CAST(CAST(EventDate@5 AS Int32) AS Date32) >= 2013-07-01 AND CAST(CAST(EventDate@5 AS Int32) AS Date32) <= 2013-07-31 AND IsRefresh@15 = 0, pruning_predicate=CounterID_null_count@2 != row_count@3 AND Cou [...]
+10)------------------DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/core/tests/data/clickbench_hits_10.parquet]]}, projection=[EventDate, CounterID, URL, Referer, IsRefresh, TraficSourceID, SearchEngineID, AdvEngineID], file_type=parquet, predicate=CounterID@6 = 62 AND EventDate@5 >= 15887 AND EventDate@5 <= 15917 AND IsRefresh@15 = 0, pruning_predicate=CounterID_null_count@2 != row_count@3 AND CounterID_min@0 <= 62 AND 62 <= CounterID_max@1 AND EventDate_null_count@5 [...]
query IIITTI
SELECT "TraficSourceID", "SearchEngineID", "AdvEngineID", CASE WHEN ("SearchEngineID" = 0 AND "AdvEngineID" = 0) THEN "Referer" ELSE '' END AS Src, "URL" AS Dst, COUNT(*) AS PageViews FROM hits WHERE "CounterID" = 62 AND "EventDate" >= '2013-07-01' AND "EventDate" <= '2013-07-31' AND "IsRefresh" = 0 GROUP BY "TraficSourceID", "SearchEngineID", "AdvEngineID", Src, Dst ORDER BY PageViews DESC LIMIT 10 OFFSET 1000;
@@ -1098,8 +1098,8 @@ logical_plan
04)------Aggregate: groupBy=[[hits.URLHash, hits.EventDate]], aggr=[[count(Int64(1))]]
05)--------SubqueryAlias: hits
06)----------Projection: hits_raw.URLHash, CAST(CAST(hits_raw.EventDate AS Int32) AS Date32) AS EventDate
-07)------------Filter: hits_raw.CounterID = Int32(62) AND CAST(CAST(hits_raw.EventDate AS Int32) AS Date32) >= Date32("2013-07-01") AND CAST(CAST(hits_raw.EventDate AS Int32) AS Date32) <= Date32("2013-07-31") AND hits_raw.IsRefresh = Int16(0) AND (hits_raw.TraficSourceID = Int16(-1) OR hits_raw.TraficSourceID = Int16(6)) AND hits_raw.RefererHash = Int64(3594120000172545465)
-08)--------------TableScan: hits_raw projection=[EventDate, CounterID, IsRefresh, TraficSourceID, RefererHash, URLHash], partial_filters=[hits_raw.CounterID = Int32(62), CAST(CAST(hits_raw.EventDate AS Int32) AS Date32) >= Date32("2013-07-01"), CAST(CAST(hits_raw.EventDate AS Int32) AS Date32) <= Date32("2013-07-31"), hits_raw.IsRefresh = Int16(0), hits_raw.TraficSourceID = Int16(-1) OR hits_raw.TraficSourceID = Int16(6), hits_raw.RefererHash = Int64(3594120000172545465)]
+07)------------Filter: hits_raw.CounterID = Int32(62) AND hits_raw.EventDate >= UInt16(15887) AND hits_raw.EventDate <= UInt16(15917) AND hits_raw.IsRefresh = Int16(0) AND (hits_raw.TraficSourceID = Int16(-1) OR hits_raw.TraficSourceID = Int16(6)) AND hits_raw.RefererHash = Int64(3594120000172545465)
+08)--------------TableScan: hits_raw projection=[EventDate, CounterID, IsRefresh, TraficSourceID, RefererHash, URLHash], partial_filters=[hits_raw.CounterID = Int32(62), hits_raw.EventDate >= UInt16(15887), hits_raw.EventDate <= UInt16(15917), hits_raw.IsRefresh = Int16(0), hits_raw.TraficSourceID = Int16(-1) OR hits_raw.TraficSourceID = Int16(6), hits_raw.RefererHash = Int64(3594120000172545465)]
physical_plan
01)GlobalLimitExec: skip=100, fetch=10
02)--SortPreservingMergeExec: [pageviews@2 DESC], fetch=110
@@ -1109,9 +1109,9 @@ physical_plan
06)----------RepartitionExec: partitioning=Hash([URLHash@0, EventDate@1], 4), input_partitions=4
07)------------AggregateExec: mode=Partial, gby=[URLHash@0 as URLHash, EventDate@1 as EventDate], aggr=[count(Int64(1))]
08)--------------ProjectionExec: expr=[URLHash@0 as URLHash, CAST(CAST(EventDate@1 AS Int32) AS Date32) as EventDate]
-09)----------------FilterExec: CounterID@1 = 62 AND CAST(CAST(EventDate@0 AS Int32) AS Date32) >= 2013-07-01 AND CAST(CAST(EventDate@0 AS Int32) AS Date32) <= 2013-07-31 AND IsRefresh@2 = 0 AND (TraficSourceID@3 = -1 OR TraficSourceID@3 = 6) AND RefererHash@4 = 3594120000172545465, projection=[URLHash@5, EventDate@0]
+09)----------------FilterExec: CounterID@1 = 62 AND EventDate@0 >= 15887 AND EventDate@0 <= 15917 AND IsRefresh@2 = 0 AND (TraficSourceID@3 = -1 OR TraficSourceID@3 = 6) AND RefererHash@4 = 3594120000172545465, projection=[URLHash@5, EventDate@0]
10)------------------RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1
-11)--------------------DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/core/tests/data/clickbench_hits_10.parquet]]}, projection=[EventDate, CounterID, IsRefresh, TraficSourceID, RefererHash, URLHash], file_type=parquet, predicate=CounterID@6 = 62 AND CAST(CAST(EventDate@5 AS Int32) AS Date32) >= 2013-07-01 AND CAST(CAST(EventDate@5 AS Int32) AS Date32) <= 2013-07-31 AND IsRefresh@15 = 0 AND (TraficSourceID@37 = -1 OR TraficSourceID@37 = 6) AND RefererHash@102 = 3594120 [...]
+11)--------------------DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/core/tests/data/clickbench_hits_10.parquet]]}, projection=[EventDate, CounterID, IsRefresh, TraficSourceID, RefererHash, URLHash], file_type=parquet, predicate=CounterID@6 = 62 AND EventDate@5 >= 15887 AND EventDate@5 <= 15917 AND IsRefresh@15 = 0 AND (TraficSourceID@37 = -1 OR TraficSourceID@37 = 6) AND RefererHash@102 = 3594120000172545465, pruning_predicate=CounterID_null_count@2 != row_count@3 AN [...]
query IDI
SELECT "URLHash", "EventDate", COUNT(*) AS PageViews FROM hits WHERE "CounterID" = 62 AND "EventDate" >= '2013-07-01' AND "EventDate" <= '2013-07-31' AND "IsRefresh" = 0 AND "TraficSourceID" IN (-1, 6) AND "RefererHash" = 3594120000172545465 GROUP BY "URLHash", "EventDate" ORDER BY PageViews DESC LIMIT 10 OFFSET 100;
@@ -1128,8 +1128,8 @@ logical_plan
04)------Aggregate: groupBy=[[hits.WindowClientWidth, hits.WindowClientHeight]], aggr=[[count(Int64(1))]]
05)--------SubqueryAlias: hits
06)----------Projection: hits_raw.WindowClientWidth, hits_raw.WindowClientHeight
-07)------------Filter: hits_raw.CounterID = Int32(62) AND CAST(CAST(hits_raw.EventDate AS Int32) AS Date32) >= Date32("2013-07-01") AND CAST(CAST(hits_raw.EventDate AS Int32) AS Date32) <= Date32("2013-07-31") AND hits_raw.IsRefresh = Int16(0) AND hits_raw.DontCountHits = Int16(0) AND hits_raw.URLHash = Int64(2868770270353813622)
-08)--------------TableScan: hits_raw projection=[EventDate, CounterID, IsRefresh, WindowClientWidth, WindowClientHeight, DontCountHits, URLHash], partial_filters=[hits_raw.CounterID = Int32(62), CAST(CAST(hits_raw.EventDate AS Int32) AS Date32) >= Date32("2013-07-01"), CAST(CAST(hits_raw.EventDate AS Int32) AS Date32) <= Date32("2013-07-31"), hits_raw.IsRefresh = Int16(0), hits_raw.DontCountHits = Int16(0), hits_raw.URLHash = Int64(2868770270353813622)]
+07)------------Filter: hits_raw.CounterID = Int32(62) AND hits_raw.EventDate >= UInt16(15887) AND hits_raw.EventDate <= UInt16(15917) AND hits_raw.IsRefresh = Int16(0) AND hits_raw.DontCountHits = Int16(0) AND hits_raw.URLHash = Int64(2868770270353813622)
+08)--------------TableScan: hits_raw projection=[EventDate, CounterID, IsRefresh, WindowClientWidth, WindowClientHeight, DontCountHits, URLHash], partial_filters=[hits_raw.CounterID = Int32(62), hits_raw.EventDate >= UInt16(15887), hits_raw.EventDate <= UInt16(15917), hits_raw.IsRefresh = Int16(0), hits_raw.DontCountHits = Int16(0), hits_raw.URLHash = Int64(2868770270353813622)]
physical_plan
01)GlobalLimitExec: skip=10000, fetch=10
02)--SortPreservingMergeExec: [pageviews@2 DESC], fetch=10010
@@ -1138,9 +1138,9 @@ physical_plan
05)--------AggregateExec: mode=FinalPartitioned, gby=[WindowClientWidth@0 as WindowClientWidth, WindowClientHeight@1 as WindowClientHeight], aggr=[count(Int64(1))]
06)----------RepartitionExec: partitioning=Hash([WindowClientWidth@0, WindowClientHeight@1], 4), input_partitions=4
07)------------AggregateExec: mode=Partial, gby=[WindowClientWidth@0 as WindowClientWidth, WindowClientHeight@1 as WindowClientHeight], aggr=[count(Int64(1))]
-08)--------------FilterExec: CounterID@1 = 62 AND CAST(CAST(EventDate@0 AS Int32) AS Date32) >= 2013-07-01 AND CAST(CAST(EventDate@0 AS Int32) AS Date32) <= 2013-07-31 AND IsRefresh@2 = 0 AND DontCountHits@5 = 0 AND URLHash@6 = 2868770270353813622, projection=[WindowClientWidth@3, WindowClientHeight@4]
+08)--------------FilterExec: CounterID@1 = 62 AND EventDate@0 >= 15887 AND EventDate@0 <= 15917 AND IsRefresh@2 = 0 AND DontCountHits@5 = 0 AND URLHash@6 = 2868770270353813622, projection=[WindowClientWidth@3, WindowClientHeight@4]
09)----------------RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1
-10)------------------DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/core/tests/data/clickbench_hits_10.parquet]]}, projection=[EventDate, CounterID, IsRefresh, WindowClientWidth, WindowClientHeight, DontCountHits, URLHash], file_type=parquet, predicate=CounterID@6 = 62 AND CAST(CAST(EventDate@5 AS Int32) AS Date32) >= 2013-07-01 AND CAST(CAST(EventDate@5 AS Int32) AS Date32) <= 2013-07-31 AND IsRefresh@15 = 0 AND DontCountHits@61 = 0 AND URLHash@103 = 28687702703538136 [...]
+10)------------------DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/core/tests/data/clickbench_hits_10.parquet]]}, projection=[EventDate, CounterID, IsRefresh, WindowClientWidth, WindowClientHeight, DontCountHits, URLHash], file_type=parquet, predicate=CounterID@6 = 62 AND EventDate@5 >= 15887 AND EventDate@5 <= 15917 AND IsRefresh@15 = 0 AND DontCountHits@61 = 0 AND URLHash@103 = 2868770270353813622, pruning_predicate=CounterID_null_count@2 != row_count@3 AND CounterI [...]
query III
SELECT "WindowClientWidth", "WindowClientHeight", COUNT(*) AS PageViews FROM hits WHERE "CounterID" = 62 AND "EventDate" >= '2013-07-01' AND "EventDate" <= '2013-07-31' AND "IsRefresh" = 0 AND "DontCountHits" = 0 AND "URLHash" = 2868770270353813622 GROUP BY "WindowClientWidth", "WindowClientHeight" ORDER BY PageViews DESC LIMIT 10 OFFSET 10000;
@@ -1157,8 +1157,8 @@ logical_plan
04)------Aggregate: groupBy=[[date_trunc(Utf8("minute"), to_timestamp_seconds(hits.EventTime))]], aggr=[[count(Int64(1))]]
05)--------SubqueryAlias: hits
06)----------Projection: hits_raw.EventTime
-07)------------Filter: hits_raw.CounterID = Int32(62) AND CAST(CAST(hits_raw.EventDate AS Int32) AS Date32) >= Date32("2013-07-14") AND CAST(CAST(hits_raw.EventDate AS Int32) AS Date32) <= Date32("2013-07-15") AND hits_raw.IsRefresh = Int16(0) AND hits_raw.DontCountHits = Int16(0)
-08)--------------TableScan: hits_raw projection=[EventTime, EventDate, CounterID, IsRefresh, DontCountHits], partial_filters=[hits_raw.CounterID = Int32(62), CAST(CAST(hits_raw.EventDate AS Int32) AS Date32) >= Date32("2013-07-14"), CAST(CAST(hits_raw.EventDate AS Int32) AS Date32) <= Date32("2013-07-15"), hits_raw.IsRefresh = Int16(0), hits_raw.DontCountHits = Int16(0)]
+07)------------Filter: hits_raw.CounterID = Int32(62) AND hits_raw.EventDate >= UInt16(15900) AND hits_raw.EventDate <= UInt16(15901) AND hits_raw.IsRefresh = Int16(0) AND hits_raw.DontCountHits = Int16(0)
+08)--------------TableScan: hits_raw projection=[EventTime, EventDate, CounterID, IsRefresh, DontCountHits], partial_filters=[hits_raw.CounterID = Int32(62), hits_raw.EventDate >= UInt16(15900), hits_raw.EventDate <= UInt16(15901), hits_raw.IsRefresh = Int16(0), hits_raw.DontCountHits = Int16(0)]
physical_plan
01)GlobalLimitExec: skip=1000, fetch=10
02)--SortPreservingMergeExec: [date_trunc(minute, m@0) ASC NULLS LAST], fetch=1010
@@ -1167,9 +1167,9 @@ physical_plan
05)--------AggregateExec: mode=FinalPartitioned, gby=[date_trunc(Utf8("minute"),to_timestamp_seconds(hits.EventTime))@0 as date_trunc(Utf8("minute"),to_timestamp_seconds(hits.EventTime))], aggr=[count(Int64(1))]
06)----------RepartitionExec: partitioning=Hash([date_trunc(Utf8("minute"),to_timestamp_seconds(hits.EventTime))@0], 4), input_partitions=4
07)------------AggregateExec: mode=Partial, gby=[date_trunc(minute, to_timestamp_seconds(EventTime@0)) as date_trunc(Utf8("minute"),to_timestamp_seconds(hits.EventTime))], aggr=[count(Int64(1))]
-08)--------------FilterExec: CounterID@2 = 62 AND CAST(CAST(EventDate@1 AS Int32) AS Date32) >= 2013-07-14 AND CAST(CAST(EventDate@1 AS Int32) AS Date32) <= 2013-07-15 AND IsRefresh@3 = 0 AND DontCountHits@4 = 0, projection=[EventTime@0]
+08)--------------FilterExec: CounterID@2 = 62 AND EventDate@1 >= 15900 AND EventDate@1 <= 15901 AND IsRefresh@3 = 0 AND DontCountHits@4 = 0, projection=[EventTime@0]
09)----------------RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1
-10)------------------DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/core/tests/data/clickbench_hits_10.parquet]]}, projection=[EventTime, EventDate, CounterID, IsRefresh, DontCountHits], file_type=parquet, predicate=CounterID@6 = 62 AND CAST(CAST(EventDate@5 AS Int32) AS Date32) >= 2013-07-14 AND CAST(CAST(EventDate@5 AS Int32) AS Date32) <= 2013-07-15 AND IsRefresh@15 = 0 AND DontCountHits@61 = 0, pruning_predicate=CounterID_null_count@2 != row_count@3 AND CounterID_m [...]
+10)------------------DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/datafusion/core/tests/data/clickbench_hits_10.parquet]]}, projection=[EventTime, EventDate, CounterID, IsRefresh, DontCountHits], file_type=parquet, predicate=CounterID@6 = 62 AND EventDate@5 >= 15900 AND EventDate@5 <= 15901 AND IsRefresh@15 = 0 AND DontCountHits@61 = 0, pruning_predicate=CounterID_null_count@2 != row_count@3 AND CounterID_min@0 <= 62 AND 62 <= CounterID_max@1 AND EventDate_null_count@5 != row_c [...]
query PI
SELECT DATE_TRUNC('minute', to_timestamp_seconds("EventTime")) AS M, COUNT(*) AS PageViews FROM hits WHERE "CounterID" = 62 AND "EventDate" >= '2013-07-14' AND "EventDate" <= '2013-07-15' AND "IsRefresh" = 0 AND "DontCountHits" = 0 GROUP BY DATE_TRUNC('minute', to_timestamp_seconds("EventTime")) ORDER BY DATE_TRUNC('minute', M) LIMIT 10 OFFSET 1000;
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org