Re: BUG #3259: Problem with automatic string cast

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
>
>