CAST(<expression> AS <type name> NOT NULL) [CORE6428]

[firebird-automations]

Submitted by: @ibprovider

Hello,

At current time Firebird does not allow expression like "CAST(ColumnName AS INTEGER NOT NULL)".

The expected behavior is generation of error for a NULL value.

This feature may be used in LINQ-translators for "Nullable<type>.Value" expressions.

[firebird-automations]

Commented by: @mrotteveel

Such a syntax is not defined in the SQL standard. Furthermore, the General Rules in 6.13 <cast specification> explicitly state:
"""
a) If the <cast operand> specifies NULL, then the result of CS is the null value and no further General Rules of this Subclause are applied.
[..]
b) If SV is the null value, then the result of CS is the null value and no further General Rules of this Subclause are applied.
"""

In other words, the result of a cast of null should always be null.

[firebird-automations]

Commented by: @livius2

It will be usefull extension
but in such situation i do maybe not elegant way but it work

instead of your
CAST(ColumnName AS INTEGER NOT NULL)

i use
COALESCE(CAST(ColumnName AS INTEGER), 1/0)
or inside cast
CAST(COALESCE(ColumnName, 1/0) AS INTEGER)

i generate division by 0 here.

[firebird-automations]

Commented by: @ibprovider

Karol,

UP! :)

[firebird-automations]

Commented by: @mrotteveel

Having a separate function or expression that raises an error when the value is null, or returns the value seems more sensible to me to be honest than to force this into cast.

[firebird-automations]

Commented by: @livius2

Of course Mark.
But above sample is the simplest posssible one, without any ddl first. And as Dmitry say it is about LINQ-translators which do nor run ddl ;-)
On previous FB versions we can do select from stored_procedure which raise an error, on FB3 it is even simpler and elegant.
You can declare function which raise exception as a universal one and use in coalesce or some "overloaded" with different param types and return value if provided or raise an error if null.

[firebird-automations]

Commented by: @mrotteveel

I was arguing that if such a feature were to be added it should be as a separate built-in function (or an expression), not as part of cast.