BUG #7570: WHERE .. IN bug from 9.2.0 not fixed in 9.2.1

From: mtesfaye(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #7570: WHERE .. IN bug from 9.2.0 not fixed in 9.2.1
Date: 2012-09-26 16:31:40
Message-ID: E1TGuWS-00061g-Vz@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 7570
Logged by: Melese Tesfaye
Email address: mtesfaye(at)gmail(dot)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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Sachin Srivastava 2012-09-26 20:02:19 Re: BUG #7565: not able to install
Previous Message mtesfaye 2012-09-26 16:25:21 BUG #7569: WHERE .. IN bug from 9.2.0 not fixed in 9.2.1