db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Susan Cline <home4...@pacbell.net>
Subject Reference Manual: Built-in Functions, null arguments and time duration
Date Fri, 01 Sep 2006 22:42:47 GMT
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'.
 
 

Mime
View raw message