You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Pavel Pereslegin (Jira)" <ji...@apache.org> on 2025/03/28 10:34:00 UTC
[jira] [Updated] (CALCITE-6922) Add stricter date patterns validation for CAST FORMAT

     [ 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)