BUG #6673: Value out of range for type integer when adding WHERE clause

The following bug has been logged on the website:

Bug reference: 6673
Logged by: Michael Fork
Email address: mfork00(at)yahoo(dot)com
PostgreSQL version: 9.1.3
Operating system: Scientific Linux release 6.2 (Carbon)
Description:

The following query and plan executes as expected

++++++++++++++++++++++++++++++++++++++++++++++

springboard_v2=# explain SELECT (SELECT number FROM dblink('dbname=card',
'SELECT number FROM mint.card WHERE id = ' || card_id) AS card (number
TEXT)) AS card_number, mdn, created_at FROM (SELECT
trail.parsecardidfromreferencecode(reference_code)::integer AS card_id, mdn,
created_at FROM trail.event INNER JOIN trail.activation_event ON
activation_event.id = event.activation_id INNER JOIN trail.credit_event ON
event.id = credit_event.id WHERE created_at >= 'YESTERDAY' AND created_at <
'TODAY' and outcome = 'SUCCESSFUL' AND type = 'CREDIT' AND method = 'CARD')
AS event LEFT OUTER JOIN (SELECT id FROM dblink('dbname=card', 'SELECT id
FROM mint.card WHERE used_at >= ''YESTERDAY'' AND used_at < ''TODAY'' AND
state = ''REPLENISHED''') AS card (id integer)) AS card ON event.card_id =
card.id;

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Right Join (cost=339861.85..473494.95 rows=12660 width=37)
Merge Cond: (card.id =
((trail.parsecardidfromreferencecode(trail.event.reference_code))::integer))
-> Sort (cost=59.83..62.33 rows=1000 width=4)
Sort Key: card.id
-> Function Scan on dblink card (cost=0.00..10.00 rows=1000
width=4)
-> Sort (cost=339802.01..339808.34 rows=2532 width=37)
Sort Key:
((trail.parsecardidfromreferencecode(trail.event.reference_code))::integer)
-> Nested Loop (cost=0.00..339658.88 rows=2532 width=37)
-> Nested Loop (cost=0.00..312285.68 rows=2694 width=30)
-> Index Scan using idx_event_created_at on event
(cost=0.00..17550.96 rows=15644 width=34)
Index Cond: ((created_at >= '2012-06-01
00:00:00-04'::timestamp with time zone) AND (created_at < '2012-06-02
00:00:00-04'::timestamp with time zone))
Filter: ((outcome = 'SUCCESSFUL'::text) AND (type
= 'CREDIT'::text))
-> Index Scan using credit_event_pkey on credit_event
(cost=0.00..18.83 rows=1 width=4)
Index Cond: (id = trail.event.id)
Filter: (method = 'CARD'::text)
-> Index Scan using activation_event_pkey on
activation_event (cost=0.00..10.15 rows=1 width=15)
Index Cond: (id = trail.event.activation_id)
SubPlan 1
-> Function Scan on dblink card (cost=0.27..10.27 rows=1000
width=32)

++++++++++++++++++++++++++++++++++++++++++++++

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.

++++++++++++++++++++++++++++++++++++++++++++++

springboard_v2=# explain SELECT (SELECT number FROM dblink('dbname=card',
'SELECT number FROM mint.card WHERE id = ' || card_id) AS card (number
TEXT)) AS card_number, mdn, created_at FROM (SELECT
trail.parsecardidfromreferencecode(reference_code)::integer AS card_id, mdn,
created_at FROM trail.event INNER JOIN trail.activation_event ON
activation_event.id = event.activation_id INNER JOIN trail.credit_event ON
event.id = credit_event.id WHERE created_at >= 'YESTERDAY' AND created_at <
'TODAY' and outcome = 'SUCCESSFUL' AND type = 'CREDIT' AND method = 'CARD')
AS event LEFT OUTER JOIN (SELECT id FROM dblink('dbname=card', 'SELECT id
FROM mint.card WHERE used_at >= ''YESTERDAY'' AND used_at < ''TODAY'' AND
state = ''REPLENISHED''') AS card (id integer)) AS card ON event.card_id =
card.id WHERE card.id IS NULL;;

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=22.50..209968.44 rows=1266 width=37)
-> Nested Loop (cost=22.50..183286.35 rows=1347 width=30)
-> Hash Anti Join (cost=22.50..35918.99 rows=7822 width=34)
Hash Cond:
((trail.parsecardidfromreferencecode(trail.event.reference_code))::integer =
card.id)
-> Index Scan using idx_event_created_at on event
(cost=0.00..17550.96 rows=15644 width=34)
Index Cond: ((created_at >= '2012-06-01
00:00:00-04'::timestamp with time zone) AND (created_at < '2012-06-02
00:00:00-04'::timestamp with time zone))
Filter: ((outcome = 'SUCCESSFUL'::text) AND (type =
'CREDIT'::text))
-> Hash (cost=10.00..10.00 rows=1000 width=4)
-> Function Scan on dblink card (cost=0.00..10.00
rows=1000 width=4)
-> Index Scan using credit_event_pkey on credit_event
(cost=0.00..18.83 rows=1 width=4)
Index Cond: (id = trail.event.id)
Filter: (method = 'CARD'::text)
-> Index Scan using activation_event_pkey on activation_event
(cost=0.00..10.15 rows=1 width=15)
Index Cond: (id = trail.event.activation_id)
SubPlan 1
-> Function Scan on dblink card (cost=0.27..10.27 rows=1000
width=32)

ERROR: value "1338523218442" is out of range for type integer