You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@spark.apache.org by "Maxim Gekk (JIRA)" <ji...@apache.org> on 2019/05/06 15:58:00 UTC

[jira] [Comment Edited] (SPARK-27638) date format yyyy-M-dd string comparison not handled properly

    [ https://issues.apache.org/jira/browse/SPARK-27638?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16833949#comment-16833949 ] 

Maxim Gekk edited comment on SPARK-27638 at 5/6/19 3:57 PM:
------------------------------------------------------------

[~srowen] The date literal should be casted to the date type by [stringToDate|https://github.com/apache/spark/blob/ab8710b57916a129fcb89464209361120d224535/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala#L376] that is able to parse the date by default, see supported patterns:
{code}
`yyyy`
`yyyy-[m]m`
`yyyy-[m]m-[d]d`
`yyyy-[m]m-[d]d `
`yyyy-[m]m-[d]d *`
`yyyy-[m]m-[d]dT*
{code}

 


was (Author: maxgekk):
[~srowen] The date literal should be casted to the date type by [stringToDate|[https://github.com/apache/spark/blob/ab8710b57916a129fcb89464209361120d224535/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/util/DateTimeUtils.scala#L376]] that is able to parse the date by default, see supported patterns:
{code}
`yyyy`
`yyyy-[m]m`
`yyyy-[m]m-[d]d`
`yyyy-[m]m-[d]d `
`yyyy-[m]m-[d]d *`
`yyyy-[m]m-[d]dT*
{code}

 

> date format yyyy-M-dd string comparison not handled properly 
> -------------------------------------------------------------
>
>                 Key: SPARK-27638
>                 URL: https://issues.apache.org/jira/browse/SPARK-27638
>             Project: Spark
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 2.4.2
>            Reporter: peng bo
>            Priority: Major
>
> The below example works with both Mysql and Hive, however not with spark.
> {code:java}
> mysql> select * from date_test where date_col >= '2000-1-1';
> +------------+
> | date_col   |
> +------------+
> | 2000-01-01 |
> +------------+
> {code}
> The reason is that Spark casts both sides to String type during date and string comparison for partial date support. Please find more details in https://issues.apache.org/jira/browse/SPARK-8420.
> Based on some tests, the behavior of Date and String comparison in Hive and Mysql:
> Hive: Cast to Date, partial date is not supported
> Spark: Cast to Date,  certain "partial date" is supported by defining certain date string parse rules. Check out {{str_to_datetime}} in https://github.com/mysql/mysql-server/blob/5.5/sql-common/my_time.c
> Here's 2 proposals:
> a. Follow Mysql parse rule, but some partial date string comparison cases won't be supported either. 
> b. Cast String value to Date, if it passes use date.toString, original string otherwise.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org