[SPARK-31868] Wrong results for week-based-year

• Type: Bug
• Status: Resolved
• Priority: Blocker
• Resolution: Won't Fix
• Affects Version/s: 3.0.0, 3.1.0
• Component/s: SQL
• Labels: correctness
• Assignee: Unassigned
• Reporter: Kent Yao 2
• Created: 29/May/20 08:42
• Updated: 04/Jun/20 04:34
• Resolved: 04/Jun/20 04:34

Description

spark-sql> set spark.sql.legacy.timeParserPolicy=exception;
spark.sql.legacy.timeParserPolicy	exception
spark-sql> explain select to_timestamp('1969-01-01', 'YYYY-MM-dd')
         > ;
== Physical Plan ==
*(1) Project [-28800000000 AS to_timestamp(1969-01-01, YYYY-MM-dd)#37]
+- *(1) Scan OneRowRelation[]


spark-sql> set spark.sql.legacy.timeParserPolicy=legacy;
spark.sql.legacy.timeParserPolicy	legacy
spark-sql> explain select to_timestamp('1969-01-01', 'YYYY-MM-dd');
== Physical Plan ==
*(1) Project [-31824000000000 AS to_timestamp(1969-01-01, YYYY-MM-dd)#53]
+- *(1) Scan OneRowRelation[]


spark-sql> set spark.sql.legacy.timeParserPolicy=exception;
spark.sql.legacy.timeParserPolicy	exception
spark-sql> explain select to_timestamp('1969-01-01', 'YYYY-MM-dd');
== Physical Plan ==
*(1) Project [-28800000000 AS to_timestamp(1969-01-01, YYYY-MM-dd)#69]
+- *(1) Scan OneRowRelation[]


spark-sql> select to_timestamp('1969-01-01', 'YYYY-MM-dd');
1970-01-01 00:00:00
spark-sql> set spark.sql.legacy.timeParserPolicy=legacy;
spark.sql.legacy.timeParserPolicy	legacy
spark-sql> explain select to_timestamp('1969-01-01', 'YYYY-MM-dd');
== Physical Plan ==
*(1) Project [-31824000000000 AS to_timestamp(1969-01-01, YYYY-MM-dd)#87]
+- *(1) Scan OneRowRelation[]


spark-sql> select to_timestamp('1969-01-01', 'YYYY-MM-dd');
1968-12-29 00:00:00
spark-sql> select to_timestamp('1969-01-01', 'yyyy-MM-dd');
1969-01-01 00:00:00
spark-sql> set spark.sql.legacy.timeParserPolicy=exception;
spark.sql.legacy.timeParserPolicy	exception
spark-sql> select to_timestamp('1969-01-01', 'yyyy-MM-dd');
1969-01-01 00:00:00

== Physical Plan ==
*(1) Project [-28800000000 AS to_timestamp(2000-01-01, YYYY-MM-dd)#73]
+- *(1) Scan OneRowRelation[]

spark-sql> select to_timestamp('2000-01-01', 'YYYY-MM-dd');
1970-01-01 00:00:00

Seems that `-28800000000` is static

Issue Links

• is related to: SPARK-31892 Disable week-based date filed for parsing
• links to: [Github] Pull Request #28674 (yaooqinn)
• links to: [Github] Pull Request #28674 (yaooqinn)