Many new 2.1 built in functions have incorrect NULL semantics [CORE1514]

[firebird-automations]

Submitted by: Richard Wesley (hawkfish)

Is related to QA188
Is duplicated by CORE1529

I have just grabbed a copy of the 2.1b1 server and started running some tests of the new built in functions. We are very glad to see some of these, but I am distressed that many of them do not seem to have correct NULL semantics.

Here are all the functions that have incorrect (IMHO) NULL semantics:

ABS
ACOS
ASCII_VAL
ASIN
ATAN
ATAN2
BIN_AND
BIN_OR
BIN_XOR
COS
COSH
COT (produces infinity)
DATEADD ( dateadd(year, null, timestamp) seems to produce the epoch, so someone is detecting the null internally, but another layer is translating it to 0?)
FLOOR
LN (produces -infinity)
LOG
LOG10 (produces -infinity)
LPAD ( LPAD(cast(null as char(32)), 1, ' ') returns null, but LPAD(cast(null as char(32)), 1) fails to match a function )
MOD
ROUND (can't match on null integer or null double precision)
RPAD (same issue as LPAD)
SIGN
SIN
SINH
SQRT
TAN
TANH

Functions that seem correct:

ASCII_CHAR
BIN_SHL
BIN_SHR
CEILING
DATEDIFF
EXP
HASH
LEFT
MAXVALUE
MINVALUE
OVERLAY
POSITION
POWER
REPLACE
REVERSE
RIGHT
TRUNC

Since the results are so inconsistent (e.g. FLOOR vs CEILING) or just plain goofy (the DATEADD example should produce the input timestamp at the very least), I have to presume that these are bugs.

Commits: ca7004e 3073cc8 d45c169

[firebird-automations]

Commented by: @ibprovider

I think, this problem relating with next error also

FB 2.1 [build 16902]

PREPARE SQL: "SELECT POWER(10,NULL)*NULL FROM RDB$DATABASE"

ERROR: expression evaluation not supported.

[firebird-automations]

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

[firebird-automations]

Commented by: @asfernandes

Please test again in a next snapshot.

But I haven't see any problem to match functions LPAD, RPAD and ROUND and the differences between FLOOR and CEILING.

Note that ROUND needs two parameters.

[firebird-automations]

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.1 RC1 [ 10201 ]

[firebird-automations]

Commented by: Richard Wesley (hawkfish)

My bad on ROUND - with two arguments it works correctly in 2.1b1. I was thinking of MySQL which has a 1 argument version but the Firebird documentation specifies only a two argument version.

LPAD and RPAD also seem to work with my 2.1b1 server, so I must have mistyped something. Either that or Adriano snuck onto our server in the night and fixed it ;-)

I will verify next time I get cycles for 2.1. This will make our expressions shorter, which is always nice.

[firebird-automations]

Modified by: @asfernandes

Link: This issue is duplicated by CORE1529 [ CORE1529 ]

[firebird-automations]

Commented by: @asfernandes

Another problem being fixed now:
select trunc(1, cast(null as integer)) from rdb$database;

[firebird-automations]

Modified by: @pcisar

Link: This issue is related to QA188 [ QA188 ]

[firebird-automations]

Modified by: @pcisar

Workflow: jira [ 13290 ] => Firebird [ 14002 ]

[firebird-automations]

Commented by: @pmakowski

Q/A test ok

[firebird-automations]

Modified by: @pmakowski

status: Resolved [ 5 ] => Closed [ 6 ]

[firebird-automations]

Modified by: @pavel-zotov

QA Status: No test

[firebird-automations]

Modified by: @pavel-zotov

QA Status: No test => Done successfully