Обсуждение: BUG #7644: Missing implicit types of Result and failing type-conversion
BUG #7644: Missing implicit types of Result and failing type-conversion
От
rep.dot.nop@gmail.com
Дата:
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,
rep.dot.nop@gmail.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
Re: BUG #7644: Missing implicit types of Result and failing type-conversion
От
"Bernhard Reutner-Fischer"
Дата:
On 10 November 2012 16:20:24 Tom Lane <tgl@sss.pgh.pa.us> wrote: > rep.dot.nop@gmail.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