The following bug has been logged on the website: Bug reference: 7570 Logged by: Melese Tesfaye Email address: mtesf...@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_PostgreSQL_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=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 | +--------+--------+---------+ (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=B.pnr_id WHERE A.pnr_id IN(2056) AND B.departure_date_time>=DATE('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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
