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(dot)m(dot)guerrero(at)usit(dot)uio(dot)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(dot)m(dot)guerrero(at)usit(dot)uio(dot)no>
Center for Information Technology Services
University of Oslo, Norway

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