BUG #5028: CASE returns ELSE value always when type is "char"

It appears the problem is with the "char" being in the ELSE expression,
not being in the CASE expression

[local]:playpen=> select c.relkind, CASE c.relkind WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN
's' THEN 'special' WHEN 'c' THEN 'composite type' WHEN 't' THEN 'toast'
ELSE c.relkind::char
playpen-> END from (select 'r'::"char" AS relkind) c;
relkind | relkind
---------+---------
r | table
(1 row)

Time: 0.674 ms
[local]:playpen=> select c.relkind, CASE c.relkind WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN
's' THEN 'special' WHEN 'c' THEN 'composite type' WHEN 't' THEN 'toast'
ELSE c.relkind::"char"
playpen-> END from (select 'r'::char AS relkind) c;
relkind | relkind
---------+---------
r | t
(1 row)

Joseph Shraibman wrote:

On Tue, Sep 01, 2009 at 04:36:25PM +0000, Joseph Shraibman wrote:

Description: CASE returns ELSE value always when type is "char"

I think it's just silently truncating the literal to a single character.

[local]:playpen=> select c.relkind, CASE c.relkind WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's'
THEN 'special' WHEN 'c' THEN 'composite type' WHEN 't' THEN 'toast' ELSE
c.relkind
playpen-> END from (select 'r'::"char" AS relkind) c;
relkind | relkind
---------+---------
r | t

Here, 'r' maps to the "char" literal 'table' which PG interprets as the
value 't'--i.e. PG silently chops of the 'able'. The bug would seem to
be in your code, but PG could maybe throw an error to tell you this is
what is happening?

A possible fix would be to have your ELSE clause as:

c.relkind::text

As that way the other branches would be interpreted as text and they
wouldn't be getting chopped off along the way.

--
Sam http://samason.me.uk/

Sam Mason wrote:

On Tue, Sep 01, 2009 at 04:36:25PM +0000, Joseph Shraibman wrote:

Description: CASE returns ELSE value always when type is "char"

I think it's just silently truncating the literal to a single character.

[local]:playpen=> select c.relkind, CASE c.relkind WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's'
THEN 'special' WHEN 'c' THEN 'composite type' WHEN 't' THEN 'toast' ELSE
c.relkind
playpen-> END from (select 'r'::"char" AS relkind) c;
relkind | relkind
---------+---------
r | t

Ah, OK it seems I didn't pay close enough attention.

Here, 'r' maps to the "char" literal 'table' which PG interprets as the
value 't'--i.e. PG silently chops of the 'able'.

So the type of what is in the ELSE clause determines the type of the output?

Joseph Shraibman <jks@selectacast.net> writes:

So the type of what is in the ELSE clause determines the type of the output?

If all the other branches are unknown literals, yes.

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> wrote:

Joseph Shraibman <jks@selectacast.net> writes:

So the type of what is in the ELSE clause determines the type of
the output?

If all the other branches are unknown literals, yes.

What's the best place to look to get a handle on what the benefits are
of treating character string literals as being of unknown type?
(Other than the obvious backward compatibility issues.)

-Kevin

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:

Tom Lane <tgl@sss.pgh.pa.us> wrote:

Joseph Shraibman <jks@selectacast.net> writes:

So the type of what is in the ELSE clause determines the type of
the output?

If all the other branches are unknown literals, yes.

What's the best place to look to get a handle on what the benefits are
of treating character string literals as being of unknown type?
(Other than the obvious backward compatibility issues.)

I think the odds of changing that are not measurably different from
zero.

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:

Tom Lane <tgl@sss.pgh.pa.us> wrote:

Joseph Shraibman <jks@selectacast.net> writes:

So the type of what is in the ELSE clause determines the type of
the output?

If all the other branches are unknown literals, yes.

What's the best place to look to get a handle on what the benefits
are of treating character string literals as being of unknown type?
(Other than the obvious backward compatibility issues.)

