Thread: 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

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