Bug 67179 - EDITING: JDBC - Firebird: Integer-fields formatted with wrong field properties
Summary: EDITING: JDBC - Firebird: Integer-fields formatted with wrong field properties
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
(earliest affected) rc
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
Depends on:
Reported: 2013-07-22 15:57 UTC by robert
Modified: 2013-11-09 01:27 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:

Integer field shows currency-formatting and length of 32 (29.44 KB, image/png)
2013-07-22 15:57 UTC, robert
Integer field after saving: lenth is changed to 10. (27.05 KB, image/png)
2013-07-22 15:58 UTC, robert

Note You need to log in before you can comment on or make changes to this bug.
Description robert 2013-07-22 15:57:47 UTC
Created attachment 82825 [details]
Integer field shows currency-formatting and length of 32

When I create a table in Firebird, connecting with JDBC to LO 4.*, and chose some numeric fields, they show wrong field properties.
In the attachment you see an integer-field. The length of this field is shown during editing with 32 characters. The format shows a German currency-field with two digits. I could change the currency-format to a numeric format without digits, but could not change the length.
When I reopen the table I couldn't edit anything of the old fields. But have a look at the length, which is displayed now: 10.
This formatting-problem appears with the following field-types:
Big_Int, Integer, Small Integer, Number and Decimal. It doesn't appear with Float and Double.

I have tested this with OpenSUSE 12.3 64bit rpm, openjdk 1.7.0_21 and Jaybird-2.2.3JDK_1.7 (JDBC-Connector to Firebird)
Comment 1 robert 2013-07-22 15:58:54 UTC
Created attachment 82826 [details]
Integer field after saving: lenth is changed to 10.
Comment 2 Julien Nabet 2013-07-22 17:25:36 UTC
Andrzej: one for you?
Comment 3 Andrzej Hunt 2013-07-22 18:31:16 UTC
Hi -- I wouldn't know much about this unfortunately (I'm working on the native firebird driver which avoids going through JDBC -- which will eventually work with external and remote dbs).

However assuming this is jaybird specific and doesn't happen with other jdbc databases then it could be a jaybird specific probem, otherwise it'll probably be a jdbc driver problem. Probably worth testing there to determine where exactly the problem lies -- testing 4.0 on openSuse 12.2 64 bit the embedded hsqldb (which uses jdbc) seems to work fine, i.e. field length starts at 5, 10, 100 etc. (depending on type) and stays such after closing and reopening.
Comment 4 Popa Adrian Marius 2013-07-23 14:21:41 UTC
I have asked on Firebird Java mailing list about this issue

and here is the thread 

Comment 5 Mark Rotteveel 2013-07-23 18:51:02 UTC
I am the developer of Jaybird (the JDBC driver for Firebird).

The actual bugs is in the creation stage where the length is displayed as 32 (or 64 for BIGINT). The 'problem' is that Jaybird 2.2.3 (and earlier) in DatabaseMetaData.getTypeInfo() returns the binary precision (ie 32 base 2) of the datatype, while the getColumns() method declares it in decimal (ie 10 base 10) precision.

See section 4.4.2 of SQL:2011 Foundation:
"An exact numeric type has a precision P and a scale S. P is a positive integer that determines the number of significant digits in a particular radix R, where R is either 2 or 10."

The value of 32 comes from getTypeInfo()[1] (column PRECISION, combined with column NUM_PREC_RADIX). Jaybird 2.2.3 returns 32 base 2 for integer (64 base 2 for BIGINT and 16 base 2 for SMALLINT.

The value of 10 shown after creation comes from DatabaseMetaData.getColumns()[1] (column COLUMN_SIZE, combined with column NUM_PREC_RADIX). Jaybird returns 10 here if the column is an integer, and 19 for a bigint and 5 for a smallint (all with base 10 as specified in NUM_PREC_RADIX).

This has been changed for the upcoming 2.3 release of Jaybird, so getTypeInfo() uses the same base as getColumns (ie base 10).

On the Libre Office side, if this is intended to always display the number of digits (characters), it can be achieved by transforming the values using:

(with log = 10log, power = power function, ceil = ceiling function). 

For 32 base 2 this will yield 20 for bigint, which is not entirely correct (it should be 19 for the signed bigint that Firebird has), but it is close enough for most purposes. For numbers in base 10 this will simply return the original value.

Changing this will yield another bug with current versions of Jaybird, as getTypeInfo() incorrectly uses radix 2 for other lengths as well (those lengths are specified in base 10, but NUM_PREC_RADIX reports 2).

 [1] http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getTypeInfo()
 [2] http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getColumns(java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String)
Comment 6 Mark Rotteveel 2013-07-23 19:06:44 UTC
I scheduled a change in Jaybird to fix this for Jaybird 2.2.4 as well: http://tracker.firebirdsql.org/browse/JDBC-317
As it will take a while before 2.3 is released.
Comment 7 Alex Thurgood 2013-09-06 14:21:46 UTC
Note that the formatting bug, i.e. where "integer" is set to currency format described by Robert also appears in the native embedded firebird driver, at least on Mac OSX in my latest master build.

Comment 8 Andrzej Hunt 2013-09-06 15:42:11 UTC
(In reply to comment #7)
> Note that the formatting bug, i.e. where "integer" is set to currency format
> described by Robert also appears in the native embedded firebird driver, at
> least on Mac OSX in my latest master build.
> Alex

I too noticed that for some reason the native driver likes to use currency a lot.

That is in fact very weird, given that isCurrency in ResultSetMetaData is currently hard-coded to return false (not yet implemented), so this shouldn't ever be a currency. I suspect there is some underlying weirdness within Base itself which I'll try and investigate.
Comment 9 Alex Thurgood 2013-11-05 10:54:06 UTC
Setting this as NOTOURBUG in view of Mark's helpful comments. I have opened bug 71256 for the currency formatting issue.

Comment 10 Lionel Elie Mamane 2013-11-05 18:17:41 UTC
I don't quite agree with the "NOTOURBUG" assessment. Essentially, what Mark is saying is that LibreOffice blindly assumes NUM_PREC_RADIX == 10, but we should check its value and react accordingly.

Some of the treatment of NUM_PREC_RADIX was wrongly removed in a series of "make code warning-free" commits back in ... 2006. I reinstated it, but that was just storing the value in LibreOffice's internal datastructures, there is still no use of it made... EasyHack maybe?