CREATE DOMAIN and ALTER DOMAIN do not validate the default value

[mrotteveel]

CREATE DOMAIN does not evaluate the default value for validity, which makes it possible to create a domain with an invalid default:

create domain ts_invalid timestamp default '  .  .       :  :  ';

Result: no error
Expected:

Statement failed, SQLSTATE = 22018
conversion error from string " . . : : "

Same happens for ALTER DOMAIN if the domain is not yet used for a NOT NULL column.

create domain ts_invalid;
alter domain ts_invalid set default  '  .  .       :  :  ';

Result: no error
Expected:

Statement failed, SQLSTATE = 22018
conversion error from string " . . : : "

If you then attempt to create a table with this domain as a nullable column, it works, until you insert with default values. If you create a table with the domain as a not null column, it fails. The ALTER DOMAIN does fail if the domain was already used for a NOT NULL column.

I would expect Firebird to validate if the DEFAULT expression produces a valid value of the domain type.

[mrotteveel]

I only tagged 5.0.1, but I assume this also applies to older versions.

[asfernandes]

Domain's default can return dynamic expression like CURRENT_USER, which may be a number inside a string or a date.
So even if practically that would not make sense, technically it should not validate things it can't know for sure and that moment.

If you create a table with the domain as a not null column, it fails

Because in this case it must evaluate to store the value in the table format.

[mrotteveel]

Technically, it should validate things, as specified by the SQL Standard, 11.5 <default clause>, but those rules are more strict than the expressions and conversions that Firebird allows.

For example, TIMESTAMP should only accept a datetime literal, not a string literal:

If the subject data type is datetime, then the <literal> shall be a <datetime literal> with the same primary datetime fields and the same time zone datetime fields as the subject data type. If SECOND is one of these fields, then the fractional seconds precision of the <datetime literal> shall be less than or equal to the fractional seconds precision of the subject data type.

or a datetime function of the exact same type:

If <datetime value function> is specified, then the subject data type shall be datetime with the
same declared datetime data type of the <datetime value function>

And, as to your example, CURRENT_USER should only be allowed against a string-type column:

If CURRENT_USER, CURRENT_ROLE, SESSION_USER, SYSTEM_USER, CURRENT_CATALOG, or CURRENT_SCHEMA is specified, then the subject data type shall be character string with character set SQL_IDENTIFIER. If the length of the subject data type is fixed, then its length shall not be less than 128 characters. If the length of the subject data type is variable, then its maximum length shall not be less than 128 characters.

Obviously, that is too restrictive for historic reasons, but some form of validation should be performed, if only for literals.

[mrotteveel]

Removed my previous comment about CURRENT_USER preventing table alteration, as it is probably not accurate.

[mrotteveel]

In other words, maybe we shouldn't generally validate it, but we should validate if a literal is convertible without error to the domain type.