I'm reviewing some of the Built-in functions in the reference manual and have found these things which I believe are incorrect: 1) SECOND function The documentation for the SECOND function says (http://db.apache.org/derby/docs/10.2/ref/rrefsecondfunc.html): If the argument can be null, the result can be null; if the argument is null, the result is the null value.
I'm not entirely clear I understand what this means, but here is a test to show that if the argument is null, the result is NOT null; ij> create table timestamp_tab (id integer, col2 timestamp); 0 rows inserted/updated/deleted ij> insert into timestamp_tab(id) values (1); 1 row inserted/updated/deleted ij> insert into timestamp_tab values (2, null); 1 row inserted/updated/deleted ij> select second(col2) from timestamp_tab; 1 ---------------------- 0.0 << these should be null according to the doc, and are 0 0.0 Also, for the SECOND function is this statement: 'If the argument is a time duration or timestamp duration: The result is the seconds part of the value, which is an integer between -99 and 99. A nonzero result has the same sign as the argument.' This may sound foolish ;-), but I don't understand what a time or timestamp duration is, and how you get an integer between -99 and 99. For other folks like me (hopefully!) that don't understand this, could someone provide an example that we could include with the docs? Also, I can't figure out how to give the second function a non-zero argument, since the argument must be a time, timestamp or character string representaion of a time or timestamp. Finally, the doc says 'The result of the function is a large integer.' Is this a specific data type? Or should it just say INTEGER, BIGINT, SMALLINT? ------------------------------------- 2) The SMALLINT function has no example. http://db.apache.org/derby/docs/10.2/ref/rrefbuiltsmallint.html Here are two: ij> values smallint (32767.99); 1 ------ 32767 1 row selected ij> values smallint('1'); 1 ------ 1 -------------------------- 3) SUBSTR function SUBSTR({ CharacterExpression }, StartPosition [, LengthOfString ] ) http://db.apache.org/derby/docs/10.2/ref/rrefsqlj93082.html The doc says 'SUBSTR returns NULL if lengthOfString is specified and it is less than zero. If startPosition is positive, it refers to position from the start of the source expression (counting the first character as 1). If startPosition is negative, it is the position from the end of the source.' This is not true; ij> values substr('hello', -1); 1 ----- ERROR 22011: The second or third argument of the SUBSTR function is out of range ij> values substr('hello', 1,-2); 1 --------------- ERROR 22011: The second or third argument of the SUBSTR function is out of range . ij> values substr('hello', -1,2); 1 ---- ERROR 22011: The second or third argument of the SUBSTR function is out of range . Also, there are not any examples. Here are a couple: ij> values substr('hello', 2); 1 ----- ello ij> values substr('hello', 1,2); 1 ---- he 1 row selected -------------------------- 4) Inconsistent use of the statement 'If the argument can be null, the result can be null; if the argument is null, the result is the null value.' throughout the built-in functions section. For example the TIME function has this statement, while the TIMESTAMP function does not, but the result of passing in a null argument are the same (a return value of null.) --------------------------- 5) TIMESTAMP Function http://db.apache.org/derby/docs/10.2/ref/rreftimestampfunc.html The example sql is incorrect and the return value output is incorrect. The doc says "TIMESTAMP(START_DATE, START_TIME) Returns the value '1998-12-25-17.12.30.000000'. " It should be something like this; create table timestamp_tab2(id integer, col2 date, col3 time) insert into timestamp_tab2 values(1, '1998-12-25', '17.12.30'); ij> select timestamp(col2, col3) from timestamp_tab2; 1 -------------------------- 1998-12-25 17:12:30.0 or this: VALUES TIMESTAMP('1998-12-25', '17.12.30'); 1 -------------------------- 1998-12-25 17:12:30.0 ----------------------------------- 6) UCASE or UPPER http://db.apache.org/derby/docs/10.2/ref/rrefsqlj29930.html Instead of the word 'Syntax' like all of the other functions have, the word 'Format' is used. It needs to be changed to 'Syntax'.
