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

On Wednesday, April 22, 2015, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:

> On Wed, 2015-04-22 at 20:35 -0700, David G. Johnston wrote:
>
> > But the fact that column "b" has the data type "unknown" is only a
> > warning - not an error.
> >
> I get an error:
>
> postgres=# SELECT ' '::text = 'a';
> ?column?
> ----------
> f
> (1 row)
>
> postgres=# SELECT a=b FROM (SELECT ''::text, ' ') x(a,b);
> ERROR: failed to find conversion function from unknown to text
>
> So that means the column reference "b" is treated differently than the
> literal. Here I don't mean a reference to an actual column of a real
> table, just an identifier ("b") that parses as a columnref.
>
> Creating the table gives you a warning (not an error), but I think that
> was a poor example for me to choose, and not important to my point.

I get the point but the warning stems from converting from untyped to
unknown. Then you get the error looking for an implicit cast from
unknown. The error you had stated referred to the first situation, the
conversion of untyped to unknown.

>
> > 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(at)mail(dot)gmail(dot)com <javascript:;>
> >
> >
> I think that is a different problem. That's a runtime type conversion
> error (execution time), and I'm talking about something happening at
> parse analysis time.

Again, referring here to why your proposed error seems unlikely in face of
similar errors not currently providing sufficient context either. I don't
know enough to posit why this is the case.

> >
> > 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.
>
> Yes, I believe that's what's happening. When we use an unknown literal,
> it's acting more like a value constructor and will pass it to the type
> input function. When it's a columnref, even if unknown, it tries to cast
> it and fails.
>
> But that is very confusing. In the example at the top of this email, it
> seems like the second query should be equivalent to the first, or even
> that postgres should be able to rewrite the second into the first. But
> the second query fails where the first succeeds.

Agreed (sorta, I can understand your PoV) - but it is consistently
confusing...and quite obvious when you've changed from one to the other.

Is there something concrete to dig teeth into here?

>
> > At this point...backward compatibility?
>
> Backwards compatibility of what queries? I guess the ones that return
> unknowns to the client or create tables with unknown columns?

Yes, disallowing unknown and requiring everything to be untyped or error.

> > 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?
>
> Not that I recall.
>
>
>
> > ​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.
>
> I'm not proposing that we eliminate unknown. I just think columnrefs and
> literals should behave consistently. If we really don't want unknown
> columnrefs, it seems like we could at least throw a better error.

We do allow unknown column refs. We don't allow you to do much with them
though - given the lack of casts, implicit and otherwise. The error that
result from that situation are where the complaint lies. Since we cannot
disallow unknown column refs the question is can the resultant errors be
improved. I really don't see value in expending effort solely trying to
improve this limited situation. If the same effort also improves a wider
swath of the code base then great.

The only other option is to allow unknowns to be implicitly cast to text
and then fed into the input type just like an untyped literal would. But
those are not the same thing - no matter how similar your two mock queries
make them seem - and extrapolation from those two alone doesn't seem
justified. And his is crux of where your similarity falls apart. If you
can justify the above behavior then maybe...

>
> If we were starting from scratch, I'd also not return unknown to the
> client, but we have to worry about the backwards compatibility.
>
> > 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.
>
> Not sure I understand that proposal, can you elaborate?
>

Purely documentation explaining and naming the two different behaviors you
are seeing.

Reading and writing all this I'm convinced you have gotten the idea in your
mind an expectation of equivalency and consistency where there really is
little or none from an overall design perspective. And none insofar as
would merit trying to force the two example queries you provide to behave
identically. There are a number of things about SQL that one either simply
lives with or goes through mind contortions to understand the, possibly
imperfect, reasoning behind. This is one of those things: and while it's
been fun to do those contortions in the end I am only a little bit better
off than when I simply accepted the fact the unknown and untyped were
similar but different (even if I hadn't considered giving them different
names).

Literals and column references are different. If you put a literal into a
column you lose the ability to then treat it as a literal.

David J.