Re: BUG #6202: type of union column selection bug

On Fri, Sep 9, 2011 at 9:41 PM, Roman <zotov(at)oe-it(dot)ru> wrote:
> Query
> select null union select 4
> is Ok but
> select null union select null union select 4
> is Fail
>
> i think it`s a bug because you choose type in second query twice
> null and null -> text
> text and int -> fail

Yeah, this is lousy. Unfortunately, it's also not easy to fix, so I
think we're stuck with it for now. You can fix it by casting the NULL
constants to int, e.g.:

SELECT null::int4 UNION SELECT null::int4 UNION SELECT 4

> and i think what check type category is not true, because i can`t create
> implicit cast to resolve some problems, like
> select 'a' union select 4
> if i have implicit cast int->text i think it must work!!
>
> Sorry second part is not bug it`s a feature but it`s my problem too

Sadly, I don't think that'll work - the undecorated literal is going
to be treated as "unknown", not text. Implicitly casting int to text
is maybe not a good idea; we removed those casts on purpose in
PostgreSQL 8.3. But if you want to restore (much of) the pre-8.3
behavior, you might want to have a look at this blog post:

http://petereisentraut.blogspot.com/2008/03/readding-implicit-casts-in-postgresql.html

But in this case I think you're likely to still need a cast in there somewhere.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company