db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: [PATCH] Timestamp Arithmetic
Date Mon, 23 May 2005 17:59:23 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Jack, this patch submitted on Friday is already not based on latest
codeline... Shreyas's patch seems to have modified couple of files you
changed. Can you submit an updated patch, that I will commit quickly,
unless anyone else has comments on this patch.<br>
<br>
Satheesh<br>
<br>
Jack Klebanoff wrote:
<blockquote cite="mid428E3ABA.2070504@sbcglobal.net" type="cite">Satheesh
Bandaram wrote:
  <br>
  <br>
  <blockquote type="cite">TimestampAdd seems to allow adding
non-integer intervals, but the behavior doesn't seem right. Either the
result should be "10:10:20.9" or the statement should error. Though
JDBC documenation is not very clear, I thought the "count" intervals
need to be an integer?
    <br>
    <br>
ij&gt; values {fn timestampadd(SQL_TSI_SECOND, 10.9,
time('10:10:10'))};
    <br>
1
    <br>
--------------------------
    <br>
2005-05-18 10:10:20.0
    <br>
    <br>
If we only allow integers for count, then the following needs to
change:
    <br>
    <br>
+&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if( ! bindParameter( rightOperand, Types.INTEGER))
    <br>
+&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {
    <br>
+&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if( ! rightOperand.getTypeId().isNumericTypeId())
    <br>
+&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; throw
StandardException.newException(SQLState.LANG_INVALID_FUNCTION_ARG_TYPE,
    <br>
+&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
rightOperand.getTypeId().getSQLTypeName(),
    <br>
+&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
ReuseFactory.getInteger(2),
    <br>
+&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; operator);
    <br>
+&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }
    <br>
    <br>
Satheesh
    <br>
    <br>
  </blockquote>
I have attached a new patch with the change that Satheesh requested.
  <br>
  <br>
svn status:
  <br>
M&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java
  <br>
M&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java
  <br>
M&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
  <br>
M&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java
  <br>
M&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; java/engine/org/apache/derby/iapi/types/DateTimeDataValue.java
  <br>
M&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; java/engine/org/apache/derby/iapi/types/SQLDate.java
  <br>
M&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; java/engine/org/apache/derby/iapi/types/SQLTime.java
  <br>
M&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; java/engine/org/apache/derby/iapi/types/SQLTimestamp.java
  <br>
M&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; java/engine/org/apache/derby/iapi/reference/SQLState.java
  <br>
M&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; java/engine/org/apache/derby/loc/messages_en.properties
  <br>
A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java/testing/org/apache/derbyTesting/functionTests/tests/lang/timestampArith.java
  <br>
A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java/testing/org/apache/derbyTesting/functionTests/master/timestampArith.out
  <br>
M&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
  <br>
  <br>
Jack
  <br>
  <pre wrap="">
<hr size="4" width="90%">
Index: java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java	(revision 170976)
+++ java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java	(working copy)
@@ -468,6 +468,8 @@
           case C_NodeTypes.LOCATE_FUNCTION_NODE:
 		  case C_NodeTypes.SUBSTRING_OPERATOR_NODE:
 		  case C_NodeTypes.TRIM_OPERATOR_NODE:
+		  case C_NodeTypes.TIMESTAMP_ADD_FN_NODE:
+		  case C_NodeTypes.TIMESTAMP_DIFF_FN_NODE:
 		  	return C_NodeNames.TERNARY_OPERATOR_NODE_NAME;
 
 		  case C_NodeTypes.SELECT_NODE:
@@ -564,7 +566,7 @@
             return C_NodeNames.DB2_LENGTH_OPERATOR_NODE_NAME;
 
 		  // WARNING: WHEN ADDING NODE TYPES HERE, YOU MUST ALSO ADD
-		  // THEM TO $WS/tools/release/config/dbms/cloudscapenodes.properties
+		  // THEM TO tools/jar/DBMSnodes.properties
 
 		  default:
 			throw StandardException.newException(SQLState.NOT_IMPLEMENTED);
Index: java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java	(revision 170976)
+++ java/engine/org/apache/derby/impl/sql/compile/TernaryOperatorNode.java	(working copy)
@@ -44,6 +44,7 @@
 
 import org.apache.derby.impl.sql.compile.ExpressionClassBuilder;
 import org.apache.derby.iapi.util.JBitSet;
+import org.apache.derby.iapi.util.ReuseFactory;
 
 import java.lang.reflect.Modifier;
 
@@ -78,17 +79,23 @@
 	public static final int LOCATE = 1;
 	public static final int SUBSTRING = 2;
 	public static final int LIKE = 3;
-	static final String[] TernaryOperators = {"trim", "LOCATE", "substring", "like"};
-	static final String[] TernaryMethodNames = {"trim", "locate", "substring", "like"};
+	public static final int TIMESTAMPADD = 4;
+	public static final int TIMESTAMPDIFF = 5;
+	static final String[] TernaryOperators = {"trim", "LOCATE", "substring", "like", "TIMESTAMPADD", "TIMESTAMPDIFF"};
+	static final String[] TernaryMethodNames = {"trim", "locate", "substring", "like", "timestampAdd", "timestampDiff"};
 	static final String[] TernaryResultType = {ClassName.StringDataValue, 
 			ClassName.NumberDataValue,
 			ClassName.ConcatableDataValue,
-			ClassName.BooleanDataValue};
+			ClassName.BooleanDataValue,
+            ClassName.DateTimeDataValue, 
+			ClassName.NumberDataValue};
 	static final String[][] TernaryArgType = {
 	{ClassName.StringDataValue, ClassName.StringDataValue, "java.lang.Integer"},
 	{ClassName.StringDataValue, ClassName.StringDataValue, ClassName.NumberDataValue},
 	{ClassName.ConcatableDataValue, ClassName.NumberDataValue, ClassName.NumberDataValue},
-	{ClassName.DataValueDescriptor, ClassName.DataValueDescriptor, ClassName.DataValueDescriptor}
+	{ClassName.DataValueDescriptor, ClassName.DataValueDescriptor, ClassName.DataValueDescriptor},
+    {ClassName.DateTimeDataValue, "java.lang.Integer", ClassName.NumberDataValue}, // time.timestampadd( interval, count)
+    {ClassName.DateTimeDataValue, "java.lang.Integer", ClassName.DateTimeDataValue}// time2.timestampDiff( interval, time1)
 	};
 
 	/**
@@ -233,6 +240,10 @@
 			locateBind();
 		else if (operatorType == SUBSTRING)
 			substrBind();
+		else if (operatorType == TIMESTAMPADD)
+            timestampAddBind();
+		else if (operatorType == TIMESTAMPDIFF)
+            timestampDiffBind();
 
 		return this;
 	}
@@ -330,6 +341,21 @@
 			nargs = 4;
 			receiverType = receiverInterfaceType;
 		}
+		else if (operatorType == TIMESTAMPADD || operatorType == TIMESTAMPDIFF)
+        {
+            Object intervalType = leftOperand.getConstantValueAsObject();
+            if( SanityManager.DEBUG)
+                SanityManager.ASSERT( intervalType != null &amp;&amp; intervalType instanceof Integer,
+                                      "Invalid interval type used for " + operator);
+            mb.push( ((Integer) intervalType).intValue());
+            rightOperand.generateExpression( acb, mb);
+            mb.upCast( TernaryArgType[ operatorType][2]);
+            acb.getCurrentDateExpression( mb);
+			mb.getField(field);
+			nargs = 4;
+			receiverType = receiverInterfaceType;
+        }
+            
 		mb.callMethod(VMOpcode.INVOKEINTERFACE, receiverType, methodName, resultInterfaceType, nargs);
 
 		/*
@@ -781,6 +807,72 @@
 		return this;
 	}
 
+
+	/**
+	 * Bind TIMESTAMPADD expression.  
+	 *
+	 * @return	The new top of the expression tree.
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+
+ 	private ValueNode timestampAddBind() 
+			throws StandardException
+	{
+        if( ! bindParameter( rightOperand, Types.INTEGER))
+        {
+            int jdbcType = rightOperand.getTypeId().getJDBCTypeId();
+            if( jdbcType != Types.TINYINT &amp;&amp; jdbcType != Types.SMALLINT &amp;&amp;
+                jdbcType != Types.INTEGER &amp;&amp; jdbcType != Types.BIGINT)
+                throw StandardException.newException(SQLState.LANG_INVALID_FUNCTION_ARG_TYPE,
+                                                     rightOperand.getTypeId().getSQLTypeName(),
+                                                     ReuseFactory.getInteger( 2),
+                                                     operator);
+        }
+        bindDateTimeArg( receiver, 3);
+        setType(DataTypeDescriptor.getBuiltInDataTypeDescriptor( Types.TIMESTAMP));
+        return this;
+    } // end of timestampAddBind
+
+	/**
+	 * Bind TIMESTAMPDIFF expression.  
+	 *
+	 * @return	The new top of the expression tree.
+	 *
+	 * @exception StandardException		Thrown on error
+	 */
+
+ 	private ValueNode timestampDiffBind() 
+			throws StandardException
+	{
+        bindDateTimeArg( rightOperand, 2);
+        bindDateTimeArg( receiver, 3);
+        setType(DataTypeDescriptor.getBuiltInDataTypeDescriptor( Types.INTEGER));
+        return this;
+    } // End of timestampDiffBind
+
+    private void bindDateTimeArg( ValueNode arg, int argNumber) throws StandardException
+    {
+        if( ! bindParameter( arg, Types.TIMESTAMP))
+        {
+            if( ! arg.getTypeId().isDateTimeTimeStampTypeId())
+                throw StandardException.newException(SQLState.LANG_INVALID_FUNCTION_ARG_TYPE,
+                                                     arg.getTypeId().getSQLTypeName(),
+                                                     ReuseFactory.getInteger( argNumber),
+                                                     operator);
+        }
+    } // end of bindDateTimeArg
+
+    private boolean bindParameter( ValueNode arg, int jdbcType) throws StandardException
+    {
+        if( arg.isParameterNode() &amp;&amp; arg.getTypeId() == null)
+        {
+            ((ParameterNode) arg).setDescriptor( new DataTypeDescriptor(TypeId.getBuiltInTypeId( jdbcType), true));
+            return true;
+        }
+        return false;
+    } // end of bindParameter
+
 	public ValueNode getReceiver()
 	{
 		return receiver;
Index: java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj	(revision 170976)
+++ java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj	(working copy)
@@ -1547,6 +1547,12 @@
 		throw StandardException.newException(SQLState.LANG_IDENTIFIER_TOO_LONG, identifier, String.valueOf(identifier_length_limit));
     }
 
+    private ValueNode getJdbcIntervalNode( int intervalType) throws StandardException
+    {
+        return (ValueNode) nodeFactory.getNode( C_NodeTypes.INT_CONSTANT_NODE,
+                                                ReuseFactory.getInteger( intervalType),
+                                                getContextManager());
+    }
 }
 
 PARSER_END(SQLParser)
