PostgreSQL executing my function too many times during query - Mailing list pgsql-general

I have several records in my database which have encrypted fields.  I
want to find all the ones that match a certain format but do NOT match
another.

My problem is that the 'cc_encrypt' function is being executed for every
matching row in the table instead of just once.  The function was
defined as STABLE and I tried IMMUTABLE as well.  That doesn't seem to
be helping.

This format causes the function to execute too many times:

   SELECT COUNT(*) AS result
   FROM credit_card
   WHERE card_number_enc = cc_encrypt('4111-1111-1111-1111', 'pwd')
   AND card_number_enc != cc_encrypt('4111111111111111', 'pwd');

So, the second cc_encrypt is being executed for every row matching the
first cc_encrypt condition.  My expectation is that both functions would
be executed ONCE the result would be used in the query like this:

   SELECT COUNT(*) AS result
   FROM credit_card
   WHERE card_number_enc = <RESULT>
   AND card_number_enc != <RESULT>;

To fix the "bug", I can rewrite my query like this and the functions
will only be executed once each as expected:

   SELECT COUNT(*) AS result
   FROM credit_card
   WHERE card_number_enc = cc_encrypt('4111-1111-1111-1111', 'pwd')
   AND card_number_enc NOT IN (
     SELECT cc_encrypt('4111111111111111', 'pwd')
   );

I don't understand what's happening here.  Any help?  Maybe the EXPLAIN
tells something?

# EXPLAIN SELECT COUNT(*) AS result
# FROM credit_card
# WHERE card_number_enc = cc_encrypt('4111-1111-1111-1111', 'pwd')
# AND card_number_enc != cc_encrypt('4111111111111111', 'pwd');
                                                     QUERY PLAN

------------------------------------------------------------------------------------------------------------------
  Aggregate  (cost=196.96..196.97 rows=1 width=0)
    ->  Bitmap Heap Scan on credit_card  (cost=4.87..196.76 rows=79 width=0)
          Recheck Cond: (card_number_enc =
credit_card_encrypt('4111-1111-1111-1111'::text, 'password'::text))
          Filter: (card_number_enc <>
credit_card_encrypt('4111111111111111'::text, 'password'::text))
          ->  Bitmap Index Scan on credit_card_idx_card_number_enc
(cost=0.00..4.85 rows=79 width=0)
                Index Cond: (card_number_enc =
credit_card_encrypt('4111-1111-1111-1111'::text, 'password'::text))
(6 rows)

Oddly, when I use 'EXPLAIN', I see my debug logging "RAISE NOTICE"
statements showing that the function was only executed once each.  When
I don't use EXPLAIN, it's back to showing that the second function was
executed for each matching record of the first.

# SELECT version();
                                                  version

----------------------------------------------------------------------------------------------------------
  PostgreSQL 8.2.4 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC)
4.1.1 20070105 (Red Hat 4.1.1-52)
(1 row)

-- Dante