The following bug has been logged on the website:
Bug reference: 7644
Logged by: Bernhard Reutner-Fischer
Email address: rep.dot.nop@gmail.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,