You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@spark.apache.org by "Nicolas Lalevée (JIRA)" <ji...@apache.org> on 2017/12/08 18:53:00 UTC
[jira] [Created] (SPARK-22740) [SQL][JDBC] Reserved SQL words are
not escaped for table names
Nicolas Lalevée created SPARK-22740:
---------------------------------------
Summary: [SQL][JDBC] Reserved SQL words are not escaped for table names
Key: SPARK-22740
URL: https://issues.apache.org/jira/browse/SPARK-22740
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 2.2.0
Reporter: Nicolas Lalevée
I have a mysql database where there is a table named 'group'.
Then in Java this line fails:
{code:java}
sparkSession.read().jdbc("jdbc:mysql://localhost/scoop", "group", new Properties()).createTempView("mysql_group");
{code}
with this exception:
{noformat}
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group WHERE 1=0' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4074)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4006)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2468)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2629)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2719)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2318)
at org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.resolveTable(JDBCRDD.scala:62)
at org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation.<init>(JDBCRelation.scala:113)
at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:47)
at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:306)
at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:178)
at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:146)
at org.apache.spark.sql.DataFrameReader.jdbc(DataFrameReader.scala:193)
{noformat}
In mysql, the table name should escaped with back ticks: {{SELECT * FROM `group` WHERE 1=0}}
I tried to work on a fix, but the "table name" in the code of spark may be the qualified name of the table, with the name of the database, so this is not trivial.
Instead of generating: {{SELECT * FROM `mydb.group` WHERE 1=0}}
This should be generated: {{SELECT * FROM `mydb`.`group` WHERE 1=0}}
Here are the functions that I have found that might be affected by reserved word as a table name:
- JdbcDialects#getTableExistsQuery
- JdbcDialects#getSchemaQuery
- MySQLDialect#getTableExistsQuery
- PostgresDialects#getTableExistsQuery
- JdbcUtils#dropTable
- JdbcUtils#truncateTable
- JdbcUtils#getInsertStatement
- JdbcUtils#createTable
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
---------------------------------------------------------------------
To unsubscribe, e-mail: issues-unsubscribe@spark.apache.org
For additional commands, e-mail: issues-help@spark.apache.org