FreeTDS Development Group

["Alistair Gee" <alistair.gee AT gmail.com>]

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