2

I found a strange behaviour of sqldf - it issues "NAs introduced by coercion" warning when a variable exists with the same name as a column and different data type. The following code:

x <- structure(list(euring = c(12380, 12430), species = c("Locustella luscinioides", 
"Acrocephalus schoenobaenus")), .Names = c("euring", "species"
), row.names = 1:2, class = "data.frame")

species <- structure(list(EURING = c(0, 980), Species = c(NA_integer_, NA_integer_
)), .Names = c("EURING", "Species"), row.names = 1:2, class = "data.frame")

require(sqldf)

result <- sqldf("
select species as Species
from x
")

produces:

Warning message: In FUN(X[[i]], ...) : NAs introduced by coercion

and result then contains only NAs.

If variable species didn't exist, everything is OK. But sqldf shoudln't even touch the variable species, right??

EDIT: I suspect it is a bug. I reported it: https://github.com/ggrothendieck/sqldf/issues/23

Tomas
  • 57,621
  • 49
  • 238
  • 373
  • Running that exact code does not give me the warning message. Have you tried restarting your R Session? – Shique Jun 07 '18 at 10:04
  • @Shique wow! I tried to open a new session and no warning issued!! I don't get it. How is this possible?? – Tomas Jun 07 '18 at 10:04
  • 1
    A probable cause would be that some `options()` were set that aren't default. But it can be something else for all I know. – Shique Jun 07 '18 at 10:06
  • I was not setting any options. It can't be in the data either, because I initialize them very simply as you see. There must be something rotten in either R or sqldf! – Tomas Jun 07 '18 at 10:07
  • @Shique I found it! The problem was that there was a data.frame called `species`!! But `sqldf` shouldn't even touch it, right? Please see the updated question. – Tomas Jun 07 '18 at 10:31
  • 1
    This was cross posted to https://github.com/ggrothendieck/sqldf/issues/23 and answered there. – G. Grothendieck Jun 07 '18 at 20:52

1 Answers1

2

It's curious. But you can solve it by referring to the table in the query.

> sqldf("select species as Species from x")
  Species
1      NA
2      NA
Warning message:
In asfn(rs[[i]]) : NAs introduced by coercion
> sqldf("select a.species as Species from x a")
                     Species
1    Locustella luscinioides
2 Acrocephalus schoenobaenus

There is a bug when method is null. method = data.frame is another option.

> sqldf("select species as Species from x", method = data.frame)
                     Species
1    Locustella luscinioides
2 Acrocephalus schoenobaenus
Juan Antonio Roldán Díaz
  • 2,017
  • 8
  • 22
  • interesting, thanks! Anyway, I suspect that the sqldf behaviour is a bug, I reported it. – Tomas Jun 07 '18 at 10:59
  • Yes, is a bug. I have traced the problem and there is an error in the `method`. Fixing method is also fixed. – Juan Antonio Roldán Díaz Jun 07 '18 at 11:51
  • thanks @Juan. How comes that every time I have so much work and I'm in time pressure I have to deal with a bug where it's least expected? ;/ – Tomas Jun 07 '18 at 14:05
  • you are welcome to comment on the issue in GitHub: https://github.com/ggrothendieck/sqldf/issues/23 – Tomas Jun 07 '18 at 14:06