Open GoogleCodeExporter opened 8 years ago
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.
Original comment by bmusi...@aptecgroup.com
on 24 May 2013 at 3:42
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
Original comment by mmu...@gmail.com
on 3 Jul 2013 at 9:09
Original comment by tomoa...@kenroku.kanazawa-u.ac.jp
on 16 Jul 2013 at 12:16
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?
Original comment by jak...@gmail.com
on 7 Jul 2014 at 9:06
Too bad there is no movement here since it really is annoying.
Ping. I just got bitten by this:
Error in mutate_impl(.data, dots) :
Binding not found: COL1.
when trying to run dplyr::mutate
on the results of dbGetQuery
. It works fine with odbc, but fails with RPostgreSQL, because of 0 columns in the empty result set.
Note BTW that the documentation for DBI::dbGetQuery says:
dbGetQuery always returns a data.frame with as many rows as records were fetched and as many columns as fields in the result set, even if the result is a single value or has one or zero rows.
So if I read that right, this violates the spec.
Thanks for working on RPostgreSQL!
For anyone else who encounters this bug, I recommend switching to RPostgres, which doesn't have it. RPostgreSQL seems to be abandoned.
Original issue reported on code.google.com by
bmusi...@aptecgroup.com
on 3 Apr 2013 at 8:52