sqlCreateTable temp table not temp #452

r2evans · 2021-03-29T20:01:34Z

Up front, I think the problem is that odbc::sqlCreateTable has a different order of default arguments, leading to a corruption of both row.names= and temporary=.

sqlCreateTable(..., temporary=TRUE) is not adding temporary.

con
# <OdbcConnection> postgres@127.0.0.1
#   PostgreSQL Version: 11.0.8
sqlCreateTable(con, "mt", mtcars, row.names = FALSE, temporary = TRUE)
# <SQL> CREATE TABLE "mt" (
#   "row_names" TEXT,
#   "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 debugging it,

func <- getMethod("sqlCreateTable", "OdbcConnection")
func
# 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) 
#             "TEMPORARY ", "TABLE ", table, " (\n", "  ", paste(fields, 
#             collapse = ",\n  "), "\n)\n"))
#     }
#     .local(con, table, fields, row.names, temporary, ...)
# }
# <bytecode: 0x0000000012f38508>
# <environment: namespace:odbc>
# Signatures:
#         con             
# target  "OdbcConnection"
# defined "OdbcConnection"

Because this is within the method, I'll debug the .local function as well.

func(con, "mt", mtcars, row.names = FALSE, temporary = TRUE)
# debugging in: func(con, "mt", mtcars, row.names = FALSE, temporary = TRUE)
# debug: { ...
# Browse[2]> 
n
# Browse[2]> 
n
# Browse[2]> 
debug(.local)
# Browse[2]> 
temporary
# [1] TRUE
# Browse[2]> 
row.names
# [1] FALSE
# Browse[2]> 
.local(con, table, fields, row.names, temporary, ...)
# debugging in: .local(con, table, fields, row.names, temporary, ...)
# debug: {
#     table <- dbQuoteIdentifier(con, table)
#     fields <- createFields(con, fields, field.types, row.names)
#     SQL(paste0("CREATE ", if (temporary) 
#         "TEMPORARY ", "TABLE ", table, " (\n", "  ", paste(fields, 
#         collapse = ",\n  "), "\n)\n"))
# }
# Browse[4]> 
temporary
# [1] FALSE
# Browse[4]> 
row.names
# [1] TRUE

Note that temporary and row.names arguments are reversed. I'm not proficient enough debugging these methods enough to know if I'm introducing any problems, but ... since it doesn't work on the console and this way shows that temporary and row.names have somehow swapped values, then that leads me to think I'm on the right track.

If I force the formals names, then it works:

# Browse[2]> 
temporary
# [1] TRUE
# Browse[2]> 
row.names
# [1] FALSE
# Browse[2]> 
.local(con=con, table=table, fields=fields, row.names=row.names, temporary=temporary, ...)
# <SQL> CREATE TEMPORARY TABLE "mt" (
#   "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
# )

I suspect that this may be because the signature in odbc is different than the signature in DBI:

From: https://github.com/r-dbi/DBI/blob/main/R/table-create.R#L41-L42 and https://github.com/r-dbi/odbc/blob/main/R/Table.R#L169-L170, respectively:

