[GitHub] [spark] pengbo opened a new pull request #24567: [SPARK-27638][SQL]: date format 'yyyy-M-dd' string comparison not handled properly

[GitBox <gi...@apache.org>]

pengbo opened a new pull request #24567: [SPARK-27638][SQL]: date format 'yyyy-M-dd' string comparison not handled properly
URL: https://github.com/apache/spark/pull/24567
 
 
   ## What changes were proposed in this pull request?
   
   The below example works with both Mysql and Hive, however not with spark.
   
   ```
   mysql> select * from date_test where date_col >= '2000-1-1';
   +------------+
   | date_col   |
   +------------+
   | 2000-01-01 |
   +------------+
   ```
   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
   Mysql: 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
   
   As below date patterns have been supported, the PR is to cast string to date when comparing string and date:
   ```
   `yyyy`
   `yyyy-[m]m`
   `yyyy-[m]m-[d]d`
   `yyyy-[m]m-[d]d `
   `yyyy-[m]m-[d]d *`
   `yyyy-[m]m-[d]dT*
   ```
   
   ## How was this patch tested?
   UT has been added

----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
users@infra.apache.org


With regards,
Apache Git Services

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