Re: Failure to coerce unknown type to specific type

Kevin Grittner <kgrittn(at)ymail(dot)com> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> This is irrelevant, because such a construct fails the syntax rules
>> and thus we never get to the question of what type should be inferred,
>> at least not without going outside the spec. See my other reply.

> ... It is an extension to the spec
> to allow a NULL literal within a COALESCE clause at all. We would
> surely break a lot of working code to forbid it, though.

Actually, per my last reply, what's illegal per spec is for *all* the arms
to be NULL literals (so SQL Server is indeed enforcing the spec exactly).
As long as there's an arm with an identifiable type, the CASE's result
type can be determined.

> If we
> *are* going to allow it, it would be pretty confusing to have it
> behave differently that what I previously outlined (regarding the
> equivalent long form CASE clause).

AFAICT, we do treat them the same; can you provide an example where
we don't?

> To save an extra post -- I did modify the statements in SQL Fiddle
> to get to the point where the subquery returned a column without a
> type and a column with an int type in the dialect supported. I'm
> not sure how that's relevant to the issue about how they resolve
> that in the outer query, but I can post the form of the query used
> for each product if you think it is germane.

It may not be. I suspect what is really going on is that they're
resolving the sub-SELECT output column to TEXT (or local equivalent
idiom) and then being laxer than we are about coercing that type to
other types. It would be interesting to try variants of the

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

example to see what they do if the column has to be converted to two
mutually inconsistent types, assuming you can find candidate types
in each system. Another idea would be to try things like

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

and see exactly what error gets thrown.

> To restate it, this hardly seems like the most important issue to
> address; I just don't think the standard gives us much cover here.

I stand by my opinion that the cases that are controversial here
are all illegal per spec. We may well want to allow them on usability
grounds, but what the spec does *not* provide any cover for is claiming
that the spec requires some particular non-error interpretation.

regards, tom lane