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