BUG #3259: Problem with automatic string cast

The following bug has been logged online:

Bug reference: 3259
Logged by: Thomas
Email address: thbley(at)gmail(dot)com
PostgreSQL version: 8.2.1
Operating system: WinXP SP2
Description: Problem with automatic string cast
Details:

I have this SQL:

select * from (select 'years' as recurrence) p where recurrence = 'years'

it gives:

ERROR: failed to find conversion function from "unknown" to text SQL state:
XX000

but this one is ok:

select * from (select 'years' as recurrence union select 'years' as
recurrence) p where recurrence='years'

it gives:

recurrence text
'years'

this is a default installation with the binaries from postgresql.org with no
changes in the configs:
PostgreSQL 8.2.1 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
(mingw-special)

"Thomas" <thbley(at)gmail(dot)com> writes:
> select * from (select 'years' as recurrence) p where recurrence = 'years'
> it gives:
> ERROR: failed to find conversion function from "unknown" to text

Try casting the unknown value to some specific type, eg

regression=# select * from (select 'years'::text as recurrence) p where recurrence = 'years';
recurrence
------------
years
(1 row)

Do you have a less artificial example where not resolving the
subselect's output type is a problem? We could change it to
force the type to text sooner, but I'm afraid that that would
break other people's usages.

regards, tom lane

Hello,

MySQL 4.1 and Oracle 10g do the cast automatically, so I think the
behavior should be changed if the SQL standards require it in this case.
In general, it is confusing for programmers that 'years' has type
unknown, 0 is integer, 0.5 is numeric, true is boolean, etc.
Also I can imagine that it makes sense to perform an automatic cast for
the "=" operator.

My query is normally a bit bigger:
A birthday field is mapped to a iCalendar dataset in a subquery. In most
applications this subquery would be declared as a view.

select
id,subject,begin,ending,duration,allday,recurrence,repeatinterval,repeatcount,repeatuntil,repeatexcludes,until,folder,created,lastmodified,createdby,lastmodifiedby
from (select b.*,('Birthday: '||firstname||' '||lastname||' '||company)
as subject,86399 as duration, 0 as repeatuntil,birthday as
begin,(birthday+86399) as ending,1 as allday,cast('years' as char) as
recurrence, 0 as until,1 as repeatinterval,0 as repeatcount,'' as
repeatexcludes,0 as repeatbegin, 365 as repeatend from simple_contacts
b where birthday!=0 ) p where ...

bye
Thomas

Tom Lane wrote:
> "Thomas" <thbley(at)gmail(dot)com> writes:
>
>> select * from (select 'years' as recurrence) p where recurrence = 'years'
>> it gives:
>> ERROR: failed to find conversion function from "unknown" to text
>>
>
> Try casting the unknown value to some specific type, eg
>
> regression=# select * from (select 'years'::text as recurrence) p where recurrence = 'years';
> recurrence
> ------------
> years
> (1 row)
>
> Do you have a less artificial example where not resolving the
> subselect's output type is a problem? We could change it to
> force the type to text sooner, but I'm afraid that that would
> break other people's usages.
>
> regards, tom lane
>
>