Bug 74172 - Embedded Firebird : data type DECIMAL/NUMERIC has been forgotten?
Summary: Embedded Firebird : data type DECIMAL/NUMERIC has been forgotten?
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.2.0.0.beta1
Hardware: All All
: medium normal
Assignee: Tamas Bunth
URL:
Whiteboard: target:5.3.0
Keywords:
Depends on:
Blocks: Database-Firebird-Default
  Show dependency treegraph
 
Reported: 2014-01-29 08:59 UTC by ribotb
Modified: 2016-12-23 17:38 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description ribotb 2014-01-29 08:59:47 UTC
Hello,

In the list of data types, when we want to create a field in a table  with the table editor, there is not DECIMAL or NUMERIC (data types that exist in Firebird SQL Server 2.5).

Is this an omission or is this intentional?

Regards,
Bernard Ribot
Comment 1 Robert Großkopf 2014-01-29 14:51:51 UTC
Can confirm this for OpenSUSE 12.3 64bit and L= 4.2.0.4.
No possibility to save a Decimal value.
Comment 2 ribotb 2014-04-25 07:39:49 UTC
Adding as blocker for making Firebird the default
Comment 3 Julien Nabet 2014-05-04 07:16:18 UTC
On pc Debian x86-64 with master sources updated yesterday or 4.2 sources updated some days ago, I don't reproduce this (or perhaps missed the point).

For the test, could you give a try to 4.2 daily build (see http://dev-builds.libreoffice.org/daily/libreoffice-4-2/) or master sources (future 4.3.0) daily build (see http://dev-builds.libreoffice.org/daily/master/)
Comment 4 Julien Nabet 2014-05-04 07:30:12 UTC
Forget my last comment.
Comment 5 ribotb 2014-05-04 08:20:40 UTC
Hi,
I forgot ;-))

I verified with version 4.3.0.0.alpha1+
Build ID: 20778b037c688759a4dc46acb8aeb66c9d1290cc
TinderBox: Win-x86@39, Branch:master, Time: 2014-05-03_23:16:12

Data type DECIMAL/NUMERIC is not present.

Regards,
Bernard
Comment 6 Julien Nabet 2014-06-01 15:13:16 UTC
Code pointer:
http://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/firebird/DatabaseMetaData.cxx#947

Other I suppose

Doc pointers:
1) http://www.firebirdfaq.org/faq340/
"
In SQL Standard, NUMERIC is a more strict datatype which should enforce the declared precision, while DECIMAL can accept more numbers than declared. However, in Firebird, both types accept more numbers (i.e. they behave like DECIMAL).
"
2) http://www.firebirdsql.org/en/sql-conformance/
"
NUMERIC is not compliant and represents almost the same semantics as DECIMAL
"
3) http://www.firebirdsql.org/en/firebird-technical-specifications/
"
Name: Numeric (precision, scale)
Size: Variable (16, 32, or 64 bits)
Range/Precision: specifies exactly precision digits of precision
Description: Example: Numeric(10,3) holds numbers accurately in the following format: ppppppp.sss

Name: Decimal (precision, scale)
Size: Variable (16, 32, or 64 bits)
Range/Precision: specifies at least precision digits of precision
Description: Example: Numeric(10,3) holds numbers accurately in the following format: ppppppp.sss
"
Comment 7 Julien Nabet 2014-06-01 15:31:06 UTC
Ref for data type:
offapi/com/sun/star/sdbc/DataType.idl
Comment 8 Julien Nabet 2014-06-01 15:52:20 UTC
I tried to begin to add NUMERIC and DECIMAL but I got this:
diff --git a/connectivity/source/drivers/firebird/DatabaseMetaData.cxx b/connectivity/source/drivers/firebird/DatabaseMetaData.cxx
index bff558c..6850863 100644
--- a/connectivity/source/drivers/firebird/DatabaseMetaData.cxx
+++ b/connectivity/source/drivers/firebird/DatabaseMetaData.cxx
@@ -936,6 +936,20 @@ uno::Reference< XResultSet > SAL_CALL ODatabaseMetaData::getTypeInfo()
                 sal_Int16(ColumnSearch::FULL)); // Searchable
             aRow[12] = new ORowSetValueDecorator(sal_True); // Autoincrement
         }
