BUG #6626: union all with values of type "unknown"

On Thu, May 3, 2012 at 9:01 PM, <will@heroku.com> wrote:

The following bug has been logged on the website:

Bug reference:      6626
Logged by:          Will Leinweber
Email address:      will@heroku.com
PostgreSQL version: 9.1.3
Operating system:   ubuntu 10.04
Description:

This was surprising because it worked without the UNION ALL. Casting to text
fixes the problem. It seems that this should a column of type unknown.

deik3qfhu265n6=> with hello as (select 'hello' as name)
, bye as (select 'bye' as name)
select * from hello;
 name
-------
 hello
(1 row)

deik3qfhu265n6=> with hello as (select 'hello' as name)
deik3qfhu265n6-> , bye as (select 'bye' as name)
deik3qfhu265n6-> select * from hello UNION ALL select * from bye;
ERROR:  failed to find conversion function from unknown to text

deik3qfhu265n6=> with hello as (select 'hello'::text as name)
deik3qfhu265n6-> , bye as (select 'bye'::text as name)
deik3qfhu265n6-> select * from hello UNION ALL select * from bye;
 name
-------
 hello
 bye
(2 rows)

I think it should return a column of type text, just as if you'd done this:

select v from (select 'hello' union all select 'bye') x(v);

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

Robert Haas <robertmhaas@gmail.com> writes:

deik3qfhu265n6=> with hello as (select 'hello' as name)
deik3qfhu265n6-> , bye as (select 'bye' as name)
deik3qfhu265n6-> select * from hello UNION ALL select * from bye;
ERROR: �failed to find conversion function from unknown to text

I think it should return a column of type text, just as if you'd done this:
select v from (select 'hello' union all select 'bye') x(v);

I don't think it's a great idea to make CTEs handle this differently
from other places where the same issue arises (from memory, views and
INSERT/SELECT have problems with unknown literals, and there are
probably other places I'm forgetting).

Should we institute a uniform policy of forcing unknown sub-select
outputs to text type? This would almost certainly break a few peoples'
queries, but the reduction of surprise might be worth it for most.

regards, tom lane

On Tue, May 22, 2012 at 3:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

deik3qfhu265n6=> with hello as (select 'hello' as name)
deik3qfhu265n6-> , bye as (select 'bye' as name)
deik3qfhu265n6-> select * from hello UNION ALL select * from bye;
ERROR:  failed to find conversion function from unknown to text

I think it should return a column of type text, just as if you'd done this:
select v from (select 'hello' union all select 'bye') x(v);

I don't think it's a great idea to make CTEs handle this differently
from other places where the same issue arises (from memory, views and
INSERT/SELECT have problems with unknown literals, and there are
probably other places I'm forgetting).

Should we institute a uniform policy of forcing unknown sub-select
outputs to text type?  This would almost certainly break a few peoples'
queries, but the reduction of surprise might be worth it for most.

I think if we can't do real type inference, forcing unknown to text is
probably the least of evils.

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

2012/5/22 Robert Haas <robertmhaas@gmail.com>:

On Tue, May 22, 2012 at 3:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

deik3qfhu265n6=> with hello as (select 'hello' as name)
deik3qfhu265n6-> , bye as (select 'bye' as name)
deik3qfhu265n6-> select * from hello UNION ALL select * from bye;
ERROR:  failed to find conversion function from unknown to text

I think it should return a column of type text, just as if you'd done this:
select v from (select 'hello' union all select 'bye') x(v);

I don't think it's a great idea to make CTEs handle this differently
from other places where the same issue arises (from memory, views and
INSERT/SELECT have problems with unknown literals, and there are
probably other places I'm forgetting).

Should we institute a uniform policy of forcing unknown sub-select
outputs to text type?  This would almost certainly break a few peoples'
queries, but the reduction of surprise might be worth it for most.

I think if we can't do real type inference, forcing unknown to text is
probably the least of evils.

can we implement late cast? Cast unknown to text only when exception
is raised, resp. before? This issue is relative unfriendly for
beginners

Regards

Pavel