gaodayue opened a new issue #3008: Wrong answer for tableau generated SQL URL: https://github.com/apache/incubator-doris/issues/3008 **Describe the bug** Tableau will generated the following expression when using string fields in date filters ```SQL -- trying to parse varchar column `col` into date value CASE WHEN (NOT ISNULL(DATE(TIMESTAMP(STR_TO_DATE(SUBSTRING(`col`, 1, 1024), '%Y-%m-%d'))))) THEN DATE(TIMESTAMP(STR_TO_DATE(SUBSTRING(`col`, 1, 1024), '%Y-%m-%d'))) WHEN (NOT ISNULL(IFNULL(DATE(SUBSTRING(`col`, 1, 1024)), STR_TO_DATE(SUBSTRING(`col`, 1, 1024),'%b %e %Y')))) THEN IFNULL(DATE(SUBSTRING(`col`, 1, 1024)), STR_TO_DATE(SUBSTRING(`col`, 1, 1024),'%b %e %Y')) ELSE NULL END ``` The following results from Doris is wrong ``` // correct mysql> select str_to_date(substring('2020-02-09', 1, 1024), '%b %e %Y'); +-----------------------------------------------------------+ | str_to_date(substring('2020-02-09', 1, 1024), '%b %e %Y') | +-----------------------------------------------------------+ | NULL | +-----------------------------------------------------------+ // correct mysql> select ifnull(date(substring('2020-02-09', 1, 1024)), null); +------------------------------------------------------+ | ifnull(date(substring('2020-02-09', 1, 1024)), NULL) | +------------------------------------------------------+ | 2020-02-09 | +------------------------------------------------------+ // WRONG: expect to be 2020-02-09 mysql> select ifnull(date(substring('2020-02-09', 1, 1024)), str_to_date(substring('2020-02-09', 1, 1024), '%b %e %Y')); +-----------------------------------------------------------------------------------------------------------+ | ifnull(date(substring('2020-02-09', 1, 1024)), str_to_date(substring('2020-02-09', 1, 1024), '%b %e %Y')) | +-----------------------------------------------------------------------------------------------------------+ | 20200209 | +-----------------------------------------------------------------------------------------------------------+ // WRONG: expect to be 1 (true) mysql> SELECT CASE WHEN (NOT ISNULL(DATE(TIMESTAMP(STR_TO_DATE(SUBSTRING('2020-02-98', 1, 1024), '%Y-%m-%d'))))) -> THEN DATE(TIMESTAMP(STR_TO_DATE(SUBSTRING('2020-02-09', 1, 1024), '%Y-%m-%d'))) -> WHEN (NOT ISNULL(IFNULL(DATE(SUBSTRING('2020-02-09', 1, 1024)), -> STR_TO_DATE(SUBSTRING('2020-02-09', 1, 1024),'%b %e %Y')))) -> THEN IFNULL(DATE(SUBSTRING('2020-02-09', 1, 1024)), -> STR_TO_DATE(SUBSTRING('2020-02-09', 1, 1024),'%b %e %Y')) -> ELSE NULL -> END = TIMESTAMP('2020-02-09 00:00:00') as c1; +------+ | c1 | +------+ | 0 | +------+ ``` Doris version: 0.11
---------------------------------------------------------------- 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: us...@infra.apache.org With regards, Apache Git Services --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org
