Weird ..... (a=1 or a=2) <> (a=2 or a=1)

Hello

Today, one user complained that one of the tickets in our system had
disappeared some places but could be accessed other places. I thought
this was weird and startet debugging.

I have found out the sql statement with 'problems'. Can anybody explain
me why A) returns 12 rows and B) returns 13 rows?. The only different is
the "open OR new" / "new OR open" part. Should not they return the samme
result?

PostgreSQL ver.: 7.4.12

--------------------------------------------------
A)
--------------------------------------------------
rtprod=# SELECT id,effectiveid,status,type,queue FROM Tickets main
WHERE ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted'))
AND ((main.Type = 'ticket')) AND ((main.Queue = '29')AND ( (main.Status
= 'open') OR (main.Status = 'new') ) )  ORDER BY main.id ASC  LIMIT 50;
  id   | effectiveid | status |  type  | queue
-------+-------------+--------+--------+-------
 37968 |       37968 | open   | ticket |    29
 38052 |       38052 | open   | ticket |    29
 38176 |       38176 | open   | ticket |    29
 38185 |       38185 | open   | ticket |    29
 38386 |       38386 | open   | ticket |    29
 38394 |       38394 | open   | ticket |    29
 38403 |       38403 | open   | ticket |    29
 38406 |       38406 | open   | ticket |    29
 38422 |       38422 | open   | ticket |    29
 38467 |       38467 | open   | ticket |    29
 38474 |       38474 | open   | ticket |    29
 38530 |       38530 | open   | ticket |    29
(12 rows)
--------------------------------------------------
B)
--------------------------------------------------
rtprod=# SELECT id,effectiveid,status,type,queue FROM Tickets main
WHERE ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted'))
AND ((main.Type = 'ticket')) AND ((main.Queue = '29')AND ( (main.Status
= 'new') OR (main.Status = 'open') ) )  ORDER BY main.id ASC  LIMIT 50;
  id   | effectiveid | status |  type  | queue
-------+-------------+--------+--------+-------
 37775 |       37775 | new    | ticket |    29
 37968 |       37968 | open   | ticket |    29
 38052 |       38052 | open   | ticket |    29
 38176 |       38176 | open   | ticket |    29
 38185 |       38185 | open   | ticket |    29
 38386 |       38386 | open   | ticket |    29
 38394 |       38394 | open   | ticket |    29
 38403 |       38403 | open   | ticket |    29
 38406 |       38406 | open   | ticket |    29
 38422 |       38422 | open   | ticket |    29
 38467 |       38467 | open   | ticket |    29
 38474 |       38474 | open   | ticket |    29
 38530 |       38530 | open   | ticket |    29
(13 rows)
--------------------------------------------------

The ticket with id=37775 is the one that disappear some places in the
application.

Here is the explain analyze of these statements:
--------------------------------------------------

rtprod=# explain analyze SELECT id,effectiveid,status,type,queue FROM
Tickets main   WHERE ((main.EffectiveId = main.id)) AND ((main.Status !=
'deleted')) AND ((main.Type = 'ticket')) AND ((main.Queue = '29')AND (
(main.Status = 'open') OR (main.Status = 'new') ) )  ORDER BY main.id
ASC  LIMIT 50;

QUERY
PLAN
--------------------------------------------------------------------------------------
 Limit  (cost=1658.83..1658.84 rows=1 width=33) (actual
time=4.003..4.028 rows=12 loops=1)
   ->  Sort  (cost=1658.83..1658.84 rows=1 width=33) (actual
time=3.999..4.008 rows=12 loops=1)
         Sort Key: id
         ->  Index Scan using tickets6, tickets6 on tickets main
(cost=0.00..1658.82 rows=1 width=33) (actual time=1.001..3.969 rows=12
loops=1)
               Index Cond: (((status)::text = 'open'::text) OR
((status)::text = 'new'::text))
               Filter: ((effectiveid = id) AND ((status)::text <>
'deleted'::text) AND (("type")::text = 'ticket'::text) AND (queue = 29))
 Total runtime: 4.104 ms
(7 rows)

rtprod=# explain analyze SELECT id,effectiveid,status,type,queue FROM
Tickets main   WHERE ((main.EffectiveId = main.id)) AND ((main.Status !=
'deleted')) AND ((main.Type = 'ticket')) AND ((main.Queue = '29')AND (
(main.Status = 'new') OR (main.Status = 'open') ) )  ORDER BY main.id
ASC  LIMIT 50;

QUERY
PLAN
----------------------------------------------------------------------------------------
 Limit  (cost=1658.83..1658.84 rows=1 width=33) (actual
time=3.946..3.974 rows=13 loops=1)
   ->  Sort  (cost=1658.83..1658.84 rows=1 width=33) (actual
time=3.944..3.954 rows=13 loops=1)
         Sort Key: id
         ->  Index Scan using tickets6, tickets6 on tickets main
(cost=0.00..1658.82 rows=1 width=33) (actual time=0.790..3.909 rows=13
loops=1)
               Index Cond: (((status)::text = 'new'::text) OR
((status)::text = 'open'::text))
               Filter: ((effectiveid = id) AND ((status)::text <>
'deleted'::text) AND (("type")::text = 'ticket'::text) AND (queue = 29))
 Total runtime: 4.052 ms
(7 rows)
--------------------------------------------------

Thanks in advance
--
Rafael Martinez, <r.m.guerrero@usit.uio.no>
Center for Information Technology Services
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/