Details
-
Bug
-
Status: Resolved
-
Major
-
Resolution: Incomplete
-
None
-
None
Description
It seems Spark SQL fails to implicitly cast (or detect widen type) from string with date/timestamp.
spark-sql> select greatest("2015-02-02", date("2015-01-01")) ; Error in query: cannot resolve 'greatest('2015-02-02', CAST('2015-01-01' AS DATE))' due to data type mismatch: The expressions should all have the same type, got GREATEST(string, date).; line 1 pos 7
It seems, at least, other DBMS support this by implicit casting/widened types.
hive> select greatest("2015-02-02", date("2015-01-01")); OK 2015-02-02 hive> select greatest("2015-02-021", date("2015-01-01")); OK 2015-01-01 Time taken: 0.019 seconds, Fetched: 1 row(s) hive> select greatest("9999-02-021", date("2015-01-01")); OK 2015-01-01 Time taken: 0.02 seconds, Fetched: 1 row(s) hive> hive> CREATE TEMPORARY TABLE typeof as select greatest("2015-02-02", date("2015-01-01")); OK Time taken: 2.63 seconds hive> DESCRIBE typeof; OK _c0 date Time taken: 0.031 seconds, Fetched: 1 row(s)
mysql> select greatest("2015-02-02abc", date("2015-01-01")); +-----------------------------------------------+ | greatest("2015-02-02abc", date("2015-01-01")) | +-----------------------------------------------+ | 2015-02-02abc | +-----------------------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> CREATE TEMPORARY TABLE typeof as select greatest("2015-02-02", date("2015-01-01")); Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> DESCRIBE typeof; +--------------------------------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------------------------------+-------------+------+-----+---------+-------+ | greatest("2015-02-02", date("2015-01-01")) | varchar(10) | YES | | NULL | | +--------------------------------------------+-------------+------+-----+---------+-------+ 1 row in set (0.00 sec)
postgres=# select greatest('2015-02-02abc', date('2015-01-01')); ERROR: invalid input syntax for type date: "2015-02-02abc" LINE 1: select greatest('2015-02-02abc', date('2015-01-01')); postgres=# CREATE TEMPORARY TABLE typeof as select greatest('2015-02-02', date('2015-01-01')); SELECT 1 postgres=# \d+ typeof Table "pg_temp_3.typeof" Column | Type | Modifiers | Storage | Stats target | Description ----------+------+-----------+---------+--------------+------------- greatest | date | | plain | | Has OIDs: no
I tracked down and it seems we want Hive's behaviour assuming from SPARK-12201.