sqlCreateTable does not create proper result with Teradata connection

[its-gazza]

For teradata connection, when I try to create a sql query through sqlCreateTable, the output result is incorrect. Notice parameter temporary does not work too.

# Packages
require(reprex)
#> Loading required package: reprex
require(DBI)
#> Loading required package: DBI
require(odbc)
#> Loading required package: odbc

# Parameters
table <- 'mtcars'
fields <- mtcars
temporary <- TRUE

con <- dbConnect(odbc(),
                 dsn = 'dwh32')
con@info
#> $dbname
#> [1] "DBNAME"
#> 
#> $dbms.name
#> [1] "Teradata"
#> 
#> $db.version
#> [1] "16.10.0310  16.10.03.10"
#> 
#> $username
#> [1] "USER"
#> 
#> $host
#> [1] ""
#> 
#> $port
#> [1] ""
#> 
#> $sourcename
#> [1] "dwh32"
#> 
#> $servername
#> [1] "SERVERNAME"
#> 
#> $drivername
#> [1] "tdataodbc_sb64.so"
#> 
#> $odbc.version
#> [1] "03.52.0000"
#> 
#> $driver.version
#> [1] "16.20.00.054"
#> 
#> $odbcdriver.version
#> [1] "03.52"
#> 
#> $supports.transactions
#> [1] TRUE
#> 
#> attr(,"class")
#> [1] "Teradata"    "driver_info" "list"
DBI::sqlCreateTable(con, 'mtcars', mtcars, temporary = TRUE)
#> <SQL> CREATE TABLE "mtcars" (
#>   "row_names" VARCHAR(255),
#>   "mpg" DOUBLE PRECISION,
#>   "cyl" DOUBLE PRECISION,
#>   "disp" DOUBLE PRECISION,
#>   "hp" DOUBLE PRECISION,
#>   "drat" DOUBLE PRECISION,
#>   "wt" DOUBLE PRECISION,
#>   "qsec" DOUBLE PRECISION,
#>   "vs" DOUBLE PRECISION,
#>   "am" DOUBLE PRECISION,
#>   "gear" DOUBLE PRECISION,
#>   "carb" DOUBLE PRECISION
#> )

# Try creating the function from 
# Copy createField function https://github.com/r-dbi/odbc/blob/master/R/Table.R#L135
createFields <- function(con, fields, field.types, row.names) {
  if (is.data.frame(fields)) {
    fields <- sqlRownamesToColumn(fields, row.names)
    fields <- vapply(fields, function(x) DBI::dbDataType(con, x), character(1))
  }
  if (!is.null(field.types)) {
    fields[names(field.types)] <- field.types
  }
  
  field_names <- dbQuoteIdentifier(con, names(fields))
  field_types <- unname(fields)
  paste0(field_names, " ", field_types)
}
# Below are code extracted from https://github.com/r-dbi/odbc/blob/master/R/db.R#L25
table <- dbQuoteIdentifier(con, table)
fields <- createFields(con, fields, field.types = NULL, row.names = NULL)
SQL(paste0(
  "CREATE ", if (temporary) " MULTISET VOLATILE ", "TABLE ", table, " (\n",
  "  ", paste(fields, collapse = ",\n  "), "\n)\n", if (temporary) " ON COMMIT PRESERVE ROWS"
))
#> <SQL> CREATE  MULTISET VOLATILE TABLE "mtcars" (
#>   "mpg" DOUBLE PRECISION,
#>   "cyl" DOUBLE PRECISION,
#>   "disp" DOUBLE PRECISION,
#>   "hp" DOUBLE PRECISION,
#>   "drat" DOUBLE PRECISION,
#>   "wt" DOUBLE PRECISION,
#>   "qsec" DOUBLE PRECISION,
#>   "vs" DOUBLE PRECISION,
#>   "am" DOUBLE PRECISION,
#>   "gear" DOUBLE PRECISION,
#>   "carb" DOUBLE PRECISION
#> )
#>  ON COMMIT PRESERVE ROWS

^{Created on 2019-09-06 by the reprex package (v0.3.0)}

I've replaced some variables with dummy names.

[But2ene]

I encounter the same error.

My connection object has the Teradata class. So I would expect the correct flavor of sqlCreateTable to be used.

odbc::dbGetInfo(db)
#>$dbname
#>[1] "XXXXXXX" # hidden
#>
#>$dbms.name
#>[1] "Teradata"
#>
#>$db.version
#>[1] "16.20.3225  16.20.32.25"
#>
#>$username
#>[1] "XXXXXXX" # hidden
#>
#>$host
#>[1] ""
#>
#>$port
#>[1] ""
#>
#>$sourcename
#>[1] "YYYYYYYYYYY" #hidden
#>
#>$servername
#>[1] "ZZZZZZZZZ" # hidden
#>
#>$drivername
#>[1] "tdataodbc_sb64.dll"
#>
#>$odbc.version
#>[1] "03.80.0000"
#>
#>$driver.version
#>[1] "16.20.00.1043"
#>
#>$odbcdriver.version
#>[1] "03.80"
#>
#>$supports.transactions
#>[1] TRUE
#>
#>$getdata.extensions.any_column
#>[1] TRUE
#>
#>$getdata.extensions.any_order
#>[1] TRUE
#>
#>attr(,"class")
#>[1] "Teradata"    "driver_info" "list" 

The surprising thing is that the code looks correct to me on the following function. https://github.com/r-dbi/odbc/blob/76c7293cfcd1673f98ce064048ac2399622984b1/R/db.R#L25

[But2ene]

I found the error In the RStudio Code Browser, the interface of the function is

new("MethodDefinition", .Data = function (con, table, fields, 
  row.names = NA, temporary = FALSE, ...) 
{
  .local <- function (con, table, fields, field.types = NULL, 
    row.names = NA, temporary = FALSE, ...) 
  {
    table <- dbQuoteIdentifier(con, table)
    fields <- createFields(con, fields, field.types, row.names)
    SQL(paste0("CREATE ", if (temporary) 
      " MULTISET VOLATILE ", "TABLE ", table, " (\n", 
      "  ", paste(fields, collapse = ",\n  "), "\n)\n", 
      if (temporary) 
        " ON COMMIT PRESERVE ROWS"))
  }
  .local(con, table, fields, row.names, temporary, ...)
}, target = new("signature", .Data = "Teradata", names = "con", 
  package = ".GlobalEnv"), defined = new("signature", .Data = "Teradata", 
  names = "con", package = ".GlobalEnv"), generic = "sqlCreateTable")

By zooming, we can see

.local <- function (con, table, fields, field.types = NULL, row.names = NA, temporary = FALSE, ...) # function definition
.local(con, table, fields, row.names, temporary, ...) # function call
# which is equivalent to
.local(con = con, table = table, fields = fields, field.types  = row.names, row.names = temporary, ...)

I do not find the code from the RStudio Code Browser in the odbc source code. The parameters are always correctly defined in odbc source code. I need help to solve this issue.