Skip to Content.
Sympa Menu

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

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Alistair Gee" <alistair.gee AT gmail.com>
  • To: freetds AT lists.ibiblio.org
  • Subject: [freetds] bug? Last row of query result is duplicated
  • Date: Mon, 18 Jun 2007 20:04:59 -0700

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




Archive powered by MHonArc 2.6.24.

Top of Page