[
https://issues.apache.org/jira/browse/HIVE-28483?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17882720#comment-17882720
]
Shohei Okumiya edited comment on HIVE-28483 at 9/18/24 1:57 PM:
----------------------------------------------------------------
I'm checking SQL:2023 Part 2. This comment could include my misunderstanding as
the chapters of CAST and literals are complicated and too long.
According to 6.13 <cast specification> -> General Rules -> 14) -> a), CAST(x AS
DATE) shall return a date value if it is valid as a date literal or unquoted
date string. Otherwise, it shall throw an error. The accepted format seems like
`YYYY-MM-DD`, which is valid as a Gregorian calendar date with four digits as
the year part. So, I assume throwing an exception is the most correct behavior.
But it is uncommon for Hive to throw an error on CAST(e.g., Hive returns NULL
in another case, such as CAST('aaa' AS INT)).
I currently prefer to see NULL as an alternative to an error. However, it could
also be a valid option to keep the compatibility, i.e. saying the behavior of
4.0.0 is the right behavior of Hive. I don't have a very strong preference to
be honest.
was (Author: okumin):
I'm checking SQL:2023 Part 2. This comment could include my misunderstanding as
the chapters of CAST and literals are complicated and too long.
According to 6.13 <cast specification> -> General Rules -> 14) -> a), CAST(x AS
DATE) shall return a date value if it is valid as a date literal or unquoted
date string. Otherwise, it shall throw an error. The accepted format seems like
`YYYY-MM-DD`, which is valid as a Gregorian calendar date with four digits as
the year part. So, I assume throwing an exception is the most correct behavior.
But it is uncommon for Hive to throw an error on CAST(e.g., Hive returns NULL
in another case, such as CAST('aaa' AS INT)).
I currently prefer to see NULL as an alternative to an error. However, it could
also be a valid option to keep the compatibility.
> String date cast giving wrong result
> ------------------------------------
>
> Key: HIVE-28483
> URL: https://issues.apache.org/jira/browse/HIVE-28483
> Project: Hive
> Issue Type: Bug
> Reporter: Zoltán Rátkai
> Assignee: Zoltán Rátkai
> Priority: Minor
> Labels: pull-request-available
>
> Date conversation gives wrong result. Like:1 row selected (6.403 seconds)
> select to_date('03-08-2024');
> Result:
> +-------------+
> | _c0 |
> +-------------+
> |0003-08-20 |
> +-------------+
> or:
> select to_date(last_day(add_months(last_day('03-08-2024'), -1))) ;
> Result:
> +-------------+
> | _c0 |
> +-------------+
> |0003-07-31 |
> +-------------
> Here is my comparison with other database systems:
> +
> ----------------------------------------------------------------------------------------------------------
> --------------------------------------------PostgreSQL----------------------------------------------------
> ----------------------------------------------------------------------------------------------------------
> SELECT TO_DATE('03-08-2024','YYYYMMDD');
> invalid value "03-0" for "YYYY" DETAIL: Field requires 4 characters, but only
> 2 could be parsed. HINT: If your source string is not fixed-width, try using
> the "FM" modifier.
> SELECT TO_DATE('03-08-2024','DD-MM-YYYY');
> to_date
> Sat, 03 Aug 2024 00:00:00 GMT
> SELECT CAST('03-08-2024' AS date);
> date
> Fri, 08 Mar 2024 00:00:00 GMT
> SELECT CAST('2024-08-03' AS date);
> date
> Sat, 03 Aug 2024 00:00:00 GMT
> SELECT CAST('2024-08-03 T' AS date);
> invalid input syntax for type date: "2024-08-03 T" LINE 1: SELECT
> CAST('2024-08-03 T' AS date) ^
> SELECT CAST('2024-08-03T' AS date);
> invalid input syntax for type date: "2024-08-03T" LINE 1: SELECT
> CAST('2024-08-03T' AS date) ^
> SELECT CAST('2024-08-03T12:00:00' AS date);
> date
> Sat, 03 Aug 2024 00:00:00 GMT
> SELECT CAST('2024-08-0312:00:00' AS date);
> date/time field value out of range: "2024-08-0312:00:00" LINE 1: SELECT
> CAST('2024-08-0312:00:00' AS date) ^ HINT: Perhaps you need a different
> "datestyle" setting.
> ----------------------------------------------------------------------------------------------------------
> ---------------------------------------------ORACLE-------------------------------------------------------
> ----------------------------------------------------------------------------------------------------------
> select CAST('2024-08-03 12:00:00' AS date) from dual;
> Output:
> select CAST('2024-08-03 12:00:00' AS date) from dual
> *
> ERROR at line 1:
> ORA-01861: literal does not match format string
> ---------------------
> select CAST('2024-08-03' AS date) from dual;
> Output:
> select CAST('2024-08-03' AS date) from dual
> *
> ERROR at line 1:
> ORA-01861: literal does not match format string
> ---------------------
> SELECT TO_DATE('08/03/2024', 'MM/DD/YYYY') FROM DUAL;
> Output:
> TO_DATE('
> ---------
> 03-AUG-24
> ---------------------
> SELECT TO_DATE('2024-08-03', 'YYYY-MM-DD') FROM DUAL;
> Output:
> TO_DATE('
> ---------
> 03-AUG-24
> ---------------------
> select CAST('03-08-2024' AS date) from dual;
> Output:
> select CAST('03-08-2024' AS date) from dual
> *
> ERROR at line 1:
> ORA-01843: An invalid month was specified.
> ---------------------
> select CAST('2024-08-0312:00:00' AS date) from dual;
> Output:
> select CAST('2024-08-0312:00:00' AS date) from dual
> *
> ERROR at line 1:
> ORA-01861: literal does not match format string
> ---------------------
> select CAST('10-AUG-24' AS date) from dual;
> Output:
> CAST('10-
> ---------
> 10-AUG-24
> ---------------------
> select CAST('10-AUG-2024' AS date) from dual;
> Output:
> CAST('10-
> ---------
> 10-AUG-24
> ---------------------
> select CAST('03-08-24' AS date) from dual;
> Output:
> select CAST('03-08-24' AS date) from dual
> *
> ERROR at line 1:
> ORA-01843: An invalid month was specified.
>
> --------------------------------------
> select CAST('03-08-2024' AS date) from dual;
> Output:
> select CAST('03-08-2024' AS date) from dual
> *
> ERROR at line 1:
> ORA-01843: An invalid month was specified.
> --------------------------------
> SELECT sysdate FROM DUAL;
> Output:
> SYSDATE
> ---------
> 10-SEP-24
> SYSDATE
> ---------
> 10-SEP-24
> ----------------------------------------------------------------------------------------------------------
> ---------------------------------------------MYSQL-------------------------------------------------------
> ----------------------------------------------------------------------------------------------------------
> SELECT CAST('03-08-2024' AS date);
> Output:
> +----------------------------+
> | CAST('03-08-2024' AS date) |
> +----------------------------+
> | NULL |
> +----------------------------+
> --------------------------------
> SELECT STR_TO_DATE('03-08-2024','%d-%m-%Y');
> Output:
> +--------------------------------------+
> | STR_TO_DATE('03-08-2024','%d-%m-%Y') |
> +--------------------------------------+
> | 2024-08-03 |
> +--------------------------------------+
> --------------------------------
> --------------------------------
> SELECT CAST('03-08-2024' AS date);
> Output:
> +----------------------------+
> | CAST('03-08-2024' AS date) |
> +----------------------------+
> | NULL |
> +----------------------------+
> --------------------------------
> SELECT CAST('2024-08-03' AS date);
> Output:
> +----------------------------+
> | CAST('2024-08-03' AS date) |
> +----------------------------+
> | 2024-08-03 |
> +----------------------------+
> --------------------------------
> SELECT CAST('2024-08-03 T' AS date);
> Output:
> +------------------------------+
> | CAST('2024-08-03 T' AS date) |
> +------------------------------+
> | 2024-08-03 |
> +------------------------------+
> --------------------------------
> SELECT CAST('2024-08-03T' AS date);
> Output:
> +-----------------------------+
> | CAST('2024-08-03T' AS date) |
> +-----------------------------+
> | 2024-08-03 |
> +-----------------------------+
> --------------------------------
> SELECT CAST('2024-08-03T12:00:00' AS date);
> Output:
> +-------------------------------------+
> | CAST('2024-08-03T12:00:00' AS date) |
> +-------------------------------------+
> | 2024-08-03 |
> +-------------------------------------+
> --------------------------------
> SELECT CAST('2024-08-0312:00:00' AS date);
> Output:
> +------------------------------------+
> | CAST('2024-08-0312:00:00' AS date) |
> +------------------------------------+
> | NULL |
> +------------------------------------+
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
