Ramin Gharib created FLINK-41591:
------------------------------------
Summary: PARSE_XML keeps only microseconds of a dateTime in
1677-09-21 00:12:43
Key: FLINK-41591
URL: https://issues.apache.org/jira/browse/FLINK-41591
Project: Flink
Issue Type: Bug
Components: Table SQL / Runtime
Reporter: Ramin Gharib
A {{dateTime}} with digits below a microsecond keeps its nanoseconds if it is
in the range of a nanosecond timestamp, from {{1677-09-21 00:12:43.145224192}}
to {{2262-04-11 23:47:16.854775807}} UTC. In the first second of that range,
PARSE_XML keeps only microseconds.
{code:sql}
SELECT x, CAST(PARSE_XML('<a
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="dateTime">' ||
x || '</a>')['a'] AS TIMESTAMP(9)) AS ts
FROM (VALUES ('1677-09-21T00:12:43.145224192'),
('1677-09-21T00:12:43.999999999'), ('1677-09-21T00:12:44.000000001')) AS t(x);
{code}
||x||Expected||Actual||
|1677-09-21T00:12:43.145224192|1677-09-21 00:12:43.145224192|1677-09-21
00:12:43.145224000|
|1677-09-21T00:12:43.999999999|1677-09-21 00:12:43.999999999|1677-09-21
00:12:43.999999000|
|1677-09-21T00:12:44.000000001|1677-09-21 00:12:44.000000001|1677-09-21
00:12:44.000000001|
The same in batch and streaming mode. The upper bound works:
{{2262-04-11T23:47:16.854775807}} keeps its nanoseconds.
h3. Cause
{{BinaryVariantUtil#nanosSinceEpoch}} returns
{{ChronoUnit.NANOS.between(Instant.EPOCH, instant)}}. For 00:12:43, the epoch
second is -9223372037, and {{Instant#until}} multiplies it by 10^9 before it
adds the nanoseconds of the second. The product overflows, although the sum
fits into a {{long}}. The method throws, and {{XmlToVariantParser}} falls back
to microseconds. From the code, {{BinaryVariantBuilder}} calls the same method
for an {{Instant}} or a {{LocalDateTime}} without catching it, so it rejects
the same second.
h3. Proposal
* Compute the count from the epoch second plus one and the nanoseconds minus
10^9 when the epoch second is negative, so the intermediate product stays in
range.
* Test the first and the last value of the range, and one nanosecond outside
each.
* The documentation page "XML and VARIANT" now gives the range from
{{1677-09-21 00:12:44}}. Restore the full range there with the fix.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)