@@ -1851,11 +1857,22 @@
 |	&lt;SAVEPOINT: "savepoint"&gt;
 |	&lt;SCALE: "scale"&gt;
 |	&lt;SERIALIZABLE: "serializable"&gt;
+|	&lt;SQL_TSI_FRAC_SECOND: "sql_tsi_frac_second"&gt;
+|	&lt;SQL_TSI_SECOND: "sql_tsi_second"&gt;
+|	&lt;SQL_TSI_MINUTE: "sql_tsi_minute"&gt;
+|	&lt;SQL_TSI_HOUR: "sql_tsi_hour"&gt;
+|	&lt;SQL_TSI_DAY: "sql_tsi_day"&gt;
+|	&lt;SQL_TSI_WEEK: "sql_tsi_week"&gt;
+|	&lt;SQL_TSI_MONTH: "sql_tsi_month"&gt;
+|	&lt;SQL_TSI_QUARTER: "sql_tsi_quarter"&gt;
+|	&lt;SQL_TSI_YEAR: "sql_tsi_year"&gt;
 |	&lt;START: "start"&gt;
 |	&lt;STATEMENT: "statement"&gt;
 |	&lt;THEN: "then"&gt;
 |	&lt;TIME: "time"&gt;
 |	&lt;TIMESTAMP: "timestamp"&gt;
+|	&lt;TIMESTAMPADD: "timestampadd"&gt;
+|	&lt;TIMESTAMPDIFF: "timestampdiff"&gt;
 |	&lt;TRUNCATE: "truncate"&gt;
 |	&lt;TYPE: "type"&gt;
 |	&lt;UNCOMMITTED: "uncommitted"&gt;
@@ -5473,10 +5490,106 @@
 	{
 		return value;
 	}
+|
+    value = timestampArithmeticFuncion()
+    {
+        return value;
+    }
+}
 
+/*
+ * &lt;A NAME="timestampArithmeticFuncion"&gt;timestampArithmeticFuncion&lt;/A&gt;
+ */
+ValueNode
+timestampArithmeticFuncion() throws StandardException :
+{
+    ValueNode intervalType;
+    ValueNode tstamp1;
+    ValueNode tstamp2;
+    ValueNode count;
 }
+{
+    &lt;TIMESTAMPADD&gt; &lt;LEFT_PAREN&gt; intervalType = jdbcIntervalType() &lt;COMMA&gt;
+       count = additiveExpression(null,0,false) &lt;COMMA&gt;
+       tstamp1 = additiveExpression(null,0,false) &lt;RIGHT_PAREN&gt;
+    {
+        return (ValueNode) nodeFactory.getNode( C_NodeTypes.TIMESTAMP_ADD_FN_NODE,
+                                                tstamp1,
+                                                intervalType,
+                                                count,
+                                                ReuseFactory.getInteger( TernaryOperatorNode.TIMESTAMPADD),
+                                                null,
+                                                getContextManager());
+    }
+|
+    &lt;TIMESTAMPDIFF&gt; &lt;LEFT_PAREN&gt; intervalType = jdbcIntervalType() &lt;COMMA&gt;
+       tstamp1 = additiveExpression(null,0,false) &lt;COMMA&gt;
+       tstamp2 = additiveExpression(null,0,false) &lt;RIGHT_PAREN&gt;
+    {
+        return (ValueNode) nodeFactory.getNode( C_NodeTypes.TIMESTAMP_DIFF_FN_NODE,
+                                                tstamp2,
+                                                intervalType,
+                                                tstamp1,
+                                                ReuseFactory.getInteger( TernaryOperatorNode.TIMESTAMPDIFF),
+                                                null,
+                                                getContextManager());
+    }
+}       
 
 /*
+ * &lt;A NAME="jdbcIntervalType"&gt;jdbcIntervalType&lt;/A&gt;
+ */
+ValueNode jdbcIntervalType() throws StandardException :
+{
+}
+{
+    &lt;SQL_TSI_FRAC_SECOND&gt;
+    {
+        return getJdbcIntervalNode( DateTimeDataValue.FRAC_SECOND_INTERVAL);
+    }
+|
+    &lt;SQL_TSI_SECOND&gt;
+    {
+        return getJdbcIntervalNode( DateTimeDataValue.SECOND_INTERVAL);
+    }
+|
+    &lt;SQL_TSI_MINUTE&gt;
+    {
+        return getJdbcIntervalNode( DateTimeDataValue.MINUTE_INTERVAL);
+    }
+|
+    &lt;SQL_TSI_HOUR&gt;
+    {
+        return getJdbcIntervalNode( DateTimeDataValue.HOUR_INTERVAL);
+    }
+|
+    &lt;SQL_TSI_DAY&gt;
+    {
+        return getJdbcIntervalNode( DateTimeDataValue.DAY_INTERVAL);
+    }
+|
+    &lt;SQL_TSI_WEEK&gt;
+    {
+        return getJdbcIntervalNode( DateTimeDataValue.WEEK_INTERVAL);
+    }
+|
+    &lt;SQL_TSI_MONTH&gt;
+    {
+        return getJdbcIntervalNode( DateTimeDataValue.MONTH_INTERVAL);
+    }
+|
+    &lt;SQL_TSI_QUARTER&gt;
+    {
+        return getJdbcIntervalNode( DateTimeDataValue.QUARTER_INTERVAL);
+    }
+|
+    &lt;SQL_TSI_YEAR&gt;
+    {
+        return getJdbcIntervalNode( DateTimeDataValue.YEAR_INTERVAL);
+    }
+}
+
+/*
  * &lt;A NAME="numericValueFunction"&gt;numericValueFunction&lt;/A&gt;
  */
 ValueNode
@@ -11395,6 +11508,15 @@
 	|	tok = &lt;SHARE&gt;
 	|   tok = &lt;SPECIFIC&gt;
 	|	tok = &lt;SQLID&gt;
+	|	tok = &lt;SQL_TSI_FRAC_SECOND&gt;
+	|	tok = &lt;SQL_TSI_SECOND&gt;
+	|	tok = &lt;SQL_TSI_MINUTE&gt;
+	|	tok = &lt;SQL_TSI_HOUR&gt;
+	|	tok = &lt;SQL_TSI_DAY&gt;
+	|	tok = &lt;SQL_TSI_WEEK&gt;
+	|	tok = &lt;SQL_TSI_MONTH&gt;
+	|	tok = &lt;SQL_TSI_QUARTER&gt;
+	|	tok = &lt;SQL_TSI_YEAR&gt;
     |   tok = &lt;SQRT&gt;
     |       tok = &lt;STABILITY&gt;
 	|	tok = &lt;START&gt;
@@ -11404,6 +11526,8 @@
 	|	tok = &lt;THEN&gt;
 	|	tok = &lt;TIME&gt;
 	|	tok = &lt;TIMESTAMP&gt;
+	|	tok = &lt;TIMESTAMPADD&gt;
+	|	tok = &lt;TIMESTAMPDIFF&gt;
     |   tok = &lt;TRIGGER&gt;
 	|	tok = &lt;TRUNCATE&gt;
 	|	tok = &lt;TS&gt;
Index: java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java
===================================================================
--- java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java	(revision 170976)
+++ java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java	(working copy)
@@ -186,7 +186,9 @@
 	// UNUSED static final int BOOLEAN_NODE = 155;
 	static final int DROP_ALIAS_NODE = 156;
     static final int INTERSECT_OR_EXCEPT_NODE = 157;
-	// 158 - 185 available
+	// 158 - 183 available
+    static final int TIMESTAMP_ADD_FN_NODE = 184;
+    static final int TIMESTAMP_DIFF_FN_NODE = 185;
 	static final int MODIFY_COLUMN_TYPE_NODE = 186;
 	static final int MODIFY_COLUMN_CONSTRAINT_NODE = 187;
     static final int ABSOLUTE_OPERATOR_NODE = 188;
Index: java/engine/org/apache/derby/iapi/types/DateTimeDataValue.java
===================================================================
--- java/engine/org/apache/derby/iapi/types/DateTimeDataValue.java	(revision 170976)
+++ java/engine/org/apache/derby/iapi/types/DateTimeDataValue.java	(working copy)
@@ -31,6 +31,17 @@
 	public static final int MINUTE_FIELD = 4;
 	public static final int SECOND_FIELD = 5;
 
+    // The JDBC interval types
+    public static final int FRAC_SECOND_INTERVAL = 0;
+    public static final int SECOND_INTERVAL = 1;
+    public static final int MINUTE_INTERVAL = 2;
+    public static final int HOUR_INTERVAL = 3;
+    public static final int DAY_INTERVAL = 4;
+    public static final int WEEK_INTERVAL = 5;
+    public static final int MONTH_INTERVAL = 6;
+    public static final int QUARTER_INTERVAL = 7;
+    public static final int YEAR_INTERVAL = 8;
+
 	/**
 	 * Get the year number out of a date.
 	 *
@@ -108,5 +119,46 @@
 	 */
 	NumberDataValue getSeconds(NumberDataValue result)
 							throws StandardException;
+
+    /**
+     * Add a number of intervals to a datetime value. Implements the JDBC escape TIMESTAMPADD function.
+     *
+     * @param intervalType One of FRAC_SECOND_INTERVAL, SECOND_INTERVAL, MINUTE_INTERVAL, HOUR_INTERVAL,
+     *                     DAY_INTERVAL, WEEK_INTERVAL, MONTH_INTERVAL, QUARTER_INTERVAL, or YEAR_INTERVAL
+     * @param intervalCount The number of intervals to add
+     * @param currentDate Used to convert time to timestamp
+     * @param resultHolder If non-null a DateTimeDataValue that can be used to hold the result. If null then
+     *                     generate a new holder
+     *
+     * @return startTime + intervalCount intervals, as a timestamp
+     *
+     * @exception StandardException
+     */
+    DateTimeDataValue timestampAdd( int intervalType,
+                                    NumberDataValue intervalCount,
+                                    java.sql.Date currentDate,
+                                    DateTimeDataValue resultHolder)
+        throws StandardException;
+
+    /**
+     * Finds the difference between two datetime values as a number of intervals. Implements the JDBC
+     * TIMESTAMPDIFF escape function.
+     *
+     * @param intervalType One of FRAC_SECOND_INTERVAL, SECOND_INTERVAL, MINUTE_INTERVAL, HOUR_INTERVAL,
+     *                     DAY_INTERVAL, WEEK_INTERVAL, MONTH_INTERVAL, QUARTER_INTERVAL, or YEAR_INTERVAL
+     * @param time1
+     * @param currentDate Used to convert time to timestamp
+     * @param resultHolder If non-null a DateTimeDataValue that can be used to hold the result. If null then
+     *                     generate a new holder
+     *
+     * @return the number of intervals by which this datetime is greater than time1
+     *
+     * @exception StandardException
+     */
+    NumberDataValue timestampDiff( int intervalType,
+                                   DateTimeDataValue time1,
+                                   java.sql.Date currentDate,
+                                   NumberDataValue resultHolder)
+        throws StandardException;
 }
 
