Issue Details (XML | Word | Printable)

Key: CORE-1514
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Richard Wesley
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Many new 2.1 built in functions have incorrect NULL semantics

Created: 13/Oct/07 05:04 PM   Updated: 07/Oct/08 10:18 AM
Component/s: Engine
Affects Version/s: 2.1 Beta 1
Fix Version/s: 2.1 RC1

Time Tracking:
Not Specified

Environment: Windows XP SP2
Issue Links:
Duplicate
 
Relate
 


 Description  « Hide
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.


 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Kovalenko Dmitry added a comment - 17/Oct/07 05:42 AM
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.

Adriano dos Santos Fernandes added a comment - 17/Oct/07 02:34 PM
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.

Richard Wesley added a comment - 17/Oct/07 04:15 PM
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.

Adriano dos Santos Fernandes added a comment - 11/Nov/07 07:07 PM
Another problem being fixed now:
    select trunc(1, cast(null as integer)) from rdb$database;

Philippe Makowski added a comment - 07/Oct/08 10:18 AM
Q/A test ok