Re: Failure to coerce unknown type to specific type

Kevin Grittner <kgrittn@ymail.com> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Jeff Davis <pgsql@j-davis.com> writes:
>>> So, what would happen for something like:
>>> select u+i from (select '1' as u, '2'::int as i) s;

>> I don't think there's any useful alternative to failing on this
>> type of case.  You can't realistically postpone resolution of the
>> subquery output types long enough for outer-level expression
>> resolution to provide context.

> According to SQL Fiddle: MySQL, SQL Lite, and MS SQL Server all
> come up with 3 as the answer.

Really?  The :: syntax is a Postgres-ism, so you surely didn't test
this query on those.

>> Even if you could, the behavior wouldn't be very well defined,
>> because (as you note) there might be more than one such
>> expression leading to contradictory results.

> Do you have a simple example of what you mean?

Jeff already pointed out the issue, but consider

select u+i from (select '1' as u, '2'::int as i) s where u<'foo'::text;

At the very least such a query would behave differently depending on
whether we process the outer query's WHERE clause before or after its
SELECT output list.

            regards, tom lane