[
https://issues.apache.org/jira/browse/CALCITE-6922?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Pavel Pereslegin updated CALCITE-6922:
--------------------------------------
Description:
Currently, when using CAST with FORMAT we can specify strange
date/time/timestamp patterns that (I believe) are not SQL standard compliant
and which produce confusing results.
For example:
{code:java}
f.checkScalar("cast('9999999-9999999-9999999' as date format 'y-M-dd')",
"(+0'-0(-..",
"DATE NOT NULL");
f.checkScalar("cast('0-0-0' as date format 'yyyyyyy-M-dddddd')",
"0002-12-31",
"DATE NOT NULL");
{code}
I guess this is because java {{SimpleDateFormat}} is used for parsing without
any significant restrictions.
I think that only the templates listed in CALCITE-2980 should be allowed to be
used.
For example 'y' (lowercase) and 'YYYYY' (>4 symbols) should not be allowed.
It is also worth adding validation of the length of the field values.
For example {{cast('12345-01-01' as date format 'YYYY-MM-DD'}} should raise
exception that the value '12345-01-01' does not match pattern 'YYYY-MM-DD'.
was:
Currently, when using CAST with FORMAT we can specify strange
date/time/timestamp patterns that (I believe) are not SQL standard compliant
and which produce confusing results.
For example:
{code:java}
f.checkScalar("cast('9999999-9999999-9999999' as date format 'y-M-dd')",
"(+0'-0(-..",
"DATE NOT NULL");
f.checkScalar("cast('0-0-0' as date format 'yyyyyyy-M-dddddd')",
"0002-12-31",
"DATE NOT NULL");
{code}
I guess this is because java {{SimpleDateFormat}} is used for parsing without
any significant restrictions.
I think that only the templates listed in CALCITE-2980 should be allowed to be
used.
For example 'y' (lowercase) and 'YYYYY' (>4 symbols) should not be allowed.
It is also worth adding validation of the length of the field values.
For example {{cast('12345-1-1' as date format 'yyyy-M-D'}} should raise
exception that the value '12345-1-1' does not match pattern 'yyyy-M-D'.
> Add stricter date patterns validation for CAST FORMAT
> -----------------------------------------------------
>
> Key: CALCITE-6922
> URL: https://issues.apache.org/jira/browse/CALCITE-6922
> Project: Calcite
> Issue Type: Bug
> Affects Versions: 1.39.0
> Reporter: Pavel Pereslegin
> Priority: Major
>
> Currently, when using CAST with FORMAT we can specify strange
> date/time/timestamp patterns that (I believe) are not SQL standard compliant
> and which produce confusing results.
> For example:
> {code:java}
> f.checkScalar("cast('9999999-9999999-9999999' as date format 'y-M-dd')",
> "(+0'-0(-..",
> "DATE NOT NULL");
> f.checkScalar("cast('0-0-0' as date format 'yyyyyyy-M-dddddd')",
> "0002-12-31",
> "DATE NOT NULL");
> {code}
> I guess this is because java {{SimpleDateFormat}} is used for parsing without
> any significant restrictions.
> I think that only the templates listed in CALCITE-2980 should be allowed to
> be used.
> For example 'y' (lowercase) and 'YYYYY' (>4 symbols) should not be allowed.
> It is also worth adding validation of the length of the field values.
> For example {{cast('12345-01-01' as date format 'YYYY-MM-DD'}} should raise
> exception that the value '12345-01-01' does not match pattern 'YYYY-MM-DD'.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
