Issue 51 in rpostgresql: dbGetQuery handles empty recordsets in a problematic manner

[rpost...@googlecode.com]

Status: New
Owner: ----
Labels: Type-Defect Priority-Medium

New issue 51 by bmusi...@aptecgroup.com: dbGetQuery handles empty
recordsets in a problematic manner
http://code.google.com/p/rpostgresql/issues/detail?id=51

What steps will reproduce the problem?
1. Create a table with 2 or more columns:
dbGetQuery(con, "CREATE TABLE junk (col1 INTEGER, col2 VARCHAR(100))");

2. Use dbGetQuery() to retrieve a data.frame with the contents of this
table:
a <- dbGetQuery(con, "SELECT * FROM junk");

3. I would expect to receive back a data.frame with 0 rows and 2 columns
named "col1" and "col2". Instead, I get back:
print(a)
data frame with 0 columns and 0 rows

If I try to use this data.frame naively by commands like:
print(a[1])
I get errors like:
Error in .subset2(x, i, exact = exact) : subscript out of bounds

4. In contrast, if the table is not empty, I get a data.frame with the
appropriate number of rows and two columns:
dbGetQuery(con, "INSERT INTO junk VALUES (1, 'wowee')");
a <- dbGetQuery(con, "SELECT * FROM junk");
print(a)

col1 col2
1 1 wowee

Now the returned data.frame behaves in the expected manner:
print(a[1])

col1
1 1

What is the expected output? What do you see instead?

I would expect to get back a data.frame with a predictable structure from a
dbGetQuery() regardless of whether or not the recordset is empty. With the
current design, I need to check every return from dbGetQuery() to see if it
is empty before I attempt to access columns or use the return result in a
merge() or match() function. A common code construct for my application
looks like:
Lookup <- dbGetQuery(con, "SELECT id, description FROM lookup_table");
F <- merge(my_data, Lookup, by.x="id", by.y="id", all.x=TRUE,
all.y=FALSE);

but the merge fails if the lookup table happens to be empty.

Note that the R database interface to SQLite3 returns a zero-row data.frame
with the correct columns when the dbGetQuery() returns no rows. The
problem therefore is not intrinsic to DBI, but seems to be specific to
RPostgreSQL.

What version of the product are you using? On what operating system?
R 2.15.2, RPostgreSQL 0.4 compiled on R 2.15.3, Windows 7 64-bit

Please provide any additional information below.



--
You received this message because this project is configured to send all
issue notifications to this address.
You may adjust your notification preferences at:
https://code.google.com/hosting/settings

[rpost...@googlecode.com]

Comment #1 on issue 51 by bmusi...@aptecgroup.com: dbGetQuery handles empty

recordsets in a problematic manner
http://code.google.com/p/rpostgresql/issues/detail?id=51

Looking through the source code, it seems likely that the system being used
to detect whether the statement was a SELECT is failing when the result set
has zero length, and this causes the return of a data.frame with 0 rows and
0 columns.

[rpost...@googlecode.com]

Comment #2 on issue 51 by mmu...@gmail.com: dbGetQuery handles empty

recordsets in a problematic manner
http://code.google.com/p/rpostgresql/issues/detail?id=51

i glanced quickly through the source and believe the easiest fix would be
at the R-code level.
in PostgreSQL.R, DBIResult class methods that return result set data frames
(e.g. "fetch"), have this check:

out <- postgresqlFetch(res, n, ...)
if(is.null(out))
out <- data.frame(out)
out

the casting of the null "out" to a data frame is where the 0-row-0-col data
frame is created.
instead of that casting, i'd use a function specifically designed to return
a "smart" empty data frame, like so:

emptyResultDataFrame <- function(res)
{
klasses <- dbColumnInfo(res)$Sclass
names(klasses) <- dbColumnInfo(res)$name
data.frame(sapply(klasses, function(klass) eval(parse(text =
sprintf("%s(0)", klass))), simplify = FALSE))
}


and thus alter the existing code blocks that look like so:

out <- postgresqlFetch(res, n, ...)
if(is.null(out))
out <- data.frame(out)
out

to:

out <- postgresqlFetch(res, n, ...)
if(is.null(out))
out <- emptyResultDataFrame(res)
out


NOTE 1: i'm pretty sure there's a better way to initialize an empty vector
from a class name stored as a string (saving the trouble of my eval/parse
trick above), so people more familiar with low-level expressions in R can
probably make an improvement there.

NOTE 2: there's also the chance for a bug here, since i'm *assuming* that
the Sclass field in the getColumnInfo() table returns valid R classes/types
which have 'empty' constructors that follow the 'numeric(0)'
or 'character(0)' or 'logical(0)' etc. paradigm.
there's probably also a safer way to initialize an object of zero-length
with the class name as a string, as mentioned in NOTE 1, and this would
also help prevent the possible bug introduced by my assumption.

NOTE 2b: i'm aware that "vector(klass, 0)" will work, but only for base
('mode') types... so i'm not sure if the Sclass field can be populated by
high-level types in R that might break the "vector()" function, which is
why i opted for the eval/parse trick instead.

anyhow, i rolled this fix into a forked version of RPostgreSQL on my system
and it works like a charm to fix the annoying 0-row-0-column bug, so feel
free to include some version of this in the next release.

cheers,

-murat

[rpost...@googlecode.com]

Updates:
Labels: -Priority-Medium Priority-High

Comment #3 on issue 51 by tomoa...@kenroku.kanazawa-u.ac.jp: dbGetQuery

handles empty recordsets in a problematic manner
http://code.google.com/p/rpostgresql/issues/detail?id=51

(No comment was entered for this change.)

[rpost...@googlecode.com]

Comment #4 on issue 51 by jak...@gmail.com: dbGetQuery handles empty

recordsets in a problematic manner
http://code.google.com/p/rpostgresql/issues/detail?id=51

Any update on this? We're still seeing this. The result of dbGetQuery might
or might not have column names, depending on if the result is empty. Is
there any way to get the column names from the empty result without
querying for them?