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_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
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
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:
Show quoted text
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_problemsregards, 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
Import Notes
Reply to msg id not found: CAJWHPqp10-NBu4ebez9mCaTY7oLVP1e-CGr-OAScLeOuJMWBxA@mail.gmail.com
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:
Show quoted text
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,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
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