BUG #3259: Problem with automatic string cast

"Thomas" <thbley@gmail.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: