db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From be...@apache.org
Subject 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/
Date Fri, 29 Sep 2006 07:48:47 GMT
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;



Mime
View raw message