Index: java/engine/org/apache/derby/iapi/types/SQLDate.java
===================================================================
--- java/engine/org/apache/derby/iapi/types/SQLDate.java	(revision 170976)
+++ java/engine/org/apache/derby/iapi/types/SQLDate.java	(working copy)
@@ -997,4 +997,56 @@
             throw se;
         }
     } // end of computeDateFunction
+
+    /**
+     * Add a number of intervals to a datetime value. Implements the JDBC escape TIMESTAMPADD function.
+     *
+     * @param intervalType One of FRAC_SECOND_INTERVAL, SECOND_INTERVAL, MINUTE_INTERVAL, HOUR_INTERVAL,
+     *                     DAY_INTERVAL, WEEK_INTERVAL, MONTH_INTERVAL, QUARTER_INTERVAL, or YEAR_INTERVAL
+     * @param intervalCount The number of intervals to add
+     * @param currentDate Used to convert time to timestamp
+     * @param resultHolder If non-null a DateTimeDataValue that can be used to hold the result. If null then
+     *                     generate a new holder
+     *
+     * @return startTime + intervalCount intervals, as a timestamp
+     *
+     * @exception StandardException
+     */
+    public DateTimeDataValue timestampAdd( int intervalType,
+                                           NumberDataValue intervalCount,
+                                           java.sql.Date currentDate,
+                                           DateTimeDataValue resultHolder)
+        throws StandardException
+    {
+        return toTimestamp().timestampAdd( intervalType, intervalCount, currentDate, resultHolder);
+    }
+
+    private SQLTimestamp toTimestamp() throws StandardException
+    {
+        return new SQLTimestamp( getEncodedDate(), 0, 0);
+    }
+    
+    /**
+     * Finds the difference between two datetime values as a number of intervals. Implements the JDBC
+     * TIMESTAMPDIFF escape function.
+     *
+     * @param intervalType One of FRAC_SECOND_INTERVAL, SECOND_INTERVAL, MINUTE_INTERVAL, HOUR_INTERVAL,
+     *                     DAY_INTERVAL, WEEK_INTERVAL, MONTH_INTERVAL, QUARTER_INTERVAL, or YEAR_INTERVAL
+     * @param time1
+     * @param currentDate Used to convert time to timestamp
+     * @param resultHolder If non-null a NumberDataValue that can be used to hold the result. If null then
+     *                     generate a new holder
+     *
+     * @return the number of intervals by which this datetime is greater than time1
+     *
+     * @exception StandardException
+     */
+    public NumberDataValue timestampDiff( int intervalType,
+                                          DateTimeDataValue time1,
+                                          java.sql.Date currentDate,
+                                          NumberDataValue resultHolder)
+        throws StandardException
+    {
+        return toTimestamp().timestampDiff( intervalType, time1, currentDate, resultHolder);
+    }
 }
Index: java/engine/org/apache/derby/iapi/types/SQLTime.java
===================================================================
--- java/engine/org/apache/derby/iapi/types/SQLTime.java	(revision 170976)
+++ java/engine/org/apache/derby/iapi/types/SQLTime.java	(working copy)
@@ -965,5 +965,59 @@
 		currentCal.setTime(value);
 		return computeEncodedTime(currentCal);
 	}
+
+    /**
+     * Add a number of intervals to a datetime value. Implements the JDBC escape TIMESTAMPADD function.
+     *
+     * @param intervalType One of FRAC_SECOND_INTERVAL, SECOND_INTERVAL, MINUTE_INTERVAL, HOUR_INTERVAL,
+     *                     DAY_INTERVAL, WEEK_INTERVAL, MONTH_INTERVAL, QUARTER_INTERVAL, or YEAR_INTERVAL
+     * @param intervalCount The number of intervals to add
+     * @param currentDate Used to convert time to timestamp
+     * @param resultHolder If non-null a DateTimeDataValue that can be used to hold the result. If null then
+     *                     generate a new holder
+     *
+     * @return startTime + intervalCount intervals, as a timestamp
+     *
+     * @exception StandardException
+     */
+    public DateTimeDataValue timestampAdd( int intervalType,
+                                           NumberDataValue intervalCount,
+                                           java.sql.Date currentDate,
+                                           DateTimeDataValue resultHolder)
+        throws StandardException
+    {
+        return toTimestamp( currentDate).timestampAdd( intervalType, intervalCount, currentDate, resultHolder);
+    }
+
+    private SQLTimestamp toTimestamp(java.sql.Date currentDate) throws StandardException
+    {
+        return new SQLTimestamp( SQLDate.computeEncodedDate( currentDate, (Calendar) null),
+                                 getEncodedTime(),
+                                 0 /* nanoseconds */);
+    }
+    
+    /**
+     * Finds the difference between two datetime values as a number of intervals. Implements the JDBC
+     * TIMESTAMPDIFF escape function.
+     *
+     * @param intervalType One of FRAC_SECOND_INTERVAL, SECOND_INTERVAL, MINUTE_INTERVAL, HOUR_INTERVAL,
+     *                     DAY_INTERVAL, WEEK_INTERVAL, MONTH_INTERVAL, QUARTER_INTERVAL, or YEAR_INTERVAL
+     * @param time1
+     * @param currentDate Used to convert time to timestamp
+     * @param resultHolder If non-null a NumberDataValue that can be used to hold the result. If null then
+     *                     generate a new holder
+     *
+     * @return the number of intervals by which this datetime is greater than time1
+     *
+     * @exception StandardException
+     */
+    public NumberDataValue timestampDiff( int intervalType,
+                                          DateTimeDataValue time1,
+                                          java.sql.Date currentDate,
+                                          NumberDataValue resultHolder)
+        throws StandardException
+    {
+        return toTimestamp( currentDate ).timestampDiff( intervalType, time1, currentDate, resultHolder);
+    }
 }
 
Index: java/engine/org/apache/derby/iapi/types/SQLTimestamp.java
===================================================================
--- java/engine/org/apache/derby/iapi/types/SQLTimestamp.java	(revision 170976)
+++ java/engine/org/apache/derby/iapi/types/SQLTimestamp.java	(working copy)
@@ -41,12 +41,11 @@
 import org.apache.derby.iapi.services.i18n.LocaleFinder;
 import org.apache.derby.iapi.services.cache.ClassSize;
 import org.apache.derby.iapi.util.StringUtil;
+import org.apache.derby.iapi.util.ReuseFactory;
 
 import org.apache.derby.iapi.types.SQLDouble;
 import org.apache.derby.iapi.types.SQLTime;
 
-
-
 import java.sql.Date;
 import java.sql.Time;
 import java.sql.Timestamp;
@@ -87,6 +86,8 @@
 
     static final int MAX_FRACTION_DIGITS = 6; // Only microsecond resolution on conversion to/from strings
     static final int FRACTION_TO_NANO = 1000; // 10**(9 - MAX_FRACTION_DIGITS)
+
+    static final int ONE_BILLION = 1000000000;
     
 	private int	encodedDate;
 	private int	encodedTime;
@@ -439,7 +440,7 @@
 		setValue(value, (Calendar) null);
 	}
 
