[R] RPostgreSQL segfault with LEFT JOIN

[Dylan Beaudette]

On Sunday 07 June 2009, Dirk Eddelbuettel wrote:
> On 7 June 2009 at 06:40, Neil Tiffin wrote:
> | I am adding your note to google code issues
> | (http://code.google.com/p/rpostgresql/issues/list ), issue Number 1. 
> | Normally I monitor R-SIG-DB
> | (https://stat.ethz.ch/mailman/listinfo/r-sig-db ).
>
> That list seems most appropriate.
>
> | Hope to look at these in the next couple of weeks.
> |
> | Thanks, for the suggested changes.
>
> Thanks from my end too.
>
> It does compile, and it passes the existing tests that R CMD check runs so
> it looks good so far. It also passes simple
>
>      select foo        from bar
>      select foo as fii from bar
>
> where the second form bombed without the patch.  So thanks!!
>
> [ Incidentally, I am having issues with googlecode.com which no longer
> wants me to connect / update / initialise from home behind NAT and a simple
> firewall:
>
> edd at ron:~/svn/rpostgresql> svn up
> svn: OPTIONS of 'https://rpostgresql.googlecode.com/svn/trunk': could not
> connect to server (https://rpostgresql.googlecode.com)
> edd at ron:~/svn/rpostgresql>
>
> Does anybody know what's happening there?  I can check out etc fine from
> another machine somewhere else. I can connect and authenticate fine to the
> https url using a browswer, it is just svn that croaks.  Ideas ? Does this
> now need rpc or portmap back to me? ]
>
> Dirk
>
> | Neil
> |
> | On Jun 6, 2009, at 11:18 PM, Joe Conway wrote:
> | > Dylan Beaudette wrote:
> | >> After some further investigation, I see that the query works fine
> | >> if I *do not use column aliases* :
> | >
> | > Looks like *any* query using a column alias will segfault unless the
> | > alias exactly matches the column name (in which case why bother).
> | > The code starting at line 423 in RS-PostgreSQL.c looks like:
> | >
> | > 8<-------------------------------
> | >    if(PQftablecol(my_result,j) !=0) {
> | >
> | >    /* Code to find whether a row can be nullable or not */
> | >    sprintf(buff,
> | >            "select attnotnull from pg_attribute
> | >             where attrelid=%d and attname='%s'",
> | >            PQftable(my_result,j),(char*)PQfname(my_result,j));
> | >    res = PQexec (conn, buff );
> | >
> | >    if(strcmp(PQgetvalue(res,0,0),"f")==0) {
> | > 8<-------------------------------
> | > The crash occurs at line 430 (the strcmp()) because PQgetvalue(res,
> | > 0,0) returns NULL.
> | >
> | > PQfname() will return the column alias, not the actual column name,
> | > therefore the PQexec() here returns no results. At the very least,
> | > PQresultStatus(res) or perhaps PQntuples(res) should be used
> | > immediately after PQexec() to ensure you have a good result before
> | > trying to use it in strcmp().
> | >
> | > In any case, I think the simple fix (untested) is something like:
> | >
> | > 8<-------------------------------
> | >    if(PQftablecol(my_result,j) !=0) {
> | >
> | >    /* Code to find whether a row can be nullable or not */
> | >    sprintf(buff,
> | >            "select attnotnull from pg_attribute
> | >             where attrelid=%d and attnum=%d",
> | >            PQftable(my_result,j),PQftablecol(my_result,j));
> | > 8<-------------------------------
> | > i.e. use the table column number and pg_attribute.attnum field.
> | >
> | > This is beyond what is appropriate for r-help, so I suggest any
> | > further discussion go off-list (or is there somewhere more
> | > appropriate, e.g. r-devel?)
> | >
> | > HTH,
> | >
> | > Joe

Thanks for looking into this. Is there any follow-up that I should do? Any 
idea on when the package will be updated on CRAN?

Cheers,
Dylan

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