Why is Select distinct from function returning duplicates?

Question

I tried two different variations on the same thing. The first version selects from freetexttable, the other insets into a temp table and selects from that. I've tried numerous variations on the first version (select several combinations, at both levels of scope, of group by, distinct, and casting [rank] to an integer. Regardless, the first query consistently returns 3 rows each having value 137 whereas the second query consistently returns 1 row having value of 137.

What is going on here? Why does freetext return duplicates and why aren't they eliminated with select distinct or with group by?

Note: I want to know why, not how to fix it. I already have acceptable workarounds.

select * from
(
select distinct [rank] from freetexttable(dbo.vw_PPN, allKeywords, N'foo', 100000 )
where [key] = 3781054
) as CT

create table #temp ([rank] int)
insert into #temp
    select distinct [rank] from freetexttable(dbo.vw_PPN, allKeywords, N'foo', 100000 )
    where [key] = 3781054
select * from #temp
drop table #temp

score 0 · Answer 1 · answered Nov 04 '09 at 20:17

I'd guess by the fact that you're casting rank to an integer that it is actually a float? If so, then my next guess would be that it comes down to typical floating point comparison issues.

Regarding your temp table, what you're doing is selecting all the duplicate data, putting it into the temp table verbatim, then just dumping it out, duplicates and all. This might have more success

create table #temp ([rank] int)
insert into #temp
    select [rank] from freetexttable(dbo.vw_PPN, allKeywords, N'foo', 100000 )
    where [key] = 3781054
select distinct [rank] from #temp
drop table #temp

No, the temp table version does not have the duplicates. And I thought maybe it rank was a float, but really I was just trying random stuff to try to figure it out. If floating point issues were the problem (which I doubt), casting it in the inner select and then picking distinct in the outer select would have fixed this. — Brian, Nov 04 '09 at 20:21

score 0 · Answer 2 · answered Nov 04 '09 at 20:40

0

What does the subquery in the first query return, if you run it standalone? Running SELECT * FROM (SELECT DISTINCT ...) is a bit strange (although it should of course return exactly the same thing as the inner query).

answered Nov 04 '09 at 20:40

Arthur Reutenauer

2,622
1
17
15

It returns exactly the same thing (3 instances of 137). – Brian Nov 04 '09 at 21:50
The reason I run Select * from (Select distinct ...) is because I tried many different permutations of distinct, group by, and casting in hopes of getting lucky and making it work (which would then give me more information to use in seeking understanding). – Brian Nov 04 '09 at 23:01
That's a riddle, then... I really have no idea what might by happening, sorry. – Arthur Reutenauer Nov 05 '09 at 15:18

score 0 · Answer 3 · answered Sep 06 '11 at 10:49

I can never ignore a good mystery, but in this case I just couldn't reproduce this behavior. Either its something to do with that freetexttable or maybe you posted a different version of the query which doesn't exhibit the issue. It would have been nice if we could look at the result of :

Select * from freetexttable(dbo.vw_PPN, allKeywords, N'foo', 100000 )

Replacing your freetexttable with a subquery to allow testing, I get one row in both cases, even though the subquery has three like you said :

Select Distinct [rank] from 
    (select 137 as [rank], 3781054 as [key] union all 
     select 137, 3781054 union all 
     select 137, 3781054) x
Where [key] = 3781054

rank

137

(1 row(s) affected)

create table #temp ([rank] int)
insert into #temp
    select distinct [rank] from 
        (select 137 as [rank], 3781054 as [key] union all 
         select 137, 3781054 union all 
         select 137, 3781054) x
    where [key] = 3781054
select * from #temp
drop table #temp

(1 row(s) affected)

rank

137

(1 row(s) affected)

Unfortunately, I don't know what code I had that caused this issue; I spent 15 minutes looking and didn't find it (or even any instances of "freetexttable") in the database. I think at some point whatever feature this was intended to support was dropped. Sorry. — Brian, Sep 06 '11 at 17:36

Why is Select distinct from function returning duplicates?

3 Answers3