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