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

The following bug has been logged on the website:

Bug reference: 6626
Logged by: Will Leinweber
Email address: will(at)heroku(dot)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)

deik3qfhu265n6=> \x
Expanded display is on.
deik3qfhu265n6=> select version();
-[ RECORD 1
]-----------------------------------------------------------------------------------------------------------
version | PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by
gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5.1) 4.4.3, 64-bit

On Thu, May 3, 2012 at 9:01 PM, <will(at)heroku(dot)com> wrote:
> The following bug has been logged on the website:
>
> Bug reference:      6626
> Logged by:          Will Leinweber
> Email address:      will(at)heroku(dot)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(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)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(at)gmail(dot)com>:
> On Tue, May 22, 2012 at 3:55 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Robert Haas <robertmhaas(at)gmail(dot)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
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs