BUG #10667: [9.2.4] Incorrect output for query involving where clause with coalesce - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      10667
Logged by:          Timothy Garnett
Email address:      tgarnett@panjiva.com
PostgreSQL version: 9.2.4
Operating system:   Ubuntu Linux x86_64 12.04.3 LTS
Description:

Hi All,

I'm not sure if this is appropriate to report or not since I'm only able to
produce it in 9.2.4 (not able to produce it 9.3.4) but I didn't see anything
in the change logs that looked specific to what we saw here [though this
("Fix incorrect planning in cases where the same non-strict expression
appears in multiple WHERE and outer JOIN equality clauses (Tom Lane)") might
be similar?] so thought it might be useful as a record if nothing else.

In 9.2.4 this setup will produce incorrect output:

CREATE SEQUENCE pid_seq;
CREATE SEQUENCE bid_seq;
CREATE SEQUENCE sid_seq;

CREATE TABLE sbr (
sid integer NOT NULL DEFAULT nextval('sid_seq'),
bid integer NOT NULL DEFAULT nextval('bid_seq'),
dummy integer
);
CREATE INDEX sbr_bid ON sbr (bid);
CREATE INDEX sbr_sid ON sbr (sid);

CREATE TABLE jc (
id integer CONSTRAINT jc_pkey PRIMARY KEY,
sid integer,
bid integer,
pid integer NOT NULL DEFAULT nextval('pid_seq'),
apid integer,
dummy integer
);
CREATE INDEX jc_sid ON jc (sid);
CREATE INDEX jc_bid ON jc (bid);
CREATE INDEX jc_pid ON jc (pid);
CREATE INDEX jc_apid ON jc (apid);

CREATE TABLE ap (
id integer CONSTRAINT ap_pkey PRIMARY KEY,
pid integer NOT NULL DEFAULT nextval('pid_seq'),
dummy integer
);
CREATE INDEX ap_pid ON ap (pid);

INSERT INTO ap SELECT dummy FROM generate_series(1,1000000) dummy;
INSERT INTO sbr (sid, bid) SELECT dummy, dummy FROM generate_series(1,10)
dummy;
INSERT INTO jc (id, sid, apid) SELECT dummy, dummy, dummy FROM
generate_series(1,10) dummy;

SELECT COALESCE(ap.pid, jc.pid) AS pid
 FROM sbr
  JOIN jc ON jc.sid = sbr.sid
  LEFT JOIN ap ON ap.id = jc.apid
 WHERE sbr.bid IN (1,2,3) AND COALESCE(ap.pid, jc.pid) = 1
GROUP BY COALESCE(ap.pid, jc.pid) LIMIT 10;
=>
   pid
---------
       1
 1000002
 1000003

-- This returns 3 rows, two of which don't actually satisfy the where
clause. The plan:

 Limit  (cost=0.00..757.49 rows=1 width=8) (actual time=0.038..0.062 rows=3
loops=1)
   ->  Group  (cost=0.00..757.49 rows=1 width=8) (actual time=0.037..0.061
rows=3 loops=1)
         ->  Nested Loop Left Join  (cost=0.00..757.49 rows=1 width=8)
(actual time=0.034..0.055 rows=3 loops=1)
               ->  Nested Loop  (cost=0.00..57.48 rows=219 width=8) (actual
time=0.023..0.032 rows=3 loops=1)
                     ->  Index Scan using sbr_bid on sbr  (cost=0.00..22.27
rows=29 width=4) (actual time=0.014..0.017 rows=3 loops=1)
                           Index Cond: (bid = ANY ('{1,2,3}'::integer[]))
                     ->  Index Scan using jc_sid on jc  (cost=0.00..1.13
rows=8 width=12) (actual time=0.002..0.002 rows=1 loops=3)
                           Index Cond: (sid = sbr.sid)
               ->  Index Scan using ap_pkey on ap  (cost=0.00..3.19 rows=1
width=8) (actual time=0.006..0.007 rows=0 loops=3)
                     Index Cond: (id = jc.apid)
                     Filter: (COALESCE(pid, jc.pid) = 1)
                     Rows Removed by Filter: 1
 Total runtime: 0.120 ms
(13 rows)

Shows the filter being applied at a place where it's not possible for it to
be applied (not all relevant columns present).

On 9.3.4 the query does plan and execute correctly:

 Limit  (cost=0.73..782.18 rows=1 width=8) (actual time=0.032..0.046 rows=1
loops=1)
   ->  Group  (cost=0.73..782.18 rows=1 width=8) (actual time=0.031..0.045
rows=1 loops=1)
         ->  Nested Loop Left Join  (cost=0.73..782.18 rows=1 width=8)
(actual time=0.029..0.042 rows=1 loops=1)
               Filter: (COALESCE(ap.pid, jc.pid) = 1)
               Rows Removed by Filter: 2
               ->  Nested Loop  (cost=0.31..54.36 rows=219 width=8) (actual
time=0.019..0.025 rows=3 loops=1)
                     ->  Index Scan using sbr_bid on sbr  (cost=0.15..21.97
rows=29 width=4) (actual time=0.013..0.015 rows=3 loops=1)
                           Index Cond: (bid = ANY ('{1,2,3}'::integer[]))
                     ->  Index Scan using jc_sid on jc  (cost=0.15..1.04
rows=8 width=12) (actual time=0.002..0.002 rows=1 loops=3)
                           Index Cond: (sid = sbr.sid)
               ->  Index Scan using ap_pkey on ap  (cost=0.42..3.31 rows=1
width=8) (actual time=0.004..0.005 rows=1 loops=3)
                     Index Cond: (id = jc.apid)
 Total runtime: 0.093 ms
(13 rows)