| /* |
| * 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.phoenix.end2end; |
| |
| import static org.apache.phoenix.query.QueryConstants.MILLIS_IN_DAY; |
| import static org.apache.phoenix.util.TestUtil.ATABLE_NAME; |
| import static org.apache.phoenix.util.TestUtil.A_VALUE; |
| import static org.apache.phoenix.util.TestUtil.B_VALUE; |
| import static org.apache.phoenix.util.TestUtil.C_VALUE; |
| import static org.apache.phoenix.util.TestUtil.E_VALUE; |
| import static org.apache.phoenix.util.TestUtil.ROW1; |
| import static org.apache.phoenix.util.TestUtil.ROW2; |
| import static org.apache.phoenix.util.TestUtil.ROW3; |
| import static org.apache.phoenix.util.TestUtil.ROW4; |
| import static org.apache.phoenix.util.TestUtil.ROW5; |
| import static org.apache.phoenix.util.TestUtil.ROW6; |
| import static org.apache.phoenix.util.TestUtil.ROW7; |
| import static org.apache.phoenix.util.TestUtil.ROW8; |
| import static org.apache.phoenix.util.TestUtil.ROW9; |
| import static org.junit.Assert.assertEquals; |
| import static org.junit.Assert.assertFalse; |
| import static org.junit.Assert.assertNotNull; |
| import static org.junit.Assert.assertNull; |
| import static org.junit.Assert.assertTrue; |
| |
| import java.math.BigDecimal; |
| import java.sql.*; |
| import java.text.Format; |
| import java.util.Calendar; |
| import java.util.GregorianCalendar; |
| |
| import org.apache.phoenix.util.DateUtil; |
| import org.junit.After; |
| import org.junit.Before; |
| import org.junit.Test; |
| |
| |
| public class DateTimeIT extends ParallelStatsDisabledIT { |
| |
| protected Connection conn; |
| protected Date date; |
| protected static final String tenantId = getOrganizationId(); |
| protected final static String ROW10 = "00D123122312312"; |
| protected String tableName; |
| |
| public DateTimeIT() throws Exception { |
| super(); |
| date = new Date(System.currentTimeMillis()); |
| } |
| |
| @Before |
| public void setUp() throws SQLException { |
| conn = DriverManager.getConnection(getUrl()); |
| this.tableName = initAtable(); |
| } |
| |
| @After |
| public void tearDown() throws SQLException { |
| conn.close(); |
| } |
| |
| private String initAtable() throws SQLException { |
| String tableName = generateUniqueName(); |
| ensureTableCreated(getUrl(), tableName, ATABLE_NAME, (byte[][])null, null); |
| PreparedStatement stmt = conn.prepareStatement( |
| "upsert into " + tableName + |
| "(" + |
| " ORGANIZATION_ID, " + |
| " ENTITY_ID, " + |
| " A_STRING, " + |
| " B_STRING, " + |
| " A_INTEGER, " + |
| " A_DATE, " + |
| " X_DECIMAL, " + |
| " X_LONG, " + |
| " X_INTEGER," + |
| " Y_INTEGER," + |
| " A_BYTE," + |
| " A_SHORT," + |
| " A_FLOAT," + |
| " A_DOUBLE," + |
| " A_UNSIGNED_FLOAT," + |
| " A_UNSIGNED_DOUBLE)" + |
| "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); |
| stmt.setString(1, tenantId); |
| stmt.setString(2, ROW1); |
| stmt.setString(3, A_VALUE); |
| stmt.setString(4, B_VALUE); |
| stmt.setInt(5, 1); |
| stmt.setDate(6, date); |
| stmt.setBigDecimal(7, null); |
| stmt.setNull(8, Types.BIGINT); |
| stmt.setNull(9, Types.INTEGER); |
| stmt.setNull(10, Types.INTEGER); |
| stmt.setByte(11, (byte)1); |
| stmt.setShort(12, (short) 128); |
| stmt.setFloat(13, 0.01f); |
| stmt.setDouble(14, 0.0001); |
| stmt.setFloat(15, 0.01f); |
| stmt.setDouble(16, 0.0001); |
| stmt.execute(); |
| |
| stmt.setString(1, tenantId); |
| stmt.setString(2, ROW2); |
| stmt.setString(3, A_VALUE); |
| stmt.setString(4, C_VALUE); |
| stmt.setInt(5, 2); |
| stmt.setDate(6, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY * 1)); |
| stmt.setBigDecimal(7, null); |
| stmt.setNull(8, Types.BIGINT); |
| stmt.setNull(9, Types.INTEGER); |
| stmt.setNull(10, Types.INTEGER); |
| stmt.setByte(11, (byte)2); |
| stmt.setShort(12, (short) 129); |
| stmt.setFloat(13, 0.02f); |
| stmt.setDouble(14, 0.0002); |
| stmt.setFloat(15, 0.02f); |
| stmt.setDouble(16, 0.0002); |
| stmt.execute(); |
| |
| stmt.setString(1, tenantId); |
| stmt.setString(2, ROW3); |
| stmt.setString(3, A_VALUE); |
| stmt.setString(4, E_VALUE); |
| stmt.setInt(5, 3); |
| stmt.setDate(6, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY * 2)); |
| stmt.setBigDecimal(7, null); |
| stmt.setNull(8, Types.BIGINT); |
| stmt.setNull(9, Types.INTEGER); |
| stmt.setNull(10, Types.INTEGER); |
| stmt.setByte(11, (byte)3); |
| stmt.setShort(12, (short) 130); |
| stmt.setFloat(13, 0.03f); |
| stmt.setDouble(14, 0.0003); |
| stmt.setFloat(15, 0.03f); |
| stmt.setDouble(16, 0.0003); |
| stmt.execute(); |
| |
| stmt.setString(1, tenantId); |
| stmt.setString(2, ROW4); |
| stmt.setString(3, A_VALUE); |
| stmt.setString(4, B_VALUE); |
| stmt.setInt(5, 4); |
| stmt.setDate(6, date == null ? null : date); |
| stmt.setBigDecimal(7, null); |
| stmt.setNull(8, Types.BIGINT); |
| stmt.setNull(9, Types.INTEGER); |
| stmt.setNull(10, Types.INTEGER); |
| stmt.setByte(11, (byte)4); |
| stmt.setShort(12, (short) 131); |
| stmt.setFloat(13, 0.04f); |
| stmt.setDouble(14, 0.0004); |
| stmt.setFloat(15, 0.04f); |
| stmt.setDouble(16, 0.0004); |
| stmt.execute(); |
| |
| stmt.setString(1, tenantId); |
| stmt.setString(2, ROW5); |
| stmt.setString(3, B_VALUE); |
| stmt.setString(4, C_VALUE); |
| stmt.setInt(5, 5); |
| stmt.setDate(6, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY * 1)); |
| stmt.setBigDecimal(7, null); |
| stmt.setNull(8, Types.BIGINT); |
| stmt.setNull(9, Types.INTEGER); |
| stmt.setNull(10, Types.INTEGER); |
| stmt.setByte(11, (byte)5); |
| stmt.setShort(12, (short) 132); |
| stmt.setFloat(13, 0.05f); |
| stmt.setDouble(14, 0.0005); |
| stmt.setFloat(15, 0.05f); |
| stmt.setDouble(16, 0.0005); |
| stmt.execute(); |
| |
| stmt.setString(1, tenantId); |
| stmt.setString(2, ROW6); |
| stmt.setString(3, B_VALUE); |
| stmt.setString(4, E_VALUE); |
| stmt.setInt(5, 6); |
| stmt.setDate(6, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY * 2)); |
| stmt.setBigDecimal(7, null); |
| stmt.setNull(8, Types.BIGINT); |
| stmt.setNull(9, Types.INTEGER); |
| stmt.setNull(10, Types.INTEGER); |
| stmt.setByte(11, (byte)6); |
| stmt.setShort(12, (short) 133); |
| stmt.setFloat(13, 0.06f); |
| stmt.setDouble(14, 0.0006); |
| stmt.setFloat(15, 0.06f); |
| stmt.setDouble(16, 0.0006); |
| stmt.execute(); |
| |
| stmt.setString(1, tenantId); |
| stmt.setString(2, ROW7); |
| stmt.setString(3, B_VALUE); |
| stmt.setString(4, B_VALUE); |
| stmt.setInt(5, 7); |
| stmt.setDate(6, date == null ? null : date); |
| stmt.setBigDecimal(7, BigDecimal.valueOf(0.1)); |
| stmt.setLong(8, 5L); |
| stmt.setInt(9, 5); |
| stmt.setNull(10, Types.INTEGER); |
| stmt.setByte(11, (byte)7); |
| stmt.setShort(12, (short) 134); |
| stmt.setFloat(13, 0.07f); |
| stmt.setDouble(14, 0.0007); |
| stmt.setFloat(15, 0.07f); |
| stmt.setDouble(16, 0.0007); |
| stmt.execute(); |
| |
| stmt.setString(1, tenantId); |
| stmt.setString(2, ROW8); |
| stmt.setString(3, B_VALUE); |
| stmt.setString(4, C_VALUE); |
| stmt.setInt(5, 8); |
| stmt.setDate(6, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY * 1)); |
| stmt.setBigDecimal(7, BigDecimal.valueOf(3.9)); |
| long l = Integer.MIN_VALUE - 1L; |
| assert(l < Integer.MIN_VALUE); |
| stmt.setLong(8, l); |
| stmt.setInt(9, 4); |
| stmt.setNull(10, Types.INTEGER); |
| stmt.setByte(11, (byte)8); |
| stmt.setShort(12, (short) 135); |
| stmt.setFloat(13, 0.08f); |
| stmt.setDouble(14, 0.0008); |
| stmt.setFloat(15, 0.08f); |
| stmt.setDouble(16, 0.0008); |
| stmt.execute(); |
| |
| stmt.setString(1, tenantId); |
| stmt.setString(2, ROW9); |
| stmt.setString(3, C_VALUE); |
| stmt.setString(4, E_VALUE); |
| stmt.setInt(5, 9); |
| stmt.setDate(6, date == null ? null : new Date(date.getTime() + MILLIS_IN_DAY * 2)); |
| stmt.setBigDecimal(7, BigDecimal.valueOf(3.3)); |
| l = Integer.MAX_VALUE + 1L; |
| assert(l > Integer.MAX_VALUE); |
| stmt.setLong(8, l); |
| stmt.setInt(9, 3); |
| stmt.setInt(10, 300); |
| stmt.setByte(11, (byte)9); |
| stmt.setShort(12, (short) 0); |
| stmt.setFloat(13, 0.09f); |
| stmt.setDouble(14, 0.0009); |
| stmt.setFloat(15, 0.09f); |
| stmt.setDouble(16, 0.0009); |
| stmt.execute(); |
| |
| stmt.setString(1, tenantId); |
| stmt.setString(2, ROW10); |
| stmt.setString(3, B_VALUE); |
| stmt.setString(4, B_VALUE); |
| stmt.setInt(5, 7); |
| // Intentionally null |
| stmt.setDate(6, null); |
| stmt.setBigDecimal(7, BigDecimal.valueOf(0.1)); |
| stmt.setLong(8, 5L); |
| stmt.setInt(9, 5); |
| stmt.setNull(10, Types.INTEGER); |
| stmt.setByte(11, (byte)7); |
| stmt.setShort(12, (short) 134); |
| stmt.setFloat(13, 0.07f); |
| stmt.setDouble(14, 0.0007); |
| stmt.setFloat(15, 0.07f); |
| stmt.setDouble(16, 0.0007); |
| stmt.execute(); |
| |
| conn.commit(); |
| return tableName; |
| |
| } |
| |
| @Test |
| public void selectBetweenDates() throws Exception { |
| Format formatter = DateUtil.getDateFormatter("yyyy-MM-dd"); |
| Calendar cal = Calendar.getInstance(); |
| cal.setTime(date); |
| java.util.Date dateToday = cal.getTime(); |
| cal.add(Calendar.DAY_OF_YEAR, 1); |
| java.util.Date dateTomorrow = cal.getTime(); |
| String tableName = generateUniqueName(); |
| String today = formatter.format(dateToday); |
| String tomorrow = formatter.format(dateTomorrow); |
| String query = "SELECT entity_id FROM " + this.tableName + " WHERE a_integer < 4 AND a_date BETWEEN date '" + today + "' AND date '" + tomorrow + "' "; |
| Statement statement = conn.createStatement(); |
| ResultSet rs = statement.executeQuery(query); |
| assertTrue(rs.next()); |
| assertEquals(ROW1, rs.getString(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testSelectLiteralDate() throws Exception { |
| String s = DateUtil.DEFAULT_DATE_FORMATTER.format(date); |
| String query = "SELECT DATE '" + s + "' FROM " + this.tableName; |
| Statement statement = conn.createStatement(); |
| ResultSet rs = statement.executeQuery(query); |
| assertTrue(rs.next()); |
| assertEquals(date, rs.getDate(1)); |
| } |
| |
| @Test |
| public void testSelectLiteralDateCompare() throws Exception { |
| String query = "SELECT (DATE '" + date + "' = DATE '" + date + "') FROM " + this.tableName; |
| Statement statement = conn.createStatement(); |
| ResultSet rs = statement.executeQuery(query); |
| assertTrue(rs.next()); |
| assertTrue(rs.getBoolean(1)); |
| } |
| |
| @Test |
| public void testSelectWhereDatesEqual() throws Exception { |
| String query = "SELECT entity_id FROM " + this.tableName + " WHERE a_integer < 4 AND DATE '" + date + "' = DATE '" + date + "'"; |
| Statement statement = conn.createStatement(); |
| ResultSet rs = statement.executeQuery(query); |
| assertTrue(rs.next()); |
| |
| } |
| |
| @Test |
| public void testSelectWhereDateAndToDateEqual() throws Exception { |
| String query = "SELECT entity_id FROM " + this.tableName + " WHERE a_integer < 4 AND DATE '" + date + "' = TO_DATE ('" + date + "')"; |
| Statement statement = conn.createStatement(); |
| ResultSet rs = statement.executeQuery(query); |
| assertTrue(rs.next()); |
| |
| } |
| |
| @Test |
| public void testSelectWhereDateAndTimestampEqual() throws Exception { |
| final String timestamp = "2012-09-08 07:08:23"; |
| String query = "SELECT entity_id FROM " + this.tableName + " WHERE a_integer < 4 AND DATE '" + timestamp + "' = TIMESTAMP '" + timestamp + "'"; |
| |
| Statement statement = conn.createStatement(); |
| ResultSet rs = statement.executeQuery(query); |
| assertTrue(rs.next()); |
| } |
| |
| @Test |
| public void testSelectWhereSameDatesUnequal() throws Exception { |
| String query = "SELECT entity_id FROM " + this.tableName + " WHERE a_integer < 4 AND DATE '" + date + "' > DATE '" + date + "'"; |
| Statement statement = conn.createStatement(); |
| ResultSet rs = statement.executeQuery(query); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testDateInList() throws Exception { |
| String query = "SELECT entity_id FROM " + this.tableName + " WHERE a_date IN (?,?) AND a_integer < 4"; |
| PreparedStatement statement = conn.prepareStatement(query); |
| statement.setDate(1, new Date(0)); |
| statement.setDate(2, date); |
| ResultSet rs = statement.executeQuery(); |
| assertTrue(rs.next()); |
| assertEquals(ROW1, rs.getString(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testDateBetweenLiterals() throws Exception { |
| Format formatter = DateUtil.getDateFormatter("yyyy-MM-dd"); |
| Calendar cal = Calendar.getInstance(); |
| cal.setTime(date); |
| java.util.Date dateToday = cal.getTime(); |
| cal.add(Calendar.DAY_OF_YEAR, 1); |
| java.util.Date dateTomorrow = cal.getTime(); |
| String today = formatter.format(dateToday); |
| String tomorrow = formatter.format(dateTomorrow); |
| String query = "SELECT entity_id FROM " + this.tableName + " WHERE a_integer < 4 AND a_date BETWEEN date '" + today + "' AND date '" + tomorrow + "' "; |
| Statement statement = conn.createStatement(); |
| ResultSet rs = statement.executeQuery(query); |
| assertTrue(rs.next()); |
| assertEquals(ROW1, rs.getString(1)); |
| assertFalse(rs.next()); |
| } |
| |
| private static int callYearFunction(Connection conn, String invocation) throws SQLException { |
| Statement stmt = conn.createStatement(); |
| ResultSet rs = |
| stmt.executeQuery(String |
| .format("SELECT %s FROM \"SYSTEM\".\"CATALOG\" LIMIT 1", invocation)); |
| assertTrue(rs.next()); |
| int returnValue = rs.getInt(1); |
| assertFalse(rs.next()); |
| rs.close(); |
| stmt.close(); |
| return returnValue; |
| } |
| |
| private int callYearFunction(String invocation) throws SQLException { |
| return callYearFunction(conn, invocation); |
| } |
| |
| @Test |
| public void testYearFunctionDate() throws SQLException { |
| |
| assertEquals(2008, callYearFunction("\"YEAR\"(TO_DATE('2008-01-01', 'yyyy-MM-dd', 'local'))")); |
| |
| assertEquals(2004, |
| callYearFunction("\"YEAR\"(TO_DATE('2004-12-13 10:13:18', 'yyyy-MM-dd hh:mm:ss'))")); |
| |
| assertEquals(2015, callYearFunction("\"YEAR\"(TO_DATE('2015-01-27T16:17:57+00:00'))")); |
| |
| assertEquals(2005, callYearFunction("\"YEAR\"(TO_DATE('2005-12-13 10:13:18'))")); |
| |
| assertEquals(2006, callYearFunction("\"YEAR\"(TO_DATE('2006-12-13'))")); |
| |
| assertEquals(2015, callYearFunction("\"YEAR\"(TO_DATE('2015-W05'))")); |
| |
| assertEquals( |
| 2008, |
| callYearFunction("\"YEAR\"(TO_DATE('Sat, 3 Feb 2008 03:05:06 GMT', 'EEE, d MMM yyyy HH:mm:ss z', 'UTC'))")); |
| } |
| |
| @Test |
| public void testYearFunctionTimestamp() throws SQLException { |
| |
| assertEquals(2015, callYearFunction("\"YEAR\"(TO_TIMESTAMP('2015-01-27T16:17:57+00:00'))")); |
| |
| assertEquals(2015, callYearFunction("\"YEAR\"(TO_TIMESTAMP('2015-01-27T16:17:57Z'))")); |
| |
| assertEquals(2015, callYearFunction("\"YEAR\"(TO_TIMESTAMP('2015-W10-3'))")); |
| |
| assertEquals(2015, callYearFunction("\"YEAR\"(TO_TIMESTAMP('2015-W05'))")); |
| |
| assertEquals(2015, callYearFunction("\"YEAR\"(TO_TIMESTAMP('2015-063'))")); |
| |
| assertEquals(2006, callYearFunction("\"YEAR\"(TO_TIMESTAMP('2006-12-13'))")); |
| |
| assertEquals(2004, |
| callYearFunction("\"YEAR\"(TO_TIMESTAMP('2004-12-13 10:13:18', 'yyyy-MM-dd hh:mm:ss'))")); |
| |
| assertEquals( |
| 2008, |
| callYearFunction("\"YEAR\"(TO_TIMESTAMP('Sat, 3 Feb 2008 03:05:06 GMT', 'EEE, d MMM yyyy HH:mm:ss z', 'UTC'))")); |
| } |
| |
| @Test |
| public void testYearFuncAgainstColumns() throws Exception { |
| String tableName = generateUniqueName(); |
| String ddl = |
| "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER NOT NULL, dates DATE, timestamps TIMESTAMP, times TIME, " + |
| "unsignedDates UNSIGNED_DATE, unsignedTimestamps UNSIGNED_TIMESTAMP, unsignedTimes UNSIGNED_TIME CONSTRAINT pk PRIMARY KEY (k1))"; |
| conn.createStatement().execute(ddl); |
| String dml = "UPSERT INTO " + tableName + " VALUES (1, TO_DATE('2004-03-01 00:00:00'), TO_TIMESTAMP('2006-02-01 00:00:00'), TO_TIME('2008-02-01 00:00:00'), " + |
| "TO_DATE('2010-03-01 00:00:00:896', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-02-01'), TO_TIME('2015-02-01 00:00:00'))"; |
| conn.createStatement().execute(dml); |
| dml = "UPSERT INTO " + tableName + " VALUES (2, TO_DATE('2005-03-01 00:00:00'), TO_TIMESTAMP('2006-02-01 00:00:00'), TO_TIME('2008-02-01 00:00:00'), " + |
| "TO_DATE('2010-03-01 00:00:00:896', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-02-01'), TO_TIME('2015-02-01 00:00:00'))"; |
| conn.createStatement().execute(dml); |
| dml = "UPSERT INTO " + tableName + " VALUES (3, TO_DATE('2006-03-01 00:00:00'), TO_TIMESTAMP('2006-02-01 00:00:00'), TO_TIME('2008-02-01 00:00:00'), " + |
| "TO_DATE('2010-03-01 00:00:00:896', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-02-01'), TO_TIME('2015-02-01 00:00:00'))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT k1, \"YEAR\"(timestamps), \"YEAR\"(times), \"YEAR\"(unsignedDates), \"YEAR\"(unsignedTimestamps), " + |
| "\"YEAR\"(unsignedTimes) FROM " + tableName + " where \"YEAR\"(dates) = 2004"); |
| assertTrue(rs.next()); |
| assertEquals(1, rs.getInt(1)); |
| assertEquals(2006, rs.getInt(2)); |
| assertEquals(2008, rs.getInt(3)); |
| assertEquals(2010, rs.getInt(4)); |
| assertEquals(2012, rs.getInt(5)); |
| assertEquals(2015, rs.getInt(6)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testMonthFuncAgainstColumns() throws Exception { |
| String tableName = generateUniqueName(); |
| String ddl = |
| "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER NOT NULL, dates DATE, timestamps TIMESTAMP, times TIME, " + |
| "unsignedDates UNSIGNED_DATE, unsignedTimestamps UNSIGNED_TIMESTAMP, unsignedTimes UNSIGNED_TIME CONSTRAINT pk PRIMARY KEY (k1))"; |
| conn.createStatement().execute(ddl); |
| String dml = "UPSERT INTO " + tableName + " VALUES (1, TO_DATE('2004-03-10 00:00:00'), TO_TIMESTAMP('2006-04-12 00:00:00'), TO_TIME('2008-05-16 00:00:00'), " + |
| "TO_DATE('2010-06-20 00:00:00:789', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-07-28'), TO_TIME('2015-12-25 00:00:00'))"; |
| conn.createStatement().execute(dml); |
| dml = "UPSERT INTO " + tableName + " VALUES (2, TO_DATE('2004-04-10 00:00:00'), TO_TIMESTAMP('2006-04-12 00:00:00'), TO_TIME('2008-05-16 00:00:00'), " + |
| "TO_DATE('2010-06-20 00:00:00:789', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-07-28'), TO_TIME('2015-12-25 00:00:00'))"; |
| conn.createStatement().execute(dml); |
| dml = "UPSERT INTO " + tableName + " VALUES (3, TO_DATE('2004-05-10 00:00:00'), TO_TIMESTAMP('2006-04-12 00:00:00'), TO_TIME('2008-05-16 00:00:00'), " + |
| "TO_DATE('2010-06-20 00:00:00:789', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-07-28'), TO_TIME('2015-12-25 00:00:00'))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT k1, \"MONTH\"(timestamps), \"MONTH\"(times), \"MONTH\"(unsignedDates), \"MONTH\"(unsignedTimestamps), " + |
| "\"MONTH\"(unsignedTimes) FROM " + tableName + " where \"MONTH\"(dates) = 3"); |
| assertTrue(rs.next()); |
| assertEquals(1, rs.getInt(1)); |
| assertEquals(4, rs.getInt(2)); |
| assertEquals(5, rs.getInt(3)); |
| assertEquals(6, rs.getInt(4)); |
| assertEquals(7, rs.getInt(5)); |
| assertEquals(12, rs.getInt(6)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testUnsignedTimeDateWithLiteral() throws Exception { |
| String tableName = generateUniqueName(); |
| String ddl = |
| "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER NOT NULL," + |
| "unsignedDates UNSIGNED_DATE, unsignedTimestamps UNSIGNED_TIMESTAMP, unsignedTimes UNSIGNED_TIME CONSTRAINT pk PRIMARY KEY (k1))"; |
| conn.createStatement().execute(ddl); |
| String dml = "UPSERT INTO " + tableName + " VALUES (1, " + |
| "'2010-06-20 12:00:00', '2012-07-28 12:00:00', '2015-12-25 12:00:00')"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT k1, unsignedDates, " + |
| "unsignedTimestamps, unsignedTimes FROM " + tableName + " where k1 = 1"); |
| assertTrue(rs.next()); |
| assertEquals(DateUtil.parseDate("2010-06-20 12:00:00"), rs.getDate(2)); |
| assertEquals(DateUtil.parseTimestamp("2012-07-28 12:00:00"), rs.getTimestamp(3)); |
| assertEquals(DateUtil.parseTime("2015-12-25 12:00:00"), rs.getTime(4)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testSecondFuncAgainstColumns() throws Exception { |
| String tableName = generateUniqueName(); |
| String ddl = |
| "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER NOT NULL, dates DATE, timestamps TIMESTAMP, times TIME, " + |
| "unsignedDates UNSIGNED_DATE, unsignedTimestamps UNSIGNED_TIMESTAMP, unsignedTimes UNSIGNED_TIME CONSTRAINT pk PRIMARY KEY (k1))"; |
| conn.createStatement().execute(ddl); |
| String dml = "UPSERT INTO " + tableName + " VALUES (1, TO_DATE('2004-03-01 00:00:10'), TO_TIMESTAMP('2006-04-12 00:00:20'), TO_TIME('2008-05-16 10:00:30'), " + |
| "TO_DATE('2010-06-20 00:00:40:789', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-07-28'), TO_TIME('2015-12-25 00:00:50'))"; |
| conn.createStatement().execute(dml); |
| dml = "UPSERT INTO " + tableName + " VALUES (2, TO_DATE('2004-03-01 00:00:10'), TO_TIMESTAMP('2006-04-12 00:20:30'), TO_TIME('2008-05-16 10:00:30'), " + |
| "TO_DATE('2010-06-20 00:00:40:789', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-07-28'), TO_TIME('2015-12-25 00:00:50'))"; |
| conn.createStatement().execute(dml); |
| dml = "UPSERT INTO " + tableName + " VALUES (3, TO_DATE('2004-03-01 00:00:10'), TO_TIMESTAMP('2006-04-12 00:50:30'), TO_TIME('2008-05-16 10:00:30'), " + |
| "TO_DATE('2010-06-20 00:00:40:789', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-07-28'), TO_TIME('2015-12-25 00:00:50'))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT k1, SECOND(dates), SECOND(times), SECOND(unsignedDates), SECOND(unsignedTimestamps), " + |
| "SECOND(unsignedTimes) FROM " + tableName + " where SECOND(timestamps)=20"); |
| assertTrue(rs.next()); |
| assertEquals(1, rs.getInt(1)); |
| assertEquals(10, rs.getInt(2)); |
| assertEquals(30, rs.getInt(3)); |
| assertEquals(40, rs.getInt(4)); |
| assertEquals(0, rs.getInt(5)); |
| assertEquals(50, rs.getInt(6)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testWeekFuncAgainstColumns() throws Exception { |
| String tableName = generateUniqueName(); |
| String ddl = |
| "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER NOT NULL, dates DATE, timestamps TIMESTAMP, times TIME CONSTRAINT pk PRIMARY KEY (k1))"; |
| conn.createStatement().execute(ddl); |
| String dml = "UPSERT INTO " + tableName + " VALUES (1, TO_DATE('2004-01-10 10:00:10'), TO_TIMESTAMP('2006-04-12 08:00:20'), TO_TIME('2008-05-16 10:00:30'))"; |
| conn.createStatement().execute(dml); |
| dml = "UPSERT INTO " + tableName + " VALUES (2, TO_DATE('2004-01-10 10:00:10'), TO_TIMESTAMP('2006-05-18 08:00:20'), TO_TIME('2008-05-16 10:00:30'))"; |
| conn.createStatement().execute(dml); |
| dml = "UPSERT INTO " + tableName + " VALUES (3, TO_DATE('2004-01-10 10:00:10'), TO_TIMESTAMP('2006-05-18 08:00:20'), TO_TIME('2008-05-16 10:00:30'))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT k1, \"WEEK\"(dates), \"WEEK\"(times) FROM " + tableName + " where \"WEEK\"(timestamps)=15"); |
| assertTrue(rs.next()); |
| assertEquals(1, rs.getInt(1)); |
| assertEquals(2, rs.getInt(2)); |
| assertEquals(20, rs.getInt(3)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testHourFuncAgainstColumns() throws Exception { |
| String tableName = generateUniqueName(); |
| String ddl = |
| "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER NOT NULL, dates DATE, timestamps TIMESTAMP, times TIME CONSTRAINT pk PRIMARY KEY (k1))"; |
| conn.createStatement().execute(ddl); |
| String dml = "UPSERT INTO " + tableName + " VALUES (1, TO_DATE('Sat, 3 Feb 2008 03:05:06 GMT', 'EEE, d MMM yyyy HH:mm:ss z', 'UTC'), TO_TIMESTAMP('2006-04-12 15:10:20'), " + |
| "TO_TIME('2008-05-16 20:40:30'))"; |
| conn.createStatement().execute(dml); |
| dml = "UPSERT INTO " + tableName + " VALUES (2, TO_DATE('Sat, 3 Feb 2008 03:05:06 GMT', 'EEE, d MMM yyyy HH:mm:ss z', 'UTC'), TO_TIMESTAMP('2006-04-12 10:10:20'), " + |
| "TO_TIME('2008-05-16 20:40:30'))"; |
| conn.createStatement().execute(dml); |
| dml = "UPSERT INTO " + tableName + " VALUES (3, TO_DATE('Sat, 3 Feb 2008 03:05:06 GMT', 'EEE, d MMM yyyy HH:mm:ss z', 'UTC'), TO_TIMESTAMP('2006-04-12 08:10:20'), " + |
| "TO_TIME('2008-05-16 20:40:30'))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT k1, \"HOUR\"(dates), \"HOUR\"(times) FROM " + tableName + " where \"HOUR\"(timestamps)=15"); |
| assertTrue(rs.next()); |
| assertEquals(1, rs.getInt(1)); |
| assertEquals(3, rs.getInt(2)); |
| assertEquals(20, rs.getInt(3)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testNowFunction() throws Exception { |
| String tableName = generateUniqueName(); |
| Date date = new Date(System.currentTimeMillis()); |
| String ddl = |
| "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER NOT NULL, timestamps TIMESTAMP CONSTRAINT pk PRIMARY KEY (k1))"; |
| conn.createStatement().execute(ddl); |
| String dml = "UPSERT INTO " + tableName + " VALUES (?, ?)"; |
| PreparedStatement stmt = conn.prepareStatement(dml); |
| stmt.setInt(1, 1); |
| stmt.setDate(2, new Date(date.getTime()-500)); |
| stmt.execute(); |
| stmt.setInt(1, 2); |
| stmt.setDate(2, new Date(date.getTime()+600000)); |
| stmt.execute(); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT * from " + tableName + " where now() > timestamps"); |
| assertTrue(rs.next()); |
| assertEquals(1, rs.getInt(1)); |
| assertEquals(new Date(date.getTime()-500), rs.getDate(2)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testMinuteFuncAgainstColumns() throws Exception { |
| String tableName = generateUniqueName(); |
| String ddl = |
| "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER NOT NULL, dates DATE, timestamps TIMESTAMP, times TIME, " + |
| "unsignedDates UNSIGNED_DATE, unsignedTimestamps UNSIGNED_TIMESTAMP, unsignedTimes UNSIGNED_TIME CONSTRAINT pk PRIMARY KEY (k1))"; |
| conn.createStatement().execute(ddl); |
| String dml = "UPSERT INTO " + tableName + " VALUES (1, TO_DATE('2004-03-01 00:10:10'), TO_TIMESTAMP('2006-04-12 00:20:20'), TO_TIME('2008-05-16 10:30:30'), " + |
| "TO_DATE('2010-06-20 00:40:40:789', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-07-28'), TO_TIME('2015-12-25 00:50:50'))"; |
| conn.createStatement().execute(dml); |
| dml = "UPSERT INTO " + tableName + " VALUES (2, TO_DATE('2004-03-01 00:10:10'), TO_TIMESTAMP('2006-04-12 00:50:20'), TO_TIME('2008-05-16 10:30:30'), " + |
| "TO_DATE('2010-06-20 00:40:40:789', 'yyyy-MM-dd HH:mm:ss:SSS'), TO_TIMESTAMP('2012-07-28'), TO_TIME('2015-12-25 00:50:50'))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT k1, \"MINUTE\"(dates), \"MINUTE\"(times), \"MINUTE\"(unsignedDates), \"MINUTE\"(unsignedTimestamps), " + |
| "\"MINUTE\"(unsignedTimes) FROM " + tableName + " where \"MINUTE\"(timestamps)=20"); |
| assertTrue(rs.next()); |
| assertEquals(1, rs.getInt(1)); |
| assertEquals(10, rs.getInt(2)); |
| assertEquals(30, rs.getInt(3)); |
| assertEquals(40, rs.getInt(4)); |
| assertEquals(0, rs.getInt(5)); |
| assertEquals(50, rs.getInt(6)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testDayOfMonthFuncAgainstColumns() throws Exception { |
| String tableName = generateUniqueName(); |
| String ddl = |
| "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER NOT NULL, dates DATE, timestamps TIMESTAMP, times TIME CONSTRAINT pk PRIMARY KEY (k1))"; |
| conn.createStatement().execute(ddl); |
| String dml = "UPSERT INTO " + tableName + " VALUES (1, TO_DATE('2004-01-08 10:00:10'), TO_TIMESTAMP('2006-04-12 08:00:20'), TO_TIME('2008-05-26 11:00:30'))"; |
| conn.createStatement().execute(dml); |
| dml = "UPSERT INTO " + tableName + " VALUES (2, TO_DATE('2004-01-18 10:00:10'), TO_TIMESTAMP('2006-05-22 08:00:20'), TO_TIME('2008-12-30 11:00:30'))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT k1, DAYOFMONTH(dates), DAYOFMONTH(times) FROM " + tableName + " where DAYOFMONTH(timestamps)=12"); |
| assertTrue(rs.next()); |
| assertEquals(1, rs.getInt(1)); |
| assertEquals(8, rs.getInt(2)); |
| assertEquals(26, rs.getInt(3)); |
| assertFalse(rs.next()); |
| } |
| |
| /* |
| Reference for dates used in the test |
| 2013-04-09 - Tuesday (2) |
| 2014-05-18 - Sunday (7) |
| 2015-06-27 - Saturday (6) |
| */ |
| @Test |
| public void testDayOfWeekFuncAgainstColumns() throws Exception { |
| String tableName = generateUniqueName(); |
| String ddl = "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER NOT NULL, dates DATE, timestamps TIMESTAMP, times TIME CONSTRAINT pk PRIMARY KEY (k1))"; |
| conn.createStatement().execute(ddl); |
| String dml = "UPSERT INTO " + tableName + " VALUES (1, TO_DATE('2012-03-08 11:01:10'), TO_TIMESTAMP('2013-06-16 12:02:20'), TO_TIME('2014-09-23 13:03:30'))"; |
| conn.createStatement().execute(dml); |
| dml = "UPSERT INTO " + tableName + " VALUES (2, TO_DATE('2013-04-09 11:02:10'), TO_TIMESTAMP('2014-05-18 12:03:20'), TO_TIME('2015-06-27 13:04:30'))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT k1, DAYOFWEEK(dates), DAYOFWEEK(timestamps) FROM " + tableName + " where DAYOFWEEK(times)=6"); |
| assertTrue(rs.next()); |
| assertEquals(2, rs.getInt(1)); |
| assertEquals(2, rs.getInt(2)); |
| assertEquals(7, rs.getInt(3)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testDayOfYearFuncAgainstColumns() throws Exception { |
| String tableName = generateUniqueName(); |
| String ddl = "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER NOT NULL, dates DATE, timestamps TIMESTAMP, times TIME CONSTRAINT pk PRIMARY KEY (k1))"; |
| conn.createStatement().execute(ddl); |
| String dml = "UPSERT INTO " + tableName + " VALUES (1, TO_DATE('2012-03-01 11:01:10'), TO_TIMESTAMP('2013-02-01 12:02:20'), TO_TIME('2014-01-15 13:03:30'))"; |
| conn.createStatement().execute(dml); |
| dml = "UPSERT INTO " + tableName + " VALUES (2, TO_DATE('2013-04-09 11:02:10'), TO_TIMESTAMP('2014-05-18 12:03:20'), TO_TIME('2015-06-27 13:04:30'))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT k1, DAYOFYEAR(dates), DAYOFYEAR(timestamps) FROM " + tableName + " where DAYOFYEAR(times)=15"); |
| |
| assertTrue(rs.next()); |
| assertEquals(1, rs.getInt(1)); |
| assertEquals(61, rs.getInt(2)); |
| assertEquals(32, rs.getInt(3)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testNullDate() throws Exception { |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT a_date, entity_id from " + this.tableName + " WHERE entity_id = '" + ROW10 + "'"); |
| assertNotNull(rs); |
| assertTrue(rs.next()); |
| assertEquals(ROW10, rs.getString(2)); |
| assertNull(rs.getDate(1)); |
| assertNull(rs.getDate(1, GregorianCalendar.getInstance())); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testCurrentDateWithNoTable() throws Exception { |
| long expectedTime = System.currentTimeMillis(); |
| ResultSet rs = conn.createStatement().executeQuery("SELECT CURRENT_DATE()"); |
| assertTrue(rs.next()); |
| long actualTime = rs.getDate(1).getTime(); |
| assertTrue(Math.abs(actualTime - expectedTime) < MILLIS_IN_DAY); |
| } |
| @Test |
| public void testSelectBetweenNanos() throws Exception { |
| String tableName = generateUniqueName(); |
| String ddl = |
| "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER NOT NULL PRIMARY KEY, ts " + |
| "TIMESTAMP(3))"; |
| conn.createStatement().execute(ddl); |
| String dml = "UPSERT INTO " + tableName + " VALUES (1, TIMESTAMP'2015-01-01 00:00:00.111111111')"; |
| conn.createStatement().execute(dml); |
| dml = "UPSERT INTO " + tableName + " VALUES (2, TIMESTAMP'2015-01-01 00:00:00.111111115')"; |
| conn.createStatement().execute(dml); |
| dml = "UPSERT INTO " + tableName + " VALUES (3, TIMESTAMP'2015-01-01 00:00:00.111111113')"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT k1,ts from " + tableName + " where ts between" + |
| " TIMESTAMP'2015-01-01 00:00:00.111111112' AND TIMESTAMP'2015-01-01 00:00:00" + |
| ".111111114'"); |
| assertTrue(rs.next()); |
| assertEquals(3, rs.getInt(1)); |
| assertEquals(111111113, rs.getTimestamp(2).getNanos()); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testCurrentTimeWithProjectedTable () throws Exception { |
| String tableName1 = generateUniqueName(); |
| String tableName2 = generateUniqueName(); |
| String ddl = "CREATE TABLE " + tableName1 + " ( ID integer primary key)"; |
| conn.createStatement().execute(ddl); |
| ddl = "CREATE TABLE " + tableName2 + " ( ID integer primary key)"; |
| conn.createStatement().execute(ddl); |
| String ups = "UPSERT INTO " + tableName1 + " VALUES (1)"; |
| conn.createStatement().execute(ups); |
| ups = "UPSERT INTO " + tableName2 + " VALUES (1)"; |
| conn.createStatement().execute(ups); |
| conn.commit(); |
| ResultSet rs = conn.createStatement().executeQuery("select /*+ USE_SORT_MERGE_JOIN */ op" + |
| ".id, current_time() from " +tableName1 + " op where op.id in (select id from " + tableName2 + ")"); |
| assertTrue(rs.next()); |
| assertEquals(new java.util.Date().getYear(),rs.getTimestamp(2).getYear()); |
| } |
| |
| @Test |
| public void testLiteralDateComparison() throws Exception { |
| ResultSet rs = |
| conn.createStatement().executeQuery( |
| "select DATE '2016-05-10 00:00:00' > DATE '2016-05-11 00:00:00'"); |
| |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testLiteralTimestampComparison() throws Exception { |
| ResultSet rs = |
| conn.createStatement().executeQuery( |
| "select TIMESTAMP '2016-05-10 00:00:00' > TIMESTAMP '2016-05-11 00:00:00'"); |
| |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testLiteralDateTimestampComparison() throws Exception { |
| ResultSet rs = |
| conn.createStatement().executeQuery( |
| "select \"DATE\" '2016-05-10 00:00:00' > \"TIMESTAMP\" '2016-05-11 00:00:00'"); |
| |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testLiteralDateTimestampComparison2() throws Exception { |
| ResultSet rs = |
| conn.createStatement().executeQuery( |
| "select \"TIMESTAMP\" '2016-05-10 00:00:00' > \"DATE\" '2016-05-11 00:00:00'"); |
| |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testFunctionOnNullDate() throws Exception { |
| ResultSet rs = conn.createStatement().executeQuery("SELECT \"YEAR\"(a_date), entity_id from " + this.tableName + " WHERE entity_id = '" + ROW10 + "'"); |
| assertNotNull(rs); |
| assertTrue(rs.next()); |
| assertEquals(ROW10, rs.getString(2)); |
| assertNull(rs.getDate(1)); |
| assertNull(rs.getDate(1, GregorianCalendar.getInstance())); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testProjectedDateTimestampUnequal() throws Exception { |
| String tableName = generateUniqueName(); |
| String ddl = |
| "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER PRIMARY KEY, dates DATE, timestamps TIMESTAMP)"; |
| conn.createStatement().execute(ddl); |
| // Differ by date |
| String dml = "UPSERT INTO " + tableName + " VALUES (1," + |
| "TO_DATE('2004-02-04 00:10:10')," + |
| "TO_TIMESTAMP('2006-04-12 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| // Differ by time |
| dml = "UPSERT INTO " + tableName + " VALUES (2," + |
| "TO_DATE('2004-02-04 00:10:10'), " + |
| "TO_TIMESTAMP('2004-02-04 15:10:20'))"; |
| conn.createStatement().execute(dml); |
| // Differ by nanoseconds |
| PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES (?, ?, ?)"); |
| stmt.setInt(1, 3); |
| stmt.setDate(2, new Date(1000)); |
| Timestamp ts = new Timestamp(1000); |
| ts.setNanos(100); |
| stmt.setTimestamp(3, ts); |
| stmt.execute(); |
| // Equality |
| dml = "UPSERT INTO " + tableName + " VALUES (4," + |
| "TO_DATE('2004-02-04 00:10:10'), " + |
| "TO_TIMESTAMP('2004-02-04 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT dates = timestamps FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(true, rs.getBoolean(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testProjectedTimeTimestampCompare() throws Exception { |
| String tableName = generateUniqueName(); |
| String ddl = |
| "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER PRIMARY KEY, times TIME, timestamps TIMESTAMP)"; |
| conn.createStatement().execute(ddl); |
| // Differ by date |
| String dml = "UPSERT INTO " + tableName + " VALUES (1," + |
| "TO_TIME('2004-02-04 00:10:10')," + |
| "TO_TIMESTAMP('2006-04-12 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| // Differ by time |
| dml = "UPSERT INTO " + tableName + " VALUES (2," + |
| "TO_TIME('2004-02-04 00:10:10'), " + |
| "TO_TIMESTAMP('2004-02-04 15:10:20'))"; |
| conn.createStatement().execute(dml); |
| // Differ by nanoseconds |
| PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES (?, ?, ?)"); |
| stmt.setInt(1, 3); |
| stmt.setTime(2, new Time(1000)); |
| Timestamp ts = new Timestamp(1000); |
| ts.setNanos(100); |
| stmt.setTimestamp(3, ts); |
| stmt.execute(); |
| // Equality |
| dml = "UPSERT INTO " + tableName + " VALUES (4," + |
| "TO_TIME('2004-02-04 00:10:10'), " + |
| "TO_TIMESTAMP('2004-02-04 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT times = timestamps FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(true, rs.getBoolean(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testProjectedDateTimeCompare() throws Exception { |
| String tableName = generateUniqueName(); |
| String ddl = |
| "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER PRIMARY KEY, dates DATE, times TIME)"; |
| conn.createStatement().execute(ddl); |
| // Differ by date |
| String dml = "UPSERT INTO " + tableName + " VALUES (1," + |
| "TO_DATE('2004-02-04 00:10:10')," + |
| "TO_TIME('2006-04-12 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| // Differ by time |
| dml = "UPSERT INTO " + tableName + " VALUES (2," + |
| "TO_DATE('2004-02-04 00:10:10'), " + |
| "TO_TIME('2004-02-04 15:10:20'))"; |
| conn.createStatement().execute(dml); |
| // Equality |
| dml = "UPSERT INTO " + tableName + " VALUES (3," + |
| "TO_DATE('2004-02-04 00:10:10'), " + |
| "TO_TIME('2004-02-04 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT dates = times FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(true, rs.getBoolean(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testProjectedDateUnsignedTimestampCompare() throws Exception { |
| String tableName = generateUniqueName(); |
| String ddl = |
| "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER PRIMARY KEY, dates DATE, timestamps UNSIGNED_TIMESTAMP)"; |
| conn.createStatement().execute(ddl); |
| // Differ by date |
| String dml = "UPSERT INTO " + tableName + " VALUES (1," + |
| "TO_DATE('2004-02-04 00:10:10')," + |
| "TO_TIMESTAMP('2006-04-12 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| // Differ by time |
| dml = "UPSERT INTO " + tableName + " VALUES (2," + |
| "TO_DATE('2004-02-04 00:10:10'), " + |
| "TO_TIMESTAMP('2004-02-04 15:10:20'))"; |
| conn.createStatement().execute(dml); |
| // Differ by nanoseconds |
| PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES (?, ?, ?)"); |
| stmt.setInt(1, 3); |
| stmt.setDate(2, new Date(1000)); |
| Timestamp ts = new Timestamp(1000); |
| ts.setNanos(100); |
| stmt.setTimestamp(3, ts); |
| stmt.execute(); |
| // Equality |
| dml = "UPSERT INTO " + tableName + " VALUES (4," + |
| "TO_DATE('2004-02-04 00:10:10'), " + |
| "TO_TIMESTAMP('2004-02-04 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT dates = timestamps FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(true, rs.getBoolean(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testProjectedTimeUnsignedTimestampCompare() throws Exception { |
| String tableName = generateUniqueName(); |
| String ddl = |
| "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER PRIMARY KEY, times TIME, timestamps UNSIGNED_TIMESTAMP)"; |
| conn.createStatement().execute(ddl); |
| // Differ by date |
| String dml = "UPSERT INTO " + tableName + " VALUES (1," + |
| "TO_TIME('2004-02-04 00:10:10')," + |
| "TO_TIMESTAMP('2006-04-12 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| // Differ by time |
| dml = "UPSERT INTO " + tableName + " VALUES (2," + |
| "TO_TIME('2004-02-04 00:10:10'), " + |
| "TO_TIMESTAMP('2004-02-04 15:10:20'))"; |
| conn.createStatement().execute(dml); |
| // Differ by nanoseconds |
| PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES (?, ?, ?)"); |
| stmt.setInt(1, 3); |
| stmt.setTime(2, new Time(1000)); |
| Timestamp ts = new Timestamp(1000); |
| ts.setNanos(100); |
| stmt.setTimestamp(3, ts); |
| stmt.execute(); |
| // Equality |
| dml = "UPSERT INTO " + tableName + " VALUES (4," + |
| "TO_TIME('2004-02-04 00:10:10'), " + |
| "TO_TIMESTAMP('2004-02-04 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT times = timestamps FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(true, rs.getBoolean(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testProjectedDateUnsignedTimeCompare() throws Exception { |
| String tableName = generateUniqueName(); |
| String ddl = |
| "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER PRIMARY KEY, dates DATE, times UNSIGNED_TIME)"; |
| conn.createStatement().execute(ddl); |
| // Differ by date |
| String dml = "UPSERT INTO " + tableName + " VALUES (1," + |
| "TO_DATE('2004-02-04 00:10:10')," + |
| "TO_TIME('2006-04-12 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| // Differ by time |
| dml = "UPSERT INTO " + tableName + " VALUES (2," + |
| "TO_DATE('2004-02-04 00:10:10'), " + |
| "TO_TIME('2004-02-04 15:10:20'))"; |
| conn.createStatement().execute(dml); |
| // Equality |
| dml = "UPSERT INTO " + tableName + " VALUES (3," + |
| "TO_DATE('2004-02-04 00:10:10'), " + |
| "TO_TIME('2004-02-04 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT dates = times FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(true, rs.getBoolean(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testProjectedUnsignedDateTimestampCompare() throws Exception { |
| String tableName = generateUniqueName(); |
| String ddl = |
| "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER PRIMARY KEY, dates UNSIGNED_DATE, timestamps TIMESTAMP)"; |
| conn.createStatement().execute(ddl); |
| // Differ by date |
| String dml = "UPSERT INTO " + tableName + " VALUES (1," + |
| "TO_DATE('2004-02-04 00:10:10')," + |
| "TO_TIMESTAMP('2006-04-12 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| // Differ by time |
| dml = "UPSERT INTO " + tableName + " VALUES (2," + |
| "TO_DATE('2004-02-04 00:10:10'), " + |
| "TO_TIMESTAMP('2004-02-04 15:10:20'))"; |
| conn.createStatement().execute(dml); |
| // Differ by nanoseconds |
| PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES (?, ?, ?)"); |
| stmt.setInt(1, 3); |
| stmt.setDate(2, new Date(1000)); |
| Timestamp ts = new Timestamp(1000); |
| ts.setNanos(100); |
| stmt.setTimestamp(3, ts); |
| stmt.execute(); |
| // Equality |
| dml = "UPSERT INTO " + tableName + " VALUES (4," + |
| "TO_DATE('2004-02-04 00:10:10'), " + |
| "TO_TIMESTAMP('2004-02-04 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT dates = timestamps FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(true, rs.getBoolean(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testProjectedUnsignedTimeTimestampCompare() throws Exception { |
| String tableName = generateUniqueName(); |
| String ddl = |
| "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER PRIMARY KEY, times UNSIGNED_TIME, timestamps TIMESTAMP)"; |
| conn.createStatement().execute(ddl); |
| // Differ by date |
| String dml = "UPSERT INTO " + tableName + " VALUES (1," + |
| "TO_TIME('2004-02-04 00:10:10')," + |
| "TO_TIMESTAMP('2006-04-12 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| // Differ by time |
| dml = "UPSERT INTO " + tableName + " VALUES (2," + |
| "TO_TIME('2004-02-04 00:10:10'), " + |
| "TO_TIMESTAMP('2004-02-04 15:10:20'))"; |
| conn.createStatement().execute(dml); |
| // Differ by nanoseconds |
| PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES (?, ?, ?)"); |
| stmt.setInt(1, 3); |
| stmt.setTime(2, new Time(1000)); |
| Timestamp ts = new Timestamp(1000); |
| ts.setNanos(100); |
| stmt.setTimestamp(3, ts); |
| stmt.execute(); |
| // Equality |
| dml = "UPSERT INTO " + tableName + " VALUES (4," + |
| "TO_TIME('2004-02-04 00:10:10'), " + |
| "TO_TIMESTAMP('2004-02-04 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT times = timestamps FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(true, rs.getBoolean(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testProjectedUnsignedDateTimeCompare() throws Exception { |
| String tableName = generateUniqueName(); |
| String ddl = |
| "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER PRIMARY KEY, dates UNSIGNED_DATE, times TIME)"; |
| conn.createStatement().execute(ddl); |
| // Differ by date |
| String dml = "UPSERT INTO " + tableName + " VALUES (1," + |
| "TO_DATE('2004-02-04 00:10:10')," + |
| "TO_TIME('2006-04-12 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| // Differ by time |
| dml = "UPSERT INTO " + tableName + " VALUES (2," + |
| "TO_DATE('2004-02-04 00:10:10'), " + |
| "TO_TIME('2004-02-04 15:10:20'))"; |
| conn.createStatement().execute(dml); |
| // Equality |
| dml = "UPSERT INTO " + tableName + " VALUES (3," + |
| "TO_DATE('2004-02-04 00:10:10'), " + |
| "TO_TIME('2004-02-04 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT dates = times FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(true, rs.getBoolean(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testProjectedUnsignedDateUnsignedTimestampCompare() throws Exception { |
| String tableName = generateUniqueName(); |
| String ddl = |
| "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER PRIMARY KEY, dates UNSIGNED_DATE, timestamps UNSIGNED_TIMESTAMP)"; |
| conn.createStatement().execute(ddl); |
| // Differ by date |
| String dml = "UPSERT INTO " + tableName + " VALUES (1," + |
| "TO_DATE('2004-02-04 00:10:10')," + |
| "TO_TIMESTAMP('2006-04-12 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| // Differ by time |
| dml = "UPSERT INTO " + tableName + " VALUES (2," + |
| "TO_DATE('2004-02-04 00:10:10'), " + |
| "TO_TIMESTAMP('2004-02-04 15:10:20'))"; |
| conn.createStatement().execute(dml); |
| // Differ by nanoseconds |
| PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES (?, ?, ?)"); |
| stmt.setInt(1, 3); |
| stmt.setDate(2, new Date(1000)); |
| Timestamp ts = new Timestamp(1000); |
| ts.setNanos(100); |
| stmt.setTimestamp(3, ts); |
| stmt.execute(); |
| // Equality |
| dml = "UPSERT INTO " + tableName + " VALUES (4," + |
| "TO_DATE('2004-02-04 00:10:10'), " + |
| "TO_TIMESTAMP('2004-02-04 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT dates = timestamps FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(true, rs.getBoolean(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testProjectedUnsignedTimeUnsignedTimestampCompare() throws Exception { |
| String tableName = generateUniqueName(); |
| String ddl = |
| "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER PRIMARY KEY, times UNSIGNED_TIME, timestamps UNSIGNED_TIMESTAMP)"; |
| conn.createStatement().execute(ddl); |
| // Differ by date |
| String dml = "UPSERT INTO " + tableName + " VALUES (1," + |
| "TO_TIME('2004-02-04 00:10:10')," + |
| "TO_TIMESTAMP('2006-04-12 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| // Differ by time |
| dml = "UPSERT INTO " + tableName + " VALUES (2," + |
| "TO_TIME('2004-02-04 00:10:10'), " + |
| "TO_TIMESTAMP('2004-02-04 15:10:20'))"; |
| conn.createStatement().execute(dml); |
| // Differ by nanoseconds |
| PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES (?, ?, ?)"); |
| stmt.setInt(1, 3); |
| stmt.setTime(2, new Time(1000)); |
| Timestamp ts = new Timestamp(1000); |
| ts.setNanos(100); |
| stmt.setTimestamp(3, ts); |
| stmt.execute(); |
| // Equality |
| dml = "UPSERT INTO " + tableName + " VALUES (4," + |
| "TO_TIME('2004-02-04 00:10:10'), " + |
| "TO_TIMESTAMP('2004-02-04 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT times = timestamps FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(true, rs.getBoolean(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testProjectedUnsignedDateUnsignedTimeCompare() throws Exception { |
| String tableName = generateUniqueName(); |
| String ddl = |
| "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER PRIMARY KEY, dates UNSIGNED_DATE, times UNSIGNED_TIME)"; |
| conn.createStatement().execute(ddl); |
| // Differ by date |
| String dml = "UPSERT INTO " + tableName + " VALUES (1," + |
| "TO_DATE('2004-02-04 00:10:10')," + |
| "TO_TIME('2006-04-12 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| // Differ by time |
| dml = "UPSERT INTO " + tableName + " VALUES (2," + |
| "TO_DATE('2004-02-04 00:10:10'), " + |
| "TO_TIME('2004-02-04 15:10:20'))"; |
| conn.createStatement().execute(dml); |
| // Equality |
| dml = "UPSERT INTO " + tableName + " VALUES (3," + |
| "TO_DATE('2004-02-04 00:10:10'), " + |
| "TO_TIME('2004-02-04 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT dates = times FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(true, rs.getBoolean(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testProjectedDateDateCompare() throws Exception { |
| String tableName = generateUniqueName(); |
| String ddl = |
| "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER PRIMARY KEY, date1 DATE, date2 DATE)"; |
| conn.createStatement().execute(ddl); |
| // Differ by date |
| String dml = "UPSERT INTO " + tableName + " VALUES (1," + |
| "TO_DATE('2004-02-04 00:10:10')," + |
| "TO_DATE('2006-04-12 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| // Differ by time |
| dml = "UPSERT INTO " + tableName + " VALUES (2," + |
| "TO_DATE('2004-02-04 00:10:10'), " + |
| "TO_DATE('2004-02-04 15:10:20'))"; |
| conn.createStatement().execute(dml); |
| // Equality |
| dml = "UPSERT INTO " + tableName + " VALUES (3," + |
| "TO_DATE('2004-02-04 00:10:10'), " + |
| "TO_DATE('2004-02-04 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT date1 = date2 FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(true, rs.getBoolean(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testProjectedUnsignedDateUnsignedDateCompare() throws Exception { |
| String tableName = generateUniqueName(); |
| String ddl = |
| "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER PRIMARY KEY, date1 UNSIGNED_DATE, date2 UNSIGNED_DATE)"; |
| conn.createStatement().execute(ddl); |
| // Differ by date |
| String dml = "UPSERT INTO " + tableName + " VALUES (1," + |
| "TO_DATE('2004-02-04 00:10:10')," + |
| "TO_DATE('2006-04-12 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| // Differ by time |
| dml = "UPSERT INTO " + tableName + " VALUES (2," + |
| "TO_DATE('2004-02-04 00:10:10'), " + |
| "TO_DATE('2004-02-04 15:10:20'))"; |
| conn.createStatement().execute(dml); |
| // Equality |
| dml = "UPSERT INTO " + tableName + " VALUES (3," + |
| "TO_DATE('2004-02-04 00:10:10'), " + |
| "TO_DATE('2004-02-04 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT date1 = date2 FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(true, rs.getBoolean(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testProjectedTimeTimeCompare() throws Exception { |
| String tableName = generateUniqueName(); |
| String ddl = |
| "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER PRIMARY KEY, time1 TIME, time2 TIME)"; |
| conn.createStatement().execute(ddl); |
| // Differ by date |
| String dml = "UPSERT INTO " + tableName + " VALUES (1," + |
| "TO_TIME('2004-02-04 00:10:10')," + |
| "TO_TIME('2006-04-12 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| // Differ by time |
| dml = "UPSERT INTO " + tableName + " VALUES (2," + |
| "TO_TIME('2004-02-04 00:10:10'), " + |
| "TO_TIME('2004-02-04 15:10:20'))"; |
| conn.createStatement().execute(dml); |
| // Equality |
| dml = "UPSERT INTO " + tableName + " VALUES (3," + |
| "TO_TIME('2004-02-04 00:10:10'), " + |
| "TO_TIME('2004-02-04 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT time1 = time2 FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(true, rs.getBoolean(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testProjectedUnsignedTimeUnsignedTimeCompare() throws Exception { |
| String tableName = generateUniqueName(); |
| String ddl = |
| "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER PRIMARY KEY, time1 UNSIGNED_TIME, time2 UNSIGNED_TIME)"; |
| conn.createStatement().execute(ddl); |
| // Differ by date |
| String dml = "UPSERT INTO " + tableName + " VALUES (1," + |
| "TO_TIME('2004-02-04 00:10:10')," + |
| "TO_TIME('2006-04-12 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| // Differ by time |
| dml = "UPSERT INTO " + tableName + " VALUES (2," + |
| "TO_TIME('2004-02-04 00:10:10'), " + |
| "TO_TIME('2004-02-04 15:10:20'))"; |
| conn.createStatement().execute(dml); |
| // Equality |
| dml = "UPSERT INTO " + tableName + " VALUES (3," + |
| "TO_TIME('2004-02-04 00:10:10'), " + |
| "TO_TIME('2004-02-04 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT time1 = time2 FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(true, rs.getBoolean(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testProjectedTimeStampTimeStampCompare() throws Exception { |
| String tableName = generateUniqueName(); |
| String ddl = |
| "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER PRIMARY KEY, timestamp1 TIMESTAMP, timestamp2 TIMESTAMP)"; |
| conn.createStatement().execute(ddl); |
| // Differ by date |
| String dml = "UPSERT INTO " + tableName + " VALUES (1," + |
| "TO_TIMESTAMP('2004-02-04 00:10:10')," + |
| "TO_TIMESTAMP('2006-04-12 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| // Differ by time |
| dml = "UPSERT INTO " + tableName + " VALUES (2," + |
| "TO_TIMESTAMP('2004-02-04 00:10:10'), " + |
| "TO_TIMESTAMP('2004-02-04 15:10:20'))"; |
| conn.createStatement().execute(dml); |
| // Differ by nanoseconds |
| PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES (?, ?, ?)"); |
| stmt.setInt(1, 3); |
| Timestamp ts = new Timestamp(1000); |
| stmt.setTimestamp(2, ts); |
| ts.setNanos(100); |
| stmt.setTimestamp(3, ts); |
| stmt.execute(); |
| // Equality |
| dml = "UPSERT INTO " + tableName + " VALUES (4," + |
| "TO_TIMESTAMP('2004-02-04 00:10:10'), " + |
| "TO_TIMESTAMP('2004-02-04 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT timestamp1 = timestamp2 FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(true, rs.getBoolean(1)); |
| assertFalse(rs.next()); |
| } |
| |
| @Test |
| public void testProjectedUnsignedTimeStampUnsignedTimeStampCompare() throws Exception { |
| String tableName = generateUniqueName(); |
| String ddl = |
| "CREATE TABLE IF NOT EXISTS " + tableName + " (k1 INTEGER PRIMARY KEY, timestamp1 UNSIGNED_TIMESTAMP, timestamp2 UNSIGNED_TIMESTAMP)"; |
| conn.createStatement().execute(ddl); |
| // Differ by date |
| String dml = "UPSERT INTO " + tableName + " VALUES (1," + |
| "TO_TIMESTAMP('2004-02-04 00:10:10')," + |
| "TO_TIMESTAMP('2006-04-12 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| // Differ by time |
| dml = "UPSERT INTO " + tableName + " VALUES (2," + |
| "TO_TIMESTAMP('2004-02-04 00:10:10'), " + |
| "TO_TIMESTAMP('2004-02-04 15:10:20'))"; |
| conn.createStatement().execute(dml); |
| // Differ by nanoseconds |
| PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + tableName + " VALUES (?, ?, ?)"); |
| stmt.setInt(1, 3); |
| Timestamp ts = new Timestamp(1000); |
| stmt.setTimestamp(2, ts); |
| ts.setNanos(100); |
| stmt.setTimestamp(3, ts); |
| stmt.execute(); |
| // Equality |
| dml = "UPSERT INTO " + tableName + " VALUES (4," + |
| "TO_TIMESTAMP('2004-02-04 00:10:10'), " + |
| "TO_TIMESTAMP('2004-02-04 00:10:10'))"; |
| conn.createStatement().execute(dml); |
| conn.commit(); |
| |
| ResultSet rs = conn.createStatement().executeQuery("SELECT timestamp1 = timestamp2 FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(false, rs.getBoolean(1)); |
| assertTrue(rs.next()); |
| assertEquals(true, rs.getBoolean(1)); |
| assertFalse(rs.next()); |
| } |
| } |
