db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: Reference Manual: Built-in Functions, null arguments and time duration
Date Mon, 04 Sep 2006 13:16:37 GMT
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


Mime
View raw message