Bug 71251

Summary: EDITING - Embedded Firebird - Creating table from default choices via wizard fails
Product: LibreOffice Reporter: Alex Thurgood <iplaw67>
Component: DatabaseAssignee: Not Assigned <libreoffice-bugs>
Status: NEW --- QA Contact:
Severity: blocker    
Priority: high CC: andrzej, iplaw67, lionel, ribotb, robert, suokunlong
Version: 4.2.0.0.alpha0+ Master   
Hardware: x86 (IA32)   
OS: All   
Whiteboard:
i915 platform: i915 features:
Bug Depends on:    
Bug Blocks: 51780    

Description Alex Thurgood 2013-11-05 10:14:40 UTC
How to reproduce :

1) Start LO Base
2) Choose to create a new embedded Firebird database.
3) Start the table creation wizard
4) Choose Invoices from the Business category of proposed default tables
5) Click Next at each step of the wizard, accepting defaults for field types.
6) At the Set Primary Key dialog, select Create Primary Key - Automatically Add Primary Key - Autovalue
7) Click on Next
8) Click on Finish (accept the defaults)

ERROR : table creation will not complete, and displays following error message :

Error code: 1

firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -842
*Short integer expected
caused by
'isc_dsql_prepare'



Alex
Comment 1 Alex Thurgood 2013-11-05 10:17:19 UTC
This is a blocker if the user can't create via the UI from the default table choices provided by the application. The SQL error message is somewhat too sybilline for a normal user who will have no hope of understanding what is going wrong.


Alex
Comment 2 Alex Thurgood 2013-11-05 10:20:54 UTC
The problem still occurs even if the user does not choose to set a primary key at step 6 outlined above, however, the error message is different :

Error code: 1

firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -204
*Data type unknown
*Implementation limit exceeded
*COLUMN Notes
caused by
'isc_dsql_prepare'


Alex
Comment 3 Alex Thurgood 2013-11-05 10:32:39 UTC
The problem occurs with the majority of the default tables proposed by LO in the Business category.

If you choose the Transactions table, the wizard doesn't even get to the end, try clicking on "Finish" and nothing happens, clicking on Next several times eventually gets you stuck on the Primary Key definition page of the dialog, and then you can go no further - there seems to be some focus issues here with the wizard and event firing/handling. I guess that this particular behaviour is a separate issue though.


Alex
Comment 4 robert 2013-11-08 18:03:02 UTC
I could confirm this behavior. Seems to be a problem with the datatypes.

*Short integer expected : There is no "Short inter" as type of a field in Base and isn't in Firebird. Could be Bug67179 or Bug71256.
*Implementation limit exceeded
*COLUMN Notes : There is defined a length of 32767 chars, but Firebird sets a limit to 32767 bytes. UTF-8, for example, allows 4byte for one character. So this must be limited to 8191 characters, when using UTF-8. For more content Firebird uses BLOB (BLOB SUB_TYPE 1).