+        // SQL_NUMERIC
+        aRow[1] = new ORowSetValueDecorator(OUString("NUMERIC"));
+        aRow[2] = new ORowSetValueDecorator(getColumnTypeFromFBType(SQL_NUMERIC));
+        aRow[3] = new ORowSetValueDecorator(sal_Int16(15)); // Precision
+        aRow[14] = new ORowSetValueDecorator(sal_Int16(1)); // Minimum scale
+        aRow[15] = new ORowSetValueDecorator(sal_Int16(15)); // Max scale
+        aResults.push_back(aRow);
+        // SQL_DECIMAL
+        aRow[1] = new ORowSetValueDecorator(OUString("DECIMAL"));
+        aRow[2] = new ORowSetValueDecorator(getColumnTypeFromFBType(SQL_DECIMAL));
+        aRow[3] = new ORowSetValueDecorator(sal_Int16(15)); // Precision
+        aRow[14] = new ORowSetValueDecorator(sal_Int16(1)); // Minimum scale
+        aRow[15] = new ORowSetValueDecorator(sal_Int16(15)); // Max scale
+        aResults.push_back(aRow);

But I got this:
/home/julien/compile-libreoffice/libreoffice/connectivity/source/drivers/firebird/DatabaseMetaData.cxx: In member function ‘virtual com::sun::star::uno::Reference<com::sun::star::sdbc::XResultSet> connectivity::firebird::ODatabaseMetaData::getTypeInfo()’:
/home/julien/compile-libreoffice/libreoffice/connectivity/source/drivers/firebird/DatabaseMetaData.cxx:941:69: error: ‘SQL_NUMERIC’ was not declared in this scope
         aRow[2] = new ORowSetValueDecorator(getColumnTypeFromFBType(SQL_NUMERIC));
                                                                     ^
/home/julien/compile-libreoffice/libreoffice/connectivity/source/drivers/firebird/DatabaseMetaData.cxx:948:69: error: ‘SQL_DECIMAL’ was not declared in this scope
         aRow[2] = new ORowSetValueDecorator(getColumnTypeFromFBType(SQL_DECIMAL));
                                                                     ^
I've got no idea where these must be declared, any idea?
Comment 9 Alex Thurgood 2015-01-03 17:39:51 UTC Comment hidden (no-value)
Comment 10 Julien Nabet 2015-01-11 22:00:14 UTC
I noticed that DECIMAL and NUMERIC were in fact subtypes.
Here's part of code from firebird (isql/isql.h=
// Integral subtypes

const int MAX_INTSUBTYPES       = 2;
static const SCHAR* Integral_subtypes[] = {
        "UNKNOWN",                               // Defined type, NTX: keyword
        "NUMERIC",                               // NUMERIC, NTX: keyword
        "DECIMAL"                                // DECIMAL, NTX: keyword
};

isql/extract.epp and isql/show.epp show that they're subtypes of SMALLINT, INTEGER and BIGINT, example from first file :
    438 if (isqlGlob.major_ods >= ODS_VERSION10)
    439 {                                
    440     // Handle Integral subtypes NUMERIC and DECIMAL
    441     if ((FLD.RDB$FIELD_TYPE == SMALLINT) ||
    442        (FLD.RDB$FIELD_TYPE == INTEGER)  ||              
    443        (FLD.RDB$FIELD_TYPE == BIGINT))                  
    444     {                                    
    445        FOR FLD1 IN RDB$FIELDS WITH                      
    446         FLD1.RDB$FIELD_NAME EQ FLD.RDB$FIELD_NAME               
    447          /* We are ODS >= 10 and could be any Dialect */
    448          if (!FLD1.RDB$FIELD_PRECISION.NULL)                    
    449          {                                                      
    450              /* We are Dialect >=3 since FIELD_PRECISION is non-NULL */
    451              if (FLD1.RDB$FIELD_SUB_TYPE > 0 &&                         
    452               FLD1.RDB$FIELD_SUB_TYPE <= MAX_INTSUBTYPES)                       
    453              {
    454                 sprintf (Print_buffer, "%s(%d, %d)",                     
    455                   Integral_subtypes[FLD1.RDB$FIELD_SUB_TYPE],                                    
    456                   FLD1.RDB$FIELD_PRECISION,                                                      
    457                   -FLD1.RDB$FIELD_SCALE);                                                        
    458                 precision_known = true;
    459              }
    460          }
    461       END_FOR
    462       ON_ERROR
    463         ISQL_errmsg (isc_status);
    464         return ps_ERR;
    465       END_ERROR;
    466     }
    467 }
