Re: Failure to coerce unknown type to specific type

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Fri, May 1, 2015 at 1:50 AM, Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
>> => select u=t from (select 'x' as u, 'y'::text as t) s;
>> ERROR: failed to find conversion function from unknown to text
>>
>> That's an elog (not an ereport, just plain elog) for what is a
>> high-level query compilation error of a fairly sane-looking query, which
>> seems wrong.

> I agree. I'm not sure what the right fix is, but that query should
> probably work, and if it must fail, it certainly shouldn't elog().

What really ought to happen here, IMO, is that the output columns of the
sub-select ought to get resolved to non-unknown types while we are doing
parse analysis of the sub-select.

I believe the core reason why we haven't done this ages ago is that
currently, the "right thing" will happen if you do this:

insert into t (a, b) select x, 'foo' from s;

namely that after the sub-select is parsed, the INSERT will reach down and
coerce the unknown literal to the datatype of b. If we force the output
of the sub-select to text earlier, that will stop working (or at least, it
will only work in cases where there's an assignment cast from text to b's
type). That's a horrid kluge, but if memory serves it's required to
handle some case the SQL spec expects to work.

It's conceivable that we could preserve the desirable aspects of
INSERT/SELECT while eliminating "unknown" outputs from sub-selects,
if INSERT were to pass down some context saying "here are the target
column types I want", and then the resolution rule in SELECT target list
processing would be "if an output column is UNKNOWN, coerce it to the
target type provided by context if any, otherwise coerce to text".
Not sure how much overhead this would add ... probably not very much,
since INSERT will have to identify the target column types sooner or
later.

regards, tom lane