/*******************************************************************************
 * 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.sql.*;
import java.util.*;
import java.math.*;

public class ArithmeticQueryTest 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 testDecimalArithmetic", "table testDecimalArithmatic", "table source", "table target"));
        doBaseTestSuiteSetup();
    }
    /* @AfterClass, @Before, @After are defined in BaseTest */

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

        try {
            String ddl = null;
            if (tgtPH()) ddl = "CREATE TABLE IF NOT EXISTS testDecimalArithmetic" + 
                               "  (pk VARCHAR NOT NULL PRIMARY KEY, " +
                               "col1 DECIMAL(31,0), col2 DECIMAL(5), col3 DECIMAL(5,2), col4 DECIMAL)";
            else if (tgtTR()) ddl = "CREATE TABLE IF NOT EXISTS testDecimalArithmetic" +
                                    "  (pk VARCHAR(128) NOT NULL PRIMARY KEY, " +
                                    "col1 DECIMAL(18,0), col2 DECIMAL(5), col3 DECIMAL(5,2), col4 DECIMAL)";
            else if (tgtSQ()) ddl = "CREATE TABLE testDecimalArithmetic" +
                                    "  (pk VARCHAR(128) NOT NULL PRIMARY KEY, " +
                                    "col1 DECIMAL(18,0), col2 DECIMAL(5), col3 DECIMAL(5,2), col4 DECIMAL)";

            conn.createStatement().execute(ddl);

            conn.setAutoCommit(false);
 
            // Test upsert correct values 
            String query = null;
            if (tgtPH()||tgtTR()) query = "UPSERT INTO testDecimalArithmetic(pk, col1, col2, col3, col4) VALUES(?,?,?,?,?)";
            else if (tgtSQ()) query = "INSERT INTO testDecimalArithmetic(pk, col1, col2, col3, col4) VALUES(?,?,?,?,?)";

            PreparedStatement stmt = conn.prepareStatement(query);
            stmt.setString(1, "valueOne");
            stmt.setBigDecimal(2, new BigDecimal("123456789123456789"));
            stmt.setBigDecimal(3, new BigDecimal("12345"));
            stmt.setBigDecimal(4, new BigDecimal("12.34"));
            stmt.setBigDecimal(5, new BigDecimal("12345.6789"));
            stmt.execute();
            conn.commit();
           
            query = "SELECT col1, col2, col3, col4 FROM testDecimalArithmetic WHERE pk = 'valueOne'";
            stmt = conn.prepareStatement(query);
            ResultSet rs = stmt.executeQuery();
            assertTrue(rs.next());
            assertEquals(new BigDecimal("123456789123456789"), rs.getBigDecimal(1));
            assertEquals(new BigDecimal("12345"), rs.getBigDecimal(2));
            assertEquals(new BigDecimal("12.34"), rs.getBigDecimal(3));
            if (tgtPH()) assertEquals(new BigDecimal("12345.6789"), rs.getBigDecimal(4));
            else if (tgtSQ()||tgtTR()) assertTrue(rs.getBigDecimal(4).equals(new BigDecimal("12345")) || rs.getBigDecimal(4).equals(new BigDecimal("12346")));
            assertFalse(rs.next());
           
            if (tgtPH()||tgtTR()) query = "UPSERT INTO testDecimalArithmetic(pk, col1, col2, col3) VALUES(?,?,?,?)";
            else if (tgtSQ()) query = "INSERT INTO testDecimalArithmetic(pk, col1, col2, col3) VALUES(?,?,?,?)";
 
            stmt = conn.prepareStatement(query);
            stmt.setString(1, "valueTwo");
            if (tgtPH()) stmt.setBigDecimal(2, new BigDecimal("1234567890123456789012345678901.12345"));
            else if (tgtSQ()||tgtTR()) stmt.setBigDecimal(2, new BigDecimal("123456789012345678.12345"));
            stmt.setBigDecimal(3, new BigDecimal("12345.6789"));
            stmt.setBigDecimal(4, new BigDecimal("123.45678"));
            stmt.execute();
            conn.commit();
            
            query = "SELECT col1, col2, col3 FROM testDecimalArithmetic WHERE pk = 'valueTwo'";
            stmt = conn.prepareStatement(query);
            rs = stmt.executeQuery();
            assertTrue(rs.next());
            if (tgtPH()) assertEquals(new BigDecimal("1234567890123456789012345678901"), rs.getBigDecimal(1));
            else if (tgtSQ()||tgtTR()) assertEquals(new BigDecimal("123456789012345678"), rs.getBigDecimal(1));
            assertTrue(rs.getBigDecimal(2).equals(new BigDecimal("12345")) || rs.getBigDecimal(2).equals(new BigDecimal("12346")));
            assertTrue(rs.getBigDecimal(3).equals(new BigDecimal("123.45")) || rs.getBigDecimal(3).equals(new BigDecimal("123.46")));
            assertFalse(rs.next());
            
            // Test upsert incorrect values and confirm exceptions would be thrown.
            try {
                if (tgtPH()||tgtTR()) query = "UPSERT INTO testDecimalArithmetic(pk, col1, col2, col3) VALUES(?,?,?,?)";
                else if (tgtSQ()) query = "INSERT INTO testDecimalArithmetic(pk, col1, col2, col3) VALUES(?,?,?,?)";
                stmt = conn.prepareStatement(query);
                stmt.setString(1, "badValues");
                // one more than max_precision
                stmt.setBigDecimal(2, new BigDecimal("12345678901234567890123456789012"));
                stmt.setBigDecimal(3, new BigDecimal("12345")); 
                stmt.setBigDecimal(4, new BigDecimal("123.45"));
                stmt.execute();
                conn.commit();
                fail("Should have caught bad values.");
            } catch (Exception e) {
                if (tgtPH()) assertTrue(e.getMessage(), e.getMessage().contains("ERROR 206 (22003): The value is outside the range for the data type. value=12345678901234567890123456789012 columnName=COL1"));
                // This error message is different between T2 and T4.
                // Only make sure that we get an exception now.
                // else if (tgtSQ()||tgtTR()) assertTrue(e.getMessage(), e.getMessage().contains("*** ERROR[29188]"));
            }
            try {
                if (tgtPH()||tgtTR()) query = "UPSERT INTO testDecimalArithmetic(pk, col1, col2, col3) VALUES(?,?,?,?)";
                else if (tgtSQ()) query = "INSERT INTO testDecimalArithmetic(pk, col1, col2, col3) VALUES(?,?,?,?)";
                stmt = conn.prepareStatement(query);
                stmt.setString(1, "badValues");
                stmt.setBigDecimal(2, new BigDecimal("123456"));
                // Exceeds specified precision by 1
                stmt.setBigDecimal(3, new BigDecimal("123456"));
                stmt.setBigDecimal(4, new BigDecimal("123.45"));
                stmt.execute();
                conn.commit();
                fail("Should have caught bad values.");
            } catch (Exception e) {
                if (tgtPH()) assertTrue(e.getMessage(), e.getMessage().contains("ERROR 206 (22003): The value is outside the range for the data type. value=123456 columnName=COL2"));
                // This error message is different between T2 and T4.
                // Only make sure that we get an exception now.
                // else if (tgtSQ()||tgtTR()) assertTrue(e.getMessage(), e.getMessage().contains("*** ERROR[29188]"));
            }
        } finally {
        }
    }

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

        try {
            String ddl = null;
            if (tgtPH()) ddl = "CREATE TABLE IF NOT EXISTS source" + 
                               " (pk VARCHAR NOT NULL PRIMARY KEY, col1 DECIMAL(5,2), col2 DECIMAL(5,1), col3 DECIMAL(5,2), col4 DECIMAL(4,4))";
            else if (tgtTR()) ddl = "CREATE TABLE IF NOT EXISTS source" +
                                    " (pk VARCHAR(128) NOT NULL PRIMARY KEY, col1 DECIMAL(5,2), col2 DECIMAL(5,1), col3 DECIMAL(5,2), col4 DECIMAL(4,4))";
            else if (tgtSQ()) ddl = "CREATE TABLE source" +
                                    " (pk VARCHAR(128) NOT NULL PRIMARY KEY, col1 DECIMAL(5,2), col2 DECIMAL(5,1), col3 DECIMAL(5,2), col4 DECIMAL(4,4))";

            conn.createStatement().execute(ddl);

            if (tgtPH()) ddl = "CREATE TABLE IF NOT EXISTS target" + 
                               " (pk VARCHAR NOT NULL PRIMARY KEY, col1 DECIMAL(5,1), col2 DECIMAL(5,2), col3 DECIMAL(4,4))";
            else if (tgtTR()) ddl = "CREATE TABLE IF NOT EXISTS target" +
                                    " (pk VARCHAR(128) NOT NULL PRIMARY KEY, col1 DECIMAL(5,1), col2 DECIMAL(5,2), col3 DECIMAL(4,4))";
            else if (tgtSQ()) ddl = "CREATE TABLE target" +
                                    " (pk VARCHAR(128) NOT NULL PRIMARY KEY, col1 DECIMAL(5,1), col2 DECIMAL(5,2), col3 DECIMAL(4,4))";
            conn.createStatement().execute(ddl);
        
            conn.setAutoCommit(false);
    
            String query = null;
            if (tgtPH()||tgtTR()) query = "UPSERT INTO source(pk, col1) VALUES(?,?)";
            else if (tgtSQ()) query = "INSERT INTO source(pk, col1) VALUES(?,?)";
            PreparedStatement stmt = conn.prepareStatement(query);
            stmt.setString(1, "1");
            stmt.setBigDecimal(2, new BigDecimal("100.12"));
            stmt.execute();
            conn.commit();
            stmt.setString(1, "2");
            stmt.setBigDecimal(2, new BigDecimal("100.34"));
            stmt.execute();
            conn.commit();
            
            // Evaluated on client side.
            // source and target in different tables, values scheme compatible.
            if (tgtPH()||tgtTR()) query = "UPSERT INTO target(pk, col2) SELECT pk, col1 from source";
            else if (tgtSQ()) query = "INSERT INTO target(pk, col2) SELECT pk, col1 from source";
            stmt = conn.prepareStatement(query);
            stmt.execute();
            conn.commit();
            if (tgtPH()) query = "SELECT col2 FROM target";
            else if (tgtSQ()||tgtTR()) query = "SELECT col2 FROM target order by 1";
            stmt = conn.prepareStatement(query);
            ResultSet rs = stmt.executeQuery();
            assertTrue(rs.next());
            assertEquals(new BigDecimal("100.12"), rs.getBigDecimal(1));
            assertTrue(rs.next());
            assertEquals(new BigDecimal("100.34"), rs.getBigDecimal(1));
            assertFalse(rs.next());
            // source and target in different tables, values requires scale chopping.
            if (tgtPH()||tgtTR()) query = "UPSERT INTO target(pk, col1) SELECT pk, col1 from source";
            else if (tgtSQ()) query = "UPDATE target SET col1=(select col1 from source where source.pk=target.pk)";
            stmt = conn.prepareStatement(query);
            stmt.execute();
            conn.commit();
            if (tgtPH()) query = "SELECT col1 FROM target";
            else if (tgtSQ()||tgtTR()) query = "SELECT col1 FROM target order by 1";
            stmt = conn.prepareStatement(query);
            rs = stmt.executeQuery();
            assertTrue(rs.next());
            assertEquals(new BigDecimal("100.1"), rs.getBigDecimal(1));
            assertTrue(rs.next());
            assertEquals(new BigDecimal("100.3"), rs.getBigDecimal(1));
            assertFalse(rs.next());
            // source and target in different tables, values scheme incompatible.
            try {
                if (tgtPH()||tgtTR()) query = "UPSERT INTO target(pk, col3) SELECT pk, col1 from source";
                else if (tgtSQ()) query = "INSERT INTO target(pk, col3) SELECT pk, col1 from source";
                stmt = conn.prepareStatement(query);
                stmt.execute();
                conn.commit();
                fail("Should have caught bad upsert.");
            } catch (Exception e) {
                if (tgtPH()) assertTrue(e.getMessage(), e.getMessage().contains("ERROR 206 (22003): The value is outside the range for the data type. columnName=COL3"));
                else if (tgtSQ()||tgtTR()) assertTrue(e.getMessage(), e.getMessage().contains("*** ERROR[8411]"));

            }
           
            if (tgtPH()) { 
                // Evaluate on server side.
                conn.setAutoCommit(true);
                // source and target in same table, values scheme compatible.
                if (tgtPH()||tgtTR()) query = "UPSERT INTO source(pk, col3) SELECT pk, col1 from source";
                else if (tgtSQ()) query = "UPDATE target SET col3=(select col1 from source where source.pk=target.pk)";
                stmt = conn.prepareStatement(query);
                stmt.execute();
                if (tgtPH()) query = "SELECT col3 FROM source";
                else if (tgtSQ()||tgtTR()) query = "SELECT col3 FROM source order by 1";
                stmt = conn.prepareStatement(query);
                rs = stmt.executeQuery();
                assertTrue(rs.next());
                assertEquals(new BigDecimal("100.12"), rs.getBigDecimal(1));
                assertTrue(rs.next());
                assertEquals(new BigDecimal("100.34"), rs.getBigDecimal(1));
                assertFalse(rs.next());
                // source and target in same table, values requires scale chopping.
                if (tgtPH()||tgtTR()) query = "UPSERT INTO source(pk, col2) SELECT pk, col1 from source";
                else if (tgtSQ()) query = "UPDATE target SET col2=(select col1 from source where source.pk=target.pk)";
                stmt = conn.prepareStatement(query);
                stmt.execute();
                if (tgtPH()) query = "SELECT col2 FROM source";
                else if (tgtSQ()||tgtTR()) query = "SELECT col2 FROM source order by 1"; 
                stmt = conn.prepareStatement(query);
                rs = stmt.executeQuery();
                assertTrue(rs.next());
                assertEquals(new BigDecimal("100.1"), rs.getBigDecimal(1));
                assertTrue(rs.next());
                assertEquals(new BigDecimal("100.3"), rs.getBigDecimal(1));
                assertFalse(rs.next());
                // source and target in same table, values scheme incompatible.
                if (tgtPH()||tgtTR()) query = "UPSERT INTO source(pk, col4) SELECT pk, col1 from source";
                else if (tgtSQ()) query = "UPDATE target SET col4=(select col1 from source where source.pk=target.pk)";
                stmt = conn.prepareStatement(query);
                stmt.execute();
                query = "SELECT col4 FROM source";
                stmt = conn.prepareStatement(query);
                rs = stmt.executeQuery();
                assertTrue(rs.next());
                assertNull(rs.getBigDecimal(1));
                assertTrue(rs.next());
                assertNull(rs.getBigDecimal(1));
                assertFalse(rs.next());
            } 
        } finally {
        }
    }

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

        try {
            String ddl = null;
            if (tgtPH()) ddl = "CREATE TABLE IF NOT EXISTS testDecimalArithmatic" + 
                                    "  (pk VARCHAR NOT NULL PRIMARY KEY, col1 DECIMAL(31, 11), col2 DECIMAL(31,1), col3 DECIMAL(38,1))";
            else if (tgtTR()) ddl = "CREATE TABLE IF NOT EXISTS testDecimalArithmatic" +
                                    "  (pk VARCHAR(128) NOT NULL PRIMARY KEY, col1 DECIMAL(18, 11), col2 DECIMAL(18,1), col3 DECIMAL(18,1))";
            else if (tgtSQ()) ddl = "CREATE TABLE testDecimalArithmatic" +
                                    "  (pk VARCHAR(128) NOT NULL PRIMARY KEY, col1 DECIMAL(18, 11), col2 DECIMAL(18,1), col3 DECIMAL(18,1))";         
            conn.createStatement().execute(ddl);
        
            conn.setAutoCommit(false);
    
            String query = null;
            if (tgtPH()||tgtTR()) query = "UPSERT INTO testDecimalArithmatic(pk, col1, col2, col3) VALUES(?,?,?,?)";
            else if (tgtSQ()) query = "INSERT INTO testDecimalArithmatic(pk, col1, col2, col3) VALUES(?,?,?,?)";
            PreparedStatement stmt = conn.prepareStatement(query);
            stmt.setString(1, "1");
            if (tgtPH()) {
                stmt.setBigDecimal(2, new BigDecimal("99999999999999999999.1"));
                stmt.setBigDecimal(3, new BigDecimal("99999999999999999999.1"));
                stmt.setBigDecimal(4, new BigDecimal("9999999999999999999999999999999999999.1"));
            } else if (tgtSQ()||tgtTR()) {
                stmt.setBigDecimal(2, new BigDecimal("9999999.11111111111"));
                stmt.setBigDecimal(3, new BigDecimal("99999999999999999.1"));
                stmt.setBigDecimal(4, new BigDecimal("99999999999999999.1"));
            }

            stmt.execute();
            conn.commit();
            stmt.setString(1, "2");
            stmt.setBigDecimal(2, new BigDecimal("0"));
            stmt.setBigDecimal(3, new BigDecimal("0"));
            stmt.setBigDecimal(4, new BigDecimal("0"));
            stmt.execute();
            conn.commit();
            stmt.setString(1, "3");
            stmt.setBigDecimal(2, new BigDecimal("0"));
            stmt.setBigDecimal(3, new BigDecimal("0"));
            stmt.setBigDecimal(4, new BigDecimal("0"));
            stmt.execute();
            conn.commit();
            
            // Averaging
            // result scale should be: max(max(ls, rs), 4).
            // We are not imposing restriction on precisioin.
            query = "SELECT avg(col1) FROM testDecimalArithmatic";
            stmt = conn.prepareStatement(query);
            ResultSet rs = stmt.executeQuery();
            assertTrue(rs.next());
            BigDecimal result = rs.getBigDecimal(1);
            if (tgtPH()) assertEquals(new BigDecimal("33333333333333333333.03333333333"), result);
            else if (tgtSQ()||tgtTR()) assertEquals(new BigDecimal("3333333.03703703703"), result);
            
            query = "SELECT avg(col2) FROM testDecimalArithmatic";
            stmt = conn.prepareStatement(query);
            rs = stmt.executeQuery();
            assertTrue(rs.next());
            result = rs.getBigDecimal(1);
            if (tgtPH()) assertEquals(new BigDecimal("33333333333333333333.0333"), result);
            else if (tgtSQ()||tgtTR()) assertEquals(new BigDecimal("33333333333333333.0"), result);
            
            // We cap our decimal to a precision of 38.
            query = "SELECT avg(col3) FROM testDecimalArithmatic";
            stmt = conn.prepareStatement(query);
            rs = stmt.executeQuery();
            assertTrue(rs.next());
            result = rs.getBigDecimal(1);
            if (tgtPH()) assertEquals(new BigDecimal("3333333333333333333333333333333333333"), result);
            else if (tgtSQ()||tgtTR()) assertEquals(new BigDecimal("33333333333333333.0"), result);
        } finally {
        }
    }

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

        try {
            String ddl = null;
            if (tgtPH()) ddl = "CREATE TABLE IF NOT EXISTS testDecimalArithmatic" + 
                                    "  (pk VARCHAR NOT NULL PRIMARY KEY, " +
                                    "col1 DECIMAL(38,0), col2 DECIMAL(5, 2), col3 INTEGER, col4 BIGINT, col5 DECIMAL)";
            else if (tgtTR()) ddl = "CREATE TABLE IF NOT EXISTS testDecimalArithmatic" +
                                    "  (pk VARCHAR(128) NOT NULL PRIMARY KEY, " +
                                    "col1 DECIMAL(18,0), col2 DECIMAL(5, 2), col3 INTEGER, col4 BIGINT, col5 DECIMAL)";
            else if (tgtSQ()) ddl = "CREATE TABLE testDecimalArithmatic" +
                                    "  (pk VARCHAR(128) NOT NULL PRIMARY KEY, " +
                                    "col1 DECIMAL(18,0), col2 DECIMAL(5, 2), col3 INTEGER, col4 BIGINT, col5 DECIMAL)";
            conn.createStatement().execute(ddl);
        
            conn.setAutoCommit(false);
    
            String query = null;
            if (tgtPH()||tgtTR()) query = "UPSERT INTO testDecimalArithmatic(pk, col1, col2, col3, col4, col5) VALUES(?,?,?,?,?,?)";
            else if (tgtSQ()) query = "INSERT INTO testDecimalArithmatic(pk, col1, col2, col3, col4, col5) VALUES(?,?,?,?,?,?)";
            PreparedStatement stmt = conn.prepareStatement(query);
            stmt.setString(1, "testValueOne");
            if (tgtPH()) stmt.setBigDecimal(2, new BigDecimal("1234567890123456789012345678901"));
            else if (tgtSQ()||tgtTR()) stmt.setBigDecimal(2, new BigDecimal("123456789012345678"));
            stmt.setBigDecimal(3, new BigDecimal("123.45"));
            stmt.setInt(4, 10);
            stmt.setLong(5, 10L);
            stmt.setBigDecimal(6, new BigDecimal("111.111"));
            stmt.execute();
            conn.commit();

            stmt.setString(1, "testValueTwo");
            if (tgtPH()) stmt.setBigDecimal(2, new BigDecimal("12345678901234567890123456789012345678"));
            else if (tgtSQ()||tgtTR()) stmt.setBigDecimal(2, new BigDecimal("123456789012345678"));
            stmt.setBigDecimal(3, new BigDecimal("123.45"));
            stmt.setInt(4, 10);
            stmt.setLong(5, 10L);
            stmt.setBigDecimal(6, new BigDecimal("123456789.0123456789"));
            stmt.execute();
            conn.commit();
            
            // INT has a default precision and scale of (10, 0)
            // LONG has a default precision and scale of (19, 0)
            query = "SELECT col1 + col3 FROM testDecimalArithmatic WHERE pk='testValueOne'";
            stmt = conn.prepareStatement(query);
            ResultSet rs = stmt.executeQuery();
            assertTrue(rs.next());
            BigDecimal result = rs.getBigDecimal(1);
            if (tgtPH()) assertEquals(new BigDecimal("1234567890123456789012345678911"), result);
            else if (tgtSQ()||tgtTR()) assertEquals(new BigDecimal("123456789012345688"), result);
            
            query = "SELECT col1 + col4 FROM testDecimalArithmatic WHERE pk='testValueOne'";
            stmt = conn.prepareStatement(query);
            rs = stmt.executeQuery();
            assertTrue(rs.next());
            result = rs.getBigDecimal(1);
            if (tgtPH()) assertEquals(new BigDecimal("1234567890123456789012345678911"), result);
            else if (tgtSQ()||tgtTR()) assertEquals(new BigDecimal("123456789012345688"), result);            
            query = "SELECT col2 + col3 FROM testDecimalArithmatic WHERE pk='testValueOne'";
            stmt = conn.prepareStatement(query);
            rs = stmt.executeQuery();
            assertTrue(rs.next());
            result = rs.getBigDecimal(1);
            assertEquals(new BigDecimal("133.45"), result);
 
            query = "SELECT col2 + col4 FROM testDecimalArithmatic WHERE pk='testValueOne'";
            stmt = conn.prepareStatement(query);
            rs = stmt.executeQuery();
            assertTrue(rs.next());
            result = rs.getBigDecimal(1);
            if (tgtPH()) assertEquals(new BigDecimal("133.45"), result);
            else if (tgtSQ()||tgtTR()) assertEquals(new BigDecimal("133"), result);
 
            query = "SELECT col5 + col3 FROM testDecimalArithmatic WHERE pk='testValueOne'";
            stmt = conn.prepareStatement(query);
            rs = stmt.executeQuery();
            assertTrue(rs.next());
            result = rs.getBigDecimal(1);
            if (tgtPH()) assertEquals(new BigDecimal("121.111"), result);
            else if (tgtSQ()||tgtTR()) assertEquals(new BigDecimal("121"), result);

            query = "SELECT col5 + col4 FROM testDecimalArithmatic WHERE pk='testValueOne'";
            stmt = conn.prepareStatement(query);
            rs = stmt.executeQuery();
            assertTrue(rs.next());
            result = rs.getBigDecimal(1);
            if (tgtPH()) assertEquals(new BigDecimal("121.111"), result);
            else if (tgtSQ()||tgtTR()) assertEquals(new BigDecimal("121"), result);
 
            query = "SELECT col1 - col3 FROM testDecimalArithmatic WHERE pk='testValueOne'";
            stmt = conn.prepareStatement(query);
            rs = stmt.executeQuery();
            assertTrue(rs.next());
            result = rs.getBigDecimal(1);
            if (tgtPH()) assertEquals(new BigDecimal("1234567890123456789012345678891"), result);
            else if (tgtSQ()||tgtTR()) assertEquals(new BigDecimal("123456789012345668"), result);
 
            query = "SELECT col1 - col4 FROM testDecimalArithmatic WHERE pk='testValueOne'";
            stmt = conn.prepareStatement(query);
            rs = stmt.executeQuery();
            assertTrue(rs.next());
            result = rs.getBigDecimal(1);
            if (tgtPH()) assertEquals(new BigDecimal("1234567890123456789012345678891"), result);
            else if (tgtSQ()||tgtTR()) assertEquals(new BigDecimal("123456789012345668"), result);
 
            query = "SELECT col2 - col3 FROM testDecimalArithmatic WHERE pk='testValueOne'";
            stmt = conn.prepareStatement(query);
            rs = stmt.executeQuery();
            assertTrue(rs.next());
            result = rs.getBigDecimal(1);
            assertEquals(new BigDecimal("113.45"), result);
 
            query = "SELECT col2 - col4 FROM testDecimalArithmatic WHERE pk='testValueOne'";
            stmt = conn.prepareStatement(query);
            rs = stmt.executeQuery();
            assertTrue(rs.next());
            result = rs.getBigDecimal(1);
            if (tgtPH()) assertEquals(new BigDecimal("113.45"), result);
            else if (tgtSQ()||tgtTR()) assertEquals(new BigDecimal("113"), result);
 
            query = "SELECT col5 - col3 FROM testDecimalArithmatic WHERE pk='testValueOne'";
            stmt = conn.prepareStatement(query);
            rs = stmt.executeQuery();
            assertTrue(rs.next());
            result = rs.getBigDecimal(1);
            if (tgtPH()) assertEquals(new BigDecimal("101.111"), result);
            else if (tgtSQ()||tgtTR()) assertEquals(new BigDecimal("101"), result);
 
            query = "SELECT col5 - col4 FROM testDecimalArithmatic WHERE pk='testValueOne'";
            stmt = conn.prepareStatement(query);
            rs = stmt.executeQuery();
            assertTrue(rs.next());
            result = rs.getBigDecimal(1);
            if (tgtPH()) assertEquals(new BigDecimal("101.111"), result);
            else if (tgtSQ()||tgtTR()) assertEquals(new BigDecimal("101"), result);
 
            query = "SELECT col1 * col3 FROM testDecimalArithmatic WHERE pk='testValueOne'";
            stmt = conn.prepareStatement(query);
            rs = stmt.executeQuery();
            assertTrue(rs.next());
            result = rs.getBigDecimal(1);
            if (tgtPH()) assertEquals(new BigDecimal("1.234567890123456789012345678901E+31"), result);
            else if (tgtSQ()||tgtTR()) assertEquals(new BigDecimal("1234567890123456780"), result);
 
            query = "SELECT col1 * col4 FROM testDecimalArithmatic WHERE pk='testValueOne'";
            stmt = conn.prepareStatement(query);
            rs = stmt.executeQuery();
            assertTrue(rs.next());
            result = rs.getBigDecimal(1);
            if (tgtPH()) assertEquals(new BigDecimal("1.234567890123456789012345678901E+31"), result);
            else if (tgtSQ()||tgtTR()) assertEquals(new BigDecimal("1234567890123456780"), result);

            query = "SELECT col1 * col3 FROM testDecimalArithmatic WHERE pk='testValueOne'";
            stmt = conn.prepareStatement(query);
            rs = stmt.executeQuery();
            assertTrue(rs.next());
            result = rs.getBigDecimal(1);
            if (tgtPH()) assertEquals(new BigDecimal("1.234567890123456789012345678901E+31"), result);
            else if (tgtSQ()||tgtTR()) assertEquals(new BigDecimal("1234567890123456780"), result);

            if (tgtPH()) { 
                try {
            	    query = "SELECT col1 * col3 FROM testDecimalArithmatic WHERE pk='testValueTwo'";
            	    stmt = conn.prepareStatement(query);
            	    rs = stmt.executeQuery();
            	    assertTrue(rs.next());
            	    result = rs.getBigDecimal(1);
            	    fail("Should have caught error.");
                } catch (Exception e) {
            	    assertTrue(e.getMessage(), e.getMessage().contains("ERROR 206 (22003): The value is outside the range for the data type. DECIMAL(38,0)"));
                }
            
                try {
            	    query = "SELECT col1 * col4 FROM testDecimalArithmatic WHERE pk='testValueTwo'";
            	    stmt = conn.prepareStatement(query);
            	    rs = stmt.executeQuery();
            	    assertTrue(rs.next());
            	    result = rs.getBigDecimal(1);
            	    fail("Should have caught error.");
                } catch (Exception e) {
            	    assertTrue(e.getMessage(), e.getMessage().contains("ERROR 206 (22003): The value is outside the range for the data type. DECIMAL(38,0)"));
                }
            }
 
            query = "SELECT col4 * col5 FROM testDecimalArithmatic WHERE pk='testValueOne'";
            stmt = conn.prepareStatement(query);
            rs = stmt.executeQuery();
            assertTrue(rs.next());
            result = rs.getBigDecimal(1);
            if (tgtPH()) assertEquals(0, result.compareTo(new BigDecimal("1111.11")));
            else if (tgtSQ()||tgtTR()) assertEquals(0, result.compareTo(new BigDecimal("1110")));

            query = "SELECT col3 * col5 FROM testDecimalArithmatic WHERE pk='testValueOne'";
            stmt = conn.prepareStatement(query);
            rs = stmt.executeQuery();
            assertTrue(rs.next());
            result = rs.getBigDecimal(1);
            if (tgtPH()) assertEquals(0, result.compareTo(new BigDecimal("1111.11")));
            else if (tgtSQ()||tgtTR()) assertEquals(0, result.compareTo(new BigDecimal("1110"))); 

            query = "SELECT col2 * col4 FROM testDecimalArithmatic WHERE pk='testValueOne'";
            stmt = conn.prepareStatement(query);
            rs = stmt.executeQuery();
            assertTrue(rs.next());
            result = rs.getBigDecimal(1);
            if (tgtPH()) assertEquals(new BigDecimal("1234.5"), result);
            else if (tgtSQ()||tgtTR()) assertEquals(new BigDecimal("1234"), result); 

            // Result scale has value of 0
            query = "SELECT col1 / col3 FROM testDecimalArithmatic WHERE pk='testValueOne'";
            stmt = conn.prepareStatement(query);
            rs = stmt.executeQuery();
            assertTrue(rs.next());
            result = rs.getBigDecimal(1);
            if (tgtPH()) assertEquals(new BigDecimal("1.2345678901234567890123456789E+29"), result);
            else if (tgtSQ()||tgtTR()) assertEquals(new BigDecimal("12345678901234567"), result);

            query = "SELECT col1 / col4 FROM testDecimalArithmatic WHERE pk='testValueOne'";
            stmt = conn.prepareStatement(query);
            rs = stmt.executeQuery();
            assertTrue(rs.next());
            result = rs.getBigDecimal(1);
            if (tgtPH()) assertEquals(new BigDecimal("1.2345678901234567890123456789E+29"), result);
            else if (tgtSQ()||tgtTR()) assertEquals(new BigDecimal("12345678901234567"), result);
            
            // Result scale is 2.
            query = "SELECT col2 / col3 FROM testDecimalArithmatic WHERE pk='testValueOne'";
            stmt = conn.prepareStatement(query);
            rs = stmt.executeQuery();
            assertTrue(rs.next());
            result = rs.getBigDecimal(1);
            if (tgtPH()) assertEquals(new BigDecimal("12.34"), result);
            else if (tgtSQ()||tgtTR()) assertEquals(new BigDecimal("12.345000000000"), result);
            
            query = "SELECT col2 / col4 FROM testDecimalArithmatic WHERE pk='testValueOne'";
            stmt = conn.prepareStatement(query);
            rs = stmt.executeQuery();
            assertTrue(rs.next());
            result = rs.getBigDecimal(1);
            if (tgtPH()) assertEquals(new BigDecimal("12.34"), result);
            else if (tgtSQ()||tgtTR()) assertEquals(new BigDecimal("12.345000000000000"), result);
 
            // col5 has NO_SCALE, so the result's scale is not expected to be truncated to col5 value's scale of 4
            query = "SELECT col5 / col3 FROM testDecimalArithmatic WHERE pk='testValueOne'";
            stmt = conn.prepareStatement(query);
            rs = stmt.executeQuery();
            assertTrue(rs.next());
            result = rs.getBigDecimal(1);
            if (tgtPH()) assertEquals(new BigDecimal("11.1111"), result);
            else if (tgtSQ()||tgtTR()) assertEquals(new BigDecimal("11.100000000"), result);
 
            query = "SELECT col5 / col4 FROM testDecimalArithmatic WHERE pk='testValueOne'";
            stmt = conn.prepareStatement(query);
            rs = stmt.executeQuery();
            assertTrue(rs.next());
            result = rs.getBigDecimal(1);
            if (tgtPH()) assertEquals(new BigDecimal("11.1111"), result);
            else if (tgtSQ()||tgtTR()) assertEquals(new BigDecimal("11.100000000"), result);
        } finally {
        }
    }
}