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@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 ______________________________________________ R-help@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.