I think the odds of changing that are not measurably different from
zero.

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.

-Kevin

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

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.

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

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.

But you can't have both of those desirable behaviors unless you are
somehow aware that "'January 01, 2009'" is something more malleable than
"now()" in example #2. Calling the RHS "unknown" in example #2 gives us
that information.

Regards,
Jeff Davis

On Tue, Sep 01, 2009 at 05:49:25PM +0100, Sam Mason wrote:

PG could maybe throw an error to tell you this is
what is happening?

Would something like the included patch be accepted?

--
Sam http://samason.me.uk/

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

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:

Jeff Davis <pgsql@j-davis.com> wrote:

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.

Because then we would have to provide implicit casts from text to
everything else, which would be horribly dangerous.

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';

[ shrug... ] The current design is a compromise between usability
and strictness of semantics. This proposal appears to be all strictness
and no usability. No thanks ... even without any backward-compatibility
considerations, I wouldn't find this to be an improvement.

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> wrote:

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:

No, that's not it. I'm wondering why it isn't treated as text.
Period. Full stop. Nothing to infer.

Because then we would have to provide implicit casts from text to
everything else, which would be horribly dangerous.

I would like that even less. I like errors on type conflicts.

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';

The current design is a compromise between usability and strictness
of semantics. This proposal appears to be all strictness and no
usability.

I was not proposing anything; I was trying to understand the reasons
for the current behavior so that I could think about what might make
sense to address some of the places where current behavior causes a
result which is different from a non-error result should be obtained
under the standard. I couldn't begin to anticipate what might be
acceptable in these situations without understanding the reason things
are as they are.

I do understand that there will be "convenience" extensions to the
standard -- all products do that. I wasn't sure whether that was the
reason for the behavior or whether there was something else in play.

Thanks for clarifying,

-Kevin

Sam Mason <sam@samason.me.uk> writes:

On Tue, Sep 01, 2009 at 05:49:25PM +0100, Sam Mason wrote:

PG could maybe throw an error to tell you this is
what is happening?

Would something like the included patch be accepted?

ISTM this was debated once before and rejected. However, there's a
proposal over here
http://archives.postgresql.org/pgsql-hackers/2009-08/msg02073.php
to tighten up what the "char" input functions will take, and maybe
throwing error for excess input would fit in with that.

regards, tom lane

On Wed, 2009-09-02 at 08:57 -0500, Kevin Grittner wrote:

