Re: [BUGS] BUG #6626: union all with values of type "unknown"
2012/5/22 Robert Haas : > On Tue, May 22, 2012 at 3:55 PM, Tom Lane wrote: >> Robert Haas 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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6626: union all with values of type "unknown"
On Tue, May 22, 2012 at 3:55 PM, Tom Lane wrote: > Robert Haas 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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6626: union all with values of type "unknown"
Robert Haas 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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6626: union all with values of type "unknown"
On Thu, May 3, 2012 at 9:01 PM, wrote: > The following bug has been logged on the website: > > Bug reference: 6626 > Logged by: Will Leinweber > Email address: w...@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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] 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: w...@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) 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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
