Re: BUG #4085: No implicit cast after coalesce

The following bug has been logged online:

Bug reference: 4085
Logged by: Jeff Dwyer
Email address: jdwyer(at)patientslikeme(dot)com
PostgreSQL version: 8.3.1
Operating system: Mac OS X
Description: No implicit cast after coalesce
Details:

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? This broke code that worked in 8.1.

Thanks,
-Jeff

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.

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Jeff Dwyer wrote:
>> 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.

Yes. The query "worked" in pre-8.3 only for rather small values of
"work": if you had been using a non-ISO datestyle the comparisons would
in fact have come out wrong. Also, it being a textual rather than date
comparison, any index on the date column being compared to wouldn't have
been used.

regards, tom lane

OK, worksforme. I guess I still find it odd, but I much prefer
explicitness & robustness to small values of 'work'.

Thanks for the prompt response.

-Jeff

On Apr 2, 2008, at 7:15 PM, Tom Lane wrote:

> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>> Jeff Dwyer wrote:
>>> 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.
>
> Yes. The query "worked" in pre-8.3 only for rather small values of
> "work": if you had been using a non-ISO datestyle the comparisons
> would
> in fact have come out wrong. Also, it being a textual rather than
> date
> comparison, any index on the date column being compared to wouldn't
> have
> been used.
>
> regards, tom lane

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