Обсуждение: BUG #10667: [9.2.4] Incorrect output for query involving where clause with coalesce

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)

Timothy Garnett wrote
> The following bug has been logged on the website:
>
> Bug reference:      10667
> Logged by:          Timothy Garnett
> Email address:

> tgarnett@

> 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.

If you cannot reproduce this in 9.2.8 - the most recent version - then you
either need to work around it or upgrade.  Reporting a bug on a 4 behind
point version does little good.

David J.





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/BUG-10667-9-2-4-Incorrect-output-for-query-involving-where-clause-with-coalesce-tp5807410p5807418.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

"tgarnett@panjiva.com" <tgarnett@panjiva.com> wrote:=0A=0A> I'm not sure if=
 this is appropriate to report or not since I'm=0A> only able to produce it=
 in 9.2.4 (not able to produce it 9.3.4)=0A=0A> [test case]=0A=0AConfirmed =
bug in 9.2.4.=A0 Confirmed fixed in 9.2.8.=A0 Haven't bisected.=0A=0A--=0AK=
evin Grittner=0AEDB: http://www.enterprisedb.com=0AThe Enterprise PostgreSQ=
L Company

tgarnett@panjiva.com wrote:
> 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.

This doesn't reproduce in 9.2.8 FWIW, which is a year ahead of 9.2.4 in
terms of bugfixes.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

tgarnett@panjiva.com writes:
> 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.

I think this is the same thing as bug #8049, which was fixed in 9.2.5.

            regards, tom lane