Lists: | pgsql-bugs |
---|
From: | rep(dot)dot(dot)nop(at)gmail(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #7644: Missing implicit types of Result and failing type-conversion |
Date: | 2012-11-09 09:57:45 |
Message-ID: | E1TWlLN-0001Kk-0p@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: 7644
Logged by: Bernhard Reutner-Fischer
Email address: rep(dot)dot(dot)nop(at)gmail(dot)com
PostgreSQL version: 9.1.6
Operating system: debian
Description:
Hi,
According to
http://www.postgresql.org/docs/9.1/static/typeconv-union-case.html
i think that these 2 (separate?) issues are bugs(?)
1)
select distinct on (bug) bug
from (
select * from (select 'string one' AS bug from generate_series(0,1))
s1
UNION
select * from (select 'string two' bug from generate_series(0,0))
s2
) x
;
ERROR: could not determine which collation to use for string comparison
HINT: Use the COLLATE clause to set the collation explicitly.
When casting the return value of the union to :text _explicitly_ it works as
expected:
select distinct on (bug) bug
from (
select * from (select 'string one'::text AS bug from
generate_series(0,1)) s1
UNION
select * from (select 'string two' bug from generate_series(0,0))
s2
) x
;
bug
------------
string one
string two
(2 rows)
2)
select distinct on (bug) bug
from (
select * from (select 'string one' AS bug from generate_series(0,1))
s1
UNION
select * from (select 'string two' bug) s2
) x
;
ERROR: failed to find conversion function from unknown to text
In this case, however, using a "Result" fails to implicitly typecast to
:text, i.e. casting the return value of the first return value of the union
to :text explicitly does _not_ work:
select distinct on (bug) bug
from (
select * from (select 'string one'::text AS bug from
generate_series(0,1)) s1
UNION
select * from (select 'string two' bug ) s2
) x
;
ERROR: failed to find conversion function from unknown to text
What am i missing or doing wrong?
thanks and cheers,
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | rep(dot)dot(dot)nop(at)gmail(dot)com |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #7644: Missing implicit types of Result and failing type-conversion |
Date: | 2012-11-10 15:20:24 |
Message-ID: | 8891.1352560824@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
rep(dot)dot(dot)nop(at)gmail(dot)com writes:
> select distinct on (bug) bug
> from (
> select * from (select 'string one' AS bug from generate_series(0,1))
> s1
> UNION
> select * from (select 'string two' bug from generate_series(0,0))
> s2
> ) x
> ;
> ERROR: could not determine which collation to use for string comparison
> HINT: Use the COLLATE clause to set the collation explicitly.
> What am i missing or doing wrong?
Leaving out the cast. An undecorated string literal is not a constant
of type text --- it is a constant of unknown type, and the parser will
throw an error if the type can't be resolved from fairly-local context.
(The same applies to NULL, btw.)
regards, tom lane
From: | "Bernhard Reutner-Fischer" <rep(dot)dot(dot)nop(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG #7644: Missing implicit types of Result and failing type-conversion |
Date: | 2012-11-10 15:56:02 |
Message-ID: | 13aeb08ee8b.3848.2760.rep.dot.nop@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
On 10 November 2012 16:20:24 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> rep(dot)dot(dot)nop(at)gmail(dot)com writes:
> > select distinct on (bug) bug
> > from (
> > select * from (select 'string one' AS bug from generate_series(0,1))
> > s1
> > UNION
> > select * from (select 'string two' bug from generate_series(0,0))
> > s2
> > ) x
> > ;
> > ERROR: could not determine which collation to use for string comparison
> > HINT: Use the COLLATE clause to set the collation explicitly.
>
> > What am i missing or doing wrong?
>
> Leaving out the cast. An undecorated string literal is not a constant
> of type text --- it is a constant of unknown type, and the parser will
> throw an error if the type can't be resolved from fairly-local context.
> (The same applies to NULL, btw.)
Fair enough but what about the failure for the case with the Result
that fails to merge? There I do have a cast but it fails on the Result
as opposed to a genera e_series(0,0):
select distinct on (bug) bug
from (
select * from (select 'string one'::text AS bug from
generate_series(0,1)) s1
UNION
select * from (select 'string two' bug ) s2
) x
;
Sent with AquaMail for Android
http://www.aqua-mail.com
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Bernhard Reutner-Fischer" <rep(dot)dot(dot)nop(at)gmail(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #7644: Missing implicit types of Result and failing type-conversion |
Date: | 2012-11-10 15:58:51 |
Message-ID: | 9677.1352563131@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
"Bernhard Reutner-Fischer" <rep(dot)dot(dot)nop(at)gmail(dot)com> writes:
> Fair enough but what about the failure for the case with the Result
> that fails to merge? There I do have a cast
No you don't:
> select * from (select 'string two' bug ) s2
regards, tom lane
From: | Bernhard Reutner-Fischer <rep(dot)dot(dot)nop(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #7644: Missing implicit types of Result and failing type-conversion |
Date: | 2012-11-12 09:14:03 |
Message-ID: | CAC1BbcQoVod11J6rnR8GMyGBnxTa6irj6hUhcn2yGW=gJZ5BKA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
On 10 November 2012 16:58, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Bernhard Reutner-Fischer" <rep(dot)dot(dot)nop(at)gmail(dot)com> writes:
> > Fair enough but what about the failure for the case with the Result
> > that fails to merge? There I do have a cast
>
> No you don't:
>
> > select * from (select 'string two' bug ) s2
>
> regards, tom lane
>
I do not need the cast in the second select when using generate_series
either.
select distinct on (bug) bug
from (
select * from (select 'string one'::text bug from generate_series(0,1))
s1
UNION
select * from (select 'string two' bug) s2
-- select * from (select 'string two' bug from generate_series(0,0)) s2
) x
;
IMO this contradicts the "Type Resolution for UNION, CASE, and Related
Constructs" from the documentation.
4) Choose the first non-unknown input type which is a preferred type in
that category, if there is one.
Please explain why the two selects for 'string two' in the example above
behave different?
Many TIA && cheers,