Re[3]: [GENERAL] Bug or my crooked hands?

Hello Richard,

Once, Thursday, January 20, 2000, 3:04:43 PM, you wrote:

RH> ----- Original Message -----
RH> From: Yury Don <yura(at)vpcit(dot)ru>
RH> To: pgsql-general <pgsql-general(at)postgreSQL(dot)org>
RH> Sent: Wednesday, January 19, 2000 11:32 AM
RH> Subject: Re[2]: [GENERAL] Bug or my crooked hands?
>> >> I have the following situation:
>> >>
>> >> CREATE TABLE "Contacts" (
>> >> "Contact" Serial,
RH> [snipped fields]
>> >> );
>> >>
>> >> Then I have inserted data from text file and do a query
>> >>
>> >> mdb=> select Contact from Contacts;
>> >> Contacts
>> >> -----------
>> >> -2112563299
RH> [snip]
>> >> ...
>>
>> JR> How did you get negative values in a Serial - field??
>> JR> Serial-fields default to positive numbers....
>>
>> I have copied the data from MS Access table and there where negative
>> values. I can't change data in field Contacts because they are using
>> in other tables.

RH> Speaking of which, what range have you got on that sequence (serial type =
RH> sequence AFAIK)?

RH> It couldn't be that postgres is looking at your WHERE and deciding that the
RH> value is out of range, so there can't be any records to return.

RH> What happens if you insert a new record and then try to look for that?

RH> --
RH> Richard Huxton
RH> Archonet Ltd.

RH> ************

I am already found that this is seems a bug in postgres with using of
indexes on int4.

I have created the table

CREATE TABLE "tt" (
"cc" int4);
COPY "tt" FROM stdin;
-2112563299
-2111287024
-2110307960
.....

2146589610
2146589611
2146589612
\.

About 30 000 records totally

Then I am doing the following:

mdb=> select cc from tt where cc = -2112563299;
cc
-----------
-2112563299
(1 row)

mdb=> create index i_tt_cc on tt (cc);
CREATE
mdb=> select cc from tt where cc = -2112563299;
cc
(0 rows)

mdb=> drop index i_tt_cc;
DROP
mdb=> select cc from tt where cc = -2112563299;
cc
-----------
-2112563299
(1 row)

So, whith index postgresql works incorectly in this situation.
But when there are less records in the table (about 12 000),
everything works well.
I tested this on 3 different computers on Debian Linux 2.1 with
postgresql 6.5.2 and 6.5.3.

Best regards,
Yury ICQ 11831432
mailto:yura(at)vpcit(dot)ru