Re: BUG #5974: UNION construct type cast gives poor error message

On 13/04/11 19:32, Tom Lane wrote:
> "Jeff Wu"<jwu@atlassian.com>  writes:
>> The UNION construct (as noted on this page:
>> http://www.postgresql.org/docs/9.0/static/typeconv-union-case.html) will
>> cast unknown types to TEXT, however, if you try to do three or more UNIONs
>> the order in which the UNIONs are executed will cause some columns to be
>> cast to TEXT prematurely.  The result is a type mismatch error.
> <snip>
>
> Or maybe we could find out that some other products
> do it like that despite what the spec says?
>
>             regards, tom lane

I happen to have a MS SQLServer 2008 instance at work as well as a MySQL
5.1 and an Oracle 10g. With the query:

SELECT 1,null,null
UNION
SELECT 2,3,null
UNION
SELECT 3,null,4

In MS SQLServer I get (NB: no column headings):

----------------------
----------------------
1 | <null> | <null>
2 | 3      | <null>
3 | <null> | 4


In MySQL I get:
--------------------
1 | NULL   | NULL
--------------------
1 | <null> | <null>
2 | 3      | <null>
3 | <null> | 4

In Oracle I get a delicious error message:

Error: ORA-00923: FROM keyword not found where expected

SQLState:  42000
ErrorCode: 923
Position: 19

Regards,

--
Mike Fowler
Registered Linux user: 379787