Signature differences for sqlCreateTable compared to DBI function specification

[adithya604]

Issue Description and Expected Result

Incorrect function signature for sqlCreateTable in R/db.R The following snippet is the signature given by odbc package:

setMethod("sqlCreateTable", "Teradata",
  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"
        ))
  })

However, the signature in DBI specification is (note that field.types argument is not present):

Database

Teradata

Reproducible Example

library(odbc)
library(DBI)
con <- dbConnect(odbc::odbc(), dsn = "TeradataDSN")
# row_names column is not created even though "row.names" argument is set to TRUE.
> query <- sqlCreateTable(con, table = "rock_table", fields = rock, row.names = TRUE)
> query
<SQL> CREATE TABLE "rock_table" (
  "area" INTEGER,
  "peri" FLOAT,
  "shape" FLOAT,
  "perm" FLOAT
)
# Note that temporary table is not created and row_names column is created, even though the  "temporary"
# argument is set and "row.names" argument is NA.
> query <- sqlCreateTable(con, table = "rock_table", fields = rock, row.names = NA, temporary = TRUE)
> query
<SQL> CREATE TABLE "rock_table" (
  "row_names" VARCHAR(255),
  "area" INTEGER,
  "peri" FLOAT,
  "shape" FLOAT,
  "perm" FLOAT
)
# Note that temporary table is created when there is additional TRUE (which is part of ... in function definition)
> query <- sqlCreateTable(con, table = "rock_table", fields = rock, row.names = NA, temporary = TRUE, TRUE)
> query
<SQL> CREATE  MULTISET VOLATILE TABLE "rock_table" (
  "row_names" VARCHAR(255),
  "area" INTEGER,
  "peri" FLOAT,
  "shape" FLOAT,
  "perm" FLOAT
)
 ON COMMIT PRESERVE ROWS
# getMethod call
> getMethod("sqlCreateTable", "Teradata")
Method Definition:

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, ...)
}
<bytecode: 0x000001f6f015a660>
<environment: namespace:odbc>

Signatures:
        con       
target  "Teradata"
defined "Teradata"

Possible issue

Note that from getMethod call that the function passes the arguments to .local() function, which does not use named arguments but just positional argument and there is field.types argument in .local() which is not part of sqlCreateTable. Hence, row.names is passed to field.types, temporary is passed to row.names and TRUE (as part of ...) is passed to temporary. The behavior is same when column types is provided in the argument fields as per DBI sqlCreateTable specification.

# Note that temporary table is not created and row_names column is created, even though the  "temporary"
# argument is set and "row.names" argument is NA.
> query <- sqlCreateTable(con, table = "rock_table", fields = rock_types, row.names = NA, temporary = TRUE)
> query
<SQL> CREATE TABLE "rock_table" (
  "area" integer,
  "peri" float,
  "shape" decimal(5, 2),
  "perm" decimal(15, 3)
)
# Note that temporary table is created when there is additional TRUE (which is part of ... in function definition)
> query <- sqlCreateTable(con, table = "rock_table", fields = rock_types, row.names = NA, temporary = TRUE, TRUE)
> query
<SQL> CREATE  MULTISET VOLATILE TABLE "rock_table" (
  "area" integer,
  "peri" float,
  "shape" decimal(5, 2),
  "perm" decimal(15, 3)
)
 ON COMMIT PRESERVE ROWS

```r > devtools::session_info() - Session info ----------------------------------------------------------------------------------------------------------------------------- setting value version R version 3.6.3 (2020-02-29) os Windows 10 x64 system x86_64, mingw32 ui RStudio language (EN) collate English_United States.1252 ctype English_United States.1252 tz Asia/Calcutta date 2021-03-23

• Packages --------------------------------------------------------------------------------------------------------------------------------- package * version date lib source
  assertthat 0.2.1 2019-03-21 [1] CRAN (R 3.6.3) backports 1.1.7 2020-05-13 [1] CRAN (R 3.6.3) bit 4.0.4 2020-08-04 [1] CRAN (R 3.6.3) bit64 4.0.5 2020-08-30 [1] CRAN (R 3.6.3) blob 1.2.1 2020-01-20 [1] CRAN (R 3.6.3) callr 3.5.1 2020-10-13 [1] CRAN (R 3.6.3) cli 2.3.1 2021-02-23 [1] CRAN (R 3.6.3) crayon 1.3.4 2017-09-16 [1] CRAN (R 3.6.3) DBI * 1.1.1 2021-01-15 [1] CRAN (R 3.6.3) desc 1.2.0 2018-05-01 [1] CRAN (R 3.6.3) devtools 2.3.1 2020-07-21 [1] CRAN (R 3.6.3) digest 0.6.27 2020-10-24 [1] CRAN (R 3.6.3) ellipsis 0.3.1 2020-05-15 [1] CRAN (R 3.6.3) fs 1.5.0 2020-07-31 [1] CRAN (R 3.6.3) glue 1.4.2 2020-08-27 [1] CRAN (R 3.6.3) hms 0.5.3 2020-01-08 [1] CRAN (R 3.6.3) magrittr 2.0.1 2020-11-17 [1] CRAN (R 3.6.3) memoise 1.1.0 2017-04-21 [1] CRAN (R 3.6.3) odbc 1.2.2 2020-01-10 [1] CRAN (R 3.6.3) pkgbuild 1.1.0 2020-07-13 [1] CRAN (R 3.6.3) pkgconfig 2.0.3 2019-09-22 [1] CRAN (R 3.6.3) pkgload 1.1.0 2020-05-29 [1] CRAN (R 3.6.3) prettyunits 1.1.1 2020-01-24 [1] CRAN (R 3.6.3) processx 3.4.5 2020-11-30 [1] CRAN (R 3.6.3) ps 1.3.4 2020-08-11 [1] CRAN (R 3.6.3) R6 2.5.0 2020-10-28 [1] CRAN (R 3.6.3) Rcpp 1.0.5 2020-07-06 [1] CRAN (R 3.6.3) remotes 2.2.0 2020-07-21 [1] CRAN (R 3.6.3) rlang 0.4.10 2020-12-30 [1] CRAN (R 3.6.3) rprojroot 1.3-2 2018-01-03 [1] CRAN (R 3.6.3) rstudioapi 0.11 2020-02-07 [1] CRAN (R 3.6.3) sessioninfo 1.1.1 2018-11-05 [1] CRAN (R 3.6.3) testthat 3.0.2 2021-02-14 [1] CRAN (R 3.6.3) usethis 1.6.1 2020-04-29 [1] CRAN (R 3.6.3) vctrs 0.3.5 2020-11-17 [1] CRAN (R 3.6.3) withr 2.4.1 2021-01-26 [1] CRAN (R 3.6.3)

[1] C:/Users/AA250114/Documents/R/win-library/3.6 [2] C:/Program Files/R/R-3.6.3/library

</details>