Thread: 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@heroku.com PostgreSQL version: 9.1.3 Operating system: ubuntu 10.04 Description:=20=20=20=20=20=20=20=20 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=3D> with hello as (select 'hello' as name) , bye as (select 'bye' as name) select * from hello; name=20=20 ------- hello (1 row) deik3qfhu265n6=3D> 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=3D> 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=20=20 ------- hello bye (2 rows) deik3qfhu265n6=3D> \x Expanded display is on. deik3qfhu265n6=3D> 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@heroku.com> wrote: > The following bug has been logged on the website: > > Bug reference: =A0 =A0 =A06626 > Logged by: =A0 =A0 =A0 =A0 =A0Will Leinweber > Email address: =A0 =A0 =A0will@heroku.com > PostgreSQL version: 9.1.3 > Operating system: =A0 ubuntu 10.04 > Description: > > This was surprising because it worked without the UNION ALL. Casting to t= ext > fixes the problem. It seems that this should a column of type unknown. > > deik3qfhu265n6=3D> with hello as (select 'hello' as name) > , bye as (select 'bye' as name) > select * from hello; > =A0name > ------- > =A0hello > (1 row) > > deik3qfhu265n6=3D> with hello as (select 'hello' as name) > deik3qfhu265n6-> , bye as (select 'bye' as name) > deik3qfhu265n6-> select * from hello UNION ALL select * from bye; > ERROR: =A0failed to find conversion function from unknown to text > > > deik3qfhu265n6=3D> 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; > =A0name > ------- > =A0hello > =A0bye > (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); --=20 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=3D> with hello as (select 'hello' as name) >>> deik3qfhu265n6-> , bye as (select 'bye' as name) >>> deik3qfhu265n6-> select * from hello UNION ALL select * from bye; >>> ERROR: =A0failed to find conversion function from unknown to text > >> I think it should return a column of type text, just as if you'd done th= is: >> 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? =A0This 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. --=20 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=3D> with hello as (select 'hello' as name) >>>> deik3qfhu265n6-> , bye as (select 'bye' as name) >>>> deik3qfhu265n6-> select * from hello UNION ALL select * from bye; >>>> ERROR: =C2=A0failed to find conversion function from unknown to text >> >>> I think it should return a column of type text, just as if you'd done t= his: >>> 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? =C2=A0This would almost certainly break a few peop= les' >> 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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs