/*******************************************************************************
 * Copyright (c) 2013, Salesforce.com, Inc.
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 *     Redistributions of source code must retain the above copyright notice,
 *     this list of conditions and the following disclaimer.
 *     Redistributions in binary form must reproduce the above copyright notice,
 *     this list of conditions and the following disclaimer in the documentation
 *     and/or other materials provided with the distribution.
 *     Neither the name of Salesforce.com nor the names of its contributors may
 *     be used to endorse or promote products derived from this software without
 *     specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
 * ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
 * DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE
 * FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
 * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
 * SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
 * CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
 * OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
 * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 ******************************************************************************/
/**********************************
 *
 * Later modifications to test Trafodion instead of Phoenix were granted to ASF.
 *
 * 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 test.java.org.trafodion.phoenix.end2end;

import static org.junit.Assert.*;
import org.junit.*;
import java.math.*;
import java.sql.*;
import java.sql.Date;
import java.util.*;
import java.text.*;

public class VariableLengthPKTest extends BaseTest {

    @BeforeClass
    public static void doTestSuiteSetup() throws Exception {
        /* List all of the object names being used in this entire class.
         * The objects are dropped with errors ignored, so it is OK if the
         * object does not exist for a particular test.
         */
        objDropList = new ArrayList<String>(
            Arrays.asList("table " + PTSDB_NAME, "table " + PTSDB2_NAME, "table VarcharKeyTest", "table BTABLE", "table substr_test"));
        doBaseTestSuiteSetup();
    }
    /* @AfterClass, @Before, @After are defined in BaseTest */