Comment 11 QA Administrators 2016-01-17 20:01:37 UTC Comment hidden (obsolete)
Comment 12 Robert Großkopf 2016-01-18 16:11:42 UTC
Bug still exists unde LO 5.1.0.2, OpenSUSE Leap 42.1, 64bit rpm Linux.
Comment 13 ribotb 2016-01-18 17:24:33 UTC
Hi,

Bug always present with :
Version: 5.1.0.2
Build ID: ecd3574d51754b043f865cf5bafee286d24db7cc
CPU Threads: 2; OS Version: Windows 6.1; UI Render: default; 
Locale : fr-FR (fr_FR)
on Win7/x86

Data typrs present in Base :

Biglnt [ BIGINT ]
Texte (fixe) [CHAR]
Integer [ INTEGER ]
Small Integer [ SMALLIrJT ]
Float[FLOAT]
Double [ DOUBLE PRECISION ]
Texte [ VARCHAR ]
Date [DATE]
Heure [TIME]
Date/Heure [TIMESTAMP ]
BLOB [BLOB]

Bernard
Comment 14 Commit Notification 2016-11-10 09:40:17 UTC
Wastack committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=21cc1826c758bb71bb0cfa055b3bb51a38bc2acb

WiP tdf#74172 use DECIMAL and NUMERIC data types

It will be available in 5.3.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 15 ribotb 2016-11-12 08:38:30 UTC
Hi,

When I enter a value in a field with type DECIMAL LO crashes. But It seems that it crashes randomly. I am looking for the steps to reproduce.

Bernard

Version: 5.3.0.0.alpha1+
Build ID: c64b8ffe851d3320a15071fa4bc4b0f12e9939d5
CPU Threads: 2; OS Version: Windows 6.1; UI Render: default; Layout Engine: new; 
TinderBox: Win-x86@39, Branch:master, Time: 2016-11-11_01:34:42
Locale: fr-FR (fr_FR); Calc: group
Comment 16 Commit Notification 2016-11-21 16:54:53 UTC
Wastack committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=a2686cc717187637c47f9c748b115a46bf16eced

tdf#74172 ensure prepared while setting numeric

It will be available in 5.3.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 17 ribotb 2016-11-27 18:27:11 UTC
No crash with :

Version: 5.4.0.0.alpha0+
Build ID: 719f7cb94ce783349fb1cf366a78edd9996d3e37
CPU Threads: 2; OS Version: Windows 6.1; UI Render: default; 
TinderBox: Win-x86@42, Branch:master, Time: 2016-11-27_03:58:17
Locale: fr-FR (fr_FR); Calc: group
Comment 18 Robert Großkopf 2016-11-27 19:41:13 UTC
No problem to create a DECIMAL/NUMERIC field. But if I insert any value there (with and without decimal places) the whole LO crashes immediately.

Tested with
Version: 5.3.0.0.beta1
Build-ID: 690f553ecb3efd19143acbf01f3af4e289e94536
CPU-Threads: 4; BS-Version: Linux 4.1; UI-Render: Standard; VCL: kde4; Layout-Engine: neu; 
Gebietsschema: de-DE (de_DE.UTF-8); Calc: group

on OpenSUSE 42.1 64bit rpm Linux

Should I open a new bug for this?
Comment 19 Lionel Elie Mamane 2016-11-28 10:08:08 UTC
(In reply to robert from comment #18)
> No problem to create a DECIMAL/NUMERIC field. But if I insert any value
> there (with and without decimal places) the whole LO crashes immediately.

> Should I open a new bug for this?

Yes, please, especially if it can be reproduced with recent 5.4.0.alpha (nightly) also. Put myself and Tamás/Wastack in CC of the new bug report.
Comment 20 Robert Großkopf 2016-11-28 15:58:39 UTC
(In reply to Lionel Elie Mamane from comment #19)
> (In reply to robert from comment #18)
> > No problem to create a DECIMAL/NUMERIC field. But if I insert any value
> > there (with and without decimal places) the whole LO crashes immediately.
> 
> > Should I open a new bug for this?
> 
> Yes, please, especially if it can be reproduced with recent 5.4.0.alpha
> (nightly) also. Put myself and Tamás/Wastack in CC of the new bug report.

Done. The problem aren't the Decimal and Numeric fields. Every update for an existing row is impossible without crash.
https://bugs.documentfoundation.org/show_bug.cgi?id=104227