[
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-unsubscr...@spark.apache.org
For additional commands, e-mail: issues-h...@spark.apache.org