setMethod("sqlCreateTable", signature("DBIConnection"),
  function(con, table, fields, row.names = NA, temporary = FALSE, ...) {
setMethod("sqlCreateTable", "OdbcConnection",
  function(con, table, fields, field.types = NULL, row.names = NA, temporary = FALSE, ...) {
###                            ^^^^^^^^^^^^^^^^^^

I am currently unable to compile on my windows box (collapse-log below, if you're curious ... but not this issue), so I cannot test, but I wonder if the fix is to move field.types=NULL to the end of parameters.

Database

I've tested this against a couple of DBMSes, most recently postgres-11 on docker, though the same problem evidences in non-local database connections.

DBI::dbGetQuery(con, "select version()")$version
# [1] "PostgreSQL 11.8 (Debian 11.8-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit"

Session Info

devtools::session_info()
# - Session info ---------------------------------------------------------------
#  setting  value                       
#  version  R version 4.0.3 (2020-10-10)
#  os       Windows 10 x64              
#  system   x86_64, mingw32             
#  ui       RTerm                       
#  language (EN)                        
#  collate  English_United States.1252  
#  ctype    English_United States.1252  
#  tz       America/New_York            
#  date     2021-03-29                  
# - Packages -------------------------------------------------------------------
#  !  package     * version    date       lib source                                             
#     assertthat    0.2.1      2019-03-21 [1] CRAN (R 4.0.0)                 
#     backports     1.1.6      2020-04-05 [1] CRAN (R 4.0.0)                 
#     bit           1.1-15.2   2020-02-10 [1] CRAN (R 4.0.0)                 
#     bit64         0.9-7      2017-05-08 [1] CRAN (R 4.0.0)                 
#     blob          1.2.1      2020-01-20 [1] CRAN (R 4.0.0)                 
#     callr         3.5.1      2020-10-13 [1] CRAN (R 4.0.3)                 
#     cli           2.3.0      2021-01-31 [1] CRAN (R 4.0.3)                 
#     config        0.3        2018-03-27 [1] CRAN (R 4.0.0)                 
#     crayon        1.3.4      2017-09-16 [1] CRAN (R 4.0.0)                 
#     data.table    1.13.6     2020-12-30 [1] CRAN (R 4.0.3)                 
#     DBI         * 1.1.1      2021-01-15 [1] CRAN (R 4.0.3)                 
#     debugme       1.1.0      2017-10-22 [1] CRAN (R 4.0.3)                 
#     desc          1.2.0      2018-05-01 [1] CRAN (R 4.0.0)                 
#     devtools      2.3.0      2020-04-10 [1] CRAN (R 4.0.0)                 
#     digest        0.6.25     2020-02-23 [1] CRAN (R 4.0.0)                 
#     dplyr         1.0.5      2021-03-05 [1] CRAN (R 4.0.4)                 
#     ellipsis      0.3.0      2019-09-20 [1] CRAN (R 4.0.0)                 
#     evaluate      0.14       2019-05-28 [1] CRAN (R 4.0.0)                 
#     fs            1.4.1      2020-04-04 [1] CRAN (R 4.0.0)                 
#     generics      0.0.2      2018-11-29 [1] CRAN (R 4.0.0)                 
#     glue          1.4.0      2020-04-03 [1] CRAN (R 4.0.0)                 
#     hms           0.5.3      2020-01-08 [1] CRAN (R 4.0.0)                 
#     htmltools     0.5.1.1    2021-01-22 [1] CRAN (R 4.0.3)                 
#     keyring       1.1.0      2020-07-02 [1] local                          
#     knitr         1.30       2020-09-22 [1] CRAN (R 4.0.3)                 
#     lifecycle     1.0.0      2021-02-15 [1] CRAN (R 4.0.4)                 
#     magrittr      2.0.1      2020-11-17 [1] CRAN (R 4.0.3)                 
#     memoise       1.1.0      2017-04-21 [1] CRAN (R 4.0.0)                 
#     odbc        * 1.3.1      2021-03-16 [1] CRAN (R 4.0.4)                 
#     pillar        1.4.4      2020-05-05 [1] CRAN (R 4.0.0)                 
#     pkgbuild      1.0.8      2020-05-07 [1] CRAN (R 4.0.0)                 
#     pkgconfig     2.0.3      2019-09-22 [1] CRAN (R 4.0.0)                 
#     pkgload       1.0.2      2018-10-29 [1] CRAN (R 4.0.0)                 
#     prettyunits   1.1.1      2020-01-24 [1] CRAN (R 4.0.0)                 
#     processx      3.4.5      2020-11-30 [1] CRAN (R 4.0.3)                 
#     ps            1.5.0      2020-12-05 [1] CRAN (R 4.0.3)                 
#     purrr         0.3.4      2020-04-17 [1] CRAN (R 4.0.0)                 
#     r2          * 0.9.11     2021-02-15 [1] local                          
#     R6            2.4.1      2019-11-12 [1] CRAN (R 4.0.0)                 
#     Rcpp          1.0.6      2021-01-15 [1] CRAN (R 4.0.4)                 
#     remotes       2.1.1      2020-02-15 [1] CRAN (R 4.0.0)                 
#     rlang         0.4.10     2020-12-30 [1] CRAN (R 4.0.3)                 
#     rmarkdown     2.4        2020-09-30 [1] CRAN (R 4.0.2)                 
#     rprojroot     1.3-2      2018-01-03 [1] CRAN (R 4.0.0)                 
#     rstudioapi    0.11       2020-02-07 [1] CRAN (R 4.0.0)                 
#     sessioninfo   1.1.1      2018-11-05 [1] CRAN (R 4.0.0)                 
#     testthat    * 3.0.2.9000 2021-03-01 [1] Github (r-lib/testthat@4793514)
#     tibble        3.0.5      2021-01-15 [1] CRAN (R 4.0.3)                 
#     tidyselect    1.1.0      2020-05-11 [1] CRAN (R 4.0.0)                 
#     usethis       1.6.1      2020-04-29 [1] CRAN (R 4.0.0)                 
#     vctrs         0.3.6      2020-12-17 [1] CRAN (R 4.0.3)                 
#     withr         2.4.1      2021-01-26 [1] CRAN (R 4.0.3)                 
#     xfun          0.20       2021-01-06 [1] CRAN (R 4.0.3)                 
#     yaml          2.2.1      2020-02-01 [1] CRAN (R 4.0.0)                 
# [1] C:/Users/r2/R/win-library/4.0
# [2] C:/R/R-4.0.3/library
#  V -- Loaded and on-disk version mismatch.
#  P -- Loaded and on-disk path mismatch.

fyi: odbc compile fail

I'm having a difficult time testing my theory, since for some reason I can not compile the current version of odbc. I've been able to compile it in the past (with Rtools40, same installation), and I can still compile other packages (e.g., jsonlite, as the rtools40 docs suggest as a quick test). While tangential to this issue, if you happen to know quickly how to fix this error, I'll be able to test my theory better and submit a PR.

> devtools::load_all("~/Projects/github/odbc")
Loading odbc
Re-compiling odbc
-  installing *source* package 'odbc' ...
   ** using staged installation
   ** libs
   "C:/Rtools40/mingw64/bin/"g++  -std=gnu++11 -I"c:/R/R-4.0.3/include" -DNDEBUG  -I'C:/Users/r2/R/win-library/4.0/Rcpp/include'     -I. -Icctz/include -Inanodbc   -O2 -Wall  -mfpmath=sse -msse2 -mstackrealign -UNDEBUG -Wall -pedantic -g -O0 -c odbc_result.cpp -o odbc_result.o
   "C:/Rtools40/mingw64/bin/"g++  -std=gnu++11 -I"c:/R/R-4.0.3/include" -DNDEBUG  -I'C:/Users/r2/R/win-library/4.0/Rcpp/include'     -I. -Icctz/include -Inanodbc   -O2 -Wall  -mfpmath=sse -msse2 -mstackrealign -UNDEBUG -Wall -pedantic -g -O0 -c connection.cpp -o connection.o
   "C:/Rtools40/mingw64/bin/"g++  -std=gnu++11 -I"c:/R/R-4.0.3/include" -DNDEBUG  -I'C:/Users/r2/R/win-library/4.0/Rcpp/include'     -I. -Icctz/include -Inanodbc   -O2 -Wall  -mfpmath=sse -msse2 -mstackrealign -UNDEBUG -Wall -pedantic -g -O0 -c nanodbc/nanodbc.cpp -o nanodbc.o
   In file included from C:/Rtools40/mingw64/include/c++/8.3.0/cassert:44,
                    from nanodbc/nanodbc.cpp:33:
   nanodbc/nanodbc.cpp: In function 'std::__cxx11::list<nanodbc::data_source> nanodbc::list_data_sources()':
   nanodbc/nanodbc.cpp:34:30: error: no match for 'operator!' (operand type is 'nanodbc::connection')
    #define NANODBC_ASSERT(expr) assert(expr)
                                 ^~~~~~
   nanodbc/nanodbc.cpp:3467:9: note: in expansion of macro 'NANODBC_ASSERT'
            NANODBC_ASSERT(env);
            ^~~~~~~~~~~~~~
   nanodbc/nanodbc.cpp:34:30: note: candidate: 'operator!(bool)' <built-in>
    #define NANODBC_ASSERT(expr) assert(expr)
                                 ^~~~~~
   nanodbc/nanodbc.cpp:3467:9: note: in expansion of macro 'NANODBC_ASSERT'
            NANODBC_ASSERT(env);
            ^~~~~~~~~~~~~~
   nanodbc/nanodbc.cpp:34:30: note:   no known conversion for argument 1 from 'nanodbc::connection' to 'bool'
    #define NANODBC_ASSERT(expr) assert(expr)
                                 ^~~~~~
   nanodbc/nanodbc.cpp:3467:9: note: in expansion of macro 'NANODBC_ASSERT'
            NANODBC_ASSERT(env);
            ^~~~~~~~~~~~~~
   make: *** [Makevars.win:17: nanodbc.o] Error 1
   ERROR: compilation failed for package 'odbc'
-  removing 'C:/Users/r2/AppData/Local/Temp/RtmpS8cin4/devtools_install_554c4e155121/odbc'
Error in (function (command = NULL, args = character(), error_on_status = TRUE,  : 
  System command 'Rcmd.exe' failed, exit status: 1, stdout + stderr (last 10 lines):
E>          ^~~~~~~~~~~~~~
E> nanodbc/nanodbc.cpp:34:30: note:   no known conversion for argument 1 from 'nanodbc::connection' to 'bool'
E>  #define NANODBC_ASSERT(expr) assert(expr)
E>                               ^~~~~~
E> nanodbc/nanodbc.cpp:3467:9: note: in expansion of macro 'NANODBC_ASSERT'
E>          NANODBC_ASSERT(env);
E>          ^~~~~~~~~~~~~~
E> make: *** [Makevars.win:17: nanodbc.o] Error 1
E> ERROR: compilation failed for package 'odbc'
E> * removing 'C:/Users/r2/AppData/Local/Temp/RtmpS8cin4/devtools_install_554c4e155121/odbc'
Type .Last.error.trace to see where the error occured

The text was updated successfully, but these errors were encountered:

krlmlr · 2021-10-12T02:25:38Z

Thanks for the analysis. Is this a duplicate of #448?

r2evans · 2021-10-12T20:14:51Z

Yes it is, not sure how I didn't see it (it should have percolated to the top, only 1 week prior to my posting here).

krlmlr added the bug an unexpected problem or unintended behavior label Oct 12, 2021

r2evans closed this as completed Oct 12, 2021

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

sqlCreateTable temp table not temp #452

sqlCreateTable temp table not temp #452

r2evans commented Mar 29, 2021

krlmlr commented Oct 12, 2021

r2evans commented Oct 12, 2021

sqlCreateTable temp table not temp #452

sqlCreateTable temp table not temp #452

Comments

r2evans commented Mar 29, 2021

Database

krlmlr commented Oct 12, 2021

r2evans commented Oct 12, 2021