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

On Tue, 2006-05-16 at 12:32 -0400, Tom Lane wrote:
> Rafael Martinez <r.m.guerrero@usit.uio.no> writes:
> > On Tue, 2006-05-16 at 11:14 -0400, Tom Lane wrote:
> >> I suspect this may be a question of a corrupt index, in which case
> >> REINDEXing the index being used would fix it.
>
> > This is what I thought when I found out the problem. So before I sent my
> > first e-mail I executed a "reindex index tickets6" but it did not help.
>
> So much for that theory.  If you copy the table (create table foo as
> select * from tickets) and build a similar index on the copy, does the
> behavior persist in the copy?
>

The new table behaves well:
----------------------------------------------
rtprod=# CREATE TABLE foo as select * from tickets;
SELECT

rtprod=# CREATE INDEX foo6 on foo (status);
CREATE INDEX

rtprod=# SELECT id,effectiveid,status,type,queue FROM foo 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
-------+-------------+--------+--------+-------
 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
 38474 |       38474 | open   | ticket |    29
 38530 |       38530 | open   | ticket |    29
 38539 |       38539 | open   | ticket |    29
(13 rows)

rtprod=# SELECT id,effectiveid,status,type,queue FROM foo 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
 38474 |       38474 | open   | ticket |    29
 38530 |       38530 | open   | ticket |    29
 38539 |       38539 | open   | ticket |    29
(13 rows)
----------------------------------------------

> I'm wondering a little bit about encoding issues.  What encoding does
> the database have (see \l)

SQL_ASCII

> and what do SHOW LC_COLLATE and SHOW LC_CTYPE
> show?
>

----------------------------------------------
rtprod=# SHOW LC_COLLATE;
 lc_collate
------------
 C
(1 row)

rtprod=# SHOW LC_CTYPE;
 lc_ctype
----------
 C
(1 row)
----------------------------------------------



--
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/