[jira] [Created] (CALCITE-6922) Add stricter date patterns validation for CAST FORMAT

["Pavel Pereslegin (Jira)" <ji...@apache.org>]

Pavel Pereslegin created CALCITE-6922:
-----------------------------------------

             Summary: 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


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'.




--
This message was sent by Atlassian Jira
(v8.20.10#820010)