[
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]