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
