On Fri, Aug 28, 2009 at 1:38 PM, Kevin Grittner<[email protected]> wrote: > Merlin Moncure <[email protected]> wrote: > >> This leads to some very weird behaviors, for example 'coalesce(foo, >> something)' and 'case when foo is null then something else foo end' >> can give different answers. > > Quite apart from the issue you're pursuing, this is another example of > how the COALESCE predicate in PostgreSQL is not compliant with the > standard, where it is *defined as* an abbreviation of the CASE > predicate. > > I might be persuaded otherwise by a reference to the standard, but my > understanding is that the CASE predicate should be conceptually > similar to the "? :" predicate in C. Does anyone else feel that these > aren't implemented quite right in PostgreSQL?
I agree with you...it's a mess. Here's what I'm thinking: 1) 'is null', coalesce, STRICT, PQgetisnull, etc should all behave in consistent manner (and ideally should use the same code paths) 2) make a decision on composite types: 3) If we decide the sql standard is correct, so that (null, null) is null == true, then we should observe rule 1 and make things work in consistent way. This means, for example, that null::foo and (null, null)::foo should not be distinct. 4) If we decide to continue to ignore the standard, so that null::foo is distinct from (null, null)::foo (which is basically how things work now), then IS NULL as currently implemented is wrong and should be changed. 5) plpgsql has a lot of corner cases where composite type behavior is different from sql...POLS violations. For example, to assign a type from a type selected in a query, sometimes you have to do (foo).* and sometimes you have to proxy it through a record variable. input and output arguments are especially vexing. merlin -- Sent via pgsql-bugs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
