On Wed, May 06, 2009 at 01:26:05PM -0400, Tom Lane wrote:
> Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> writes:
> > Tom Lane wrote:
> Oh! What is happening is that to_number(1) is being reduced to constant
> NULL, whereupon it concludes that ad_tab_id=to_number(1) is constant
> NULL, ergo the EXISTS can never succeed, ergo the entire WHERE is
> constant false. I suppose the change I made here
> http://archives.postgresql.org/pgsql-committers/2009-04/msg00329.php
> to improve constant-join-qual handling is what is preventing the
> assertion failure, though I'm still not quite sure why (I'd better look
> closer to see if there is still some form of the bug lurking).
>
> Anyway I think this is an object lesson in why ignoring "WHEN OTHERS"
> errors is dangerous. to_number(integer) is defined as
>
> CREATE FUNCTION to_number(integer) RETURNS numeric
> LANGUAGE plpgsql IMMUTABLE
> AS $_$
> BEGIN
> RETURN to_number($1, 'S99999999999999D999999');
> EXCEPTION
> WHEN OTHERS THEN
> RETURN NULL;
> END;
> $_$;
>
> and what is actually happening inside there is
>
> regression=# select to_number(1, 'S99999999999999D999999');
> ERROR: function to_number(integer, unknown) does not exist
> LINE 1: select to_number(1, 'S99999999999999D999999');
> ^
> HINT: No function matches the given name and argument types. You might need to add explicit type casts.
>
> which is probably not what the author expects, but the WHEN OTHERS
> exception is hiding it.
This could be a side effect of my try to find a minimal testcase. I did try to give the minimum needed functions.
In the original app there are more overloaded to_number function which perhaps i did omit...
I will recheck this.. indepdently i agree that hiding all possible exceptions is quite a bad idea...
Regards,
Stefan
