Re: Strange behavior for query comparing 8.2.5 and 8.3.0

We have a query that returns a different result in 8.2.5 and 8.3.0.
This is the query, I apologize for the names and aliases, it is
hibernate-generated.

select * from action_items actionitem0_
left outer join action_item_states states1_ on
actionitem0_.id=states1_.action_item_id
where (
actionitem0_.organization_id='5b2d316e-9ede-4869-
a253-804275f12af7'
or actionitem0_.organization_id is null
)
and (
actionitem0_.inclusive=true
and
states1_.contact_id='b0606295-9e5a-452b-84a2-1b4e1948f410'
and states1_.action_item_status='pending'
or actionitem0_.inclusive=false
and (
states1_.id is null
or states1_.contact_id <>
'b0606295-9e5a-452b-84a2-1b4e1948f410'
and (
states1_.action_item_status is null
)
)
)

In 8.2.5 the explain output looks like this;

-----------8.2.5 EXPLAIN----------
Nested Loop Left Join (cost=0.00..113.87 rows=1 width=1022)
Filter: ((actionitem0_.inclusive AND ((states1_.contact_id)::text
= 'b0606295-9e5a-452b-84a2-1b4e1948f410'::text) AND
((states1_.action_item_status)::text = 'pending'::text)) OR ((NOT
actionitem0_.inclusive) AND ((states1_.id IS NULL) OR
(((states1_.contact_id)::text <>
'b0606295-9e5a-452b-84a2-1b4e1948f410'::text) AND
(states1_.action_item_status IS NULL)))))
-> Seq Scan on action_items actionitem0_ (cost=0.00..2.25
rows=14 width=867)
Filter: ((((organization_id)::text = '5b2d316e-9ede-4869-
a253-804275f12af7'::text) OR (organization_id IS NULL)) AND (inclusive
OR (NOT inclusive)))
-> Index Scan using idx_action_item_states_action_item_id on
action_item_states states1_ (cost=0.00..7.65 rows=16 width=155)
Index Cond: ((actionitem0_.id)::text =
(states1_.action_item_id)::text)
---------------------

In 8.3.0 the explain output looks like this:

-----------8.3.0 EXPLAIN----------
Nested Loop Left Join (cost=0.00..18.54 rows=1 width=4561)
Filter: ((actionitem0_.inclusive AND ((states1_.contact_id)::text
= 'b0606295-9e5a-452b-84a2-1b4e1948f410'::text) AND
((states1_.action_item_status)::text = 'pending'::text)) OR ((NOT
actionitem0_.inclusive) AND ((states1_.id IS NULL) OR
(((states1_.contact_id)::text <>
'b0606295-9e5a-452b-84a2-1b4e1948f410'::text) AND
(states1_.action_item_status IS NULL)))))
-> Seq Scan on action_items actionitem0_ (cost=0.00..10.25
rows=1 width=3751)
Filter: ((inclusive OR (NOT inclusive)) AND
(((organization_id)::text = '5b2d316e-9ede-4869-
a253-804275f12af7'::text) OR (organization_id IS NULL)))
-> Index Scan using idx_action_item_states_action_item_id on
action_item_states states1_ (cost=0.00..8.27 rows=1 width=810)
Index Cond: ((actionitem0_.id)::text =
(states1_.action_item_id)::text)
Filter: (((states1_.contact_id)::text =
'b0606295-9e5a-452b-84a2-1b4e1948f410'::text) OR (states1_.id IS NULL)
OR (states1_.action_item_status IS NULL))
---------------------

As far as I can tell, the only difference is that in 8.2.5 that final
Filter is not happening. We disabled hashjoins and mergejoins in
8.2.5 to force it to take the same plan as 8.3.0 but even before
disabling those this Filter was not present.

The result in 8.3.0 is what we expect. We are currently testing 8.3
on our local workstations but our QA and Production environments are
still on 8.2.5. It is when we pushed our code to QA, running on 8.2.5
that we noticed the issue.

Any idea why the query is different between versions? Perhaps it is
time for us to just bite the bullet and get 8.3 on our servers.
Please let me know if I can provide any additional information.

Any help is greatly appreciated. I apologize if this is a known
issue, I was unable to turn up a match though struggled to figure out
which search terms might yield a result.

Joe

Joe Uhl <joeuhl(at)gmail(dot)com> writes:
> We have a query that returns a different result in 8.2.5 and 8.3.0.

I think this is the same bug discussed yesterday:
http://archives.postgresql.org/pgsql-bugs/2008-09/msg00078.php

regards, tom lane

Possibly, I saw that thread but may not have dug into the content
deeply enough. My apologies if this is the case, i'll check it out
further.

On Sep 10, 2008, at 3:38 PM, Tom Lane wrote:

> Joe Uhl <joeuhl(at)gmail(dot)com> writes:
>> We have a query that returns a different result in 8.2.5 and 8.3.0.
>
> I think this is the same bug discussed yesterday:
> http://archives.postgresql.org/pgsql-bugs/2008-09/msg00078.php
>
> regards, tom lane

Joe Uhl <joeuhl(at)gmail(dot)com> writes:
> We have a query that returns a different result in 8.2.5 and 8.3.0.
> ...
> The result in 8.3.0 is what we expect.

I guess I read a little too fast, because I overlooked that comment.
It's the 8.3.0 plan that is broken in my opinion --- it shouldn't be
pushing down the IS NULL conditions. If you think that 8.2.5 is giving
a wrong answer then you need to provide a test case demonstrating that.

regards, tom lane