When to use DISTINCT When is it good to use SELECT DISTINCT .. and when not?

Basically whenever I see DISTINCT keyword I start to get cautious as most of the cases it is ends up finding that actual query has some problems (not selective enough, bad joins, incorrect logic and so on).

Do you have similar experience or is it just me?
SELECT DISTINCT is sometimes an indication that someone has 'fixed' a query that returns duplicate rows in a naive way. This (mis-)use is probably more common among those with relatively little database experience.

That said, some people (Chris Date, for example) argue that DISTINCT should be the default qualifier for SELECT; we should have to write SELECT ALL to return a non-relational result (relations do not contain duplicate tuples by definition).

My view is that there is nothing inherently wrong with SELECT DISTINCT. It can be the most natural way to express a query. If the execution plan and performance is acceptable, I see no sensible reason to mandate that it should never be used.

At the same time, it is clearly wrong to simply add DISTINCT at the top level of a complex query to eliminate duplicates at a late stage, when the query should be simply written more efficiently.