Обсуждение: BUG #1712: JDBC column precision for bigint is 0
The following bug has been logged online:
Bug reference: 1712
Logged by: Gilles Dubochet
Email address: dubochet@urbanet.ch
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");
}
}
Gilles Dubochet wrote: > The following bug has been logged online: > > Bug reference: 1712 > Logged by: Gilles Dubochet > Email address: dubochet@urbanet.ch > 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
>> 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.
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 <dubochet@urbanet.ch> 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.
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
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 <oliver@opencloud.com> 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 :(