[R-sig-DB] RODBC in R-3.1.1 and R-3.0.3

[Paul Gilbert]

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