FYI The SQL standard says that null argument should give null result. See also http://issues.apache.org/jira/browse/DERBY-729
Susan Cline wrote: > 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'. > > -- Bernt Marius Johnsen, Database Technology Group, Staff Engineer, Technical Lead Derby/Java DB Sun Microsystems, Trondheim, Norway
signature.asc
Description: OpenPGP digital signature