// TRAF private static Format format = DateUtil.getDateParser(DateUtil.DEFAULT_DATE_FORMAT);
    private DateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
    private final String DS1 = "1970-01-01 00:58:00";
    private final Date D1 = toDate(DS1);
    private final Timestamp T1 = toTimestamp(DS1);

    private Date toDate(String dateString) {
        try {
            // TRAF return (Date)format.parseObject(dateString);
            return new Date(format.parse(dateString).getTime());
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

   private Timestamp toTimestamp(String dateString) {
        try {
            return new Timestamp(format.parse(dateString).getTime());
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }


    private void initGroupByRowKeyColumns() throws Exception {
        createTestTable(PTSDB_NAME);

        // Insert all rows at ts
        PreparedStatement stmt = null;
        if (tgtPH()) stmt = conn.prepareStatement(
                "upsert into " +
                "PTSDB(" +
                "    INST, " +
                "    HOST," +
                "    DATE)" +
                "VALUES (?, ?, CURRENT_DATE())");
        else if (tgtTR()) stmt = conn.prepareStatement(
                "upsert into " +
                "PTSDB(" +
                "    INST, " +
                "    HOST1," +
                "    DATE1)" +
                "VALUES (?, ?, CURRENT_TIMESTAMP)");
        else if (tgtSQ()) stmt = conn.prepareStatement(
                "insert into " +
                "PTSDB(" +
                "    INST, " +
                "    HOST1," +
                "    DATE1)" +
                "VALUES (?, ?, CURRENT_TIMESTAMP)");
        stmt.setString(1, "ab");
        stmt.setString(2, "a");
        stmt.execute();
        stmt.setString(1, "ac");
        stmt.setString(2, "b");
        stmt.execute();
        stmt.setString(1, "ad");
        stmt.setString(2, "a");
        stmt.execute();
    }

    private void initTableValues() throws Exception {
        createTestTable(PTSDB_NAME);

        // Insert all rows at ts
        conn.setAutoCommit(true);
        PreparedStatement stmt = null;
        if (tgtPH()) stmt = conn.prepareStatement(
                "upsert into " +
                "PTSDB(" +
                "    INST, " +
                "    HOST," +
                "    DATE," +
                "    VAL)" +
                "VALUES (?, ?, ?, ?)");
        else if (tgtTR()) stmt = conn.prepareStatement(
                "upsert into " +
                "PTSDB(" +
                "    INST, " +
                "    HOST1," +
                "    DATE1," +
                "    VAL)" +
                "VALUES (?, ?, ?, ?)");
        else if (tgtSQ()) stmt = conn.prepareStatement(
                "insert into " +
                "PTSDB(" +
                "    INST, " +
                "    HOST1," +
                "    DATE1," +
                "    VAL)" +
                "VALUES (?, ?, ?, ?)");
        stmt.setString(1, "abc");
        stmt.setString(2, "abc-def-ghi");
        if (tgtPH()) stmt.setDate(3, new Date(System.currentTimeMillis()));
        else if (tgtSQ()||tgtTR()) stmt.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
        stmt.setBigDecimal(4, new BigDecimal(.5));
        stmt.execute();

        createTestTable(BTABLE_NAME);
        if (tgtPH()||tgtSQ())
            conn.setAutoCommit(false);
        else if (tgtTR())
            conn.setAutoCommit(true);

        // Insert all rows at ts
        if (tgtPH()) stmt = conn.prepareStatement(
                "upsert into " +
                "BTABLE(" +
                "    A_STRING, " +
                "    A_ID," +
                "    B_STRING," +
                "    A_INTEGER," +
                "    B_INTEGER," +
                "    C_INTEGER," +
                "    D_STRING," +
                "    E_STRING)" +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
        else if (tgtTR()) stmt = conn.prepareStatement(
                "upsert into " +
                "BTABLE(" +
                "    A_STRING, " +
                "    A_ID," +
                "    B_STRING," +
                "    A_INTEGER," +
                "    B_INTEGER," +
                "    C_INTEGER," +
                "    D_STRING," +
                "    E_STRING," +
                "    C_STRING)" + // TRAF: C_STRING has been changed to not null and no default for it to be a primary key.
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, '')");
        else if (tgtSQ()) stmt = conn.prepareStatement(
                "insert into " +
                "BTABLE(" +
                "    A_STRING, " +
                "    A_ID," +
                "    B_STRING," +
                "    A_INTEGER," +
                "    B_INTEGER," +
                "    C_INTEGER," +
                "    D_STRING," +
                "    E_STRING," +
                "    C_STRING)" + // TRAF: C_STRING has been changed to not null and no default for it to be a primary key.
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, '')");
        stmt.setString(1, "abc");
        stmt.setString(2, "111");
        stmt.setString(3, "x");
        stmt.setInt(4, 1);
        stmt.setInt(5, 10);
        stmt.setInt(6, 1000);
        stmt.setString(7, null);
        stmt.setString(8, "0123456789");
        stmt.execute();

        stmt.setString(1, "abcd");
        stmt.setString(2, "222");
        stmt.setString(3, "xy");
        stmt.setInt(4, 2);
        stmt.setNull(5, Types.INTEGER);
        stmt.setNull(6, Types.INTEGER);
        stmt.execute();

        stmt.setString(3, "xyz");
        stmt.setInt(4, 3);
        stmt.setInt(5, 10);
        stmt.setInt(6, 1000);
        stmt.setString(7, "efg");
        stmt.execute();

        stmt.setString(3, "xyzz");
        stmt.setInt(4, 4);
        stmt.setInt(5, 40);
        stmt.setNull(6, Types.INTEGER);
        stmt.setString(7, null);
        stmt.execute();

        String ddl = null;
        if (tgtPH()) ddl = "create table VarcharKeyTest" +
            "   (pk varchar not null primary key)";
        else if (tgtSQ()||tgtTR()) ddl = "create table VarcharKeyTest" +
            "   (pk varchar(128) not null primary key)";
        conn.createStatement().execute(ddl);
        if (tgtPH()||tgtTR()) stmt = conn.prepareStatement(
                "upsert into " +
                "VarcharKeyTest(pk) " +
                "VALUES (?)");
        else if (tgtSQ()) stmt = conn.prepareStatement(
                "insert into " +
                "VarcharKeyTest(pk) " +
                "VALUES (?)");
        stmt.setString(1, "   def");
        stmt.execute();
        stmt.setString(1, "jkl   ");
        stmt.execute();
        stmt.setString(1, "   ghi   ");
        stmt.execute();

        if (tgtPH()||tgtSQ())
            conn.commit();
    }

    @Test
    public void testSingleColumnScanKey() throws Exception {
        printTestDescription();

        String query = "SELECT A_STRING,substr(a_id,1,1),B_STRING,A_INTEGER,B_INTEGER FROM BTABLE WHERE A_STRING=?";
        try {
            initTableValues();
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, "abc");
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("abc", rs.getString(1));
            assertEquals("1", rs.getString(2));
            assertEquals("x", rs.getString(3));
            assertEquals(1, rs.getInt(4));
            assertEquals(10, rs.getInt(5));
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testSingleColumnGroupBy() throws Exception {
        printTestDescription();

        String query = "SELECT INST FROM PTSDB GROUP BY INST";
        try {
            initTableValues();
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("abc", rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testNonfirstColumnGroupBy() throws Exception {
        printTestDescription();

        String query = null;
        if (tgtPH()) query = "SELECT HOST FROM PTSDB WHERE INST='abc' GROUP BY HOST";
        else if (tgtSQ()||tgtTR()) query = "SELECT HOST1 FROM PTSDB WHERE INST='abc' GROUP BY HOST1";
        try {
            initTableValues();
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("abc-def-ghi", rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testGroupByRowKeyColumns() throws Exception {
        printTestDescription();

        String query = null;
        if (tgtPH()) query = "SELECT SUBSTR(INST,1,1),HOST FROM PTSDB GROUP BY SUBSTR(INST,1,1),HOST";
        else if (tgtSQ()||tgtTR()) query = "SELECT SUBSTR(INST,1,1),HOST1 FROM PTSDB GROUP BY SUBSTR(INST,1,1),HOST1 order by 1,2";
        try {
            initGroupByRowKeyColumns();
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("a", rs.getString(1));
            assertEquals("a", rs.getString(2));
            assertTrue(rs.next());
            assertEquals("a", rs.getString(1));
            assertEquals("b", rs.getString(2));
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testSkipScan() throws Exception {
        printTestDescription();

        String query = null;
        if (tgtPH()) query = "SELECT HOST FROM PTSDB WHERE INST='abc' AND DATE>=TO_DATE('1970-01-01 00:00:00') AND DATE <TO_DATE('2115-01-01 00:00:00')";
        else if (tgtSQ()||tgtTR()) query = "SELECT HOST1 FROM PTSDB WHERE INST='abc' AND DATE1>=TIMESTAMP '1970-01-01 00:00:00' AND DATE1 <TIMESTAMP '2115-01-01 00:00:00'";

        try {
            initTableValues();
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("abc-def-ghi", rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testSkipMax() throws Exception {
        printTestDescription();

        String query = null;
        if (tgtPH()) query = "SELECT MAX(INST),MAX(DATE) FROM PTSDB WHERE INST='abc' AND DATE>=TO_DATE('1970-01-01 00:00:00') AND DATE <TO_DATE('2171-01-01 00:00:00')";
        else if (tgtSQ()||tgtTR()) query = "SELECT MAX(INST),MAX(DATE1) FROM PTSDB WHERE INST='abc' AND DATE1>=TIMESTAMP '1970-01-01 00:00:00' AND DATE1 <TIMESTAMP '2171-01-01 00:00:00'";
        try {
            initTableValues();
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("abc", rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testSkipMaxWithLimit() throws Exception {
        printTestDescription();

        String query = null;
        if (tgtPH()) query = "SELECT MAX(INST),MAX(DATE) FROM PTSDB WHERE INST='abc' AND DATE>=TO_DATE('1970-01-01 00:00:00') AND DATE <TO_DATE('2171-01-01 00:00:00') LIMIT 2";
        else if (tgtTR()) query = "SELECT MAX(INST),MAX(DATE1) FROM PTSDB WHERE INST='abc' AND DATE1>=TIMESTAMP '1970-01-01 00:00:00' AND DATE1 <TIMESTAMP '2171-01-01 00:00:00' LIMIT 2";
        else if (tgtSQ()) query = "SELECT [FIRST 2] MAX(INST),MAX(DATE1) FROM PTSDB WHERE INST='abc' AND DATE1>=TIMESTAMP '1970-01-01 00:00:00' AND DATE1 <TIMESTAMP '2171-01-01 00:00:00'";
        try {
            initTableValues();
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("abc", rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testSingleColumnKeyFilter() throws Exception {
        printTestDescription();

        // Requires not null column to be projected, since the only one projected in the query is
        // nullable and will cause the no key value to be returned if it is the only one projected.
        String query = "SELECT A_STRING,substr(a_id,1,1),B_STRING,A_INTEGER,B_INTEGER FROM BTABLE WHERE B_STRING=?";
        try {
            initTableValues();
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, "xy");
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("abcd", rs.getString(1));
            assertEquals("2", rs.getString(2));
            assertEquals("xy", rs.getString(3));
            assertEquals(2, rs.getInt(4));
            assertEquals(0, rs.getInt(5));
            assertTrue(rs.wasNull());
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testMultiColumnEqScanKey() throws Exception {
        printTestDescription();

        // TODO: add compile test to confirm start/stop scan key
        String query = "SELECT A_STRING,substr(a_id,1,1),B_STRING,A_INTEGER,B_INTEGER FROM BTABLE WHERE A_STRING=? AND A_ID=? AND B_STRING=?";
        try {
            initTableValues();
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, "abcd");
            statement.setString(2, "222");
            statement.setString(3, "xy");
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("abcd", rs.getString(1));
            assertEquals("2", rs.getString(2));
            assertEquals("xy", rs.getString(3));
            assertEquals(2, rs.getInt(4));
            assertEquals(0, rs.getInt(5));
            assertTrue(rs.wasNull());
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testMultiColumnGTScanKey() throws Exception {
        printTestDescription();

        // TODO: add compile test to confirm start/stop scan key
        String query = null;
        if (tgtPH()) query = "SELECT A_STRING,substr(a_id,1,1),B_STRING,A_INTEGER,B_INTEGER FROM BTABLE WHERE A_STRING=? AND A_ID=? AND B_STRING>?";
        else if (tgtSQ()||tgtTR()) query = "SELECT A_STRING,substr(a_id,1,1),B_STRING,A_INTEGER,B_INTEGER FROM BTABLE WHERE A_STRING=? AND A_ID=? AND B_STRING>? order by 3 ";
        try {
            initTableValues();
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, "abcd");
            statement.setString(2, "222");
            statement.setString(3, "xy");
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("abcd", rs.getString(1));
            assertEquals("2", rs.getString(2));
            assertEquals("xyz", rs.getString(3));
            assertEquals(3, rs.getInt(4));
            assertEquals(10, rs.getInt(5));
            assertTrue(rs.next());
            assertEquals("abcd", rs.getString(1));
            assertEquals("2", rs.getString(2));
            assertEquals("xyzz", rs.getString(3));
            assertEquals(4, rs.getInt(4));
            assertEquals(40, rs.getInt(5));
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testMultiColumnGTKeyFilter() throws Exception {
        printTestDescription();

        // TODO: add compile test to confirm start/stop scan key
        String query = "SELECT A_STRING,substr(a_id,1,1),B_STRING,A_INTEGER,B_INTEGER FROM BTABLE WHERE A_STRING>? AND A_INTEGER>=?";
        try {
            initTableValues();
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, "abc");
            statement.setInt(2, 4);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("abcd", rs.getString(1));
            assertEquals("2", rs.getString(2));
            assertEquals("xyzz", rs.getString(3));
            assertEquals(4, rs.getInt(4));
            assertEquals(40, rs.getInt(5));
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testNullValueEqualityScan() throws Exception {
        printTestDescription();

        createTestTable(PTSDB_NAME);

        // Insert all rows at ts
        conn.setAutoCommit(true);
        PreparedStatement stmt = null;
        if (tgtPH()||tgtTR()) stmt = conn.prepareStatement("upsert into PTSDB VALUES ('', '', ?, 0.5)");
        else if (tgtSQ()) stmt = conn.prepareStatement("insert into PTSDB VALUES ('', '', ?, 0.5)");
        if (tgtPH()) stmt.setDate(1, D1);
        else if (tgtSQ()||tgtTR()) stmt.setTimestamp(1, T1);
        stmt.execute();

        // Comparisons against null are always false.
        String query = null;
        if (tgtPH()) query = "SELECT HOST,DATE FROM PTSDB WHERE HOST='' AND INST=''";
        else if (tgtSQ()||tgtTR()) query = "SELECT HOST1,DATE1 FROM PTSDB WHERE HOST1='' AND INST=''"; 
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            if (tgtPH()) assertFalse(rs.next());
            else if (tgtSQ()||tgtTR()) assertTrue(rs.next()); // TRAF, the insert did put in '' and '' for HOST1 and INST
        } finally {
        }
    }

    @Test
    public void testVarLengthPKColScan() throws Exception {
        printTestDescription();

        createTestTable(PTSDB_NAME);

        conn.setAutoCommit(true);
        PreparedStatement stmt = null;
        if (tgtPH()||tgtTR()) stmt = conn.prepareStatement("upsert into PTSDB VALUES (?, 'y', ?, 0.5)");
        else if (tgtSQ()) stmt = conn.prepareStatement("insert into PTSDB VALUES (?, 'y', ?, 0.5)");
        stmt.setString(1, "x");
        if (tgtPH()) stmt.setDate(2, D1);
        else if (tgtSQ()||tgtTR()) stmt.setTimestamp(2, T1);
        stmt.execute();
        stmt.setString(1, "xy");
        stmt.execute();

        String query = null;
        if (tgtPH()) query = "SELECT HOST,DATE FROM PTSDB WHERE INST='x' AND HOST='y'";
        else if (tgtSQ()||tgtTR()) query = "SELECT HOST1,DATE1 FROM PTSDB WHERE INST='x' AND HOST1='y'";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            if (tgtPH()) assertEquals(D1, rs.getDate(2));
            else if (tgtSQ()||tgtTR()) assertEquals(T1, rs.getTimestamp(2));
        } finally {
        }
    }

    @Test
    public void testEscapedQuoteScan() throws Exception {
        printTestDescription();

        createTestTable(PTSDB_NAME);
        conn.setAutoCommit(true);
        PreparedStatement stmt = null;
        if (tgtPH()||tgtTR()) stmt = conn.prepareStatement("upsert into PTSDB VALUES (?, 'y', ?, 0.5)");
        else if (tgtSQ()) stmt = conn.prepareStatement("insert into PTSDB VALUES (?, 'y', ?, 0.5)");
        stmt.setString(1, "x'y");
        if (tgtPH()) stmt.setDate(2, D1);
        else if (tgtSQ()||tgtTR()) stmt.setTimestamp(2, T1);
        stmt.execute();
        stmt.setString(1, "x");
        stmt.execute();

        String query1 = null;
        if (tgtPH()) query1 = "SELECT INST,DATE FROM PTSDB WHERE INST='x''y'";
        else if (tgtSQ()||tgtTR()) query1 = "SELECT INST,DATE1 FROM PTSDB WHERE INST='x''y'";
        String query2 = null;
        if (tgtPH()) query2 = "SELECT INST,DATE FROM PTSDB WHERE INST='x\\\'y'";
        else if (tgtSQ()||tgtTR()) query2 = "SELECT INST,DATE1 FROM PTSDB WHERE INST='x''y'";
        try {
            PreparedStatement statement = conn.prepareStatement(query1);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("x'y", rs.getString(1));
            if (tgtPH()) assertEquals(D1, rs.getDate(2));
            else if (tgtSQ()||tgtTR()) assertEquals(T1, rs.getTimestamp(2));
            assertFalse(rs.next());

            statement = conn.prepareStatement(query2);
            rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("x'y", rs.getString(1));
            if (tgtPH()) assertEquals(D1, rs.getDate(2));
            else if (tgtSQ()||tgtTR()) assertEquals(T1, rs.getTimestamp(2));
            assertFalse(rs.next());
        } finally {
        }
    }

    private void initPtsdbTableValues() throws Exception {
        createTestTable(PTSDB_NAME);

        conn.setAutoCommit(true);
        PreparedStatement stmt = null;
        if (tgtPH()||tgtTR()) stmt = conn.prepareStatement("upsert into PTSDB VALUES ('x', 'y', ?, 0.5)");
        else if (tgtSQ()) stmt = conn.prepareStatement("insert into PTSDB VALUES ('x', 'y', ?, 0.5)");
        if (tgtPH()) stmt.setDate(1, D1);
        else if (tgtSQ()||tgtTR()) stmt.setTimestamp(1, T1);
        stmt.execute();
    }

    @Test
    public void testToStringOnDate() throws Exception {
        printTestDescription();

        initPtsdbTableValues();

        String query = null;
        if (tgtPH()) query = "SELECT HOST,DATE FROM PTSDB WHERE INST='x' AND HOST='y'";
        else if (tgtSQ()||tgtTR()) query = "SELECT HOST1,CAST(DATE1 as TIMESTAMP(1)) FROM PTSDB WHERE INST='x' AND HOST1='y'";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            if (tgtPH()) assertEquals(D1.toString(), rs.getString(2));
            else if (tgtSQ()||tgtTR()) assertEquals(T1.toString(), rs.getString(2));
            assertFalse(rs.next());
        } finally {
        }
    }

    private void initPtsdbTableValues2(Date d) throws Exception {
        createTestTable(PTSDB2_NAME);

        conn.setAutoCommit(true);
        PreparedStatement stmt = null;
        if (tgtPH()) stmt = conn.prepareStatement("upsert into "+PTSDB2_NAME+"(inst,date,val2) VALUES (?, ?, ?)");
        else if (tgtTR()) stmt = conn.prepareStatement("upsert into "+PTSDB2_NAME+"(inst,date1,val2) VALUES (?, ?, ?)");
        else if (tgtSQ()) stmt = conn.prepareStatement("insert into "+PTSDB2_NAME+"(inst,date1,val2) VALUES (?, ?, ?)");
        stmt.setString(1, "a");
        stmt.setDate(2, d);
        stmt.setDouble(3, 101.3);
        stmt.execute();
        stmt.setString(1, "a");
        stmt.setDate(2, new Date(d.getTime() + 1 * MILLIS_IN_DAY));
        stmt.setDouble(3, 99.7);
        stmt.execute();
        stmt.setString(1, "a");
        stmt.setDate(2, new Date(d.getTime() - 1 * MILLIS_IN_DAY));
        stmt.setDouble(3, 105.3);
        stmt.execute();
        stmt.setString(1, "b");
        stmt.setDate(2, d);
        stmt.setDouble(3, 88.5);
        stmt.execute();
        stmt.setString(1, "b");
        stmt.setDate(2, new Date(d.getTime() + 1 * MILLIS_IN_DAY));
        stmt.setDouble(3, 89.7);
        stmt.execute();
        stmt.setString(1, "b");
        stmt.setDate(2, new Date(d.getTime() - 1 * MILLIS_IN_DAY));
        stmt.setDouble(3, 94.9);
        stmt.execute();
    }

    // TRAF: a new override function for Timestamp
    private void initPtsdbTableValues2(Timestamp d) throws Exception {
        createTestTable(PTSDB2_NAME);

        conn.setAutoCommit(true);
        PreparedStatement stmt = null;
        if (tgtPH()) stmt = conn.prepareStatement("upsert into "+PTSDB2_NAME+"(inst,date,val2) VALUES (?, ?, ?)");
        else if (tgtTR()) stmt = conn.prepareStatement("upsert into "+PTSDB2_NAME+"(inst,date1,val2) VALUES (?, ?, ?)");
        else if (tgtSQ()) stmt = conn.prepareStatement("insert into "+PTSDB2_NAME+"(inst,date1,val2) VALUES (?, ?, ?)");
        stmt.setString(1, "a");
        stmt.setTimestamp(2, d);
        stmt.setDouble(3, 101.3);
        stmt.execute();
        stmt.setString(1, "a");
        stmt.setTimestamp(2, new Timestamp(d.getTime() + 1 * MILLIS_IN_DAY));
        stmt.setDouble(3, 99.7);
        stmt.execute();
        stmt.setString(1, "a");
        stmt.setTimestamp(2, new Timestamp(d.getTime() - 1 * MILLIS_IN_DAY));
        stmt.setDouble(3, 105.3);
        stmt.execute();
        stmt.setString(1, "b");
        stmt.setTimestamp(2, d);
        stmt.setDouble(3, 88.5);
        stmt.execute();
        stmt.setString(1, "b");
        stmt.setTimestamp(2, new Timestamp(d.getTime() + 1 * MILLIS_IN_DAY));
        stmt.setDouble(3, 89.7);
        stmt.execute();
        stmt.setString(1, "b");
        stmt.setTimestamp(2, new Timestamp(d.getTime() - 1 * MILLIS_IN_DAY));
        stmt.setDouble(3, 94.9);
        stmt.execute();
    }


    @Test
    public void testRoundOnDate() throws Exception {
        printTestDescription();

        if (tgtPH()) {
            Date date = new Date(System.currentTimeMillis());
            initPtsdbTableValues2(date);
        } else if (tgtSQ()||tgtTR()) {
            Timestamp tsp = new Timestamp(System.currentTimeMillis());
            initPtsdbTableValues2(tsp);
        }

        String query = null;
        if (tgtPH()) query = "SELECT MAX(val2)"
        + " FROM "+PTSDB2_NAME
        + " WHERE inst='a'"
        + " GROUP BY ROUND(date,'day',1)"
        + " ORDER BY MAX(val2)"; // disambiguate row order
        // TRAF: (1) ORDER BY does not take a function.  (2) GROUP BY only takes a function when the function is part of the SELECTed list.
        else if (tgtSQ()||tgtTR()) query = "SELECT MAX(val2), DATE_TRUNC('hour',DATE_ADD(date1,interval '30' minute))"
        + " FROM "+PTSDB2_NAME
        + " WHERE inst='a'"
        + " GROUP BY DATE_TRUNC('hour',DATE_ADD(date1,interval '30' minute)) "
        + " ORDER BY 1"; // disambiguate row order
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(99.7, rs.getDouble(1), 1e-6);
            assertTrue(rs.next());
            assertEquals(101.3, rs.getDouble(1), 1e-6);
            assertTrue(rs.next());
            assertEquals(105.3, rs.getDouble(1), 1e-6);
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testOrderBy() throws Exception {
        printTestDescription();

        if (tgtPH()) {
            Date date = new Date(System.currentTimeMillis());
            initPtsdbTableValues2(date);
        } else if (tgtSQ()||tgtTR()) {
            Timestamp tsp = new Timestamp(System.currentTimeMillis());
            initPtsdbTableValues2(tsp);
        }

        String query = null;
        if (tgtPH()) query = "SELECT inst,MAX(val2),MIN(val2)"
        + " FROM "+PTSDB2_NAME
        + " GROUP BY inst,ROUND(date,'day',1)"
        + " ORDER BY inst,ROUND(date,'day',1)"
        ;
        // TRAF: (1) ORDER BY does not take a function.  (2) GROUP BY only takes a function when the function is part of the SELECTed list.
        else if (tgtSQ()||tgtTR()) query = "SELECT inst,MAX(val2),MIN(val2),DATE_TRUNC('hour',DATE_ADD(date1,interval '30' minute))"
        + " FROM "+PTSDB2_NAME
        + " GROUP BY inst,DATE_TRUNC('hour',DATE_ADD(date1,interval '30' minute))"
        + " ORDER BY 1, 2"
        ;
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            if (tgtPH()) {
                assertTrue(rs.next());
                assertEquals("a", rs.getString(1));
                assertEquals(105.3, rs.getDouble(2), 1e-6);
                assertEquals(105.3, rs.getDouble(3), 1e-6);
                assertTrue(rs.next());
                assertEquals("a", rs.getString(1));
                assertEquals(101.3, rs.getDouble(2), 1e-6);
                assertEquals(101.3, rs.getDouble(3), 1e-6);
                assertTrue(rs.next());
                assertEquals("a", rs.getString(1));
                assertEquals(99.7, rs.getDouble(2), 1e-6);
                assertEquals(99.7, rs.getDouble(3), 1e-6);
                assertTrue(rs.next());
                assertEquals("b", rs.getString(1));
                assertEquals(94.9, rs.getDouble(2), 1e-6);
                assertEquals(94.9, rs.getDouble(3), 1e-6);
                assertTrue(rs.next());
                assertEquals("b", rs.getString(1));
                assertEquals(88.5, rs.getDouble(2), 1e-6);
                assertEquals(88.5, rs.getDouble(3), 1e-6);
                assertTrue(rs.next());
                assertEquals("b", rs.getString(1));
                assertEquals(89.7, rs.getDouble(2), 1e-6);
                assertEquals(89.7, rs.getDouble(3), 1e-6);
                assertFalse(rs.next());
            } else if (tgtSQ() || tgtPH()) {
                assertTrue(rs.next());
                assertEquals("a", rs.getString(1));
                assertEquals(99.7, rs.getDouble(2), 1e-6);
                assertEquals(99.7, rs.getDouble(3), 1e-6);
                assertTrue(rs.next());
                assertEquals("a", rs.getString(1));
                assertEquals(101.3, rs.getDouble(2), 1e-6);
                assertEquals(101.3, rs.getDouble(3), 1e-6);
                assertTrue(rs.next());
                assertEquals("a", rs.getString(1));
                assertEquals(105.3, rs.getDouble(2), 1e-6);
                assertEquals(105.3, rs.getDouble(3), 1e-6);
                assertTrue(rs.next());
                assertEquals("b", rs.getString(1));
                assertEquals(88.5, rs.getDouble(2), 1e-6);
                assertEquals(88.5, rs.getDouble(3), 1e-6);
                assertTrue(rs.next());
                assertEquals("b", rs.getString(1));
                assertEquals(89.7, rs.getDouble(2), 1e-6);
                assertEquals(89.7, rs.getDouble(3), 1e-6);
                assertTrue(rs.next());
                assertEquals("b", rs.getString(1));
                assertEquals(94.9, rs.getDouble(2), 1e-6);
                assertEquals(94.9, rs.getDouble(3), 1e-6);
                assertFalse(rs.next());
            } 
        } finally {
        }
    }

    @Test
    public void testSelectCount() throws Exception {
        printTestDescription();

        if (tgtPH()) {
            Date date = new Date(System.currentTimeMillis());
            initPtsdbTableValues2(date);
        } else if (tgtSQ()||tgtTR()) {
            Timestamp tsp = new Timestamp(System.currentTimeMillis());
            initPtsdbTableValues2(tsp);
        }

        String query = "SELECT COUNT(*)"
        + " FROM "+PTSDB2_NAME
        + " WHERE inst='a'";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(3, rs.getInt(1));
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testBatchUpsert() throws Exception {
        printTestDescription();

        createTestTable(PTSDB2_NAME);
        Date d = null;
        Timestamp t = null;
        if (tgtPH()) d = new Date(1); 
        else if (tgtSQ()||tgtTR()) t = new Timestamp(1);

        String query = "SELECT SUM(val1),SUM(val2),SUM(val3) FROM "+PTSDB2_NAME;
        String sql1 = null;
        String upt1 = null;
        if (tgtPH()) sql1 = "UPSERT INTO "+PTSDB2_NAME+"(inst,date,val1) VALUES (?, ?, ?)";
        else if (tgtTR()) sql1 = "UPSERT INTO "+PTSDB2_NAME+"(inst,date1,val1) VALUES (?, ?, ?)";
        else if (tgtSQ()) { 
            sql1 = "INSERT INTO "+PTSDB2_NAME+"(inst,date1,val1) VALUES (?, ?, ?)";
            upt1 = "UPDATE "+PTSDB2_NAME+" SET val1 = ? WHERE (inst, date1) = (?, ?)";
        }

        String sql2 = null;
        if (tgtPH()) sql2 = "UPSERT INTO "+PTSDB2_NAME+"(inst,date,val2) VALUES (?, ?, ?)";
        else if (tgtTR()) sql2 = "UPSERT INTO "+PTSDB2_NAME+"(inst,date1,val2) VALUES (?, ?, ?)";
        else if (tgtSQ()) sql2 = "INSERT INTO "+PTSDB2_NAME+"(inst,date1,val2) VALUES (?, ?, ?)";

        String sql3 = null;
        if (tgtPH()) sql3 = "UPSERT INTO "+PTSDB2_NAME+"(inst,date,val3) VALUES (?, ?, ?)";
        else if (tgtTR()) sql3 = "UPSERT INTO "+PTSDB2_NAME+"(inst,date1,val3) VALUES (?, ?, ?)"; 
        else if (tgtSQ()) sql3 = "INSERT INTO "+PTSDB2_NAME+"(inst,date1,val3) VALUES (?, ?, ?)";

        conn.setAutoCommit(false);
        // conn.setAutoCommit(true);

        {
            // verify precondition: SUM(val{1,2,3}) are null
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertNull(rs.getBigDecimal(1));
            assertNull(rs.getBigDecimal(2));
            assertNull(rs.getBigDecimal(3));
            assertFalse(rs.next());
            statement.close();
        }

        {
            PreparedStatement s = conn.prepareStatement(sql1);
            s.setString(1, "a");
            if (tgtPH()) s.setDate(2, d);
            else if (tgtSQ()||tgtTR()) s.setTimestamp(2, t);
            s.setInt(3, 1);
            assertEquals(1, s.executeUpdate());
            s.close();
        }
        {
            PreparedStatement s = conn.prepareStatement(sql2);
            s.setString(1, "b");
            if (tgtPH()) s.setDate(2, d);
            else if (tgtSQ()||tgtTR()) s.setTimestamp(2, t);
            s.setInt(3, 1);
            assertEquals(1, s.executeUpdate());
            s.close();
        }
        {
            PreparedStatement s = conn.prepareStatement(sql3);
            s.setString(1, "c");
            if (tgtPH()) s.setDate(2, d);
            else if (tgtSQ()||tgtTR()) s.setTimestamp(2, t);
            s.setInt(3, 1);
            assertEquals(1, s.executeUpdate());
            s.close();
        }
        {
            PreparedStatement s = null;
            if (tgtPH()) {
                s = conn.prepareStatement(sql1);
                s.setString(1, "a");
                s.setDate(2, d);
                s.setInt(3, 5);
            } else if (tgtTR()) {
                s = conn.prepareStatement(sql1);
                s.setString(1, "a");
                s.setTimestamp(2, t);
                s.setInt(3, 5);
            } else if (tgtSQ()) {
                s = conn.prepareStatement(upt1);
                s.setInt(1, 5);
                s.setString(2, "a");
                s.setTimestamp(3, t);
            }
            assertEquals(1, s.executeUpdate());
            s.close();
        }
        {
            PreparedStatement s = null;
            if (tgtPH()) {
                s = conn.prepareStatement(sql1);
                s.setString(1, "b");
                s.setDate(2, d);
                s.setInt(3, 5);
            } else if (tgtTR()) {
                s = conn.prepareStatement(sql1);
                s.setString(1, "b");
                s.setTimestamp(2, t);
                s.setInt(3, 5);
            } else if (tgtSQ()) {
                s = conn.prepareStatement(upt1);
                s.setInt(1, 5);
                s.setString(2, "b");
                s.setTimestamp(3, t);
            }
            assertEquals(1, s.executeUpdate());
            s.close();
        }
        {
            PreparedStatement s = null;
            if (tgtPH()) {
                s = conn.prepareStatement(sql1);
                s.setString(1, "c");
                s.setDate(2, d);
                s.setInt(3, 5);
            } else if (tgtTR()) {
                s = conn.prepareStatement(sql1);
                s.setString(1, "c");
                s.setTimestamp(2, t);
                s.setInt(3, 5);
            } else if (tgtSQ()) {
                s = conn.prepareStatement(upt1);
                s.setInt(1, 5);
                s.setString(2, "c");
                s.setTimestamp(3, t);
            }
            assertEquals(1, s.executeUpdate());
            s.close();
        }
        conn.commit();
       
        // Query at a time after the upsert to confirm they took place
        {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(15, rs.getDouble(1), 1e-6);
            assertEquals(1, rs.getDouble(2), 1e-6);
            assertEquals(1, rs.getDouble(3), 1e-6);
            assertFalse(rs.next());
            statement.close();
        }
    }

    @Test
    public void testSelectStar() throws Exception {
        printTestDescription();

        initPtsdbTableValues();

        String query = null;
        if (tgtPH()) query = "SELECT * FROM PTSDB WHERE INST='x' AND HOST='y'";
        else if (tgtSQ()||tgtTR()) query = "SELECT * FROM PTSDB WHERE INST='x' AND HOST1='y'";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("x",rs.getString("inst"));
            if (tgtPH()) assertEquals("y",rs.getString("host"));
            else if (tgtSQ()||tgtTR()) assertEquals("y",rs.getString("host1"));
            if (tgtPH()) assertEquals(D1, rs.getDate("date"));
            else if (tgtSQ()||tgtTR()) assertEquals(T1, rs.getTimestamp("date1"));
            if (tgtPH()) assertEquals(BigDecimal.valueOf(0.5), rs.getBigDecimal("val"));
            else if (tgtSQ()||tgtTR()) assertEquals(BigDecimal.valueOf(0.5).setScale(10), rs.getBigDecimal("val"));
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test 
    public void testToCharOnDate() throws Exception {
        printTestDescription();

        initPtsdbTableValues();

        String query = null;
        if (tgtPH()) query = "SELECT HOST,TO_CHAR(DATE) FROM PTSDB WHERE INST='x' AND HOST='y'";
        else if (tgtSQ()||tgtTR()) query = "SELECT HOST1,RTRIM(CAST(CAST(DATE1 as TIMESTAMP(1)) as CHAR(128))) FROM PTSDB WHERE INST='x' AND HOST1='y'";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            if (tgtPH()) assertEquals(D1.toString(), rs.getString(2));
            else if (tgtSQ()||tgtTR()) assertEquals(T1.toString(), rs.getString(2));
        } finally {
        }
    }

    @Test
    public void testToCharWithFormatOnDate() throws Exception {
        printTestDescription();

        initPtsdbTableValues();

        String format = "HH:mm:ss";
        String query = null;
        if (tgtPH()) query = "SELECT HOST,TO_CHAR(DATE,'" + format + "') FROM PTSDB WHERE INST='x' AND HOST='y'";
        else if (tgtSQ()||tgtTR()) query = "SELECT HOST1,RTRIM(CAST(CAST(DATE1 as timestamp(1)) as CHAR(128))) FROM PTSDB WHERE INST='x' AND HOST1='y'";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            if (tgtPH()) assertEquals(D1.toString(), rs.getString(2));
            else if (tgtSQ()||tgtTR()) assertEquals(T1.toString(), rs.getString(2));
        } finally {
        }
    }

    @Test
    public void testToDateWithFormatOnDate() throws Exception {
        printTestDescription();

        initPtsdbTableValues();

        String format = "yyyy-MM-dd HH:mm:ss.S";
        String query = null;
        if (tgtPH()) query = "SELECT HOST,TO_CHAR(DATE,'" + format + "') FROM PTSDB WHERE INST='x' AND HOST='y' and DATE=TO_DATE(?,'" + format + "')";
        else if (tgtSQ()||tgtTR()) query = "SELECT HOST1,RTRIM(CAST(CAST(DATE1 as timestamp(1)) as CHAR(128))) FROM PTSDB WHERE INST='x' AND HOST1='y' and DATE1=?";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            if (tgtPH()) statement.setString(1, D1.toString());
            else if (tgtSQ()||tgtTR()) statement.setString(1, T1.toString());
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            if (tgtPH()) assertEquals(D1.toString(), rs.getString(2));
            else if (tgtSQ()||tgtTR()) assertEquals(T1.toString(), rs.getString(2));

        } finally {
        }
    }

    @Test
    public void testMissingPKColumn() throws Exception {
        printTestDescription();

        createTestTable(PTSDB_NAME);

        conn.setAutoCommit(true);
        Statement stmt = conn.createStatement();
        try {
            if (tgtPH()) stmt.execute("upsert into PTSDB(INST,HOST,VAL) VALUES ('abc', 'abc-def-ghi', 0.5)");
            else if (tgtTR()) stmt.execute("upsert into PTSDB(INST,HOST1,VAL,DATE1) VALUES ('abc', 'abc-def-ghi', 0.5, null)");
            else if (tgtSQ()) stmt.execute("insert into PTSDB(INST,HOST1,VAL,DATE1) VALUES ('abc', 'abc-def-ghi', 0.5, null)");
            fail();
        } catch (Exception e) {
            if (tgtPH()) assertTrue(e.getMessage().contains("may not be null"));
            else if (tgtSQ()||tgtTR()) assertTrue(e.getMessage().contains("*** ERROR[4122] NULL cannot be assigned to NOT NULL column"));
        } finally {
        }
    }

    @Test
    public void testNoKVColumn() throws Exception {
        printTestDescription();

        createTestTable(BTABLE_NAME);

        conn.setAutoCommit(true);
        PreparedStatement stmt = null;
        if (tgtPH()) stmt = conn.prepareStatement(
                "upsert into BTABLE VALUES (?, ?, ?, ?, ?)");
        else if (tgtTR()) stmt = conn.prepareStatement(
                "upsert into BTABLE (a_string, a_id, b_string, a_integer, c_string) VALUES (?, ?, ?, ?, ?)");
        else if (tgtSQ()) stmt = conn.prepareStatement(
                "insert into BTABLE (a_string, a_id, b_string, a_integer, c_string) VALUES (?, ?, ?, ?, ?)");
        stmt.setString(1, "abc");
        stmt.setString(2, "123");
        stmt.setString(3, "x");
        stmt.setInt(4, 1);
        stmt.setString(5, "ab");
        // Succeeds since we have an empty KV
        stmt.execute();
    }

    // Broken, since we don't know if insert vs update. @Test
    public void testMissingKVColumn() throws Exception {
        printTestDescription();

        createTestTable(BTABLE_NAME);

        conn.setAutoCommit(true);
        PreparedStatement stmt = conn.prepareStatement(
                "upsert into BTABLE VALUES (?, ?, ?, ?, ?, ?)");
        stmt.setString(1, "abc");
        stmt.setString(2, "123");
        stmt.setString(3, "x");
        stmt.setInt(4, 1);
        stmt.setString(5, "ab");
        stmt.setInt(6, 1);
        try {
            stmt.execute();
            fail();
        } catch (Exception e) {
            // Non nullable key value E_STRING has no value
            assertTrue(e.getMessage().contains("may not be null"));
        } finally {
        }
    }

    @Test
    public void testTooShortKVColumn() throws Exception {
        printTestDescription();

        createTestTable(BTABLE_NAME);

        conn.setAutoCommit(true);
        // Insert all rows at ts
        PreparedStatement stmt = null;
        if (tgtPH()||tgtTR()) stmt = conn.prepareStatement(
                "upsert into " +
                "BTABLE(" +
                "    A_STRING, " +
                "    A_ID," +
                "    B_STRING," +
                "    A_INTEGER," +
                "    C_STRING," +
                "    E_STRING)" +
                "VALUES (?, ?, ?, ?, ?, ?)");
        else if (tgtSQ()) stmt = conn.prepareStatement(
                "insert into " +
                "BTABLE(" +
                "    A_STRING, " +
                "    A_ID," +
                "    B_STRING," +
                "    A_INTEGER," +
                "    C_STRING," +
                "    E_STRING)" +
                "VALUES (?, ?, ?, ?, ?, ?)");
        stmt.setString(1, "abc");
        stmt.setString(2, "123");
        stmt.setString(3, "x");
        stmt.setInt(4, 1);
        stmt.setString(5, "ab");
        stmt.setString(6, "01234");

        try {
            stmt.execute();
        } catch (Exception e) {
            fail("Constraint voilation Exception should not be thrown, the characters have to be padded");
        } finally {
        }
    }

    @Test
    public void testTooShortPKColumn() throws Exception {
        printTestDescription();

        createTestTable(BTABLE_NAME);

        conn.setAutoCommit(true);
        // Insert all rows at ts
        PreparedStatement stmt = null;
        if (tgtPH()||tgtTR()) stmt = conn.prepareStatement(
                "upsert into " +
                "BTABLE(" +
                "    A_STRING, " +
                "    A_ID," +
                "    B_STRING," +
                "    A_INTEGER," +
                "    C_STRING," +
                "    E_STRING)" +
                "VALUES (?, ?, ?, ?, ?, ?)");
        else if (tgtSQ()) stmt = conn.prepareStatement(
                "insert into " +
                "BTABLE(" +
                "    A_STRING, " +
                "    A_ID," +
                "    B_STRING," +
                "    A_INTEGER," +
                "    C_STRING," +
                "    E_STRING)" +
                "VALUES (?, ?, ?, ?, ?, ?)");
        stmt.setString(1, "abc");
        stmt.setString(2, "12");
        stmt.setString(3, "x");
        stmt.setInt(4, 1);
        stmt.setString(5, "ab");
        stmt.setString(6, "0123456789");

        try {
            stmt.execute();
        } catch (Exception e) {
            fail("Constraint voilation Exception should not be thrown, the characters have to be padded");
        } finally {
        }
    }

    @Test
    public void testTooLongPKColumn() throws Exception {
        printTestDescription();

        createTestTable(BTABLE_NAME);

        conn.setAutoCommit(true);
        // Insert all rows at ts
        PreparedStatement stmt = null;
        if (tgtPH()||tgtTR()) stmt = conn.prepareStatement(
                "upsert into " +
                "BTABLE(" +
                "    A_STRING, " +
                "    A_ID," +
                "    B_STRING," +
                "    A_INTEGER," +
                "    C_STRING," +
                "    E_STRING)" +
                "VALUES (?, ?, ?, ?, ?, ?)");
        else if (tgtSQ()) stmt = conn.prepareStatement(
                "insert into " +
                "BTABLE(" +
                "    A_STRING, " +
                "    A_ID," +
                "    B_STRING," +
                "    A_INTEGER," +
                "    C_STRING," +
                "    E_STRING)" +
                "VALUES (?, ?, ?, ?, ?, ?)");
        stmt.setString(1, "abc");
        stmt.setString(2, "123");
        stmt.setString(3, "x");
        stmt.setInt(4, 1);
        stmt.setString(5, "abc");
        stmt.setString(6, "0123456789");

        try {
            stmt.execute();
            fail();
        } catch (Exception e) {
            if (tgtPH()) assertTrue(e.getMessage().contains(" may not exceed 2 bytes"));
            // This error message is different between T2 and T4.
            // Only make sure that we get an exception now.
            // else if (tgtSQ()||tgtTR()) assertTrue(e.getMessage().contains("VARCHAR data longer than column length"));
        } finally {
        }
    }

    @Test
    public void testTooLongKVColumn() throws Exception {
        printTestDescription();

        createTestTable(BTABLE_NAME);

        conn.setAutoCommit(true);
        // Insert all rows at ts
        PreparedStatement stmt = null;
        if (tgtPH()||tgtTR()) stmt = conn.prepareStatement(
                "upsert into " +
                "BTABLE(" +
                "    A_STRING, " +
                "    A_ID," +
                "    B_STRING," +
                "    A_INTEGER," +
                "    C_STRING," +
                "    D_STRING," +
                "    E_STRING)" +
                "VALUES (?, ?, ?, ?, ?, ?, ?)");
        else if (tgtSQ()) stmt = conn.prepareStatement(
                "insert into " +
                "BTABLE(" +
                "    A_STRING, " +
                "    A_ID," +
                "    B_STRING," +
                "    A_INTEGER," +
                "    C_STRING," +
                "    D_STRING," +
                "    E_STRING)" +
                "VALUES (?, ?, ?, ?, ?, ?, ?)");
        stmt.setString(1, "abc");
        stmt.setString(2, "123");
        stmt.setString(3, "x");
        stmt.setInt(4, 1);
        stmt.setString(5, "ab");
        stmt.setString(6,"abcd");
        stmt.setString(7, "0123456789");

        try {
            stmt.execute();
            fail();
        } catch (Exception e) {
            if (tgtPH()) assertTrue(e.getMessage().contains(" may not exceed 3 bytes"));
            // This error message is different between T2 and T4.  
            // Only make sure that we get an exception now. 
            // else if (tgtSQ()||tgtTR()) assertTrue(e.getMessage().contains("VARCHAR data longer than column length"));
        } finally {
        }
    }

    @Test
    public void testMultiFixedLengthNull() throws Exception {
        printTestDescription();

        String query = null;
        if (tgtPH()) query = "SELECT B_INTEGER,C_INTEGER,COUNT(1) FROM BTABLE GROUP BY C_INTEGER,B_INTEGER";
        else if (tgtSQ()||tgtTR()) query = "SELECT B_INTEGER,C_INTEGER,COUNT(1) FROM BTABLE GROUP BY C_INTEGER,B_INTEGER order by 1";
        try {
            initTableValues();
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            if (tgtPH()) {
                assertTrue(rs.next());
                assertEquals(0, rs.getInt(1));
                assertTrue(rs.wasNull());
                assertEquals(0, rs.getInt(2));
                assertTrue(rs.wasNull());
                assertEquals(1, rs.getLong(3));
            }

            assertTrue(rs.next());
            assertEquals(10, rs.getInt(1));
            assertEquals(1000, rs.getInt(2));
            assertEquals(2, rs.getLong(3));

            assertTrue(rs.next());
            assertEquals(40, rs.getInt(1));
            assertEquals(0, rs.getInt(2));
            assertTrue(rs.wasNull());
            assertEquals(1, rs.getLong(3));

            if (tgtSQ()||tgtTR()) {
                assertTrue(rs.next());
                assertEquals(0, rs.getInt(1));
                assertTrue(rs.wasNull());
                assertEquals(0, rs.getInt(2));
                assertTrue(rs.wasNull());
                assertEquals(1, rs.getLong(3));
            }

            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testSingleFixedLengthNull() throws Exception {
        printTestDescription();

        String query = null;
        if (tgtPH()) query = "SELECT C_INTEGER,COUNT(1) FROM BTABLE GROUP BY C_INTEGER";
        else if (tgtSQ()||tgtTR()) query = "SELECT C_INTEGER,COUNT(1) FROM BTABLE GROUP BY C_INTEGER order by 1 desc";
        try {
            initTableValues();
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(0, rs.getInt(1));
            assertTrue(rs.wasNull());
            assertEquals(2, rs.getLong(2));

            assertTrue(rs.next());
            assertEquals(1000, rs.getInt(1));
            assertEquals(2, rs.getLong(2));

            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testMultiMixedTypeGroupBy() throws Exception {
        printTestDescription();

        String query = null;
        if (tgtPH()) query = "SELECT A_ID, E_STRING, D_STRING, C_INTEGER, COUNT(1) FROM BTABLE GROUP BY A_ID, E_STRING, D_STRING, C_INTEGER";
        else if (tgtSQ()||tgtTR()) query = "SELECT A_ID, E_STRING, D_STRING, C_INTEGER, COUNT(1) FROM BTABLE GROUP BY A_ID, E_STRING, D_STRING, C_INTEGER order by 1, 3 desc";
        try {
            initTableValues();
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("111", rs.getString(1));
            assertEquals("0123456789", rs.getString(2));
            assertEquals(null, rs.getString(3));
            assertEquals(1000, rs.getInt(4));
            assertEquals(1, rs.getInt(5));

            assertTrue(rs.next());
            assertEquals("222", rs.getString(1));
            assertEquals("0123456789", rs.getString(2));
            assertEquals(null, rs.getString(3));
            assertEquals(0, rs.getInt(4));
            assertTrue(rs.wasNull());
            assertEquals(2, rs.getInt(5));

            assertTrue(rs.next());
            assertEquals("222", rs.getString(1));
            assertEquals("0123456789", rs.getString(2));
            assertEquals("efg", rs.getString(3));
            assertEquals(1000, rs.getInt(4));
            assertEquals(1, rs.getInt(5));

            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testSubstrFunction() throws Exception {
        printTestDescription();

        String query[] = null;
        if (tgtPH()) query = new String[] {
            "SELECT substr('ABC',-1,1) FROM BTABLE LIMIT 1",
            "SELECT substr('ABC',-4,1) FROM BTABLE LIMIT 1",
            "SELECT substr('ABC',2,4) FROM BTABLE LIMIT 1",
            "SELECT substr('ABC',1,1) FROM BTABLE LIMIT 1",
            "SELECT substr('ABC',0,1) FROM BTABLE LIMIT 1",
            // Test for multibyte characters support.
            "SELECT substr('ĎďĒ',0,1) FROM BTABLE LIMIT 1",
            "SELECT substr('ĎďĒ',0,2) FROM BTABLE LIMIT 1",
            "SELECT substr('ĎďĒ',1,1) FROM BTABLE LIMIT 1",
            "SELECT substr('ĎďĒ',1,2) FROM BTABLE LIMIT 1",
            "SELECT substr('ĎďĒ',2,1) FROM BTABLE LIMIT 1",
            "SELECT substr('ĎďĒ',2,2) FROM BTABLE LIMIT 1",
            "SELECT substr('ĎďĒ',-1,1) FROM BTABLE LIMIT 1",
            "SELECT substr('Ďďɚʍ',2,4) FROM BTABLE LIMIT 1",
            "SELECT pk FROM VarcharKeyTest WHERE substr(pk, 0, 3)='jkl'",
        };
        else if (tgtTR()) query = new String[] {
            "SELECT substr('ABC',-1,1) FROM BTABLE LIMIT 1",
            "SELECT substr('ABC',-4,1) FROM BTABLE LIMIT 1",
            "SELECT substr('ABC',2,4) FROM BTABLE LIMIT 1",
            "SELECT substr('ABC',1,1) FROM BTABLE LIMIT 1",
            "SELECT substr('ABC',0,1) FROM BTABLE LIMIT 1",
            // Test for multibyte characters support.
            // TRAF: use ASCII for now.
            "SELECT substr('ABC',0,2) FROM BTABLE LIMIT 1",
            "SELECT substr('ABC',0,3) FROM BTABLE LIMIT 1",
            "SELECT substr('ABC',1,2) FROM BTABLE LIMIT 1",
            "SELECT substr('ABC',1,3) FROM BTABLE LIMIT 1",
            "SELECT substr('ABC',2,1) FROM BTABLE LIMIT 1",
            "SELECT substr('ABC',2,2) FROM BTABLE LIMIT 1",
            "SELECT substr('ABC',2,4) FROM BTABLE LIMIT 1",
            "SELECT substr('ABC',1,100) FROM BTABLE LIMIT 1",
            "SELECT pk FROM VarcharKeyTest WHERE substr(pk, 1, 3)='jkl'",
         };
        else if (tgtSQ()) query = new String[] {
            "SELECT [FIRST 1] substr('ABC',-1,1) FROM BTABLE",
            "SELECT [FIRST 1] substr('ABC',-4,1) FROM BTABLE",
            "SELECT [FIRST 1] substr('ABC',2,4) FROM BTABLE",
            "SELECT [FIRST 1] substr('ABC',1,1) FROM BTABLE",
            "SELECT [FIRST 1] substr('ABC',0,1) FROM BTABLE",
            // Test for multibyte characters support.
            // TRAF: use ASCII for now.
            "SELECT [FIRST 1] substr('ABC',0,2) FROM BTABLE",
            "SELECT [FIRST 1] substr('ABC',0,3) FROM BTABLE",
            "SELECT [FIRST 1] substr('ABC',1,2) FROM BTABLE",
            "SELECT [FIRST 1] substr('ABC',1,3) FROM BTABLE",
            "SELECT [FIRST 1] substr('ABC',2,1) FROM BTABLE",
            "SELECT [FIRST 1] substr('ABC',2,2) FROM BTABLE",
            "SELECT [FIRST 1] substr('ABC',2,4) FROM BTABLE",
            "SELECT [FIRST 1] substr('ABC',1,100) FROM BTABLE",
            "SELECT pk FROM VarcharKeyTest WHERE substr(pk, 1, 3)='jkl'",
         };
        String result[] = null;
        if (tgtPH()) result = new String[]{
            "C",
            null,
            "BC",
            "A",
            "A",
            "Ď",
            "Ďď",
            "Ď",
            "Ďď",
            "ď",
            "ďĒ",
            "Ē",
            "ďɚʍ",
            "jkl   ",
        };
        else if (tgtSQ()||tgtTR()) result = new String[]{
            "",
            "",
            "BC",
            "A",
            "",
            "A",
            "AB",
            "AB",
            "ABC",
            "B",
            "BC",
            "BC",
            "ABC",
            "jkl   ",
        };
        assertEquals(query.length,result.length);
        try {
            initTableValues();
            for (int i = 0; i < query.length; i++) {
                PreparedStatement statement = conn.prepareStatement(query[i]);
                ResultSet rs = statement.executeQuery();
                assertTrue(rs.next());
                assertEquals(result[i], rs.getString(1));
                assertFalse(rs.next());
            }
        } finally {
        }
    }

    @Test
    public void testRegexReplaceFunction() throws Exception {
        printTestDescription();

        if (tgtSQ()||tgtTR()) return; // TRAF: do not support regexp_replace()

        // NOTE: we need to double escape the "\\" here because conn.prepareStatement would
        // also try to evaluate the escaping. As a result, to represent what normally would be
        // a "\d" in this test, it would become "\\\\d".
        String query[] = {
            "SELECT regexp_replace('', '') FROM BTABLE LIMIT 1",
            "SELECT regexp_replace('', 'abc', 'def') FROM BTABLE LIMIT 1",
            "SELECT regexp_replace('123abcABC', '[a-z]+') FROM BTABLE LIMIT 1",
            "SELECT regexp_replace('123-abc-ABC', '-[a-zA-Z-]+') FROM BTABLE LIMIT 1",
            "SELECT regexp_replace('abcABC123', '\\\\d+', '') FROM BTABLE LIMIT 1",
            "SELECT regexp_replace('abcABC123', '\\\\D+', '') FROM BTABLE LIMIT 1",
            "SELECT regexp_replace('abc', 'abc', 'def') FROM BTABLE LIMIT 1",
            "SELECT regexp_replace('abc123ABC', '\\\\d+', 'def') FROM BTABLE LIMIT 1",
            "SELECT regexp_replace('abc123ABC', '[0-9]+', '#') FROM BTABLE LIMIT 1",
            "SELECT CASE WHEN regexp_replace('abcABC123', '[a-zA-Z]+') = '123' THEN '1' ELSE '2' END FROM BTABLE LIMIT 1",
            "SELECT A_STRING FROM BTABLE WHERE A_ID = regexp_replace('abcABC111', '[a-zA-Z]+') LIMIT 1", // 111
            // Test for multibyte characters support.
            "SELECT regexp_replace('Ďď Ēĕ ĜĞ ϗϘϛϢ', '[a-zA-Z]+') from BTABLE LIMIT 1",
            "SELECT regexp_replace('Ďď Ēĕ ĜĞ ϗϘϛϢ', '[Ď-ě]+', '#') from BTABLE LIMIT 1",
            "SELECT regexp_replace('Ďď Ēĕ ĜĞ ϗϘϛϢ', '.+', 'replacement') from BTABLE LIMIT 1",
            "SELECT regexp_replace('Ďď Ēĕ ĜĞ ϗϘϛϢ', 'Ďď', 'DD') from BTABLE LIMIT 1",
        };
        String result[] = {
            null,
            null,
            "123ABC",
            "123",
            "abcABC",
            "123",
            "def",
            "abcdefABC",
            "abc#ABC",
            "1",
            "abc", // the first column
            "Ďď Ēĕ ĜĞ ϗϘϛϢ",
            "# # ĜĞ ϗϘϛϢ",
            "replacement",
            "DD Ēĕ ĜĞ ϗϘϛϢ",
        };
        assertEquals(query.length,result.length);
        try {
            initTableValues();
            for (int i = 0; i < query.length; i++) {
                PreparedStatement statement = conn.prepareStatement(query[i]);
                ResultSet rs = statement.executeQuery();
                assertTrue(rs.next());
                assertEquals(result[i], rs.getString(1));
                assertFalse(rs.next());
            }
        } finally {
        }
    }

    @Test
    public void testRegexpSubstrFunction() throws Exception {
        printTestDescription();

        if (tgtSQ()||tgtTR()) return; // TRAF: do not support regexp_substr()

        String query[] = {
            "SELECT regexp_substr('', '', 0) FROM BTABLE LIMIT 1",
            "SELECT regexp_substr('', '', 1) FROM BTABLE LIMIT 1",
            "SELECT regexp_substr('', 'abc', 0) FROM BTABLE LIMIT 1",
            "SELECT regexp_substr('abc', '', 0) FROM BTABLE LIMIT 1",
            "SELECT regexp_substr('123', '123', 3) FROM BTABLE LIMIT 1",
            "SELECT regexp_substr('123', '123', -4) FROM BTABLE LIMIT 1",
            "SELECT regexp_substr('123ABC', '[a-z]+', 0) FROM BTABLE LIMIT 1",
            "SELECT regexp_substr('123ABC', '[0-9]+', 4) FROM BTABLE LIMIT 1",
            "SELECT regexp_substr('123ABCabc', '\\\\d+', 0) FROM BTABLE LIMIT 1",
            "SELECT regexp_substr('123ABCabc', '\\\\D+', 0) FROM BTABLE LIMIT 1",
            "SELECT regexp_substr('123ABCabc', '\\\\D+', 4) FROM BTABLE LIMIT 1",
            "SELECT regexp_substr('123ABCabc', '\\\\D+', 7) FROM BTABLE LIMIT 1",
            "SELECT regexp_substr('na11-app5-26-sjl', '[^-]+', 0) FROM BTABLE LIMIT 1",
            "SELECT regexp_substr('na11-app5-26-sjl', '[^-]+') FROM BTABLE LIMIT 1",
            // Test for multibyte characters support.
            "SELECT regexp_substr('ĎďĒĕĜĞ', '.+') from BTABLE LIMIT 1",
            "SELECT regexp_substr('ĎďĒĕĜĞ', '.+', 3) from BTABLE LIMIT 1",
            "SELECT regexp_substr('ĎďĒĕĜĞ', '[a-zA-Z]+', 0) from BTABLE LIMIT 1",
            "SELECT regexp_substr('ĎďĒĕĜĞ', '[Ď-ě]+', 3) from BTABLE LIMIT 1",
        };
        String result[] = {
            null,
            null,
            null,
            null,
            null,
            null,
            null,
            null,
            "123",
            "ABCabc",
            "ABCabc",
            "abc",
            "na11",
            "na11",
            "ĎďĒĕĜĞ",
            "ĒĕĜĞ",
            null,
            "Ēĕ",
        };
        assertEquals(query.length,result.length);
        try {
            initTableValues();
            for (int i = 0; i < query.length; i++) {
                PreparedStatement statement = conn.prepareStatement(query[i]);
                ResultSet rs = statement.executeQuery();
                assertTrue(rs.next());
                assertEquals(result[i], rs.getString(1));
                assertFalse(rs.next());
            }
        } finally {
        }
    }

    @Test
    public void testLikeConstant() throws Exception {
        printTestDescription();

        String query[] = null;
        if (tgtPH()) query = new String[] {
            "SELECT CASE WHEN 'ABC' LIKE '' THEN '1' ELSE '2' END FROM BTABLE LIMIT 1",
            "SELECT CASE WHEN 'ABC' LIKE 'A_' THEN '1' ELSE '2' END FROM BTABLE LIMIT 1",
            "SELECT CASE WHEN 'ABC' LIKE 'A__' THEN '1' ELSE '2' END FROM BTABLE LIMIT 1",
            "SELECT CASE WHEN 'AB_C' LIKE 'AB\\_C' THEN '1' ELSE '2' END FROM BTABLE LIMIT 1",
            "SELECT CASE WHEN 'ABC%DE' LIKE 'ABC\\%D%' THEN '1' ELSE '2' END FROM BTABLE LIMIT 1",
        };
        else if (tgtTR()) query = new String[] {
            "SELECT CASE WHEN 'ABC' LIKE '' THEN '1' ELSE '2' END FROM BTABLE LIMIT 1",
            "SELECT CASE WHEN 'ABC' LIKE 'A_' THEN '1' ELSE '2' END FROM BTABLE LIMIT 1",
            "SELECT CASE WHEN 'ABC' LIKE 'A__' THEN '1' ELSE '2' END FROM BTABLE LIMIT 1",
            "SELECT CASE WHEN 'AB_C' LIKE 'AB\\_C' ESCAPE '\\' THEN '1' ELSE '2' END FROM BTABLE LIMIT 1",
            "SELECT CASE WHEN 'ABC%DE' LIKE 'ABC\\%D%' ESCAPE '\\' THEN '1' ELSE '2' END FROM BTABLE LIMIT 1",
        };
        else if (tgtSQ()) query = new String[] {
            "SELECT [FIRST 1] CASE WHEN 'ABC' LIKE '' THEN '1' ELSE '2' END FROM BTABLE",
            "SELECT [FIRST 1] CASE WHEN 'ABC' LIKE 'A_' THEN '1' ELSE '2' END FROM BTABLE",
            "SELECT [FIRST 1] CASE WHEN 'ABC' LIKE 'A__' THEN '1' ELSE '2' END FROM BTABLE",
            "SELECT [FIRST 1] CASE WHEN 'AB_C' LIKE 'AB\\_C' ESCAPE '\\' THEN '1' ELSE '2' END FROM BTABLE",
            "SELECT [FIRST 1] CASE WHEN 'ABC%DE' LIKE 'ABC\\%D%' ESCAPE '\\' THEN '1' ELSE '2' END FROM BTABLE",
        };
        String result[] = {
            "2",
            "2",
            "1",
            "1",
            "1",
        };
        assertEquals(query.length,result.length);
        try {
            initTableValues();
            for (int i = 0; i < query.length; i++) {
                PreparedStatement statement = conn.prepareStatement(query[i]);
                ResultSet rs = statement.executeQuery();
                assertTrue(rs.next());
                assertEquals(query[i],result[i], rs.getString(1));
                assertFalse(rs.next());
            }
        } finally {
        }
    }

    @Test
    public void testInListConstant() throws Exception {
        printTestDescription();

        String query[] = null;
        if (tgtPH()) query = new String[] {
            "SELECT CASE WHEN 'a' IN (null,'a') THEN '1' ELSE '2' END FROM BTABLE LIMIT 1",
            "SELECT CASE WHEN NOT 'a' IN (null,'b') THEN '1' ELSE '2' END FROM BTABLE LIMIT 1",
            "SELECT CASE WHEN 'a' IN (null,'b') THEN '1' ELSE '2' END FROM BTABLE LIMIT 1",
            "SELECT CASE WHEN NOT 'a' IN ('c','b') THEN '1' ELSE '2' END FROM BTABLE LIMIT 1",
            "SELECT CASE WHEN 1 IN ('foo',2,1) THEN '1' ELSE '2' END FROM BTABLE LIMIT 1",
        };
        // TRAF: null is not allowed in predicate ('a'=null).  This is a 4099
        // error. (only col IS NULL is supported)  Remove null from IN () predicates.
        else if (tgtTR()) query = new String[] {
            "SELECT CASE WHEN 'a' IN ('a') THEN '1' ELSE '2' END FROM BTABLE LIMIT 1",
            "SELECT CASE WHEN NOT 'a' IN ('b') THEN '1' ELSE '2' END FROM BTABLE LIMIT 1",
            "SELECT CASE WHEN 'a' IN ('b') THEN '1' ELSE '2' END FROM BTABLE LIMIT 1",
            "SELECT CASE WHEN NOT 'a' IN ('c','b') THEN '1' ELSE '2' END FROM BTABLE LIMIT 1",
            "SELECT CASE WHEN 1 IN (2,1) THEN '1' ELSE '2' END FROM BTABLE LIMIT 1",
        };
        else if (tgtSQ()) query = new String[] {
            "SELECT [FIRST 1] CASE WHEN 'a' IN ('a') THEN '1' ELSE '2' END FROM BTABLE",
            "SELECT [FIRST 1] CASE WHEN NOT 'a' IN ('b') THEN '1' ELSE '2' END FROM BTABLE",
            "SELECT [FIRST 1] CASE WHEN 'a' IN ('b') THEN '1' ELSE '2' END FROM BTABLE",
            "SELECT [FIRST 1] CASE WHEN NOT 'a' IN ('c','b') THEN '1' ELSE '2' END FROM BTABLE",
            "SELECT [FIRST 1] CASE WHEN 1 IN (2,1) THEN '1' ELSE '2' END FROM BTABLE",
        };

        String result[] = null;
        if (tgtPH()) result = new String[] {
            "1",
            "2",
            "2",
            "1",
            "1",
        };
        else if (tgtSQ()||tgtTR()) result = new String[] {
            "1",
            "1",
            "2",
            "1",
            "1",
        };

        assertEquals(query.length,result.length);
        try {
            initTableValues();
            for (int i = 0; i < query.length; i++) {
                PreparedStatement statement = conn.prepareStatement(query[i]);
                ResultSet rs = statement.executeQuery();
                assertTrue(rs.next());
                assertEquals(query[i],result[i], rs.getString(1));
                assertFalse(rs.next());
            }
        } finally {
        }
    }

    @Test
    public void testLikeOnColumn() throws Exception {
        printTestDescription();

        createTestTable(PTSDB_NAME);

        PreparedStatement stmt = null;
        if (tgtPH()||tgtTR()) stmt = conn.prepareStatement("upsert into PTSDB VALUES (?, ?, ?, 0.5)");
        else if (tgtSQ()) stmt = conn.prepareStatement("insert into PTSDB VALUES (?, ?, ?, 0.5)");
        if (tgtPH()) stmt.setDate(3, D1);
        else if (tgtSQ()||tgtTR()) stmt.setTimestamp(3, T1);

        stmt.setString(1, "a");
        stmt.setString(2, "a");
        stmt.execute();

        stmt.setString(1, "x");
        stmt.setString(2, "a");
        stmt.execute();

        stmt.setString(1, "xy");
        stmt.setString(2, "b");
        stmt.execute();

        stmt.setString(1, "xyz");
        stmt.setString(2, "c");
        stmt.execute();

        stmt.setString(1, "xyza");
        stmt.setString(2, "d");
        stmt.execute();

        stmt.setString(1, "xyzab");
        stmt.setString(2, "e");
        stmt.execute();

        stmt.setString(1, "z");
        stmt.setString(2, "e");
        stmt.execute();

        PreparedStatement statement = null;
        ResultSet rs;
        try {
            // Test 1
            if (tgtPH()) statement = conn.prepareStatement("SELECT INST FROM PTSDB WHERE INST LIKE 'x%'");
            else if (tgtSQ()||tgtTR()) statement = conn.prepareStatement("SELECT INST FROM PTSDB WHERE INST LIKE 'x%' order by 1");

            rs = statement.executeQuery();

            assertTrue(rs.next());
            assertEquals("x", rs.getString(1));

            assertTrue(rs.next());
            assertEquals("xy", rs.getString(1));

            assertTrue(rs.next());
            assertEquals("xyz", rs.getString(1));

            assertTrue(rs.next());
            assertEquals("xyza", rs.getString(1));

            assertTrue(rs.next());
            assertEquals("xyzab", rs.getString(1));

            assertFalse(rs.next());

            // Test 2
            if (tgtPH()) statement = conn.prepareStatement("SELECT INST FROM PTSDB WHERE INST LIKE 'xy_a%'");
            else if (tgtSQ()||tgtTR()) statement = conn.prepareStatement("SELECT INST FROM PTSDB WHERE INST LIKE 'xy_a%' order by 1");

            rs = statement.executeQuery();

            assertTrue(rs.next());
            assertEquals("xyza", rs.getString(1));

            assertTrue(rs.next());
            assertEquals("xyzab", rs.getString(1));

            assertFalse(rs.next());

            // Test 3
            if (tgtPH()) statement = conn.prepareStatement("SELECT INST FROM PTSDB WHERE INST NOT LIKE 'xy_a%'");
            else if (tgtSQ()||tgtTR()) statement = conn.prepareStatement("SELECT INST FROM PTSDB WHERE INST NOT LIKE 'xy_a%' order by 1");

            rs = statement.executeQuery();

            assertTrue(rs.next());
            assertEquals("a", rs.getString(1));

            assertTrue(rs.next());
            assertEquals("x", rs.getString(1));

            assertTrue(rs.next());
            assertEquals("xy", rs.getString(1));

            assertTrue(rs.next());
            assertEquals("xyz", rs.getString(1));

            assertTrue(rs.next());
            assertEquals("z", rs.getString(1));

            assertFalse(rs.next());

            // Test 4
            statement = conn.prepareStatement("SELECT INST FROM PTSDB WHERE 'xzabc' LIKE 'xy_a%'");
            rs = statement.executeQuery();
            assertFalse(rs.next());

        } finally {
        }
    }

    @Test
    public void testIsNullInPK() throws Exception {
        printTestDescription();

        createTestTable(PTSDB_NAME);

        conn.setAutoCommit(true);
        PreparedStatement stmt = null;
        if (tgtPH()) stmt = conn.prepareStatement("upsert into PTSDB VALUES ('', '', ?, 0.5)");
        else if (tgtTR()) stmt = conn.prepareStatement("upsert into PTSDB VALUES (null, null, ?, 0.5)");
        else if (tgtSQ()) stmt = conn.prepareStatement("insert into PTSDB VALUES (null, null, ?, 0.5)");
        stmt.setDate(1, D1);
        stmt.execute();

        String query = null;
        if (tgtPH()) query = "SELECT HOST,INST,DATE FROM PTSDB WHERE HOST IS NULL AND INST IS NULL AND DATE=?";
        else if (tgtSQ()||tgtTR()) query = "SELECT HOST1,INST,DATE1 FROM PTSDB WHERE HOST1 IS NULL AND INST IS NULL AND DATE1=?";
        try { 
            PreparedStatement statement = conn.prepareStatement(query);
            if (tgtPH()) statement.setDate(1, D1);
            else if (tgtSQ()||tgtTR()) statement.setTimestamp(1, T1);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertNull(rs.getString(1));
            assertNull(rs.getString(2));
            if (tgtPH()) assertEquals(D1, rs.getDate(3));
            else if (tgtSQ()||tgtTR()) assertEquals(T1, rs.getTimestamp(3));
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testLengthFunction() throws Exception {
        printTestDescription();

        String query[] = null;
        if (tgtPH()) query = new String[] {
            "SELECT length('') FROM BTABLE LIMIT 1",
            "SELECT length(' ') FROM BTABLE LIMIT 1",
            "SELECT length('1') FROM BTABLE LIMIT 1",
            "SELECT length('1234') FROM BTABLE LIMIT 1",
            "SELECT length('ɚɦɰɸ') FROM BTABLE LIMIT 1",
            "SELECT length('ǢǛǟƈ') FROM BTABLE LIMIT 1",
            "SELECT length('This is a test!') FROM BTABLE LIMIT 1",
            "SELECT A_STRING FROM BTABLE WHERE length(A_STRING)=3",
        };
        else if (tgtTR()) query = new String[] {
            "SELECT char_length('') FROM BTABLE LIMIT 1",
            "SELECT char_length(' ') FROM BTABLE LIMIT 1",
            "SELECT char_length('1') FROM BTABLE LIMIT 1",
            "SELECT char_length('1234') FROM BTABLE LIMIT 1",
            "SELECT char_length('ABCD') FROM BTABLE LIMIT 1",
            "SELECT char_length('!@#$') FROM BTABLE LIMIT 1",
            "SELECT char_length('This is a test!') FROM BTABLE LIMIT 1",
            "SELECT A_STRING FROM BTABLE WHERE char_length(A_STRING)=3",
        };
        else if (tgtSQ()) query = new String[] {
            "SELECT [FIRST 1] char_length('') FROM BTABLE",
            "SELECT [FIRST 1] char_length(' ') FROM BTABLE",
            "SELECT [FIRST 1] char_length('1') FROM BTABLE",
            "SELECT [FIRST 1] char_length('1234') FROM BTABLE",
            "SELECT [FIRST 1] char_length('ABCD') FROM BTABLE",
            "SELECT [FIRST 1] char_length('!@#$') FROM BTABLE",
            "SELECT [FIRST 1] char_length('This is a test!') FROM BTABLE",
            "SELECT A_STRING FROM BTABLE WHERE char_length(A_STRING)=3",
        };

        String result[] = null;
        if (tgtPH()) result = new String[] {
            null,
            "1",
            "1",
            "4",
            "4",
            "4",
            "15",
            "abc",
        };
        else if (tgtSQ()||tgtTR()) result = new String[] {
            "0",
            "1",
            "1",
            "4",
            "4",
            "4",
            "15",
            "abc",
        };
        assertEquals(query.length,result.length);
        try {
            initTableValues();
            for (int i = 0; i < query.length; i++) {
                PreparedStatement statement = conn.prepareStatement(query[i]);
                ResultSet rs = statement.executeQuery();
                assertTrue(rs.next());
                assertEquals(query[i],result[i], rs.getString(1));
                assertFalse(rs.next());
            }
        } finally {
        }
    }

    @Test
    public void testUpperFunction() throws Exception {
        printTestDescription();

        String query[] = null;
        if (tgtPH()) query = new String[] {
                "SELECT upper('abc') FROM BTABLE LIMIT 1",
                "SELECT upper('Abc') FROM BTABLE LIMIT 1",
                "SELECT upper('ABC') FROM BTABLE LIMIT 1",
                "SELECT upper('ĎďĒ') FROM BTABLE LIMIT 1",
                "SELECT upper('ß') FROM BTABLE LIMIT 1",
        };
        else if (tgtTR()) query = new String[] {
                "SELECT upper('abc') FROM BTABLE LIMIT 1",
                "SELECT upper('Abc') FROM BTABLE LIMIT 1",
                "SELECT upper('ABC') FROM BTABLE LIMIT 1",
                // TRAF: test only ASCII for now
                "SELECT upper('123') FROM BTABLE LIMIT 1",
                "SELECT upper('#$+') FROM BTABLE LIMIT 1",
        };
        else if (tgtSQ()) query = new String[] {
                "SELECT [FIRST 1] upper('abc') FROM BTABLE",
                "SELECT [FIRST 1] upper('Abc') FROM BTABLE",
                "SELECT [FIRST 1] upper('ABC') FROM BTABLE",
                // TRAF: test only ASCII for now
                "SELECT [FIRST 1] upper('123') FROM BTABLE",
                "SELECT [FIRST 1] upper('#$+') FROM BTABLE",
        };
        String result[] = null;
        if (tgtPH()) result = new String[] {
                "ABC",
                "ABC",
                "ABC",
                "ĎĎĒ",
                "SS",
        };
        else if (tgtSQ()||tgtTR()) result = new String[] {
                "ABC",
                "ABC",
                "ABC",
                "123",
                "#$+",
        };
        assertEquals(query.length, result.length);
        try {
            initTableValues();
            for (int i = 0; i < query.length; i++) {
                PreparedStatement statement = conn.prepareStatement(query[i]);
                ResultSet rs = statement.executeQuery();
                assertTrue(rs.next());
                assertEquals(query[i],result[i], rs.getString(1));
                assertFalse(rs.next());
            }
        } finally {
        }
    }

    @Test
    public void testLowerFunction() throws Exception {
        printTestDescription();

        String query[] = null;
        if (tgtPH()) query = new String[] {
                "SELECT lower('abc') FROM BTABLE LIMIT 1",
                "SELECT lower('Abc') FROM BTABLE LIMIT 1",
                "SELECT lower('ABC') FROM BTABLE LIMIT 1",
                "SELECT lower('ĎďĒ') FROM BTABLE LIMIT 1",
                "SELECT lower('ß') FROM BTABLE LIMIT 1",
                "SELECT lower('SS') FROM BTABLE LIMIT 1",
        };
        else if (tgtTR()) query = new String[] {
                "SELECT lower('abc') FROM BTABLE LIMIT 1",
                "SELECT lower('Abc') FROM BTABLE LIMIT 1",
                "SELECT lower('ABC') FROM BTABLE LIMIT 1",
                // TRAF: test only ASCII for now
                "SELECT upper('123') FROM BTABLE LIMIT 1",
                "SELECT upper('#$+') FROM BTABLE LIMIT 1",
                "SELECT lower('SS') FROM BTABLE LIMIT 1",
        };
        else if (tgtSQ()) query = new String[] {
                "SELECT [FIRST 1] lower('abc') FROM BTABLE",
                "SELECT [FIRST 1] lower('Abc') FROM BTABLE",
                "SELECT [FIRST 1] lower('ABC') FROM BTABLE",
                // TRAF: test only ASCII for now
                "SELECT [FIRST 1] upper('123') FROM BTABLE",
                "SELECT [FIRST 1] upper('#$+') FROM BTABLE",
                "SELECT [FIRST 1] lower('SS') FROM BTABLE",
        };

        String result[] = null;
        if (tgtPH()) result = new String[] {
                "abc",
                "abc",
                "abc",
                "ďďē",
                "ß",
                "ss",
        };
        else if (tgtSQ()||tgtTR()) result = new String[] {
                "abc",
                "abc",
                "abc",
                "123",
                "#$+",
                "ss",
        };

        assertEquals(query.length, result.length);
        try {
            initTableValues();
            for (int i = 0; i < query.length; i++) {
                PreparedStatement statement = conn.prepareStatement(query[i]);
                ResultSet rs = statement.executeQuery();
                assertTrue(rs.next());
                assertEquals(query[i],result[i], rs.getString(1));
                assertFalse(rs.next());
            }
        } finally {
        }
    }

    @Test
    public void testRTrimFunction() throws Exception {
        printTestDescription();

        String query[] = null;
        if (tgtPH()) query = new String[] {
            "SELECT rtrim('') FROM BTABLE LIMIT 1",
            "SELECT rtrim(' ') FROM BTABLE LIMIT 1",
            "SELECT rtrim('   ') FROM BTABLE LIMIT 1",
            "SELECT rtrim('abc') FROM BTABLE LIMIT 1",
            "SELECT rtrim('abc   ') FROM BTABLE LIMIT 1",
            "SELECT rtrim('abc   def') FROM BTABLE LIMIT 1",
            "SELECT rtrim('abc   def   ') FROM BTABLE LIMIT 1",
            "SELECT rtrim('ĎďĒ   ') FROM BTABLE LIMIT 1",
            "SELECT pk FROM VarcharKeyTest WHERE rtrim(pk)='jkl' LIMIT 1",
        };
        else if (tgtTR()) query = new String[] {
            "SELECT rtrim('') FROM BTABLE LIMIT 1",
            "SELECT rtrim(' ') FROM BTABLE LIMIT 1",
            "SELECT rtrim('   ') FROM BTABLE LIMIT 1",
            "SELECT rtrim('abc') FROM BTABLE LIMIT 1",
            "SELECT rtrim('abc   ') FROM BTABLE LIMIT 1",
            "SELECT rtrim('abc   def') FROM BTABLE LIMIT 1",
            "SELECT rtrim('abc   def   ') FROM BTABLE LIMIT 1",
            // TRAF: Test only ASCII for now
            "SELECT rtrim('12 3   ') FROM BTABLE LIMIT 1",
            "SELECT pk FROM VarcharKeyTest WHERE rtrim(pk)='jkl'",
        };
        else if (tgtSQ()) query = new String[] {
            "SELECT [FIRST 1] rtrim('') FROM BTABLE",
            "SELECT [FIRST 1] rtrim(' ') FROM BTABLE",
            "SELECT [FIRST 1] rtrim('   ') FROM BTABLE",
            "SELECT [FIRST 1] rtrim('abc') FROM BTABLE",
            "SELECT [FIRST 1] rtrim('abc   ') FROM BTABLE",
            "SELECT [FIRST 1] rtrim('abc   def') FROM BTABLE",
            "SELECT [FIRST 1] rtrim('abc   def   ') FROM BTABLE",
            // TRAF: Test only ASCII for now
            "SELECT [FIRST 1] rtrim('12 3   ') FROM BTABLE",
            "SELECT [FIRST 1] pk FROM VarcharKeyTest WHERE rtrim(pk)='jkl'",
        };

        String result[] = null;
        if (tgtPH()) result = new String[] {
            null,
            null,
            null,
            "abc",
            "abc",
            "abc   def",
            "abc   def",
            "ĎďĒ",
            "jkl   ",
        };
        else if (tgtSQ()||tgtTR()) result = new String[] {
            "",
            "",
            "",
            "abc",
            "abc",
            "abc   def",
            "abc   def",
            "12 3",
            "jkl   ",
        };

        assertEquals(query.length, result.length);
        try {
            initTableValues();
            for (int i = 0; i < query.length; i++) {
                PreparedStatement statement = conn.prepareStatement(query[i]);
                ResultSet rs = statement.executeQuery();
                assertTrue(rs.next());
                assertEquals(query[i],result[i], rs.getString(1));
                assertFalse(rs.next());
            }
        } finally {
        }
    }

    @Test
    public void testLTrimFunction() throws Exception {
        printTestDescription();

        String query[] = null;
        if (tgtPH()) query = new String[] {
            "SELECT ltrim('') FROM BTABLE LIMIT 1",
            "SELECT ltrim(' ') FROM BTABLE LIMIT 1",
            "SELECT ltrim('   ') FROM BTABLE LIMIT 1",
            "SELECT ltrim('abc') FROM BTABLE LIMIT 1",
            "SELECT ltrim('   abc') FROM BTABLE LIMIT 1",
            "SELECT ltrim('abc   def') FROM BTABLE LIMIT 1",
            "SELECT ltrim('   abc   def') FROM BTABLE LIMIT 1",
            "SELECT ltrim('   ĎďĒ') FROM BTABLE LIMIT 1",
            "SELECT pk FROM VarcharKeyTest WHERE ltrim(pk)='def' LIMIT 1",
        };
        else if (tgtTR()) query = new String[] {
            "SELECT ltrim('') FROM BTABLE LIMIT 1",
            "SELECT ltrim(' ') FROM BTABLE LIMIT 1",
            "SELECT ltrim('   ') FROM BTABLE LIMIT 1",
            "SELECT ltrim('abc') FROM BTABLE LIMIT 1",
            "SELECT ltrim('   abc') FROM BTABLE LIMIT 1",
            "SELECT ltrim('abc   def') FROM BTABLE LIMIT 1",
            "SELECT ltrim('   abc   def') FROM BTABLE LIMIT 1",
            // TRAF: Test ASCII only for now.
            "SELECT ltrim('   12 3') FROM BTABLE LIMIT 1",
            "SELECT pk FROM VarcharKeyTest WHERE ltrim(pk)='def' LIMIT 1",
        };
        else if (tgtSQ()) query = new String[] {
            "SELECT [FIRST 1] ltrim('') FROM BTABLE",
            "SELECT [FIRST 1] ltrim(' ') FROM BTABLE",
            "SELECT [FIRST 1] ltrim('   ') FROM BTABLE",
            "SELECT [FIRST 1] ltrim('abc') FROM BTABLE",
            "SELECT [FIRST 1] ltrim('   abc') FROM BTABLE",
            "SELECT [FIRST 1] ltrim('abc   def') FROM BTABLE",
            "SELECT [FIRST 1] ltrim('   abc   def') FROM BTABLE",
            // TRAF: Test ASCII only for now.
            "SELECT [FIRST 1] ltrim('   12 3') FROM BTABLE",
            "SELECT [FIRST 1] pk FROM VarcharKeyTest WHERE ltrim(pk)='def'",
        };

        String result[] = null;
        if (tgtPH()) result = new String[] {
            null,
            null,
            null,
            "abc",
            "abc",
            "abc   def",
            "abc   def",
            "ĎďĒ",
            "   def",
        };
        else if (tgtSQ()||tgtTR()) result = new String[] {
            "",
            "",
            "",
            "abc",
            "abc",
            "abc   def",
            "abc   def",
            "12 3",
            "   def",
        };

        assertEquals(query.length, result.length);
        try {
            initTableValues();
            for (int i = 0; i < query.length; i++) {
                PreparedStatement statement = conn.prepareStatement(query[i]);
                ResultSet rs = statement.executeQuery();
                assertTrue(rs.next());
                assertEquals(query[i],result[i], rs.getString(1));
                assertFalse(rs.next());
            }
        } finally {
        }
    }

    @Test
    public void testSubstrFunctionOnRowKeyInWhere() throws Exception {
        printTestDescription();

        if (tgtPH()) conn.createStatement().execute("CREATE TABLE substr_test (s1 varchar not null, s2 varchar not null constraint pk primary key(s1,s2))");
        else if (tgtSQ()||tgtTR()) conn.createStatement().execute("CREATE TABLE substr_test (s1 varchar(128) not null, s2 varchar(128) not null, constraint pk_substr_test primary key(s1,s2))");

        if (tgtPH()||tgtTR()) {
            conn.createStatement().execute("UPSERT INTO substr_test VALUES('abc','a')");
            conn.createStatement().execute("UPSERT INTO substr_test VALUES('abcd','b')");
            conn.createStatement().execute("UPSERT INTO substr_test VALUES('abce','c')");
            conn.createStatement().execute("UPSERT INTO substr_test VALUES('abcde','d')");
        } else if (tgtSQ()) {
            conn.createStatement().execute("INSERT INTO substr_test VALUES('abc','a')");
            conn.createStatement().execute("INSERT INTO substr_test VALUES('abcd','b')");
            conn.createStatement().execute("INSERT INTO substr_test VALUES('abce','c')");
            conn.createStatement().execute("INSERT INTO substr_test VALUES('abcde','d')");
        }
        ResultSet rs = null;
        if (tgtPH()) rs = conn.createStatement().executeQuery("SELECT s1 from substr_test where substr(s1,1,4) = 'abcd'");
        else if (tgtSQ()||tgtTR()) rs = conn.createStatement().executeQuery("SELECT s1 from substr_test where substr(s1,1,4) = 'abcd' order by 1");
        assertTrue(rs.next());
        assertEquals("abcd",rs.getString(1));
        assertTrue(rs.next());
        assertEquals("abcde",rs.getString(1));
        assertFalse(rs.next());
    }

    @Test
    public void testRTrimFunctionOnRowKeyInWhere() throws Exception {
        printTestDescription();

        if (tgtPH()) conn.createStatement().execute("CREATE TABLE substr_test (s1 varchar not null, s2 varchar not null constraint pk primary key(s1,s2))");
        else if (tgtSQ()||tgtTR()) conn.createStatement().execute("CREATE TABLE substr_test (s1 varchar(128) not null, s2 varchar(128) not null, constraint pk_substr_test primary key(s1,s2))");
      
        if (tgtPH()) {
            conn.createStatement().execute("UPSERT INTO substr_test VALUES('abc','a')");
            conn.createStatement().execute("UPSERT INTO substr_test VALUES('abcd','b')");
            conn.createStatement().execute("UPSERT INTO substr_test VALUES('abcd ','c')");
            conn.createStatement().execute("UPSERT INTO substr_test VALUES('abcd  ','c')");
            conn.createStatement().execute("UPSERT INTO substr_test VALUES('abcd  a','c')"); // Need TRAVERSE_AND_LEAVE for cases like this
            conn.createStatement().execute("UPSERT INTO substr_test VALUES('abcde','d')");
        } else if (tgtTR()) {
            conn.createStatement().execute("UPSERT INTO substr_test VALUES('abc','a')");
            conn.createStatement().execute("UPSERT INTO substr_test VALUES('abcd','b')");
            conn.createStatement().execute("UPSERT INTO substr_test VALUES('abcd ','c')");
            // TRAF: For SQ & TR, it uses 'PAD SPACE' implementation, 'abcd ' 
            // and 'abcd  ' are treated as the same.  It has been decided that
            // this behavior will not change.  Changed the following 'c' to 'd'
            // to make the test work. 
            // conn.createStatement().execute("UPSERT INTO substr_test VALUES('abcd  ','c')");
            conn.createStatement().execute("UPSERT INTO substr_test VALUES('abcd  ','d')");
            conn.createStatement().execute("UPSERT INTO substr_test VALUES('abcd  a','e')"); // Need TRAVERSE_AND_LEAVE for cases like this
            conn.createStatement().execute("UPSERT INTO substr_test VALUES('abcde','f')");
        } else if (tgtSQ()) {
            conn.createStatement().execute("INSERT INTO substr_test VALUES('abc','a')");
            conn.createStatement().execute("INSERT INTO substr_test VALUES('abcd','b')");
            conn.createStatement().execute("INSERT INTO substr_test VALUES('abcd ','c')");
            // TRAF: For SQ & TR, it uses 'PAD SPACE' implementation, 'abcd '
            // and 'abcd  ' are treated as the same.  SQ will see 8102 unique 
            // constraint error in this case. It has been decided that
            // this behavior will not change.  Changed the following 'c' to 'd'
            // to make the test work.
            // conn.createStatement().execute("INSERT INTO substr_test VALUES('abcd  ','c')");
            conn.createStatement().execute("INSERT INTO substr_test VALUES('abcd  ','d')");
            conn.createStatement().execute("INSERT INTO substr_test VALUES('abcd  a','e')"); // Need TRAVERSE_AND_LEAVE for cases like this
            conn.createStatement().execute("INSERT INTO substr_test VALUES('abcde','f')");
        }

        ResultSet rs = null;
        if (tgtPH()) rs = conn.createStatement().executeQuery("SELECT s1 from substr_test where rtrim(s1) = 'abcd'");
        else if (tgtSQ()||tgtTR()) rs = conn.createStatement().executeQuery("SELECT s1 from substr_test where rtrim(s1) = 'abcd' order by s2");
       
        assertTrue(rs.next());
        assertEquals("abcd",rs.getString(1));
        assertTrue(rs.next());
        assertEquals("abcd ",rs.getString(1));
        assertTrue(rs.next());
        assertEquals("abcd  ",rs.getString(1));
        assertFalse(rs.next());
    }

    @Test
    public void testLikeFunctionOnRowKeyInWhere() throws Exception {
        printTestDescription();

        if (tgtPH()) conn.createStatement().execute("CREATE TABLE substr_test (s1 varchar not null, s2 varchar not null constraint pk primary key(s1,s2))");
        else if (tgtSQ()||tgtTR()) conn.createStatement().execute("CREATE TABLE substr_test (s1 varchar(128) not null, s2 varchar(128) not null, constraint pk primary key(s1,s2))");

        if (tgtPH()||tgtTR()) {
            conn.createStatement().execute("UPSERT INTO substr_test VALUES('abc','a')");
            conn.createStatement().execute("UPSERT INTO substr_test VALUES('abcd','b')");
            conn.createStatement().execute("UPSERT INTO substr_test VALUES('abcd-','c')");
            conn.createStatement().execute("UPSERT INTO substr_test VALUES('abcd-1','c')");
            conn.createStatement().execute("UPSERT INTO substr_test VALUES('abce','d')");
        } else if (tgtSQ()) {
            conn.createStatement().execute("INSERT INTO substr_test VALUES('abc','a')");
            conn.createStatement().execute("INSERT INTO substr_test VALUES('abcd','b')");
            conn.createStatement().execute("INSERT INTO substr_test VALUES('abcd-','c')");
            conn.createStatement().execute("INSERT INTO substr_test VALUES('abcd-1','c')");
            conn.createStatement().execute("INSERT INTO substr_test VALUES('abce','d')");
        }

        ResultSet rs = conn.createStatement().executeQuery("SELECT s1 from substr_test where s1 like 'abcd%1'");
        assertTrue(rs.next());
        assertEquals("abcd-1",rs.getString(1));
        assertFalse(rs.next());
    }

    @Test
    public void testTrimFunction() throws Exception {
        printTestDescription();

        String query[] = null;
        if (tgtPH()) query = new String[] {
            "SELECT trim('') FROM BTABLE LIMIT 1",
            "SELECT trim(' ') FROM BTABLE LIMIT 1",
            "SELECT trim('   ') FROM BTABLE LIMIT 1",
            "SELECT trim('abc') FROM BTABLE LIMIT 1",
            "SELECT trim('   abc') FROM BTABLE LIMIT 1",
            "SELECT trim('abc   ') FROM BTABLE LIMIT 1",
            "SELECT trim('abc   def') FROM BTABLE LIMIT 1",
            "SELECT trim('   abc   def') FROM BTABLE LIMIT 1",
            "SELECT trim('abc   def   ') FROM BTABLE LIMIT 1",
            "SELECT trim('   abc   def   ') FROM BTABLE LIMIT 1",
            "SELECT trim('   ĎďĒ   ') FROM BTABLE LIMIT 1",
            "SELECT pk FROM VarcharKeyTest WHERE trim(pk)='ghi'",
        };
        else if (tgtTR()) query = new String[] {
            "SELECT trim('') FROM BTABLE LIMIT 1",
            "SELECT trim(' ') FROM BTABLE LIMIT 1",
            "SELECT trim('   ') FROM BTABLE LIMIT 1",
            "SELECT trim('abc') FROM BTABLE LIMIT 1",
            "SELECT trim('   abc') FROM BTABLE LIMIT 1",
            "SELECT trim('abc   ') FROM BTABLE LIMIT 1",
            "SELECT trim('abc   def') FROM BTABLE LIMIT 1",
            "SELECT trim('   abc   def') FROM BTABLE LIMIT 1",
            "SELECT trim('abc   def   ') FROM BTABLE LIMIT 1",
            "SELECT trim('   abc   def   ') FROM BTABLE LIMIT 1",
            // TRAF: Test only ASCII for now.
            "SELECT trim('   12 3        ') FROM BTABLE LIMIT 1",
            "SELECT pk FROM VarcharKeyTest WHERE trim(pk)='ghi'",
        };
        else if (tgtSQ()) query = new String[] {
            "SELECT [FIRST 1] trim('') FROM BTABLE",
            "SELECT [FIRST 1] trim(' ') FROM BTABLE",
            "SELECT [FIRST 1] trim('   ') FROM BTABLE",
            "SELECT [FIRST 1] trim('abc') FROM BTABLE",
            "SELECT [FIRST 1] trim('   abc') FROM BTABLE",
            "SELECT [FIRST 1] trim('abc   ') FROM BTABLE",
            "SELECT [FIRST 1] trim('abc   def') FROM BTABLE",
            "SELECT [FIRST 1] trim('   abc   def') FROM BTABLE",
            "SELECT [FIRST 1] trim('abc   def   ') FROM BTABLE",
            "SELECT [FIRST 1] trim('   abc   def   ') FROM BTABLE",
            // TRAF: Test only ASCII for now.
            "SELECT [FIRST 1] trim('   12 3        ') FROM BTABLE",
            "SELECT pk FROM VarcharKeyTest WHERE trim(pk)='ghi'",
        };

        String result[] = null;
        if (tgtPH()) result = new String[] {
            null,
            null,
            null,
            "abc",
            "abc",
            "abc",
            "abc   def",
            "abc   def",
            "abc   def",
            "abc   def",
            "ĎďĒ",
            "   ghi   ",
        };
        else if (tgtSQ()||tgtTR()) result = new String[] {
            "",
            "",
            "",
            "abc",
            "abc",
            "abc",
            "abc   def",
            "abc   def",
            "abc   def",
            "abc   def",
            "12 3",
            "   ghi   ",
        };

        assertEquals(query.length, result.length);
        try {
            initTableValues();
            for (int i = 0; i < query.length; i++) {
                PreparedStatement statement = conn.prepareStatement(query[i]);
                ResultSet rs = statement.executeQuery();
                assertTrue(rs.next());
                assertEquals(query[i],result[i], rs.getString(1));
                assertFalse(rs.next());
            }
        } finally {
        }
    }
}