[R] RPostgreSQL segfault with LEFT JOIN

[Dylan Beaudette]

On Thursday 04 June 2009, Dirk Eddelbuettel wrote:
> On 4 June 2009 at 16:17, Dylan Beaudette wrote:
> | 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?
>
> As per some recent messages on the r-sig-db list, I think that the error is
> due to a bug in the handling of 'schema.table' queries.  If you just use
> 'select ... from table' you're fine.
>
> Not sure if this helps you -- someone has to go in and fix the bug.
>
> Dirk

Thanks Dirk,

After some further investigation, I see that the query works fine if I *do not 
use column aliases* :

# segfaults:
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 "


# works fine:
q <- "
SELECT deb_lab_data.* ,
matrix_wet_color_hue, matrix_wet_color_value, 
matrix_wet_color_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 "


Very strange...

Dylan



> | 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.UT
> |F-8;LC_MONETARY=C;LC_MESSAGES=en_US.UTF-8;LC_PAPER=en_US.UTF-8;LC_NAME=C;L
> |C_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
> |
> | ______________________________________________
> | R-help at r-project.org mailing list
> | https://stat.ethz.ch/mailman/listinfo/r-help
> | PLEASE do read the posting guide
> | http://www.R-project.org/posting-guide.html and provide commented,
> | minimal, self-contained, reproducible code.



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