[
https://issues.apache.org/jira/browse/SPARK-61087?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated SPARK-61087:
-----------------------------------
Labels: pull-request-available (was: )
> to_time, try_to_time and time_from_seconds return TIME(6) values with more
> than 6 fractional digits
> ---------------------------------------------------------------------------------------------------
>
> Key: SPARK-61087
> URL: https://issues.apache.org/jira/browse/SPARK-61087
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 5.0.0
> Environment: Apache Spark master branch, commit
> 352ac9bf22806d6aa2047bf3c62096697d0afeb1 (5.0.0-SNAPSHOT). Local mode, Java
> 21.
> Reporter: Joel Robin
> Priority: Major
> Labels: pull-request-available
>
> h3. Problem
> The functions {{to_time}}, {{try_to_time}} and {{time_from_seconds}} return
> the type {{TIME(6)}}. But the values that they return can have 7, 8 or 9
> fractional digits. A {{TIME(6)}} value must not have more than 6 fractional
> digits.
> Other parts of Spark obey this rule. {{CAST}} truncates a value to the
> precision of the target type. The Parquet reader truncates a value to the
> precision of the column. These three functions do not truncate the value.
> The {{TIME}} type is a preview feature. To use it, set
> {{spark.sql.timeType.enabled=true}}.
> h3. Steps to reproduce
> {code:sql}
> SET spark.sql.timeType.enabled=true;
> SELECT typeof(to_time('00:00:00.1234567')) AS type,
> to_time('00:00:00.1234567') AS to_time_v,
> try_to_time('00:00:00.123456789') AS try_to_time_v,
> time_from_seconds(0.0000001) AS from_secs_v;
> SELECT CAST(TIME'00:00:00.1234567' AS TIME(6)) AS expected_time6;
> SELECT to_time('00:00:00.1234567') = TIME'00:00:00.123456' AS eq,
> time_from_seconds(0.0000001) = TIME'00:00:00' AS eq2;
> SELECT time_from_seconds(0.0000001) AS v UNION SELECT TIME'00:00:00';
> {code}
> h3. Actual and expected results
> ||Expression||Actual result||Expected result||
> |{{typeof(to_time('00:00:00.1234567'))}}|{{time(6)}}|{{time(6)}}|
> |{{to_time('00:00:00.1234567')}}|{{00:00:00.1234567}}|{{00:00:00.123456}}|
> |{{try_to_time('00:00:00.123456789')}}|{{00:00:00.123456789}}|{{00:00:00.123456}}|
> |{{time_from_seconds(0.0000001)}}|{{00:00:00.0000001}}|{{00:00:00}}|
> |{{CAST(TIME'00:00:00.1234567' AS
> TIME(6))}}|{{00:00:00.123456}}|{{00:00:00.123456}}|
> |{{eq}}|{{false}}|{{true}}|
> |{{eq2}}|{{false}}|{{true}}|
> |{{UNION}} query|2 rows|1 row|
> This is not only a display problem. The collected {{java.time.LocalTime}}
> values have {{toNanoOfDay()}} equal to 123456700, 123456789 and 100.
> h3. Other effects
> 1. The problem also occurs with a column argument, not only with a constant.
> It also occurs when you give a format, for example
> {{to_time('00:00:00.1234567', 'HH:mm:ss.SSSSSSS')}}. It also occurs with a
> {{DOUBLE}} argument to {{time_from_seconds}}.
> 2. {{CAST(to_time(s) AS TIME(6))}} does not change the value, because the
> type of the input is already {{TIME(6)}}.
> 3. The result of a query can change with the optimizer rules. Use this view:
> {code:sql}
> CREATE OR REPLACE TEMP VIEW src AS
> SELECT CONCAT('00:00:00.123456', CAST(id + 7 AS STRING)) AS s FROM range(1);
> SELECT CAST(to_time(s) AS TIME(7)) = TIME'00:00:00.1234567' FROM src;
> {code}
> The query returns {{false}}. Then set
> {{spark.sql.optimizer.excludedRules=org.apache.spark.sql.catalyst.optimizer.UnwrapCastInBinaryComparison}}.
> Now the same query returns {{true}}. This rule expects that a {{TIME(p)}}
> value has a maximum of p fractional digits.
> 4. A write to Parquet and a read from Parquet change the value. Before the
> write, {{time_from_seconds(0.0000001) = TIME'00:00:00'}} is {{false}}. Write
> the value to a {{TIME(6)}} column of a Parquet table, and read it again. Now
> the comparison is {{true}}.
> h3. Cause
> 1. {{ToTime}} sets the result type to {{TimeType()}}, which is {{TIME(6)}}.
> {{ToTimeParser.parse}} returns the nanoseconds from the parser, and does not
> truncate them. {{try_to_time}} is {{TryEval(ToTime(...))}}, thus it has the
> same problem.
> 2. SPARK-57551 changed {{SparkDateTimeUtils.stringToTime}} to keep the digits
> 7 to 9. That change added truncation to {{CAST}}, but not to {{ToTime}}.
> Before SPARK-57551, the default parser kept only microseconds. I found this
> in the diff. I did not run the earlier commit.
> 3. {{TimeFromBase}} sets the result type to {{TIME(6)}}.
> {{DateTimeUtils.timeFromSeconds(Decimal)}} and
> {{DateTimeUtils.timeFromSeconds(Double)}} multiply the seconds by
> {{NANOS_PER_SECOND}}. They do not truncate the result. This code has not
> changed since SPARK-54442.
> h3. Possible fix
> Truncate the result to the precision of the result type with
> {{DateTimeUtils.truncateTimeToPrecision}}. {{CAST}} uses the same function.
> h3. Related issues
> * SPARK-60547 reports the same problem for {{createDataFrame}}.
> * The JSON, CSV and XML readers have a similar problem when the schema has a
> {{TIME(p)}} column. This example returns {{00:00:00.1234567}}:
> {code:sql}
> SELECT from_json('{"t":"00:00:00.1234567"}', 't TIME(6)').t;
> {code}
> This ticket does not include these readers.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
