[ 
https://issues.apache.org/jira/browse/SPARK-61943?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

ASF GitHub Bot updated SPARK-61943:
-----------------------------------
    Labels: correctness pull-request-available  (was: correctness)

> 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.2.0, 4.1.1
>            Reporter: Yaoxuan Wu
>            Priority: Major
>              Labels: correctness, pull-request-available
>
> 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