On Thu, Apr 03, 2008 at 12:24:17AM +0200, Peter Eisentraut wrote: > Jeff Dwyer wrote: > > This works fine: > > select 1 where current_date between '1900-3-3' and '1900-2-2'; > > This doesn't: > > select 1 where current_date between coalesce(null,current_date) and > > coalesce(null, '1900-1-2'); > > > > This fix works: > > select 1 where current_date between coalesce(null,current_date) and > > coalesce(null, date('1900-1-2')); > > > > This seems like a bug to me. Why should an explicit cast be necessary after > > a coalesce? > > Because coalesce(null, '1900-1-2') has no other type information attached, so > it would have picked text by default as result type, and that then clashes > with the result type of coalesce(null,current_date), which can be derived to > be date. This is a robustness improvement: 8.2 and earlier would silently > accept coalesce(null, 'abc') and apply text-semantics comparison.
The types look as though they could be interpreted unambiguously and correctly very easily. Parametric polymorphism and some basic type inference would easily be able to resolve this. BETWEEN would have the following type (very informally presented; lower case characters stand for type variables, Titlecase for type names, UPPERCASE for identifiers!): Boolean (t BETWEEN t AND t) i.e. when BETWEEN is called all the types must be the same. COALESCE is also parametrised over a single type: t COALESCE(t,t) NULLs could be encoded in the type system in many ways as long it had a polymorphic type. The type system should realise that "current_date" is of type Date and because NULL is polymorphic the COALESCEs would unify, both returning values of type Date, which would in turn unify with the BETWEEN operator resulting in a value of BOOLEAN type, which is exactly what the WHERE clause expects. This sort of type inference has been known (and extensively studied) for about 50 years now, it always surprises me how little it's known outside the functional programming community (ML and Haskell being the old guard). Apparently, according to the fountain of wisdom that is Wikipedia, It's finally starting to break into very mainstream languages like the next versions of VB9 and C#3. Moving an existing implementation over to a new type system is an entirely non-trivial matter though! Sam -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs