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