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

Answer 1

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

Answer 2

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).

Answer 3

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)