Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> writes:
> Tom Lane wrote:
>> I was trying it on HEAD ... but I don't see any post-beta1 changes
>> in the cvs log that look like they might have fixed this ...
> confirmed - it does not crash on -HEAD for me as well but the plan
> generated by EXPLAIN looks kinda funny:
> QUERY PLAN
> ------------------------------------------
> Result (cost=0.00..0.01 rows=1 width=0)
> One-Time Filter: false
> (2 rows)
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.
regards, tom lane