[freetds] bug? Last row of query result is duplicated

Alistair Gee alistair.gee at gmail.com
Mon Jun 18 23:04:59 EDT 2007


I'm seeing a bug where the last row of a query result is duplicated.

System:
* Debian Linux x86-64 (AMD64)
* freetds 0.63
* unixodbc 2.2.11
* Microsoft SQL Server 2000
* freetds protocol 8.0
* Programming language: Gnu R 2.5


How to reproduce:

1. Create sample table and data:

create table bar (a int)
go
insert into bar (a) values(1)
insert into bar (a) values(2)
go

2. I see this bug from both Gnu R and from Common Lisp (SBCL), so I
   suspect that the bug is not in the R or Common Lisp libraries. Here
   is the script for Gnu R:

library(RODBC)                          # load R's ODBC library
## connect to database
db <- odbcConnect("dsn", "user", "password", believeNRows=FALSE)

## do query
print(sqlQuery(db, "
select t.a,
       min(t.c)
from  (select a,
              sum(case when 0=1 then 1 end) c
       from   bar
       group by a) t
group by t.a
"))

This query returns 3 rows (the 2nd row is duplicated):

  1 NA
  2 NA
  2 NA

But it should return 2 rows:

  1 NA
  2 NA

(For those unfamiliar with R, NA corresponds to NULL.)

The example might look contrived, but I took a real query and
reduced it to the simpler case shown above.

However, sqsh (correctly) returns 2 rows.

3. Strangely, the following variations of the query do not have the bug:

select t.a,
       min(t.c)
from  (select a,
              sum(case when 0=1 then 1 else 0 end) c
       from   bar
       group by a) t
group by t.a


select t.a,
       min(t.c)
from  (select a,
              null c
       from   bar
       group by a) t
group by t.a


select t.a
from  (select a,
              sum(case when 0=1 then 1 end) c
       from   bar
       group by a) t
group by t.a


More information about the FreeTDS mailing list