Issue Details (XML | Word | Printable)

Key: JDBC-450
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Mark Rotteveel
Reporter: Honza Hubeny
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Jaybird JCA/JDBC Driver

Wrong FBResultSetMetaData.getPrecision() on "computed by" columns

Created: 31/Aug/16 07:07 AM   Updated: 18/Dec/16 02:58 PM
Component/s: JDBC driver
Affects Version/s: Jaybird 2.2.11, Jaybird 3.0.0-alpha-1
Fix Version/s: Jaybird 2.2.12, Jaybird 3.0.0

Environment: Firebird 2.5.5 Ubuntu 16.04. 64 bit Classic server


 Description  « Hide
Let's have table in firebird database

create table TEST (
    A NUMERIC(15,3)
    B NUMERIC(15,3)
    C computed by (A+B)
)

than when wi have Java code:
Connection conn;
...
PreparedStatement statement = conn.prepareStatement ("select * from TEST");
ResultSet retVal = statement.executeQuery();
FBResultSetMetaData metaData = (FBResultSetMetaData) sqlResultSet.getMetaData();
int i = index of C column;
//This column returns ZERO
metaData.getPrecision(i);



 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Mark Rotteveel added a comment - 31/Aug/16 08:40 AM
What is the dialect of your database?

Honza Hubeny added a comment - 31/Aug/16 10:12 AM
We have following parameters in the connection string:

dialect=3&encoding=UTF8&defaultHoldable=true&columnLabelForName=true

Therefore sql dialect is 3.

Further comment to java code:
for Columns A and B
metaData.getPrecision(i); // returns 15
metaData.getScale(i); // returns 3

for column C
metaData.getPrecision(i); // returns 0
metaData.getScale(i); // returns 3


Mark Rotteveel added a comment - 01/Sep/16 06:48 PM - edited
The caused by a bug in Firebird 2.5, which has been fixed in Firebird 3. The problem is that Firebird 2.5 reports the field as precision 0; you can see this for yourself in RDB$FIELDS, eg using: select * from RDB$FIELDS where RDB$COMPUTED_SOURCE = '(A+B)'.

I can probably work around this problem by 'estimating' the precision if it is reported as zero; the estimated precision would be 19 (and not 18).

Honza Hubeny added a comment - 02/Sep/16 05:57 AM
We made a similar workaround directly in our Java application too. I must appologize that I did not explore this bug in deep before reporting it. I did not check whether it is correct in firebird system tables.

Mark Rotteveel added a comment - 18/Sep/16 08:05 AM
Fixed for 2.2.12 and 3.0. The estimate for 2.2.12 will be 19, for 3.0 it will be 18 (which is more correct).

The primary reason to use 19 in 2.2.12 is that we already estimated the precision in some cases and that used 19; changing it to 18 would have been a (potentially) breaking change which I didn't want to do in a point release. This estimation had already been revised to 18 in 3.0.