Reserved keywords as column names cannot be used in generated where clause

[morazow]

Currently it is not possible to generate where clause in predicate pushdown if there are any column names as reserved keyword.

For example, WHERE CONDITION < 5 ..., this is going to fail currently.

[jpizagno]

@morazow @3cham
Is this a related issue? When I do something dumb, like use "condition" as a column name in the schema, then I also get an error. I imagine this fails for column names "select" "from" "where" etc..
This however, has nothing to do with the WHERE clause.

Is this an error/ticket for us and this ticket, or should we note this as "user error" ?
:>

scala> case class Dog(name:String, condition:String)  // condition defined here
scala> import scala.collection.mutable.ListBuffer
scala> val dogs = ListBuffer.empty[Dog]
scala> dogs += Dog("lulu","young")
scala> dogs += Dog("pedro","old")
scala> import spark.implicits._
scala> val dfDogs = dogs.toDF
scala> dfDogs.write.mode("overwrite").option("host","10.29.7.11").option("port","8563").option("username","jpizagno").option("password",ocnExasolPassword).option("table", "OV_AS_ACCESS_LAB.DOG").option("create_table", "true").format("exasol").save()

java.sql.SQLException: syntax error, unexpected CONDITION_ [line 1, column 47] (Session: 1624714496948686218)
  at com.exasol.jdbc.ExceptionFactory.createSQLException(ExceptionFactory.java:175)
  at com.exasol.jdbc.EXASQLException.getSQLExceptionIntern(EXASQLException.java:50)
  at com.exasol.jdbc.AbstractEXAStatement.execute(AbstractEXAStatement.java:468)
  at com.exasol.jdbc.EXAStatement.execute(EXAStatement.java:278)
  at com.exasol.jdbc.AbstractEXAStatement.exaExecuteLargeUpdate(AbstractEXAStatement.java:618)
  at com.exasol.jdbc.EXAStatement.executeLargeUpdate(EXAStatement.java:413)
  at com.exasol.jdbc.EXAStatement.executeUpdate(EXAStatement.java:468)
  at com.exasol.spark.util.ExasolConnectionManager$$anonfun$createTable$1.apply(ExasolConnectionManager.scala:182)
  at com.exasol.spark.util.ExasolConnectionManager$$anonfun$createTable$1.apply(ExasolConnectionManager.scala:181)
  at com.exasol.spark.util.ExasolConnectionManager$.using(ExasolConnectionManager.scala:230)
  at com.exasol.spark.util.ExasolConnectionManager$$anonfun$withStatement$1.apply(ExasolConnectionManager.scala:98)
  at com.exasol.spark.util.ExasolConnectionManager$$anonfun$withStatement$1.apply(ExasolConnectionManager.scala:96)
  at com.exasol.spark.util.ExasolConnectionManager$.using(ExasolConnectionManager.scala:230)
  at com.exasol.spark.util.ExasolConnectionManager.withConnection(ExasolConnectionManager.scala:83)
  at com.exasol.spark.util.ExasolConnectionManager.withStatement(ExasolConnectionManager.scala:96)
  at com.exasol.spark.util.ExasolConnectionManager.createTable(ExasolConnectionManager.scala:181)
  at com.exasol.spark.DefaultSource.createDFTable(DefaultSource.scala:152)
  at com.exasol.spark.DefaultSource.createRelation(DefaultSource.scala:89)
  at org.apache.spark.sql.execution.datasources.DataSource.write(DataSource.scala:469)

[morazow]

Hey @jpizagno,

Yes, it is because of reserved condition keyword. This is currently an issue with the connector.

Generally, we should keep the quotes, for example, the users provide columns as quoted. There is another ticket for this at #39. Similarly, if users do not know which keywords are reserved the connector should detect and properly quote them. This issue is about it.

Since Exasol has SYS tables for reserved keywords and such, the very first solution might be to check whether the column name is one of them. For example, when inferring the Spark dataframe schema.

[morazow]

Using SQL Statement Builder (#84) should help solve this issue.