Thread: BUG #7570: WHERE .. IN bug from 9.2.0 not fixed in 9.2.1
The following bug has been logged on the website: Bug reference: 7570 Logged by: Melese Tesfaye Email address: mtesfaye@gmail.com PostgreSQL version: 9.2.1 Operating system: Ubuntu 12.04.1 LTS + Debian 6 (both x86_64 Description: = I had a problem with missing rows in a resultset when using WHERE .. IN after upgrading to 9.2.0. I was about to file a bug report when I found out that verion 9.2.1 was just released to address the index visibility issue. = I then upgraded to 9.2.1 and followed the instructions for vacuuming and rebuilding indices (http://wiki.postgresql.org/wiki/20120924updaterelease#Steps_for_Users_of_P= ostgreSQL_9.2). I still missed rows in the resultset. I ended up downgrading to 9.1.5 and it works just fine without missing rows. = Here are my examples: Query 1 - note **no rows returned for pnr_id 2056 ** not the desired outcome SELECT DISTINCT(A.*) FROM pnr_names_t A LEFT JOIN pnr_itn_v B ON A.pnr_id=3DB.pnr_id WHERE A.pnr_id IN(1801,2056) AND B.departure_date_time>=3DDATE('2012-09-26= ') ORDER BY pnr_id ASC,nam_id ASC; +--------+--------+---------+ | pnr_id | nam_id | pty_num | +--------+--------+---------+ | 1801 | 3359 | 1 | | 1801 | 3360 | 1 | | 1801 | 3361 | 1 | | 1801 | 3362 | 1 | +--------+--------+---------+ (4 rows) Query 2 - note **rows returned for = pnr_id 2056 *** desired outcome. The difference between Query 1 and Query 2 is the presence of two pnr_ids in the IN element in Query 1 SELECT DISTINCT(A.*) FROM pnr_names_t A LEFT JOIN pnr_itn_v B ON A.pnr_id=3DB.pnr_id WHERE A.pnr_id IN(2056) AND B.departure_date_time>=3DDATE('2012-09-26') ORDER BY pnr_id ASC,nam_id ASC; +--------+--------+---------+ | pnr_id | nam_id | pty_num | +--------+--------+---------+ | 2056 | 3894 | 1 | | 2056 | 3895 | 1 | +--------+--------+---------+ (2 rows) When using queries against tables directly (that is, without using views), then the resultset is as expected. = Thanks
mtesfaye@gmail.com writes: > I had a problem with missing rows in a resultset when using WHERE .. IN > after upgrading to 9.2.0. We'll need a self-contained test case to investigate that. The query alone is of no help without table definitions and sample data sufficient to reproduce the misbehavior. Since I gather that the table names in the query are not even tables but views, what you've provided is even more inadequate for investigation. You can find some advice about submitting useful bug reports at https://wiki.postgresql.org/wiki/Guide_to_reporting_problems regards, tom lane
Melese Tesfaye <mtesfaye@gmail.com> writes:
> I do have a self-contained test case which duplicates the problem. However,
> since there are data to be attached and there wasn't a way to attach in the
> bug reporting form, I wasn't sure how to proceed.
Just send it to the pgsql-bugs@postgresql.org mailing list. (If you
subscribe first, it won't get delayed for moderation ... but you're not
required to do that.)
regards, tom lane
Melese Tesfaye <mtesfaye@gmail.com> writes:
> [ test case ]
Argh. The problem query has a plan like this:
-> Merge Join (cost=1084.06..1354.58 rows=4 width=13)
Merge Cond: (table2_t.pnr_id = a.pnr_id)
-> stuff ...
-> Index Scan using table1_t_pnr_id_idx5 on table1_t a (cost=0.00..12.60 rows=4 width=13)
Index Cond: (pnr_id = ANY ('{1801,2056}'::integer[]))
which means the indexscan has to support mark/restore calls. And it
looks like I blew it on mark/restore support when I taught btree to
handle =ANY conditions natively,
http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=9e8da0f75731aaa7605cf4656c21ea09e84d2eb1
Will look into fixing that tomorrow. In the meantime, you should be
able to work around this with "set enable_mergejoin = off".
regards, tom lane
Thanks Tom,
Yes, that did it - it worked after setting enable_mergejoin to off.
mtesfaye@[local](test_db)=# set enable_mergejoin = off;
SET
Time: 0.107 ms
mtesfaye@[local](test_db)=# SELECT DISTINCT(A.*)
FROM table1_t A LEFT JOIN table2_v B
ON A.pnr_id=B.pnr_id
WHERE A.pnr_id IN(1801,2056) AND B.departure_date_time>=DATE('2012-09-26')
ORDER BY pnr_id ASC,nam_id ASC;
+--------+--------+---------+
| pnr_id | nam_id | pty_num |
+--------+--------+---------+
| 1801 | 3359 | 1 |
| 1801 | 3360 | 1 |
| 1801 | 3361 | 1 |
| 1801 | 3362 | 1 |
| 2056 | 3894 | 1 |
| 2056 | 3895 | 1 |
+--------+--------+---------+
(6 rows)
Time: 14.273 ms
On Thu, Sep 27, 2012 at 5:13 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Melese Tesfaye <mtesfaye@gmail.com> writes:
> > [ test case ]
>
> Argh. The problem query has a plan like this:
>
> -> Merge Join (cost=1084.06..1354.58 rows=4 width=13)
> Merge Cond: (table2_t.pnr_id = a.pnr_id)
> -> stuff ...
> -> Index Scan using table1_t_pnr_id_idx5 on table1_t a
> (cost=0.00..12.60 rows=4 width=13)
> Index Cond: (pnr_id = ANY ('{1801,2056}'::integer[]))
>
> which means the indexscan has to support mark/restore calls. And it
> looks like I blew it on mark/restore support when I taught btree to
> handle =ANY conditions natively,
>
> http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=9e8da0f75731aaa7605cf4656c21ea09e84d2eb1
>
> Will look into fixing that tomorrow. In the meantime, you should be
> able to work around this with "set enable_mergejoin = off".
>
> regards, tom lane
>
Thanks Tom, I do have a self-contained test case which duplicates the problem. However, since there are data to be attached and there wasn't a way to attach in the bug reporting form, I wasn't sure how to proceed. Would appreciate your assistance. Thanks again. On Wed, Sep 26, 2012 at 11:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > mtesfaye@gmail.com writes: > > I had a problem with missing rows in a resultset when using WHERE .. IN > > after upgrading to 9.2.0. > > We'll need a self-contained test case to investigate that. The query > alone is of no help without table definitions and sample data sufficient > to reproduce the misbehavior. Since I gather that the table names in > the query are not even tables but views, what you've provided is even > more inadequate for investigation. > > You can find some advice about submitting useful bug reports at > https://wiki.postgresql.org/wiki/Guide_to_reporting_problems > > regards, tom lane >
I wrote:
> Argh. The problem query has a plan like this: ...
> which means the indexscan has to support mark/restore calls. And it
> looks like I blew it on mark/restore support when I taught btree to
> handle =ANY conditions natively,
I've committed a patch for this. Thanks for the report!
regards, tom lane