Hi Paul, I can confirm the behavior has changed for me as well upon upgrade to R 3.1.0. I use RODBC to connect to Microsoft SQL Server. While the exact example you provided didn't produce the bad/changed behavior, it wasn't too hard to find an example that did.
# R 3.1.0 > x <- sqlQuery(channel=con, "SELECT cast(1 as numeric(20,16));")[[1]] > x [1] "1.0000000000000000" > class(x) [1] "character" # R 3.0.3 > x <- sqlQuery(channel=con, "SELECT cast(1 as numeric(20,16));")[[1]] > x [1] 1 class(x) [1] "numeric" Cheers, Robert -----Original Message----- From: r-sig-db-boun...@r-project.org [mailto:r-sig-db-boun...@r-project.org] On Behalf Of Paul Gilbert Sent: Monday, April 14, 2014 8:06 PM To: r-sig-db@r-project.org Subject: [R-sig-DB] RODBC in R-3.1.1 and R-3.0.3 I am seeing a difference in RODBC_1.3-10 behaviour in R-3.1.1 and R-3.0.3 when getting double precision numbers from a database and I am wondering if others are seeing similar behaviour on other systems and with other database engines. This seems to be provoked by the change in type.convert(). This is happening for me with a Linux (Ubuntu 13.10 64-bit) PostgreSQL server, both running R on the server and running R on a Linux client (Mint 15 Cinnamon 2.0.14 64-bit). It should be possible to reproduce it as follows. > require("RODBC") Loading required package: RODBC # assuming user/password/host info in .odbc.ini # and appropriate privileges: > con <- odbcConnect(dsn='test') > sqlQuery(channel=con, "CREATE TABLE ZZ ( id VARCHAR(40), v double precision DEFAULT NULL );") [1] "No Data" > sqlQuery(channel=con, + "INSERT INTO ZZ VALUES ( 'a', 0.2)") character(0) # RODBC in R-3.1.0 then gives > r <- sqlQuery(channel=con, "select v from ZZ where id='a';") > str(r) 'data.frame': 1 obs. of 1 variable: $ v: Factor w/ 1 level "0.20000000000000001": 1 > r <- sqlQuery(channel=con, "select v - 0.2 from ZZ where id='a';") > str(r) 'data.frame': 1 obs. of 1 variable: $ ?column?: int 0 So the loss in accuracy that type.convert uses to decide it should return a factor seems to be generated by extra digits that odbc and/or postgresql are deciding should be added. # RODBC in R-3.0.3 gives > r <- sqlQuery(channel=con, "select v from ZZ where id='a';") > str(r) 'data.frame': 1 obs. of 1 variable: $ v: num 0.2 > print(r, digits=20) v 1 0.2000000000000000111 # RPostgreSQL in R-3.1.0 gives (unchanged from R-3.0.3) > require("RPostgreSQL") # assuming user/password info in .pgpass : > con <- dbConnect("PostgreSQL", dbname='test', host='192.168.1.152') > r <- dbGetQuery(con,"select v from ZZ where id='a';") > str(r) 'data.frame': 1 obs. of 1 variable: $ v: num 0.2 > print(r, digits=20) v 1 0.2000000000000000111 And in the psql client I get test=> select v from ZZ where id='a'; v ----- 0.2 (1 row) I'm working on a work-around for a package that uses RODBC and I am wondering if the difficulty is unique to my setup. Thanks, Paul Gilbert _______________________________________________ R-sig-DB mailing list -- R Special Interest Group R-sig-DB@r-project.org https://stat.ethz.ch/mailman/listinfo/r-sig-db _______________________________________________ R-sig-DB mailing list -- R Special Interest Group R-sig-DB@r-project.org https://stat.ethz.ch/mailman/listinfo/r-sig-db
