[jira] [Resolved] (SPARK-20885) JDBC predicate pushdown uses hardcoded date format

["Hyukjin Kwon (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/SPARK-20885?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Hyukjin Kwon resolved SPARK-20885.
----------------------------------
    Resolution: Incomplete

> JDBC predicate pushdown uses hardcoded date format
> --------------------------------------------------
>
>                 Key: SPARK-20885
>                 URL: https://issues.apache.org/jira/browse/SPARK-20885
>             Project: Spark
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 2.1.0
>            Reporter: Peter Halverson
>            Priority: Minor
>              Labels: bulk-closed
>
> If a date literal is used in a pushed-down filter expression, e.g.
> {code}
> val postingDate = java.sql.Date.valueOf("2016-06-03")
> val count = jdbcDF.filter($"POSTINGDATE" === postingDate).count
> {code}
> where the {{POSTINGDATE}} column is of JDBC type Date, the resulting pushed-down SQL query looks like the following:
> {code}
> SELECT .. <columns> ... FROM <table> WHERE POSTINGDATE = '2016-06-03'
> {code}
> Specifically, the date is compiled into a string literal using the hardcoded yyyy-MM-dd format that {{java.sql.Date.toString}} emits. Note the implied string conversion for date (and timestamp) values in {{JDBCRDD.compileValue}}
> {code}
>   /**
>    * Converts value to SQL expression.
>    */
>   private def compileValue(value: Any): Any = value match {
>     case stringValue: String => s"'${escapeSql(stringValue)}'"
>     case timestampValue: Timestamp => "'" + timestampValue + "'"
>     case dateValue: Date => "'" + dateValue + "'"
>     case arrayValue: Array[Any] => arrayValue.map(compileValue).mkString(", ")
>     case _ => value
>   }
> {code}
> The resulting query fails if the database is expecting a different format for date string literals. For example, the default format for Oracle is 'dd-MMM-yy', so when the relation query is executed, it fails with a syntax error.
> {code}
> ORA-01861: literal does not match format string
> 01861. 00000 -  "literal does not match format string"
> {code}
> In some situations it may be possible to change the database's expected date format to match the Java format, but this is not always possible (e.g. reading from an external database server)
> Shouldn't this kind of conversion be going through some kind of vendor specific translation (e.g. through a {{JDBCDialect}})?



--
This message was sent by Atlassian Jira
(v8.3.4#803005)

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