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.