[
https://issues.apache.org/jira/browse/SPARK-42635?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Chenhao Li updated SPARK-42635:
-------------------------------
Description:
# When the time is close to daylight saving time transition, the result may be
discontinuous and not monotonic.
We currently have:
{code:scala}
scala> spark.conf.set("spark.sql.session.timeZone", "America/Los_Angeles")
scala> spark.sql("select timestampadd(second, 24 * 3600 - 1,
timestamp'2011-03-12 03:00:00')").show
+------------------------------------------------------------------------+
|timestampadd(second, ((24 * 3600) - 1), TIMESTAMP '2011-03-12 03:00:00')|
+------------------------------------------------------------------------+
| 2011-03-13 03:59:59|
+------------------------------------------------------------------------+
scala> spark.sql("select timestampadd(second, 24 * 3600, timestamp'2011-03-12
03:00:00')").show
+------------------------------------------------------------------+
|timestampadd(second, (24 * 3600), TIMESTAMP '2011-03-12 03:00:00')|
+------------------------------------------------------------------+
| 2011-03-13 03:00:00|
+------------------------------------------------------------------+ {code}
In the second query, adding one more second will set the time back one hour
instead. Plus, there are only {{23 * 3600}} seconds from {{2011-03-12
03:00:00}} to {{2011-03-13 03:00:00}}, instead of {{24 * 3600}} seconds, due to
the daylight saving time transition.
The root cause of the problem is the Spark code at
[https://github.com/apache/spark/blob/master/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala#L790]
wrongly assumes every day has {{MICROS_PER_DAY}} seconds, and does the day and
time-in-day split before looking at the timezone.
2. Adding month, quarter, and year silently ignores Int overflow during unit
conversion.
The root cause is
[https://github.com/apache/spark/blob/master/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala#L1246].
{{quantity}} is multiplied by {{3}} or {{MONTHS_PER_YEAR}} without checking
overflow. Note that we do have overflow checking in adding the amount to the
timestamp, so the behavior is inconsistent.
This can cause counter-intuitive results like this:
{code:scala}
scala> spark.sql("select timestampadd(quarter, 1431655764,
timestamp'1970-01-01')").show
+------------------------------------------------------------------+
|timestampadd(quarter, 1431655764, TIMESTAMP '1970-01-01 00:00:00')|
+------------------------------------------------------------------+
| 1969-09-01 00:00:00|
+------------------------------------------------------------------+{code}
3. Adding sub-month units (week, day, hour, minute, second, millisecond,
microsecond)silently ignores Long overflow during unit conversion.
This is similar to the previous problem:
{code:scala}
scala> spark.sql("select timestampadd(day, 106751992,
timestamp'1970-01-01')").show(false)
+-------------------------------------------------------------+
|timestampadd(day, 106751992, TIMESTAMP '1970-01-01 00:00:00')|
+-------------------------------------------------------------+
|-290308-12-22 15:58:10.448384 |
+-------------------------------------------------------------+{code}
was:
# When the time is close to daylight saving time transition, the result may be
discontinuous and not monotonic.
We currently have:
{code:java}
scala> spark.conf.set("spark.sql.session.timeZone", "America/Los_Angeles")
scala> spark.sql("select timestampadd(second, 24 * 3600 - 1,
timestamp'2011-03-12 03:00:00')").show
+------------------------------------------------------------------------+
|timestampadd(second, ((24 * 3600) - 1), TIMESTAMP '2011-03-12 03:00:00')|
+------------------------------------------------------------------------+
| 2011-03-13 03:59:59|
+------------------------------------------------------------------------+
scala> spark.sql("select timestampadd(second, 24 * 3600, timestamp'2011-03-12
03:00:00')").show
+------------------------------------------------------------------+
|timestampadd(second, (24 * 3600), TIMESTAMP '2011-03-12 03:00:00')|
+------------------------------------------------------------------+
| 2011-03-13 03:00:00|
+------------------------------------------------------------------+ {code}
In the second query, adding one more second will set the time back one hour
instead. Plus, there are only {{23 * 3600}} seconds from {{2011-03-12
03:00:00}} to {{2011-03-13 03:00:00}}, instead of {{24 * 3600}} seconds, due to
the daylight saving time transition.
The root cause of the problem is the Spark code at wrongly assumes every day
has {{MICROS_PER_DAY}} seconds, and does the day and time-in-day split before
looking at the timezone.
2. Adding month, quarter, and year silently ignores Int overflow during unit
conversion.
The root cause is here . quantity is multiplied by 3 or MONTHS_PER_YEARwithout
checking overflow. Note that we do have overflow checking in adding the amount
to the timestamp, so the behavior is inconsistent.
This can cause counter-intuitive results like this:
{{scala> spark.sql("select timestampadd(quarter, 1431655764,
timestamp'1970-01-01')").show
+------------------------------------------------------------------+
|timestampadd(quarter, 1431655764, TIMESTAMP '1970-01-01 00:00:00')|
+------------------------------------------------------------------+
|1969-09-01 00:00:00|
{+}------------------------------------------------------------------{+}}}
3. Adding sub-month units (week, day, hour, minute, second, millisecond,
microsecond)silently ignores Long overflow during unit conversion.
This is similar to the previous problem:
{{scala> spark.sql("select timestampadd(day, 106751992,
timestamp'1970-01-01')").show(false)
+-------------------------------------------------------------+
|timestampadd(day, 106751992, TIMESTAMP '1970-01-01 00:00:00')|
+-------------------------------------------------------------+
|-290308-12-22 15:58:10.448384|
{+}-------------------------------------------------------------{+}}}
> Several counter-intuitive behaviours in the TimestampAdd expression
> -------------------------------------------------------------------
>
> Key: SPARK-42635
> URL: https://issues.apache.org/jira/browse/SPARK-42635
> Project: Spark
> Issue Type: Bug
> Components: Spark Core, SQL
> Affects Versions: 3.3.0, 3.3.1, 3.3.2
> Reporter: Chenhao Li
> Priority: Major
>
> # When the time is close to daylight saving time transition, the result may
> be discontinuous and not monotonic.
> We currently have:
> {code:scala}
> scala> spark.conf.set("spark.sql.session.timeZone", "America/Los_Angeles")
> scala> spark.sql("select timestampadd(second, 24 * 3600 - 1,
> timestamp'2011-03-12 03:00:00')").show
> +------------------------------------------------------------------------+
> |timestampadd(second, ((24 * 3600) - 1), TIMESTAMP '2011-03-12 03:00:00')|
> +------------------------------------------------------------------------+
> | 2011-03-13 03:59:59|
> +------------------------------------------------------------------------+
> scala> spark.sql("select timestampadd(second, 24 * 3600, timestamp'2011-03-12
> 03:00:00')").show
> +------------------------------------------------------------------+
> |timestampadd(second, (24 * 3600), TIMESTAMP '2011-03-12 03:00:00')|
> +------------------------------------------------------------------+
> | 2011-03-13 03:00:00|
> +------------------------------------------------------------------+ {code}
>
> In the second query, adding one more second will set the time back one hour
> instead. Plus, there are only {{23 * 3600}} seconds from {{2011-03-12
> 03:00:00}} to {{2011-03-13 03:00:00}}, instead of {{24 * 3600}} seconds, due
> to the daylight saving time transition.
> The root cause of the problem is the Spark code at
> [https://github.com/apache/spark/blob/master/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala#L790]
> wrongly assumes every day has {{MICROS_PER_DAY}} seconds, and does the day
> and time-in-day split before looking at the timezone.
> 2. Adding month, quarter, and year silently ignores Int overflow during unit
> conversion.
> The root cause is
> [https://github.com/apache/spark/blob/master/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala#L1246].
> {{quantity}} is multiplied by {{3}} or {{MONTHS_PER_YEAR}} without checking
> overflow. Note that we do have overflow checking in adding the amount to the
> timestamp, so the behavior is inconsistent.
> This can cause counter-intuitive results like this:
> {code:scala}
> scala> spark.sql("select timestampadd(quarter, 1431655764,
> timestamp'1970-01-01')").show
> +------------------------------------------------------------------+
> |timestampadd(quarter, 1431655764, TIMESTAMP '1970-01-01 00:00:00')|
> +------------------------------------------------------------------+
> | 1969-09-01 00:00:00|
> +------------------------------------------------------------------+{code}
> 3. Adding sub-month units (week, day, hour, minute, second, millisecond,
> microsecond)silently ignores Long overflow during unit conversion.
> This is similar to the previous problem:
> {code:scala}
> scala> spark.sql("select timestampadd(day, 106751992,
> timestamp'1970-01-01')").show(false)
> +-------------------------------------------------------------+
> |timestampadd(day, 106751992, TIMESTAMP '1970-01-01 00:00:00')|
> +-------------------------------------------------------------+
> |-290308-12-22 15:58:10.448384 |
> +-------------------------------------------------------------+{code}
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
