Re: Failure to coerce unknown type to specific type

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.

Yeah, our posts have been crossing a bit. The point about <value
expression> not allowing a NULL literal is valid. I yield on that
regarding COALESCE within the spec. 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. 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).

The <result> from a long form of the CASE clause explicitly does
explicitly allow an untyped NULL literal, and forcing it to text is
wrong per section 9.3.

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.

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.
What we do for the CASE clause is clearly wrong per spec, and if we
allow a bare NULL in a COALESCE clause it would be crazy not to
have the behavior match CASE. But it is clearly good form to
always cast a NULL literal to some type, and that is a workaround
which should not be too painful for most people. We shouldn't rush
to do anything big here, but we should recognize where we stand.

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