Joel Robin created SPARK-61087:
----------------------------------
Summary: 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
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]
