Yaoxuan Wu created SPARK-61943:
----------------------------------
Summary: Recursive CTE changes DATE / TIMESTAMP values: steps with
few rows are converted to a LocalRelation through external Rows (java.sql.Date
/ Timestamp rebase)
Key: SPARK-61943
URL: https://issues.apache.org/jira/browse/SPARK-61943
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 4.1.1, 4.2.0
Reporter: Yaoxuan Wu
A recursive CTE changes DATE and TIMESTAMP values that do not survive a round
trip through java.sql.Date / java.sql.Timestamp. With default settings, a DATE
in the Julian-Gregorian gap comes back as 1582-10-15, and this already happens
in the anchor rows. No error is raised.
{code:sql}
CREATE OR REPLACE TEMP VIEW t AS SELECT /*+ REPARTITION(2) */ * FROM VALUES
(1, DATE'1582-10-10'),
(2, DATE'2020-01-01') AS v(id, d);
SELECT id, CAST(d AS STRING) FROM t ORDER BY id;
-- 1 1582-10-10
-- 2 2020-01-01
WITH RECURSIVE r AS (
SELECT id, d, 0 AS lvl FROM t
UNION ALL
SELECT id, d, lvl + 1 FROM r WHERE lvl < 1)
SELECT lvl, id, CAST(d AS STRING) FROM r ORDER BY id, lvl;
-- expected: 0 1 1582-10-10 / 1 1 1582-10-10 / 0 2 2020-01-01 / 1 2 2020-01-01
-- actual: 0 1 1582-10-15 / 1 1 1582-10-15 / 0 2 2020-01-01 / 1 2 2020-01-01
{code}
* Same on 4.1.1 and 4.2.0, in any JVM time zone.
* The result is correct with
{{spark.sql.cteRecursionAnchorRowsLimitToConvertToLocalRelation = 0}}, or with
{{spark.sql.datetime.java8API.enabled = true}}.
* TIMESTAMPs are affected the same way when the Julian rebase isn't injective.
For example, with the JVM zone America/Los_Angeles, {{TIMESTAMP'1883-11-18
19:52:58Z'}} (in that zone's LMT-to-PST overlap) comes back 422 seconds later:
{{unix_micros}} -2717640000000000 instead of -2717640422000000.
* In the query where this was found, the changed timestamp then hashed
differently in the recursive step's {{WHERE pmod(hash(...), 3) <> 0}}, so the
recursion returned a different number of rows than the same query unrolled by
hand.
Cause (master d3f4a73a, UnionLoopExec.scala, {{executeAndCacheAndCount}}):
{code:scala}
if (count <= localRelationLimit) {
val local = LocalRelation.fromExternalRows(df.logicalPlan.output,
df.collect().toIndexedSeq)
{code}
1. The anchor and every recursion step with at most
{{cteRecursionAnchorRowsLimitToConvertToLocalRelation}} rows (default 100) are
replaced by a {{LocalRelation}} built from {{df.collect()}}, which returns
external {{Row}}s.
2. With the default {{java8API.enabled = false}}, a DATE becomes a
{{java.sql.Date}} and a TIMESTAMP a {{java.sql.Timestamp}}
({{DateTimeUtils.toJavaDate}} / {{toJavaTimestamp}}). Both rebase from the
Proleptic Gregorian calendar to the hybrid Julian calendar.
{{fromExternalRows}} then converts them back with {{CatalystTypeConverters}}.
3. The hybrid calendar has no 1582-10-05..14, so those dates move to
1582-10-15. In a zone with an LMT overlap, the Gregorian-to-Julian rebase maps
two instants to the same value.
The collected rows could stay internal: for example,
{{df.queryExecution.executedPlan.executeCollect()}} copied into
{{LocalRelation(output, rows)}}. The {{Project(_, OneRowRelation)}} case just
above already builds the {{LocalRelation}} from {{InternalRow}}s.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]