Обсуждение: BUG #11266: failed to find conversion function from unknown to bigint

The following bug has been logged on the website:

Bug reference:      11266
Logged by:          zoulx1982
Email address:      zoulx1982@163.com
PostgreSQL version: 9.4beta2
Operating system:   Win7
Description:

when i migrate my application from oracle to postgrs,
i encounter a query raise error "failed to find conversion function from
unknown to bigint"
the original query is very complicated, so i summarize a simple testcase:

create table t1(a varchar(10));
create table t2(x varchar(10), y bigint);
select s.*
from
(select a, null b from t1
group by a
) s
union all
select x,y from t2;--ERROR

postgres=# select version();
                            version
----------------------------------------------------------------
 PostgreSQL 9.5devel, compiled by Visual C++ build 1600, 32-bit
(1 row)

and when i remove the "group" clause, the query works well
or if i don't use sub-query, it can also work well, i.e.
select a, null b from t1 group by a union all select x,y from t2;
and
select s.* from (select a, null b from t1) s union all select x,y from t2;
is all OK.

is there parameters to avoid the issue without changing original sql,
or doe's somebody can fix the issue later ?

thanks.

zoulx1982@163.com writes:
> select s.*
> from
> (select a, null b from t1
> group by a
> ) s
> union all
> select x,y from t2;--ERROR

> is there parameters to avoid the issue without changing original sql,
> or doe's somebody can fix the issue later ?

No, and no.  You need to cast the NULL literal to some specific datatype
(in this example, you want the type of t2.y).

A bare NULL like this is illegal per SQL standard; you're always supposed
to cast it, or else write it in a place like an INSERT list where the
null's type can be inferred from immediate context.  Most real DBMSes
allow you to violate that rule to some extent, but you should not be
surprised that the exact places where you can get away with it vary from
product to product.  Postgres is not exactly like Oracle in this respect,
and we're unlikely to try to make it so.

            regards, tom lane