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@yahoo.com
PostgreSQL version: 9.1.3
Operating system:   Scientific Linux release 6.2 (Carbon)
Description:=20=20=20=20=20=20=20=20

The following query and plan executes as expected

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

springboard_v2=3D# explain SELECT (SELECT number FROM dblink('dbname=3Dcard=
',
'SELECT number FROM mint.card WHERE id =3D ' || 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 =3D event.activation_id INNER JOIN trail.credit_event ON
event.id =3D credit_event.id WHERE created_at >=3D 'YESTERDAY' AND created_=
at <
'TODAY' and outcome =3D 'SUCCESSFUL' AND type =3D 'CREDIT' AND method =3D '=
CARD')
AS event LEFT OUTER JOIN (SELECT id FROM dblink('dbname=3Dcard', 'SELECT id
FROM mint.card WHERE used_at >=3D ''YESTERDAY'' AND used_at < ''TODAY'' AND
state =3D ''REPLENISHED''') AS card (id integer)) AS card ON event.card_id =
=3D
card.id;
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20
       QUERY PLAN
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
----------------------------
 Merge Right Join  (cost=3D339861.85..473494.95 rows=3D12660 width=3D37)
   Merge Cond: (card.id =3D
((trail.parsecardidfromreferencecode(trail.event.reference_code))::integer))
   ->  Sort  (cost=3D59.83..62.33 rows=3D1000 width=3D4)
         Sort Key: card.id
         ->  Function Scan on dblink card  (cost=3D0.00..10.00 rows=3D1000
width=3D4)
   ->  Sort  (cost=3D339802.01..339808.34 rows=3D2532 width=3D37)
         Sort Key:
((trail.parsecardidfromreferencecode(trail.event.reference_code))::integer)
         ->  Nested Loop  (cost=3D0.00..339658.88 rows=3D2532 width=3D37)
               ->  Nested Loop  (cost=3D0.00..312285.68 rows=3D2694 width=
=3D30)
                     ->  Index Scan using idx_event_created_at on event=20
(cost=3D0.00..17550.96 rows=3D15644 width=3D34)
                           Index Cond: ((created_at >=3D '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 =3D 'SUCCESSFUL'::text) AND (t=
ype
=3D 'CREDIT'::text))
                     ->  Index Scan using credit_event_pkey on credit_event=
=20
(cost=3D0.00..18.83 rows=3D1 width=3D4)
                           Index Cond: (id =3D trail.event.id)
                           Filter: (method =3D 'CARD'::text)
               ->  Index Scan using activation_event_pkey on
activation_event  (cost=3D0.00..10.15 rows=3D1 width=3D15)
                     Index Cond: (id =3D trail.event.activation_id)
   SubPlan 1
     ->  Function Scan on dblink card  (cost=3D0.27..10.27 rows=3D1000
width=3D32)

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

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.=
=20
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=3D# explain SELECT (SELECT number FROM dblink('dbname=3Dcard=
',
'SELECT number FROM mint.card WHERE id =3D ' || 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 =3D event.activation_id INNER JOIN trail.credit_event ON
event.id =3D credit_event.id WHERE created_at >=3D 'YESTERDAY' AND created_=
at <
'TODAY' and outcome =3D 'SUCCESSFUL' AND type =3D 'CREDIT' AND method =3D '=
CARD')
AS event LEFT OUTER JOIN (SELECT id FROM dblink('dbname=3Dcard', 'SELECT id
FROM mint.card WHERE used_at >=3D ''YESTERDAY'' AND used_at < ''TODAY'' AND
state =3D ''REPLENISHED''') AS card (id integer)) AS card ON event.card_id =
=3D
card.id WHERE card.id IS NULL;;
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20
    QUERY PLAN
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
----------------------
 Nested Loop  (cost=3D22.50..209968.44 rows=3D1266 width=3D37)
   ->  Nested Loop  (cost=3D22.50..183286.35 rows=3D1347 width=3D30)
         ->  Hash Anti Join  (cost=3D22.50..35918.99 rows=3D7822 width=3D34)
               Hash Cond:
((trail.parsecardidfromreferencecode(trail.event.reference_code))::integer =
=3D
card.id)
               ->  Index Scan using idx_event_created_at on event=20
(cost=3D0.00..17550.96 rows=3D15644 width=3D34)
                     Index Cond: ((created_at >=3D '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 =3D 'SUCCESSFUL'::text) AND (type =
=3D
'CREDIT'::text))
               ->  Hash  (cost=3D10.00..10.00 rows=3D1000 width=3D4)
                     ->  Function Scan on dblink card  (cost=3D0.00..10.00
rows=3D1000 width=3D4)
         ->  Index Scan using credit_event_pkey on credit_event=20
(cost=3D0.00..18.83 rows=3D1 width=3D4)
               Index Cond: (id =3D trail.event.id)
               Filter: (method =3D 'CARD'::text)
   ->  Index Scan using activation_event_pkey on activation_event=20
(cost=3D0.00..10.15 rows=3D1 width=3D15)
         Index Cond: (id =3D trail.event.activation_id)
   SubPlan 1
     ->  Function Scan on dblink card  (cost=3D0.27..10.27 rows=3D1000
width=3D32)

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