Lists: | pgsql-bugs |
---|
From: | will(at)heroku(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #6626: union all with values of type "unknown" |
Date: | 2012-05-04 01:01:24 |
Message-ID: | E1SQ6tg-0003qf-5P@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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
From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | will(at)heroku(dot)com |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #6626: union all with values of type "unknown" |
Date: | 2012-05-22 19:41:00 |
Message-ID: | CA+TgmoZgbsVsjDNxGbp9w1AmPMWz0G72ccCcckPdMM6251uuCA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | will(at)heroku(dot)com, pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #6626: union all with values of type "unknown" |
Date: | 2012-05-22 19:55:49 |
Message-ID: | 7003.1337716549@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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
From: | Robert Haas <robertmhaas(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | will(at)heroku(dot)com, pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #6626: union all with values of type "unknown" |
Date: | 2012-05-22 19:57:10 |
Message-ID: | CA+TgmoYJKcoKUEHPyYGK0NevuEjx2eVnNhFQmU6k07tgaYp8kw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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
From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Robert Haas <robertmhaas(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, will(at)heroku(dot)com, pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #6626: union all with values of type "unknown" |
Date: | 2012-05-23 03:24:40 |
Message-ID: | CAFj8pRAYTgsN9pEA+a=8Pgny6rsi=QhN01GpyW-jA49oV1r_fw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
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