| /* |
| |
| 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.Date; |
| import java.sql.PreparedStatement; |
| import java.sql.ResultSet; |
| import java.sql.ResultSetMetaData; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| import java.sql.Time; |
| import java.sql.Timestamp; |
| import java.util.Calendar; |
| import java.util.TimeZone; |
| import junit.framework.Test; |
| import org.apache.derbyTesting.junit.BaseJDBCTestCase; |
| import org.apache.derbyTesting.junit.BaseTestSuite; |
| 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() { |
| BaseTestSuite suite = new BaseTestSuite("DateTimeTest"); |
| suite.addTest(baseSuite("DateTimeTest:Embedded")); |
| suite.addTest(TestConfiguration |
| .clientServerDecorator(baseSuite("DateTimeTest:Client"))); |
| return suite; |
| } |
| |
| protected static Test baseSuite(String name) { |
| BaseTestSuite suite = new BaseTestSuite(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(); |
| } |
| |
| /** |
| * Test that conversion from timestamp to string is correct when a |
| * calendar is specified. Specifically, verify that the full nanosecond |
| * resolution is used and that the converted timestamp is not rounded to |
| * microsecond resolution. Regression test case for DERBY-4626. |
| */ |
| public void testConvertToStringWithCalendar() throws SQLException { |
| PreparedStatement ps = |
| prepareStatement("values cast(? as varchar(29))"); |
| |
| // Generate a timestamp representing 2010-09-01 20:31:40.123456789 GMT |
| Timestamp ts = new Timestamp(1283373100000L); |
| ts.setNanos(123456789); |
| |
| // Array of (timezone, timestamp string) pairs representing a timezone |
| // with which to test and the expected timestamp string produced. |
| String[][] testData = { |
| { "GMT", "2010-09-01 20:31:40.123456789" }, |
| { "Europe/Oslo", "2010-09-01 22:31:40.123456789" }, |
| }; |
| |
| for (int i = 0; i < testData.length; i++) { |
| Calendar cal = Calendar.getInstance( |
| TimeZone.getTimeZone(testData[i][0])); |
| ps.setTimestamp(1, ts, cal); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), testData[i][1]); |
| } |
| } |
| |
| /** |
| * Regression test case for DERBY-4621, which caused the conversion of |
| * timestamp and time values to varchar to generate wrong results when |
| * a Calendar object was supplied. |
| */ |
| public void testConversionToString() throws SQLException { |
| String timestampString = "2010-04-20 15:17:36.0"; |
| String timeString = "15:17:36"; |
| String dateString = "2010-04-20"; |
| |
| Timestamp ts = Timestamp.valueOf(timestampString); |
| Time t = Time.valueOf(timeString); |
| Date d = Date.valueOf(dateString); |
| |
| PreparedStatement ps = |
| prepareStatement("VALUES CAST(? AS VARCHAR(40))"); |
| |
| ps.setTimestamp(1, ts); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), timestampString); |
| |
| // Used to give wrong result - 2010-04-20 03:17:36 |
| ps.setTimestamp(1, ts, Calendar.getInstance()); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), timestampString); |
| |
| ps.setTime(1, t); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), timeString); |
| |
| // Used to give wrong result - 03:17:36 |
| ps.setTime(1, t, Calendar.getInstance()); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), timeString); |
| |
| ps.setDate(1, d); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), dateString); |
| |
| ps.setDate(1, d, Calendar.getInstance()); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), dateString); |
| } |
| |
| /** |
| * Test that trailing zeros in the nanoseconds component of a timestamp |
| * are handled the same way by |
| * {@code PreparedStatement.setTimestamp(int,Timestamp)} and |
| * {@code PreparedStatement.setTimestamp(int,Timestamp, Calendar)} |
| * when converting the timestamp to a VARCHAR. (DERBY-4810) |
| */ |
| public void testTrailingZeros() throws SQLException { |
| PreparedStatement ps = |
| prepareStatement("values cast(? as varchar(29))"); |
| |
| String[] tsStrings = { |
| "2010-09-22 14:40:33.000000000", |
| "2010-09-22 14:40:33.012000000", |
| "2010-09-22 14:40:33.123456000", |
| "2010-09-22 14:40:33.139990900", |
| "2010-09-22 14:40:33.139990983", |
| }; |
| |
| for (int i = 0; i < tsStrings.length; i++) { |
| Timestamp ts = Timestamp.valueOf(tsStrings[i]); |
| |
| // We expect the converted value to have the same format as |
| // what Timestamp.toString() returns. |
| String expected = ts.toString(); |
| |
| ps.setTimestamp(1, ts); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), expected); |
| |
| ps.setTimestamp(1, ts, Calendar.getInstance()); |
| JDBC.assertSingleValueResultSet(ps.executeQuery(), expected); |
| } |
| } |
| |
| 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 nanoseconds in ISO format. |
| */ |
| public void testISOFormat_MoreThanNanoseconds() throws SQLException{ |
| Statement st = createStatement(); |
| |
| assertStatementError("22007", st, "insert into ts (ts1) values " |
| + "('2003-03-05-17.05.43.999999999999')"); |
| |
| assertStatementError("22007", st, " insert into ts (ts1) values " |
| + "('2003-03-05-17.05.43.999999999000')"); |
| |
| 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" }); |
| 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(); |
| } |
| |
| /** |
| * Regression test case for DERBY-3856. The embedded driver sometimes |
| * returned an unnormalized datetime string when getString() was used |
| * to retrieve the result from an invocation of the unary TIMESTAMP or |
| * DATE function. |
| */ |
| public void testDerby3856() throws SQLException { |
| // TIMESTAMP called on literal has always a returned normalized value. |
| assertSingleValue( |
| "values timestamp('2003-03-05-17.05.43.111111')", |
| "2003-03-05 17:05:43.111111"); |
| |
| // This one used to return the original unnormalized input with the |
| // embedded driver. |
| assertSingleValue( |
| "values timestamp(cast('2003-03-05-17.05.43.111111' " + |
| "as varchar(32)))", |
| "2003-03-05 17:05:43.111111"); |
| |
| // DATE called on literal has always a returned normalized value. |
| assertSingleValue("values date('10/07/2008')", "2008-10-07"); |
| |
| // This one used to return the original unnormalized input with the |
| // embedded driver. |
| assertSingleValue( |
| "values date(cast('10/07/2008' as varchar(10)))", |
| "2008-10-07"); |
| |
| // TIME called on literal has always a returned normalized string. |
| assertSingleValue("values time('10.00.00')", "10:00:00"); |
| |
| // TIME has also always returned a normalized value for the variant |
| // with a CAST. |
| assertSingleValue( |
| "values time(cast('10.00.00' as varchar(10)))", |
| "10:00:00"); |
| } |
| |
| /** |
| * Test case to show that timestamp function accepts nanoseconds |
| * resolution (DERBY-4625). |
| */ |
| public void testNanosecondResolution() throws SQLException{ |
| assertSingleValue("values timestamp('2010-04-21 12:00:00.123456789')", |
| "2010-04-21 12:00:00.123456789"); |
| } |
| |
| /** |
| * Execute an SQL statement and check that it returns a single, specific |
| * value. |
| * |
| * @param sql the statement to execute |
| * @param expectedValue the expected value |
| */ |
| private void assertSingleValue(String sql, String expectedValue) |
| throws SQLException { |
| JDBC.assertSingleValueResultSet( |
| createStatement().executeQuery(sql), expectedValue); |
| } |
| } |
