[R] RPostgreSQL segfault with LEFT JOIN

[Dylan Beaudette]

Hi,

I recently upgraded to R 2.9.0 on linux x86. After doing so, I switched to the 
RPostgreSQL package for interfacing with a postgresql database. I am using 
postgresql  8.3.7.

A query that works from the postgresql terminal is causing a segfault when 
executed from R.

My sessionInfo, the error message, and the R code used to generate the error 
are listed below.

I have noticed that a trivial query (SELECT 1 as value) or other queries seem 
to work fine. It is only when I enable the LEFT JOIN (see below) that I get a 
segfault. Could this be related to the treatment of null values?

Any ideas?
Thanks!
Dylan

Here is the code that caused the error
----------------------------------------------------------------------------------------------------
# libs
library(RPostgreSQL)

## query DB
q <- "
SELECT deb_lab_data.*
-- matrix_wet_color_hue as hue, matrix_wet_color_value as value, 
matrix_wet_color_chroma as chroma
FROM deb_lab_data
-- LEFT JOIN horizon USING (pedon_id, hz_number)
WHERE deb_lab_data.pedon_id ~~ '%SJER%'
ORDER BY deb_lab_data.pedon_id, deb_lab_data.top ASC "

# create an PostgreSQL instance and create one connection.
drv <- dbDriver("PostgreSQL")
conn <- dbConnect(drv, host="localhost", dbname="XXX", user="XXX")
query <- dbSendQuery(conn, q)
x <- fetch(query, n = -1) # extract all rows
----------------------------------------------------------------------------------------------------

Here is the error message in R:
----------------------------------------------------------------------------------------------------
row number 0 is out of range 0..-1

 *** caught segfault ***
address (nil), cause 'memory not mapped'

Traceback:
 1: .Call("RS_PostgreSQL_exec", conId, statement, PACKAGE 
= .PostgreSQLPkgName)
 2: postgresqlExecStatement(conn, statement, ...)
 3: is(object, Cl)
 4: is(object, Cl)
 
5: .valueClassTest(standardGeneric("dbSendQuery"), "DBIResult",     "dbSendQuery")
 6: dbSendQuery(conn, q)
----------------------------------------------------------------------------------------------------



Here are the details on my R install:
----------------------------------------------------------------------------------------------------
R version 2.9.0 (2009-04-17) 
i686-pc-linux-gnu 

locale:
LC_CTYPE=en_US.UTF-8;LC_NUMERIC=C;LC_TIME=en_US.UTF-8;LC_COLLATE=en_US.UTF-8;LC_MONETARY=C;LC_MESSAGES=en_US.UTF-8;LC_PAPER=en_US.UTF-8;LC_NAME=C;LC_ADDRESS=C;LC_TELEPHONE=C;LC_MEASUREMENT=en_US.UTF-8;LC_IDENTIFICATION=C

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] RPostgreSQL_0.1-4 DBI_0.2-4
----------------------------------------------------------------------------------------------------



-- 
Dylan Beaudette
Soil Resource Laboratory
http://casoilresource.lawr.ucdavis.edu/
University of California at Davis
530.754.7341