[
https://issues.apache.org/jira/browse/IGNITE-25313?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17949895#comment-17949895
]
Maksim Zhuravkov commented on IGNITE-25313:
-------------------------------------------
FF4 - FF9 are completely broken because if this. Looks like SimpleDateFormat
always assumes that fractions are milliseconds and then translated them to
seconds when there are more than 1000 of them)
{code:java}
sql-cli> SELECT CAST('23:02:03.123456789' AS TIME FORMAT 'hh24:mi:ss.FF9')
╔════════════════════════════════════════════════════════════╗
║ CAST('23:02:03.123456789' AS TIME FORMAT 'hh24:mi:ss.FF9') ║
╠════════════════════════════════════════════════════════════╣
║ 09:19:39 ║
╚════════════════════════════════════════════════════════════╝
sql-cli> SELECT CAST('23:02:03.12345678' AS TIME FORMAT 'hh24:mi:ss.FF8')
╔═══════════════════════════════════════════════════════════╗
║ CAST('23:02:03.12345678' AS TIME FORMAT 'hh24:mi:ss.FF8') ║
╠═══════════════════════════════════════════════════════════╣
║ 02:27:48 ║
╚═══════════════════════════════════════════════════════════╝
sql-cli> SELECT CAST('23:02:03.1234567' AS TIME FORMAT 'hh24:mi:ss.FF7')
╔══════════════════════════════════════════════════════════╗
║ CAST('23:02:03.1234567' AS TIME FORMAT 'hh24:mi:ss.FF7') ║
╠══════════════════════════════════════════════════════════╣
║ 23:22:37 ║
╚══════════════════════════════════════════════════════════╝
sql-cli> SELECT CAST('23:02:03.1234567' AS TIME FORMAT 'hh24:mi:ss.FF6')
╔══════════════════════════════════════════════════════════╗
║ CAST('23:02:03.1234567' AS TIME FORMAT 'hh24:mi:ss.FF6') ║
╠══════════════════════════════════════════════════════════╣
║ 23:22:37 ║
╚══════════════════════════════════════════════════════════╝
{code}
> Sql. Time, Timestamp, Timestamp LTZ. Precision. CAST FORMAT confusing
> behaviour when translating milliseconds
> -------------------------------------------------------------------------------------------------------------
>
> Key: IGNITE-25313
> URL: https://issues.apache.org/jira/browse/IGNITE-25313
> Project: Ignite
> Issue Type: Bug
> Components: sql
> Reporter: Maksim Zhuravkov
> Priority: Major
> Labels: ignite-3
>
> 1. 100ms -> 1 ms caused by calcite's parser uses SimpleDateFormat that parses
> milliseconds this way.
> 2. parse operation completely loses milliseconds
> {noformat}
> // TIME
> SELECT CAST('12:00:00.1' AS TIME(3) FORMAT 'hh24:mi:ss.ff3');
> 12:00:00.001
> // milliseconds are lost
> SELECT CAST('12:00:00.1' AS TIME(2) FORMAT 'hh24:mi:ss.ff2');
> 12:00
> // milliseconds are lost
> SELECT CAST('12:00:00.1' AS TIME(1) FORMAT 'hh24:mi:ss.ff1');
> 12:00
> // TIMESTAMP
> // 100 ms -> 1 ms
> SELECT CAST('2020-05-01 12:00:00.1' AS TIMESTAMP(3) FORMAT 'yyyy-MM-dd
> hh24:mi:ss.ff3');
> 2020-05-01T12:00:00.001
> // milliseconds are lost
> SELECT CAST('2020-05-01 12:00:00.1' AS TIMESTAMP(2) FORMAT 'yyyy-MM-dd
> hh24:mi:ss.ff2');
> 2020-05-01T12:00
> // milliseconds are lost
> SELECT CAST('2020-05-01 12:00:00.1' AS TIMESTAMP(1) FORMAT 'yyyy-MM-dd
> hh24:mi:ss.ff1');
> 2020-05-01T12:00
> // TIMESTAMP LTZ
> // 100 ms -> 1 ms (calcite's formatter uses SimpleDateFormat)
> SELECT CAST('2020-05-01 12:00:00.1' AS TIMESTAMP(3) WITH LOCAL TIME ZONE
> FORMAT 'yyyy-MM-dd hh24:mi:ss.ff3');
> 2020-05-01T09:00:00.001Z
> SELECT CAST('2020-05-01 12:00:00.1' AS TIMESTAMP(2) WITH LOCAL TIME ZONE
> FORMAT 'yyyy-MM-dd hh24:mi:ss.ff2');
> 2020-05-01T09:00:00.001Z
> SELECT CAST('2020-05-01 12:00:00.1' AS TIMESTAMP(1) WITH LOCAL TIME ZONE
> FORMAT 'yyyy-MM-dd hh24:mi:ss.ff1');
> 2020-05-01T09:00:00.001Z
> {noformat}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)