[SPARK-35581] Casting special strings to DATE/TIMESTAMP returns inconsistent results

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 3.2.0
• Fix Version/s: 3.2.0
• Component/s: SQL
• Assignee: Max Gekk
• Reporter: Max Gekk
• Created: 31/May/21 19:55
• Updated: 25/Aug/21 20:41
• Resolved: 01/Jun/21 12:29

Description

When casting the special values "now", "today", "tomorrow", and "yesterday" to DATE/TIMESTAMP, Spark may return inconsistent results.

Looks like Spark runs the expression on each executor, on every row independently. So the results could differ across executors if they have different system time, and across rows because of the resolution of "now".

https://github.com/databricks/runtime/blob/master/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala#L876

Issue Links

• is duplicated by: SPARK-36544 Special strings for dates are no longer automatically converted without the "date" keyword
• relates to: SPARK-28141 Date type can not accept special values
• relates to: SPARK-29012 Timestamp type can not accept special values
• links to: [Github] Pull Request #32714 (MaxGekk)
• links to: [Github] Pull Request #33840 (MaxGekk)