(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.

If you have very many implicit casts, I think you lose the
predictability and safety you're looking for, and/or end up with a lot
of errors that eliminate the convenience of implicit casting.

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

The original problem has more to do with the fact that interpreting an
unknown value as a char seems to just discard a lot of information. I
assume that's part of the standard, but it seems like a bad idea any
time you silently discard data (which is why we prevented varchar(n)
from silently truncating a while ago).

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.

They are desirable for a system that infers types from context. I agree
that there's more safety by explicitly declaring the type of all
literals; but I disagree that using implicit casts to make up for a lack
of an "unknown" type will improve matters (either for convenience or
safety).

Regards,
Jeff Davis

On Wed, Sep 2, 2009 at 3:44 PM, Jeff Davis<pgsql@j-davis.com> wrote:

On Wed, 2009-09-02 at 08:57 -0500, Kevin Grittner wrote:

  (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.

If you have very many implicit casts, I think you lose the
predictability and safety you're looking for, and/or end up with a lot
of errors that eliminate the convenience of implicit casting.

Perhaps we should stop thinking of "unknown" as, er, "unknown" and
think of it as "text literal". A text literal has implicit casts to
every data type but a normal text string has to be explicitly cast.

Hm, that's not quite right because things like array(1)||'5' don't
treat the '5' as a text literal. The "implicit cast" is preferred to
treating it as text.

--
greg
http://mit.edu/~gsstark/resume.pdf

Jeff Davis <pgsql@j-davis.com> wrote:

I disagree that using implicit casts to make up for a lack of an
"unknown" type will improve matters

I certainly never meant to imply that additional implicit casts should
be added. I apologize for not being more clear about that.

Thanks again for helping fill in the blanks.

-Kevin

On Wed, Sep 02, 2009 at 03:50:05PM +0100, Greg Stark wrote:

Perhaps we should stop thinking of "unknown" as, er, "unknown" and
think of it as "text literal". A text literal has implicit casts to
every data type but a normal text string has to be explicitly cast.

How does that help things? You seem to be keeping the semantics and
only changing the name, when it's the semantics that you seem to be
complaining about.

I'm pretty sure it's correct to treat it as "unknown", other
type-systems do this and it all works well. The thing that makes things
ambiguous is the ad-hoc polymorphism that exists in function calls
and operators. With PG's type system you know almost nothing about
any types involved in an arbitrary expression, operators are better
than functions (given the types of the arguments you know the return
type, with default parameters even this knowledge doesn't exist with
functions) but still leave things far too open to have any rigor without
spelling out types in full everywhere. For example:

CREATE FUNCTION add(int,int) RETURNS int LANGUAGE sql
AS $$ SELECT $1 + $2; $$;

CREATE FUNCTION add(int,int,int DEFAULT NULL) RETURNS text LANGUAGE sql
AS $$ SELECT ($1 + $2)::text; $$;

What type should it attribute to the result of:

SELECT add(1,2);

In fact it doesn't seem to want to play ball at all. Even given the
apparently unambiguous:

SELECT 1+add(1,2);
or
SELECT 'hi'||add(1,2);

It doesn't get anywhere. No need for "text 'hi'" in the second one
because || isn't defined for values of integer type.

--
Sam http://samason.me.uk/

On Wed, Sep 2, 2009 at 11:55 AM, Sam Mason<sam@samason.me.uk> wrote:

In fact it doesn't seem to want to play ball at all.  Even given the
apparently unambiguous:

 SELECT 1+add(1,2);
or
 SELECT 'hi'||add(1,2);

It doesn't get anywhere.  No need for "text 'hi'" in the second one
because || isn't defined for values of integer type.

Right. This is exactly the sort of thing that languages with real
type inference have no problem handling. Of course, ML for example
doesn't allow overloading precisely because (AIUI) it makes type
inference difficult. It would be awesome if we could make this work
though.

...Robert

On Wed, Sep 02, 2009 at 12:36:00PM -0400, Robert Haas wrote:

On Wed, Sep 2, 2009 at 11:55 AM, Sam Mason<sam@samason.me.uk> wrote:

In fact it doesn't seem to want to play ball at all. Even given the
apparently unambiguous:

SELECT 1+add(1,2);
or
SELECT 'hi'||add(1,2);

It doesn't get anywhere. No need for "text 'hi'" in the second one
because || isn't defined for values of integer type.

Right. This is exactly the sort of thing that languages with real
type inference have no problem handling. Of course, ML for example
doesn't allow overloading precisely because (AIUI) it makes type
inference difficult. It would be awesome if we could make this work
though.

Difficult, but not intractable. Haskell has done this sort of thing for
quite a while; although it handles ad-hoc polymorphism differently than
PG does. You basically end up saying how much polymorphism you want
to allow for each function, for example the return type of an operator
(in PG) is determined exactly by the type of its arguments. In Haskell
you would have the power to say, if you so wanted, that the type of an
operator's RHS is determined exactly by the type of its LHS and return
type, or even, in the most general case, that it's parametrized over all
three types. Obviously the more types you leave free the more typing
you have to do specifying all the types as the type-inference has less
leverage to work with.

I've been trying to think about how to apply a more modern type system
to PG for a while and hence my comments about how things like NULL
rows should be handled are based on this and may come across as rather
dogmatic sometimes, it's about the only way I can get things to hold
together without introducing much more complexity than seems strictly
necessary.

--
Sam http://samason.me.uk/