[R] RSQLite query error

[David James]

Mikkel Grum wrote:
> Dear R-helpers,
> 
> I ran the following little test on RSQLite and got the
> data below from the query.  Unless I've made some
> mistake, the results of both the where and order by
> statements have problems:

This is due to the fact that SQLite as of version 2.8 is typeless
and values are stored as ASCII strings. Most expressions are evaluated 
as string expressions, as in your query.  For details see
http://www.sqlite.org/datatypes.html (SQLite Version 2.8) and
http://www.sqlite.org/datatype3.html (Version 3.0).

You can use a trivial arithmetic expression to force numeric
comparisons (awk users may recognize this trick):

> dbGetQuery(con, 
     paste("select * from arrests where Rape+0.0 > 30",
           "order by Murder+0.0"))

       row_names Murder Assault UrbanPop Rape
    1   Colorado    7.9     204       78 38.7
    2    Arizona    8.1     294       80 31.0
    3 California    9.0     276       91 40.6
    4     Alaska   10.0     263       48 44.5
    5 New Mexico   11.4     285       70 32.1
    6   Michigan   12.1     255       74 35.1
    7     Nevada   12.2     252       81 46.0
    8    Florida   15.4     335       80 31.9

Regards,

--
David
 
> 
> library(RSQLite)
> con<-dbConnect(dbDriver("SQLite"),dbname="test")
> data(USArrests)
> dbWriteTable(con,"arrests",USArrests,overwrite=TRUE)
> dbListTables(con)
> dbReadTable(con,"arrests")
> dbGetQuery(con,paste("SELECT row_names,Murder,Rape
> FROM arrests",
> "WHERE Rape>30 ORDER BY Murder"))
> 
>        row_names Murder Rape
> 1         Alaska   10.0 44.5
> 2     New Mexico   11.4 32.1
> 3       Michigan   12.1 35.1
> 4         Nevada   12.2 46.0
> 5        Florida   15.4 31.9
> 6   North Dakota    0.8  7.3
> 7  New Hampshire    2.1  9.5
> 8          Maine    2.1  7.8
> 9   Rhode Island    3.4  8.3
> 10 West Virginia    5.7  9.3
> 11      Colorado    7.9 38.7
> 12       Arizona    8.1 31.0
> 13    California    9.0 40.6
> 
> I'm running R 2.0.0 on Windows XP.  Should I make a
> bug report or can someone point to an error that I've
> made?
> 
> cheers
> Mikkel
> 
> ______________________________________________
> R-help at stat.math.ethz.ch mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html