mfork00@yahoo.com writes:
> However, the following query, which is just the above query with 'WHERE
> card.id IS NULL' tacked on to the end fails with an integer out of range.
> Important to know is that parsecardidfromreferencecode will return a valid
> integer ONLY for the rows matching the WHERE clause. However, I cannot see
> how adding the WHERE clause causes that error.
It looks like the planner has chosen to do the joins in a different
order here; the join to activation_event is now done last, so that the
value of the function might be needed for rows it was not needed for
otherwise. This could have occurred in the other version of the query
too; it's all about relative row counts.
Why don't you cast to bigint rather than integer, seeing that the
function is evidently quite capable of returning a bigint value?
If you really must use a join condition involving a function that can
fail for some inputs, possibly the best solution is to mark the function
volatile so that the planner will avoid rearranging stuff that uses it.
This might do some considerable damage to the overall quality of the
plan though.
regards, tom lane