Re: Wish: remove ancient constructs from Postgres

On Mon, 27 Feb 2006, Nikolay Samokhvalov wrote:

>it's completely different thing. look at the spec and you'll
>understand the difference. in two words, with 'DISTINCT ON' we lose
>some values (from some columns), when UNION not (it just removes
>duplicates, comparing _entire_ rows).

No it's not, really. Read the spec.

The output of a union on a text field is non-deterministic (due to some
collation choices). This means that the output of the query may be
determined by an effectively random choice of which value to use.

Basically AFAICT something like (modulo simple errors):

select foo from (
select foo from tablea union select foo from tableb
)
where foo = 'A' collate case_sensitive

can give different results in the case of tablea having 'A' and tableb
having 'a' if the union is using a case insensitive comparison.