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]

Reply via email to