Bug 105240 - Firebird: Insert NULL for auto value fails
Summary: Firebird: Insert NULL for auto value fails
Status: RESOLVED NOTABUG
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
5.3.0.0.beta2
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Firebird-Default
  Show dependency treegraph
 
Reported: 2017-01-10 17:46 UTC by Gerhard Schaber
Modified: 2017-01-18 15:30 UTC (History)
3 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 Gerhard Schaber 2017-01-10 17:46:02 UTC
The following sequence of SQL commands on a fresh FirebirdSQL file fails:
CREATE TABLE "Lehrgangsliste"("ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) PRIMARY KEY,"Tarifgruppe" INTEGER NOT NULL,"Pruefungsgruppe" INTEGER,"MitgliedsID" VARCHAR(10),"DatumsID" DATE NOT NULL,"Vorname" VARCHAR(50) NOT NULL,"Nachname" VARCHAR(50) NOT NULL,"Verein" INTEGER NOT NULL,"Grad" INTEGER)
INSERT INTO "Lehrgangsliste" VALUES ( NULL, 3, NULL, 'YW045', '2016-10-15', 'First name', 'Last name', 701, 12 )

It returns the following error:
1: firebird_sdbc error:
*validation error for column "Lehrgangsliste"."ID", value "*** null ***"
caused by
'INSERT INTO "Lehrgangsliste" VALUES ( NULL, 3, NULL, 'YW045', '2016-10-15', 'First name', 'Last name', 701, 12 )'

This makes it impossible to insert rows into a table while having the ID column being auto generated.

I consider this a very common use case.

See also bug #104942 for other migration issues to FirebirdSQL.
Comment 1 Robert Großkopf 2017-01-10 20:04:07 UTC
This couldn't be a bug.
It isn't allowed to insert NULL into a field for a primary key.
Identity columns are implicitly NOT NULL (non-nullable).

See:
http://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-ddl-enhance.html#rnfb30-ddl-identity

You have to define a value for the field "ID" (which must be a unique value) or you have to write down a list of all fields in the INSERT-command.
Comment 2 Gerhard Schaber 2017-01-10 20:48:58 UTC
Inserting NULL does not set the value to NULL in this case. Inserting NULL into an auto-value field makes the value increment automatically. At least it is like this with HSQLDB, and I think other database systems as well.
Comment 3 Robert Großkopf 2017-01-11 15:32:45 UTC
(In reply to Gerhard Schaber from comment #2)
> Inserting NULL does not set the value to NULL in this case. Inserting NULL
> into an auto-value field makes the value increment automatically. At least
> it is like this with HSQLDB, and I think other database systems as well.

HSQLDB supports this, MariaDB also - but are you shure Firebird will support it also? 

http://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-ddl-enhance.html#rnfb30-ddl-identity :
"An identity column is a column associated with an internal sequence generator. Its value is set automatically when the column is omitted in an INSERT statement."

You haven't omitted "ID". You have send NULL with your command - and this isn't allowed. So an error of Firebird appears. This couldn't be our bug but an ask for an enhancement of Firebird.

Could be my English isn't well enough for the release-notes of Firebird 3.0. So let us wait for other statements here.
Comment 4 Gerhard Schaber 2017-01-11 15:39:27 UTC
Thanks. I tried that as well. Omitting the value would also work for me, but this also does not work. At least not with LibreOffice. Maybe I am doing something wrong.
Comment 5 Gerhard Schaber 2017-01-11 15:44:44 UTC
INSERT INTO "Lehrgangsliste" VALUES ( 3, NULL, 'YW045', '2016-10-15', 'First name', 'Last name', 701, 12 )
1: firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -804
*Count of read-write columns does not equal count of values
caused by
'isc_dsql_prepare'

INSERT INTO "Lehrgangsliste" VALUES (, 3, NULL, 'YW045', '2016-10-15', 'First name', 'Last name', 701, 12 )
2: firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -104
*Token unknown - line 1, column 38
*,
caused by
'isc_dsql_prepare'
Comment 6 Gerhard Schaber 2017-01-11 15:50:23 UTC
The following actually works, but causes problems, since in some generic code, I need to be aware of all column names:
INSERT INTO "Lehrgangsliste" ("Tarifgruppe", "Pruefungsgruppe", "MitgliedsID", "DatumsID", "Vorname", "Nachname", "Verein", "Grad") VALUES (3, NULL, 'YW045', '2016-10-15', 'First name', 'Last name', 701, 12 )

But well, that apparently means I have to change more code.
Comment 7 Robert Großkopf 2017-01-11 16:10:44 UTC
(In reply to Gerhard Schaber from comment #5)
> INSERT INTO "Lehrgangsliste" VALUES ( 3, NULL, 'YW045', '2016-10-15', 'First
> name', 'Last name', 701, 12 )

This couldn't work, because you have to define the columns, if you won't add a value to every column.

INSERT INTO "Lehrgangsliste" VALUES ( 15, 3, NULL, 'YW045', '2016-10-15', 'First
 name', 'Last name', 701, 12 )

will work, if "ID" = 15 doesn't exist.
Comment 8 Alex Thurgood 2017-01-11 16:49:20 UTC
I tried alternatively :

('',...)
("",...)
( ,...)

and simply starting with a missing entry for the ID column. In each case, I got an error and the statement refused to complete. 

Confirming

Tested on 

Version: 5.4.0.0.alpha0+
Build ID: b66b1e896c177df3c5de5d33037416152fc8a381
CPU Threads: 2; OS Version: Mac OS X 10.12.2; UI Render: default; 
Locale: fr-FR (fr_FR.UTF-8); Calc: group
Comment 9 Gerhard Schaber 2017-01-11 17:03:47 UTC
I checked some Firebird docs and forums. I could not find any hint about whether this will be supported or not.
Comment 10 Lionel Elie Mamane 2017-01-11 17:45:11 UTC
(In reply to Gerhard Schaber from comment #6)
> The following actually works, but causes problems, since in some generic
> code, I need to be aware of all column names:
> INSERT INTO "Lehrgangsliste" ("Tarifgruppe", "Pruefungsgruppe",
> "MitgliedsID", "DatumsID", "Vorname", "Nachname", "Verein", "Grad") VALUES
> (3, NULL, 'YW045', '2016-10-15', 'First name', 'Last name', 701, 12 )
> 
> But well, that apparently means I have to change more code.

Well, I'm afraid that explicitly inserting NULL and getting the AutoValue instead is (I guess) not part of the SQL standard. HSQLDB and MariaDB (and others) support it as an extension, but e.g. Firebird (you tell me) and PostgreSQL (I just tested) do not.

For complete portability, you just have to do as you outline above.

You may if you wish file a wishlist bug / enhancement request directly at the Firebird project; if they decide to implement this extension/feature, we will get it when we upgrade to whatever version has it.
Comment 11 Gerhard Schaber 2017-01-18 15:30:36 UTC
This describes an alternative:
http://www.firebirdfaq.org/faq29/

Anyway, I have created a ticket for Firebird in order to get this behavior by default for a not NULL identity column:
http://tracker.firebirdsql.org/browse/CORE-5460