Обсуждение: FW: [JDBC] BIGINT vs Java's long
This came into the jdbc list Apparently bigint is not really 8 bytes??? I test this out with psql test=# create table testbigint (id serial, fp0 int8); NOTICE: CREATE TABLE will create implicit sequence 'testbigint_id_seq' for SERIAL column 'testbigint.id' NOTICE: CREATE TABLE/UNIQUE will create implicit index 'testbigint_id_key' for table 'testbigint' CREATE test=# insert into testbigint (fp0) values (1); INSERT 333698 1 test=# update testbigint set fp0 = -9223372036854775808 where id = 1; ERROR: int8 value out of range: "-9223372036854775808" Dave -----Original Message----- From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Dav Coleman Sent: August 7, 2001 11:37 AM To: pgsql-jdbc@postgresql.org Subject: [JDBC] BIGINT vs Java's long According to the Java Language Specification, http://java.sun.com/docs/books/jls/second_edition/html/typesValues.doc.h tml#9151 "For long, from -9223372036854775808 to 9223372036854775807, inclusive" Indeed, I have java code which generate random long's and println's them, and I end up with values equal to -9223372036854775808. I had those println's redirected to a .sql file which I ran against psql to update some bigint columns, but I got ERROR: int8 value out of range: "-9223372036854775808" Apparently bigint's don't like that value? Well confused, since 8 bytes should be 8 freaking bytes, I turned to JDBC. That's when things got weird, first I tried declaring a long variable with that value, and got a compilere error (Integer to large) or something like that. So I declared "long myBigint = Long.MIN_VALUE" and that compiled, but when I tried using that value in a Statement.execute() I got the exact same error. Anyone know what's going on? Here's the test code, using jdbc7.0-1.2.jar: import java.sql.*; public class testPGSQLbigint { public static void main( String[] args ) { try { Class.forName("org.postgresql.Driver"); } catch (java.lang.ClassNotFoundExceptione) { System.out.println( e ); } Connection db=null; String url = "jdbc:postgresql:abinitio2"; try { db = DriverManager.getConnection(url,"dav",""); } catch ( SQLException e ) { System.err.println( e ); } // the following gives a compiler error //long bigint = -9223372036854775808; long bigint = Long.MIN_VALUE; String sql_ = "update chembase set fp0 = "+bigint+" where id = 27948;"; System.out.println(sql_); try { Statement st = db.createStatement(); st.execute( sql_ ); st.close(); } catch( SQLException e ) { System.err.println( e ); } } } output: $ java -classpath /opt/java/jars/jdbc7.0-1.2.jar:. "testPGSQLbigint" update chembase set fp0 = -9223372036854775808 where id = 27948; java.sql.SQLException: ERROR: int8 value out of range: "-9223372036854775808" note this runs the same in linux and win2k (using Sun's SDK) -- Dav Coleman http://www.danger-island.com/dav/ ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
On Tue, 7 Aug 2001, Dave Cramer wrote: > This came into the jdbc list > > Apparently bigint is not really 8 bytes??? > > I test this out with psql > > test=# create table testbigint (id serial, fp0 int8); > NOTICE: CREATE TABLE will create implicit sequence 'testbigint_id_seq' > for SERIAL column 'testbigint.id' > NOTICE: CREATE TABLE/UNIQUE will create implicit index > 'testbigint_id_key' for table 'testbigint' > CREATE > test=# insert into testbigint (fp0) values (1); > INSERT 333698 1 > test=# update testbigint set fp0 = -9223372036854775808 where id = 1; > ERROR: int8 value out of range: "-9223372036854775808" Yes, it's failing on precisely the minimum value. It appears that the code that does this sets the sign and then makes the number and applies the sign at the end which would be wrong in this case (as it overflows on 9223372036854775808) I don't think my patch against recent sources would apply cleanly to older ones, and I didn't run the regression against it, but it seemed to work, and is only a two line change in current source.
Dave Cramer writes:
> Apparently bigint is not really 8 bytes???
It's sort of 7.999 bytes.
> test=# update testbigint set fp0 = -9223372036854775808 where id = 1;
> ERROR: int8 value out of range: "-9223372036854775808"
This is a bug in the int8 value parser. While it reads the string it
always accumulates the value as positive and then tags the sign on.
Since +9223372036854775808 doesn't fit you get this error.
ISTM that this can be fixed by accumulating toward the negative end and
taking some special care around the boundaries, like this patch:
Index: int8.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/utils/adt/int8.c,v
retrieving revision 1.30
diff -u -r1.30 int8.c
--- int8.c 2001/06/07 00:09:29 1.30
+++ int8.c 2001/08/07 19:26:35
@@ -77,16 +77,21 @@ elog(ERROR, "Bad int8 external representation \"%s\"", str); while (*ptr &&
isdigit((unsignedchar) *ptr)) /* process digits */ {
- int64 newtmp = tmp * 10 + (*ptr++ - '0');
+ /* We accumulate the value towards the negative end to allow
+ the minimum value to fit it. */
+ int64 newtmp = tmp * 10 - (*ptr++ - '0');
- if ((newtmp / 10) != tmp) /* overflow? */
+ /* overflow? */
+ if ((newtmp / 10) != tmp
+ /* This number only fits with a negative sign. */
+ || (newtmp == -9223372036854775808 && sign > 0)) elog(ERROR, "int8 value out of range: \"%s\"",
str); tmp = newtmp; } if (*ptr) /* trailing junk? */ elog(ERROR, "Bad int8
externalrepresentation \"%s\"", str);
- result = (sign < 0) ? -tmp : tmp;
+ result = (sign > 0) ? -tmp : tmp;
PG_RETURN_INT64(result);}
===end
--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
On Tue, 7 Aug 2001, Tom Lane wrote: > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: > > I don't think my patch against recent sources would apply cleanly to > > older ones, and I didn't run the regression against it, but it seemed > > to work, and is only a two line change in current source. > > This patch needs more work. You are assuming that integer division on > negative numbers works the same everywhere, which it most definitely > does not (the direction of truncation was unspecified until C99). > The overflow check will fail on platforms where negative results > truncate towards minus infinity. So we need a different way of checking > for overflow. > > Right off the bat I'm not coming up with an implementation that's both > portable and able to accept INT64_MIN, but this has got to be a solved > problem. Look around, maybe in the GNU or BSD C libraries... Actually, that wasn't a suggested patch for real inclusion (I should have mentioned that) but instead for the user in question to try. I'll look and get something complete for this. :)
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> I don't think my patch against recent sources would apply cleanly to
> older ones, and I didn't run the regression against it, but it seemed
> to work, and is only a two line change in current source.
This patch needs more work. You are assuming that integer division on
negative numbers works the same everywhere, which it most definitely
does not (the direction of truncation was unspecified until C99).
The overflow check will fail on platforms where negative results
truncate towards minus infinity. So we need a different way of checking
for overflow.
Right off the bat I'm not coming up with an implementation that's both
portable and able to accept INT64_MIN, but this has got to be a solved
problem. Look around, maybe in the GNU or BSD C libraries...
regards, tom lane