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

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

> On Thu, Apr 23, 2015 at 1:49 AM, David G. Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com <javascript:;>> wrote:
> > 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).
>
> You make some good points, but I disagree for two reasons:
>
> 1. This is a postgres issue, not a general SQL issue, so we can't
> simply say "SQL is weird" (like we can with a lot of the strange NULL
> semantics).

But it is ok to admit that we are weird when we are. Though yes, we are
being inefficient here even with the current behavior taken as desired.

2. Postgres has determined that the unknown column reference "b" in
> the query "SELECT a=b FROM (SELECT ''::text, ' ') x(a,b)" can and
> should be coerced to text.

So the error should be "operator does not exist: text = unknown"...instead
it tries and fails to cast the unknown to text.

Or allow for the coercion to proceed.

There would be fewer obvious errors, and simple cases that fail would begin
to work sensibly, but it still feels like implicit casting from text which
was recently largely removed from the system for cause. Albeit to the
disgruntlement of some and accusations of being draconian by others.

> So postgres has already made that decision.
> It's just that when it tries to coerce it, it fails. Even if you
> believe that literals and column references are not equivalent, I
> don't see any justification at all for this behavior -- postgres
> should not decide to coerce it in the first place if it's going to
> fail.
>
>
This is true - but obviously one solution is to not attempt it in the first
place.

David J.