[ 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