-	private SQLTimestamp(int encodedDate, int encodedTime, int nanos) {
+	SQLTimestamp(int encodedDate, int encodedTime, int nanos) {
 
 		this.encodedDate = encodedDate;
 		this.encodedTime = encodedTime;
@@ -909,18 +910,24 @@
     {
         if( currentCal == null)
             currentCal = new GregorianCalendar();
-		currentCal.set(Calendar.YEAR, SQLDate.getYear(encodedDate));
-		/* Note calendar month is zero based so we subtract 1*/
-		currentCal.set(Calendar.MONTH, (SQLDate.getMonth(encodedDate)-1));
-		currentCal.set(Calendar.DATE, SQLDate.getDay(encodedDate));
-		currentCal.set(Calendar.HOUR_OF_DAY, SQLTime.getHour(encodedTime));
-		currentCal.set(Calendar.MINUTE, SQLTime.getMinute(encodedTime));
-		currentCal.set(Calendar.SECOND, SQLTime.getSecond(encodedTime));
-		currentCal.set(Calendar.MILLISECOND, 0);
+        setCalendar( currentCal);
 		Timestamp t = new Timestamp(currentCal.getTime().getTime());
 		t.setNanos(nanos);
 		return t;
 	}
+
+    private void setCalendar( Calendar cal)
+    {
+		cal.set(Calendar.YEAR, SQLDate.getYear(encodedDate));
+		/* Note calendar month is zero based so we subtract 1*/
+		cal.set(Calendar.MONTH, (SQLDate.getMonth(encodedDate)-1));
+		cal.set(Calendar.DATE, SQLDate.getDay(encodedDate));
+		cal.set(Calendar.HOUR_OF_DAY, SQLTime.getHour(encodedTime));
+		cal.set(Calendar.MINUTE, SQLTime.getMinute(encodedTime));
+		cal.set(Calendar.SECOND, SQLTime.getSecond(encodedTime));
+		cal.set(Calendar.MILLISECOND, 0);
+    } // end of setCalendar
+        
 	/**
 	 * Set the encoded values for the timestamp
 	 *
@@ -1052,4 +1059,308 @@
         }
         return retVal;
     } // end of parseDateTimeInteger
+
+    /**
+     * Add a number of intervals to a datetime value. Implements the JDBC escape TIMESTAMPADD function.
+     *
+     * @param intervalType One of FRAC_SECOND_INTERVAL, SECOND_INTERVAL, MINUTE_INTERVAL, HOUR_INTERVAL,
+     *                     DAY_INTERVAL, WEEK_INTERVAL, MONTH_INTERVAL, QUARTER_INTERVAL, or YEAR_INTERVAL
+     * @param intervalCount The number of intervals to add
+     * @param currentDate Used to convert time to timestamp
+     * @param resultHolder If non-null a DateTimeDataValue that can be used to hold the result. If null then
+     *                     generate a new holder
+     *
+     * @return startTime + intervalCount intervals, as a timestamp
+     *
+     * @exception StandardException
+     */
+    public DateTimeDataValue timestampAdd( int intervalType,
+                                           NumberDataValue count,
+                                           java.sql.Date currentDate,
+                                           DateTimeDataValue resultHolder)
+        throws StandardException
+    {
+        if( resultHolder == null)
+            resultHolder = new SQLTimestamp();
+        SQLTimestamp tsResult = (SQLTimestamp) resultHolder;
+        if( isNull() || count.isNull())
+        {
+            tsResult.restoreToNull();
+            return resultHolder;
+        }
+        tsResult.setFrom( this);
+        int intervalCount = count.getInt();
+        
+        switch( intervalType)
+        {
+        case FRAC_SECOND_INTERVAL:
+            // The interval is nanoseconds. Do the computation in long to avoid overflow.
+            long nanos = this.nanos + intervalCount;
+            if( nanos &gt;= 0 &amp;&amp; nanos &lt; ONE_BILLION)
+                tsResult.nanos = (int) nanos;
+            else
+            {
+                int secondsInc = (int)(nanos/ONE_BILLION);
+                if( nanos &gt;= 0)
+                    tsResult.nanos = (int) (nanos % ONE_BILLION);
+                else
+                {
+                    secondsInc--;
+                    nanos -= secondsInc * (long)ONE_BILLION; // 0 &lt;= nanos &lt; ONE_BILLION
+                    tsResult.nanos = (int) nanos;
+                }
+                addInternal( Calendar.SECOND, secondsInc, tsResult);
+            }
+            break;
+
+        case SECOND_INTERVAL:
+            addInternal( Calendar.SECOND, intervalCount, tsResult);
+            break;
+
+        case MINUTE_INTERVAL:
+            addInternal( Calendar.MINUTE, intervalCount, tsResult);
+            break;
+
+        case HOUR_INTERVAL:
+            addInternal( Calendar.HOUR, intervalCount, tsResult);
+            break;
+
+        case DAY_INTERVAL:
+            addInternal( Calendar.DATE, intervalCount, tsResult);
+            break;
+
+        case WEEK_INTERVAL:
+            addInternal( Calendar.DATE, intervalCount*7, tsResult);
+            break;
+
+        case MONTH_INTERVAL:
+            addInternal( Calendar.MONTH, intervalCount, tsResult);
+            break;
+
+        case QUARTER_INTERVAL:
+            addInternal( Calendar.MONTH, intervalCount*3, tsResult);
+            break;
+
+        case YEAR_INTERVAL:
+            addInternal( Calendar.YEAR, intervalCount, tsResult);
+            break;
+
+        default:
+            throw StandardException.newException( SQLState.LANG_INVALID_FUNCTION_ARGUMENT,
+                                                  ReuseFactory.getInteger( intervalType),
+                                                  "TIMESTAMPADD");
+        }
+        return tsResult;
+    } // end of timestampAdd
+
+    private void addInternal( int calIntervalType, int count, SQLTimestamp tsResult) throws StandardException
+    {
+        Calendar cal = new GregorianCalendar();
+        setCalendar( cal);
+        try
+        {
+            cal.add( calIntervalType, count);
+            tsResult.encodedTime = SQLTime.computeEncodedTime( cal);
+            tsResult.encodedDate = SQLDate.computeEncodedDate( cal);
+        }
+        catch( StandardException se)
+        {
+            String state = se.getSQLState();
+            if( state != null &amp;&amp; state.length() &gt; 0 &amp;&amp; SQLState.LANG_DATE_RANGE_EXCEPTION.startsWith( state))
+            {
+                throw StandardException.newException(SQLState.LANG_OUTSIDE_RANGE_FOR_DATATYPE, "TIMESTAMP");
+            }
+            throw se;
+        }
+    } // end of addInternal
+
+    /**
+     * Finds the difference between two datetime values as a number of intervals. Implements the JDBC
+     * TIMESTAMPDIFF escape function.
+     *
+     * @param intervalType One of FRAC_SECOND_INTERVAL, SECOND_INTERVAL, MINUTE_INTERVAL, HOUR_INTERVAL,
+     *                     DAY_INTERVAL, WEEK_INTERVAL, MONTH_INTERVAL, QUARTER_INTERVAL, or YEAR_INTERVAL
+     * @param time1
+     * @param currentDate Used to convert time to timestamp
+     * @param resultHolder If non-null a NumberDataValue that can be used to hold the result. If null then
+     *                     generate a new holder
+     *
+     * @return the number of intervals by which this datetime is greater than time1
+     *
+     * @exception StandardException
+     */
+    public NumberDataValue timestampDiff( int intervalType,
+                                          DateTimeDataValue time1,
+                                          java.sql.Date currentDate,
+                                          NumberDataValue resultHolder)
+        throws StandardException
+    {
+        if( resultHolder == null)
+            resultHolder = new SQLInteger();
+ 
+       if( isNull() || time1.isNull())
+        {
+            resultHolder.setToNull();
+            return resultHolder;
+        }
+        
+        SQLTimestamp ts1 = promote( time1, currentDate);
+
+        /* Years, months, and quarters are difficult because their lengths are not constant.
+         * The other intervals are relatively easy (because we ignore leap seconds).
+         */
+        Calendar cal = new GregorianCalendar();
+        setCalendar( cal);
+        long thisInSeconds = cal.getTime().getTime()/1000;
+        ts1.setCalendar( cal);
+        long ts1InSeconds = cal.getTime().getTime()/1000;
+        long secondsDiff = thisInSeconds - ts1InSeconds;
+        int nanosDiff = nanos - ts1.nanos;
+        // Normalize secondsDiff and nanosDiff so that they are both &lt;= 0 or both &gt;= 0.
+        if( nanosDiff &lt; 0 &amp;&amp; secondsDiff &gt; 0)
+        {
+            secondsDiff--;
+            nanosDiff += ONE_BILLION;
+        }
+        else if( nanosDiff &gt; 0 &amp;&amp; secondsDiff &lt; 0)
+        {
+            secondsDiff++;
+            nanosDiff -= ONE_BILLION;
+        }
+        long ldiff = 0;
+        
+        switch( intervalType)
+        {
+        case FRAC_SECOND_INTERVAL:
+            if( secondsDiff &gt; Integer.MAX_VALUE/ONE_BILLION || secondsDiff &lt; Integer.MIN_VALUE/ONE_BILLION)
+                throw StandardException.newException(SQLState.LANG_OUTSIDE_RANGE_FOR_DATATYPE, "INTEGER");
+            ldiff = secondsDiff*ONE_BILLION + nanosDiff;
+            break;
+            
+        case SECOND_INTERVAL:
+            ldiff = secondsDiff;
+            break;
+            
+        case MINUTE_INTERVAL:
+            ldiff = secondsDiff/60;
+            break;
+
+        case HOUR_INTERVAL:
+            ldiff = secondsDiff/(60*60);
+            break;
+            
+        case DAY_INTERVAL:
+            ldiff = secondsDiff/(24*60*60);
+            break;
+            
+        case WEEK_INTERVAL:
+            ldiff = secondsDiff/(7*24*60*60);
+            break;
+
+        case QUARTER_INTERVAL:
+        case MONTH_INTERVAL:
+            // Make a conservative guess and increment until we overshoot.
+            if( Math.abs( secondsDiff) &gt; 366*24*60*60) // Certainly more than a year
+                ldiff = 12*(secondsDiff/(366*24*60*60));
+            else
+                ldiff = secondsDiff/(31*24*60*60);
+            if( secondsDiff &gt;= 0)
+            {
+                if (ldiff &gt;= Integer.MAX_VALUE)
+                    throw StandardException.newException(SQLState.LANG_OUTSIDE_RANGE_FOR_DATATYPE, "INTEGER");
+                // cal holds the time for time1
+                cal.add( Calendar.MONTH, (int) (ldiff + 1));
+                for(;;)
+                {
+                    if( cal.getTime().getTime()/1000 &gt; thisInSeconds)
+                        break;
+                    cal.add( Calendar.MONTH, 1);
+                    ldiff++;
+                }
+            }
+            else
+            {
+                if (ldiff &lt;= Integer.MIN_VALUE)
+                    throw StandardException.newException(SQLState.LANG_OUTSIDE_RANGE_FOR_DATATYPE, "INTEGER");
+                // cal holds the time for time1
+                cal.add( Calendar.MONTH, (int) (ldiff - 1));
+                for(;;)
+                {
+                    if( cal.getTime().getTime()/1000 &lt; thisInSeconds)
+                        break;
+                    cal.add( Calendar.MONTH, -1);
+                    ldiff--;
+                }
+            }
+            if( intervalType == QUARTER_INTERVAL)
+                ldiff = ldiff/3;
+            break;
+
+        case YEAR_INTERVAL:
+            // Make a conservative guess and increment until we overshoot.
+            ldiff = secondsDiff/(366*24*60*60);
+            if( secondsDiff &gt;= 0)
+            {
+                if (ldiff &gt;= Integer.MAX_VALUE)
+                    throw StandardException.newException(SQLState.LANG_OUTSIDE_RANGE_FOR_DATATYPE, "INTEGER");
+                // cal holds the time for time1
+                cal.add( Calendar.YEAR, (int) (ldiff + 1));
+                for(;;)
+                {
+                    if( cal.getTime().getTime()/1000 &gt; thisInSeconds)
+                        break;
+                    cal.add( Calendar.YEAR, 1);
+                    ldiff++;
+                }
+            }
+            else
+            {
+                if (ldiff &lt;= Integer.MIN_VALUE)
+                    throw StandardException.newException(SQLState.LANG_OUTSIDE_RANGE_FOR_DATATYPE, "INTEGER");
+                // cal holds the time for time1
+                cal.add( Calendar.YEAR, (int) (ldiff - 1));
+                for(;;)
+                {
+                    if( cal.getTime().getTime()/1000 &lt; thisInSeconds)
+                        break;
+                    cal.add( Calendar.YEAR, -1);
+                    ldiff--;
+                }
+            }
+            break;
+
+        default:
+            throw StandardException.newException( SQLState.LANG_INVALID_FUNCTION_ARGUMENT,
+                                                  ReuseFactory.getInteger( intervalType),
+                                                  "TIMESTAMPDIFF");
+        }
+		if (ldiff &gt; Integer.MAX_VALUE || ldiff &lt; Integer.MIN_VALUE)
+			throw StandardException.newException(SQLState.LANG_OUTSIDE_RANGE_FOR_DATATYPE, "INTEGER");
+        resultHolder.setValue( (int) ldiff);
+        return resultHolder;
+    } // end of timestampDiff
+
+    /**
+     * Promotes a DateTimeDataValue to a timestamp.
+     *
+     * @param datetime
+     *
+     * @return the corresponding timestamp, using the current date if datetime is a time,
+     *         or time 00:00:00 if datetime is a date.
+     *
+     * @exception StandardException
+     */
+    static SQLTimestamp promote( DateTimeDataValue dateTime, java.sql.Date currentDate) throws StandardException
+    {
+        if( dateTime instanceof SQLTimestamp)
+            return (SQLTimestamp) dateTime;
+        else if( dateTime instanceof SQLTime)
+            return new SQLTimestamp( SQLDate.computeEncodedDate( currentDate, (Calendar) null),
+                                    ((SQLTime) dateTime).getEncodedTime(),
+                                    0 /* nanoseconds */);
+        else if( dateTime instanceof SQLDate)
+            return new SQLTimestamp( ((SQLDate) dateTime).getEncodedDate(), 0, 0);
+        else
+            return new SQLTimestamp( dateTime.getTimestamp( new GregorianCalendar()));
+    } // end of promote
 }
Index: java/engine/org/apache/derby/iapi/reference/SQLState.java
===================================================================
--- java/engine/org/apache/derby/iapi/reference/SQLState.java	(revision 170976)
+++ java/engine/org/apache/derby/iapi/reference/SQLState.java	(working copy)
@@ -752,7 +752,7 @@
 	String LANG_NOT_STORABLE                                           = "42821";
 	String LANG_NULL_RESULT_SET_META_DATA                              = "42X43";
 	String LANG_INVALID_COLUMN_LENGTH                                  = "42X44";
-	// = "42X45";
+	String LANG_INVALID_FUNCTION_ARG_TYPE                              = "42X45";
 	// = "42X46";
 	// = "42X47";
 	String LANG_INVALID_PRECISION                                      = "42X48";
Index: java/engine/org/apache/derby/loc/messages_en.properties
===================================================================
--- java/engine/org/apache/derby/loc/messages_en.properties	(revision 170976)
+++ java/engine/org/apache/derby/loc/messages_en.properties	(working copy)
@@ -458,7 +458,7 @@
 42821=Columns of type ''{0}'' cannot hold values of type ''{1}''. 
 42X43=The ResultSetMetaData returned for the class/object ''{0}'' was null. The ResultSetMetaData must be non-null in order to use this class as an external virtual table.
 42X44=Invalid length ''{0}'' in column specification.
-# 42X45=
+42X45={0} is an invalid type for argument number {1} of {2}.
 # 42X46=
 # 42X47=
 42X48=Value ''{1}'' is not a valid precision for {0}.
Index: java/testing/org/apache/derbyTesting/functionTests/tests/lang/timestampArith.java
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/tests/lang/timestampArith.java	(revision 0)
+++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/timestampArith.java	(revision 0)
@@ -0,0 +1,789 @@
+/*
+
+   Derby - Class org.apache.derbyTesting.functionTests.tests.lang.timestampArith
+
+   Copyright 2005 The Apache Software Foundation or its licensors, as applicable.
+
+   Licensed under the Apache License, Version 2.0 (the "License");
+   you may not use this file except in compliance with the License.
+   You may obtain a copy of the License at
+
+      <a class="moz-txt-link-freetext" href="http://www.apache.org/licenses/LICENSE-2.0">http://www.apache.org/licenses/LICENSE-2.0</a>
+
+   Unless required by applicable law or agreed to in writing, software
+   distributed under the License is distributed on an "AS IS" BASIS,
+   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+   See the License for the specific language governing permissions and
+   limitations under the License.
+
+ */
+
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import org.apache.derby.tools.ij;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.Statement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Timestamp;
+import java.sql.Types;
+
+import java.util.Calendar;
+
+/**
+ * Test the JDBC TIMESTAMPADD and TIMESTAMPDIFF escape functions.
+ *
+ * Things to test:
+ *   + Test each interval type with timestamp, date, and time inputs.
+ *   + Test diff with all 9 combinations of datetime input types (timestamp - timestamp, timestamp - date, etc).
+ *   + Test PreparedStatements with parameters, '?', in each argument, and Statements. (Statements are prepared
+ *     internally so we do not also have to test PrepardStatements without parameters).
+ *   + Test with null inputs.
+ *   + Test with input string that is convertible to timestamp.
+ *   + Test with invalid interval type.
+ *   + Test with invalid arguments in the date time arguments.
+ *   + Test TIMESTAMPADD with an invalid type in the count argument.
+ *   + Test overflow cases.
+ */
+public class timestampArith
+{
+    private static final int FRAC_SECOND_INTERVAL = 0;
+    private static final int SECOND_INTERVAL = 1;
+    private static final int MINUTE_INTERVAL = 2;
+    private static final int HOUR_INTERVAL = 3;
+    private static final int DAY_INTERVAL = 4;
+    private static final int WEEK_INTERVAL = 5;
+    private static final int MONTH_INTERVAL = 6;
+    private static final int QUARTER_INTERVAL = 7;
+    private static final int YEAR_INTERVAL = 8;
+    private static final String[] intervalJdbcNames =
+    {"SQL_TSI_FRAC_SECOND", "SQL_TSI_SECOND", "SQL_TSI_MINUTE", "SQL_TSI_HOUR",
+     "SQL_TSI_DAY", "SQL_TSI_WEEK", "SQL_TSI_MONTH", "SQL_TSI_QUARTER", "SQL_TSI_YEAR"};
+
+    private static final int ONE_BILLION = 1000000000;
+
+    int errorCount = 0;
+    private Connection conn;
+    private PreparedStatement[] tsAddPS = new PreparedStatement[intervalJdbcNames.length];
+    private PreparedStatement[] tsDiffPS = new PreparedStatement[intervalJdbcNames.length];
+    private Statement stmt;
+    private static final String TODAY;
+    private static final String TOMORROW;
+    private static final String YEAR_FROM_TOMORROW;
+    private static final String YEAR_FROM_TODAY;
+    private static final String YESTERDAY;
+    private static final String WEEK_FROM_TODAY;
+    static {
+        Calendar cal = Calendar.getInstance();
+        // Make sure that we are not so close to midnight that TODAY might be yesterday before
+        // we are finished using it.
+        while( cal.get( Calendar.HOUR) == 23 &amp;&amp; cal.get( Calendar.MINUTE) == 58)
+        {
+            try
+            {
+                Thread.sleep( (60 - cal.get( Calendar.SECOND))*1000);
+            }
+            catch( InterruptedException ie) {};
+            cal = Calendar.getInstance();
+        }
+        TODAY = isoFormatDate( cal);
+        cal.add( Calendar.DATE, -1);
+        YESTERDAY = isoFormatDate( cal);
+        cal.add( Calendar.DATE, 2);
+        TOMORROW = isoFormatDate( cal);
+        cal.add( Calendar.YEAR, 1);
+        YEAR_FROM_TOMORROW = isoFormatDate( cal);
+        cal.add( Calendar.DATE, -1);
+        YEAR_FROM_TODAY = isoFormatDate( cal);
+        cal.add( Calendar.YEAR, -1); // today
+        cal.add( Calendar.DATE, 7);
+        WEEK_FROM_TODAY = isoFormatDate( cal);
+    }
+    
+    private static String isoFormatDate( Calendar cal)
+    {
+        StringBuffer sb = new StringBuffer();
+        String s = String.valueOf( cal.get( Calendar.YEAR));
+        for( int i = s.length(); i &lt; 4; i++)
+            sb.append( '0');
+        sb.append( s);
+        sb.append( '-');
+
+        s = String.valueOf( cal.get( Calendar.MONTH) + 1);
+        for( int i = s.length(); i &lt; 2; i++)
+            sb.append( '0');
+        sb.append( s);
+        sb.append( '-');
+
+        s = String.valueOf( cal.get( Calendar.DAY_OF_MONTH));
+        for( int i = s.length(); i &lt; 2; i++)
+            sb.append( '0');
+        sb.append( s);
+
+        return sb.toString();
+    }
+    
+    private final OneTest[] tests =
+    {
+        // timestamp - timestamp
+        new OneDiffTest( FRAC_SECOND_INTERVAL, ts( "2005-05-10 08:25:00"), ts("2005-05-10 08:25:00.000001"), 1000,
+                         null, null),
+        new OneDiffTest( SECOND_INTERVAL, ts( "2005-05-10 08:25:01"), ts("2005-05-10 08:25:00"), -1, null, null),
+        new OneDiffTest( SECOND_INTERVAL, ts( "2005-05-10 08:25:00.1"), ts("2005-05-10 08:25:00"), 0, null, null),
+        new OneDiffTest( SECOND_INTERVAL, ts( "2005-05-10 08:25:00"), ts("2005-05-10 08:26:00"), 60, null, null),
+        new OneDiffTest( MINUTE_INTERVAL, ts( "2005-05-11 08:25:00"), ts("2005-05-10 08:25:00"), -24*60, null, null),
+        new OneDiffTest( HOUR_INTERVAL, ts("2005-05-10 08:25:00"), ts( "2005-05-11 08:25:00"), 24, null, null),
+        new OneDiffTest( DAY_INTERVAL, ts("2005-05-10 08:25:00"), ts( "2005-05-11 08:25:00"), 1, null, null),
+        new OneDiffTest( DAY_INTERVAL, ts("2005-05-10 08:25:01"), ts( "2005-05-11 08:25:00"), 0, null, null),
+        new OneDiffTest( WEEK_INTERVAL, ts("2005-02-23 08:25:00"), ts( "2005-03-01 08:25:00"), 0, null, null),
+        new OneDiffTest( MONTH_INTERVAL, ts("2005-02-23 08:25:00"), ts( "2005-03-23 08:25:00"), 1, null, null),
+        new OneDiffTest( MONTH_INTERVAL, ts("2005-02-23 08:25:01"), ts( "2005-03-23 08:25:00"), 0, null, null),
+        new OneDiffTest( QUARTER_INTERVAL, ts("2005-02-23 08:25:00"), ts( "2005-05-23 08:25:00"), 1, null, null),
+        new OneDiffTest( QUARTER_INTERVAL, ts("2005-02-23 08:25:01"), ts( "2005-05-23 08:25:00"), 0, null, null),
+        new OneDiffTest( YEAR_INTERVAL, ts("2005-02-23 08:25:00"), ts( "2005-05-23 08:25:00"), 0, null, null),
+        new OneDiffTest( YEAR_INTERVAL, ts("2005-02-23 08:25:00"), ts( "2006-02-23 08:25:00"), 1, null, null),
+
+        // timestamp - time, time - timestamp
+        new OneDiffTest( FRAC_SECOND_INTERVAL, ts( TODAY + " 10:00:00.123456"), tm( "10:00:00"), -123456000, null, null),
+        new OneDiffTest( FRAC_SECOND_INTERVAL, tm( "10:00:00"), ts( TODAY + " 10:00:00.123456"), 123456000, null, null),
+        new OneDiffTest( SECOND_INTERVAL, ts( TODAY + " 10:00:00.1"), tm( "10:00:01"), 0, null, null),
+        new OneDiffTest( SECOND_INTERVAL, tm( "10:00:01"), ts( TODAY + " 10:00:00"), -1, null, null),
+        new OneDiffTest( MINUTE_INTERVAL, ts( TODAY + " 10:02:00"), tm( "10:00:00"), -2, null, null),
+        new OneDiffTest( MINUTE_INTERVAL, tm( "11:00:00"), ts( TODAY + " 10:02:00"), -58, null, null),
+        new OneDiffTest( HOUR_INTERVAL, ts( TODAY + " 10:02:00"), tm( "10:00:00"), 0, null, null),
+        new OneDiffTest( HOUR_INTERVAL, tm( "10:00:00"), ts( TODAY + " 23:02:00"), 13, null, null),
+        new OneDiffTest( DAY_INTERVAL, ts( TODAY + " 00:00:00"), tm( "23:59:59"), 0, null, null),
+        new OneDiffTest( DAY_INTERVAL, tm( "23:59:59"), ts( TODAY + " 00:00:00"), 0, null, null),
+        new OneDiffTest( WEEK_INTERVAL, ts( TODAY + " 00:00:00"), tm( "23:59:59"), 0, null, null),
+        new OneDiffTest( WEEK_INTERVAL, tm( "23:59:59"), ts( TODAY + " 00:00:00"), 0, null, null),
+        new OneDiffTest( MONTH_INTERVAL, ts( TODAY + " 00:00:00"), tm( "23:59:59"), 0, null, null),
+        new OneDiffTest( MONTH_INTERVAL, tm( "23:59:59"), ts( TODAY + " 00:00:00"), 0, null, null),
+        new OneDiffTest( QUARTER_INTERVAL, ts( TODAY + " 00:00:00"), tm( "23:59:59"), 0, null, null),
+        new OneDiffTest( QUARTER_INTERVAL, tm( "23:59:59"), ts( TODAY + " 00:00:00"), 0, null, null),
+        new OneDiffTest( YEAR_INTERVAL, ts( TODAY + " 00:00:00"), tm( "23:59:59"), 0, null, null),
+        new OneDiffTest( YEAR_INTERVAL, tm( "23:59:59"), ts( TODAY + " 00:00:00"), 0, null, null),
+
+        // timestamp - date, date - timestamp
+        new OneDiffTest( FRAC_SECOND_INTERVAL, ts( "2004-05-10 00:00:00.123456"), dt("2004-05-10"), -123456000,
+                         null, null),
+        new OneDiffTest( FRAC_SECOND_INTERVAL, dt("2004-05-10"), ts( "2004-05-10 00:00:00.123456"), 123456000,
+                         null, null),
+        new OneDiffTest( SECOND_INTERVAL, ts( "2004-05-10 08:25:01"), dt("2004-05-10"), -(1+60*(25+60*8)), null, null),
+        new OneDiffTest( SECOND_INTERVAL, dt( "2004-05-10"), ts("2004-05-09 23:59:00"), -60, null, null),
+        new OneDiffTest( MINUTE_INTERVAL, ts( "2004-05-11 08:25:00"), dt("2004-05-10"), -(24*60+8*60+25), null, null),
+        new OneDiffTest( MINUTE_INTERVAL, dt("2004-05-10"), ts( "2004-05-11 08:25:00"), 24*60+8*60+25, null, null),
+        new OneDiffTest( HOUR_INTERVAL, ts("2004-02-28 08:25:00"), dt( "2004-03-01"), 39, null, null),
+        new OneDiffTest( HOUR_INTERVAL, dt( "2005-03-01"), ts("2005-02-28 08:25:00"), -15, null, null),
+        new OneDiffTest( DAY_INTERVAL, ts("2004-05-10 08:25:00"), dt( "2004-05-11"), 0, null, null),
+        new OneDiffTest( DAY_INTERVAL, dt("2004-05-10"), ts( "2004-05-11 08:25:00"), 1, null, null),
+        new OneDiffTest( WEEK_INTERVAL, ts("2004-02-23 00:00:00"), dt( "2004-03-01"), 1, null, null),
+        new OneDiffTest( WEEK_INTERVAL, dt( "2004-03-01"), ts("2004-02-23 00:00:00"), -1, null, null),
+        new OneDiffTest( MONTH_INTERVAL, ts("2004-02-23 08:25:00"), dt( "2004-03-24"), 1, null, null),
+        new OneDiffTest( MONTH_INTERVAL, dt( "2005-03-24"), ts("2004-02-23 08:25:00"), -13, null, null),
+        new OneDiffTest( QUARTER_INTERVAL, ts("2004-02-23 08:25:00"), dt( "2004-05-24"), 1, null, null),
+        new OneDiffTest( QUARTER_INTERVAL, dt( "2004-05-23"), ts("2004-02-23 08:25:01"), 0, null, null),
+        new OneDiffTest( YEAR_INTERVAL, ts("2004-02-23 08:25:00"), dt( "2004-05-23"), 0, null, null),
+        new OneDiffTest( YEAR_INTERVAL, dt( "2004-05-23"), ts("2003-02-23 08:25:00"), -1, null, null),
+
+        // date - time, time - date
+        new OneDiffTest( FRAC_SECOND_INTERVAL, dt( TODAY), tm("00:00:01"), ONE_BILLION, null, null),
+        new OneDiffTest( FRAC_SECOND_INTERVAL, tm("00:00:02"), dt( TODAY), -2*ONE_BILLION, null, null),
+        new OneDiffTest( SECOND_INTERVAL, dt( TODAY), tm("00:00:01"), 1, null, null),
+        new OneDiffTest( SECOND_INTERVAL, tm("00:00:02"), dt( TODAY), -2, null, null),
+        new OneDiffTest( MINUTE_INTERVAL, dt( TODAY), tm("12:34:56"), 12*60 + 34, null, null),
+        new OneDiffTest( MINUTE_INTERVAL, tm("12:34:56"), dt( TODAY), -(12*60 + 34), null, null),
+        new OneDiffTest( HOUR_INTERVAL, dt( TODAY), tm("12:34:56"), 12, null, null),
+        new OneDiffTest( HOUR_INTERVAL, tm("12:34:56"), dt( TODAY), -12, null, null),
+        new OneDiffTest( DAY_INTERVAL, dt( TOMORROW), tm( "00:00:00"), -1, null, null),
+        new OneDiffTest( DAY_INTERVAL, tm( "00:00:00"), dt( TOMORROW), 1, null, null),
+        new OneDiffTest( WEEK_INTERVAL, dt( TOMORROW), tm( "00:00:00"), 0, null, null),
+        new OneDiffTest( WEEK_INTERVAL, tm( "00:00:00"), dt( TOMORROW), 0, null, null),
+        new OneDiffTest( MONTH_INTERVAL, dt( YEAR_FROM_TOMORROW), tm( "12:00:00"), -12, null, null),
+        new OneDiffTest( MONTH_INTERVAL, tm( "12:00:00"), dt( YEAR_FROM_TOMORROW), 12, null, null),
+        new OneDiffTest( QUARTER_INTERVAL, dt( YEAR_FROM_TOMORROW), tm( "12:00:00"), -4, null, null),
+        new OneDiffTest( QUARTER_INTERVAL, tm( "12:00:00"), dt( YEAR_FROM_TOMORROW), 4, null, null),
+        new OneDiffTest( YEAR_INTERVAL, dt( YEAR_FROM_TOMORROW), tm( "12:00:00"), -1, null, null),
+        new OneDiffTest( YEAR_INTERVAL, tm( "12:00:00"), dt( YEAR_FROM_TOMORROW), 1, null, null),
+
+        // Test add with all combinatons of interval types and datetime types
+        new OneAddTest( FRAC_SECOND_INTERVAL, 1000, ts("2005-05-11 15:55:00"), ts("2005-05-11 15:55:00.000001"),
+                        null, null),
+        new OneAddTest( FRAC_SECOND_INTERVAL, -1000, dt("2005-05-11"), ts("2005-05-10 23:59:59.999999"),
+                        null, null),
+        new OneAddTest( FRAC_SECOND_INTERVAL, ONE_BILLION, tm("23:59:59"), ts( TOMORROW + " 00:00:00"), null, null),
+        new OneAddTest( SECOND_INTERVAL, 60, ts("2005-05-11 15:55:00"), ts("2005-05-11 15:56:00"), null, null),
+        new OneAddTest( SECOND_INTERVAL, 60, dt("2005-05-11"), ts("2005-05-11 00:01:00"), null, null),
+        new OneAddTest( SECOND_INTERVAL, 60, tm("23:59:30"), ts( TOMORROW + " 00:00:30"), null, null),
+        new OneAddTest( MINUTE_INTERVAL, -1, ts("2005-05-11 15:55:00"), ts("2005-05-11 15:54:00"), null, null),
+        new OneAddTest( MINUTE_INTERVAL, 1, dt("2005-05-11"), ts("2005-05-11 00:01:00"), null, null),
+        new OneAddTest( MINUTE_INTERVAL, 1, tm("12:00:00"), ts( TODAY + " 12:01:00"), null, null),
+        new OneAddTest( HOUR_INTERVAL, 2, ts("2005-05-11 15:55:00"), ts("2005-05-11 17:55:00"), null, null),
+        new OneAddTest( HOUR_INTERVAL, -2, dt("2005-05-11"), ts("2005-05-10 22:00:00"), null, null),
+        new OneAddTest( HOUR_INTERVAL, 1, tm("12:00:00"), ts( TODAY + " 13:00:00"), null, null),
+        new OneAddTest( DAY_INTERVAL, 1, ts("2005-05-11 15:55:00"), ts("2005-05-12 15:55:00"), null, null),
+        new OneAddTest( DAY_INTERVAL, 1, dt("2005-05-11"), ts("2005-05-12 00:00:00"), null, null),
+        new OneAddTest( DAY_INTERVAL, -1, tm( "12:00:00"), ts( YESTERDAY + " 12:00:00"), null, null),
+        new OneAddTest( WEEK_INTERVAL, 1, ts("2005-05-11 15:55:00"), ts("2005-05-18 15:55:00"), null, null),
+        new OneAddTest( WEEK_INTERVAL, 1, dt("2005-05-11"), ts("2005-05-18 00:00:00"), null, null),
+        new OneAddTest( WEEK_INTERVAL, 1, tm("12:00:00"), ts( WEEK_FROM_TODAY + " 12:00:00"), null, null),
+        new OneAddTest( MONTH_INTERVAL, 1, ts("2005-05-11 15:55:00"), ts("2005-06-11 15:55:00"), null, null),
+        new OneAddTest( MONTH_INTERVAL, -1, dt("2005-03-29"), ts("2005-02-28 00:00:00"), null, null),
+        new OneAddTest( MONTH_INTERVAL, 12, tm( "12:00:00"), ts( YEAR_FROM_TODAY + " 12:00:00"), null, null),
+        new OneAddTest( QUARTER_INTERVAL, 1, ts("2005-10-11 15:55:00"), ts("2006-01-11 15:55:00"), null, null),
+        new OneAddTest( QUARTER_INTERVAL, -2, dt( "2005-05-05"), ts( "2004-11-05 00:00:00"), null, null),
+        new OneAddTest( QUARTER_INTERVAL, 4, tm( "12:00:00"), ts( YEAR_FROM_TODAY + " 12:00:00"), null, null),
+        new OneAddTest( YEAR_INTERVAL, -10, ts("2005-10-11 15:55:00"), ts("1995-10-11 15:55:00"), null, null),
+        new OneAddTest( YEAR_INTERVAL, 2, dt( "2005-05-05"), ts( "2007-05-05 00:00:00"), null, null),
+        new OneAddTest( YEAR_INTERVAL, 1, tm( "12:00:00"), ts( YEAR_FROM_TODAY + " 12:00:00"), null, null),
+
+        // String inputs
+        new OneStringDiffTest( SECOND_INTERVAL, "2005-05-10 08:25:00", "2005-05-10 08:26:00", 60, null, null),
+        new OneStringAddTest( DAY_INTERVAL, 1, "2005-05-11 15:55:00", ts("2005-05-12 15:55:00"), null, null),
+
+        // Overflow
+        new OneDiffTest( FRAC_SECOND_INTERVAL, ts( "2004-05-10 00:00:00.123456"), ts( "2004-05-10 00:00:10.123456"), 0,
+                         "22003", "The resulting value is outside the range for the data type INTEGER."),
+        new OneDiffTest( FRAC_SECOND_INTERVAL, ts( "2004-05-10 00:00:00.123456"), ts( "2005-05-10 00:00:00.123456"), 0,
+                         "22003", "The resulting value is outside the range for the data type INTEGER."),
+        new OneDiffTest( SECOND_INTERVAL, ts( "1904-05-10 00:00:00"), ts( "2205-05-10 00:00:00"), 0,
+                         "22003", "The resulting value is outside the range for the data type INTEGER."),
+        new OneAddTest( YEAR_INTERVAL, 99999, ts( "2004-05-10 00:00:00.123456"), null,
+                        "22003", "The resulting value is outside the range for the data type TIMESTAMP.")
+    };
+
+    private final String[][] invalid =
+    {
+        {"values( {fn TIMESTAMPDIFF( SECOND, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)})", "42X01",
+         "Syntax error: Encountered \"SECOND\" at line 1, column 28."},
+        {"values( {fn TIMESTAMPDIFF( , CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)})", "42X01",
+         "Syntax error: Encountered \",\" at line 1, column 28."},
+        {"values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 5)})", "42X01",
+         "Syntax error: Encountered \",\" at line 1, column 80."},
+        {"values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND, CURRENT_TIMESTAMP, 'x')})", "42X45",
+         "CHAR is an invalid type for argument number 3 of TIMESTAMPDIFF."},
+        {"values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND, 'x', CURRENT_TIMESTAMP)})", "42X45",
+         "CHAR is an invalid type for argument number 2 of TIMESTAMPDIFF."},
+        {"values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND, CURRENT_TIMESTAMP)})", "42X01",
+         "Syntax error: Encountered \")\" at line 1, column 61."},
+        {"values( {fn TIMESTAMPDIFF( SQL_TSI_SECOND)})", "42X01",
+         "Syntax error: Encountered \")\" at line 1, column 42."},
+        {"values( {fn TIMESTAMPADD( x, 1, CURRENT_TIMESTAMP)})", "42X01",
+           "Syntax error: Encountered \"x\" at line 1, column 27."},
+        {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND, CURRENT_DATE, CURRENT_TIMESTAMP)})", "42X45",
+           "DATE is an invalid type for argument number 2 of TIMESTAMPADD."},
+        {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND, 'XX', CURRENT_TIMESTAMP)})", "42X45",
+           "CHAR is an invalid type for argument number 2 of TIMESTAMPADD."},
+        {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND, 1.1, CURRENT_TIMESTAMP)})", "42X45",
+           "DECIMAL is an invalid type for argument number 2 of TIMESTAMPADD."},
+        {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND, 1, 2.1)})", "42X45",
+           "DECIMAL is an invalid type for argument number 3 of TIMESTAMPADD."},
+        {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND, 1, 'XX')})", "42X45",
+           "CHAR is an invalid type for argument number 3 of TIMESTAMPADD."},
+        {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND, 1)})", "42X01",
+           "Syntax error: Encountered \")\" at line 1, column 44."},
+        {"values( {fn TIMESTAMPADD( SQL_TSI_SECOND)})", "42X01",
+           "Syntax error: Encountered \")\" at line 1, column 41."}
+    };
+
+    private static java.sql.Timestamp ts( String s)
+    {
+        // Timestamp format must be yyyy-mm-dd hh:mm:ss.fffffffff
+        if( s.length() &lt; 29)
+        {
+            // Pad out the fraction with zeros
+            StringBuffer sb = new StringBuffer( s);
+            if( s.length() == 19)
+                sb.append( '.');
+            while( sb.length() &lt; 29)
+                sb.append( '0');
+            s = sb.toString();
+        }
+        try
+        {
+            return java.sql.Timestamp.valueOf( s);
+        }
+        catch( Exception e)
+        {
+            System.out.println( s + " is not a proper timestamp string.");
+            System.out.println( e.getClass().getName() + ": " + e.getMessage());
+            e.printStackTrace();
+            System.exit(1);
+            return null;
+        }
+    }
+
+    private static java.sql.Date dt( String s)
+    {
+        return java.sql.Date.valueOf( s);
+    }
+
+    private static java.sql.Time tm( String s)
+    {
+        return java.sql.Time.valueOf( s);
+    }
+
+    private static String dateTimeToLiteral( Object ts)
+    {
+        if( ts instanceof java.sql.Timestamp)
+            return "{ts '" + ((java.sql.Timestamp)ts).toString() + "'}";
+        else if( ts instanceof java.sql.Time)
+            return "{t '" + ((java.sql.Time)ts).toString() + "'}";
+        else if( ts instanceof java.sql.Date)
+            return "{d '" + ((java.sql.Date)ts).toString() + "'}";
+        else if( ts instanceof String)
+            return "TIMESTAMP( '" + ((String) ts) + "')";
+        else
+            return ts.toString();
+    }
+
+    public static void main( String[] args)
+    {
+        System.out.println("Test timestamp arithmetic starting.");
+		try
+        {
+            timestampArith tester = new timestampArith( args);
+            tester.doIt();
+            if( tester.errorCount == 0)
+                System.out.println( "PASSED.");
+            else if( tester.errorCount == 1)
+                System.out.println( "FAILED. 1 error.");
+            else
+                System.out.println( "FAILED. " + tester.errorCount + " errors.");
+        }
+        catch( SQLException sqle)
+        {
+            reportSQLException( sqle);
+            System.exit(1);
+        }
+        catch( Exception e)
+        {
+            System.out.println("Unexpected exception: " + e.getMessage());
+            e.printStackTrace();
+            System.exit(1);
+        }
+        System.exit(0);
+    } // end of main
+
+    String composeSqlStr( String fn, int interval, String parm1, String parm2)
+    {
+        return "values( {fn TIMESTAMP" + fn + "( " + intervalJdbcNames[interval] +
+          ", " + parm1 + "," + parm2 + ")})";
+    }
+    
+    private timestampArith( String[] args) throws Exception
+    {
+        // make the initial connection.
+        ij.getPropertyArg(args);
+        conn = ij.startJBMS();
+
+        conn.setAutoCommit(false);
+        for( int i = 0; i &lt; intervalJdbcNames.length; i++)
+        {
+            tsAddPS[i] = conn.prepareStatement( composeSqlStr( "ADD", i, "?", "?"));
+            tsDiffPS[i] = conn.prepareStatement( composeSqlStr( "DIFF", i, "?", "?"));
+        }
+        stmt = conn.createStatement();
+    }
+
+    private void doIt() throws SQLException
+    {
+        for( int i = 0; i &lt; tests.length; i++)
+            tests[i].runTest();
+
+        testNullInputs();
+
+        for( int i = 0; i &lt; invalid.length; i++)
+        {
+            try
+            {
+                ResultSet rs = stmt.executeQuery( invalid[i][0]);
+                rs.next();
+                reportFailure( "\"" + invalid[i][0] + "\" did not throw an exception.");
+            }
+            catch( SQLException sqle)
+            {
+                checkExpectedException( sqle, invalid[i][1], invalid[i][2], "\"" + invalid[i][0] + "\"");
+            }
+        }
+
+        testInvalidArgTypes();
+    } // end of doIt
+
+    private void testInvalidArgTypes() throws SQLException
+    {
+        expectException( tsDiffPS[ HOUR_INTERVAL], ts( "2005-05-11 15:26:00"), new Double( 2.0), "XCL12",
+                         "An attempt was made to put a data value of type 'double' into a data value of type 'TIMESTAMP'.",
+                         "TIMESTAMPDIFF with double ts2");
+        expectException( tsDiffPS[ HOUR_INTERVAL], new Double( 2.0), ts( "2005-05-11 15:26:00"), "XCL12",
+                         "An attempt was made to put a data value of type 'double' into a data value of type 'TIMESTAMP'.",
+                         "TIMESTAMPDIFF with double ts1");
+
+        expectException( tsAddPS[ MINUTE_INTERVAL], new Integer(1), new Integer(-1), "XCL12",
+                         "An attempt was made to put a data value of type 'int' into a data value of type 'TIMESTAMP'.",
+                         "TIMESTAMPADD with int ts");
+        expectException( tsAddPS[ MINUTE_INTERVAL], ts( "2005-05-11 15:26:00"), ts( "2005-05-11 15:26:00"), "XCL12",
+                         "An attempt was made to put a data value of type 'java.sql.Timestamp' into a data value of type 'INTEGER'.",
+                         "TIMESTAMPADD with timestamp count");
+    } // end of testInvalidArgTypes
+
+    private void expectException( PreparedStatement ps, Object arg1, Object arg2,
+                                  String expectedSQLState, String expectedMsg, String label)
+    {
+        try
+        {
+            ps.setObject( 1, arg1);
+            ps.setObject( 2, arg2);
+            ResultSet rs = ps.executeQuery();
+            rs.next();
+            reportFailure( label + " did not throw an exception.");
+        }
+        catch( SQLException sqle) { checkExpectedException( sqle, expectedSQLState, expectedMsg, label);};
+    } // end of expectException
+
+    private void checkExpectedException( SQLException sqle, String expectedSQLState, String expectedMsg, String label)
+    {
+        if( ! expectedSQLState.equals( sqle.getSQLState()))
+            reportFailure( "Unexpected SQLState from \"" + label + "\". expected " +
+                           expectedSQLState + " got " + sqle.getSQLState());
+        else if( expectedMsg != null &amp;&amp; ! expectedMsg.equals( sqle.getMessage()))
+            reportFailure( "Unexpected message from \"" + label + "\".\n  expected \"" +
+                           expectedMsg + "\"\n  got \"" + sqle.getMessage() + "\"");
+    } // end of checkExpectedException
+
+    private void testNullInputs() throws SQLException
+    {
+        // Null inputs, each position, each type
+        tsDiffPS[ HOUR_INTERVAL].setTimestamp( 1, ts( "2005-05-11 15:26:00"));
+        tsDiffPS[ HOUR_INTERVAL].setNull( 2, Types.TIMESTAMP);
+        expectNullResult( tsDiffPS[ HOUR_INTERVAL], "TIMESTAMPDIFF with null timestamp in third argument");
+        tsDiffPS[ HOUR_INTERVAL].setNull( 2, Types.DATE);
+        expectNullResult( tsDiffPS[ HOUR_INTERVAL], "TIMESTAMPDIFF with null date in third argument");
+
+        tsDiffPS[ HOUR_INTERVAL].setTimestamp( 2, ts( "2005-05-11 15:26:00"));
+        tsDiffPS[ HOUR_INTERVAL].setNull( 1, Types.TIMESTAMP);
+        expectNullResult( tsDiffPS[ HOUR_INTERVAL], "TIMESTAMPDIFF with null timestamp in second argument");
+        tsDiffPS[ HOUR_INTERVAL].setNull( 1, Types.DATE);
+        expectNullResult( tsDiffPS[ HOUR_INTERVAL], "TIMESTAMPDIFF with null date in second argument");
+
+        tsAddPS[ MINUTE_INTERVAL].setTimestamp( 2, ts( "2005-05-11 15:26:00"));
+        tsAddPS[ MINUTE_INTERVAL].setNull( 1, Types.INTEGER);
+        expectNullResult( tsAddPS[ MINUTE_INTERVAL], "TIMESTAMPADD with null integer in second argument");
+
+        tsAddPS[ MINUTE_INTERVAL].setInt( 1, 1);
+        tsAddPS[ MINUTE_INTERVAL].setNull( 2, Types.TIMESTAMP);
+        expectNullResult( tsAddPS[ MINUTE_INTERVAL], "TIMESTAMPADD with null timestamp in third argument");
+        tsAddPS[ MINUTE_INTERVAL].setNull( 2, Types.DATE);
+        expectNullResult( tsAddPS[ MINUTE_INTERVAL], "TIMESTAMPADD with null date in third argument");
+    } // end of testNullInputs
+
+    private void expectNullResult( PreparedStatement ps, String label)
+    {
+        try
+        {
+            ResultSet rs = ps.executeQuery();
+            if( ! rs.next())
+                reportFailure( label + " returned no rows.");
+            else if( rs.getObject( 1) != null)
+                reportFailure( label + " did not return null.");
+        }
+        catch (SQLException sqle)
+        {
+            reportFailure( "Unexpected exception from " + label);
+            reportSQLException( sqle);
+        }
+    } // end of expectNullResult
+
+    private static void reportSQLException( SQLException sqle)
+    {
+        System.out.println("Unexpected exception:");
+        for(;;)
+        {
+            System.out.println( "    " + sqle.getMessage());
+            if( sqle.getNextException() != null)
+                sqle = sqle.getNextException();
+            else
+                break;
+        }
+        sqle.printStackTrace();
+    } // end of reportSQLException
+
+    private void reportFailure( String msg)
+    {
+        errorCount++;
+        System.out.println( msg);
+    }
+
+    private static void setDateTime( PreparedStatement ps, int parameterIdx, java.util.Date dateTime)
+        throws SQLException
+    {
+        if( dateTime instanceof java.sql.Timestamp)
+            ps.setTimestamp( parameterIdx, (java.sql.Timestamp) dateTime);
+        else if( dateTime instanceof java.sql.Date)
+            ps.setDate( parameterIdx, (java.sql.Date) dateTime);
+        else if( dateTime instanceof java.sql.Time)
+            ps.setTime( parameterIdx, (java.sql.Time) dateTime);
+        else
+            ps.setTimestamp( parameterIdx, (java.sql.Timestamp) dateTime);
+    }
+    
+    abstract class OneTest
+    {
+        final int interval; // FRAC_SECOND_INTERVAL, SECOND_INTERVAL, ... or YEAR_INTERVAL
+        final String expectedSQLState; // Null if no SQLException is expected
+        final String expectedMsg; // Null if no SQLException is expected
+        String sql;
+        
+        OneTest( int interval, String expectedSQLState, String expectedMsg)
+        {
+            this.interval = interval;
+            this.expectedSQLState = expectedSQLState;
+            this.expectedMsg = expectedMsg;
+        }
+        
+        void runTest()
+        {
+            sql = composeSQL();
+            ResultSet rs = null;
+            try
+            {
+                rs = stmt.executeQuery( sql);
+                checkResultSet( rs, sql);
+                if( expectedSQLState != null)
+                    reportFailure( "Statement '" + sql + "' did not generate an exception");
+            }
+            catch( SQLException sqle)
+            {
+                checkSQLException( "Statement", sqle);
+            }
+            if( rs != null)
+            {
+                try
+                {
+                    rs.close();
+                }
+                catch( SQLException sqle){};
+                rs = null;
+            }
+            
+            try
+            {
+                rs = executePS();
+                checkResultSet( rs, sql);
+                if( expectedSQLState != null)
+                    reportFailure( "PreparedStatement '" + sql + "' did not generate an exception");
+            }
+            catch( SQLException sqle)
+            {
+                checkSQLException( "PreparedStatement", sqle);
+            }
+            if( rs != null)
+            {
+                try
+                {
+                    rs.close();
+                }
+                catch( SQLException sqle){};
+                rs = null;
+            }
+        } // end of RunTest
+
+        private void checkResultSet( ResultSet rs, String sql) throws SQLException
+        {
+            if( rs.next())
+            {
+                checkResultRow( rs, sql);
+                if( rs.next())
+                    reportFailure( "'" + sql + "' returned more than one row.");
+            }
+            else
+                reportFailure( "'" + sql + "' did not return any rows.");
+        } // end of checkResultSet
+
+        private void checkSQLException( String type, SQLException sqle)
+        {
+            if( expectedSQLState != null)
+            {
+                if( ! expectedSQLState.equals( sqle.getSQLState()))
+                    reportFailure( "Incorrect SQLState from " + type + " '" + sql + "' expected " + expectedSQLState +
+                                   " got " + sqle.getSQLState());
+                else if( expectedMsg != null &amp;&amp; ! expectedMsg.equals( sqle.getMessage()))
+                    reportFailure( "Incorrect exception message from " + type + " '" + sql + "' expected '" + expectedMsg +
+                                   "' got '" + sqle.getMessage() + "'");
+            }
+            else
+            {
+                reportFailure( "Unexpected exception from " + type + " '" + sql + "'");
+                reportSQLException( sqle);
+            }
+        } // end of checkSQLException
+
+        abstract String composeSQL();
+
+        abstract void checkResultRow( ResultSet rs, String sql) throws SQLException;
+
+        abstract ResultSet executePS() throws SQLException;
+    }
+
+    class OneDiffTest extends OneTest
+    {
+        private final java.util.Date ts1;
+        private final java.util.Date ts2;
+        final int expectedDiff;
+        protected boolean expectNull;
+
+        OneDiffTest( int interval,
+                     java.util.Date ts1,
+                     java.util.Date ts2,
+                     int expectedDiff,
+                     String expectedSQLState,
+                     String expectedMsg)
+        {
+            super( interval, expectedSQLState, expectedMsg);
+            this.ts1 = ts1;
+            this.ts2 = ts2;
+            this.expectedDiff = expectedDiff;
+            expectNull = (ts1 == null) || (ts2 == null);
+        }
+
+        String composeSQL()
+        {
+            return composeSqlStr( "DIFF", interval, dateTimeToLiteral( ts1), dateTimeToLiteral( ts2));
+        }
+        
+        void checkResultRow( ResultSet rs, String sql) throws SQLException
+        {
+            int actualDiff = rs.getInt(1);
+            if( rs.wasNull())
+            {
+                if( !expectNull)
+                    reportFailure( "Unexpected null result from '" + sql + "'.");
+            }
+            else
+            {
+                if( expectNull)
+                    reportFailure( "Expected null result from '" + sql + "'.");
+                else if( actualDiff != expectedDiff)
+                    reportFailure( "Unexpected result from '" + sql + "'.  Expected " +
+                        expectedDiff + " got " + actualDiff + ".");
+            }
+        }
+
+        ResultSet executePS() throws SQLException
+        {
+            setDateTime( tsDiffPS[interval], 1, ts1);
+            setDateTime( tsDiffPS[interval], 2, ts2);
+            return tsDiffPS[interval].executeQuery();
+        }
+    } // end of class OneDiffTest
+
+    class OneStringDiffTest extends OneDiffTest
+    {
+        private final String ts1;
+        private final String ts2;
+
+        OneStringDiffTest( int interval,
+                           String ts1,
+                           String ts2,
+                           int expectedDiff,
+                           String expectedSQLState,
+                           String expectedMsg)
+        {
+            super( interval, (java.util.Date) null, (java.util.Date) null, expectedDiff, expectedSQLState, expectedMsg);
+            this.ts1 = ts1;
+            this.ts2 = ts2;
+            expectNull = (ts1 == null) || (ts2 == null);
+        }
+
+        String composeSQL()
+        {
+            return composeSqlStr( "DIFF", interval, dateTimeToLiteral( ts1), dateTimeToLiteral( ts2));
+        }
+
+        ResultSet executePS() throws SQLException
+        {
+            tsDiffPS[interval].setString( 1, ts1);
+            tsDiffPS[interval].setString( 2, ts2);
+            return tsDiffPS[interval].executeQuery();
+        }
+    } // end of class OneStringDiffTest
+
+    class OneAddTest extends OneTest
+    {
+        private final java.util.Date ts;
+        final int count;
+        final java.sql.Timestamp expected;
+
+        OneAddTest( int interval,
+                    int count,
+                    java.util.Date ts,
+                    java.sql.Timestamp expected,
+                    String expectedSQLState,
+                    String expectedMsg)
+        {
+            super( interval, expectedSQLState, expectedMsg);
+            this.count = count;
+            this.ts = ts;
+            this.expected = expected;
+        }
+
+        String composeSQL()
+        {
+            return composeSqlStr( "ADD", interval, String.valueOf( count), dateTimeToLiteral( ts));
+        }
+
+        void checkResultRow( ResultSet rs, String sql) throws SQLException
+        {
+            java.sql.Timestamp actual = rs.getTimestamp( 1);
+            if( rs.wasNull() || actual == null)
+            {
+                if( expected != null)
+                    reportFailure( "Unexpected null result from '" + sql + "'.");
+            }
+            else
+            {
+                if( expected == null)
+                    reportFailure( "Expected null result from '" + sql + "'.");
+                else if( ! actual.equals( expected))
+                    reportFailure( "Unexpected result from '" + sql + "'.  Expected " +
+                                   expected.toString() + " got " + actual.toString() + ".");
+            }
+        }
+
+        ResultSet executePS() throws SQLException
+        {
+            tsAddPS[interval].setInt( 1, count);
+            setDateTime( tsAddPS[interval], 2, ts);
+            return tsAddPS[interval].executeQuery();
+        }
+    } // end of class OneAddTest
+
+    class OneStringAddTest extends OneAddTest
+    {
+        private final String ts;
+
+        OneStringAddTest( int interval,
+                          int count,
+                          String ts,
+                          java.sql.Timestamp expected,
+                          String expectedSQLState,
+                          String expectedMsg)
+        {
+            super( interval, count, (java.util.Date) null, expected, expectedSQLState, expectedMsg);
+            this.ts = ts;
+        }
+
+        String composeSQL()
+        {
+            return composeSqlStr( "ADD", interval, String.valueOf( count), dateTimeToLiteral( ts));
+        }
+
+        ResultSet executePS() throws SQLException
+        {
+            tsAddPS[interval].setInt( 1, count);
+            tsAddPS[interval].setString( 2, ts);
+            return tsAddPS[interval].executeQuery();
+        }
+    } // end of class OneStringAddTest
+}
Index: java/testing/org/apache/derbyTesting/functionTests/master/timestampArith.out
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/master/timestampArith.out	(revision 0)
+++ java/testing/org/apache/derbyTesting/functionTests/master/timestampArith.out	(revision 0)
@@ -0,0 +1,2 @@
+Test timestamp arithmetic starting.
+PASSED.
Index: java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall	(revision 170976)
+++ java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall	(working copy)
@@ -123,6 +123,7 @@
 lang/supersimple.sql
 lang/syscat.sql
 lang/tempRestrictions.sql
+lang/timestampArith.java
 lang/triggerBeforeTrig.sql
 lang/triggerGeneral.sql
 lang/triggerRecursion.sql
  </pre>
</blockquote>
</body>
</html>


Mime
View raw message