Error "Conversion error from string " . . : : "." (335544334) when trying to add another column

Given is a Firebird 5.0 table with this DDL:

 CREATE TABLE KASSE_DETAILEDSTATISTICS 
 (ORDERDATE KASSE_ORDERTIMESTAMP NOT NULL,
  PRODUCTID KASSE_PRODUCTID NOT NULL,
  CONSUMPTIONPLACE KASSE_INHAUS NOT NULL,
  ORDERTYPE KASSE_ORDERTYPE NOT NULL,
  QUANTITY KASSE_QUANTITY NOT NULL,
  PRICESUM KASSE_PRICE,
  TRAININGPURCHASE KASSE_BOOLEAN NOT NULL,
  KASSE_ID KASSE_Z_KASSE_ID);

ALTER TABLE KASSE_DETAILEDSTATISTICS ADD PRIMARY KEY (ORDERDATE,PRODUCTID,CONSUMPTIONPLACE);

CREATE UNIQUE INDEX IDX_KASSE_DETAILEDSTATISTICS ON KASSE_DETAILEDSTATISTICS(ORDERDATE,PRODUCTID,CONSUMPTIONPLACE);

Now I try to add another column like this:

ALTER TABLE KASSE_DETAILEDSTATISTICS ADD TEST INTEGER;

But this always results in this failure message when trying to commit it:

Conversion error from string "  .  .       :  :  ".

SQL Code: -413
IB Error Number: 335544334

Ok, it looks like date related, but why does Firebird want to convert some empty string into a timestamp? Firebird shall just add a column!

The table contains data and the ORDERDATE column only contains valid timestamp values!

Solution

This error is caused by one of your custom domains having an invalid default. I guess that is KASSE_ORDERTIMESTAMP, and its default is set to ' . . : : '. Unfortunately, CREATE DOMAIN currently doesn't validate if the default expression is actually valid for the specified datatype (I reported that as https://github.com/FirebirdSQL/firebird/issues/8303).

In ISQL, you can check this with show domain KASSE_ORDERTIMESTAMP, which will output something like:

KASSE_ORDERTIMESTAMP            TIMESTAMP Nullable
                                default '  .  .       :  :  '

In Firebird 3.0 and higher when you add a column, Firebird will produce a new format version, and at that time, the default values of all NOT NULL columns are evaluated, and that produces this error as one of them does not have a valid default value.

I think you initially created this database in an older version (2.5 or older), as those didn't evaluate default values (of existing or new columns with NOT NULL) when new columns were added.

You will need to alter the domain to drop its default before you can add a new column:

alter domain KASSE_ORDERTIMESTAMP drop default;

NOTE: This answer is based on conjecture, and observed behaviour from some experiments on Firebird 2.5, Firebird 3.0 and Firebird 5.0.