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