[
https://issues.apache.org/jira/browse/IGNITE-24825?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pavel Pereslegin updated IGNITE-24825:
--------------------------------------
Description:
Currently the value of YEAR in temporal types (DATE, DATETIME, TIMESTAMP) is
not limited.
This leads to confusion, for example the year in a binary tuple for
date/datetime is limited to 3 bits (silent overflow happens when handling
larger values).
At the same time, SQL engine currently does not support a year less than or
equal to zero ().
{{Timestamp}} ({{TIMESTAMP_WITH_LOCAL_TIME_ZONE}}) can handle larger values,
but firstly the SQL engine cannot correctly convert such a date into a VARCHAR
(see {{org.apache.calcite.avatica.util.DateTimeUtils.unixDateToString}}), and
secondly it looks that we need to have consistency between {{TIMESTAMP}} and
{{TIMESTAMP_WITH_LOCAL_TIME_ZONE}} types.
Definition of done:
* Forbid date/datetime/timestamp values with year <=0 and year > 9999 (should
be restricted in kv/sql/jdbc/etc (on a binary tuple level probably))
* Ensure interval arithmetic produces corresponding error when overflow happens
(e.g. {{select timestamp '9999-12-31 00:00:00' + INTERVAL 1 DAYS}}).
* Ensure cast with format does not accept more then 4 digit (e.g. {{SELECT
cast('10000' as date format 'YYYYY')}}).
was:
Currently the value of YEAR in temporal types (DATE, DATETIME, TIMESTAMP) is
not limited.
This leads to confusion, for example the year in a binary tuple for
date/datetime is limited to 3 bits (silent overflow happens when handling
larger values).
At the same time, SQL engine currently does not support a year less than or
equal to zero ().
{{Timestamp}} ({{TIMESTAMP_WITH_LOCAL_TIME_ZONE}}) can handle larger values,
but firstly the SQL engine cannot correctly convert such a date into a VARCHAR
(see {{org.apache.calcite.avatica.util.DateTimeUtils.unixDateToString}}), and
secondly it looks that we need to have consistency between {{TIMESTAMP}} and
{{TIMESTAMP_WITH_LOCAL_TIME_ZONE}} types.
Definition of done:
* Forbid date/datetime/timestamp values with year <=0 and year > 9999 (should
be restricted in kv/sql/jdbc/etc (on a binary tuple level probably))
* Ensure interval arithmetic produces corresponding error when overflow happens
(e.g. {{select cast(timestamp '9999-12-31 00:00:00' + INTERVAL 1 DAYS)}}).
* Ensure cast with format does not accept more then 4 digit (e.g. {{SELECT
cast('10000' as date format 'YYYYY')}}).
> Sql. Limit YEAR value to range 1-9999
> -------------------------------------
>
> Key: IGNITE-24825
> URL: https://issues.apache.org/jira/browse/IGNITE-24825
> Project: Ignite
> Issue Type: Improvement
> Components: sql
> Reporter: Pavel Pereslegin
> Priority: Major
> Labels: ignite-3
>
> Currently the value of YEAR in temporal types (DATE, DATETIME, TIMESTAMP) is
> not limited.
> This leads to confusion, for example the year in a binary tuple for
> date/datetime is limited to 3 bits (silent overflow happens when handling
> larger values).
> At the same time, SQL engine currently does not support a year less than or
> equal to zero ().
> {{Timestamp}} ({{TIMESTAMP_WITH_LOCAL_TIME_ZONE}}) can handle larger values,
> but firstly the SQL engine cannot correctly convert such a date into a
> VARCHAR (see
> {{org.apache.calcite.avatica.util.DateTimeUtils.unixDateToString}}), and
> secondly it looks that we need to have consistency between {{TIMESTAMP}} and
> {{TIMESTAMP_WITH_LOCAL_TIME_ZONE}} types.
> Definition of done:
> * Forbid date/datetime/timestamp values with year <=0 and year > 9999 (should
> be restricted in kv/sql/jdbc/etc (on a binary tuple level probably))
> * Ensure interval arithmetic produces corresponding error when overflow
> happens (e.g. {{select timestamp '9999-12-31 00:00:00' + INTERVAL 1 DAYS}}).
> * Ensure cast with format does not accept more then 4 digit (e.g. {{SELECT
> cast('10000' as date format 'YYYYY')}}).
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
