BUG #7604: adding criteria to a query against a view in 9.2 expands the results instead of constraining them - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      7604
Logged by:          Bill MacArthur
Email address:      webmaster@dhs-club.com
PostgreSQL version: 9.2.1
Operating system:   CentOS 5.8
Description:        =


vip_declines_mailers_base is a VIEW that itself uses another VIEW alongside
several other joined tables. nop_seed is a 1 column table that contains 1
date as a reference. members_cancel_pending is a VIEW.

CREATE OR REPLACE VIEW vip_decline_mailers_base AS =

 SELECT m.id, m.alias, m.firstname, m.lastname, m.emailaddress, =

        CASE
            WHEN s.void =3D false THEN s.end_date
            ELSE (s.end_date - '1 mon'::interval)::date
        END AS paid_thru, =

    mop.payment_method, m.mail_option_lvl, now()::date AS "current_date"
   FROM nop_seed, =

    subscriptions s
   JOIN mop ON mop.id =3D s.member_id
   JOIN members_cancel_pending m ON m.id =3D s.member_id AND
m.membertype::text =3D 'v'::text
   JOIN subscription_types st ON s.subscription_type =3D st.subscription_ty=
pe
  WHERE (s.end_date < nop_seed.paid_thru OR s.void =3D true) AND
st.sub_class::text =3D 'VM'::text;


Then executing a query against vip_declines_mailers_base with no
constraining arguments, the complete result set counts as this:
network=3D# select count(*) from vip_decline_mailers_base;
 count
-------
   358

vip_declines_mailers_base is another VIEW that merely adds some criteria to
limit the result set of the 'base' VIEW. In versions 9.0 and back it did
just that. After upgrading to 9.2, the criteria actually expand the result
set.
CREATE OR REPLACE VIEW vip_mailer_unpaid_current AS =

 SELECT vip_decline_mailers_base.id, vip_decline_mailers_base.alias, =

    vip_decline_mailers_base.firstname, vip_decline_mailers_base.lastname, =

    vip_decline_mailers_base.emailaddress,
vip_decline_mailers_base.paid_thru, =

    vip_decline_mailers_base.payment_method, =

    vip_decline_mailers_base.mail_option_lvl, =

    vip_decline_mailers_base."current_date", =

    current_month_text(now()) AS current_month_text
   FROM vip_decline_mailers_base
  WHERE vip_decline_mailers_base.mail_option_lvl > 0 AND
vip_decline_mailers_base.paid_thru >=3D first_of_another_month((now()::date=
 -
'1 mon'::interval)::date) AND vip_decline_mailers_base.paid_thru <=3D
(first_of_month() - 1);

network=3D# select count(*) from vip_mailer_unpaid_current;
 count
-------
   391

How can this be? What's worse, is that adding the criteria somehow mangles
the inner workings of the 'base' VIEW and causes it to return results where
the membertype does not even match the join criteria which should be 'v'
only.

I could create a self contained test case, but the number of tables and
scrubbing the data could be tedious. Perhaps there is enough here to help
pinpoint a trouble spot. I should restate, also, that these VIEWS have been
working fine with 9.0 and earlier versions.

FWIW, here are the EXPLAINs on the two queries.

network=3D# explain select count(*) from vip_decline_mailers_base;
                                                                            =

                                                     QUERY PLAN

---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-------------
--------------------------------
 Aggregate  (cost=3D82439.33..82439.34 rows=3D1 width=3D0)
   ->  Hash Right Join  (cost=3D78647.01..82439.21 rows=3D47 width=3D0)
         Hash Cond: (c.id =3D m.id)
         Filter: ((CASE WHEN (c.id IS NULL) THEN m.membertype WHEN
((c.status =3D 'tr'::text) OR (c.status =3D 'p'::text) OR (c.status =3D
'cd'::text) OR (c.status =3D 'sp'::text) OR (c.status =3D 'sa'::text)) THEN
m.membertype ELSE 'c'::character
varying END)::text =3D 'v'::text)
         ->  Seq Scan on cancellations c  (cost=3D0.00..3325.35 rows=3D1197=
35
width=3D6)
         ->  Hash  (cost=3D78529.91..78529.91 rows=3D9368 width=3D6)
               ->  Hash Join  (cost=3D6128.57..78529.91 rows=3D9368 width=
=3D6)
                     Hash Cond: (m.id =3D mop.id)
                     ->  Seq Scan on members m  (cost=3D0.00..66417.39
rows=3D1570739 width=3D6)
                     ->  Hash  (cost=3D6011.47..6011.47 rows=3D9368 width=
=3D8)
                           ->  Hash Join  (cost=3D3129.39..6011.47 rows=3D9=
368
width=3D8)
                                 Hash Cond: (mop.id =3D s.member_id)
                                 ->  Seq Scan on mop  (cost=3D0.00..2158.67
rows=3D71967 width=3D4)
                                 ->  Hash  (cost=3D3012.28..3012.28 rows=3D=
9369
width=3D4)
                                       ->  Nested Loop  (cost=3D2.67..3012.=
28
rows=3D9369 width=3D4)
                                             Join Filter: ((s.end_date <
nop_seed.paid_thru) OR s.void)
                                             ->  Seq Scan on nop_seed =

