Bug 125955 - BASE: FIREBIRD: Auto-value is incremented when input was aborted by Firebird database due to erroneous input
Summary: BASE: FIREBIRD: Auto-value is incremented when input was aborted by Firebird ...
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.2.4.2 release
Hardware: x86-64 (AMD64) All
: medium minor
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Firebird-Default
  Show dependency treegraph
 
Reported: 2019-06-16 14:09 UTC by simon.i
Modified: 2019-08-22 09:21 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
See "Table1" in the attached database - not NULL for field Surname (13.71 KB, application/vnd.oasis.opendocument.database)
2019-06-16 14:26 UTC, Robert Großkopf
Details

Note You need to log in before you can comment on or make changes to this bug.
Description simon.i 2019-06-16 14:09:07 UTC
Description:
I have created a form with a table control element which is used to input data to a table. Some of the fields have "input required" enabled, so when I want to jump to another row (could also happen by clicking somewhere else by mistake) before I entered any data into those fields, Base shows the correct error message and lets me return to that row to input the missing data. However, it still increments the auto-value of the primary key.

So let's say, for example, the previous table entry has ID=5. -> I start a new row -> mess up while inputting the data or forget one field -> that error message is shown -> I correct my input -> continue to the next row, the row will now have ID=7 instead of ID=6.

(This is the error message: firebird_sdbc error:
*validation error for column "tablename"."fieldname", value "*** null ***"
caused by
'isc_dsql_execute')

Steps to Reproduce:
1. Create a table
2. Create a primary key with type integer and auto-value enabled
3. Create a field of any type without "input required"
4. create some other field with "input required"
5. Save the table
6. edit the table: enter something into first field (the one without input required)
7. leave the second field blank (the one with input required)
8. try to get to the next row by using tabulator key
9. Error message pops up because of blank field. Exit message
10. enter data into second field (the one which was previoulsy left blank)
11. tab into next row by using tabulator key
12. Primary key integer value is now 2 whereas it should be 1


Actual Results:
inconsistent IDs

Expected Results:
The Primary key value should not be increased when the data record submission was aborted due to an error


Reproducible: Always


User Profile Reset: No



Additional Info:
Version: 6.2.4.2 (x64)
Build-ID: 2412653d852ce75f65fbfa83fb7e7b669a126d64
CPU-Threads: 4; BS: Windows 10.0; UI-Render: Standard; VCL: win; 
Gebietsschema: de-DE (de_DE); UI-Sprache: de-DE
Calc: CL
Comment 1 Robert Großkopf 2019-06-16 14:26:27 UTC
Created attachment 152232 [details]
See "Table1" in the attached database - not NULL for field Surname

The buggy behaviour isn't a buggy behaviour of the form. It could also be reproduced in a table.
Try to insert content in the table "Table1" without filling "Surname" with content. It will fail with an error from Firebird - but the counter for "ID" will count everytime I try to submit the data.
Comment 2 m_a_riosv 2019-06-16 23:20:30 UTC
I`m not sure it is a bug,
Firebird use transactions, Some explanation http://docs.openlinksw.com/virtuoso/identity/.

On the other hand, if I'm not wrong, basic use of auto increment columns are for to have a primary key for the table, so records have unique IDs on the table.
Comment 3 Alex Thurgood 2019-06-17 10:31:32 UTC
FWIW, I seem to recall that the same behaviour occurs when working with postgresql and an autovalue field. I also seem to recall reporting this at the time, and it being deemed not a bug...(but I can't find the bug report at the moment).
Comment 4 Lionel Elie Mamane 2019-08-22 09:10:58 UTC
autoincrement fields have unique values, but are not necessarily strictly sequential.

That is indeed so in several different DMBS. It is kinda hard (not sure even possible) to ensure the values are strictly sequential, if one allows transactions (that can be cancelled...) and/or concurrent inserts.