db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From myrn...@apache.org
Subject svn commit: r689237 - in /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests: master/datetime.out suites/derbylang.runall tests/lang/DateTimeTest.java tests/lang/datetime.sql
Date Tue, 26 Aug 2008 21:27:22 GMT
Author: myrnavl
Date: Tue Aug 26 14:27:21 2008
New Revision: 689237

URL: http://svn.apache.org/viewvc?rev=689237&view=rev
Log:
DERBY-3653; convert lang/datetime.sql to junit
  Patch contributed by Junjie Peng.

Added:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DateTimeTest.java   (with props)
Removed:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/datetime.out
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/datetime.sql
Modified:
    db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall

Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall?rev=689237&r1=689236&r2=689237&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall Tue Aug 26 14:27:21 2008
@@ -10,7 +10,6 @@
 lang/consistencyChecker.sql
 lang/currentSchema.sql
 lang/cursorerrors.sql
-lang/datetime.sql
 lang/db2Compatibility.sql
 lang/ddlTableLockMode.sql
 lang/declareGlobalTempTableJavaJDBC30.java

Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DateTimeTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DateTimeTest.java?rev=689237&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DateTimeTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DateTimeTest.java Tue Aug 26 14:27:21 2008
@@ -0,0 +1,1712 @@
+/*
+
+   Derby - Class org.apache.derbyTesting.functionTests.tests.lang.DateTimeTest
+
+       Licensed to the Apache Software Foundation (ASF) under one
+       or more contributor license agreements.  See the NOTICE file
+       distributed with this work for additional information
+       regarding copyright ownership.  The ASF licenses this file
+       to you 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.derbyTesting.functionTests.tests.lang;
+
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.sql.Statement;
+
+import junit.framework.Test;
+import junit.framework.TestSuite;
+
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.CleanDatabaseTestSetup;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.TestConfiguration;
+
+/**
+ * Test the builtin date/time types assumes these builtin types 
+ * exist: int, smallint, char, varchar, real other things we might test: 
+ * interaction with UUID and other user defined types 
+ * compatibility with dynamic parameters and JDBC getDate etc. methods.
+ */
+public final class DateTimeTest extends BaseJDBCTestCase {
+
+    /**
+     * Public constructor required for running test as standalone JUnit.
+     */
+    public DateTimeTest(String name) {
+        super(name);
+    }
+
+    public static Test suite() {
+        TestSuite suite = new TestSuite("DateTimeTest");
+        suite.addTest(baseSuite("DateTimeTest:Embedded"));
+        suite.addTest(TestConfiguration
+                .clientServerDecorator(baseSuite("DateTimeTest:Client")));
+        return suite;
+    }
+
+    protected static Test baseSuite(String name) {
+        TestSuite suite = new TestSuite(name);
+        suite.addTestSuite(DateTimeTest.class);
+        return new CleanDatabaseTestSetup(suite) {
+            /**
+             * Creates the tables used in the test cases.
+             * @exception SQLException if a database error occurs
+             */
+            protected void decorateSQL(Statement stmt) throws SQLException {
+                createTableForArithmeticTest(stmt);
+                createTableForSyntaxTest(stmt);
+                createTableForConversionTest(stmt);
+                createTableForISOFormatTest(stmt);
+            }
+        };
+    }
+    
+    private static void createTableForISOFormatTest(Statement st)
+    throws SQLException{
+        st.executeUpdate(" create table ts (ts1 timestamp, ts2 timestamp)");   
+    }
+    
+    
+    private static void createTableForConversionTest(Statement st) throws SQLException{
+        st.executeUpdate(
+                " create table convtest(d date, t time, ts timestamp)");
+        st.executeUpdate(" insert into convtest values(date('1932-03-21'),  "
+                + "time('23:49:52'), timestamp('1832-09-24 10:11:43.32'))");
+        st.executeUpdate(" insert into convtest values(date('0001-03-21'),  "
+                + "time('5:22:59'), timestamp('9999-12-31 23:59:59.999999'))");
+        st.executeUpdate(" insert into convtest values(null, null, null)");
+    }
+    
+    private static void createTableForSyntaxTest(Statement stmt) 
+    throws SQLException{
+        stmt.executeUpdate("create table source (i int, s smallint, c char(10), "
+                + "v varchar(50), d double precision, r real, e date, "
+                + "t time, p timestamp)");
+
+        stmt.executeUpdate(" create table target (e date not null, t time not "
+                + "null, p timestamp not null)");
+    }
+        
+    private static void createTableForArithmeticTest(Statement stmt)
+    throws SQLException{
+        stmt.executeUpdate("create table t (i int, s smallint, " +
+                "c char(10), v varchar(50), d double precision," +
+                " r real, e date, t time, p timestamp)");
+
+        stmt.executeUpdate(" insert into t values (null, null, " +
+                "null, null, null, null, null, null, null)");
+
+        stmt.executeUpdate(" insert into t values (0, 100, 'hello', " +
+                "'everyone is here', 200.0e0, 300.0e0, " +
+                "date('1992-01-01'), time('12:30:30'), " +
+                "timestamp('1992-01-01 12:30:30'))");
+
+        stmt.executeUpdate(" insert into t values (-1, -100, " +
+                "'goodbye', 'everyone is there', -200.0e0, " +
+                "-300.0e0, date('1992-01-01'), time('12:30:30'), " +
+                "timestamp('1992-01-01 12:30:45'))");
+    }
+    
+    /**
+     * date/times don't support math, show each combination.
+     */
+    public void testArithOpers_math() throws SQLException{
+        Statement st = createStatement();
+        
+        assertStatementError("42Y95", st, "select e + e from t");
+
+        assertStatementError("42Y95", st, " select i + e from t");
+
+        assertStatementError("42Y95", st, " select p / p from t");
+
+        assertStatementError("42Y95", st, " select p * s from t");
+
+        assertStatementError("42Y95", st, " select t - t from t");
+
+        assertStatementError("42X37", st, " select -t from t");
+
+        assertStatementError("42X37", st, " select +e from t");
+        
+        st.close();
+    }
+    
+    public void testArithOpers_Comarision() throws SQLException{
+        ResultSet rs = null;
+        Statement st = createStatement();
+
+        rs = st.executeQuery("select e from t where e = date('1992-01-01')");
+        JDBC.assertColumnNames(rs, new String[] { "E" });
+        JDBC.assertFullResultSet(rs, 
+                new String[][] { { "1992-01-01" }, { "1992-01-01" } }, true);
+
+        rs = st.executeQuery(" select e from t where date('1992-01-01') = e");
+        JDBC.assertColumnNames(rs, new String[] { "E" });
+        JDBC.assertFullResultSet(rs, 
+                new String[][] { { "1992-01-01" }, { "1992-01-01" } }, true);
+
+        rs = st.executeQuery(" select t from t where t > time('09:30:15')");
+        JDBC.assertColumnNames(rs, new String[] { "T" });
+        JDBC.assertFullResultSet(rs, 
+                new String[][] { { "12:30:30" }, { "12:30:30" } }, true);
+
+        rs = st.executeQuery(" select t from t where time('09:30:15') < t");
+        JDBC.assertColumnNames(rs, new String[] { "T" });
+        JDBC.assertFullResultSet(rs,
+                new String[][] { { "12:30:30" }, { "12:30:30" } }, true);
+
+        rs = st.executeQuery(
+                "select p from t where p < timestamp('1997-06-30 01:01:01')");
+        JDBC.assertColumnNames(rs, new String[] { "P" });
+        JDBC.assertFullResultSet(rs, 
+                new String[][] { { "1992-01-01 12:30:30.0" },
+                { "1992-01-01 12:30:45.0" } }, true);
+
+        rs = st.executeQuery(
+                "select p from t where timestamp('1997-06-30 01:01:01' )> p");
+        JDBC.assertColumnNames(rs, new String[] { "P" });
+        JDBC.assertFullResultSet(rs, 
+                new String[][] { { "1992-01-01 12:30:30.0" },
+                { "1992-01-01 12:30:45.0" } }, true);
+        
+        rs = st.executeQuery("select e from t where e >= date('1990-01-01')");
+        JDBC.assertColumnNames(rs, new String[] { "E" });
+        JDBC.assertFullResultSet(rs, 
+                new String[][] { { "1992-01-01" }, { "1992-01-01" } }, true);
+
+        rs = st.executeQuery(" select e from t where date('1990-01-01')<= e");
+        JDBC.assertColumnNames(rs, new String[] { "E" });
+        JDBC.assertFullResultSet(rs, 
+                new String[][] { { "1992-01-01" }, { "1992-01-01" } }, true);
+
+        rs = st.executeQuery(" select t from t where t <= time('09:30:15')");
+        JDBC.assertColumnNames(rs, new String[] { "T" });
+        JDBC.assertDrainResults(rs, 0);
+
+        rs = st.executeQuery(" select t from t where time('09:30:15') >= t");
+        JDBC.assertColumnNames(rs, new String[] { "T" });
+        JDBC.assertDrainResults(rs, 0);
+
+        rs = st.executeQuery(
+                "select p from t where p <> timestamp('1997-06-30 01:01:01')");
+        JDBC.assertColumnNames(rs, new String[] { "P" });
+        JDBC.assertFullResultSet(rs, 
+                new String[][] { { "1992-01-01 12:30:30.0" },
+                { "1992-01-01 12:30:45.0" } }, true);
+
+        rs = st.executeQuery(
+                "select p from t where timestamp('1997-06-30 01:01:01' )<> p");
+        JDBC.assertColumnNames(rs, new String[] { "P" });
+        JDBC.assertFullResultSet(rs, 
+                new String[][] { { "1992-01-01 12:30:30.0" },
+                { "1992-01-01 12:30:45.0" } }, true);
+        
+        st.close();
+    }
+    
+    /**
+     * Show comparisons with mixed types don't work.
+     */
+    public void testArithOpers_CompraionOnMixedTypes() throws SQLException{
+        Statement st = createStatement();
+        
+        assertStatementError("42818", st, "select e from t where e <= i");
+
+        assertStatementError("42818", st, " select e from t where t < s");
+
+        assertStatementError("42818", st, " select e from t where p > d");
+
+        assertStatementError("42818", st, " select e from t where e >= t");
+
+        assertStatementError("42818", st, " select e from t where t <> p");
+
+        assertStatementError("42818", st, " select e from t where p = e");
+
+        st.close();
+    }
+    
+    /**
+     * Look for a value that isn't in the table.
+     */
+    public void testArithOpers_CompraionOnNotExistingValue() 
+    throws SQLException{
+        ResultSet rs = null;
+        Statement st = createStatement();
+        
+        rs = st.executeQuery("select e from t where e <> date('1992-01-01')");
+        JDBC.assertColumnNames(rs, new String[] { "E" });
+        JDBC.assertDrainResults(rs, 0);
+
+        rs = st.executeQuery("select e from t where date('1992-01-01') <> e");
+        JDBC.assertColumnNames(rs, new String[] { "E" });
+        JDBC.assertDrainResults(rs, 0);
+
+        st.close();
+    }
+    
+    /**
+     * Show garbage in == errors out
+     */
+    public void testArithOpers_ComparisonOnGarbage() throws SQLException {
+        Statement st = createStatement();
+        
+        assertStatementError("22008", st, 
+                "select date( 'xxxx') from t where p is null");
+
+        assertStatementError("22007", st, 
+                " select time( '') from t where p is null");
+
+        assertStatementError("22008", st,
+                " select timestamp( 'is there anything here?' )from " +
+                "t where p is null");
+
+        assertStatementError("22008", st,
+                " select timestamp( '1992-01- there anything here?')" +
+                "from t where p is null");
+
+        assertStatementError("22008", st,
+                " select timestamp( '--::' )from t where p is null");
+
+        assertStatementError("22007", st, 
+                " select time('::::') from t where p is null");
+        
+        st.close();
+    }
+    
+    /**
+     * Check limit values.
+     */
+    public void testArithOpers_ComparisonOnLimits() throws SQLException {
+        ResultSet rs = null;
+        Statement st = createStatement();
+
+        rs = st.executeQuery("values( date('0001-1-1'), date('9999-12-31'), "
+                + "date('2/29/2000'), date('29.2.2004'))");
+        JDBC.assertColumnNames(rs, new String[] { "1", "2", "3", "4" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "0001-01-01", 
+                "9999-12-31", "2000-02-29", "2004-02-29" } }, true);
+
+        rs = st.executeQuery(" values( time('00:00:00'), time('23:59:59'))");
+        JDBC.assertColumnNames(rs, new String[] { "1", "2" });
+        JDBC.assertFullResultSet(rs,
+                new String[][] { { "00:00:00", "23:59:59" } }, true);
+
+        rs = st.executeQuery(" values( time('00 AM'), time( '12:59 AM')," +
+        		" time('1 PM'), time('12:59 PM'))");
+        JDBC.assertColumnNames(rs, new String[] { "1", "2", "3", "4" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "00:00:00", 
+                "00:59:00", "13:00:00", "12:59:00" } }, true);
+
+        rs = st.executeQuery(" values( time('00.00.00'), time('23.59.59'), " +
+                "time('24.00.00'))");
+        JDBC.assertColumnNames(rs, new String[] { "1", "2", "3" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "00:00:00", 
+                "23:59:59", "00:00:00" } }, true);
+
+        rs = st.executeQuery(" values( timestamp('0001-1-1 00:00:00'), "
+                + "timestamp('9999-12-31 23:59:59.999999'))");
+        JDBC.assertColumnNames(rs, new String[] { "1", "2" });
+        JDBC.assertFullResultSet(rs, new String[][] { { 
+            "0001-01-01 00:00:00.0", "9999-12-31 23:59:59.999999" } }, true);
+        
+        st.close();
+    }
+    
+    /**
+     * Show that overflow and underflow are not allowed
+     *  (SQL92 would have these report errors).
+     */
+    public void testArithOpers_ComparisonOnBeyondLimits() throws SQLException {
+        Statement st = createStatement();
+        
+        assertStatementError("22008", st , "values( date('0000-01-01'))");
+
+        assertStatementError("22008", st, " values( date('2000-00-01'))");
+
+        assertStatementError("22008", st, " values( date('2000-01-00'))");
+
+        assertStatementError("22008", st, " values( date('10000-01-01'))");
+
+        assertStatementError("22008", st, " values( date('2000-13-01'))");
+
+        assertStatementError("22008", st, " values( date('2000-01-32'))");
+
+        assertStatementError("22008", st, " values( date('1900-02-29'))");
+
+        assertStatementError("22008", st, " values( date('2001-02-29'))");
+
+        assertStatementError("22007", st, " values( time('25.00.00'))");
+
+        assertStatementError("22007", st, " values( time('24.00.01'))");
+
+        assertStatementError("22007", st, " values( time('0:60:00'))");
+
+        assertStatementError("22007", st, " values( time('00:00:60'))");
+
+        st.close();
+    }
+    
+    public void testArithOpers_ComparisonOnNullAndNonNull() 
+    throws SQLException {
+        ResultSet rs = null;
+        Statement st = createStatement();
+        
+        rs = st.executeQuery("select e, t, p from t " +
+        		"where e = e or t = t or p = p");
+        JDBC.assertColumnNames(rs, new String[] { "E", "T", "P" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "1992-01-01", 
+                "12:30:30", "1992-01-01 12:30:30.0" }, { "1992-01-01",
+                "12:30:30", "1992-01-01 12:30:45.0" } }, true);
+        
+        rs = st.executeQuery("select * from t where e is not null " +
+        		"and t is not " + "null and p is not null");
+        JDBC.assertColumnNames(rs, new String[] { "I", "S", "C", "V",
+                "D", "R", "E", "T", "P" });
+        JDBC.assertFullResultSet(rs,  new String[][] {
+                { "0", "100", "hello", "everyone is here", "200.0", "300.0",
+                    "1992-01-01", "12:30:30", "1992-01-01 12:30:30.0" },
+                { "-1", "-100", "goodbye", "everyone is there", "-200.0",
+                    "-300.0", "1992-01-01", "12:30:30", 
+                    "1992-01-01 12:30:45.0" } }, true);
+        
+        st.close();
+    }
+
+    /**
+     * Test =SQ .
+     */
+    public void testArithOpers_ComparisonOnEqualSQ() throws SQLException{
+        ResultSet rs = null;
+        Statement st = createStatement();
+
+        assertStatementError("21000", st,
+                "select 'fail' from t where e = (select e from t)");
+
+        rs = st.executeQuery("select 'pass' from t " +
+        		"where e = (select e from t where d=200)");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs,
+                new String[][] { { "pass" }, { "pass" } }, true);
+
+        assertStatementError("21000", st,
+                "select 'fail' from t where t = (select t from t)");
+
+        rs = st.executeQuery("select 'pass' from t " +
+        		"where t = (select t from t where d=200)");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, 
+                new String[][] { { "pass" }, { "pass" } }, true);
+
+        assertStatementError("21000", st,
+                "select 'fail' from t where p = (select p from t)");
+
+        rs = st.executeQuery("select 'pass' from t " +
+        		"where p = (select p from t where d=200)");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "pass" } }, true);
+
+        st.close();
+    }
+    
+    /**
+     * Test syntax: precision cannot be specified.
+     */
+    public void testSyntax_SpecifiedPrecision() throws SQLException{        
+        Statement st = createStatement();
+        
+        assertStatementError("42X01", st , 
+                "create table wrong (t time(-100))");
+
+        assertStatementError("42X01", st, 
+                " create table wrong (t time(0))");
+
+        assertStatementError("42X01", st, 
+                " create table wrong (t time(23))");
+
+        assertStatementError("42X01", st,
+                " create table wrong (t timestamp(-100))");
+
+        assertStatementError("42X01", st,
+                " create table wrong (t timestamp(0))");
+
+        assertStatementError("42X01", st,
+                " create table wrong (t timestamp(6))");
+
+        assertStatementError("42X01", st,
+                " create table wrong (t timestamp(9))");
+
+        assertStatementError("42X01", st,
+                " create table wrong (t timestamp(23))");
+        
+        st.close();
+    }
+    
+
+    /**
+     * Test a variety of inserts.
+     */
+    public void testSyntax_Insert() throws SQLException{
+        Statement st = createStatement();
+        
+        st.executeUpdate("insert into source values (1, 2, '3', '4', 5, 6, "
+                + "date('1997-07-07'), "
+                + "time('08:08:08'),timestamp('1999-09-09 09:09:09'))");
+        
+        st.executeUpdate("insert into target select e,t,p from source");
+        
+        //wrong columns should fail
+        assertStatementError("42821", st,
+                "insert into target select p,e,t from source");
+        
+        assertStatementError("42821", st,
+        " insert into target select i,s,d from source");
+
+        assertStatementError("42821", st,
+            " insert into target (t,p) select c,r from source");
+
+        assertUpdateCount(st, 1, " delete from source");
+        
+        
+        st.executeUpdate(" insert into source values (null, null, null, null, "
+                + "null, null, null, null, null)");
+
+        // these fail because the target won't take a null -- of any type
+        assertStatementError("23502", st,
+                "insert into target values(null, null, null)");
+
+        assertStatementError("23502", st,
+                " insert into target select e,t,p from source");
+        
+        //these still fail with type errors:
+        assertStatementError("42821", st,
+                "insert into target select p,e,t from source");
+
+        assertStatementError("42821", st,
+                " insert into target select i,s,d from source");
+
+        assertStatementError("42821", st,
+                " insert into target (t,p)select c,r from source");
+
+        //expect 1 row in target.
+        ResultSet rs = st.executeQuery("select * from target");
+        JDBC.assertColumnNames(rs, new String[] { "E", "T", "P" });
+        JDBC.assertFullResultSet(rs, new String[][] { {
+                "1997-07-07", "08:08:08", "1999-09-09 09:09:09.0" } }, true);
+        
+        st.close();
+    }
+    
+    /**
+     * Test a variety of updates.
+     */
+    public void testSyntax_Update() throws SQLException{
+        Statement st = createStatement();
+        
+        //expect 1 row in target.
+        ResultSet rs = st.executeQuery("select * from target");
+        JDBC.assertColumnNames(rs, new String[] { "E", "T", "P" });
+        JDBC.assertFullResultSet(rs, new String[][] { {
+                "1997-07-07", "08:08:08", "1999-09-09 09:09:09.0" } }, true);
+        
+        // unchanged:
+        assertUpdateCount(st, 1, "update target set e = e, t = t, p = p");
+        rs = st.executeQuery(" select * from target");
+        JDBC.assertColumnNames(rs, new String[] { "E", "T", "P" });
+        JDBC.assertFullResultSet(rs, new String[][] { { 
+                "1997-07-07", "08:08:08", "1999-09-09 09:09:09.0" } }, true);
+
+        // alters the row:
+        assertUpdateCount(st, 1, "update target set e = date('1990-01-01')");
+        rs = st.executeQuery(" select * from target");
+        JDBC.assertColumnNames(rs, new String[] { "E", "T", "P" });
+        JDBC.assertFullResultSet(rs, new String[][] { {
+                "1990-01-01", "08:08:08", "1999-09-09 09:09:09.0" } }, true);
+        
+        //not settable to null
+        assertStatementError("23502", st, "update target set e = null");
+        rs = st.executeQuery(" select * from target");
+        JDBC.assertColumnNames(rs, new String[] { "E", "T", "P" });
+        JDBC.assertFullResultSet(rs, new String[][] { { 
+                "1990-01-01", "08:08:08", "1999-09-09 09:09:09.0" } }, true);
+        
+        // nullable col can be set to null:
+        assertUpdateCount(st, 1, "update source set e = date('1492-10-01')");
+        rs = st.executeQuery(" select e from source");
+        JDBC.assertColumnNames(rs, new String[] { "E" });
+        JDBC.assertFullResultSet(rs, 
+                new String[][] { { "1492-10-01" } }, true);
+
+        assertUpdateCount(st, 1, " update source set e = null");
+        rs = st.executeQuery(" select e from source");
+        JDBC.assertColumnNames(rs,  new String[] { "E" });
+        JDBC.assertFullResultSet(rs, new String[][] { { null } }, true);
+        
+        // these should get type errors
+        assertStatementError("42821", st, "update target set e = 1");
+        assertStatementError("42821", st, " update source set p = 1.4e10");
+        assertStatementError("42821", st,
+                " update source set i = date('1001-01-01')");
+        
+        st.close();
+    }
+    
+    public void testSyntax_CurrentFunctions() throws SQLException{
+        ResultSet rs = null;
+        Statement st = createStatement();
+        
+        // tests with current functions:
+        assertUpdateCount(st, 1, "delete from source");
+        assertUpdateCount(st, 1, " delete from target");
+
+        st.executeUpdate(" insert into source values (1, 2, '3', " +
+        		"'4', 5, 6, date('1997-06-07'), time('08:08:08'), " +
+        		"timestamp('9999-09-09 09:09:09'))");
+
+        // these tests are 'funny' so that the masters won't show 
+        // a diff every time.
+        rs = st.executeQuery("select 'pass' from source where current_date = "
+                + "current_date and current_time = current_time and "
+                + "current_timestamp = current_timestamp");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "pass" } }, true);
+
+        rs = st.executeQuery(" select 'pass' from source where current_date > "
+                + "date('1996-12-31') and current_time <= "
+                + "time(        '23:59:59') -- may oopsie on leap second days "
+                + "and current_timestamp <> timestamp( -- this comment "
+                + "is just more whitespace '1996-12-31 00:00:00')");
+        JDBC.assertColumnNames(rs, new String[] { "1" });        
+        JDBC.assertFullResultSet(rs, new String[][] { { "pass" } }, true);
+
+        // test with DB2 compatible syntax
+        rs = st.executeQuery("select 'pass' from source where current date = "
+                + "current date and current time = current time and "
+                + "current timestamp = current timestamp");
+        JDBC.assertFullResultSet(rs, new String[][] { { "pass" } }, true);
+
+        rs = st.executeQuery(" select 'pass' from source where current date > "
+                + "date('1996-12-31') and current time <= "
+                + "time(        '23:59:59') -- may oopsie on leap second days "
+                + "and current timestamp <> timestamp( -- this comment "
+                + "is just more whitespace '1996-12-31 00:00:00')");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "pass" } }, true);
+    }
+    
+    public void testSyntax_EscapedFunctions() throws SQLException{
+        ResultSet rs = null;
+        Statement st = createStatement();
+        
+        //CURRENT_DATE escaped function not supported in DB2 UDB 
+        //CURRENT_TIME escaped function not supported in DB2 UDB
+        assertStatementError("42X01", st, "select 'pass' from source " +
+        		"where current_date = {fn current_date()} " +
+        		"and current_time = {fn current_time()} " +
+        		"and current_timestamp = current_timestamp");
+
+        rs = st.executeQuery(" select 'pass' from source " +
+        		"where current_date = {fn curdate()} " +
+        		"and current_time = {fn curtime()} " +
+        		"and current_timestamp = current_timestamp");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "pass" } }, true);
+
+        
+        // current_date() and current_time() not valid in DB2. 
+        // curdate() and curtime() are as escaped functions only.
+        assertStatementError("42Y03", st, "values curdate()");
+
+        assertStatementError("42Y03", st, " values curtime()");
+
+        assertStatementError("42X01", st, " values current_date()");
+
+        assertStatementError("42X01", st, " values current_time()");
+
+        assertStatementError("42X01", st, " values {fn current_date()}");
+
+        assertStatementError("42X01", st, " values {fn current_time()}");
+
+        
+        // DB2 UDB compatible test for escaped functions
+        rs = st.executeQuery("select 'pass' from source " +
+        		"where hour(current_time) = {fn hour(current_time)} " +
+        		"and minute(current_time) = {fn minute(current_time)}" +
+        		" and second(current_time) = {fn second(current_time)} " +
+        		"and year(current_date)   = {fn year(current_date)}");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "pass" } }, true);
+
+        
+        // valid jdbc date and time escaped functions
+        rs = st.executeQuery("values {fn hour('23:38:10')}");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "23" } }, true);
+
+        rs = st.executeQuery(" values {fn minute('23:38:10')}");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "38" } }, true);
+
+        rs = st.executeQuery(" values {fn second('23:38:10')}");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "10" } }, true);
+
+        rs = st.executeQuery(" values {fn year('2004-03-22')}");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "2004" } }, true);
+
+        
+        // currents do have types, these inserts fail:
+        assertStatementError("42821", st,
+                "insert into source values (0, 0, '0', '0', 0, 0, "
+                        + "current_time, current_time, current_timestamp)");
+
+        assertStatementError("42821", st, " insert into source values" +
+        		" (0, 0, '0', '0', 0, 0, current_date, " +
+        		"current_timestamp, current_timestamp)");
+
+        assertStatementError("42821", st,
+                " insert into source values (0, 0, '0', '0', 0, 0, "
+                        + "current_date, current_time, current_date)");
+
+        // this insert works
+        st.executeUpdate("insert into source values (0, 0, '0', '0', 0, 0, "
+                + "current_date, current_time, current_timestamp)");
+
+        // test with DB2 syntax this insert works
+        st.executeUpdate("insert into source values (0, 0, '0', '0', 0, 0, "
+                + "current date, current time, current timestamp)");
+
+        // this test will diff if the select is run just after 
+        // midnight, and the insert above was run just before 
+        // midnight...
+        rs = st.executeQuery("select * from source " +
+        		"where e <> current_date and p <> current_timestamp");
+        JDBC.assertColumnNames(rs, new String[] { "I", "S", "C", "V", "D", 
+                "R", "E", "T", "P" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "1", "2", 
+            "3", "4", "5.0", "6.0", "1997-06-07", "08:08:08",
+            "9999-09-09 09:09:09.0" } }, true);
+
+        
+        // test with DB2 syntax
+        rs = st.executeQuery("select * from source " +
+        		"where e <> current date and p <> current timestamp");
+        JDBC.assertColumnNames(rs, new String[] { "I", "S", "C", "V", 
+                "D", "R", "E", "T", "P" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "1", "2", "3",
+            "4", "5.0", "6.0", "1997-06-07", "08:08:08", 
+            "9999-09-09 09:09:09.0" } }, true);
+
+        rs = st.executeQuery(" select 'pass' from source " +
+        		"where e <= current_date and p <= current_timestamp");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, 
+                new String[][] { { "pass" }, { "pass" } }, true);
+
+        // reduce it back to one row
+        assertUpdateCount(st, 2, "delete from source where i=0");
+        
+        st.close();
+    }
+    
+    public void testSyntax_Extract() throws SQLException{
+        Statement st = createStatement();
+        
+        ResultSet rs = st.executeQuery("select year( e), month( e), day( date( "
+                + "'1997-01-15')), hour( t), minute( t), second( time( "
+                + "'01:01:42')), year( p), month( p), day( p), hour( "
+                + "timestamp( '1992-01-01 14:11:23')), minute( p), "
+                + "second( p) from source");
+        JDBC.assertColumnNames(rs, new String[] { "1", "2", "3", "4", 
+                "5", "6", "7", "8", "9", "10", "11", "12" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "1997", "6", "15", 
+            "8", "8", "42", "9999", "9", "9", "14", "9", "9.0" } }, true);
+
+        // extract won't work on other types
+        assertStatementError("42X25", st, "select month( i) from source");
+
+        assertStatementError("42X25", st, " select hour( d) from source");
+
+        // extract won't work on certain field/type combos
+        assertStatementError("42X25", st, "select month( t) from source");
+
+        assertStatementError("42X25", st, " select day( t) from source");
+
+        assertStatementError("42X25", st, " select year( t) from source");
+
+        assertStatementError("42X25", st, " select hour( e) from source");
+
+        assertStatementError("42X25", st, " select minute( e) from source");
+
+        assertStatementError("42X25", st, " select second( e) from source");
+
+        assertUpdateCount(st, 1,
+                " update source set i=month( e), s=minute( t), d=second( p)");
+
+        // should be true and atomics should match field named as 
+        // label in date/times
+        rs = st.executeQuery("select i,e as \"month\",s,t " +
+                "as \"minute\",d,p as \"second\" from source " +
+                "where (i = month(e)) and (s = minute(t)) " +
+                "and (d = second(p))");
+        JDBC.assertColumnNames(rs, new String[] { "I", "month", 
+                "S", "minute", "D", "second" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "6", "1997-06-07", 
+                "8", "08:08:08", "9.0", "9999-09-09 09:09:09.0" } }, true);
+
+        // fields should match the fields in the date (in order)
+        rs = st.executeQuery("select p, year( p) as \"year\", month( p) as "
+                + "\"month\", day( p) as \"day\", hour( p) as "
+                + "\"hour\", minute( p) as \"minute\", second( p) as "
+                + "\"second\" from source");
+        JDBC.assertColumnNames(rs, new String[] { "P", "year", "month", 
+                "day", "hour", "minute", "second" });
+        JDBC.assertFullResultSet(rs, new String[][] { { 
+            "9999-09-09 09:09:09.0", "9999", "9", "9",
+            "9", "9", "9.0" } }, true);
+
+        
+        // jdbc escape sequences
+        rs = st.executeQuery("values ({d '1999-01-12'}, {t '11:26:35'}, {ts "
+                + "'1999-01-12 11:26:51'})");
+        JDBC.assertColumnNames(rs, new String[] { "1", "2", "3" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "1999-01-12", 
+                "11:26:35", "1999-01-12 11:26:51.0" } }, true);
+
+        rs = st.executeQuery(" values year( {d '1999-01-12'})");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "1999" } }, true);
+
+        rs = st.executeQuery(" values hour( {t '11:28:10'})");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "11" } }, true);
+
+        rs = st.executeQuery(" values day( {ts '1999-01-12 11:28:23'})");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "12" } }, true);
+        
+        st.close();
+    }
+    
+    /**
+     * Random tests for date.
+     */
+    public void testRandom() throws SQLException{        
+        Statement st = createStatement();
+        
+        st.executeUpdate("create table sertest(d date, s Date, o Date)");
+        st.executeUpdate(" insert into sertest values (date('1992-01-03'), " +
+        		"null, null)");
+        
+        ResultSet rs = st.executeQuery(" select * from sertest");
+        JDBC.assertColumnNames(rs, new String[] { "D", "S", "O" });
+        JDBC.assertFullResultSet(rs,
+                new String[][] { { "1992-01-03", null, null } }, true);
+        
+        assertUpdateCount(st, 1, " update sertest set s=d");
+        assertUpdateCount(st, 1, " update sertest set o=d");
+        st.executeUpdate(" insert into sertest values (date( '3245-09-09'), "
+                + "date( '1001-06-07'), date( '1999-01-05'))");
+        
+        rs = st.executeQuery(" select * from sertest");
+        JDBC.assertColumnNames(rs, new String[] { "D", "S", "O" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "1992-01-03", 
+                "1992-01-03", "1992-01-03" }, { "3245-09-09", "1001-06-07", 
+                    "1999-01-05" } }, true);
+
+        rs = st.executeQuery(" select * from sertest where d > s");
+        JDBC.assertColumnNames(rs, new String[] { "D", "S", "O" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "3245-09-09", 
+                "1001-06-07", "1999-01-05" } }, true);
+
+        assertUpdateCount(st, 2, " update sertest set d=s");
+
+        // should get type errors:
+        assertStatementError("42821", st,
+                "insert into sertest values (date('3245-09-09'), "
+                        + "time('09:30:25'), null)");
+
+        assertStatementError("42821", st,
+                " insert into sertest values (null, null, time('09:30:25'))");
+
+        assertStatementError("42821", st,
+                " insert into sertest values (null, null, "
+                        + "timestamp('1745-01-01 09:30:25'))");
+
+        assertUpdateCount(st, 2, "update sertest set d=o");
+
+        rs = st.executeQuery(" select * from sertest where s is null " +
+        		"and o is not null");
+        JDBC.assertColumnNames(rs, new String[] { "D", "S", "O" });
+        JDBC.assertDrainResults(rs, 0);
+
+        rs = st.executeQuery("select month(s) from sertest " +
+        		"where s is not null");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "1" }, { "6" } },
+                true);
+
+        rs = st.executeQuery(" select day(o) from sertest");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "3" }, { "5" } }, 
+                true);
+
+        dropTable("sertest");
+    }
+    
+    public void testConvertFromString() throws SQLException{        
+        Statement st = createStatement();
+        
+        st.executeUpdate("create table convstrtest(d varchar(30), t char(30), "
+                + "ts long varchar)");
+        st.executeUpdate(" insert into convstrtest values('1932-03-21',  "
+                + "'23:49:52', '1832-09-24 10:11:43.32')");
+        st.executeUpdate(" insert into convstrtest values(null, null, null)");
+
+        assertStatementError("22007", st,
+                "select CAST (d AS time) from convstrtest");
+
+        assertStatementError("22007", st,
+                " select CAST (t AS date) from convstrtest");
+
+        assertStatementError("42846", st,
+                " select CAST (ts AS time) from convstrtest");
+
+        assertStatementError("42846", st,
+                " select CAST (ts AS date) from convstrtest");
+
+
+        ResultSet rs = st.executeQuery("select CAST (t AS time) from convstrtest");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "23:49:52" }, 
+                { null } }, true);
+
+        rs = st.executeQuery(" select CAST (d AS date) from convstrtest");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, 
+                new String[][] { { "1932-03-21" }, { null } }, true);
+
+        // show time and date separately as timestamp will be 
+        // filtered out
+        assertStatementError("42846", st, "select " +
+                "CAST(CAST (ts AS timestamp) AS date), " +
+                "CAST(CAST (ts AS timestamp) AS time) from convstrtest");
+
+        dropTable("convstrtest");
+        
+        st.close();
+    }
+    
+    public void testConversion_Aggregates() throws SQLException{
+        Statement st = createStatement();
+        
+        //test aggregates sum should fail
+        assertStatementError("42Y22", st, "select sum(d) from convtest");
+
+        assertStatementError("42Y22", st, " select sum(t) from convtest");
+
+        assertStatementError("42Y22", st, " select sum(ts) from convtest");
+
+        // these should work
+        ResultSet rs = st.executeQuery("select count(d) from convtest");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "2" }, }, true);
+
+        rs = st.executeQuery(" select count(t) from convtest");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "2" }, }, true);
+
+        rs = st.executeQuery(" select count(ts) from convtest");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "2" }, }, true);
+
+        st.executeUpdate(" insert into convtest values(date('0001-03-21'),  "
+                + "time('5:22:59'), timestamp('9999-12-31 23:59:59.999999'))");
+
+        // distinct count should be 2 not 3
+        rs = st.executeQuery("select count(distinct d) from convtest");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "2" }, }, true);
+
+        rs = st.executeQuery(" select count(distinct t) from convtest");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "2" }, }, true);
+
+        rs = st.executeQuery(" select count(distinct ts) from convtest");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "2" }, }, true);
+
+        // min should not be null!!!!!!!!
+        rs = st.executeQuery("select min(d) from convtest");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "0001-03-21" }, }, 
+                true);
+
+        rs = st.executeQuery(" select min(t) from convtest");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, 
+                new String[][] { { "05:22:59" }, }, true);
+
+        // show time and date separately as timestamp will be 
+        // filtered out
+        rs = st.executeQuery("select " +
+                "CAST(CAST (min(ts) AS timestamp) AS date), " +
+                "CAST(CAST (min(ts) AS timestamp) AS time) from convtest");
+        JDBC.assertColumnNames(rs, new String[] { "1", "2" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "1832-09-24", 
+            "10:11:43" }, }, true);
+
+        rs = st.executeQuery(" select max(d) from convtest");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, 
+                new String[][] { { "1932-03-21" }, }, true);
+
+        rs = st.executeQuery(" select max(t) from convtest");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs,
+                new String[][] { { "23:49:52" }, }, true);
+
+        // show time and date separately as timestamp will be 
+        // filtered out
+        rs = st.executeQuery("select " +
+                "CAST(CAST (max(ts) AS timestamp) AS date), " +
+                "CAST(CAST (max(ts) AS timestamp) AS time) from convtest");
+        JDBC.assertColumnNames(rs, new String[] { "1", "2" });
+        JDBC.assertFullResultSet(rs,
+                new String[][] { { "9999-12-31", "23:59:59" }, }, true);
+        
+        //just to recover the test environment
+        dropTable("convtest");
+        createTableForConversionTest(st);
+
+        st.close();
+    }
+    
+    public void testConversion() throws SQLException{        
+        Statement st = createStatement();
+        
+        // these should fail
+        assertStatementError("42846", st, 
+                "select CAST (d AS time) from convtest");
+
+        assertStatementError("42846", st,
+                " select CAST (t AS date) from convtest");
+
+        // these should work
+        ResultSet rs = st.executeQuery(
+                "select CAST (t AS time) from convtest");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "23:49:52" }, 
+                { "05:22:59" }, { null } }, true);
+
+        rs = st.executeQuery(" select CAST (d AS date) from convtest");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "1932-03-21" },
+                { "0001-03-21" }, { null } }, true);
+
+        rs = st.executeQuery(" select CAST (ts AS time) from convtest");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "10:11:43" }, 
+                { "23:59:59" }, { null } }, true);
+
+        rs = st.executeQuery(" select CAST (ts AS date) from convtest");
+        JDBC.assertColumnNames(rs, new String[] { "1" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "1832-09-24" }, 
+                { "9999-12-31" }, { null } }, true);
+
+        // show time and date separately as timestamp will be 
+        // filtered out
+        rs = st.executeQuery("select CAST(CAST (ts AS timestamp) AS date), " +
+        		"CAST(CAST (ts AS timestamp) AS time) from convtest");
+        JDBC.assertColumnNames(rs, new String[] { "1", "2" });
+
+        JDBC.assertFullResultSet(rs, new String[][] { { "1832-09-24", 
+                "10:11:43" }, { "9999-12-31", "23:59:59" }, 
+                { null, null } }, true);
+
+        // casting from a time to a timestamp sets the date to 
+        // current date
+        assertStatementError("42846", st, "select 'pass', " +
+        		"CAST (CAST(t AS timestamp) AS time) from convtest " +
+        		"where CAST(CAST(t AS timestamp) AS date)=current_date");
+
+        // time should be 0
+        assertStatementError("42846", st, "select " +
+        		"CAST (CAST (d AS timestamp) AS date), " +
+        		"CAST(CAST(d AS timestamp) AS time) from convtest");
+        
+        st.close();
+    }
+    
+    /**
+     * leading zeros may be omitted from the month, 
+     * day and part of the timestamp.
+     */
+    public void testISOFormat_OmitLeadingZero() throws SQLException{
+        Statement st = createStatement();
+
+        assertEquals(1, st.executeUpdate(
+                "insert into ts values ('2003-03-05-17.05.43.111111', " +
+                "'2003-03-05 17:05:43.111111')"));
+
+        assertEquals(1, st.executeUpdate(" insert into ts values " +
+        		"('2003-3-03-17.05.43.111111', '2003-3-03 17:05:43.111111')"));
+
+        assertEquals(1, st.executeUpdate(" insert into ts values " +
+        		"('2003-3-2-17.05.43.111111', '2003-3-2 17:05:43.111111')"));
+
+        assertEquals(1, st.executeUpdate(" insert into ts values " +
+        		"('2003-03-2-17.05.43.111111', '2003-03-2 17:05:43.111111')"));
+
+        assertEquals(1, st.executeUpdate(" insert into ts values " +
+        		"('2003-3-1-17.05.43.1', '2003-3-1 17:05:43.1')"));
+
+        assertEquals(1, st.executeUpdate(" insert into ts values " +
+        		"('2003-3-1-17.05.43.12', '2003-3-1 17:05:43.12')"));
+
+        assertEquals(1, st.executeUpdate(" insert into ts values " +
+        		"('2003-3-1-17.05.43.123', '2003-3-1 17:05:43.123')"));
+
+        assertEquals(1, st.executeUpdate(" insert into ts values " +
+        		"('2003-3-1-17.05.43.1234', '2003-3-1 17:05:43.1234')"));
+
+        assertEquals(1, st.executeUpdate(" insert into ts values " +
+        		"('2003-3-1-17.05.43.12345', '2003-3-1 17:05:43.12345')"));
+
+        assertEquals(1, st.executeUpdate(" insert into ts values " +
+        		"('2003-3-1-17.05.43.123456', '2003-3-1 17:05:43.123456')"));
+
+        assertEquals(1, st.executeUpdate(" insert into ts values " +
+        		"('2003-3-1-17.05.43', '2003-3-1 17:05:43')"));
+        
+        st.close();
+    }    
+    
+    /**
+     *Trailing blanks are allowed.
+     */
+    public void testISOFormat_TrailingBlanks() throws SQLException{
+        Statement st = createStatement();
+        
+        assertEquals(1, st.executeUpdate("insert into ts values " +
+        		"('2002-03-05-17.05.43.111111  ', " +
+        		"'2002-03-05 17:05:43.111111   ')"));
+
+        assertEquals(1, st.executeUpdate(" insert into ts values " +
+        		"('2002-03-05-17.05.43.1   ', '2002-03-05 17:05:43.1   ')"));
+
+        assertEquals(1, st.executeUpdate(" insert into ts values " +
+        		"('2002-03-05-17.05.43    ', '2002-03-05 17:05:43    ')"));
+        
+        st.close();
+    }
+    
+    /**
+     *  UDB allows this by "appending a zero"; so, cloudscape follows.
+     */
+    public void testISOFormat_TrailingZero() throws SQLException{
+        Statement st = createStatement();
+        
+        assertEquals(1, st.executeUpdate("insert into ts values " +
+        		"('2003-3-1-17.05.43.', '2003-3-1 17:05:43')"));
+
+        assertEquals(1, st.executeUpdate(" insert into ts values " +
+        		"('2003-3-1-17.05.43.0', '2003-3-1 17:05:43.0')"));
+
+        assertEquals(1, st.executeUpdate(" insert into ts values " +
+        		"('0003-03-05-17.05.43.111111'," +
+        		" '0003-03-05 17:05:43.111111')"));
+        
+        ResultSet rs = st.executeQuery(" select count(*) from ts " +
+        		"where ts1=ts2");
+        rs.next();
+        int rows = rs.getInt(1);
+        rs = st.executeQuery(" select count(*) from ts ");
+        rs.next();
+        assertEquals(rows, rs.getInt(1));
+        
+        st.executeUpdate(" delete from ts");
+        
+        st.close();
+    }
+    
+    /**
+     * Should be rejected because leading zero in year is missing.
+     */
+    public void testISOFormat_LeadingZero() throws SQLException{
+        Statement st = createStatement();
+                
+        assertStatementError("22007", st,
+                "insert into ts (ts1) values ('03-03-05-17.05.43.111111')");
+
+        assertStatementError("22007", st,
+                " insert into ts (ts1) values ('103-03-05-17.05.43.111111')");
+
+        assertStatementError("22007", st,
+                " insert into ts (ts1) values ('3-03-05-17.05.43.111111')");
+        
+        st.close();
+    }
+    
+    /**
+     * not valid Time format in the timestamp strings:  cloudscape rejects
+     */
+    public void testISOFormat_WrongTimestampFormat() throws SQLException{
+        Statement st = createStatement();
+        
+        assertStatementError("22007", st,
+                "insert into ts (ts1) values ('2003-3-24-13.1.02.566999')");
+
+        assertStatementError("22007", st,
+                " insert into ts (ts1) values ('2003-3-24-13.1.1.569')");
+
+        assertStatementError("22007", st,
+                " insert into ts (ts1) values ('2003-3-24-1.1.1.56')");
+
+        assertStatementError("22007", st,
+                " insert into ts (ts1) values ('2003-3-24-1.1.1')");
+
+        assertStatementError("22007", st,
+                " insert into ts (ts1) values ('2003-3-1-17.05.4.')");
+
+        assertEquals(1, st.executeUpdate(" insert into ts (ts1) values " +
+        		"('2003-03-05-7.05.43.111111')"));
+
+        // invalid ISO format: cloudscape rejects
+
+        assertStatementError("22007", st,
+                "insert into ts (ts1) values ('2003-3-1 17.05.43.123456')");
+        
+        st.close();
+    }
+    
+    /**
+     *  Don't allow more than microseconds in ISO format: cloudscape rejects.
+     */
+    public void testISOFormat_MoreThanMicroseconds() throws SQLException{
+        Statement st = createStatement();
+        
+        assertStatementError("22007", st, "insert into ts (ts1) values "
+                + "('2003-03-05-17.05.43.999999999')");
+
+        assertStatementError("22007", st, " insert into ts (ts1) values "
+                + "('2003-03-05-17.05.43.999999000')");
+
+        st.close();
+    }
+    
+    /**
+     * Test the timestamp( d, t) function.
+     */
+    public void testTimeStampFunction() throws SQLException{
+        Statement st = createStatement();
+        
+        st .executeUpdate("create table tt " +
+        		"(datecol date, dateStr varchar(16), timecol time, " +
+        		"timeStr varchar(16), expected timestamp)");
+
+        st.executeUpdate(" insert into tt ( dateStr, timeStr) " +
+        		"values( '2004-03-04', '12:01:02')");
+
+        st.executeUpdate(" insert into tt ( dateStr, timeStr) " +
+        		"values( null, '12:01:03')");
+
+        st.executeUpdate(" insert into tt ( dateStr, timeStr) " +
+        		"values( '2004-03-05', null)");
+
+        assertUpdateCount(st, 3, " update tt  set datecol = date( dateStr), " +
+        		"timecol = time( timeStr)");
+
+        assertUpdateCount(st, 1, " update tt  set expected = " +
+        		"timestamp( dateStr || ' ' || timeStr) " +
+        		"where dateStr is not null and timeStr is not null");
+
+        ResultSet rs = st.executeQuery(" select dateStr, timeStr from tt " +
+        		"where (expected is not null and (expected <> " +
+        		"timestamp( dateCol, timeCol) or " +
+        		"timestamp( dateCol, timeCol) is null)) or " +
+        		"(expected is null and " +
+        		"timestamp( dateCol, timeCol) is not null)");
+        JDBC.assertColumnNames(rs, new String[] { "DATESTR", "TIMESTR" });
+        JDBC.assertDrainResults(rs, 0);
+
+        rs = st.executeQuery(" select dateStr, timeStr from tt " +
+        		"where (expected is not null and (expected <> " +
+        		"timestamp( dateStr, timeStr) or " +
+        		"timestamp( dateStr, timeStr) is null)) " +
+        		"or (expected is null and " +
+        		"timestamp( dateStr, timeStr) is not null)");
+
+        JDBC.assertColumnNames(rs, new String[] { "DATESTR", "TIMESTR" });
+        JDBC.assertDrainResults(rs, 0);
+
+        rs = st.executeQuery(" select dateStr, timeStr from tt " +
+        		"where (expected is not null and " +
+        		"timestamp( dateStr, timeStr) <> timestamp( dateCol, timeCol))" +
+        		" or (expected is null and " +
+        		"timestamp( dateStr, timeStr) is not null)");
+
+        JDBC.assertColumnNames(rs, new String[] { "DATESTR", "TIMESTR" });
+        JDBC.assertDrainResults(rs, 0);
+
+        rs = st.executeQuery(" select dateStr, timeStr from tt " +
+        		"where expected is not null and " +
+        		"date( timestamp( dateCol, timeCol)) <> dateCol");
+        JDBC.assertColumnNames(rs, new String[] { "DATESTR", "TIMESTR" });
+        JDBC.assertDrainResults(rs, 0);
+
+        rs = st.executeQuery(" select dateStr, timeStr from tt " +
+        		"where expected is not null and " +
+        		"time( timestamp( dateCol, timeCol)) <> timeCol");
+        JDBC.assertColumnNames(rs, new String[] { "DATESTR", "TIMESTR" });
+        JDBC.assertDrainResults(rs, 0);
+
+        // Error cases
+        assertStatementError("42Y95", st,
+                "select timestamp( dateCol, dateCol) from tt where "
+                        + "dateCol is not null");
+
+        assertStatementError("42Y95", st,
+                " select timestamp( timeCol, timeCol) from tt where "
+                        + "timeCol is not null");
+
+        assertStatementError("22007", st,
+                "values timestamp( 'xyz', '12:01:02')");
+
+        assertStatementError("22007", st,
+                " values timestamp( '2004-03-04', 'xyz')");
+
+        dropTable("tt");
+        
+        st.close();
+    }
+    
+    public void testFormat() throws SQLException{
+        Statement st = createStatement();
+        
+        st.executeUpdate(" create table t_format (t time)");
+
+        // ISO format: UDB is okay.
+        assertEquals(1, 
+                st.executeUpdate("insert into t_format values ('17.05.44')"));
+
+        assertEquals(1, 
+                st.executeUpdate(" insert into t_format values ('17.05.00')"));
+
+        assertEquals(1, 
+                st.executeUpdate(" insert into t_format values ('00.05.43')"));
+
+        assertEquals(1, 
+                st.executeUpdate(" insert into t_format values ('00.00.00')"));
+
+        // DB2 keeps '24:00:00' but Cloudcape returns '00:00:00'
+        assertEquals(1, 
+                st.executeUpdate("insert into t_format values ('24.00.00')"));
+
+        // trailing blanks are allowed
+        assertEquals(1, 
+                st.executeUpdate("insert into t_format values ('17.05.11  ')"));
+
+        assertEquals(1, 
+                st.executeUpdate(" insert into t_format values ('17:05:11  ')"));
+
+        // seconds can be omitted
+        assertEquals(1, 
+                st.executeUpdate("insert into t_format values ('1:01')"));
+
+        assertEquals(1, 
+                st.executeUpdate(" insert into t_format values ('1:02 ')"));
+
+        assertEquals(1, 
+                st.executeUpdate(" insert into t_format values ('2.01')"));
+
+        assertEquals(1, 
+                st.executeUpdate(" insert into t_format values ('2.02 ')"));
+
+        // 11 rows
+        ResultSet rs = st.executeQuery("select * from t_format");
+        JDBC.assertColumnNames(rs, new String[] { "T" });
+
+        JDBC.assertFullResultSet(rs, 
+                new String[][] { { "17:05:44" }, { "17:05:00" },
+                { "00:05:43" }, { "00:00:00" }, { "00:00:00" }, { "17:05:11" },
+                { "17:05:11" }, { "01:01:00" }, { "01:02:00" }, { "02:01:00" },
+                { "02:02:00" } }, true);
+        assertUpdateCount(st, 11, " delete from t_format");
+
+        // end value tests...
+        assertStatementError("22007", st, "insert into t_format values ('24.60.60')");
+
+        assertStatementError("22007", st, " insert into t_format values ('04.00.60')");
+
+        assertStatementError("22007", st, " insert into t_format values ('03.60.00')");
+
+        // not valid Time string ISO format: HH.MM.SS
+        assertStatementError("22007", st, 
+                "insert into t_format values ('07.5.44')");
+
+        assertStatementError("22007", st, 
+                " insert into t_format values ('07.05.4')");
+
+        assertStatementError("22007", st, 
+                " insert into t_format values ('7.5.44')");
+
+        assertStatementError("22007", st, 
+                " insert into t_format values ('7.5.4')");
+
+        assertStatementError("22007", st, 
+                " insert into t_format values ('7.5.0')");
+
+        assertStatementError("22007", st, 
+                " insert into t_format values ('-4.00.00')");
+
+        assertStatementError("22007", st, 
+                " insert into t_format values ('A4.00.00')");
+
+        assertStatementError("22007", st, 
+                " insert into t_format values ('7.5.999')");
+
+        assertStatementError("22007", st, 
+                " insert into t_format values ('07.05.111')");
+
+        assertStatementError("22007", st, 
+                " insert into t_format values ('111.05.11')");
+
+        assertStatementError("22007", st, 
+                " insert into t_format values ('11.115.00')");
+
+        // no row
+        rs = st.executeQuery("select * from t_format");
+        JDBC.assertColumnNames(rs, new String[] { "T" });
+        JDBC.assertDrainResults(rs, 0);
+
+        dropTable("t_format");
+        
+        st.close();
+    }
+
+    public void testFormat_Additional() throws SQLException{
+        Statement st = createStatement();
+        
+        ResultSet rs = st .executeQuery(
+        		"values time('2004-04-15 16:15:32.387')");
+        JDBC.assertFullResultSet(rs, new String[][] { { "16:15:32" } }, true);
+
+        rs = st.executeQuery(" values time('2004-04-15-16.15.32.387')");
+        JDBC.assertFullResultSet(rs, new String[][] { { "16:15:32" } }, true);
+
+        assertStatementError("22007", st,
+                " values time('2004-04-15-16.15.32.387 zz')");
+
+        assertStatementError("22007", st,
+                " values time('x-04-15-16.15.32.387')");
+
+        rs = st.executeQuery(" values date('2004-04-15 16:15:32.387')");
+        JDBC.assertFullResultSet(rs, 
+                new String[][] { { "2004-04-15" } }, true);
+
+        rs = st.executeQuery(" values date('2004-04-15-16.15.32.387')");
+        JDBC.assertFullResultSet(rs, 
+                new String[][] { { "2004-04-15" } }, true);
+
+        assertStatementError("22008", st,
+                " values date('2004-04-15-16.15.32.387 zz')");
+
+        assertStatementError("22008", st,
+                " values date('2004-04-15-16.15.32.y')");
+
+        rs = st.executeQuery(" values time('13:59')");
+        JDBC.assertFullResultSet(rs, 
+                new String[][] { { "13:59:00" } }, true);
+
+        rs = st.executeQuery(" values time('1:00')");
+        JDBC.assertFullResultSet(rs, new String[][] { { "01:00:00" } }, true);
+        
+        st.close();
+    }
+    
+    /**
+     * Test unary date and datetime functions. 
+     * Test with both constant and variable arguments.
+     */
+    public void test_DateAndDatetimeFunctionsMore() throws Exception {
+        ResultSet rs = null;
+        ResultSetMetaData rsmd;
+        PreparedStatement pSt;
+        Statement st = createStatement();
+
+        String[][] expRS;
+        String[] expColNames;      
+
+        setAutoCommit(false);
+        // test date(integer)
+        st.executeUpdate("create table t_func( i int, d date)");
+
+        commit();
+        st.executeUpdate(" insert into t_func 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
+        assertStatementError("22008", st, "insert into t_func values( 0, date(0))");
+
+        assertStatementError("22008", st,
+                " insert into t_func values( -1, date(-1))");
+
+        assertStatementError("22008", st,
+                " insert into t_func values( 3652060, date( 3652060))");
+
+        rs = st.executeQuery(" select i,d,date(i),date(d) from t_func order by i");
+        JDBC.assertColumnNames(rs, new String[] { "I", "D", "3", "4" });
+        JDBC.assertFullResultSet(rs, new String[][] {
+                { "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" } }, true);
+
+        rollback();
+
+        assertEquals(1, st.executeUpdate(" insert into t_func(i) values( 0)"));
+        
+        assertStatementError("22008", st, " select date(i) from t_func");
+
+        rollback();
+
+        st.executeUpdate(" insert into t_func(i) values( -1)");
+
+        assertStatementError("22008", st, " select date(i) from t_func");
+        rollback();
+
+        st.executeUpdate(" insert into t_func(i) values( 3652060)");
+        assertStatementError("22008", st, " select date(i) from t_func");
+        rollback();
+
+        st.executeUpdate(" drop table t_func");
+        
+        st.executeUpdate(" create table t_func( s varchar(32), d date)");
+
+        commit();
+        assertEquals(6, st.executeUpdate(" insert into t_func " +
+        		"values('1900060', date('1900060')), ('1904060', " +
+        		"date('1904060')), ('1904366', date('1904366')), " +
+        		"('2000060', date('2000060')), ('2001060'," +
+        		" date('2001060')), ('2001365', date('2001365'))"));
+
+        rs = st.executeQuery(" select s,d,date(s) from t_func order by s");
+        JDBC.assertColumnNames(rs, new String[] { "S", "D", "3" });
+        JDBC.assertFullResultSet(rs, 
+                new String[][] { { "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" } }, true);
+
+        rollback();
+
+        // failure cases
+        assertStatementError("22008", st, "values( date('2001000'))");
+
+        assertStatementError("22008", st, " values( date('2001366'))");
+
+        assertStatementError("22008", st, " values( date('2000367'))");
+
+        assertStatementError("22008", st, " values( date('xxxxxxx'))");
+
+        st.executeUpdate(" insert into t_func(s) values( '2001000')");
+
+        assertStatementError("22008", st, "select date(s) from t_func");
+        rollback();
+
+        st.executeUpdate(" insert into t_func(s) values( '2001366')");
+
+        assertStatementError("22008", st, "select date(s) from t_func");
+        rollback();
+
+        st.executeUpdate(" insert into t_func(s) values( '2000367')");
+
+        assertStatementError("22008", st, "select date(s) from t_func");
+        rollback();
+
+        st.executeUpdate(" insert into t_func(s) values( 'xxxxxxx')");
+
+        assertStatementError("22008", st, "select date(s) from t_func");
+        rollback();
+
+        // test parameter
+        pSt = prepareStatement("values( date(cast(? as integer))," +
+        		"timestamp(cast(? as varchar(32))))");
+        rs = st.executeQuery("values(cast(1 as integer), " +
+        		"'2003-03-05-17.05.43.111111')");
+
+        rs.next();
+        rsmd = rs.getMetaData();
+        for (int i = 1; i <= rsmd.getColumnCount(); i++)
+            pSt.setObject(i, rs.getObject(i));
+        
+        rs = pSt.executeQuery();
+        JDBC.assertColumnNames(rs, new String[] { "1", "2" });
+        // See DERBY-3856 - there's a diff between Embedded and DerbyNetClient
+        // in how the cast returns the following...
+        if (usingDerbyNetClient())
+            JDBC.assertFullResultSet(rs, new String[][] { { "1970-01-01",
+            "2003-03-05 17:05:43.111111" } }, true);
+        else
+            JDBC.assertFullResultSet(rs, new String[][] { { "1970-01-01",
+                "2003-03-05-17.05.43.111111" } }, true);
+
+        rs = st.executeQuery("values(2, '20030422190200')");
+        rs.next();
+        rsmd = rs.getMetaData();
+        for (int i = 1; i <= rsmd.getColumnCount(); i++)
+            pSt.setObject(i, rs.getObject(i));
+
+        rs = pSt.executeQuery();
+        JDBC.assertColumnNames(rs, new String[] { "1", "2" });
+        JDBC.assertFullResultSet(rs, new String[][] { { "1970-01-02", 
+                "2003-04-22 19:02:00.0" } }, true);
+
+        rs = st.executeQuery(" values( date(date(1)), "
+                + "date(timestamp('2003-03-05-17.05.43.111111')))");
+        JDBC.assertColumnNames(rs, new String[] { "1", "2" });
+        JDBC.assertFullResultSet(rs, 
+                new String[][] { { "1970-01-01", "2003-03-05" } }, true);
+
+        st.executeUpdate(" drop table t_func");
+        
+        
+        st.executeUpdate(" create table t_func( s varchar(32), ts timestamp, " + 
+                "expected timestamp)");
+
+        commit();
+        st.executeUpdate(" insert into t_func(ts) values( " +
+                "timestamp('2003-03-05-17.05.43.111111'))");
+
+        rs = st.executeQuery(" select date(ts) from t_func");
+
+        expColNames = new String[] { "1" };
+        JDBC.assertColumnNames(rs, expColNames);
+
+        expRS = new String[][] { { "2003-03-05" } };
+
+        JDBC.assertFullResultSet(rs, expRS, true);
+
+        rollback();
+
+        // Test special unary timestamp function rules: yyyyxxddhhmmss
+        st.executeUpdate("insert into t_func values('20000228235959', " + 
+                "timestamp('20000228235959'), '2000-02-28-23.59.59'), " +
+                "('20000229000000', timestamp('20000229000000'), " +
+                "'2000-02-29-00.00.00')");
+
+        rs = st.executeQuery(" select s from t_func where ts <> expected or " + 
+                "timestamp(s) <> expected or timestamp(ts) <> expected");
+        JDBC.assertColumnNames(rs, new String[] { "S" });
+        JDBC.assertDrainResults(rs, 0);
+
+        rollback();
+
+        // invalid
+        assertStatementError("22008", st,
+                "values( timestamp('2000 1 1 0 0 0'))");
+
+        assertStatementError("22008", st,
+                " values( timestamp('aaaaaaaaaaaaaa'))");
+
+        assertEquals(1, st.executeUpdate(
+                " insert into t_func(s) values('2000 1 1 0 0 0')"));
+        
+        assertStatementError("22008", st, " select timestamp(s) from t_func");
+
+        rollback();
+        assertEquals(1, st.executeUpdate(
+                " insert into t_func(s) values('aaaaaaaaaaaaaa')"));
+        assertStatementError("22008", st, " select timestamp(s) from t_func");
+        rollback();
+
+        commit();
+
+        getConnection().rollback();
+        st.close();
+    }
+    
+    /**
+     *  Null values in datetime scalar functions.
+     */
+    public void testNulls() throws SQLException{
+        Statement st = createStatement();
+        
+        st.executeUpdate("create table nulls (t time, d date, ts timestamp)");
+
+        st.executeUpdate(" insert into nulls values (null,null,null)");
+
+        commit();
+        assertStatementError("42X25", st, " select year(t) from nulls");
+
+        assertStatementError("42X25", st, " select month(t) from nulls");
+
+        assertStatementError("42X25", st, " select day(t) from nulls");
+
+        ResultSet rs = st.executeQuery(" select hour(t) from nulls");
+        JDBC.assertFullResultSet(rs, new String[][] { { null } }, true);
+
+        rs = st.executeQuery(" select minute(t) from nulls");
+        JDBC.assertFullResultSet(rs, new String[][] { { null } }, true);
+
+        rs = st.executeQuery(" select second(t) from nulls");
+        JDBC.assertFullResultSet(rs, new String[][] { { null } }, true);
+
+        rs = st.executeQuery(" select year(d) from nulls");
+        JDBC.assertFullResultSet(rs, new String[][] { { null } }, true);
+
+        rs = st.executeQuery(" select month(d) from nulls");
+        JDBC.assertFullResultSet(rs, new String[][] { { null } }, true);
+
+        rs = st.executeQuery(" select day(d) from nulls");
+        JDBC.assertFullResultSet(rs, new String[][] { { null } }, true);
+
+        assertStatementError("42X25", st, " select hour(d) from nulls");
+
+        assertStatementError("42X25", st, " select minute(d) from nulls");
+
+        assertStatementError("42X25", st, " select second(d) from nulls");
+
+        rs = st.executeQuery(" select year(ts) from nulls");
+        JDBC.assertFullResultSet(rs, new String[][] { { null } }, true);
+
+        rs = st.executeQuery(" select month(ts) from nulls");
+        JDBC.assertFullResultSet(rs, new String[][] { { null } }, true);
+
+        rs = st.executeQuery(" select day(ts) from nulls");
+        JDBC.assertFullResultSet(rs, new String[][] { { null } }, true);
+
+        rs = st.executeQuery(" select hour(ts) from nulls");
+        JDBC.assertFullResultSet(rs, new String[][] { { null } }, true);
+
+        rs = st.executeQuery(" select minute(ts) from nulls");
+        JDBC.assertFullResultSet(rs, new String[][] { { null } }, true);
+
+        rs = st.executeQuery(" select second(ts) from nulls");
+        JDBC.assertFullResultSet(rs, new String[][] { { null } }, true);
+
+        st.executeUpdate(" drop table nulls");
+        
+        st.close();
+    }
+}

Propchange: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DateTimeTest.java
------------------------------------------------------------------------------
    svn:eol-style = native



Mime
View raw message