/*
 * 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.apache.phoenix.util.TestUtil.TEST_PROPERTIES;
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.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.Format;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.Properties;
import java.util.TimeZone;

import org.apache.commons.lang.time.FastDateFormat;
import org.apache.hadoop.hbase.filter.CompareFilter.CompareOp;
import org.apache.hadoop.hbase.io.ImmutableBytesWritable;
import org.apache.hadoop.hbase.util.Bytes;
import org.apache.phoenix.compile.StatementContext;
import org.apache.phoenix.jdbc.PhoenixConnection;
import org.apache.phoenix.jdbc.PhoenixStatement;
import org.apache.phoenix.query.QueryConstants;
import org.apache.phoenix.schema.types.PDataType;
import org.apache.phoenix.schema.types.PDate;
import org.apache.phoenix.schema.types.PTime;
import org.apache.phoenix.schema.types.PTimestamp;
import org.apache.phoenix.util.ByteUtil;
import org.apache.phoenix.util.DateUtil;
import org.apache.phoenix.util.PropertiesUtil;
import org.apache.phoenix.util.TestUtil;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;


public class DateTimeIT extends ParallelStatsDisabledIT {
    private static final String PRODUCT_METRICS_NAME = "PRODUCT_METRICS";
    private static final Date SPLIT1 = toDate("1970-01-01 01:30:00");
    private static final Date SPLIT2 = toDate("1970-01-01 02:00:00");
    private static final String R1 = "R1";
    private static final String R2 = "R2";

    protected Connection conn;
    protected Date date;
    protected static final String tenantId = getOrganizationId();
    protected final static String ROW10 = "00D123122312312";
    protected  String tableName;

    private static void initDateTableValues(String tablename, Connection conn, String tenantId, Date startDate) throws Exception {
        double dateIncrement = 2.0;
        PreparedStatement stmt = conn.prepareStatement(
                "upsert into " +tablename+
                        "(" +
                        "    ORGANIZATION_ID, " +
                        "    \"DATE\", " +
                        "    FEATURE, " +
                        "    UNIQUE_USERS, " +
                        "    TRANSACTIONS, " +
                        "    CPU_UTILIZATION, " +
                        "    DB_UTILIZATION, " +
                        "    REGION, " +
                        "    IO_TIME)" +
                        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
        stmt.setString(1, tenantId);
        stmt.setDate(2, startDate);
        stmt.setString(3, "A");
        stmt.setInt(4, 10);
        stmt.setLong(5, 100L);
        stmt.setBigDecimal(6, BigDecimal.valueOf(0.5));
        stmt.setBigDecimal(7, BigDecimal.valueOf(0.2));
        stmt.setString(8, R2);
        stmt.setNull(9, Types.BIGINT);
        stmt.execute();

        startDate = new Date(startDate.getTime() + (long)(QueryConstants.MILLIS_IN_DAY * dateIncrement));
        stmt.setString(1, tenantId);
        stmt.setDate(2, startDate);
        stmt.setString(3, "B");
        stmt.setInt(4, 20);
        stmt.setLong(5, 200);
        stmt.setBigDecimal(6, BigDecimal.valueOf(1.0));
        stmt.setBigDecimal(7, BigDecimal.valueOf(0.4));
        stmt.setString(8, null);
        stmt.setLong(9, 2000);
        stmt.execute();

        startDate = new Date(startDate.getTime() + (long)(QueryConstants.MILLIS_IN_DAY * dateIncrement));
        stmt.setString(1, tenantId);
        stmt.setDate(2, startDate);
        stmt.setString(3, "C");
        stmt.setInt(4, 30);
        stmt.setLong(5, 300);
        stmt.setBigDecimal(6, BigDecimal.valueOf(2.5));
        stmt.setBigDecimal(7, BigDecimal.valueOf(0.6));
        stmt.setString(8, R1);
        stmt.setNull(9, Types.BIGINT);
        stmt.execute();

        startDate = new Date(startDate.getTime() + (long)(QueryConstants.MILLIS_IN_DAY * dateIncrement));
        stmt.setString(1, tenantId);
        stmt.setDate(2, startDate);
        stmt.setString(3, "D");
        stmt.setInt(4, 40);
        stmt.setLong(5, 400);
        stmt.setBigDecimal(6, BigDecimal.valueOf(3.0));
        stmt.setBigDecimal(7, BigDecimal.valueOf(0.8));
        stmt.setString(8, R1);
        stmt.setLong(9, 4000);
        stmt.execute();

        startDate = new Date(startDate.getTime() + (long)(QueryConstants.MILLIS_IN_DAY * dateIncrement));
        stmt.setString(1, tenantId);
        stmt.setDate(2, startDate);
        stmt.setString(3, "E");
        stmt.setInt(4, 50);
        stmt.setLong(5, 500);
        stmt.setBigDecimal(6, BigDecimal.valueOf(3.5));
        stmt.setBigDecimal(7, BigDecimal.valueOf(1.2));
        stmt.setString(8, R2);
        stmt.setLong(9, 5000);
        stmt.execute();

        startDate = new Date(startDate.getTime() + (long)(QueryConstants.MILLIS_IN_DAY * dateIncrement));
        stmt.setString(1, tenantId);
        stmt.setDate(2, startDate);
        stmt.setString(3, "F");
        stmt.setInt(4, 60);
        stmt.setLong(5, 600);
        stmt.setBigDecimal(6, BigDecimal.valueOf(4.0));
        stmt.setBigDecimal(7, BigDecimal.valueOf(1.4));
        stmt.setString(8, null);
        stmt.setNull(9, Types.BIGINT);
        stmt.execute();
    }

    private static void initDateTableValues(String tablename, String tenantId, byte[][] splits, Date startDate) throws Exception {
        ensureTableCreated(getUrl(), tablename, PRODUCT_METRICS_NAME, splits, null, null);

       Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
       Connection conn = DriverManager.getConnection(getUrl(), props);
       try {
           initDateTableValues(tablename, conn, tenantId, startDate);
           conn.commit();
       } finally {
           conn.close();
       }
    }


    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 testNowWithSubquery() throws Exception {
        String query =
                "SELECT now(), reference_date FROM (select now() as "
                        + "reference_date union all select now() as reference_date) limit 1";
        Statement statement = conn.createStatement();
        ResultSet rs = statement.executeQuery(query);
        assertTrue(rs.next());
        assertTrue(Math.abs(rs.getTime(1).getTime()-rs.getTime(2).getTime())<10000);
        assertEquals(rs.getDate(2).toString(), rs.getDate(1).toString());
        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());
    }
    
    private static byte[][] getSplits(String tenantId) {
        return new byte[][] {
                ByteUtil.concat(Bytes.toBytes(tenantId), PDate.INSTANCE.toBytes(SPLIT1)),
                ByteUtil.concat(Bytes.toBytes(tenantId), PDate.INSTANCE.toBytes(SPLIT2)),
        };
    }

    private static Date toDate(String dateString) {
        return DateUtil.parseDate(dateString);
    }

    @Test
    public void testDateSubtractionCompareNumber() throws Exception {
        String tablename=generateUniqueName();
        String tenantId = getOrganizationId();
        String query = "SELECT feature FROM "+tablename+" WHERE organization_id = ? and ? - \"DATE\" > 3";
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(getUrl(), props);
        try {
            Date startDate = new Date(System.currentTimeMillis());
            Date endDate = new Date(startDate.getTime() + 6 * QueryConstants.MILLIS_IN_DAY);
            initDateTableValues(tablename, tenantId, getSplits(tenantId), startDate);
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            statement.setDate(2, endDate);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("A", rs.getString(1));
            assertTrue(rs.next());
            assertEquals("B", rs.getString(1));
            assertFalse(rs.next());
        } finally {
            conn.close();
        }
    }

    @Test
    public void testDateSubtractionLongToDecimalCompareNumber() throws Exception {
        String tablename=generateUniqueName();
        String tenantId = getOrganizationId();
        String query = "SELECT feature FROM "+tablename+" WHERE organization_id = ? and ? - \"DATE\" - 1.5 > 3";
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(getUrl(), props);
        try {
            Date startDate = new Date(System.currentTimeMillis());
            Date endDate = new Date(startDate.getTime() + 9 * QueryConstants.MILLIS_IN_DAY);
            initDateTableValues(tablename, tenantId, getSplits(tenantId), startDate);
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            statement.setDate(2, endDate);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("A", rs.getString(1));
            assertTrue(rs.next());
            assertEquals("B", rs.getString(1));
            assertTrue(rs.next());
            assertEquals("C", rs.getString(1));
            assertFalse(rs.next());
        } finally {
            conn.close();
        }
    }

    @Test
    public void testDateSubtractionCompareDate() throws Exception {
        String tablename=generateUniqueName();
        String tenantId = getOrganizationId();
        String query = "SELECT feature FROM "+tablename+" WHERE organization_id = ? and date - 1 >= ?";
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(getUrl(), props);
        try {
            Date startDate = new Date(System.currentTimeMillis());
            Date endDate = new Date(startDate.getTime() + 9 * QueryConstants.MILLIS_IN_DAY);
            initDateTableValues(tablename, tenantId, getSplits(tenantId), startDate);
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            statement.setDate(2, endDate);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("F", rs.getString(1));
            assertFalse(rs.next());
        } finally {
            conn.close();
        }
    }

    @Test
    public void testDateAddCompareDate() throws Exception {
        String tablename=generateUniqueName();
        String tenantId = getOrganizationId();
        String query = "SELECT feature FROM "+tablename+" WHERE organization_id = ? and date + 1 >= ?";
        Connection conn = DriverManager.getConnection(url);
        try {
            Date startDate = new Date(System.currentTimeMillis());
            Date endDate = new Date(startDate.getTime() + 8 * QueryConstants.MILLIS_IN_DAY);
            initDateTableValues(tablename, tenantId, getSplits(tenantId), startDate);
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            statement.setDate(2, endDate);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("E", rs.getString(1));
            assertTrue(rs.next());
            assertEquals("F", rs.getString(1));
            assertFalse(rs.next());
        } finally {
            conn.close();
        }
    }

    @Test
    public void testCurrentDate() throws Exception {
        String tablename=generateUniqueName();
        String tenantId = getOrganizationId();
        String query = "SELECT feature FROM "+tablename+" WHERE organization_id = ? and \"DATE\" - current_date() > 8";
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(getUrl(), props);
        try {
            Date startDate = new Date(System.currentTimeMillis());
            initDateTableValues(tablename, tenantId, getSplits(tenantId), startDate);
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("F", rs.getString(1));
            assertFalse(rs.next());
        } finally {
            conn.close();
        }
    }

    @Test
    public void testCurrentTime() throws Exception {
        String tablename=generateUniqueName();
        String tenantId = getOrganizationId();
        String query = "SELECT feature FROM "+tablename+" WHERE organization_id = ? and \"DATE\" - current_time() > 8";
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection conn = DriverManager.getConnection(getUrl(), props);
        try {
            Date startDate = new Date(System.currentTimeMillis());
            initDateTableValues(tablename, tenantId, getSplits(tenantId), startDate);
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("F", rs.getString(1));
            assertFalse(rs.next());
        } finally {
            conn.close();
        }
    }


    @Test
    public void testTimestamp() throws Exception {
        String updateStmt = 
            "upsert into " + tableName +
            " (" +
            "    ORGANIZATION_ID, " +
            "    ENTITY_ID, " +
            "    A_TIMESTAMP) " +
            "VALUES (?, ?, ?)";
        // Override value that was set at creation time
        Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
        Connection upsertConn = DriverManager.getConnection(url, props);
        upsertConn.setAutoCommit(true); // Test auto commit
        PreparedStatement stmt = upsertConn.prepareStatement(updateStmt);
        stmt.setString(1, tenantId);
        stmt.setString(2, ROW4);
        Timestamp tsValue1 = new Timestamp(5000);
        byte[] ts1 = PTimestamp.INSTANCE.toBytes(tsValue1);
        stmt.setTimestamp(3, tsValue1);
        stmt.execute();
        
        Connection conn1 = DriverManager.getConnection(url, props);
        TestUtil.analyzeTable(conn1, tableName);
        conn1.close();
        
        updateStmt = 
            "upsert into " + tableName +
            " (" +
            "    ORGANIZATION_ID, " +
            "    ENTITY_ID, " +
            "    A_TIMESTAMP," +
            "    A_TIME) " +
            "VALUES (?, ?, ?, ?)";
        stmt = upsertConn.prepareStatement(updateStmt);
        stmt.setString(1, tenantId);
        stmt.setString(2, ROW5);
        Timestamp tsValue2 = new Timestamp(5000);
        tsValue2.setNanos(200);
        byte[] ts2 = PTimestamp.INSTANCE.toBytes(tsValue2);
        stmt.setTimestamp(3, tsValue2);
        stmt.setTime(4, new Time(tsValue2.getTime()));
        stmt.execute();
        upsertConn.close();
        
        assertTrue(TestUtil.compare(CompareOp.GREATER, new ImmutableBytesWritable(ts2), new ImmutableBytesWritable(ts1)));
        assertFalse(TestUtil.compare(CompareOp.GREATER, new ImmutableBytesWritable(ts1), new ImmutableBytesWritable(ts1)));

        String query = "SELECT entity_id, a_timestamp, a_time FROM " + tableName + " WHERE organization_id=? and a_timestamp > ?";
        Connection conn = DriverManager.getConnection(url, props);
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            statement.setTimestamp(2, new Timestamp(5000));
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW5);
            assertEquals(rs.getTimestamp("A_TIMESTAMP"), tsValue2);
            assertEquals(rs.getTime("A_TIME"), new Time(tsValue2.getTime()));
            assertFalse(rs.next());
        } finally {
            conn.close();
        }
    }

    @Test
    public void testDateFormatTimeZone()throws Exception {
        String[] timeZoneIDs = {DateUtil.DEFAULT_TIME_ZONE_ID, "Asia/Yerevan", "Australia/Adelaide", "Asia/Tokyo"};
        for (String timeZoneID : timeZoneIDs) {
            testDateFormatTimeZone(timeZoneID);
        }
    }

    public void testDateFormatTimeZone(String timeZoneId) throws Exception {
        Properties props = new Properties();
        props.setProperty("phoenix.query.dateFormatTimeZone", timeZoneId);
        Connection conn1 = DriverManager.getConnection(getUrl(), props);

        String tableName = generateUniqueName();
        String ddl = "CREATE TABLE IF NOT EXISTS " + tableName +
                " (k1 INTEGER PRIMARY KEY," +
                " v_date DATE," +
                " v_time TIME," +
                " v_timestamp TIMESTAMP)";
        try {
            conn1.createStatement().execute(ddl);

            PhoenixConnection pConn = conn1.unwrap(PhoenixConnection.class);
            verifyTimeZoneIDWithConn(pConn, PDate.INSTANCE, timeZoneId);
            verifyTimeZoneIDWithConn(pConn, PTime.INSTANCE, timeZoneId);
            verifyTimeZoneIDWithConn(pConn, PTimestamp.INSTANCE, timeZoneId);

            Calendar cal = Calendar.getInstance(TimeZone.getTimeZone(timeZoneId));
            cal.setTime(date);
            String dateStr = DateUtil.getDateFormatter(DateUtil.DEFAULT_MS_DATE_FORMAT).format(date);

            String dml = "UPSERT INTO " + tableName + " VALUES (" +
                    "1," +
                    "'" + dateStr + "'," +
                    "'" + dateStr + "'," +
                    "'" + dateStr + "'" +
                    ")";
            conn1.createStatement().execute(dml);
            conn1.commit();

            PhoenixStatement stmt = conn1.createStatement().unwrap(PhoenixStatement.class);
            ResultSet rs = stmt.executeQuery("SELECT v_date, v_time, v_timestamp FROM " + tableName);

            assertTrue(rs.next());
            assertEquals(rs.getDate(1).toString(), new Date(cal.getTimeInMillis()).toString());
            assertEquals(rs.getTime(2).toString(), new Time(cal.getTimeInMillis()).toString());
            assertEquals(rs.getTimestamp(3).getTime(), cal.getTimeInMillis());
            assertFalse(rs.next());

            StatementContext stmtContext = stmt.getQueryPlan().getContext();
            verifyTimeZoneIDWithFormatter(stmtContext.getDateFormatter(), timeZoneId);
            verifyTimeZoneIDWithFormatter(stmtContext.getTimeFormatter(), timeZoneId);
            verifyTimeZoneIDWithFormatter(stmtContext.getTimestampFormatter(), timeZoneId);

            stmt.close();
        } finally {
            conn1.close();
        }
    }

    private void verifyTimeZoneIDWithConn(PhoenixConnection conn, PDataType dataType, String timeZoneId) {
        Format formatter = conn.getFormatter(dataType);
        verifyTimeZoneIDWithFormatter(formatter, timeZoneId);
    }

    private void verifyTimeZoneIDWithFormatter(Format formatter, String timeZoneId) {
        assertTrue(formatter instanceof FastDateFormat);
        assertEquals(((FastDateFormat)formatter).getTimeZone().getID(), timeZoneId);
    }
}