Re: Failure to coerce unknown type to specific type

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Jeff Davis <pgsql(at)j-davis(dot)com> writes:
>> On Fri, 2015-05-01 at 10:08 -0700, Tom Lane wrote:
>>> 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.
>>
>> 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. Oracle 11g R2 (with a one-row table
to avoid the "FROM keyword not found where expected" error) gives
this rather cryptic message:

ORA-00933: SQL command not properly ended

If I pull out the subquery and run it by itself Oracle gives this:

Invalid SQL type: sqlKind = UNINITIALIZED

> 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? I'm kinda curious
whether the products that manage to handle the above give a sane
error when it becomes ambiguous, or whether they fail in confusing
ways. It's not like I feel that we need to support a statement
just because other products do, but when I respond to complaints
from users trying to migrate to PostgreSQL, it is useful to have
examples to demonstrate the down side of what other products are
doing.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company