[R] RPostgreSQL segfault with LEFT JOIN

[Joe Conway]

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