I wrote:
> Tomas Vondra <tomas.vondra@2ndquadrant.com> writes:
>> So, which part of this supposedly does not work in PostgreSQL?
> The part where it infers that b.sno is unique based solely on it having
> been equated to a.sno.
Oh, wait a second: such an inference is actually *wrong* in the general
case, or at least underdetermined. It fails in cases where the data type
considers distinguishable values to be "equal", as for example zero vs.
minus zero in IEEE floats, or numeric values with varying numbers of
trailing zeroes, or citext, etc. So for example if the sno columns are
type citext, we can be sure that a.sno does not contain both 'X' and 'x',
because the pkey would forbid it. But if it contains 'X', while b.sno
contains both 'X' and 'x', then (if we allowed this case) it'd be
indeterminate which b.sno value is returned by the GROUP BY. One might or
might not consider that OK for a particular application, but I don't think
the parser should just assume for you that it is.
regards, tom lane
