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.ClassNotFoundException e) {
            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



Tue, 7 Aug 2001 13:19:51 -0400
"Dave Cramer" <dave@fastcrypt.com>
FW: [JDBC] BIGINT vs Java's long
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.
Tue, 7 Aug 2001 11:40:07 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: FW: [JDBC] BIGINT vs Java's long
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((unsigned char) *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 external representation \"%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


Tue, 7 Aug 2001 21:34:02 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: FW: [JDBC] BIGINT vs Java's long
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

Tue, 07 Aug 2001 15:44:03 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: FW: [JDBC] BIGINT vs Java's long 
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. :)



Tue, 7 Aug 2001 13:10:20 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: FW: [JDBC] BIGINT vs Java's long 