Don't know if we should report a bug for every fieldtype, which is chosen wrong.
Comment 5 robert 2013-11-08 18:16:55 UTC
(In reply to comment #3)
 
> If you choose the Transactions table, the wizard doesn't even get to the
> end, try clicking on "Finish" and nothing happens, clicking on Next several
> times eventually gets you stuck on the Primary Key definition page of the
> dialog, and then you can go no further - there seems to be some focus issues
> here with the wizard and event firing/handling. I guess that this particular
> behaviour is a separate issue though.

The wizard doesn't allow to set types and format for this special table. Could be it is a problem with the Bool-field inside of this table. There is no special Bool-field in Firebird.
Comment 6 Lionel Elie Mamane 2013-11-09 01:30:27 UTC
(In reply to comment #5)
> Could be it is a problem with the Bool-field inside of this table. There is
> no special Bool-field in Firebird.

Hmm... Could we fake BOOLEAN in the driver, if necessary? (Meaning, if that's the problem). Maybe we could just do it like MySQL and at table *creation* time, just map BOOLEAN to TINYINT(1) or something like that. (And then, the table has a TINYINT(1) field, not a BOOLEAN field.)

(note: firebird 3 has/will have BOOLEAN datatype)
Comment 7 Lionel Elie Mamane 2013-11-09 01:32:41 UTC
(In reply to comment #6)

> Hmm... Could we fake BOOLEAN in the driver, if necessary? (Meaning, if
> that's the problem). Maybe we could just do it like MySQL and at table
> *creation* time, just map BOOLEAN to TINYINT(1) or something like that. (And
> then, the table has a TINYINT(1) field, not a BOOLEAN field.)\

Maybe add a CHECK clause "check (A=0 or A=1)" as suggested by http://tracker.firebirdsql.org/browse/CORE-726?focusedCommentId=20162&page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel#action_20162
Comment 8 Andrzej Hunt 2013-11-09 18:02:56 UTC
(In reply to comment #6)
> (In reply to comment #5)
> > Could be it is a problem with the Bool-field inside of this table. There is
> > no special Bool-field in Firebird.
> 
> Hmm... Could we fake BOOLEAN in the driver, if necessary? (Meaning, if
> that's the problem). Maybe we could just do it like MySQL and at table
> *creation* time, just map BOOLEAN to TINYINT(1) or something like that. (And
> then, the table has a TINYINT(1) field, not a BOOLEAN field.)
> 
> (note: firebird 3 has/will have BOOLEAN datatype)

It shouldn't be that hard to fake it at creation time, and reading the field as a boolean would also work fine, however there is still the issue that when reading it won't present itself as a boolean, i.e. we wouldn't have the checkboxes etc. in the UI when editing table data directly (because Base thinks it's just an integer).

However we could simply read whether there is a CHECK constraint in place every time we read a SMALLINT (that's the smallest datatype in firebird -- there's no tinyint).

(CHECK constraints (and not-nulls) are in RDB$CHECK_CONSTRAINTS, mapping to RDB$TRIGGERS, still need to figure out how to detect this specific check once there though.)
Comment 9 Lionel Elie Mamane 2013-11-09 18:12:37 UTC
(In reply to comment #8)
> (In reply to comment #6)

>> Hmm... Could we fake BOOLEAN in the driver, if necessary? (Meaning, if
>> that's the problem). Maybe we could just do it like MySQL and at table
>> *creation* time, just map BOOLEAN to TINYINT(1) or something like that. (And
>> then, the table has a TINYINT(1) field, not a BOOLEAN field.)

> It shouldn't be that hard to fake it at creation time, and reading the field
> as a boolean would also work fine, however there is still the issue that
> when reading it won't present itself as a boolean, i.e. we wouldn't have the
> checkboxes etc. in the UI when editing table data directly (because Base
> thinks it's just an integer).

I think that's OK, although not optimal.

> However we could simply read whether there is a CHECK constraint in place
> every time we read a SMALLINT (that's the smallest datatype in firebird --
> there's no tinyint).

That would be even better. We can check whether the CHECK constraint is the one that LibreOffice puts when faking BOOLEAN.
Comment 10 Gerry 2014-01-15 16:54:51 UTC
I can confirm this bug on LO 4.2 RC2. I get the same error message as reported in the bug description.

Version: 4.2.0.2, Build-ID: 601a398b803303d1a40a3299729531824fe0db56
Ubuntu 13.04
Comment 11 Björn Michaelsen 2014-01-17 00:43:33 UTC
(This is an automated message.)

LibreOffice development currently prioritizes bugs with the so called MAB (most annoying bugs) -- as this bug has not run through that process (including writing a short rationale for this bug being a candidate and other who are watching the tracker bug silently approving that rationale etc.) its priority is set to high. Note this is effectively no change in the urgency assigned to this bug, as we are currently not making a difference between high and highest and severity is untouched.

You can find out more about MABs and how the process works by contacting libreoffice qa on irc:

 http://webchat.freenode.net/?channels=libreoffice-qa

The QA wiki page also gives you hints on how to get in contact with the team (if IRC fails you, your next best choice is the mailing list):

 https://wiki.documentfoundation.org/QA
Comment 12 ribotb 2014-04-25 07:38:31 UTC
Adding as blocker for making Firebird the default
Comment 13 Kevin Suo 2014-06-17 09:58:02 UTC
Set platform to ALL, as I can also reproduce on Windows XP SP1, version 4.3.0.0 beta2.
Comment 14 Alex Thurgood 2015-01-03 17:40:47 UTC
Adding self to CC if not already on

Use of freedesktop.org services, including Bugzilla, is subject to our Code of Conduct. How we collect and use information is described in our Privacy Policy.