You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by be...@apache.org on 2006/09/29 09:48:47 UTC
svn commit: r451147 - in /db/derby/code/trunk/java:
engine/org/apache/derby/iapi/types/
testing/org/apache/derbyTesting/functionTests/master/
testing/org/apache/derbyTesting/functionTests/tests/lang/
Author: bernt
Date: Fri Sep 29 00:48:47 2006
New Revision: 451147
URL: http://svn.apache.org/viewvc?view=rev&rev=451147
Log:
DERBY-729 Scalar time and date functions return 0 instead NULL when argument is NULL. Submitted by Bernt M. Johnsen
Modified:
db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLDate.java
db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLTime.java
db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLTimestamp.java
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/datetime.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/datetime.sql
Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLDate.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLDate.java?view=diff&rev=451147&r1=451146&r2=451147
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLDate.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLDate.java Fri Sep 29 00:48:47 2006
@@ -594,20 +594,25 @@
** SQL Operators
*/
+ NumberDataValue nullValueInt() {
+ return new SQLInteger();
+ }
+
+
/**
* @see DateTimeDataValue#getYear
*
* @exception StandardException Thrown on error
*/
public NumberDataValue getYear(NumberDataValue result)
- throws StandardException
+ throws StandardException
{
- if (SanityManager.DEBUG)
- {
- SanityManager.ASSERT(!isNull(), "getYear called on a null");
- }
- return SQLDate.setSource(getYear(encodedDate), result);
- }
+ if (isNull()) {
+ return nullValueInt();
+ } else {
+ return SQLDate.setSource(getYear(encodedDate), result);
+ }
+ }
/**
* @see DateTimeDataValue#getMonth
@@ -617,11 +622,11 @@
public NumberDataValue getMonth(NumberDataValue result)
throws StandardException
{
- if (SanityManager.DEBUG)
- {
- SanityManager.ASSERT(!isNull(), "getMonth called on a null");
- }
- return SQLDate.setSource(getMonth(encodedDate), result);
+ if (isNull()) {
+ return nullValueInt();
+ } else {
+ return SQLDate.setSource(getMonth(encodedDate), result);
+ }
}
/**
@@ -632,11 +637,11 @@
public NumberDataValue getDate(NumberDataValue result)
throws StandardException
{
- if (SanityManager.DEBUG)
- {
- SanityManager.ASSERT(!isNull(), "getDate called on a null");
- }
- return SQLDate.setSource(getDay(encodedDate), result);
+ if (isNull()) {
+ return nullValueInt();
+ } else {
+ return SQLDate.setSource(getDay(encodedDate), result);
+ }
}
/**
@@ -647,10 +652,6 @@
public NumberDataValue getHours(NumberDataValue result)
throws StandardException
{
- if (SanityManager.DEBUG)
- {
- SanityManager.ASSERT(!isNull(), "getHours called on null.");
- }
throw StandardException.newException(SQLState.LANG_UNARY_FUNCTION_BAD_TYPE,
"getHours", "Date");
}
@@ -663,10 +664,6 @@
public NumberDataValue getMinutes(NumberDataValue result)
throws StandardException
{
- if (SanityManager.DEBUG)
- {
- SanityManager.ASSERT(!isNull(), "getMinutes called on null.");
- }
throw StandardException.newException(SQLState.LANG_UNARY_FUNCTION_BAD_TYPE,
"getMinutes", "Date");
}
@@ -679,10 +676,6 @@
public NumberDataValue getSeconds(NumberDataValue result)
throws StandardException
{
- if (SanityManager.DEBUG)
- {
- SanityManager.ASSERT(!isNull(), "getSeconds called on null.");
- }
throw StandardException.newException(SQLState.LANG_UNARY_FUNCTION_BAD_TYPE,
"getSeconds", "Date");
}
Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLTime.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLTime.java?view=diff&rev=451147&r1=451146&r2=451147
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLTime.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLTime.java Fri Sep 29 00:48:47 2006
@@ -656,6 +656,10 @@
** SQL Operators
*/
+ NumberDataValue nullValueInt() {
+ return new SQLInteger();
+ }
+
/**
* @see DateTimeDataValue#getYear
*
@@ -664,10 +668,6 @@
public NumberDataValue getYear(NumberDataValue result)
throws StandardException
{
- if (SanityManager.DEBUG)
- {
- SanityManager.ASSERT(!isNull(), "getYear called on null.");
- }
throw StandardException.newException(SQLState.LANG_UNARY_FUNCTION_BAD_TYPE,
"getYear", "Time");
}
@@ -680,10 +680,6 @@
public NumberDataValue getMonth(NumberDataValue result)
throws StandardException
{
- if (SanityManager.DEBUG)
- {
- SanityManager.ASSERT(!isNull(), "getMonth called on null.");
- }
throw StandardException.newException(SQLState.LANG_UNARY_FUNCTION_BAD_TYPE,
"getMonth", "Time");
}
@@ -696,10 +692,6 @@
public NumberDataValue getDate(NumberDataValue result)
throws StandardException
{
- if (SanityManager.DEBUG)
- {
- SanityManager.ASSERT(!isNull(), "getDate called on null.");
- }
throw StandardException.newException(SQLState.LANG_UNARY_FUNCTION_BAD_TYPE,
"getDate", "Time");
}
@@ -712,11 +704,11 @@
public NumberDataValue getHours(NumberDataValue result)
throws StandardException
{
- if (SanityManager.DEBUG)
- {
- SanityManager.ASSERT(!isNull(), "getHours called on null");
- }
- return SQLDate.setSource(getHour(encodedTime), result);
+ if (isNull()) {
+ return nullValueInt();
+ } else {
+ return SQLDate.setSource(getHour(encodedTime), result);
+ }
}
/**
@@ -727,11 +719,11 @@
public NumberDataValue getMinutes(NumberDataValue result)
throws StandardException
{
- if (SanityManager.DEBUG)
- {
- SanityManager.ASSERT(!isNull(), "getMinutes called on null");
- }
- return SQLDate.setSource(getMinute(encodedTime), result);
+ if (isNull()) {
+ return nullValueInt();
+ } else {
+ return SQLDate.setSource(getMinute(encodedTime), result);
+ }
}
/**
@@ -742,11 +734,11 @@
public NumberDataValue getSeconds(NumberDataValue result)
throws StandardException
{
- if (SanityManager.DEBUG)
- {
- SanityManager.ASSERT(!isNull(), "getMinutes called on null");
- }
- return SQLDate.setSource(getSecond(encodedTime), result);
+ if (isNull()) {
+ return nullValueInt();
+ } else {
+ return SQLDate.setSource(getSecond(encodedTime), result);
+ }
}
/*
Modified: db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLTimestamp.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLTimestamp.java?view=diff&rev=451147&r1=451146&r2=451147
==============================================================================
--- db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLTimestamp.java (original)
+++ db/derby/code/trunk/java/engine/org/apache/derby/iapi/types/SQLTimestamp.java Fri Sep 29 00:48:47 2006
@@ -729,6 +729,13 @@
** SQL Operators
*/
+ NumberDataValue nullValueInt() {
+ return new SQLInteger();
+ }
+
+ NumberDataValue nullValueDouble() {
+ return new SQLDouble();
+ }
/**
* @see DateTimeDataValue#getYear
@@ -738,11 +745,11 @@
public NumberDataValue getYear(NumberDataValue result)
throws StandardException
{
- if (SanityManager.DEBUG)
- {
- SanityManager.ASSERT(!isNull(), "getYear called on a null");
- }
- return SQLDate.setSource(SQLDate.getYear(encodedDate), result);
+ if (isNull()) {
+ return nullValueInt();
+ } else {
+ return SQLDate.setSource(SQLDate.getYear(encodedDate), result);
+ }
}
/**
@@ -753,11 +760,11 @@
public NumberDataValue getMonth(NumberDataValue result)
throws StandardException
{
- if (SanityManager.DEBUG)
- {
- SanityManager.ASSERT(!isNull(), "getMonth called on a null");
- }
- return SQLDate.setSource(SQLDate.getMonth(encodedDate), result);
+ if (isNull()) {
+ return nullValueInt();
+ } else {
+ return SQLDate.setSource(SQLDate.getMonth(encodedDate), result);
+ }
}
/**
@@ -768,11 +775,11 @@
public NumberDataValue getDate(NumberDataValue result)
throws StandardException
{
- if (SanityManager.DEBUG)
- {
- SanityManager.ASSERT(!isNull(), "getDate called on a null");
- }
- return SQLDate.setSource(SQLDate.getDay(encodedDate), result);
+ if (isNull()) {
+ return nullValueInt();
+ } else {
+ return SQLDate.setSource(SQLDate.getDay(encodedDate), result);
+ }
}
/**
@@ -783,11 +790,11 @@
public NumberDataValue getHours(NumberDataValue result)
throws StandardException
{
- if (SanityManager.DEBUG)
- {
- SanityManager.ASSERT(!isNull(), "getHours called on a null");
- }
- return SQLDate.setSource(SQLTime.getHour(encodedTime), result);
+ if (isNull()) {
+ return nullValueInt();
+ } else {
+ return SQLDate.setSource(SQLTime.getHour(encodedTime), result);
+ }
}
/**
@@ -798,11 +805,11 @@
public NumberDataValue getMinutes(NumberDataValue result)
throws StandardException
{
- if (SanityManager.DEBUG)
- {
- SanityManager.ASSERT(!isNull(), "getMinute called on a null");
- }
- return SQLDate.setSource(SQLTime.getMinute(encodedTime), result);
+ if (isNull()) {
+ return nullValueInt();
+ } else {
+ return SQLDate.setSource(SQLTime.getMinute(encodedTime), result);
+ }
}
/**
@@ -815,11 +822,14 @@
{
if (SanityManager.DEBUG)
{
- SanityManager.ASSERT(!isNull(), "getSeconds called on a null");
SanityManager.ASSERT(source == null || source instanceof SQLDouble,
"getSeconds for a timestamp was given a source other than a SQLDouble");
}
NumberDataValue result;
+
+ if (isNull()) {
+ return nullValueDouble();
+ }
if (source != null)
result = source;
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/datetime.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/datetime.out?view=diff&rev=451147&r1=451146&r2=451147
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/datetime.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/datetime.out Fri Sep 29 00:48:47 2006
@@ -1210,4 +1210,73 @@
--------------------------
ERROR 22008: 'aaaaaaaaaaaaaa' is an invalid argument to the timestamp function.
ij> rollback;
+ij> --- Null values in datetime scalar functions
+create table nulls (t time, d date, ts timestamp);
+0 rows inserted/updated/deleted
+ij> insert into nulls values (null,null,null);
+1 row inserted/updated/deleted
+ij> commit;
+ij> select year(t) from nulls;
+ERROR 42X25: The 'EXTRACT YEAR' function is not allowed on the 'TIME' type.
+ij> select month(t) from nulls;
+ERROR 42X25: The 'EXTRACT MONTH' function is not allowed on the 'TIME' type.
+ij> select day(t) from nulls;
+ERROR 42X25: The 'EXTRACT DAY' function is not allowed on the 'TIME' type.
+ij> select hour(t) from nulls;
+1
+-----------
+NULL
+ij> select minute(t) from nulls;
+1
+-----------
+NULL
+ij> select second(t) from nulls;
+1
+-----------
+NULL
+ij> select year(d) from nulls;
+1
+-----------
+NULL
+ij> select month(d) from nulls;
+1
+-----------
+NULL
+ij> select day(d) from nulls;
+1
+-----------
+NULL
+ij> select hour(d) from nulls;
+ERROR 42X25: The 'EXTRACT HOUR' function is not allowed on the 'DATE' type.
+ij> select minute(d) from nulls;
+ERROR 42X25: The 'EXTRACT MINUTE' function is not allowed on the 'DATE' type.
+ij> select second(d) from nulls;
+ERROR 42X25: The 'EXTRACT SECOND' function is not allowed on the 'DATE' type.
+ij> select year(ts) from nulls;
+1
+-----------
+NULL
+ij> select month(ts) from nulls;
+1
+-----------
+NULL
+ij> select day(ts) from nulls;
+1
+-----------
+NULL
+ij> select hour(ts) from nulls;
+1
+-----------
+NULL
+ij> select minute(ts) from nulls;
+1
+-----------
+NULL
+ij> select second(ts) from nulls;
+1
+----------------------
+NULL
+ij> drop table nulls;
+0 rows inserted/updated/deleted
+ij> commit;
ij>
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/datetime.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/datetime.sql?view=diff&rev=451147&r1=451146&r2=451147
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/datetime.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/datetime.sql Fri Sep 29 00:48:47 2006
@@ -686,3 +686,29 @@
insert into t(s) values('aaaaaaaaaaaaaa');
select timestamp(s) from t;
rollback;
+
+--- Null values in datetime scalar functions
+
+create table nulls (t time, d date, ts timestamp);
+insert into nulls values (null,null,null);
+commit;
+select year(t) from nulls;
+select month(t) from nulls;
+select day(t) from nulls;
+select hour(t) from nulls;
+select minute(t) from nulls;
+select second(t) from nulls;
+select year(d) from nulls;
+select month(d) from nulls;
+select day(d) from nulls;
+select hour(d) from nulls;
+select minute(d) from nulls;
+select second(d) from nulls;
+select year(ts) from nulls;
+select month(ts) from nulls;
+select day(ts) from nulls;
+select hour(ts) from nulls;
+select minute(ts) from nulls;
+select second(ts) from nulls;
+drop table nulls;
+commit;