Bug Hunting Session
Bug 119628 - EDITING: Firebird Embedded: error message when resetting auto increment a second time
Summary: EDITING: Firebird Embedded: error message when resetting auto increment a sec...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.0.6.2 release
Hardware: All Mac OS X (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Firebird-Default
  Show dependency treegraph
 
Reported: 2018-08-31 22:38 UTC by kam3don
Modified: 2019-10-20 15:33 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Attachments
A simple database for testing (5.96 KB, application/vnd.oasis.opendocument.database)
2018-08-31 22:38 UTC, kam3don
Details
a small example base with macro (12.90 KB, application/vnd.sun.xml.base)
2019-10-20 15:30 UTC, cam
Details

Note You need to log in before you can comment on or make changes to this bug.
Description kam3don 2018-08-31 22:38:00 UTC
Created attachment 144590 [details]
A simple database for testing

macOS High Sierra 10.13.6

Example: I have a table with records 1-30 with the primary key AutoValue set to Yes. I delete records 16-30. I don't want the next record I add to have a primary key of 31.

To reset this, I go to Tools > SQL and type in alter table TABLE_NAM alter FIELD_NAME restart with 15;

All is well, and I am able to add new records without a gap in the primary key sequence.

However, if I again delete some records and try to execute this SQL statement again, I receive the following error message:

1: firebird_sdbc error: *violation of PRIMARY or UNIQUE KEY constraint "INTEG_1" on table "TABLE_NAM" caused by 'alter table "TABLE_NAM" alter "FIELD_NAME" restart with NNNN;'

(NNNN being whatever number I input in my second attempt at restarting the sequence.)

The funny thing is, despite the error message, Base will do as I directed and restart the sequence once again.

Attached is the database I was using.
Comment 1 Alex Thurgood 2018-09-03 08:20:46 UTC
I get the following error message 

1: firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -104
*Token unknown - line 1, column 42
*"Employee ID"
caused by
'isc_dsql_prepare'



when using 
Version: 6.2.0.0.alpha0+
Build ID: b8658c0e847afe1ed199c5fa7a2dd5d1d61ccbac
CPU threads: 4; OS: Mac OS X 10.13.6; UI render: default; 
Locale: fr-FR (fr_FR.UTF-8); Calc: threaded

and the SQL command:

ALTER TABLE "Employee Table" ALTER FIELD "Employee ID" RESTART WITH 16
Comment 2 Alex Thurgood 2018-09-03 08:24:57 UTC
Sorry, my bad, I misread the SQL statement. Retrying with the correct expression, I can reproduce this.

Confirming
Comment 3 Xisco Faulí 2018-09-04 11:59:32 UTC
(In reply to Alex Thurgood from comment #2)
> Sorry, my bad, I misread the SQL statement. Retrying with the correct
> expression, I can reproduce this.
> 
> Confirming

and what's the correct expression ?
Comment 4 Alex Thurgood 2018-09-04 14:34:13 UTC
alter table "Employee Table" alter "Employee ID" restart with 15;
Comment 5 QA Administrators 2019-09-05 09:34:56 UTC Comment hidden (obsolete)
Comment 6 cam 2019-10-20 15:30:44 UTC
Created attachment 155164 [details]
a small example base with macro
Comment 7 cam 2019-10-20 15:33:37 UTC
I just tried this Sunday, 20/10/2019 and the bug is still present.
Cordially

I use  LibreOffice :
Version: 6.2.7.1 (x64)
Build ID: 23edc44b61b830b7d749943e020e96f5a7df63bf
Threads CPU : 4; OS : Windows 10.0; UI Render : par défaut; VCL: win; 
Locale : fr-FR (fr_FR); Langue IHM : fr-FR
Calc: CL