Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>> That means that all three of the databases you tested have
>> extensions to the standard similar to what is being contemplated
>> for PostgreSQL.
>
> Uh, no, it proves they all extend the standard to allow NULL to be
> written without an immediate cast. Mike's test really fails to
> prove anything about the point at hand, which is what data type is
> being imputed to the inner UNION.
The query run was:
SELECT 1,null,null
UNION
SELECT 2,3,null
UNION
SELECT 3,null,4
It's a bit of a stretch to think that the columns returned from the
final union weren't integer, or that integer is the default type of
the union of two nulls. It's anyone's guess at this point whether
the third column was unknown during the leftmost union and the type
set in the next union, or the set of columns involved in the union
were all evaluated as a group. If they don't have other literals of
unknown type it may be hard to discern the implementation details,
but either I've missed something or we're considering similar user
visible behavior.
> I don't know those other DBMSes well enough to suggest a test that
> would be definitive on the point, though. We'd need something
> where the choice of datatype is material to the final visible
> result, and at least in PG that requires some knowledge of
> not-very-standard behaviors.
If the implementation details for the other databases are that hard
to discern, how much do we care *how* they do it? It seems to me
that the important point here is that they don't throw an error on
that query and we do.
What am I missing?
-Kevin