1. According to the Derby documentation the DATE and TIMESTAMP functions provide some conversions beyond those handled by the normal casts. This patch implements them. The DATE function converts a numeric argument to a date by taking the integer portion of the number and considering it as the number of days since Jan. 1 1970. The DATE function also handles a string of length 7 in the format 'yyyyddd'. It is taken to indicate the ddd'th day of year yyyy. The TIMESTAMP function handles string arguments of length 14 in the format 'yyyyMMddhhmmss'. Any other arguments to the DATE and TIMESTAMP functions are handled as normal casts to date or timestamp.
(The TIME function does not do any special conversions. It is the same as the CAST function).
2. The string to time cast has been made more lenient by making the seconds field optional.
Previously the DATE and unary TIMESTAMP functions were implemented by converting them to calls to CAST. This was done in the parser. The patch changes this. The parser now generates a UnaryDateTimestampOperatorNode to represent these functions. The generated code calls new DataValueFactory methods. Most of the actual work is done in the SQLDate and SQLTimestamp classes.
The patch passed the derbyall suite.
Jack Klebanoff
Index: java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java
(revision 164436)
+++ java/engine/org/apache/derby/impl/sql/compile/NodeFactoryImpl.java
(working copy)
@@ -554,6 +554,9 @@
case C_NodeTypes.SAVEPOINT_NODE:
return C_NodeNames.SAVEPOINT_NODE_NAME;
+ case C_NodeTypes.UNARY_DATE_TIMESTAMP_OPERATOR_NODE:
+ return C_NodeNames.UNARY_DATE_TIMESTAMP_OPERATOR_NODE_NAME;
+
case C_NodeTypes.TIMESTAMP_OPERATOR_NODE:
return C_NodeNames.TIMESTAMP_OPERATOR_NODE_NAME;
Index:
java/engine/org/apache/derby/impl/sql/compile/UnaryDateTimestampOperatorNode.java
===================================================================
---
java/engine/org/apache/derby/impl/sql/compile/UnaryDateTimestampOperatorNode.java
(revision 0)
+++
java/engine/org/apache/derby/impl/sql/compile/UnaryDateTimestampOperatorNode.java
(revision 0)
@@ -0,0 +1,196 @@
+/*
+
+ Derby - Class
org.apache.derby.impl.sql.compile.UnaryDateTimestampOperatorNode
+
+ Copyright 2004 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
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ 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.derby.impl.sql.compile;
+
+import org.apache.derby.iapi.types.DataValueFactory;
+import org.apache.derby.iapi.types.DataTypeDescriptor;
+import org.apache.derby.iapi.types.DataValueDescriptor;
+import org.apache.derby.iapi.types.DateTimeDataValue;
+import org.apache.derby.iapi.services.compiler.MethodBuilder;
+import org.apache.derby.iapi.error.StandardException;
+
+import org.apache.derby.iapi.sql.compile.C_NodeTypes;
+
+import org.apache.derby.impl.sql.compile.ExpressionClassBuilder;
+
+import org.apache.derby.iapi.reference.ClassName;
+import org.apache.derby.iapi.reference.SQLState;
+
+import org.apache.derby.iapi.services.classfile.VMOpcode;
+import org.apache.derby.iapi.services.sanity.SanityManager;
+
+import java.sql.Types;
+
+import java.util.Vector;
+
+/**
+ * This class implements the timestamp( x) and date(x) functions.
+ *
+ * These two functions implement a few special cases of string conversions
beyond the normal string to
+ * date/timestamp casts.
+ */
+public class UnaryDateTimestampOperatorNode extends UnaryOperatorNode
+{
+ private static final String TIMESTAMP_METHOD_NAME = "getTimestamp";
+ private static final String DATE_METHOD_NAME = "getDate";
+
+ /**
+ * @param operand The operand of the function
+ * @param targetType The type of the result. Timestamp or Date.
+ *
+ * @exception StandardException Thrown on error
+ */
+
+ public void init( Object operand, Object targetType)
+ throws StandardException
+ {
+ setType( (DataTypeDescriptor) targetType);
+ switch( getTypeServices().getJDBCTypeId())
+ {
+ case Types.DATE:
+ super.init( operand, "date", DATE_METHOD_NAME);
+ break;
+
+ case Types.TIMESTAMP:
+ super.init( operand, "timestamp", TIMESTAMP_METHOD_NAME);
+ break;
+
+ default:
+ if( SanityManager.DEBUG)
+ SanityManager.NOTREACHED();
+ super.init( operand);
+ }
+ }
+
+ /**
+ * Called by UnaryOperatorNode.bindExpression.
+ *
+ * If the operand is a constant then evaluate the function at compile
time. Otherwise,
+ * if the operand input type is the same as the output type then discard
this node altogether.
+ * If the function is "date" and the input is a timestamp then change this
node to a cast.
+ *
+ * @param fromList The FROM list for the query this
+ * expression is in, for binding columns.
+ * @param subqueryList The subquery list being built as we
find SubqueryNodes
+ * @param aggregateVector The aggregate vector being built as we
find AggregateNodes
+ *
+ * @return The new top of the expression tree.
+ *
+ * @exception StandardException Thrown on error
+ */
+ protected ValueNode bindUnaryOperator(
+ FromList fromList, SubqueryList
subqueryList,
+ Vector aggregateVector)
+ throws StandardException
+ {
+ boolean isIdentity = false; // Is this function the identity operator?
+ boolean operandIsNumber = false;
+
+ super.bindUnaryOperator( fromList, subqueryList, aggregateVector);
+ DataTypeDescriptor operandType = operand.getTypeServices();
+ switch( operandType.getJDBCTypeId())
+ {
+ case Types.BIGINT:
+ case Types.INTEGER:
+ case Types.SMALLINT:
+ case Types.TINYINT:
+ case Types.DECIMAL:
+ case Types.NUMERIC:
+ case Types.DOUBLE:
+ case Types.FLOAT:
+ if( TIMESTAMP_METHOD_NAME.equals( methodName))
+ invalidOperandType();
+ operandIsNumber = true;
+ break;
+
+ case Types.CHAR:
+ case Types.VARCHAR:
+ break;
+
+ case Types.DATE:
+ if( TIMESTAMP_METHOD_NAME.equals( methodName))
+ invalidOperandType();
+ isIdentity = true;
+ break;
+
+ case Types.NULL:
+ break;
+
+ case Types.TIMESTAMP:
+ if( TIMESTAMP_METHOD_NAME.equals( methodName))
+ isIdentity = true;
+ break;
+
+ default:
+ invalidOperandType();
+ }
+
+ if( operand instanceof ConstantNode)
+ {
+ DataValueFactory dvf =
getLanguageConnectionContext().getDataValueFactory();
+ DataValueDescriptor sourceValue = ((ConstantNode)
operand).getValue();
+ DataValueDescriptor destValue = null;
+ if( sourceValue.isNull())
+ {
+ destValue = (TIMESTAMP_METHOD_NAME.equals( methodName))
+ ? dvf.getNullTimestamp( (DateTimeDataValue) null)
+ : dvf.getNullDate( (DateTimeDataValue) null);
+ }
+ else
+ {
+ destValue = (TIMESTAMP_METHOD_NAME.equals( methodName))
+ ? dvf.getTimestamp( sourceValue) : dvf.getDate( sourceValue);
+ }
+ return (ValueNode) getNodeFactory().getNode(
C_NodeTypes.USERTYPE_CONSTANT_NODE,
+ destValue,
getContextManager());
+ }
+
+ if( isIdentity)
+ return operand;
+ return this;
+ } // end of bindUnaryOperator
+
+ private void invalidOperandType() throws StandardException
+ {
+ throw StandardException.newException(
SQLState.LANG_UNARY_FUNCTION_BAD_TYPE,
+ getOperatorString(),
getOperand().getTypeServices().getSQLstring());
+ }
+
+ /**
+ * Do code generation for this unary operator.
+ *
+ * @param acb The ExpressionClassBuilder for the class we're
generating
+ * @param mb The method the expression will go into
+ *
+ *
+ * @exception StandardException Thrown on error
+ */
+
+ public void generateExpression( ExpressionClassBuilder acb,
+ MethodBuilder mb)
+ throws StandardException
+ {
+ acb.pushDataValueFactory( mb);
+ operand.generateExpression( acb, mb);
+ mb.cast( ClassName.DataValueDescriptor);
+ mb.callMethod( VMOpcode.INVOKEINTERFACE, (String) null, methodName,
getTypeCompiler().interfaceName(), 1);
+ } // end of generateExpression
+}
Index: java/engine/org/apache/derby/impl/sql/compile/C_NodeNames.java
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/C_NodeNames.java
(revision 164436)
+++ java/engine/org/apache/derby/impl/sql/compile/C_NodeNames.java
(working copy)
@@ -250,6 +250,9 @@
static final String UNARY_ARITHMETIC_OPERATOR_NODE_NAME =
"org.apache.derby.impl.sql.compile.UnaryArithmeticOperatorNode";
+ static final String UNARY_DATE_TIMESTAMP_OPERATOR_NODE_NAME
+ = "org.apache.derby.impl.sql.compile.UnaryDateTimestampOperatorNode";
+
static final String UNION_NODE_NAME =
"org.apache.derby.impl.sql.compile.UnionNode";
static final String INTERSECT_OR_EXCEPT_NODE_NAME =
"org.apache.derby.impl.sql.compile.IntersectOrExceptNode";
Index: java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj
===================================================================
--- java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (revision
164436)
+++ java/engine/org/apache/derby/impl/sql/compile/sqlgrammar.jj (working copy)
@@ -6081,7 +6081,7 @@
<DATE> <LEFT_PAREN> value = additiveExpression(null,0, false)
<RIGHT_PAREN>
{
return (ValueNode) nodeFactory.getNode(
- C_NodeTypes.CAST_NODE,
+
C_NodeTypes.UNARY_DATE_TIMESTAMP_OPERATOR_NODE,
value,
DataTypeDescriptor.getBuiltInDataTypeDescriptor( Types.DATE),
getContextManager());
@@ -6115,7 +6115,7 @@
<RIGHT_PAREN>
{
return (ValueNode) nodeFactory.getNode(
- C_NodeTypes.CAST_NODE,
+
C_NodeTypes.UNARY_DATE_TIMESTAMP_OPERATOR_NODE,
firstArg,
DataTypeDescriptor.getBuiltInDataTypeDescriptor( Types.TIMESTAMP),
getContextManager());
Index: java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java
===================================================================
--- java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java
(revision 164436)
+++ java/engine/org/apache/derby/iapi/sql/compile/C_NodeTypes.java
(working copy)
@@ -61,7 +61,7 @@
static final int UNARY_MINUS_OPERATOR_NODE = 29;
static final int UNARY_PLUS_OPERATOR_NODE = 30;
static final int SQL_BOOLEAN_CONSTANT_NODE = 31;
- // 32 is available
+ static final int UNARY_DATE_TIMESTAMP_OPERATOR_NODE = 32;
static final int TIMESTAMP_OPERATOR_NODE = 33;
static final int TABLE_NAME = 34;
static final int GROUP_BY_COLUMN = 35;
Index: java/engine/org/apache/derby/iapi/types/SQLDate.java
===================================================================
--- java/engine/org/apache/derby/iapi/types/SQLDate.java (revision
164436)
+++ java/engine/org/apache/derby/iapi/types/SQLDate.java (working copy)
@@ -379,7 +379,7 @@
{
parseDate(value);
}
-
+
private void parseDate( java.util.Date value) throws StandardException
{
encodedDate = computeEncodedDate(value);
@@ -929,5 +929,72 @@
currentCal.setTime(value);
return SQLDate.computeEncodedDate(currentCal);
}
+
+
+ /**
+ * Implement the date SQL function: construct a SQL date from a
string, number, or timestamp.
+ *
+ * @param operand Must be a date or a string convertible to a date.
+ * @param dvf the DataValueFactory
+ *
+ * @exception StandardException standard error policy
+ */
+ public static DateTimeDataValue computeDateFunction( DataValueDescriptor
operand,
+ DataValueFactory dvf)
throws StandardException
+ {
+ try
+ {
+ if( operand.isNull())
+ return new SQLDate();
+ if( operand instanceof SQLDate)
+ return (SQLDate) operand.getClone();
+
+ if( operand instanceof SQLTimestamp)
+ {
+ DateTimeDataValue retVal = new SQLDate();
+ retVal.setValue( operand);
+ return retVal;
+ }
+ if( operand instanceof NumberDataValue)
+ {
+ int daysSinceEpoch = operand.getInt();
+ if( daysSinceEpoch <= 0 || daysSinceEpoch > 3652059)
+ throw StandardException.newException(
SQLState.LANG_INVALID_FUNCTION_ARGUMENT,
+ operand.getString(),
"date");
+ Calendar cal = new GregorianCalendar( 1970, 0, 1, 12, 0, 0);
+ cal.add( Calendar.DATE, daysSinceEpoch - 1);
+ return new SQLDate( computeEncodedDate( cal.get(
Calendar.YEAR),
+ cal.get(
Calendar.MONTH) + 1,
+ cal.get(
Calendar.DATE)));
+ }
+ String str = operand.getString();
+ if( str.length() == 7)
+ {
+ // yyyyddd where ddd is the day of the year
+ int year = SQLTimestamp.parseDateTimeInteger( str, 0, 4);
+ int dayOfYear = SQLTimestamp.parseDateTimeInteger( str, 4, 3);
+ if( dayOfYear < 1 || dayOfYear > 366)
+ throw StandardException.newException(
SQLState.LANG_INVALID_FUNCTION_ARGUMENT,
+ operand.getString(),
"date");
+ Calendar cal = new GregorianCalendar( year, 0, 1, 2, 0, 0);
+ cal.add( Calendar.DAY_OF_YEAR, dayOfYear - 1);
+ int y = cal.get( Calendar.YEAR);
+ if( y != year)
+ throw StandardException.newException(
SQLState.LANG_INVALID_FUNCTION_ARGUMENT,
+ operand.getString(),
"date");
+ return new SQLDate( computeEncodedDate( year,
+ cal.get(
Calendar.MONTH) + 1,
+ cal.get(
Calendar.DATE)));
+ }
+ // Else use the standard cast.
+ return dvf.getDateValue( str, false);
+ }
+ catch( StandardException se)
+ {
+ if( SQLState.LANG_DATE_SYNTAX_EXCEPTION.startsWith(
se.getSQLState()))
+ throw StandardException.newException(
SQLState.LANG_INVALID_FUNCTION_ARGUMENT,
+ operand.getString(),
"date");
+ throw se;
+ }
+ } // end of computeDateFunction
}
-
Index: java/engine/org/apache/derby/iapi/types/SQLTime.java
===================================================================
--- java/engine/org/apache/derby/iapi/types/SQLTime.java (revision
164436)
+++ java/engine/org/apache/derby/iapi/types/SQLTime.java (working copy)
@@ -446,7 +446,7 @@
private static final char IBM_EUR_SEPARATOR = '.';
private static final char[] IBM_EUR_SEPARATOR_OR_END = {IBM_EUR_SEPARATOR,
(char) 0};
static final char JIS_SEPARATOR = ':';
- private static final char[] US_OR_JIS_MINUTE_END = {JIS_SEPARATOR, ' '};
+ private static final char[] US_OR_JIS_MINUTE_END = {JIS_SEPARATOR, ' ',
(char) 0};
private static final char[] ANY_SEPARATOR = { '.', ':', ' '};
private static final String[] AM_PM = {"AM", "PM"};
private static final char[] END_OF_STRING = {(char) 0};
@@ -503,8 +503,7 @@
second = parser.parseInt( 2, false, END_OF_STRING, false);
break;
- default:
- validSyntax = false;
+ // default is end of string, meaning that the seconds part
is zero.
}
break;
Index: java/engine/org/apache/derby/iapi/types/DataValueFactoryImpl.java
===================================================================
--- java/engine/org/apache/derby/iapi/types/DataValueFactoryImpl.java
(revision 164436)
+++ java/engine/org/apache/derby/iapi/types/DataValueFactoryImpl.java
(working copy)
@@ -606,6 +606,30 @@
return previous;
}
+ /**
+ * Implement the date SQL function: construct a SQL date from a
string, number, or timestamp.
+ *
+ * @param operand Must be a date, a number, or a string convertible to
a date.
+ *
+ * @exception StandardException standard error policy
+ */
+ public DateTimeDataValue getDate( DataValueDescriptor operand) throws
StandardException
+ {
+ return SQLDate.computeDateFunction( operand, this);
+ }
+
+ /**
+ * Implement the timestamp SQL function: construct a SQL timestamp
from a string, or timestamp.
+ *
+ * @param operand Must be a timestamp or a string convertible to a
timestamp.
+ *
+ * @exception StandardException standard error policy
+ */
+ public DateTimeDataValue getTimestamp( DataValueDescriptor operand)
throws StandardException
+ {
+ return SQLTimestamp.computeTimestampFunction( operand, this);
+ }
+
public DateTimeDataValue getTimestamp( DataValueDescriptor date,
DataValueDescriptor time) throws StandardException
{
return new SQLTimestamp( date, time);
Index: java/engine/org/apache/derby/iapi/types/SQLTimestamp.java
===================================================================
--- java/engine/org/apache/derby/iapi/types/SQLTimestamp.java (revision
164436)
+++ java/engine/org/apache/derby/iapi/types/SQLTimestamp.java (working copy)
@@ -998,6 +998,58 @@
ps.setTimestamp(position, getTimestamp((Calendar) null));
}
-}
+ /**
+ * Compute the SQL timestamp function.
+ *
+ * @exception StandardException
+ */
+ public static DateTimeDataValue computeTimestampFunction(
DataValueDescriptor operand,
+ DataValueFactory
dvf) throws StandardException
+ {
+ try
+ {
+ if( operand.isNull())
+ return new SQLTimestamp();
+ if( operand instanceof SQLTimestamp)
+ return (SQLTimestamp) operand.getClone();
+ String str = operand.getString();
+ if( str.length() == 14)
+ {
+ int year = parseDateTimeInteger( str, 0, 4);
+ int month = parseDateTimeInteger( str, 4, 2);
+ int day = parseDateTimeInteger( str, 6, 2);
+ int hour = parseDateTimeInteger( str, 8, 2);
+ int minute = parseDateTimeInteger( str, 10, 2);
+ int second = parseDateTimeInteger( str, 12, 2);
+ return new SQLTimestamp( SQLDate.computeEncodedDate( year,
month, day),
+ SQLTime.computeEncodedTime(
hour,minute,second),
+ 0);
+ }
+ // else use the standard cast
+ return dvf.getTimestampValue( str, false);
+ }
+ catch( StandardException se)
+ {
+ if( SQLState.LANG_DATE_SYNTAX_EXCEPTION.startsWith(
se.getSQLState()))
+ throw StandardException.newException(
SQLState.LANG_INVALID_FUNCTION_ARGUMENT,
+ operand.getString(),
"timestamp");
+ throw se;
+ }
+ } // end of computeTimestampFunction
+
+ static int parseDateTimeInteger( String str, int start, int ndigits)
throws StandardException
+ {
+ int end = start + ndigits;
+ int retVal = 0;
+ for( int i = start; i < end; i++)
+ {
+ char c = str.charAt( i);
+ if( !Character.isDigit( c))
+ throw StandardException.newException(
SQLState.LANG_DATE_SYNTAX_EXCEPTION);
+ retVal = 10*retVal + Character.digit( c, 10);
+ }
+ return retVal;
+ } // end of parseDateTimeInteger
+}
Index: java/engine/org/apache/derby/iapi/types/DataValueFactory.java
===================================================================
--- java/engine/org/apache/derby/iapi/types/DataValueFactory.java
(revision 164436)
+++ java/engine/org/apache/derby/iapi/types/DataValueFactory.java
(working copy)
@@ -395,7 +395,15 @@
DateTimeDataValue getDataValue(Timestamp value,
DateTimeDataValue previous)
throws
StandardException;
+
/**
+ * Implement the timestamp SQL function: construct a SQL timestamp
from a string, or timestamp.
+ *
+ * @param operand Must be a timestamp or a string convertible to a
timestamp.
+ */
+ DateTimeDataValue getTimestamp( DataValueDescriptor operand) throws
StandardException;
+
+ /**
* Construct a SQL timestamp from a date and time.
*
* @param date Must be convertible to a date.
@@ -404,6 +412,17 @@
DateTimeDataValue getTimestamp( DataValueDescriptor date,
DataValueDescriptor time) throws StandardException;
/**
+ * Implements the SQL date function
+ *
+ * @param operand A date, timestamp, string or integer.
+ *
+ * @return the corresponding date value
+ *
+ * @exception StandardException if the syntax is invalid or the date
is out of range.
+ */
+ public DateTimeDataValue getDate( DataValueDescriptor operand) throws
StandardException;
+
+ /**
* @param dateStr A date in one of the DB2 standard date formats or
the local format.
* @param isJdbcEscape If true then the timestamp must be in the JDBC
timestamp escape format, otherwise it must
* be in the DB2 timestamp format.
Index: java/engine/org/apache/derby/iapi/reference/SQLState.java
===================================================================
--- java/engine/org/apache/derby/iapi/reference/SQLState.java (revision
164436)
+++ java/engine/org/apache/derby/iapi/reference/SQLState.java (working copy)
@@ -654,6 +654,7 @@
String LANG_DATE_RANGE_EXCEPTION =
"22007.S.180";
String LANG_DATE_SYNTAX_EXCEPTION =
"22007.S.181";
+ String LANG_INVALID_FUNCTION_ARGUMENT =
"22008.S";
String LANG_SUBSTR_START_OR_LEN_OUT_OF_RANGE =
"22011";
String LANG_DIVIDE_BY_ZERO =
"22012";
String LANG_SQRT_OF_NEG_NUMBER =
"22013";
Index: java/engine/org/apache/derby/loc/messages_en.properties
===================================================================
--- java/engine/org/apache/derby/loc/messages_en.properties (revision
164436)
+++ java/engine/org/apache/derby/loc/messages_en.properties (working copy)
@@ -361,6 +361,7 @@
22005=An attempt was made to get a data value of type ''{0}'' from a data
value of type ''{1}''.
22007.S.180=The string representation of a datetime value is out of range.
22007.S.181=The syntax of the string representation of a datetime value is
incorrect.
+22008.S=''{0}'' is an invalid argument to the {1} function.
22011=The second or third argument of the SUBSTR function is out of range.
22012=Attempt to divide by zero.
22013=Attempt to take the square root of a negative number, ''{0}''.
Index:
java/testing/org/apache/derbyTesting/functionTests/tests/lang/datetime.sql
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/tests/lang/datetime.sql
(revision 164436)
+++ java/testing/org/apache/derbyTesting/functionTests/tests/lang/datetime.sql
(working copy)
@@ -540,7 +540,13 @@
insert into t values ('17.05.11 ');
insert into t values ('17:05:11 ');
--- 7 rows
+-- seconds can be omitted
+insert into t values ('1:01');
+insert into t values ('1:02 ');
+insert into t values ('2.01');
+insert into t values ('2.02 ');
+
+-- 11 rows
select * from t;
delete from t;
@@ -578,3 +584,105 @@
values date('2004-04-15-16.15.32.387');
values date('2004-04-15-16.15.32.387 zz');
values date('2004-04-15-16.15.32.y');
+
+values time('13:59');
+values time('1:00');
+
+-- Test unary date and datetime functions. Test with both constant and
variable arguments.
+
+autocommit off;
+
+-- test date(integer)
+create table t( i int, d date);
+commit;
+
+insert into t values( 1, date(1)),(10,
date(10.1)),(365,date(365.1e0)),(366,date(366)),(789,date(789)),(790,date(790)),(791,date(791));
+
+-- should fail
+insert into t values( 0, date(0));
+insert into t values( -1, date(-1));
+insert into t values( 3652060, date( 3652060));
+select i,d,date(i),date(d) from t order by i;
+
+rollback;
+insert into t(i) values( 0);
+select date(i) from t;
+
+rollback;
+insert into t(i) values( -1);
+select date(i) from t;
+
+
+rollback;
+insert into t(i) values( 3652060);
+select date(i) from t;
+
+rollback;
+
+drop table t;
+create table t( s varchar(32), d date);
+commit;
+
+insert into t values('1900060', date('1900060')),
+ ('1904060', date('1904060')),
+ ('1904366', date('1904366')),
+ ('2000060', date('2000060')),
+ ('2001060', date('2001060')),
+ ('2001365', date('2001365'));
+select s,d,date(s) from t order by s;
+rollback;
+
+-- failure cases
+values( date('2001000'));
+values( date('2001366'));
+values( date('2000367'));
+values( date('xxxxxxx'));
+
+insert into t(s) values( '2001000');
+select date(s) from t;
+rollback;
+
+insert into t(s) values( '2001366');
+select date(s) from t;
+rollback;
+
+insert into t(s) values( '2000367');
+select date(s) from t;
+rollback;
+
+insert into t(s) values( 'xxxxxxx');
+select date(s) from t;
+rollback;
+
+-- test parameter
+prepare dateTimePS as 'values( date(cast(? as integer)),timestamp(cast(? as
varchar(32))))';
+execute dateTimePS using 'values(cast(1 as integer),
''2003-03-05-17.05.43.111111'')';
+execute dateTimePS using 'values(2, ''20030422190200'')';
+
+values( date(date(1)), date(timestamp('2003-03-05-17.05.43.111111')));
+
+drop table t;
+create table t( s varchar(32), ts timestamp, expected timestamp);
+commit;
+
+insert into t(ts) values( timestamp('2003-03-05-17.05.43.111111'));
+select date(ts) from t;
+rollback;
+
+-- Test special unary timestamp function rules: yyyyxxddhhmmss
+insert into t values('20000228235959', timestamp('20000228235959'),
'2000-02-28-23.59.59'),
+ ('20000229000000', timestamp('20000229000000'),
'2000-02-29-00.00.00');
+select s from t where ts <> expected or timestamp(s) <> expected or
timestamp(ts) <> expected;
+rollback;
+
+-- invalid
+values( timestamp('2000 1 1 0 0 0'));
+values( timestamp('aaaaaaaaaaaaaa'));
+
+insert into t(s) values('2000 1 1 0 0 0');
+select timestamp(s) from t;
+rollback;
+
+insert into t(s) values('aaaaaaaaaaaaaa');
+select timestamp(s) from t;
+rollback;
Index: java/testing/org/apache/derbyTesting/functionTests/master/datetime.out
===================================================================
--- java/testing/org/apache/derbyTesting/functionTests/master/datetime.out
(revision 164436)
+++ java/testing/org/apache/derbyTesting/functionTests/master/datetime.out
(working copy)
@@ -148,21 +148,21 @@
ij> -- show that overflow and underflow are not allowed
-- (SQL92 would have these report errors)
values( date('0000-01-01'));
-ERROR 22007: The string representation of a datetime value is out of range.
+ERROR 22008: '0000-01-01' is an invalid argument to the date function.
ij> values( date('2000-00-01'));
-ERROR 22007: The string representation of a datetime value is out of range.
+ERROR 22008: '2000-00-01' is an invalid argument to the date function.
ij> values( date('2000-01-00'));
-ERROR 22007: The string representation of a datetime value is out of range.
+ERROR 22008: '2000-01-00' is an invalid argument to the date function.
ij> values( date('10000-01-01'));
-ERROR 22007: The syntax of the string representation of a datetime value is
incorrect.
+ERROR 22008: '10000-01-01' is an invalid argument to the date function.
ij> values( date('2000-13-01'));
-ERROR 22007: The string representation of a datetime value is out of range.
+ERROR 22008: '2000-13-01' is an invalid argument to the date function.
ij> values( date('2000-01-32'));
-ERROR 22007: The string representation of a datetime value is out of range.
+ERROR 22008: '2000-01-32' is an invalid argument to the date function.
ij> values( date('1900-02-29'));
-ERROR 22007: The string representation of a datetime value is out of range.
+ERROR 22008: '1900-02-29' is an invalid argument to the date function.
ij> values( date('2001-02-29'));
-ERROR 22007: The string representation of a datetime value is out of range.
+ERROR 22008: '2001-02-29' is an invalid argument to the date function.
ij> values( time('25.00.00'));
ERROR 22007: The string representation of a datetime value is out of range.
ij> values( time('24.00.01'));
@@ -173,15 +173,15 @@
ERROR 22007: The string representation of a datetime value is out of range.
ij> -- show garbage in == errors out
select date( 'xxxx') from t where p is null;
-ERROR 22007: The syntax of the string representation of a datetime value is
incorrect.
+ERROR 22008: 'xxxx' is an invalid argument to the date function.
ij> select time( '') from t where p is null;
ERROR 22007: The syntax of the string representation of a datetime value is
incorrect.
ij> select timestamp( 'is there anything here?' )from t where p is null;
-ERROR 22007: The syntax of the string representation of a datetime value is
incorrect.
+ERROR 22008: 'is there anything here?' is an invalid argument to the timestamp
function.
ij> select timestamp( '1992-01- there anything here?' )from t where p is null;
-ERROR 22007: The syntax of the string representation of a datetime value is
incorrect.
+ERROR 22008: '1992-01- there anything here?' is an invalid argument to the
timestamp function.
ij> select timestamp( '--::' )from t where p is null;
-ERROR 22007: The syntax of the string representation of a datetime value is
incorrect.
+ERROR 22008: '--::' is an invalid argument to the timestamp function.
ij> select time('::::') from t where p is null;
ERROR 22007: The syntax of the string representation of a datetime value is
incorrect.
ij> -- show is not null at work
@@ -957,7 +957,16 @@
1 row inserted/updated/deleted
ij> insert into t values ('17:05:11 ');
1 row inserted/updated/deleted
-ij> -- 7 rows
+ij> -- seconds can be omitted
+insert into t values ('1:01');
+1 row inserted/updated/deleted
+ij> insert into t values ('1:02 ');
+1 row inserted/updated/deleted
+ij> insert into t values ('2.01');
+1 row inserted/updated/deleted
+ij> insert into t values ('2.02 ');
+1 row inserted/updated/deleted
+ij> -- 11 rows
select * from t;
T
--------
@@ -968,8 +977,12 @@
00:00:00
17:05:11
17:05:11
+01:01:00
+01:02:00
+02:01:00
+02:02:00
ij> delete from t;
-7 rows inserted/updated/deleted
+11 rows inserted/updated/deleted
ij> -- end value tests...
insert into t values ('24.60.60');
ERROR 22007: The string representation of a datetime value is out of range.
@@ -1027,7 +1040,174 @@
----------
2004-04-15
ij> values date('2004-04-15-16.15.32.387 zz');
-ERROR 22007: The syntax of the string representation of a datetime value is
incorrect.
+ERROR 22008: '2004-04-15-16.15.32.387 zz' is an invalid argument to the date
function.
ij> values date('2004-04-15-16.15.32.y');
-ERROR 22007: The syntax of the string representation of a datetime value is
incorrect.
+ERROR 22008: '2004-04-15-16.15.32.y' is an invalid argument to the date
function.
+ij> values time('13:59');
+1
+--------
+13:59:00
+ij> values time('1:00');
+1
+--------
+01:00:00
+ij> -- Test unary date and datetime functions. Test with both constant and
variable arguments.
+autocommit off;
+ij> -- test date(integer)
+create table t( i int, d date);
+0 rows inserted/updated/deleted
+ij> commit;
+ij> insert into t values( 1, date(1)),(10,
date(10.1)),(365,date(365.1e0)),(366,date(366)),(789,date(789)),(790,date(790)),(791,date(791));
+7 rows inserted/updated/deleted
+ij> -- should fail
+insert into t values( 0, date(0));
+ERROR 22008: '0' is an invalid argument to the date function.
+ij> insert into t values( -1, date(-1));
+ERROR 22008: '-1' is an invalid argument to the date function.
+ij> insert into t values( 3652060, date( 3652060));
+ERROR 22008: '3652060' is an invalid argument to the date function.
+ij> select i,d,date(i),date(d) from t order by i;
+I |D |3 |4
+--------------------------------------------
+1 |1970-01-01|1970-01-01|1970-01-01
+10 |1970-01-10|1970-01-10|1970-01-10
+365 |1970-12-31|1970-12-31|1970-12-31
+366 |1971-01-01|1971-01-01|1971-01-01
+789 |1972-02-28|1972-02-28|1972-02-28
+790 |1972-02-29|1972-02-29|1972-02-29
+791 |1972-03-01|1972-03-01|1972-03-01
+ij> rollback;
+ij> insert into t(i) values( 0);
+1 row inserted/updated/deleted
+ij> select date(i) from t;
+1
+----------
+ERROR 22008: '0' is an invalid argument to the date function.
+ij> rollback;
+ij> insert into t(i) values( -1);
+1 row inserted/updated/deleted
+ij> select date(i) from t;
+1
+----------
+ERROR 22008: '-1' is an invalid argument to the date function.
+ij> rollback;
+ij> insert into t(i) values( 3652060);
+1 row inserted/updated/deleted
+ij> select date(i) from t;
+1
+----------
+ERROR 22008: '3652060' is an invalid argument to the date function.
+ij> rollback;
+ij> drop table t;
+0 rows inserted/updated/deleted
+ij> create table t( s varchar(32), d date);
+0 rows inserted/updated/deleted
+ij> commit;
+ij> insert into t values('1900060', date('1900060')),
+ ('1904060', date('1904060')),
+ ('1904366', date('1904366')),
+ ('2000060', date('2000060')),
+ ('2001060', date('2001060')),
+ ('2001365', date('2001365'));
+6 rows inserted/updated/deleted
+ij> select s,d,date(s) from t order by s;
+S |D |3
+------------------------------------------------------
+1900060 |1900-03-01|1900-03-01
+1904060 |1904-02-29|1904-02-29
+1904366 |1904-12-31|1904-12-31
+2000060 |2000-02-29|2000-02-29
+2001060 |2001-03-01|2001-03-01
+2001365 |2001-12-31|2001-12-31
+ij> rollback;
+ij> -- failure cases
+values( date('2001000'));
+ERROR 22008: '2001000' is an invalid argument to the date function.
+ij> values( date('2001366'));
+ERROR 22008: '2001366' is an invalid argument to the date function.
+ij> values( date('2000367'));
+ERROR 22008: '2000367' is an invalid argument to the date function.
+ij> values( date('xxxxxxx'));
+ERROR 22008: 'xxxxxxx' is an invalid argument to the date function.
+ij> insert into t(s) values( '2001000');
+1 row inserted/updated/deleted
+ij> select date(s) from t;
+1
+----------
+ERROR 22008: '2001000' is an invalid argument to the date function.
+ij> rollback;
+ij> insert into t(s) values( '2001366');
+1 row inserted/updated/deleted
+ij> select date(s) from t;
+1
+----------
+ERROR 22008: '2001366' is an invalid argument to the date function.
+ij> rollback;
+ij> insert into t(s) values( '2000367');
+1 row inserted/updated/deleted
+ij> select date(s) from t;
+1
+----------
+ERROR 22008: '2000367' is an invalid argument to the date function.
+ij> rollback;
+ij> insert into t(s) values( 'xxxxxxx');
+1 row inserted/updated/deleted
+ij> select date(s) from t;
+1
+----------
+ERROR 22008: 'xxxxxxx' is an invalid argument to the date function.
+ij> rollback;
+ij> -- test parameter
+prepare dateTimePS as 'values( date(cast(? as integer)),timestamp(cast(? as
varchar(32))))';
+ij> execute dateTimePS using 'values(cast(1 as integer),
''2003-03-05-17.05.43.111111'')';
+1 |2
+-------------------------------------
+1970-01-01|2003-03-05-17.05.43.111111
+ij> execute dateTimePS using 'values(2, ''20030422190200'')';
+1 |2
+-------------------------------------
+1970-01-02|xxxxxxFILTERED-TIMESTAMPxxxxx
+ij> values( date(date(1)), date(timestamp('2003-03-05-17.05.43.111111')));
+1 |2
+---------------------
+1970-01-01|2003-03-05
+ij> drop table t;
+0 rows inserted/updated/deleted
+ij> create table t( s varchar(32), ts timestamp, expected timestamp);
+0 rows inserted/updated/deleted
+ij> commit;
+ij> insert into t(ts) values( timestamp('2003-03-05-17.05.43.111111'));
+1 row inserted/updated/deleted
+ij> select date(ts) from t;
+1
+----------
+2003-03-05
+ij> rollback;
+ij> -- Test special unary timestamp function rules: yyyyxxddhhmmss
+insert into t values('20000228235959', timestamp('20000228235959'),
'2000-02-28-23.59.59'),
+ ('20000229000000', timestamp('20000229000000'),
'2000-02-29-00.00.00');
+2 rows inserted/updated/deleted
+ij> select s from t where ts <> expected or timestamp(s) <> expected or
timestamp(ts) <> expected;
+S
+--------------------------------
+ij> rollback;
+ij> -- invalid
+values( timestamp('2000 1 1 0 0 0'));
+ERROR 22008: '2000 1 1 0 0 0' is an invalid argument to the timestamp function.
+ij> values( timestamp('aaaaaaaaaaaaaa'));
+ERROR 22008: 'aaaaaaaaaaaaaa' is an invalid argument to the timestamp function.
+ij> insert into t(s) values('2000 1 1 0 0 0');
+1 row inserted/updated/deleted
+ij> select timestamp(s) from t;
+1
+--------------------------
+ERROR 22008: '2000 1 1 0 0 0' is an invalid argument to the timestamp function.
+ij> rollback;
+ij> insert into t(s) values('aaaaaaaaaaaaaa');
+1 row inserted/updated/deleted
+ij> select timestamp(s) from t;
+1
+--------------------------
+ERROR 22008: 'aaaaaaaaaaaaaa' is an invalid argument to the timestamp function.
+ij> rollback;
ij>
