Skip to content

Commit e41c4df

Browse files
authored
Date to Timestamp cast (#5140)
* ts to date cast * Date to Timestamp Cast
1 parent f0c0958 commit e41c4df

4 files changed

Lines changed: 95 additions & 30 deletions

File tree

‎datafusion/core/tests/sql/timestamp.rs‎

Lines changed: 50 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1572,7 +1572,6 @@ async fn test_current_time() -> Result<()> {
15721572
#[tokio::test]
15731573
async fn test_ts_dt_binary_ops() -> Result<()> {
15741574
let ctx = SessionContext::new();
1575-
15761575
// test cast in where clause
15771576
let sql =
15781577
"select count(1) result from (select now() as n) a where n = '2000-01-01'::date";
@@ -1631,5 +1630,55 @@ async fn test_ts_dt_binary_ops() -> Result<()> {
16311630

16321631
assert_batches_eq!(expected, &results);
16331632

1633+
//test cast path timestamp date using literals
1634+
let sql = "select '2000-01-01'::timestamp >= '2000-01-01'::date";
1635+
let df = ctx.sql(sql).await.unwrap();
1636+
1637+
let plan = df.explain(true, false)?.collect().await?;
1638+
let batch = &plan[0];
1639+
let mut res: Option<String> = None;
1640+
for row in 0..batch.num_rows() {
1641+
if &array_value_to_string(batch.column(0), row)?
1642+
== "logical_plan after type_coercion"
1643+
{
1644+
res = Some(array_value_to_string(batch.column(1), row)?);
1645+
break;
1646+
}
1647+
}
1648+
assert_eq!(res, Some("Projection: CAST(Utf8(\"2000-01-01\") AS Timestamp(Nanosecond, None)) >= CAST(CAST(Utf8(\"2000-01-01\") AS Date32) AS Timestamp(Nanosecond, None))\n EmptyRelation".to_string()));
1649+
1650+
//test cast path timestamp date using function
1651+
let sql = "select now() >= '2000-01-01'::date";
1652+
let df = ctx.sql(sql).await.unwrap();
1653+
1654+
let plan = df.explain(true, false)?.collect().await?;
1655+
let batch = &plan[0];
1656+
let mut res: Option<String> = None;
1657+
for row in 0..batch.num_rows() {
1658+
if &array_value_to_string(batch.column(0), row)?
1659+
== "logical_plan after type_coercion"
1660+
{
1661+
res = Some(array_value_to_string(batch.column(1), row)?);
1662+
break;
1663+
}
1664+
}
1665+
assert_eq!(res, Some("Projection: CAST(now() AS Timestamp(Nanosecond, None)) >= CAST(CAST(Utf8(\"2000-01-01\") AS Date32) AS Timestamp(Nanosecond, None))\n EmptyRelation".to_string()));
1666+
1667+
let sql = "select now() = current_date()";
1668+
let df = ctx.sql(sql).await.unwrap();
1669+
1670+
let plan = df.explain(true, false)?.collect().await?;
1671+
let batch = &plan[0];
1672+
let mut res: Option<String> = None;
1673+
for row in 0..batch.num_rows() {
1674+
if &array_value_to_string(batch.column(0), row)?
1675+
== "logical_plan after type_coercion"
1676+
{
1677+
res = Some(array_value_to_string(batch.column(1), row)?);
1678+
break;
1679+
}
1680+
}
1681+
assert_eq!(res, Some("Projection: CAST(now() AS Timestamp(Nanosecond, None)) = CAST(currentdate() AS Timestamp(Nanosecond, None))\n EmptyRelation".to_string()));
1682+
16341683
Ok(())
16351684
}

‎datafusion/expr/src/type_coercion/binary.rs‎

Lines changed: 24 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -541,33 +541,30 @@ fn is_time_with_valid_unit(datatype: DataType) -> bool {
541541
fn temporal_coercion(lhs_type: &DataType, rhs_type: &DataType) -> Option<DataType> {
542542
use arrow::datatypes::DataType::*;
543543
match (lhs_type, rhs_type) {
544-
(Date64, Date32) => Some(Date64),
545-
(Date32, Date64) => Some(Date64),
546-
(Utf8, Date32) => Some(Date32),
547-
(Date32, Utf8) => Some(Date32),
548-
(Utf8, Date64) => Some(Date64),
549-
(Date64, Utf8) => Some(Date64),
550-
(Utf8, Time32(unit)) => match is_time_with_valid_unit(Time32(unit.clone())) {
551-
false => None,
552-
true => Some(Time32(unit.clone())),
553-
},
554-
(Time32(unit), Utf8) => match is_time_with_valid_unit(Time32(unit.clone())) {
555-
false => None,
556-
true => Some(Time32(unit.clone())),
557-
},
558-
(Utf8, Time64(unit)) => match is_time_with_valid_unit(Time64(unit.clone())) {
559-
false => None,
560-
true => Some(Time64(unit.clone())),
561-
},
562-
(Time64(unit), Utf8) => match is_time_with_valid_unit(Time64(unit.clone())) {
563-
false => None,
564-
true => Some(Time64(unit.clone())),
565-
},
566-
(Timestamp(_, tz), Utf8) => Some(Timestamp(TimeUnit::Nanosecond, tz.clone())),
567-
(Utf8, Timestamp(_, tz)) => Some(Timestamp(TimeUnit::Nanosecond, tz.clone())),
568-
// TODO: need to investigate the result type for the comparison between timestamp and date
569-
(Timestamp(_, _), Date32) => Some(Date32),
570-
(Timestamp(_, _), Date64) => Some(Date64),
544+
(Date64, Date32) | (Date32, Date64) => Some(Date64),
545+
(Utf8, Date32) | (Date32, Utf8) => Some(Date32),
546+
(Utf8, Date64) | (Date64, Utf8) => Some(Date64),
547+
(Utf8, Time32(unit)) | (Time32(unit), Utf8) => {
548+
match is_time_with_valid_unit(Time32(unit.clone())) {
549+
false => None,
550+
true => Some(Time32(unit.clone())),
551+
}
552+
}
553+
(Utf8, Time64(unit)) | (Time64(unit), Utf8) => {
554+
match is_time_with_valid_unit(Time64(unit.clone())) {
555+
false => None,
556+
true => Some(Time64(unit.clone())),
557+
}
558+
}
559+
(Timestamp(_, tz), Utf8) | (Utf8, Timestamp(_, tz)) => {
560+
Some(Timestamp(TimeUnit::Nanosecond, tz.clone()))
561+
}
562+
(Timestamp(_, None), Date32) | (Date32, Timestamp(_, None)) => {
563+
Some(Timestamp(TimeUnit::Nanosecond, None))
564+
}
565+
(Timestamp(_, _tz), Date32) | (Date32, Timestamp(_, _tz)) => {
566+
Some(Timestamp(TimeUnit::Nanosecond, None))
567+
}
571568
(Timestamp(lhs_unit, lhs_tz), Timestamp(rhs_unit, rhs_tz)) => {
572569
let tz = match (lhs_tz, rhs_tz) {
573570
// can't cast across timezones

‎datafusion/expr/src/type_coercion/functions.rs‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -174,8 +174,8 @@ pub fn can_coerce_from(type_into: &DataType, type_from: &DataType) -> bool {
174174
| Float64
175175
| Decimal128(_, _)
176176
),
177-
Timestamp(TimeUnit::Nanosecond, None) => {
178-
matches!(type_from, Null | Timestamp(_, None))
177+
Timestamp(TimeUnit::Nanosecond, _) => {
178+
matches!(type_from, Null | Timestamp(_, _) | Date32)
179179
}
180180
Utf8 | LargeUtf8 => true,
181181
Null => can_cast_types(type_from, type_into),

‎datafusion/optimizer/src/type_coercion.rs‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1038,4 +1038,23 @@ mod test {
10381038
Ok(())
10391039
// TODO add more test for this
10401040
}
1041+
1042+
#[test]
1043+
fn binary_op_date32_eq_ts() -> Result<()> {
1044+
let expr = cast(
1045+
lit("1998-03-18"),
1046+
DataType::Timestamp(arrow::datatypes::TimeUnit::Nanosecond, None),
1047+
)
1048+
.eq(cast(lit("1998-03-18"), DataType::Date32));
1049+
let empty = Arc::new(LogicalPlan::EmptyRelation(EmptyRelation {
1050+
produce_one_row: false,
1051+
schema: Arc::new(DFSchema::empty()),
1052+
}));
1053+
let plan = LogicalPlan::Projection(Projection::try_new(vec![expr], empty)?);
1054+
dbg!(&plan);
1055+
let expected =
1056+
"Projection: CAST(Utf8(\"1998-03-18\") AS Timestamp(Nanosecond, None)) = CAST(CAST(Utf8(\"1998-03-18\") AS Date32) AS Timestamp(Nanosecond, None))\n EmptyRelation";
1057+
assert_optimized_plan_eq(&plan, expected)?;
1058+
Ok(())
1059+
}
10411060
}

0 commit comments

Comments
 (0)