You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Susan Cline <ho...@pacbell.net> on 2006/09/02 00:42:47 UTC
Reference Manual: Built-in Functions, null arguments and time duration
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'.
Re: Reference Manual: Built-in Functions,
null arguments and time duration
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
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