BUG #1712: JDBC column precision for bigint is 0

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #1712: JDBC column precision for bigint is 0

Gilles Dubochet

The following bug has been logged online:

Bug reference:      1712
Logged by:          Gilles Dubochet
Email address:      [hidden email]
PostgreSQL version: 8.0.2
Operating system:   Linux
Description:        JDBC column precision for bigint is 0
Details:

Whith the JDBC driver at least up to version 8.1dev-400, the result of the
getPrecision method of ResultSetMetaData on a bigint column is 0 instead of
the expected 19.

Code seems to be missing in
org.postgresql.jdbc2.AbstractJdbc2ResultSetMetadata after line 359 (in
source code 8.1dev-400).

Java code to reproduce problem:
import java.sql.*;
public class Main {
    public static void main(String[] args) throws Exception {
        Class.forName("org.postgresql.Driver");
        Connection con =
DriverManager.getConnection("jdbc:postgresql://localhost/pguser", "pguser",
"");
        Statement stmt = con.createStatement();
        stmt.execute("DROP TABLE mytable");
        stmt.execute("CREATE TABLE mytable (bi BIGINT)");
        System.out.println("We get "+
            stmt.executeQuery("select * from
mytable").getMetaData().getPrecision(1)+
        " but should get 19");
    }
}

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [hidden email] so that your
      message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: BUG #1712: JDBC column precision for bigint is 0

Oliver Jowett
Gilles Dubochet wrote:

> The following bug has been logged online:
>
> Bug reference:      1712
> Logged by:          Gilles Dubochet
> Email address:      [hidden email]
> PostgreSQL version: 8.0.2
> Operating system:   Linux
> Description:        JDBC column precision for bigint is 0
> Details:
>
> Whith the JDBC driver at least up to version 8.1dev-400, the result of the
> getPrecision method of ResultSetMetaData on a bigint column is 0 instead of
> the expected 19.

This has been reported before but I haven't got to fixing it yet. This
is partly because I haven't seen a good explanation of exactly what we
should be returning here -- what spec says we should return 19?

-O

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: BUG #1712: JDBC column precision for bigint is 0

Gilles Dubochet
>> Whith the JDBC driver at least up to version 8.1dev-400, the  
>> result of the
>> getPrecision method of ResultSetMetaData on a bigint column is 0  
>> instead of
>> the expected 19.
>>
>
> This has been reported before but I haven't got to fixing it yet. This
> is partly because I haven't seen a good explanation of exactly what we
> should be returning here -- what spec says we should return 19?
>

Well, in PostgreSQL, BIGINT uses 8 bytes (that is what the  
documentation says, at least).  Now, with 8 bytes, the range of  
numbers that can be represented is:

For 63 bits + 1 sign bit: [ (2^63/2)-1, -2^63/2] =  
[9223372036854775807, -9223372036854775808]
For 64 bits (unsigned): [2^64, 0] = [18446744073709551616, 0]

If you count the number of digits in these numbers, you'll notice  
that for the signed number, 19 decimal digits at most are required to  
represent it (if the sign comes for free, which seems assumed for  
other data types such as INT or SMALLINT).

For the unsigned number, 20 decimal digits are required. But as far  
as I understand the PostgreSQL reference, integers are always signed,  
except for serial data types, but where the range is that of a signed  
number anyway (since they need to be compatible with "normal" integer  
types to represent references).

This is why I believe 19 is the value the getPrecision method should  
return. I don't think there is some kind of standard reference that  
defines it, but it seems pretty clear what it should be really.

I hope this convinces you, if you still have any doubts, let me know:  
I'll do my best to appease them ;)

Sincerely,
Gilles Dubochet.


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: BUG #1712: JDBC column precision for bigint is 0

Kevin Grittner
In reply to this post by Gilles Dubochet
I have no official standing, but I've spent a lot of time reading the
JDBC spec and working with various implementations, and I concur.
Precision should be the maximum number of significant digits the column
is capable of returning.
 
-Kevin
 

>>> Gilles Dubochet <[hidden email]> 06/13/05 3:27 AM >>>
>> Whith the JDBC driver at least up to version 8.1dev-400, the  
>> result of the
>> getPrecision method of ResultSetMetaData on a bigint column is 0  
>> instead of
>> the expected 19.
>>
>
> This has been reported before but I haven't got to fixing it yet. This
> is partly because I haven't seen a good explanation of exactly what we
> should be returning here -- what spec says we should return 19?
>

Well, in PostgreSQL, BIGINT uses 8 bytes (that is what the  
documentation says, at least).  Now, with 8 bytes, the range of  
numbers that can be represented is:

For 63 bits + 1 sign bit: [ (2^63/2)-1, -2^63/2] =  
[9223372036854775807, -9223372036854775808]
For 64 bits (unsigned): [2^64, 0] = [18446744073709551616, 0]

If you count the number of digits in these numbers, you'll notice  
that for the signed number, 19 decimal digits at most are required to  
represent it (if the sign comes for free, which seems assumed for  
other data types such as INT or SMALLINT).

For the unsigned number, 20 decimal digits are required. But as far  
as I understand the PostgreSQL reference, integers are always signed,  
except for serial data types, but where the range is that of a signed  
number anyway (since they need to be compatible with "normal" integer  
types to represent references).

This is why I believe 19 is the value the getPrecision method should  
return. I don't think there is some kind of standard reference that  
defines it, but it seems pretty clear what it should be really.


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match
Reply | Threaded
Open this post in threaded view
|

Re: BUG #1712: JDBC column precision for bigint is 0

Oliver Jowett
In reply to this post by Gilles Dubochet
Gilles Dubochet wrote:

>>> Whith the JDBC driver at least up to version 8.1dev-400, the  result
>>> of the
>>> getPrecision method of ResultSetMetaData on a bigint column is 0
>>> instead of
>>> the expected 19.
>>>
>>
>> This has been reported before but I haven't got to fixing it yet. This
>> is partly because I haven't seen a good explanation of exactly what we
>> should be returning here -- what spec says we should return 19?

> If you count the number of digits in these numbers, you'll notice  that
> for the signed number, 19 decimal digits at most are required to
> represent it (if the sign comes for free, which seems assumed for  other
> data types such as INT or SMALLINT). [...]

> This is why I believe 19 is the value the getPrecision method should
> return. I don't think there is some kind of standard reference that
> defines it, but it seems pretty clear what it should be really.

The problem I have is how we decide that "precision" means "number of
digits, ignoring sign".

Actually, I just dug through the JDBC javadoc, and noticed that
ResultSetMetaData.getPrecision() does talk about the number of decimal
digits; but in all the other places it's used (such as DatabaseMetaData)
it's not described.

For numeric types using number of decimal digits seems reasonable since
it's consistent with the NUMERIC type's precision/scale information.

Previous reports of this also wanted to provide precision for dates and
times according to the number of digits in them, which seems much more
dubious.

I guess it's another case of the JDBC specification being woefully
underspecified :(

-O

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: BUG #1712: JDBC column precision for bigint is 0

Kevin Grittner
In reply to this post by Gilles Dubochet
I agree on all counts.  The only way that I would extend the description
of "the number of decimal digits" is that I think it's fairly clear that
for floating point numbers it should be "the number of significant
digits" since the fractional part is often only an approximation of any
decimal representation, and its representation could go on forever.

java.util.Date and its java.sql subclasses have well defined formats.
No elaboration with the precision is required, and I've never seen any
hint that it is intended to apply.

As someone tasked with keeping a software framework functional and
efficient with multiple database products through standard JDBC, I often
find myself dealing with different vendor interpretations of the softer
parts of the JDBC spec.  Each vendor is understandably reluctant to
switch from a plausible interpretation they've already implemented to
someone else's interpretation.

-Kevin

>>> Oliver Jowett <[hidden email]> 06/13/05 4:57 PM >>>

Actually, I just dug through the JDBC javadoc, and noticed that
ResultSetMetaData.getPrecision() does talk about the number of decimal
digits; but in all the other places it's used (such as DatabaseMetaData)
it's not described.

For numeric types using number of decimal digits seems reasonable since
it's consistent with the NUMERIC type's precision/scale information.

Previous reports of this also wanted to provide precision for dates and
times according to the number of digits in them, which seems much more
dubious.

I guess it's another case of the JDBC specification being woefully
underspecified :(


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [hidden email]