Re: BUG #5028: CASE returns ELSE value always when type is"char" - Mailing list pgsql-bugs

Jeff Davis <pgsql@j-davis.com> wrote:
> On Tue, 2009-09-01 at 13:49 -0500, Kevin Grittner wrote:
>> I figured that; I'm just trying to understand what seems to me like
>> an odd wart on the type system.  I figure I must be missing
>> something important, so I'd kinda like to find out what that is.
>
> If I understand your question, you're comparing:
>
>   (a) leaving a literal as "unknown" until you've finished
>       inferring types (current behavior)
>   (b) casting every unknown to text immediately, and then trying to
>       infer the types

No, that's not it.  I'm wondering why it isn't treated as text.
Period.  Full stop.  Nothing to infer.  Anywhere that we have implicit
casts defined from text to something else could, of course, still
operate; but it would be text.  No guessing.

> In general, option (b) eliminates information that might be useful
> for making good inferences about the correct operators to use, and
> also finding cases of ambiguity.

It often seems to have the opposite effect.  See the original post.

> For instance, consider the current behavior:
>
>   1. select now()::text < 'January 01, 2009'; -- true
>   2. select now()       < 'January 01, 2009'; -- false
>   3. select now()       < 'January 01, 2009'::text;
>      ERROR:  operator does not exist: timestamp with time zone <
>              text

In my view, it is wrong that any of those work.  I would expect to
have to code one of these:

select now() < date '2009-01-01';  -- implicit casts should cover
select now() < timestamp with time zone '2009-01-01 00:00:00.0';

I understand that there is probably a huge base of existing code which
counts on being able to be sloppy with types and have PostgreSQL
automagically infer types other than what is actually specified; but
I'd rather not expose such sloppy behavior to those running ad hoc
queries at my site.

> Example #2 shows that we can infer the the RHS is of type
> timestamptz based on the type of the LHS. That's desirable behavior
> in any type-inferencing system -- without it you might as well just
> explicitly cast all literals. Example #3 is ambiguous: we have no
> way to know whether to choose "< (timestamptz, timestamptz)" or "<
> (text, text)", and an ERROR is desirable behavior to avoid confusing
> results.

Here I think you have answered my question.  It is seen as a feature,
since it allows people to avoid the extra keystrokes of coding
type-specific literal values, and allows them the entertainment of
seeing how the values get interpreted.  :-)

> But you can't have both of those desirable behaviors

Whether they are desirable is the point of disagreement.  At least I
now understand the reasoning.

Thanks,

-Kevin