Re: [BUGS] Failure to coerce unknown type to specific type

My apologies if much of this is already assumed knowledge by most
-hackers...I'm trying to learn from observation instead of, largely,
reading code in a foreign language.

On Wed, Apr 22, 2015 at 6:40 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:

> Moving thread to -hackers.
>
> On Wed, Apr 8, 2015 at 11:18 PM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
> > That example was just for illustration. My other example didn't require
> > creating a table at all:
> >
> >
> ​​
> ​​
> SELECT a=b FROM (SELECT ''::text, ' ') x(a,b);
> >
> > it's fine with me if we want that to fail, but I don't think we're
> > failing in the right place, or with the right error message.
> >
> > I'm not clear on what rules we're applying such that the above query
> > should fail, but:
> >
> >
> ​​
> SELECT ''::text=' ';

>
> > should succeed. Unknown literals are OK, but unknown column references
> > are not? If that's the rule, can we catch that earlier, and throw an
> > error like 'column reference "b" has unknown type'?
>

But the fact that column "b" has the data type "unknown" is only a warning
- not an error.

This seems to be a case of the common problem (or, at least recently
mentioned) where type conversion only deals with data and not context.

http://www.postgresql.org/message-id/CADx9qBmVPQvSH3+2cH4cwwPmphW1mE18e=WUmLFUC-QZ-t7Q6Q@mail.gmail.com

Additional hinting regarding the column containing the offending data would
be welcomed by the community - but I suspect it is a non-trivial endeavor.

> Is the behavior of unknown literals vs. unknown column references
> documented anywhere? I tried looking here:
> http://www.postgresql.org/docs/devel/static/typeconv.html, but it
> doesn't seem to make the distinction between how unknown literals vs.
> unknown column references are handled.
>
> My understanding until now has been that unknown types are a
> placeholder while still inferring the types. But that leaves a few
> questions:
>
> 1. Why do we care whether the unknown is a literal or a column reference?
>

Apparently the difference is in when non-implicit casts can be used for
coercion - or, rather, when input functions can be used instead of casting
functions.

in ​SELECT ' '::text = 'a' the explicit cast between the implicit unknown
and text is used while going through the subquery forces the planner to
locate an implicit cast between the explicit unknown and text.

​The following fails no matter what you try because no casts exist from
unknown to integer:

​​SELECT a::int=b FROM (SELECT '1', 1) x(a,b);

but this too works - which is why the implicit cast concept above fails
(I'm leaving it since the thought process may help in understanding):

SELECT 1 = '1';

From which I infer that an unknown literal is allowed to be fed directly
into a type's input function to facilitate a direct coercion.
​
Writing this makes me wish for more precise terminology...is there
something already established here? "untyped" versus "unknown" makes sense
to me. untyped literals only exist within the confines of a single node
and can be passed through a type's input function to make them take on a
type. If the untyped reference passes through the node without having been
assigned an explicit type it is assigned the unknown type.

2. Unknown column references seem basically useless, because we will
> almost always encounter the "failure to find conversion" error, so why
> do we allow them?
>

At this point...backward compatibility?

I do get a warning in psql (9.3.6) from your original -bugs example

create table a(u) as select '1';

WARNING: "column "u" has type "unknown"​
DETAIL: Proceeding with relation creation anyway.

Related question: was there ever a time when the above failed instead of
just supplying a warning?

My git-fu is not super strong but the above warning was last edited by Tom
Lane back in 2003 (d8528630) though it was just a refactor - the warning
was already present. I suppose after 12 years the "why" doesn't matter so
much...

create table b(i int);
insert into b select u from a;
ERROR: failed to find conversion function from unknown to integer

Text appears to have a cast defined:

SELECT u::text FROM a;

> 3. If unknowns are just a placeholder, why do we return them to the
> client? What do we expect the client to do with it?

​We do?​ I suspect that it is effectively treated as if it were text by
client libraries.

​My gut reaction is if you feel strongly enough to add some additional
documentation or warnings/hints/details related to this topic they probably
would get put in; but disallowing "unknown" as first-class type is likely
to fail to pass a cost-benefit evaluation.

Distinguishing between "untyped" literals and "unknown type" literals seems
promising concept to aid in understanding the difference in the face of not
being able (or wanting) to actually change the behavior.

David J.