Re: [GENERAL] Bug with indexing int4?

Yury Don wrote:

> Hello All,
>
> I ma sorry, I have sent previous uncomplited e-mail accidentally.
> 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
>
> ************

I tried it on my Debian and it works...

hygea=> select version();
version
--------------------------------------------------------------
PostgreSQL 6.5.2 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
(1 row)

uname -a
Linux Debian 2.0.37 #1 Thu Sep 2 17:24:38 CEST 1999 i586 unknown

==================================================================

CREATE TABLE "tt" (
"cc" int4);
CREATE
COPY "tt" FROM stdin;

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

create index i_tt_cc on tt (cc);
CREATE
select cc from tt where cc = -2112563299;
cc
-----------
-2112563299
(1 row)

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

José