Thread: Problem with SELECT on large negative INT4
I have a DB with is updated using MS Access. Primary keys are Int4 with default random values ("NuméroAuto" +
"Aléatoire"in Access).
The DB is migrated as-is in Postgres, with tbl_prod.cle_prod field containing values from -2057496808 to 2139583719.
When I SELECT in the table, using the INT4 cle_prod value, PG doesn't find the tuple. When I SELECT using the
VARCHAR(10)ref_prod value, PG finds the tuple, and show the right value for the cle_prod filed : the same as the one I
SELECTedfor...
This sounds like the long negative integer values given in PSQL is not converted correctly while executing.
Using a long positive integer value, all works like a charm...
Below is the queries type sto sho what append.
I'm using Postgres 6.5.2 from the RPMs.
Nicolas Huillard
gest=> select cle_prod, ref_prod from tbl_prod where ref_prod='4866VC';
cle_prod|ref_prod
-----------+--------
-2057496808|4866VC
(1 row)
gest=> select cle_prod, ref_prod from tbl_prod where cle_prod = -2057496808;
cle_prod|ref_prod
--------+--------
(0 rows)
gest=> \d tbl_prod
Table = tbl_prod
+----------------------------------+----------------------------------+-------+
| Field | Type | Length|
+----------------------------------+----------------------------------+-------+
| cle_prod | int4 not null default nextval('" | 4 |
| ref_prod | varchar() not null | 10 |
...
+----------------------------------+----------------------------------+-------+
Index: tbl_prod_pkey
gest=> select version();
version
--------------------------------------------------------------
PostgreSQL 6.5.2 on i486-pc-linux-gnu, compiled by gcc 2.7.2.3
(1 row)
gest=>
> -----Original Message-----
> From: owner-pgsql-general@postgresql.org
> [mailto:owner-pgsql-general@postgresql.org]On Behalf Of Nicolas Huillard
>
> I have a DB with is updated using MS Access. Primary keys are
> Int4 with default random values ("NuméroAuto" + "Aléatoire" in Access).
> The DB is migrated as-is in Postgres, with tbl_prod.cle_prod
> field containing values from -2057496808 to 2139583719.
> When I SELECT in the table, using the INT4 cle_prod value, PG
> doesn't find the tuple. When I SELECT using the VARCHAR(10)
> ref_prod value, PG finds the tuple, and show the right value for
> the cle_prod filed : the same as the one I SELECTed for...
>
> This sounds like the long negative integer values given in PSQL
> is not converted correctly while executing.
> Using a long positive integer value, all works like a charm...
>
> Below is the queries type sto sho what append.
> I'm using Postgres 6.5.2 from the RPMs.
>
Could you try the follwoing patch ?
Regards.
Hiroshi Inoue
*** nbtcompare.c 2000/01/11 03:33:34 1.3
--- nbtcompare.c 2000/01/19 05:42:06
***************
*** 34,40 ****
int32
btint4cmp(int32 a, int32 b)
{
! return a - b;
}
int32
--- 34,45 ----
int32
btint4cmp(int32 a, int32 b)
{
! if (a > b)
! return 1;
! else if (a == b)
! return 0;
! else
! return -1;
}
int32
[Charset iso-8859-1 unsupported, filtering to ASCII...]
> > -----Original Message-----
> > From: owner-pgsql-general@postgresql.org
> > [mailto:owner-pgsql-general@postgresql.org]On Behalf Of Nicolas Huillard
> >
> > I have a DB with is updated using MS Access. Primary keys are
> > Int4 with default random values ("Num_roAuto" + "Al_atoire" in Access).
> > The DB is migrated as-is in Postgres, with tbl_prod.cle_prod
> > field containing values from -2057496808 to 2139583719.
> > When I SELECT in the table, using the INT4 cle_prod value, PG
> > doesn't find the tuple. When I SELECT using the VARCHAR(10)
> > ref_prod value, PG finds the tuple, and show the right value for
> > the cle_prod filed : the same as the one I SELECTed for...
> >
> > This sounds like the long negative integer values given in PSQL
> > is not converted correctly while executing.
> > Using a long positive integer value, all works like a charm...
> >
> > Below is the queries type sto sho what append.
> > I'm using Postgres 6.5.2 from the RPMs.
> >
>
> Could you try the follwoing patch ?
Hiroshi, I don't see this in the main tree, nor do I see it having been
requested for application. Should I apply it?
>
> Regards.
>
> Hiroshi Inoue
>
> *** nbtcompare.c 2000/01/11 03:33:34 1.3
> --- nbtcompare.c 2000/01/19 05:42:06
> ***************
> *** 34,40 ****
> int32
> btint4cmp(int32 a, int32 b)
> {
> ! return a - b;
> }
>
> int32
> --- 34,45 ----
> int32
> btint4cmp(int32 a, int32 b)
> {
> ! if (a > b)
> ! return 1;
> ! else if (a == b)
> ! return 0;
> ! else
> ! return -1;
> }
>
> int32
>
>
>
> ************
>
>
--
Bruce Momjian | http://www.op.net/~candle
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> Could you try the follwoing patch ?
> Hiroshi, I don't see this in the main tree, nor do I see it having been
> requested for application. Should I apply it?
If this does fix things, I would venture that the blame really belongs
to whatever caller of the comparison function is failing to interpret
the return value correctly ...
BTW there is a somewhat-related issue that has been bothering me:
the system is pretty erratic about converting between Datum and bool.
Some places use "DatumGetInt32(datum) != 0" as their effective test
for whether a Datum value is "true", some places use
"DatumGetInt8(datum) != 0", some places just do "(bool) datum", and
there may be other variants.
I am thinking of introducing DatumGetBool and BoolGetDatum macros and
trying to make the code use those uniformly, but there's a lot of code
to clean up. I was intending to do it during the function manager
interface rewrite, since all these same places will need to be looked
at for that.
Perhaps we should also try to clean up btree's sort comparison functions
in the same way, with some macros that provide a well-defined conversion
between Datum and the "<", "=", ">" return conditions that are intended.
regards, tom lane
> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
>
> [Charset iso-8859-1 unsupported, filtering to ASCII...]
> > > -----Original Message-----
> > > From: owner-pgsql-general@postgresql.org
> > > [mailto:owner-pgsql-general@postgresql.org]On Behalf Of
> Nicolas Huillard
> > >
> > > I have a DB with is updated using MS Access. Primary keys are
> > > Int4 with default random values ("Num_roAuto" + "Al_atoire"
> in Access).
> > > The DB is migrated as-is in Postgres, with tbl_prod.cle_prod
> > > field containing values from -2057496808 to 2139583719.
> > > When I SELECT in the table, using the INT4 cle_prod value, PG
> > > doesn't find the tuple. When I SELECT using the VARCHAR(10)
> > > ref_prod value, PG finds the tuple, and show the right value for
> > > the cle_prod filed : the same as the one I SELECTed for...
> > >
> > > This sounds like the long negative integer values given in PSQL
> > > is not converted correctly while executing.
> > > Using a long positive integer value, all works like a charm...
> > >
> > > Below is the queries type sto sho what append.
> > > I'm using Postgres 6.5.2 from the RPMs.
> > >
> >
> > Could you try the follwoing patch ?
>
> Hiroshi, I don't see this in the main tree, nor do I see it having been
> requested for application. Should I apply it?
>
Recently I have often seen this kind of bug reports though I don't
know why * recently *.
This is the second time I sent the patch but I have seen no reply.
Anyway,this is clearly a bug.
I could commit it to current tree but couldn't commit to REL tree
because I don't maintain REL tree.
Moreover int42cmp/int24cmp seems to have similar bugs and
we had better check comparison functions again.
I'm happy if you could commit it to both trees.
Regards.
Hiroshi Inoue
Inoue@tpf.co.jp
We have the same problem with 64-bit integers - and it isn't just
psql - the same problem exists in jdbc.
Anyways, to fix it, we did the following:
'-11233232322233232323232321232'::int8
whenever we did any operation on the large field.
you may want to try
'-2057496808|'::int4
in the same manner.
At 01:37 PM 1/27/00 +0100, Nicolas Huillard wrote:
>I have a DB with is updated using MS Access. Primary keys are Int4 with
>default random values ("NuméroAuto" + "Aléatoire" in Access).
>The DB is migrated as-is in Postgres, with tbl_prod.cle_prod field
>containing values from -2057496808 to 2139583719.
>When I SELECT in the table, using the INT4 cle_prod value, PG doesn't find
>the tuple. When I SELECT using the VARCHAR(10) ref_prod value, PG finds the
>tuple, and show the right value for the cle_prod filed : the same as the one
>I SELECTed for...
>
>This sounds like the long negative integer values given in PSQL is not
>converted correctly while executing.
>Using a long positive integer value, all works like a charm...
>
>Below is the queries type sto sho what append.
>I'm using Postgres 6.5.2 from the RPMs.
>
>Nicolas Huillard
>
>
>
>
>gest=> select cle_prod, ref_prod from tbl_prod where ref_prod='4866VC';
> cle_prod|ref_prod
>-----------+--------
>-2057496808|4866VC
>(1 row)
>
>gest=> select cle_prod, ref_prod from tbl_prod where cle_prod = -2057496808;
>cle_prod|ref_prod
>--------+--------
>(0 rows)
>
>gest=> \d tbl_prod
>Table = tbl_prod
>+----------------------------------+----------------------------------+----
---+
>| Field | Type |
Length|
>+----------------------------------+----------------------------------+----
---+
>| cle_prod | int4 not null default nextval('" |
4 |
>| ref_prod | varchar() not null |
10 |
>...
>+----------------------------------+----------------------------------+----
---+
>Index: tbl_prod_pkey
>
>gest=> select version();
>version
>--------------------------------------------------------------
>PostgreSQL 6.5.2 on i486-pc-linux-gnu, compiled by gcc 2.7.2.3
>(1 row)
>
>gest=>
>
>
>************
------------
johnbr@incanta.net
I don't think that patch will work - Hiroshi whipped up that patch for me a week ago for a different problem - we have a table with duplicate primary keys, which seems to be an arithmetic overflow problem because the index key values can be both very large positive and very large negative numbers. At 11:37 AM 1/27/00 -0500, Bruce Momjian wrote: >> Could you try the follwoing patch ? > >Hiroshi, I don't see this in the main tree, nor do I see it having been >requested for application. Should I apply it? > >> >> Regards. >> >> Hiroshi Inoue ------------ johnbr@incanta.net
> -----Original Message----- > From: owner-pgsql-general@postgresql.org > [mailto:owner-pgsql-general@postgresql.org]On Behalf Of John Brothers > > I don't think that patch will work - Hiroshi whipped up that > patch for me a > week ago for a > different problem - we have a table with duplicate primary keys, which > seems to be > an arithmetic overflow problem because the index key values can be both > very large positive > and very large negative numbers. > What was the result on your environment after applying my patch ? My patch workded for your test case at least on my machine. Regards. Hiroshi Inoue Inoue@tpf.co.jp
John Brothers <johnbr@mindspring.com> writes:
> I don't think that patch will work - Hiroshi whipped up that patch for
> me a week ago for a different problem - we have a table with duplicate
> primary keys, which seems to be an arithmetic overflow problem because
> the index key values can be both very large positive and very large
> negative numbers.
Actually, if Nicolas' table contains both very large positive and very
large negative integers, then his index could be messed up pretty badly.
What Hiroshi saw (and I missed :-() was that btint4cmp can fail and
return a result of the wrong sign if the difference between two integers
overflows. Since index sorting depends critically on the assumption
that the comparator always returns consistent results (a < b and b < c
must imply a < c, but this can fail if a - c overflows), you could have
an out-of-order index. And then probes into the index could fail to
find items they should find ... which is exactly the complained-of
symptom.
Hiroshi neglected to mention that you'd probably need to drop and
recreate the index after applying the patch; if it's indeed out of
order, just patching the comparator bug isn't enough to fix it.
regards, tom lane
> Actually, if Nicolas' table contains both very large positive and very > large negative integers, then his index could be messed up pretty badly. > What Hiroshi saw (and I missed :-() was that btint4cmp can fail and > return a result of the wrong sign if the difference between two integers > overflows. Since index sorting depends critically on the assumption > that the comparator always returns consistent results (a < b and b < c > must imply a < c, but this can fail if a - c overflows), you could have > an out-of-order index. And then probes into the index could fail to > find items they should find ... which is exactly the complained-of > symptom. > > Hiroshi neglected to mention that you'd probably need to drop and > recreate the index after applying the patch; if it's indeed out of > order, just patching the comparator bug isn't enough to fix it. > Does Hiroshi's patch get applied? -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> Hiroshi neglected to mention that you'd probably need to drop and
>> recreate the index after applying the patch; if it's indeed out of
>> order, just patching the comparator bug isn't enough to fix it.
> Does Hiroshi's patch get applied?
Now that I understand the problem, yes, his patch is good as far as
it goes. There are probably other places with the same problem...
regards, tom lane
> -----Original Message----- > From: owner-pgsql-sql@postgreSQL.org [mailto:owner-pgsql-sql@postgreSQL. > org]On Behalf Of Tom Lane > > Hiroshi neglected to mention that you'd probably need to drop and > recreate the index after applying the patch; if it's indeed out of Oops I neglected to mention it,sorry. > order, just patching the comparator bug isn't enough to fix it. Yes,please report us the result. If the result is wrong,we have to think more about it. Regards. Hiroshi Inoue Inoue@tpf.co.jp
Applied.
[Charset iso-8859-1 unsupported, filtering to ASCII...]
> > -----Original Message-----
> > From: owner-pgsql-general@postgresql.org
> > [mailto:owner-pgsql-general@postgresql.org]On Behalf Of Nicolas Huillard
> >
> > I have a DB with is updated using MS Access. Primary keys are
> > Int4 with default random values ("Num_roAuto" + "Al_atoire" in Access).
> > The DB is migrated as-is in Postgres, with tbl_prod.cle_prod
> > field containing values from -2057496808 to 2139583719.
> > When I SELECT in the table, using the INT4 cle_prod value, PG
> > doesn't find the tuple. When I SELECT using the VARCHAR(10)
> > ref_prod value, PG finds the tuple, and show the right value for
> > the cle_prod filed : the same as the one I SELECTed for...
> >
> > This sounds like the long negative integer values given in PSQL
> > is not converted correctly while executing.
> > Using a long positive integer value, all works like a charm...
> >
> > Below is the queries type sto sho what append.
> > I'm using Postgres 6.5.2 from the RPMs.
> >
>
> Could you try the follwoing patch ?
>
> Regards.
>
> Hiroshi Inoue
>
> *** nbtcompare.c 2000/01/11 03:33:34 1.3
> --- nbtcompare.c 2000/01/19 05:42:06
> ***************
> *** 34,40 ****
> int32
> btint4cmp(int32 a, int32 b)
> {
> ! return a - b;
> }
>
> int32
> --- 34,45 ----
> int32
> btint4cmp(int32 a, int32 b)
> {
> ! if (a > b)
> ! return 1;
> ! else if (a == b)
> ! return 0;
> ! else
> ! return -1;
> }
>
> int32
>
>
>
> ************
>
>
--
Bruce Momjian | http://www.op.net/~candle
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> Bruce Momjian <pgman@candle.pha.pa.us> writes: > >> Hiroshi neglected to mention that you'd probably need to drop and > >> recreate the index after applying the patch; if it's indeed out of > >> order, just patching the comparator bug isn't enough to fix it. > > > Does Hiroshi's patch get applied? > > Now that I understand the problem, yes, his patch is good as far as > it goes. There are probably other places with the same problem... Applied. -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026