(cost=3D0.00..1.01 rows=3D1 width=3D4)
                                             ->  Hash Join =

(cost=3D2.67..2723.56 rows=3D23017 width=3D9)
                                                   Hash Cond:
(s.subscription_type =3D st.subscription_type)
                                                   ->  Seq Scan on
subscriptions s  (cost=3D0.00..2188.61 rows=3D80561 width=3D11)
                                                   ->  Hash =

(cost=3D2.52..2.52 rows=3D12 width=3D2)
                                                         ->  Seq Scan on
subscription_types st  (cost=3D0.00..2.52 rows=3D12 width=3D2)
                                                               Filter:
((sub_class)::text =3D 'VM'::text)
(23 rows)


network=3D# explain select count(*) from vip_mailer_unpaid_current;
                                                                            =

                                                                            =

             QUERY PLAN

---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-------------
---------------------------------------------------------------------------=
-------------------------------
 Aggregate  (cost=3D25954.57..25954.58 rows=3D1 width=3D0)
   ->  Nested Loop  (cost=3D0.00..25954.57 rows=3D1 width=3D0)
         ->  Nested Loop Left Join  (cost=3D0.00..25948.57 rows=3D1 width=
=3D8)
               ->  Nested Loop  (cost=3D0.00..25741.69 rows=3D34 width=3D10)
                     ->  Nested Loop  (cost=3D0.00..25186.62 rows=3D47 widt=
h=3D4)
                           Join Filter: (s.subscription_type =3D
st.subscription_type)
                           ->  Nested Loop  (cost=3D0.00..25154.54 rows=3D1=
64
width=3D6)
                                 Join Filter: ((s.end_date <
nop_seed.paid_thru) OR s.void)
                                 ->  Seq Scan on nop_seed  (cost=3D0.00..1.=
01
rows=3D1 width=3D4)
                                 ->  Seq Scan on subscriptions s =

(cost=3D0.00..25148.50 rows=3D403 width=3D11)
                                       Filter: ((CASE WHEN (NOT void) THEN
end_date ELSE ((end_date - '1 mon'::interval))::date END <=3D
((date_trunc('month'::text, now()))::date - 1)) AND (CASE WHEN (NOT void)
THEN end_date ELSE ((end_date
 - '1 mon'::interval))::date END >=3D first_of_another_month((((now())::dat=
e -
'1 mon'::interval))::date)))
                           ->  Materialize  (cost=3D0.00..2.58 rows=3D12
width=3D2)
                                 ->  Seq Scan on subscription_types st =

(cost=3D0.00..2.52 rows=3D12 width=3D2)
                                       Filter: ((sub_class)::text =3D
'VM'::text)
                     ->  Index Scan using members_pkey on members m =

(cost=3D0.00..11.80 rows=3D1 width=3D6)
                           Index Cond: (id =3D s.member_id)
                           Filter: (mail_option_lvl > 0)
               ->  Index Scan using cancellations_id_key on cancellations c =

(cost=3D0.00..6.07 rows=3D1 width=3D6)
                     Index Cond: (id =3D m.id)
                     Filter: ((CASE WHEN (id IS NULL) THEN m.membertype WHEN
((status =3D 'tr'::text) OR (status =3D 'p'::text) OR (status =3D 'cd'::tex=
t) OR
(status =3D 'sp'::text) OR (status =3D 'sa'::text)) THEN m.membertype ELSE
'c'::character
varying END)::text =3D 'v'::text)
         ->  Index Only Scan using mop_pkey on mop  (cost=3D0.00..5.99 rows=
=3D1
width=3D4)
               Index Cond: (id =3D m.id)
(22 rows)