Cannot use built-in functions in SQL statement with DatabaseConnector

[geoxc275]

I ran into issue when using DatabaseConnector in R (v4.4.2) to create tables. I am using the latest driver for Databricks (v2.7.1), Mac OS Somona 14.7.3. (chip: M3 Pro)

DatabaseConnector::dbExecute works fine when SQL statement does not involve built-in functions, e.g., UPPER, YEAR or any functions for regex. Please see example below.

library(DatabaseConnector)

DatabaseConnector::downloadJdbcDrivers("spark", pathToDriver = Sys.getenv("DATABASECONNECTOR_JAR_FOLDER")) 

connectionDetails <- DatabaseConnector::createConnectionDetails (
  dbms = "spark",
  user="token",
  password = Sys.getenv("DATABRICKS_TOKEN"),
  connectionString = paste("jdbc:databricks://",
                           Sys.getenv("DATABRICKS_HOST"),
                           ":443/default;transportMode=http;ssl=1;UseNativeQuery=1;EnableArrow=0;AuthMech=3;httpPath=",
                           Sys.getenv("DATABRICKS_HTTP_PATH"),
                           ";",sep='')
)

conn <- DatabaseConnector::connect(connectionDetails)

Example 1: dbExecute works if no table is created

dbExecute(conn,
          "
          SELECT YEAR(start_date) AS start_year
          FROM my_table
          "
          )

Example 2: dbExecute works if built-in function is not in use

dbExecute(conn,
          "
          DROP TABLE IF EXISTS my_scratch.new_table;
          CREATE TABLE my_scratch.new_table AS 
          SELECT start_date AS start_date
          FROM my_table
          "
          )

Example 3: dbExecute fails if built-in function is call in SELECT

dbExecute(conn,
          "
          DROP TABLE IF EXISTS my_scratch.new_table;
          CREATE TABLE my_scratch.new_table AS 
          SELECT YEAR(start_date) AS start_year
          FROM my_table
          "
          )

The error message for example 3 is

Error in .jcall("RJavaTools", "Ljava/lang/Object;", "invokeMethod", cl,  : 
  java.lang.ArrayIndexOutOfBoundsException: Index 1 out of bounds for length 1

Using other built-in functions, e.g., UPPER can trigger the same error. Example 3 would work if a View is created instead.

[schuemie]

By default the dbExecute() function translates SQL using the SqlRender function. You can turn this off by using translate = FALSE as extra argument.

Note that this behavior will change in the upcoming v7, where dbExecute() will not perform translation.

CREATE TABLE AS SELECT is not valid input SQL for the translation. Instead, it uses SELECT INTO.

[geoxc275]

By default the dbExecute() function translates SQL using the SqlRender function. You can turn this off by using translate = FALSE as extra argument.

Note that this behavior will change in the upcoming v7, where dbExecute() will not perform translation.

CREATE TABLE AS SELECT is not valid input SQL for the translation. Instead, it uses SELECT INTO.

Thank you, the solution worked. I really appreciate your time and expertise.