/*******************************************************************************
 * 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.*;
// TRAF import org.apache.hadoop.hbase.filter.CompareFilter.CompareOp;
// TRAF import org.apache.hadoop.hbase.io.ImmutableBytesWritable;
// TRAF import org.apache.hadoop.hbase.util.Bytes;

/**
 * 
 * Basic tests for Phoenix JDBC implementation
 *
 * @author jtaylor
 * @since 0.1
 */
public class QueryExecTest 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 " + ATABLE_NAME, "table SumDoubleTest"));
        doBaseTestSuiteSetup();
    }
    /* @AfterClass, @Before, @After are defined in BaseTest */

    @Test
    public void testScan() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT a_string, /* comment ok? */ b_string FROM aTable WHERE ?=organization_id and 5=a_integer";
        else if (tgtSQ()||tgtTR()) query = "SELECT a_string, b_string FROM aTable WHERE ?=organization_id and 5=a_integer";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), B_VALUE);
            assertEquals(rs.getString("B_string"), C_VALUE);
            assertFalse(rs.next());
        } finally {
        } 
    }
    
    @Test
    public void testScanByByteValue() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT a_string, /* comment ok? */ b_string, a_byte FROM aTable WHERE ?=organization_id and 1=a_byte";
        else if (tgtSQ()||tgtTR()) query = "SELECT a_string, b_string, a_byte FROM aTable WHERE ?=organization_id and 1=a_byte";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), A_VALUE);
            assertEquals(rs.getString("B_string"), B_VALUE);
            assertEquals(rs.getByte(3), 1);
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testScanByShortValue() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT a_string, /* comment ok? */ b_string, a_short FROM aTable WHERE ?=organization_id and 128=a_short";
        else if (tgtSQ()||tgtTR()) query = "SELECT a_string, b_string, a_short FROM aTable WHERE ?=organization_id and 128=a_short";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), A_VALUE);
            assertEquals(rs.getString("B_string"), B_VALUE);
            assertEquals(rs.getShort("a_short"), 128);
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testScanByFloatValue() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT a_string, /* comment ok? */ b_string, a_float FROM aTable WHERE ?=organization_id and ?=a_float";
        else if (tgtSQ()||tgtTR()) query = "SELECT a_string, b_string, a_float FROM aTable WHERE ?=organization_id and ?=a_float";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            statement.setFloat(2, 0.01f);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), A_VALUE);
            assertEquals(rs.getString("B_string"), B_VALUE);
            assertTrue(Float.compare(rs.getFloat(3), 0.01f) == 0);
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testScanByUnsignedFloatValue() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT a_string, /* comment ok? */ b_string, a_unsigned_float FROM aTable WHERE ?=organization_id and ?=a_unsigned_float";
        else if (tgtSQ()||tgtTR()) query = "SELECT a_string, b_string, a_unsigned_float FROM aTable WHERE ?=organization_id and ?=a_unsigned_float";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            statement.setFloat(2, 0.01f);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), A_VALUE);
            assertEquals(rs.getString("B_string"), B_VALUE);
            assertTrue(Float.compare(rs.getFloat(3), 0.01f) == 0);
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testScanByDoubleValue() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT a_string, /* comment ok? */ b_string, a_double FROM aTable WHERE ?=organization_id and ?=a_double";
        else if (tgtSQ()||tgtTR()) query = "SELECT a_string, b_string, a_double FROM aTable WHERE ?=organization_id and ?=a_double";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            statement.setDouble(2, 0.0001);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), A_VALUE);
            assertEquals(rs.getString("B_string"), B_VALUE);
            assertTrue(Double.compare(rs.getDouble(3), 0.0001) == 0);
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testScanByUnsigned_DoubleValue() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT a_string, /* comment ok? */ b_string, a_unsigned_double FROM aTable WHERE ?=organization_id and ?=a_unsigned_double";
        else if (tgtSQ()||tgtTR()) query = "SELECT a_string,b_string, a_unsigned_double FROM aTable WHERE ?=organization_id and ?=a_unsigned_double";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            statement.setDouble(2, 0.0001);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), A_VALUE);
            assertEquals(rs.getString("B_string"), B_VALUE);
            assertTrue(Double.compare(rs.getDouble(3), 0.0001) == 0);
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testAllScan() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT ALL a_string, /* comment ok? */ b_string FROM aTable WHERE ?=organization_id and 5=a_integer";
        else if (tgtSQ()||tgtTR()) query = "SELECT ALL a_string, b_string FROM aTable WHERE ?=organization_id and 5=a_integer";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), B_VALUE);
            assertEquals(rs.getString("B_string"), C_VALUE);
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testDistinctScan() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT DISTINCT a_string FROM aTable WHERE organization_id=?";
        else if (tgtSQ()||tgtTR()) query = "SELECT DISTINCT a_string FROM aTable WHERE organization_id=? order by 1";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), A_VALUE);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), B_VALUE);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), C_VALUE);
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testInListSkipScan() throws Exception {
        printTestDescription();
        initATableValues();
        String query = query = "SELECT entity_id, b_string FROM aTable WHERE organization_id=? and entity_id IN (?,?)";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            statement.setString(2, ROW2);
            statement.setString(3, ROW4);
            ResultSet rs = statement.executeQuery();
            Set<String> expectedvals = new HashSet<String>();
            expectedvals.add(ROW2+"_"+C_VALUE);
            expectedvals.add(ROW4+"_"+B_VALUE);
            Set<String> vals = new HashSet<String>();
            assertTrue (rs.next());
            vals.add(rs.getString(1) + "_" + rs.getString(2));
            assertTrue (rs.next());
            vals.add(rs.getString(1) + "_" + rs.getString(2));
            assertFalse(rs.next());
            assertEquals(expectedvals, vals);
        } finally {
        }
    }

    @Test
    public void testNotInList() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT entity_id FROM aTable WHERE organization_id=? and entity_id NOT IN (?,?,?,?,?,?)";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM aTable WHERE organization_id=? and entity_id NOT IN (?,?,?,?,?,?) order by 1";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            statement.setString(2, ROW2);
            statement.setString(3, ROW4);
            statement.setString(4, ROW1);
            statement.setString(5, ROW5);
            statement.setString(6, ROW7);
            statement.setString(7, ROW8);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(ROW3, rs.getString(1));
            assertTrue (rs.next());
            assertEquals(ROW6, rs.getString(1));
            assertTrue (rs.next());
            assertEquals(ROW9, rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test 
    public void testSumDouble() throws Exception {
        printTestDescription();
        initSumDoubleValues();
        String query = "SELECT SUM(d) FROM SumDoubleTest";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertTrue(Double.compare(rs.getDouble(1), 0.015)==0);
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testSumUnsignedDouble() throws Exception {
        printTestDescription();
        initSumDoubleValues();
        String query = "SELECT SUM(ud) FROM SumDoubleTest";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertTrue(Double.compare(rs.getDouble(1), 0.015)==0);
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testSumFloat() throws Exception {
        printTestDescription();
        initSumDoubleValues();
        String query = "SELECT SUM(f) FROM SumDoubleTest";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertTrue(Float.compare(rs.getFloat(1), 0.15f)==0);
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testSumUnsignedFloat() throws Exception {
        printTestDescription();
        initSumDoubleValues();
        String query = "SELECT SUM(uf) FROM SumDoubleTest";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertTrue(Float.compare(rs.getFloat(1), 0.15f)==0);
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testNotInListOfFloat() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT a_float FROM aTable WHERE organization_id=? and a_float NOT IN (?,?,?,?,?,?)";
        else if (tgtSQ()||tgtTR()) query = "SELECT a_float FROM aTable WHERE organization_id=? and a_float NOT IN (?,?,?,?,?,?) order by 1";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            statement.setFloat(2, 0.01f);
            statement.setFloat(3, 0.02f);
            statement.setFloat(4, 0.03f);
            statement.setFloat(5, 0.04f);
            statement.setFloat(6, 0.05f);
            statement.setFloat(7, 0.06f);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertTrue(Float.compare(rs.getFloat(1), 0.07f)==0);
            assertTrue (rs.next());
            assertTrue(Float.compare(rs.getFloat(1), 0.08f)==0);
            assertTrue (rs.next());
            assertTrue(Float.compare(rs.getFloat(1), 0.09f)==0);
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testNotInListOfDouble() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT a_double FROM aTable WHERE organization_id=? and a_double NOT IN (?,?,?,?,?,?)";
        else if (tgtSQ()||tgtTR()) query = "SELECT a_double FROM aTable WHERE organization_id=? and a_double NOT IN (?,?,?,?,?,?) order by 1";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            statement.setDouble(2, 0.0001);
            statement.setDouble(3, 0.0002);
            statement.setDouble(4, 0.0003);
            statement.setDouble(5, 0.0004);
            statement.setDouble(6, 0.0005);
            statement.setDouble(7, 0.0006);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertTrue(Double.compare(rs.getDouble(1), 0.0007)==0);
            assertTrue (rs.next());
            assertTrue(Double.compare(rs.getDouble(1), 0.0008)==0);
            assertTrue (rs.next());
            assertTrue(Double.compare(rs.getDouble(1), 0.0009)==0);
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testGroupByPlusOne() throws Exception {
        printTestDescription();
        initATableValues();
        String query = "SELECT a_integer+1 FROM aTable WHERE organization_id=? and a_integer = 5 GROUP BY a_integer+1";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(6, rs.getInt(1));
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testNoWhereScan() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT y_integer FROM aTable";
        else if (tgtSQ()||tgtTR()) query = "SELECT y_integer FROM aTable order by 1 desc";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            for (int i =0; i < 8; i++) {
                assertTrue (rs.next());
                assertEquals(0, rs.getInt(1));
                assertTrue(rs.wasNull());
            }
            assertTrue (rs.next());
            assertEquals(300, rs.getInt(1));
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testToDateOnString() throws Exception { // TODO: test more conversion combinations
        printTestDescription();
        initATableValues();
        String query = "SELECT a_string FROM aTable WHERE organization_id=? and a_integer = 5";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            rs.getDate(1);
            fail();
// TRAF        } catch (ConstraintViolationException e) { // Expected
        } catch (Exception e) { // Expected
        } finally {
        }
    }

    @Test
    public void testNotEquals() throws Exception {
        printTestDescription();
        initATableValues();
        String query = "SELECT entity_id -- and here comment\n" +
        "FROM aTable WHERE organization_id=? and a_integer != 1 and a_integer <= 2";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW2);
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testNotEqualsByTinyInt() throws Exception {
        printTestDescription();
        initATableValues();
        String query = "SELECT a_byte -- and here comment\n" +
        "FROM aTable WHERE organization_id=? and a_byte != 1 and a_byte <= 2";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getByte(1), 2);
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testNotEqualsBySmallInt() throws Exception {
        printTestDescription();
        initATableValues();
        String query = "SELECT a_short -- and here comment\n" +
        "FROM aTable WHERE organization_id=? and a_short != 128 and a_short !=0 and a_short <= 129";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getShort(1), 129);
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testNotEqualsByFloat() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT a_float -- and here comment\n" +
        "FROM aTable WHERE organization_id=? and a_float != 0.01d and a_float <= 0.02d";
        else if (tgtSQ()||tgtTR()) query = "SELECT a_float -- and here comment\n" +
        "FROM aTable WHERE organization_id=? and a_float != 0.01 and a_float <= 0.02";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertTrue(Float.compare(rs.getFloat(1), 0.02f) == 0);
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testNotEqualsByUnsignedFloat() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT a_unsigned_float -- and here comment\n" +
        "FROM aTable WHERE organization_id=? and a_unsigned_float != 0.01d and a_unsigned_float <= 0.02d";
        else if (tgtSQ()||tgtTR()) query = "SELECT a_unsigned_float -- and here comment\n" +
        "FROM aTable WHERE organization_id=? and a_unsigned_float != 0.01 and a_unsigned_float <= 0.02";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertTrue(Float.compare(rs.getFloat(1), 0.02f) == 0);
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testNotEqualsByDouble() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT a_double -- and here comment\n" +
        "FROM aTable WHERE organization_id=? and a_double != 0.0001d and a_double <= 0.0002d";
        else if (tgtSQ()||tgtTR()) query = "SELECT a_double -- and here comment\n" +
        "FROM aTable WHERE organization_id=? and a_double != 0.0001 and a_double <= 0.0002";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertTrue(Double.compare(rs.getDouble(1), 0.0002) == 0);
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testNotEqualsByUnsignedDouble() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT a_unsigned_double -- and here comment\n" +
        "FROM aTable WHERE organization_id=? and a_unsigned_double != 0.0001d and a_unsigned_double <= 0.0002d";
        else if (tgtSQ()||tgtTR()) query = "SELECT a_unsigned_double -- and here comment\n" +
        "FROM aTable WHERE organization_id=? and a_unsigned_double != 0.0001 and a_unsigned_double <= 0.0002";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertTrue(Double.compare(rs.getDouble(1), 0.0002) == 0);
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testNotEquals2() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT entity_id FROM // one more comment  \n" +
        "aTable WHERE organization_id=? and not a_integer = 1 and a_integer <= 2";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM -- one more comment  \n" +
        "aTable WHERE organization_id=? and not a_integer = 1 and a_integer <= 2";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW2);
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testColumnOnBothSides() throws Exception {
        printTestDescription();
        initATableValues();
        String query = "SELECT entity_id FROM aTable WHERE organization_id=? and a_string = b_string";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW7);
            assertFalse(rs.next());
        } finally {
        }
    }

    // This is not a test, it is a helper function.
    public void testNoStringValue(String value) throws Exception {
        initATableValues();
        Connection upsertConn = getConnection();
        upsertConn.setAutoCommit(true); // Test auto commit
        PreparedStatement stmt = null;
        if (tgtPH()) {
            stmt = upsertConn.prepareStatement(
            "upsert into ATABLE VALUES (?, ?, ?)"); // without specifying columns
            stmt.setString(1, tenantId);
            stmt.setString(2, ROW5);
            stmt.setString(3, value);
        } else if (tgtTR()) {
            stmt = upsertConn.prepareStatement(
            "upsert into ATABLE (organization_id, entity_id, a_string) VALUES (?, ?, ?)"); // without specifying columns
            stmt.setString(1, tenantId);
            stmt.setString(2, ROW5);
            stmt.setString(3, value);
        } else if (tgtSQ()) {
            stmt = upsertConn.prepareStatement(
            "update ATABLE set a_string = ? where organization_id = ? and entity_id = ?");
            stmt.setString(1, value);
            stmt.setString(2, tenantId);
            stmt.setString(3, ROW5);
        }
        stmt.execute(); // should commit too
        upsertConn.close();

        String query = "SELECT a_string, b_string FROM aTable WHERE organization_id=? and a_integer = 5";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            if (tgtPH()) {
                assertEquals(null, rs.getString(1));
                assertTrue(rs.wasNull());
            } else if (tgtSQ()||tgtTR()) assertEquals(value, rs.getString(1));
            assertEquals(rs.getString("B_string"), C_VALUE);
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test 
    public void testNullStringValue() throws Exception {
        printTestDescription();
        testNoStringValue(null);
    }
  
    @Test
    public void testEmptyStringValue() throws Exception {
        printTestDescription();
        testNoStringValue("");
    }

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

        initATableValues(); 
        Connection upsertConn = getConnection();

        String upsertStmt = null;
        if (tgtPH()||tgtTR()) upsertStmt = 
            "upsert into " +
            "ATABLE(" +
            "    ORGANIZATION_ID, " +
            "    ENTITY_ID, " +
            "    A_INTEGER) " +
            "VALUES (?, ?, ?)";
        else if (tgtSQ()) upsertStmt =
            "update ATABLE set A_INTEGER = ? " +
            "where ORGANIZATION_ID = ? and ENTITY_ID = ?";
        upsertConn.setAutoCommit(true); // Test auto commit
        // Insert all rows at ts
        PreparedStatement stmt = upsertConn.prepareStatement(upsertStmt);
        if (tgtPH()||tgtTR()) {
            stmt.setString(1, tenantId);
            stmt.setString(2, ROW4);
            stmt.setInt(3, 5);
        } else if (tgtSQ()) {
            stmt.setInt(1, 5);
            stmt.setString(2, tenantId);
            stmt.setString(3, ROW4);
        }
        stmt.execute(); // should commit too
        upsertConn.close();

        // Override value again, but should be ignored since it's past the SCN
        // TRAF: We don't have SCN concept
        if (tgtPH()) {
            upsertConn = getConnection();
            upsertConn.setAutoCommit(true); // Test auto commit
            // Insert all rows at ts
            stmt = upsertConn.prepareStatement(upsertStmt);
            stmt.setString(1, tenantId);
            stmt.setString(2, ROW4);
            stmt.setInt(3, 9);
            stmt.execute(); // should commit too
            upsertConn.close();
        }

        String query = null;
        if (tgtPH()) query = "SELECT organization_id, a_string AS a FROM atable WHERE organization_id=? and a_integer = 5";
        else if (tgtSQ()||tgtTR()) query = "SELECT organization_id, a_string AS a FROM atable WHERE organization_id=? and a_integer = 5 order by 2";
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        if (tgtPH()) assertEquals(tenantId, rs.getString(1));
        else if (tgtSQ()||tgtTR()) assertEquals(tenantId, rs.getString(1).trim());
        assertEquals(A_VALUE, rs.getString("a"));
        assertTrue(rs.next());
        if (tgtPH()) assertEquals(tenantId, rs.getString(1));
        else if (tgtSQ()||tgtTR()) assertEquals(tenantId, rs.getString(1).trim());
        assertEquals(B_VALUE, rs.getString(2));
        assertFalse(rs.next());
    }

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

        initATableValues();
        Connection upsertConn = getConnection();

        String upsertStmt = null;
        if (tgtPH()||tgtTR()) upsertStmt = 
            "upsert into " +
            "ATABLE(" +
            "    ORGANIZATION_ID, " +
            "    ENTITY_ID, " +
            "    A_INTEGER) " +
            "VALUES (?, ?, ?)";
        else if (tgtSQ()) upsertStmt =
            "update ATABLE set A_INTEGER = ? " + 
            "where ORGANIZATION_ID = ? and ENTITY_ID = ?";
        upsertConn.setAutoCommit(true); // Test auto commit
        // Insert all rows at ts
        PreparedStatement stmt = upsertConn.prepareStatement(upsertStmt);
        if (tgtPH()||tgtTR()) {
            stmt.setString(1, tenantId);
            stmt.setString(2, ROW1);
            stmt.setInt(3, 6);
        } else if (tgtSQ()) {
            stmt.setInt(1, 6);
            stmt.setString(2, tenantId);
            stmt.setString(3, ROW1);
        }

        stmt.execute(); // should commit too
        upsertConn.close();

        // Override value again, but should be ignored since it's past the SCN
        // TRAF: we don't have this SCN concept.
        if (tgtPH()) {
           upsertConn = getConnection();
           upsertConn.setAutoCommit(true); // Test auto commit
           // Insert all rows at ts
           stmt = upsertConn.prepareStatement(upsertStmt);
           stmt.setString(1, tenantId);
           stmt.setString(2, ROW1);
           stmt.setInt(3, 0);
           stmt.execute(); // should commit too
           upsertConn.close();
        }

        String query = null;
        if (tgtPH()) query = "SELECT a_integer,b_string FROM atable WHERE organization_id=? and a_integer <= 5 limit 2";
        else if (tgtTR()) query = "SELECT a_integer,b_string FROM atable WHERE organization_id=? and a_integer <= 5 order by 1 limit 2";
        else if (tgtSQ()) query = "SELECT [first 2] a_integer,b_string FROM atable WHERE organization_id=? and a_integer <= 5 order by 1";
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals(2, rs.getInt(1));
        assertEquals(C_VALUE, rs.getString(2));
        assertTrue(rs.next());
        assertEquals(3, rs.getInt(1));
        assertEquals(E_VALUE, rs.getString(2));
        assertFalse(rs.next());
    }

    @Test
    public void testUpperLowerBoundRangeScan() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT entity_id FROM aTable WHERE organization_id=? and substr(entity_id,1,3) > '00A' and substr(entity_id,1,3) < '00C'";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM aTable WHERE organization_id=? and substr(entity_id,1,3) > '00A' and substr(entity_id,1,3) < '00C' order by 1";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW5);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW6);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW7);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW8);
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testUpperBoundRangeScan() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT entity_id FROM aTable WHERE organization_id=? and substr(entity_id,1,3) >= '00B' ";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM aTable WHERE organization_id=? and substr(entity_id,1,3) >= '00B' order by 1";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW5);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW6);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW7);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW8);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW9);
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testLowerBoundRangeScan() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT entity_id FROM aTable WHERE organization_id=? and substr(entity_id,1,3) < '00B' ";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM aTable WHERE organization_id=? and substr(entity_id,1,3) < '00B' order by 1";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW1);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW2);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW3);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW4);
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testUnboundRangeScan1() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT entity_id FROM aTable WHERE organization_id <= ?";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM aTable WHERE organization_id <= ? order by 1";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW1);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW2);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW3);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW4);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW5);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW6);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW7);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW8);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW9);
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testUnboundRangeScan2() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT entity_id FROM aTable WHERE organization_id >= ?";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM aTable WHERE organization_id >= ? order by 1";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW1);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW2);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW3);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW4);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW5);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW6);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW7);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW8);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW9);
            assertFalse(rs.next());
        } finally {
        }
    }
    
    // FIXME: this is flapping with an phoenix.memory.InsufficientMemoryException
    // in the GroupedAggregateRegionObserver. We can work around it by increasing
    // the amount of available memory in QueryServicesTestImpl, but we shouldn't
    // have to. I think something may no be being closed to reclaim the memory.
    @Test
    public void testGroupedAggregation() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT a_string, count(1), 'foo' FROM atable WHERE organization_id=? GROUP BY a_string";
        else if (tgtSQ()||tgtTR()) query = "SELECT a_string, count(1), 'foo' FROM atable WHERE organization_id=? GROUP BY a_string order by 1";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(rs.getString(1), A_VALUE);
            assertEquals(rs.getLong(2), 4L);
            assertEquals(rs.getString(3), "foo");
            assertTrue(rs.next());
            assertEquals(rs.getString(1), B_VALUE);
            assertEquals(rs.getLong(2), 4L);
            assertEquals(rs.getString(3), "foo");
            assertTrue(rs.next());
            assertEquals(rs.getString(1), C_VALUE);
            assertEquals(rs.getLong(2), 1L);
            assertEquals(rs.getString(3), "foo");
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testDistinctGroupedAggregation() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT DISTINCT a_string, count(1), 'foo' FROM atable WHERE organization_id=? GROUP BY a_string, b_string ORDER BY a_string, count(1)";
        else if (tgtSQ()||tgtTR()) query = "SELECT DISTINCT a_string, count(1), 'foo' FROM atable WHERE organization_id=? GROUP BY a_string, b_string ORDER BY 1, 2";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            
            assertTrue(rs.next());
            assertEquals(rs.getString(1), A_VALUE);
            assertEquals(rs.getLong(2), 1L);
            assertEquals(rs.getString(3), "foo");
            
            assertTrue(rs.next());
            assertEquals(rs.getString(1), A_VALUE);
            assertEquals(rs.getLong(2), 2L);
            assertEquals(rs.getString(3), "foo");
            
            assertTrue(rs.next());
            assertEquals(rs.getString(1), B_VALUE);
            assertEquals(rs.getLong(2), 1L);
            assertEquals(rs.getString(3), "foo");
            
            assertTrue(rs.next());
            assertEquals(rs.getString(1), B_VALUE);
            assertEquals(rs.getLong(2), 2L);
            assertEquals(rs.getString(3), "foo");
            
            assertTrue(rs.next());
            assertEquals(rs.getString(1), C_VALUE);
            assertEquals(rs.getLong(2), 1L);
            assertEquals(rs.getString(3), "foo");
            
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testDistinctLimitedGroupedAggregation() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT DISTINCT a_string, count(1), 'foo' FROM atable WHERE organization_id=? GROUP BY a_string, b_string ORDER BY count(1) desc,a_string LIMIT 2";
        else if (tgtTR()) query = "SELECT DISTINCT a_string, count(1), 'foo' FROM atable WHERE organization_id=? GROUP BY a_string, b_string ORDER BY 2 desc, 1 LIMIT 2";
        else if (tgtSQ()) query = "SELECT [first 2] DISTINCT a_string, count(1), 'foo' FROM atable WHERE organization_id=? GROUP BY a_string, b_string ORDER BY 2 desc, 1";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            
            assertTrue(rs.next());
            assertEquals(rs.getString(1), A_VALUE);
            assertEquals(rs.getLong(2), 2L);
            assertEquals(rs.getString(3), "foo");
            
            assertTrue(rs.next());
            assertEquals(rs.getString(1), B_VALUE);
            assertEquals(rs.getLong(2), 2L);
            assertEquals(rs.getString(3), "foo");
            
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testDistinctUngroupedAggregation() throws Exception {
        printTestDescription();
        initATableValues();
        String query = "SELECT DISTINCT count(1), 'foo' FROM atable WHERE organization_id=?";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            
            assertTrue(rs.next());
            assertEquals(9L, rs.getLong(1));
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testGroupedLimitedAggregation() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT a_string, count(1) FROM atable WHERE organization_id=? GROUP BY a_string LIMIT 2";
        else if (tgtTR()) query = "SELECT a_string, count(1) FROM atable WHERE organization_id=? GROUP BY a_string order by 1 LIMIT 2";
        else if (tgtSQ()) query = "SELECT [first 2] a_string, count(1) FROM atable WHERE organization_id=? GROUP BY a_string order by 1";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(rs.getString(1), A_VALUE);
            assertEquals(4L, rs.getLong(2));
            assertTrue(rs.next());
            assertEquals(rs.getString(1), B_VALUE);
            assertEquals(4L, rs.getLong(2));
            assertFalse(rs.next());
        } finally {
        }
    }

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

        initATableValues();
        Connection upsertConn = getConnection();

        String updateStmt = null;
        if (tgtPH()) updateStmt =
            "upsert into " +
            "ATABLE VALUES ('" + tenantId + "','" + ROW5 + "','" + C_VALUE +"')";
        else if (tgtTR()) updateStmt =
            "upsert into " +
            "ATABLE (organization_id, entity_id, a_string) VALUES ('" + tenantId + "','" + ROW5 + "','" + C_VALUE +"')";
        else if (tgtSQ()) updateStmt =
            "update ATABLE set a_string = '" + C_VALUE + "' where organization_id = '" + tenantId + "' and entity_id = '" + ROW5 + "'";

        upsertConn.setAutoCommit(true); // Test auto commit
        // Insert all rows at ts
        Statement stmt = upsertConn.createStatement();
        stmt.execute(updateStmt); // should commit too
        upsertConn.close();

        // Override value again, but should be ignored since it's past the SCN
        // TRAF: we don't have this SCN concept.
        if (tgtPH()) {
            upsertConn = getConnection();
            upsertConn.setAutoCommit(true); // Test auto commit
            updateStmt =
                "upsert into " +
                "ATABLE VALUES (?, ?, ?)";
            // Insert all rows at ts
            PreparedStatement pstmt = upsertConn.prepareStatement(updateStmt);
            pstmt.setString(1, tenantId);
            pstmt.setString(2, ROW5);
            pstmt.setString(3, E_VALUE);
            pstmt.execute(); // should commit too
            upsertConn.close();
        }

        String query = null;
        if (tgtPH()) query = "SELECT a_string, count(1) FROM atable WHERE organization_id='" + tenantId + "' GROUP BY a_string";
        else if (tgtSQ()||tgtTR()) query = "SELECT a_string, count(1) FROM atable WHERE organization_id='" + tenantId + "' GROUP BY a_string";
        Statement statement = conn.createStatement();
        ResultSet rs = statement.executeQuery(query);
        assertTrue(rs.next());
        assertEquals(A_VALUE, rs.getString(1));
        assertEquals(4, rs.getInt(2));
        assertTrue(rs.next());
        assertEquals(B_VALUE, rs.getString(1));
        assertEquals(3, rs.getLong(2));
        assertTrue(rs.next());
        assertEquals(C_VALUE, rs.getString(1));
        assertEquals(2, rs.getInt(2));
        assertFalse(rs.next());
    }

    @Test
    public void testUngroupedAggregation() throws Exception {
        printTestDescription();
        initATableValues();
        String query = "SELECT count(1) FROM atable WHERE organization_id=? and a_string = ?";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            statement.setString(2, B_VALUE);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(4, rs.getLong(1));
            assertFalse(rs.next());
        } finally {
        }
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            statement.setString(2, B_VALUE);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(4, rs.getLong(1));
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testUngroupedAggregationNoWhere() throws Exception {
        printTestDescription();
        initATableValues();
        String query = "SELECT count(*) FROM atable";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(9, rs.getLong(1));
            assertFalse(rs.next());
        } finally {
        }
    }

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

        initATableValues();
        Connection upsertConn = getConnection();
        String updateStmt = null;
        if (tgtPH()||tgtTR()) updateStmt = 
            "upsert into " +
            "ATABLE(" +
            "    ORGANIZATION_ID, " +
            "    ENTITY_ID, " +
            "    A_STRING) " +
            "VALUES (?, ?, ?)";
        else if (tgtSQ()) updateStmt =
            "update ATABLE set A_STRING = ? " +
            "where ORGANIZATION_ID = ? and ENTITY_ID = ?";
        // Insert all rows at ts
        PreparedStatement stmt = upsertConn.prepareStatement(updateStmt);
        if (tgtPH()||tgtTR()) {
            stmt.setString(1, tenantId);
            stmt.setString(2, ROW5);
            stmt.setString(3, null);
        } else if (tgtSQ()) {
            stmt.setString(1, null);
            stmt.setString(2, tenantId);
            stmt.setString(3, ROW5);
        }

        stmt.execute();
        if (tgtPH()||tgtTR()) stmt.setString(3, C_VALUE);
        else if (tgtSQ()) stmt.setString(1, C_VALUE);
        stmt.execute();
        if (tgtPH()||tgtTR()) {
            stmt.setString(2, ROW7);
            stmt.setString(3, E_VALUE);
        } else if (tgtSQ()) {
            stmt.setString(1, E_VALUE);
            stmt.setString(3, ROW7);
        }
        stmt.execute();
        upsertConn.close();

        // Override value again, but should be ignored since it's past the SCN
        // TRAF: we don't have the SCN concept
        if (tgtPH()) {
            upsertConn = getConnection();
            upsertConn.setAutoCommit(true); // Test auto commit
            stmt = upsertConn.prepareStatement(updateStmt);
            stmt.setString(1, tenantId);
            stmt.setString(2, ROW6);
            stmt.setString(3, E_VALUE);
            stmt.execute();
            upsertConn.close();
        }
 
        String query = "SELECT count(1) FROM atable WHERE organization_id=? and a_string = ?";
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        statement.setString(2, B_VALUE);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals(2, rs.getLong(1));
        assertFalse(rs.next());
    }

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

        initATableValues();
        String updateStmt = null;
        if (tgtPH()||tgtTR()) updateStmt = 
            "upsert into " +
            "ATABLE(" +
            "    ORGANIZATION_ID, " +
            "    ENTITY_ID, " +
            "    A_STRING) " +
            "VALUES (?, ?, ?)";
        else if (tgtSQ()) updateStmt =
            "update ATABLE set A_STRING = ? " +
            "where ORGANIZATION_ID = ? and ENTITY_ID = ?";
        Connection upsertConn = getConnection();
        upsertConn.setAutoCommit(true); // Test auto commit
        PreparedStatement stmt = upsertConn.prepareStatement(updateStmt);
        if (tgtPH()||tgtTR()) {
            stmt.setString(1, tenantId);
            stmt.setString(2, ROW6);
            stmt.setString(3, C_VALUE);
        } else if (tgtSQ()) {
            stmt.setString(1, C_VALUE);
            stmt.setString(2, tenantId);
            stmt.setString(3, ROW6);
        }
        stmt.execute();
        if (tgtPH()||tgtTR()) stmt.setString(3, E_VALUE);
        else if (tgtSQ()) stmt.setString(1, E_VALUE);
        stmt.execute();
        if (tgtPH()||tgtTR()) stmt.setString(3, B_VALUE);
        else if (tgtSQ()) stmt.setString(1, B_VALUE);
        stmt.execute();
        if (tgtPH()||tgtTR()) stmt.setString(3, B_VALUE);
        else if (tgtSQ()) stmt.setString(1, B_VALUE);
        stmt.execute();
        upsertConn.close();

        // Override value again, but should be ignored since it's past the SCN
        // TRAF: we don't have the SCN concept
        if (tgtPH()) {
           upsertConn = getConnection();
           upsertConn.setAutoCommit(true); // Test auto commit
           stmt = upsertConn.prepareStatement(updateStmt);
           stmt.setString(1, tenantId);
           stmt.setString(2, ROW6);
           stmt.setString(3, E_VALUE);
           stmt.execute();
           upsertConn.close();
        }

        String query = null;
        if (tgtPH()||tgtTR()) query = "SELECT count(1) FROM atable WHERE organization_id=? and a_string = ? LIMIT 3";
        else if (tgtSQ()) query = "SELECT [first 3] count(1) FROM atable WHERE organization_id=? and a_string = ?";
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        statement.setString(2, B_VALUE);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals(4, rs.getLong(1)); // LIMIT applied at end, so all rows would be counted
        assertFalse(rs.next());
    }

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

        initATableValues();
        String updateStmt = null;
        if (tgtPH()||tgtTR()) updateStmt =
            "upsert into " +
            "ATABLE(" +
            "    ORGANIZATION_ID, " +
            "    ENTITY_ID, " +
            "    A_STRING) " +
            "VALUES (?, ?, ?)";
        else if (tgtSQ()) updateStmt =
            "update ATABLE set A_STRING = ? " +
            "where ORGANIZATION_ID = ? and ENTITY_ID = ?";
        // Remove column value at ts + 1 (i.e. equivalent to setting the value to null)
        PreparedStatement stmt = conn.prepareStatement(updateStmt);
        if (tgtPH()||tgtTR()) {
            stmt.setString(1, tenantId);
            stmt.setString(2, ROW7);
            stmt.setString(3, null);
        } else if (tgtSQ()) {
            stmt.setString(1, null);
            stmt.setString(2, tenantId);
            stmt.setString(3, ROW7);
        }
        stmt.execute(); 

        // Delete row 
        stmt = conn.prepareStatement("delete from atable where organization_id=? and entity_id=?");
        stmt.setString(1, tenantId);
        stmt.setString(2, ROW5);
        stmt.execute();
        
        // Delete row at timestamp 3. This should not be seen by the query executing
        // Remove column value at ts + 1 (i.e. equivalent to setting the value to null)
        // TRAF: we don't have the SCN concept
        if (tgtPH()) {
            Connection futureConn = getConnection();
            stmt = futureConn.prepareStatement("delete from atable where organization_id=? and entity_id=?");
            stmt.setString(1, tenantId);
            stmt.setString(2, ROW6);
            stmt.execute();
            futureConn.commit();
            futureConn.close();
        }

        String query = "SELECT count(1) FROM atable WHERE organization_id=? and a_string = ?";
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        statement.setString(2, B_VALUE);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals(2, rs.getLong(1));
        assertFalse(rs.next());
    }

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

        initATableValues();
        String updateStmt = null;
        if (tgtPH()||tgtTR()) updateStmt = 
            "upsert into " +
            "ATABLE(" +
            "    ORGANIZATION_ID, " +
            "    ENTITY_ID, " +
            "    A_INTEGER) " +
            "VALUES (?, ?, ?)";
        else if (tgtSQ()) updateStmt =
            "update ATABLE set A_INTEGER = ? " +
            "where ORGANIZATION_ID = ? and ENTITY_ID = ?";
        Connection upsertConn = getConnection();
        upsertConn.setAutoCommit(true); // Test auto commit
        PreparedStatement stmt = upsertConn.prepareStatement(updateStmt);
        if (tgtPH()||tgtTR()) {
            stmt.setString(1, tenantId);
            stmt.setString(2, ROW4);
            stmt.setInt(3, -10);
        } else if (tgtSQ()) {
            stmt.setInt(1, -10);
            stmt.setString(2, tenantId);
            stmt.setString(3, ROW4);
        }
        stmt.execute();
        upsertConn.close();

        String query = null;
        if (tgtPH()) query = "SELECT entity_id FROM aTable WHERE organization_id=? and a_integer >= ?";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM aTable WHERE organization_id=? and a_integer >= ? order by 1";
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        statement.setInt(2, 7);
        ResultSet rs = statement.executeQuery();
        assertTrue (rs.next());
        assertEquals(rs.getString(1), ROW7);
        assertTrue (rs.next());
        assertEquals(rs.getString(1), ROW8);
        assertTrue (rs.next());
        assertEquals(rs.getString(1), ROW9);
        assertFalse(rs.next());

        if (tgtPH()) query = "SELECT entity_id FROM aTable WHERE organization_id=? and a_integer < 2";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM aTable WHERE organization_id=? and a_integer < 2 order by 1";
        statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        rs = statement.executeQuery();
        assertTrue (rs.next());
        assertEquals(rs.getString(1), ROW1);
        assertTrue (rs.next());
        assertEquals(rs.getString(1), ROW4);
        assertFalse(rs.next());

        if (tgtPH()) query = "SELECT entity_id FROM aTable WHERE organization_id=? and a_integer <= 2";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM aTable WHERE organization_id=? and a_integer <= 2 order by 1";
        statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        rs = statement.executeQuery();
        assertTrue (rs.next());
        assertEquals(rs.getString(1), ROW1);
        assertTrue (rs.next());
        assertEquals(rs.getString(1), ROW2);
        assertTrue (rs.next());
        assertEquals(rs.getString(1), ROW4);
        assertFalse(rs.next());

        query = "SELECT entity_id FROM aTable WHERE organization_id=? and a_integer >=9";
        statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        rs = statement.executeQuery();
        assertTrue (rs.next());
        assertEquals(rs.getString(1), ROW9);
        assertFalse(rs.next());
    }
   
    @Test
    public void testDateAdd() throws Exception {
        printTestDescription();
        initATableValues(new Date(System.currentTimeMillis()));
        String query = null;
        if (tgtPH()) query = "SELECT entity_id, b_string FROM ATABLE WHERE a_date + 0.5d < ?";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id, b_string FROM ATABLE WHERE a_date + 0 < ? order by 1";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setDate(1, new Date(System.currentTimeMillis() + MILLIS_IN_DAY));
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(ROW1, rs.getString(1));
            assertTrue(rs.next());
            assertEquals(ROW4, rs.getString(1));
            assertTrue(rs.next());
            assertEquals(ROW7, rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testDateSubtract() throws Exception {
        printTestDescription();
        initATableValues(new Date(System.currentTimeMillis()));
        String query = null;
        if (tgtPH()) query = "SELECT entity_id, b_string FROM ATABLE WHERE a_date - 0.5d > ?";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id, b_string FROM ATABLE WHERE a_date - 0 > ? order by 1";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setDate(1, new Date(System.currentTimeMillis() + MILLIS_IN_DAY));
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(ROW3, rs.getString(1));
            assertTrue(rs.next());
            assertEquals(ROW6, rs.getString(1));
            assertTrue(rs.next());
            assertEquals(ROW9, rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }

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

        initATableValues();
        String updateStmt = null;
        if (tgtPH()||tgtTR()) updateStmt = 
            "upsert into " +
            "ATABLE(" +
            "    ORGANIZATION_ID, " +
            "    ENTITY_ID, " +
            "    A_TIMESTAMP) " +
            "VALUES (?, ?, ?)";
        else if (tgtSQ()) updateStmt =
            "update ATABLE set A_TIMESTAMP = ? " +
            "where ORGANIZATION_ID = ? and ENTITY_ID = ?";
        Connection upsertConn = getConnection();
        upsertConn.setAutoCommit(true); // Test auto commit
        PreparedStatement stmt = upsertConn.prepareStatement(updateStmt);
        if (tgtPH()||tgtTR()) {
           stmt.setString(1, tenantId);
           stmt.setString(2, ROW4);
           Timestamp tsValue1 = new Timestamp(5000);
           // TRAF byte[] ts1 = PDataType.TIMESTAMP.toBytes(tsValue1);
           stmt.setTimestamp(3, tsValue1);
        } else if (tgtSQ()) {
           Timestamp tsValue1 = new Timestamp(5000);
           stmt.setTimestamp(1, tsValue1);
           stmt.setString(2, tenantId);
           stmt.setString(3, ROW4);
        }
        stmt.execute();

        if (tgtPH()||tgtTR()) updateStmt = 
            "upsert into " +
            "ATABLE(" +
            "    ORGANIZATION_ID, " +
            "    ENTITY_ID, " +
            "    A_TIMESTAMP," +
            "    A_TIME) " +
            "VALUES (?, ?, ?, ?)";
        else if (tgtSQ()) updateStmt = 
            "update ATABLE set (A_TIMESTAMP, A_TIME) = (?, ?) " +
            "where ORGANIZATION_ID = ? and ENTITY_ID = ?";
        stmt = upsertConn.prepareStatement(updateStmt);
        Timestamp tsValue2 = null;
        if (tgtPH()) {
            stmt.setString(1, tenantId);
            stmt.setString(2, ROW5);
            tsValue2 = new Timestamp(5000);
            tsValue2.setNanos(200);
            // TRAF byte[] ts2 = PDataType.TIMESTAMP.toBytes(tsValue2);
            stmt.setTimestamp(3, tsValue2);
            stmt.setTime(4, new Time(tsValue2.getTime()));
        } else if (tgtTR()) {
            tsValue2 = new Timestamp(5000);
            // TRAF tsValue2.setNanos(200);
            // TRAF a narosecond is 1E-9s, our default is 1E-6s, 200 is too
            // small, need to be at least 2000 to be visible for us.
            tsValue2.setNanos(2000);
            stmt.setString(1, tenantId);
            stmt.setString(2, ROW5);
            stmt.setTimestamp(3, tsValue2);
            stmt.setTime(4, new Time(tsValue2.getTime()));
        } else if (tgtSQ()) {
            tsValue2 = new Timestamp(5000);
            // TRAF tsValue2.setNanos(200);
            // TRAF a narosecond is 1E-9s, our default is 1E-6s, 200 is too
            // small, need to be at least 2000 to be visible for us.
            tsValue2.setNanos(2000);
            // TRAF byte[] ts2 = PDataType.TIMESTAMP.toBytes(tsValue2);
            stmt.setTimestamp(1, tsValue2);
            stmt.setTime(2, new Time(tsValue2.getTime()));
            stmt.setString(3, tenantId);
            stmt.setString(4, ROW5);
        }
        stmt.execute();
        upsertConn.close();
        
        String query = "SELECT entity_id, a_timestamp, a_time FROM aTable WHERE organization_id=? and a_timestamp > ?";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            statement.setTimestamp(2, new Timestamp(5000));
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW5);
            assertEquals(rs.getTimestamp("A_TIMESTAMP"), tsValue2);
            assertEquals(rs.getTime("A_TIME"), new Time(tsValue2.getTime()));
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testCoerceTinyIntToSmallInt() throws Exception {
        printTestDescription();
        String query = "SELECT entity_id FROM ATABLE WHERE organization_id=? AND a_byte >= a_short";
        try {
            initATableValues();
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(ROW9, rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testCoerceIntegerToLong() throws Exception {
        printTestDescription();
        String query = null;
        if (tgtPH()) query = "SELECT entity_id FROM ATABLE WHERE organization_id=? AND x_long >= x_integer";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM ATABLE WHERE organization_id=? AND x_long >= x_integer order by 1";
        try {
            initATableValues();
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(ROW7, rs.getString(1));
            assertTrue(rs.next());
            assertEquals(ROW9, rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }
   
    @Test
    public void testCoerceLongToDecimal1() throws Exception {
        printTestDescription();
        String query = "SELECT entity_id FROM ATABLE WHERE organization_id=? AND x_decimal > x_integer";
        try {
            initATableValues();
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(ROW9, rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testCoerceLongToDecimal2() throws Exception {
        printTestDescription();
        String query = "SELECT entity_id FROM ATABLE WHERE organization_id=? AND x_integer <= x_decimal";
        try {
            initATableValues();
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(ROW9, rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testSimpleCaseStatement() throws Exception {
        printTestDescription();
        String query = null;
        if (tgtPH()) query = "SELECT CASE a_integer WHEN 1 THEN 'a' WHEN 2 THEN 'b' WHEN 3 THEN 'c' ELSE 'd' END AS a FROM ATABLE WHERE organization_id=? AND a_integer < 6";
        else if (tgtSQ()||tgtTR()) query = "SELECT CASE a_integer WHEN 1 THEN 'a' WHEN 2 THEN 'b' WHEN 3 THEN 'c' ELSE 'd' END AS a FROM ATABLE WHERE organization_id=? AND a_integer < 6 order by 1";
        try {
            initATableValues();
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals("a", rs.getString(1));
            assertTrue(rs.next());
            assertEquals("b", rs.getString(1));
            assertTrue(rs.next());
            assertEquals("c", rs.getString(1));
            assertTrue(rs.next());
            assertEquals("d", rs.getString(1));
            assertTrue(rs.next());
            assertEquals("d", rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testMultiCondCaseStatement() throws Exception {
        printTestDescription();
        String query = null;
        if (tgtPH()) query = "SELECT CASE WHEN a_integer <= 2 THEN 1.5 WHEN a_integer = 3 THEN 2 WHEN a_integer <= 6 THEN 4.5 ELSE 5 END AS a FROM ATABLE WHERE organization_id=?";
        else if (tgtSQ()||tgtTR()) query = "SELECT CASE WHEN a_integer <= 2 THEN 1.5 WHEN a_integer = 3 THEN 2 WHEN a_integer <= 6 THEN 4.5 ELSE 5 END AS a FROM ATABLE WHERE organization_id=? order by 1";
        try {
            initATableValues();
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            /* TRAF for BigDecimal, really should use compareTo()
            assertEquals(BigDecimal.valueOf(1.5), rs.getBigDecimal(1));
            assertTrue(rs.next());
            assertEquals(BigDecimal.valueOf(1.5), rs.getBigDecimal(1));
            assertTrue(rs.next());
            assertEquals(BigDecimal.valueOf(2), rs.getBigDecimal(1));
            assertTrue(rs.next());
            assertEquals(BigDecimal.valueOf(4.5), rs.getBigDecimal(1));
            assertTrue(rs.next());
            assertEquals(BigDecimal.valueOf(4.5), rs.getBigDecimal(1));
            assertTrue(rs.next());
            assertEquals(BigDecimal.valueOf(4.5), rs.getBigDecimal(1));
            assertTrue(rs.next());
            assertEquals(BigDecimal.valueOf(5), rs.getBigDecimal(1));
            assertTrue(rs.next());
            assertEquals(BigDecimal.valueOf(5), rs.getBigDecimal(1));
            assertTrue(rs.next());
            assertEquals(BigDecimal.valueOf(5), rs.getBigDecimal(1));
            assertFalse(rs.next());
            */
            /* TRAF */
            assertTrue(BigDecimal.valueOf(1.5).compareTo(rs.getBigDecimal(1)) == 0);
            assertTrue(rs.next());
            assertTrue(BigDecimal.valueOf(1.5).compareTo(rs.getBigDecimal(1)) == 0);
            assertTrue(rs.next());
            assertTrue(BigDecimal.valueOf(2).compareTo(rs.getBigDecimal(1)) == 0);
            assertTrue(rs.next());
            assertTrue(BigDecimal.valueOf(4.5).compareTo(rs.getBigDecimal(1)) == 0);
            assertTrue(rs.next());
            assertTrue(BigDecimal.valueOf(4.5).compareTo(rs.getBigDecimal(1)) == 0);
            assertTrue(rs.next());
            assertTrue(BigDecimal.valueOf(4.5).compareTo(rs.getBigDecimal(1)) == 0);
            assertTrue(rs.next());
            assertTrue(BigDecimal.valueOf(5).compareTo(rs.getBigDecimal(1)) == 0);
            assertTrue(rs.next());
            assertTrue(BigDecimal.valueOf(5).compareTo(rs.getBigDecimal(1)) == 0);
            assertTrue(rs.next());
            assertTrue(BigDecimal.valueOf(5).compareTo(rs.getBigDecimal(1)) == 0);
            assertFalse(rs.next());
            /* end of TRAF */
        } finally {
        }
    }
    
    @Test
    public void testPartialEvalCaseStatement() throws Exception {
        printTestDescription();
        String query = "SELECT entity_id FROM ATABLE WHERE organization_id=? and CASE WHEN 1234 = a_integer THEN 1 WHEN x_integer = 5 THEN 2 ELSE 3 END = 2";
        try {
            initATableValues();
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(ROW7, rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }
   
    @Test
    public void testFoundIndexOnPartialEvalCaseStatement() throws Exception {
        printTestDescription();
        String query = "SELECT entity_id FROM ATABLE WHERE organization_id=? and CASE WHEN a_integer = 1234 THEN 1 WHEN x_integer = 3 THEN y_integer ELSE 3 END = 300";
        try {
            initATableValues();
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(ROW9, rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }
    
    // TODO: we need some tests that have multiple versions of key values
    @Test
    public void testUnfoundMultiColumnCaseStatement() throws Exception {
        printTestDescription();
        String query = "SELECT entity_id, b_string FROM ATABLE WHERE organization_id=? and CASE WHEN a_integer = 1234 THEN 1 WHEN a_date < ? THEN y_integer WHEN x_integer = 4 THEN 4 ELSE 3 END = 4";
        try {
            initATableValues();
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            statement.setDate(2, new Date(System.currentTimeMillis()));
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(ROW8, rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }
   
    @Test
    public void testUnfoundSingleColumnCaseStatement() throws Exception {
        printTestDescription();

        String query = "SELECT entity_id, b_string FROM ATABLE WHERE organization_id=? and CASE WHEN a_integer = 0 or a_integer != 0 THEN 1 ELSE 0 END = 0";
        initATableValues();
        // Set ROW5.A_INTEGER to null so that we have one row
        // where the else clause of the CASE statement will
        // fire.
        Connection upsertConn = getConnection();
        String upsertStmt = null;
        if (tgtPH()||tgtTR()) upsertStmt = 
            "upsert into " +
            "ATABLE(" +
            "    ENTITY_ID, " +
            "    ORGANIZATION_ID, " +
            "    A_INTEGER) " +
            "VALUES ('" + ROW5 + "','" + tenantId + "', null)";
        else if (tgtSQ()) upsertStmt =
            "update ATABLE set A_INTEGER = null " + 
            "where ENTITY_ID = '" + ROW5 +"' and ORGANIZATION_ID = '" + tenantId + "'";

        upsertConn.setAutoCommit(true); // Test auto commit
        // Insert all rows at ts
        PreparedStatement stmt = upsertConn.prepareStatement(upsertStmt);
        stmt.execute(); // should commit too
        upsertConn.close();
        
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setString(1, tenantId);
        ResultSet rs = statement.executeQuery();
        assertTrue(rs.next());
        assertEquals(ROW5, rs.getString(1));
        assertFalse(rs.next());
    }
  
    @Test
    public void testNonNullMultiCondCaseStatement() throws Exception {
        printTestDescription();
        String query = "SELECT CASE WHEN entity_id = '000000000000000' THEN 1 WHEN entity_id = '000000000000001' THEN 2 ELSE 3 END FROM ATABLE WHERE organization_id=?";
        try {
            initATableValues();
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            ResultSetMetaData rsm = rs.getMetaData();
            assertEquals(ResultSetMetaData.columnNoNulls,rsm.isNullable(1));
        } finally {
        }
    }
    
    @Test
    public void testNullMultiCondCaseStatement() throws Exception {
        printTestDescription();
        String query = "SELECT CASE WHEN entity_id = '000000000000000' THEN 1 WHEN entity_id = '000000000000001' THEN 2 END FROM ATABLE WHERE organization_id=?";
        try {
            initATableValues();
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            ResultSetMetaData rsm = rs.getMetaData();
            assertEquals(ResultSetMetaData.columnNullable,rsm.isNullable(1));
        } finally {
        }
    }
 
    @Test
    public void testNullabilityMultiCondCaseStatement() throws Exception {
        printTestDescription();
        String query = null;
        if (tgtPH()) query = "SELECT CASE WHEN a_integer <= 2 THEN ? WHEN a_integer = 3 THEN ? WHEN a_integer <= ? THEN ? ELSE 5 END AS a FROM ATABLE WHERE organization_id=?";
        // TRAF: In both SQ and TRAF, that 'ELSE 5' will force the parameter
        // replacement to become integer, and the fetching back would be 1
        // instead of 1.5.  This is true for both JDBC and hpdci if using
        // 'set param ?p'  change it to 5.0 to accomodate this behavior
        else if (tgtSQ()||tgtTR()) query = "SELECT CASE WHEN a_integer <= 2 THEN ? WHEN a_integer = 3 THEN ? WHEN a_integer <= ? THEN ? ELSE 5.0 END AS a FROM ATABLE WHERE organization_id=? order by 1";
        try {
            initATableValues();
            PreparedStatement statement = conn.prepareStatement(query);
            // TRAF DEBUG This test fails on SQ right now.  But if you don't
            // do the following parameter subsitute, execute the above 
            // commented-out query directly, it works fine.  There is something
            // strange about our JDBC's parameter subsitute.
            statement.setBigDecimal(1,BigDecimal.valueOf(1.5));
            statement.setInt(2,2);
            statement.setInt(3,6);
            statement.setBigDecimal(4,BigDecimal.valueOf(4.5));
            statement.setString(5, tenantId);
            // TRAF DEBUG end
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            if (tgtPH()) {
                assertEquals(BigDecimal.valueOf(1.5), rs.getBigDecimal(1));
                assertTrue(rs.next());
                assertEquals(BigDecimal.valueOf(1.5), rs.getBigDecimal(1));
                assertTrue(rs.next());
                assertEquals(BigDecimal.valueOf(2), rs.getBigDecimal(1));
                assertTrue(rs.next());
                assertEquals(BigDecimal.valueOf(4.5), rs.getBigDecimal(1));
                assertTrue(rs.next());
                assertEquals(BigDecimal.valueOf(4.5), rs.getBigDecimal(1));
                assertTrue(rs.next());
                assertEquals(BigDecimal.valueOf(4.5), rs.getBigDecimal(1));
                assertTrue(rs.next());
                assertEquals(BigDecimal.valueOf(5), rs.getBigDecimal(1));
                assertTrue(rs.next());
                assertEquals(BigDecimal.valueOf(5), rs.getBigDecimal(1));
                assertTrue(rs.next());
                assertEquals(BigDecimal.valueOf(5), rs.getBigDecimal(1));
                assertFalse(rs.next());
            } else if (tgtSQ()||tgtTR()) {
                assertTrue(BigDecimal.valueOf(1.5).compareTo(rs.getBigDecimal(1)) == 0);
                assertTrue(rs.next());
                assertTrue(BigDecimal.valueOf(1.5).compareTo(rs.getBigDecimal(1)) == 0);
                assertTrue(rs.next());
                assertTrue(BigDecimal.valueOf(2).compareTo(rs.getBigDecimal(1)) == 0);
                assertTrue(rs.next());
                assertTrue(BigDecimal.valueOf(4.5).compareTo(rs.getBigDecimal(1)) == 0);
                assertTrue(rs.next());
                assertTrue(BigDecimal.valueOf(4.5).compareTo(rs.getBigDecimal(1)) == 0);
                assertTrue(rs.next());
                assertTrue(BigDecimal.valueOf(4.5).compareTo(rs.getBigDecimal(1)) == 0);
                assertTrue(rs.next());
                assertTrue(BigDecimal.valueOf(5).compareTo(rs.getBigDecimal(1)) == 0);
                assertTrue(rs.next());
                assertTrue(BigDecimal.valueOf(5).compareTo(rs.getBigDecimal(1)) == 0);
                assertTrue(rs.next());
                assertTrue(BigDecimal.valueOf(5).compareTo(rs.getBigDecimal(1)) == 0);
                assertFalse(rs.next());
            }
        } finally {
        }
    }
   
    @Test
    public void testSimpleInListStatement() throws Exception {
        printTestDescription();
        String query = null;
        if (tgtPH()) query = "SELECT entity_id FROM ATABLE WHERE organization_id=? AND a_integer IN (2,4)";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM ATABLE WHERE organization_id=? AND a_integer IN (2,4) order by 1";
        try {
            initATableValues();
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(ROW2, rs.getString(1));
            assertTrue(rs.next());
            assertEquals(ROW4, rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }
    
    /**
     * Test to repro Null Pointer Exception
     * @throws Exception
     */
    @Test
    public void testInFilterOnKey() throws Exception {
        printTestDescription();
        String query = "SELECT count(entity_id) FROM ATABLE WHERE organization_id IN (?,?)";
        try {
            initATableValues();
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            statement.setString(2, tenantId);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(9, rs.getInt(1));
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testOneInListStatement() throws Exception {
        printTestDescription();
        String query = null;
        if (tgtPH()) query = "SELECT entity_id FROM ATABLE WHERE organization_id=? AND b_string IN (?)";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM ATABLE WHERE organization_id=? AND b_string IN (?) order by 1";
        try {
            initATableValues();
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            statement.setString(2, E_VALUE);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(ROW3, rs.getString(1));
            assertTrue(rs.next());
            assertEquals(ROW6, rs.getString(1));
            assertTrue(rs.next());
            assertEquals(ROW9, rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testMixedTypeInListStatement() throws Exception {
        printTestDescription();
        String query = null;
        if (tgtPH()) query = "SELECT entity_id FROM ATABLE WHERE organization_id=? AND x_long IN (5, ?)";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM ATABLE WHERE organization_id=? AND x_long IN (5, ?) order by 1";
        try {
            initATableValues();
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            long l = Integer.MAX_VALUE + 1L;
            statement.setLong(2, l);
            ResultSet rs = statement.executeQuery();
            assertTrue(rs.next());
            assertEquals(ROW7, rs.getString(1));
            assertTrue(rs.next());
            assertEquals(ROW9, rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testIsNull() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT entity_id FROM aTable WHERE X_DECIMAL is null";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM aTable WHERE X_DECIMAL is null order by 1";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW1);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW2);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW3);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW4);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW5);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW6);
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testCountIsNull() throws Exception {
        printTestDescription();
        initATableValues();
        String query = "SELECT count(1) FROM aTable WHERE X_DECIMAL is null";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(6, rs.getLong(1));
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testCountIsNotNull() throws Exception {
        printTestDescription();
        initATableValues();
        String query = "SELECT count(1) FROM aTable WHERE X_DECIMAL is not null";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(3, rs.getLong(1));
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testIsNotNull() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT entity_id FROM aTable WHERE X_DECIMAL is not null";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM aTable WHERE X_DECIMAL is not null order by 1";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW7);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW8);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW9);
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testIntSubtractionExpression() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT entity_id FROM aTable where A_INTEGER - 4  <= 0";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM aTable where A_INTEGER - 4  <= 0 order by 1";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW1);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW2);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW3);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW4);
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testDecimalSubtraction1Expression() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT entity_id FROM aTable where A_INTEGER - 3.5  <= 0";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM aTable where A_INTEGER - 3.5  <= 0 order by 1";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(ROW1, rs.getString(1));
            assertTrue (rs.next());
            assertEquals(ROW2, rs.getString(1));
            assertTrue (rs.next());
            assertEquals(ROW3, rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testDecimalSubtraction2Expression() throws Exception {// check if decimal part makes a difference
        printTestDescription();
        initATableValues();
        String query = "SELECT entity_id FROM aTable where X_DECIMAL - 3.5  > 0";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW8);
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testLongSubtractionExpression() throws Exception {
        printTestDescription();
        initATableValues();
        String query = "SELECT entity_id FROM aTable where X_LONG - 1  < 0";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW8);
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testDoubleSubtractionExpression() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT entity_id FROM aTable where a_double - 0.0002d  < 0";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM aTable where a_double - 0.0002  < 0";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW1);
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testSmallIntSubtractionExpression() throws Exception {
        printTestDescription();
        initATableValues();
        String query = "SELECT entity_id FROM aTable where a_short - 129  = 0";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW2);
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testTernarySubtractionExpression() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT entity_id FROM aTable where  X_INTEGER - X_LONG - 10  < 0";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM aTable where  X_INTEGER - X_LONG - 10  < 0 order by 1";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW7);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW9);
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testSelectWithSubtractionExpression() throws Exception {
        printTestDescription();
        initATableValues();
        String query = "SELECT entity_id, x_integer - 4 FROM aTable where  x_integer - 4 = 0";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW8);
            assertEquals(rs.getInt(2), 0);
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testConstantSubtractionExpression() throws Exception {
        printTestDescription();
        initATableValues();
        String query = "SELECT entity_id FROM aTable where A_INTEGER = 5 - 1 - 2";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW2);
            assertFalse(rs.next());
        } finally {
        }
    }
 
    @Test
    public void testIntDivideExpression() throws Exception {
        printTestDescription();
        initATableValues();
        // TRAF: In both SQ and TRAF, SQ treates A_INTEGER/3 as a floating
        // point.  A_INTEGER/3>2 is TRUE for A_INTEGER=7,8,9, so this query 
        // returns 3 rows. Pheonix treats it as an integer and it only returns 
        // 1 row when A_INTEGER=9. It has been confirmed that this behavior
        // will not change in TRAF.  Changed the following query so that it
        // would return the same row. 
        String query = null;
        if (tgtPH()) query = "SELECT entity_id FROM aTable where A_INTEGER / 3 > 2";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM aTable where A_INTEGER / 3 > 2.9999";
 
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(ROW9, rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testDoubleDivideExpression() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT entity_id FROM aTable where a_double / 3.0d = 0.0003";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM aTable where a_double / 3.0 = 0.0003";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(ROW9, rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testSmallIntDivideExpression() throws Exception {
        printTestDescription();
        initATableValues();
        String query = "SELECT entity_id FROM aTable where a_short / 135 = 1";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(ROW8, rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testIntToDecimalDivideExpression() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT entity_id FROM aTable where A_INTEGER / 3.0 > 2";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM aTable where A_INTEGER / 3.0 > 2 order by 1";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(ROW7, rs.getString(1));
            assertTrue (rs.next());
            assertEquals(ROW8, rs.getString(1));
            assertTrue (rs.next());
            assertEquals(ROW9, rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testConstantDivideExpression() throws Exception {
        printTestDescription();
        initATableValues();
        String query = "SELECT entity_id FROM aTable where A_INTEGER = 9 / 3 / 3";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW1);
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testSelectWithDivideExpression() throws Exception {
        printTestDescription();
        initATableValues();
        String query = "SELECT entity_id, a_integer/3 FROM aTable where  a_integer = 9";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(ROW9, rs.getString(1));
            assertEquals(3, rs.getInt(2));
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testNegateExpression() throws Exception {
        printTestDescription();
        initATableValues();
        String query = "SELECT entity_id FROM aTable where A_INTEGER - 4 = -1";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(ROW3, rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testIntMultiplyExpression() throws Exception {
        printTestDescription();
        initATableValues();
        String query = "SELECT entity_id FROM aTable where A_INTEGER * 2 = 16";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(ROW8, rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }
   
    @Test
    public void testDoubleMultiplyExpression() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT entity_id FROM aTable where A_DOUBLE * 2.0d = 0.0002";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM aTable where A_DOUBLE * 2.0 = 0.0002";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(ROW1, rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testLongMultiplyExpression() throws Exception {
        printTestDescription();
        initATableValues();
        String query = "SELECT entity_id FROM aTable where X_LONG * 2 * 2 = 20";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(ROW7, rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testIntToDecimalMultiplyExpression() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT entity_id FROM aTable where A_INTEGER * 1.5 > 9";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM aTable where A_INTEGER * 1.5 > 9 order by 1";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(ROW7, rs.getString(1));
            assertTrue (rs.next());
            assertEquals(ROW8, rs.getString(1));
            assertTrue (rs.next());
            assertEquals(ROW9, rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testDecimalMultiplyExpression() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT entity_id FROM aTable where X_DECIMAL * A_INTEGER > 29.5";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM aTable where X_DECIMAL * A_INTEGER > 29.5 order by 1";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(ROW8, rs.getString(1));
            assertTrue (rs.next());
            assertEquals(ROW9, rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testIntAddExpression() throws Exception {
        printTestDescription();
        initATableValues();
        String query = query = "SELECT entity_id FROM aTable where A_INTEGER + 2 = 4";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(ROW2, rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }

    @Test
    public void testDecimalAddExpression() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT entity_id FROM aTable where A_INTEGER + X_DECIMAL > 11";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM aTable where A_INTEGER + X_DECIMAL > 11 order by 1";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(ROW8, rs.getString(1));
            assertTrue (rs.next());
            assertEquals(ROW9, rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testDoubleAddExpression() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT entity_id FROM aTable where a_double + a_float > 0.08";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM aTable where a_double + a_float > 0.08 order by 1";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(ROW8, rs.getString(1));
            assertTrue (rs.next());
            assertEquals(ROW9, rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testUnsignedDoubleAddExpression() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT entity_id FROM aTable where a_unsigned_double + a_unsigned_float > 0.08";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM aTable where a_unsigned_double + a_unsigned_float > 0.08 order by 1";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(ROW8, rs.getString(1));
            assertTrue (rs.next());
            assertEquals(ROW9, rs.getString(1));
            assertFalse(rs.next());
        } finally {
        }
    }
   
/*  
    @edu.umd.cs.findbugs.annotations.SuppressWarnings(
            value="RV_RETURN_VALUE_IGNORED",
            justification="Test code.")
*/
    @Test
    public void testValidArithmetic() throws Exception {
        printTestDescription();
        initATableValues();
        String[] queries = null;
        if (tgtPH()) queries = new String[] { 
                "SELECT entity_id,organization_id FROM atable where (A_DATE - A_DATE) * 5 < 0",
                "SELECT entity_id,organization_id FROM atable where 1 + A_DATE  < A_DATE",
                "SELECT entity_id,organization_id FROM atable where A_DATE - 1 < A_DATE",
                "SELECT entity_id,organization_id FROM atable where A_INTEGER - 45 < 0",
                "SELECT entity_id,organization_id FROM atable where X_DECIMAL / 45 < 0", };
        else if (tgtSQ()||tgtTR()) queries = new String[] {
                "SELECT entity_id,organization_id FROM atable where (A_DATE - A_DATE) * 5 < interval '0' day",
                "SELECT entity_id,organization_id FROM atable where interval '1' day + A_DATE  < A_DATE",
                "SELECT entity_id,organization_id FROM atable where A_DATE - 1 < A_DATE",
                "SELECT entity_id,organization_id FROM atable where A_INTEGER - 45 < 0",
                "SELECT entity_id,organization_id FROM atable where X_DECIMAL / 45 < 0", };

        for (String query : queries) {
            try {
                PreparedStatement statement = conn.prepareStatement(query);
                statement.executeQuery();
            }
            finally {
            }
        }
    }
   
    @Test 
    public void testValidStringConcatExpression() throws Exception {//test fails with stack overflow wee
        printTestDescription();

        int counter=0;
        initATableValues();
        String[] answers = null;
        if (tgtPH()) answers = new String[]{"00D300000000XHP5bar","a5bar","5bar","15bar","5bar","5bar"};
        // TRAF in both TRAF and SQ, null||'5'||'bar' is null,
        // not '5bar'.  a_date in the table are all null, so
        // we have to change the 3rd, 5th, and 6th answer to null for
        // this reason. 
        else if (tgtSQ()||tgtTR()) answers = new String[]{"TRAF5bar","a5bar",/* "5bar" */ null,"15bar",/* "5bar" */ null , /* "5bar" */ null};
        String[] queries = null;
        if (tgtPH()) queries = new String[] { 
        		"SELECT  organization_id || 5 || 'bar' FROM atable limit 1",
        		"SELECT a_string || 5 || 'bar' FROM atable limit 1",
        		"SELECT a_date||5||'bar' FROM atable limit 1",
        		"SELECT a_integer||5||'bar' FROM atable limit 1",
        		"SELECT x_decimal||5||'bar' FROM atable limit 1",
        		"SELECT x_long||5||'bar' FROM atable limit 1"
        };
        // TRAF: null||'5'||'bar' in both SQ and TRAF is null, not '5bar'
        else if (tgtTR()) queries = new String[] {
                        "SELECT RTRIM(organization_id) || '5' || 'bar' FROM atable order by 1 limit 1",
                        "SELECT a_string || '5' || 'bar' FROM atable order by 1 limit 1",
                        "SELECT RTRIM(CAST(a_date as CHAR(128)))||'5'||'bar' FROM atable order by 1 limit 1",
                        "SELECT RTRIM(CAST(a_integer as CHAR(128)))||'5'||'bar' FROM atable order by 1 limit 1",
                        "SELECT RTRIM(CAST(x_decimal as CHAR(128)))||'5'||'bar' FROM atable order by 1 desc limit 1",
                        "SELECT RTRIM(CAST(x_long as CHAR(128)))||'5'||'bar' FROM atable order by 1 desc limit 1"
        };
        // TRAF: null||'5'||'bar' in both SQ and TRAF is null, not '5bar'
        else if (tgtSQ()) queries = new String[] {
                        "SELECT [first 1] RTRIM(organization_id) || '5' || 'bar' FROM atable order by 1",
                        "SELECT [first 1] a_string || '5' || 'bar' FROM atable order by 1",
                        "SELECT [first 1] RTRIM(CAST(a_date as CHAR(128)))||'5'||'bar' FROM atable order by 1",
                        "SELECT [first 1] RTRIM(CAST(a_integer as CHAR(128)))||'5'||'bar' FROM atable order by 1",
                        "SELECT [first 1] RTRIM(CAST(x_decimal as CHAR(128)))||'5'||'bar' FROM atable order by 1 desc",
                        "SELECT [first 1] RTRIM(CAST(x_long as CHAR(128)))||'5'||'bar' FROM atable order by 1 desc"
        };

        for (String query : queries) {
        	try {
        		PreparedStatement statement = conn.prepareStatement(query);
        		ResultSet rs=statement.executeQuery();
        		assertTrue(rs.next());
        		assertEquals(answers[counter++],rs.getString(1));
           		assertFalse(rs.next());
        	}
        	finally {
        	}
        }
    }
    
    @Test 
    public void testStartKeyStopKey() throws SQLException {
        printTestDescription(); 
        // TRAF SKIP - SQ does not support SPLIT ON
        if (tgtSQ()||tgtTR()) return;

        conn.createStatement().execute("CREATE TABLE start_stop_test (pk char(2) not null primary key) SPLIT ON ('EA','EZ')");
        
        String query = null;
        if (tgtPH()) query = "select count(*) from start_stop_test where pk >= 'EA' and pk < 'EZ'";
        else if (tgtSQ()||tgtTR()) query = "select count(*) from start_stop_test where pk >= 'EA' and pk < 'EZ'";
        Statement statement = conn.createStatement();
        statement.execute(query);
        // TRAF PhoenixStatement pstatement = statement.unwrap(PhoenixStatement.class);
        // TRAF List<KeyRange>splits = pstatement.getQueryPlan().getSplits();
        // TRAF assertTrue(splits.size() > 0);
    }
    
    @Test
    public void testRowKeySingleIn() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT entity_id FROM aTable WHERE organization_id=? and entity_id IN (?,?,?)";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM aTable WHERE organization_id=? and entity_id IN (?,?,?) order by 1";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            statement.setString(2, ROW2);
            statement.setString(3, ROW6);
            statement.setString(4, ROW8);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW2);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW6);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW8);
            assertFalse(rs.next());
        } finally {
        }
    }
    
    @Test
    public void testRowKeyMultiIn() throws Exception {
        printTestDescription();
        initATableValues();
        String query = null;
        if (tgtPH()) query = "SELECT entity_id FROM aTable WHERE organization_id=? and entity_id IN (?,?,?) and a_string IN (?,?)";
        else if (tgtSQ()||tgtTR()) query = "SELECT entity_id FROM aTable WHERE organization_id=? and entity_id IN (?,?,?) and a_string IN (?,?) order by 1";
        try {
            PreparedStatement statement = conn.prepareStatement(query);
            statement.setString(1, tenantId);
            statement.setString(2, ROW2);
            statement.setString(3, ROW6);
            statement.setString(4, ROW9);
            statement.setString(5, B_VALUE);
            statement.setString(6, C_VALUE);
            ResultSet rs = statement.executeQuery();
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW6);
            assertTrue (rs.next());
            assertEquals(rs.getString(1), ROW9);
            assertFalse(rs.next());
        } finally {
        }
    }
}