On Wed, May 06, 2009 at 01:26:05PM -0400, Tom Lane wrote: > Stefan Kaltenbrunner <ste...@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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
