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

import org.apache.commons.lang.ArrayUtils;
import com.google.common.collect.Lists;

/**
 * @author simontoens
 * @since 1.2
 */
public class DescColumnSortOrderTest extends BaseTest {

    private static final String TABLE = "DescColumnSortOrderTest";

    @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 " + TABLE));
        doBaseTestSuiteSetup();
    }
    /* @AfterClass, @Before, @After are defined in BaseTest */

    private static boolean SQ_order_by_desc = false;
    
    @Test
    public void noOder() throws Exception {
        printTestDescription();
        String ddl = null;
   
        if (tgtPH()) ddl = "CREATE TABLE " + TABLE + " (pk VARCHAR NOT NULL PRIMARY KEY)";
        else if (tgtSQ()||tgtTR()) ddl = "CREATE TABLE " + TABLE + " (pk VARCHAR(128) NOT NULL PRIMARY KEY)";
        runQueryTest(ddl, "pk", new Object[][]{{"a"}, {"b"}, {"c"}}, new Object[][]{{"a"}, {"b"}, {"c"}});
    }                                                           

    @Test
    public void noOrderCompositePK() throws Exception {
        printTestDescription();
        String ddl = null;
        if (tgtPH()) ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid, code))";
        else if (tgtSQ()||tgtTR()) ddl = "CREATE TABLE " + TABLE + " (ooid CHAR(2) NOT NULL, code INTEGER NOT NULL , constraint pk primary key (ooid, code))";
        Object[][] rows = new Object[][]{{"o1", 1}, {"o2", 2}, {"o3", 3}};
        if (tgtPH()) runQueryTest(ddl, upsert("oid", "code"), rows, rows);
        else if (tgtSQ()||tgtTR()) runQueryTest(ddl, upsert("ooid", "code"), rows, rows);
    }
    
    @Test
    public void ascOrderInlinePK() throws Exception {
        printTestDescription();
        String ddl = null;
        if (tgtPH()) ddl = "CREATE TABLE " + TABLE + " (pk VARCHAR NOT NULL PRIMARY KEY ASC)";
        else if (tgtSQ()||tgtTR()) ddl = "CREATE TABLE " + TABLE + " (pk VARCHAR(128) NOT NULL PRIMARY KEY ASC)";
        runQueryTest(ddl, "pk", new Object[][]{{"a"}, {"b"}, {"c"}}, new Object[][]{{"a"}, {"b"}, {"c"}});
    }
    
    @Test
    public void ascOrderCompositePK() throws Exception {
        printTestDescription();
        String ddl = null;
        if (tgtPH()) ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid ASC, code DESC))";
        else if (tgtSQ()||tgtTR()) ddl = "CREATE TABLE " + TABLE + " (ooid CHAR(2) NOT NULL, code INTEGER NOT NULL , constraint pk primary key (ooid ASC, code DESC))";
        Object[][] insertedRows = new Object[][]{{"o1", 1}, {"o1", 2}, {"o1", 3}};
        Object[][] expectedRows = new Object[][]{{"o1", 3}, {"o1", 2}, {"o1", 1}};
        if (tgtPH()) runQueryTest(ddl, upsert("oid", "code"), insertedRows, expectedRows);
        else if (tgtSQ()||tgtTR()) runQueryTest(ddl, upsert("ooid", "code"), insertedRows, expectedRows);              
    }

    @Test
    public void descOrderInlinePK() throws Exception {
        printTestDescription();
        String[] testTypes = null;
        if (tgtPH()) testTypes = new String[]{"CHAR(2)", "VARCHAR"};
        else if (tgtSQ()||tgtTR()) testTypes = new String[]{"CHAR(2)", "VARCHAR(128)"};

        SQ_order_by_desc = true;
        for (String type : testTypes) {
            dropTestObjects();
            String ddl = "CREATE TABLE " + TABLE + " (pk ${type} NOT NULL PRIMARY KEY DESC)".replace("${type}", type);
            runQueryTest(ddl, "pk", new Object[][]{{"aa"}, {"bb"}, {"cc"}}, new Object[][]{{"cc"}, {"bb"}, {"aa"}});
        }
        SQ_order_by_desc = false;
    }
    
    @Test
    public void descOrderCompositePK1() throws Exception {
        printTestDescription();
        String ddl = null;
        if (tgtPH()) ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code))";
        else if (tgtSQ()||tgtTR()) ddl = "CREATE TABLE " + TABLE + " (ooid CHAR(2) NOT NULL, code INTEGER NOT NULL, constraint pk primary key (ooid DESC, code))";

        Object[][] insertedRows = new Object[][]{{"o1", 1}, {"o2", 2}, {"o3", 3}};
        Object[][] expectedRows = new Object[][]{{"o3", 3}, {"o2", 2}, {"o1", 1}};

        SQ_order_by_desc = true;
        if (tgtPH()) runQueryTest(ddl, upsert("oid", "code"), insertedRows, expectedRows);        
        else if (tgtSQ()||tgtTR()) runQueryTest(ddl, upsert("ooid", "code"), insertedRows, expectedRows);
        SQ_order_by_desc = false;
    }
    
    @Test
    public void descOrderCompositePK2() throws Exception {
        printTestDescription();
        String ddl = null;
        if (tgtPH()) ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))";
        else if (tgtSQ()||tgtTR()) ddl = "CREATE TABLE " + TABLE + " (ooid CHAR(2) NOT NULL, code INTEGER NOT NULL, constraint pk primary key (ooid DESC, code DESC))";

        Object[][] insertedRows = new Object[][]{{"o1", 1}, {"o1", 2}, {"o1", 3}};
        Object[][] expectedRows = new Object[][]{{"o1", 3}, {"o1", 2}, {"o1", 1}};

        SQ_order_by_desc = true;
        if (tgtPH()) runQueryTest(ddl, upsert("oid", "code"), insertedRows, expectedRows);
        else if (tgtSQ()||tgtTR()) runQueryTest(ddl, upsert("ooid", "code"), insertedRows, expectedRows); 
        SQ_order_by_desc = false;       
    }    

    @Test
    public void equalityDescInlinePK() throws Exception {
        printTestDescription();
        String ddl = null;
        if (tgtPH()) ddl = "CREATE TABLE " + TABLE + " (pk VARCHAR NOT NULL PRIMARY KEY DESC)";
        else if (tgtSQ()||tgtTR()) ddl = "CREATE TABLE " + TABLE + " (pk VARCHAR(1) NOT NULL PRIMARY KEY DESC)";
        runQueryTest(ddl, upsert("pk"), new Object[][]{{"a"}, {"b"}, {"c"}}, new Object[][]{{"b"}}, new WhereCondition("pk", "=", "'b'"));
    }
    
    @Test
    public void equalityDescCompositePK1() throws Exception {
        printTestDescription();
        String ddl = null;

        if (tgtPH()) ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))";
        else if (tgtSQ()||tgtTR()) ddl = "CREATE TABLE " + TABLE + " (ooid CHAR(2) NOT NULL, code INTEGER NOT NULL,  constraint pk primary key (ooid DESC, code DESC))";

        Object[][] insertedRows = new Object[][]{{"o1", 1}, {"o2", 2}, {"o3", 3}};
        if (tgtPH()) runQueryTest(ddl, upsert("oid", "code"), insertedRows, new Object[][]{{"o2", 2}}, new WhereCondition("oid", "=", "'o2'"));        
        else if (tgtSQ()||tgtTR()) runQueryTest(ddl, upsert("ooid", "code"), insertedRows, new Object[][]{{"o2", 2}}, new WhereCondition("ooid", "=", "'o2'"));
    }
    
    @Test
    public void equalityDescCompositePK2() throws Exception {
        printTestDescription();
        String ddl = null;
        if (tgtPH()) ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))";
        else if (tgtSQ()||tgtTR()) ddl = "CREATE TABLE " + TABLE + " (ooid CHAR(2) NOT NULL, code INTEGER NOT NULL, constraint pk primary key (ooid DESC, code DESC))";

        Object[][] insertedRows = new Object[][]{{"o1", 1}, {"o1", 2}, {"o1", 3}};
        if (tgtPH()) runQueryTest(ddl, upsert("oid", "code"), insertedRows, new Object[][]{{"o1", 2}}, new WhereCondition("code", "=", "2"));        
        else if (tgtSQ()||tgtTR()) runQueryTest(ddl, upsert("ooid", "code"), insertedRows, new Object[][]{{"o1", 2}}, new WhereCondition("code", "=", "2"));
    }
    
    @Test
    public void inDescCompositePK1() throws Exception {
        printTestDescription();
        String ddl = null;
        if (tgtPH()) ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))";
        else if (tgtSQ()||tgtTR()) ddl = "CREATE TABLE " + TABLE + " (ooid CHAR(2) NOT NULL, code INTEGER NOT NULL, constraint pk primary key (ooid DESC, code DESC))";

        Object[][] insertedRows = new Object[][]{{"o1", 1}, {"o1", 2}, {"o1", 3}};
        if (tgtPH()) runQueryTest(ddl, upsert("oid", "code"), insertedRows, new Object[][]{{"o1", 2}}, new WhereCondition("code", "IN", "(2)"));        
        else if (tgtSQ()||tgtTR()) runQueryTest(ddl, upsert("ooid", "code"), insertedRows, new Object[][]{{"o1", 2}}, new WhereCondition("code", "IN", "(2)"));
    }
    
    @Test
    public void inDescCompositePK2() throws Exception {
        printTestDescription();
        String ddl = null;
        if (tgtPH()) ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))";
        else if (tgtSQ()||tgtTR()) ddl = "CREATE TABLE " + TABLE + " (ooid CHAR(2) NOT NULL, code INTEGER NOT NULL, constraint pk primary key (ooid DESC, code DESC))";

        Object[][] insertedRows = new Object[][]{{"o1", 1}, {"o2", 2}, {"o3", 3}};
        if (tgtPH()) runQueryTest(ddl, upsert("oid", "code"), insertedRows, new Object[][]{{"o2", 2}}, new WhereCondition("oid", "IN", "('o2')"));       
        else if (tgtSQ()||tgtTR()) runQueryTest(ddl, upsert("ooid", "code"), insertedRows, new Object[][]{{"o2", 2}}, new WhereCondition("ooid", "IN", "('o2')")); 
    }
    
    @Test
    public void likeDescCompositePK1() throws Exception {
        printTestDescription();
        String ddl = null;
        if (tgtPH()) ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))";
        else if (tgtSQ()||tgtTR()) ddl = "CREATE TABLE " + TABLE + " (ooid CHAR(2) NOT NULL, code INTEGER NOT NULL, constraint pk primary key (ooid DESC, code DESC))";

        Object[][] insertedRows = new Object[][]{{"a1", 1}, {"b2", 2}, {"c3", 3}};
        if (tgtPH()) runQueryTest(ddl, upsert("oid", "code"), insertedRows, new Object[][]{{"b2", 2}}, new WhereCondition("oid", "LIKE", "('b%')"));        
        else if (tgtSQ()||tgtTR()) runQueryTest(ddl, upsert("ooid", "code"), insertedRows, new Object[][]{{"b2", 2}}, new WhereCondition("ooid", "LIKE", "('b%')"));
    }
    
    @Test
    public void likeDescCompositePK2() throws Exception {
        printTestDescription();
        String ddl = null;
        if (tgtPH()) ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code CHAR(2) NOT NULL constraint pk primary key (oid DESC, code DESC))";
        else if (tgtSQ()||tgtTR()) ddl = "CREATE TABLE " + TABLE + " (ooid CHAR(2) NOT NULL, code CHAR(2) NOT NULL, constraint pk primary key (ooid DESC, code DESC))";

        Object[][] insertedRows = new Object[][]{{"a1", "11"}, {"b2", "22"}, {"c3", "33"}};

        if (tgtPH()) runQueryTest(ddl, upsert("oid", "code"), insertedRows, new Object[][]{{"b2", "22"}}, new WhereCondition("code", "LIKE", "('2%')"));        
        else if (tgtSQ()||tgtTR()) runQueryTest(ddl, upsert("ooid", "code"), insertedRows, new Object[][]{{"b2", "22"}}, new WhereCondition("code", "LIKE", "('2%')"));
    }
    
    @Test
    public void greaterThanDescCompositePK3() throws Exception {
        printTestDescription();
        String ddl = null;
        if (tgtPH()) ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))";
        else if (tgtSQ()||tgtTR()) ddl = "CREATE TABLE " + TABLE + " (ooid CHAR(2) NOT NULL, code INTEGER NOT NULL, constraint pk primary key (ooid DESC, code DESC))";

        Object[][] insertedRows = new Object[][]{{"o1", 1}, {"o1", 2}, {"o1", 3}};
        Object[][] expectedRows = new Object[][]{{"o1", 2}, {"o1", 1}};

        SQ_order_by_desc = true;
        if (tgtPH()) runQueryTest(ddl, upsert("oid", "code"), insertedRows, expectedRows, new WhereCondition("code", "<", "3"));        
        else if (tgtSQ()||tgtTR()) runQueryTest(ddl, upsert("ooid", "code"), insertedRows, expectedRows, new WhereCondition("code", "<", "3"));
        SQ_order_by_desc = false;
    }
    
    @Test
    public void substrDescCompositePK1() throws Exception {
        printTestDescription();
        String ddl = null;
        if (tgtPH()) ddl = "CREATE TABLE " + TABLE + " (oid CHAR(3) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code ASC))";
        else if (tgtSQ()||tgtTR()) ddl = "CREATE TABLE " + TABLE + " (ooid CHAR(3) NOT NULL, code INTEGER NOT NULL, constraint pk primary key (ooid DESC, code ASC))";

        Object[][] insertedRows = new Object[][]{{"ao1", 1}, {"bo2", 2}, {"co3", 3}};
        Object[][] expectedRows = new Object[][]{{"co3", 3}, {"bo2", 2}};

        SQ_order_by_desc = true;
        if (tgtPH()) runQueryTest(ddl, upsert("oid", "code"), insertedRows, expectedRows, new WhereCondition("SUBSTR(oid, 3, 1)", ">", "'1'"));
        else if (tgtSQ()||tgtTR()) runQueryTest(ddl, upsert("ooid", "code"), insertedRows, expectedRows, new WhereCondition("SUBSTR(ooid, 3, 1)", ">", "'1'"));
        SQ_order_by_desc = false;
    }
        
    @Test
    public void substrDescCompositePK2() throws Exception {
        printTestDescription();
        String ddl = null;
        if (tgtPH()) ddl = "CREATE TABLE " + TABLE + " (oid CHAR(4) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code ASC))";
        else if (tgtSQ()||tgtTR()) ddl = "CREATE TABLE " + TABLE + " (ooid CHAR(4) NOT NULL, code INTEGER NOT NULL, constraint pk primary key (ooid DESC, code ASC))";

        Object[][] insertedRows = new Object[][]{{"aaaa", 1}, {"bbbb", 2}, {"cccd", 3}};
        Object[][] expectedRows = new Object[][]{{"cccd", 3}};

        if (tgtPH()) runQueryTest(ddl, upsert("oid", "code"), insertedRows, expectedRows, new WhereCondition("SUBSTR(oid, 4, 1)", "=", "'d'"));
        else if (tgtSQ()||tgtTR()) runQueryTest(ddl, upsert("ooid", "code"), insertedRows, expectedRows, new WhereCondition("SUBSTR(ooid, 4, 1)", "=", "'d'"));
    }    
    
    @Test
    public void lTrimDescCompositePK() throws Exception {
        printTestDescription();
        String ddl = null;
        if (tgtPH()) ddl = "CREATE TABLE " + TABLE + " (oid VARCHAR NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code DESC))";
        else if (tgtSQ()||tgtTR()) ddl = "CREATE TABLE " + TABLE + " (ooid VARCHAR(128) NOT NULL, code INTEGER NOT NULL, constraint pk primary key (ooid DESC, code DESC))";

        Object[][] insertedRows = new Object[][]{{" o1 ", 1}, {"  o2", 2}, {"  o3", 3}};
        Object[][] expectedRows = new Object[][]{{"  o2", 2}};

        if (tgtPH()) runQueryTest(ddl, upsert("oid", "code"), insertedRows, expectedRows, new WhereCondition("LTRIM(oid)", "=", "'o2'"));
        else if (tgtSQ()||tgtTR()) runQueryTest(ddl, upsert("ooid", "code"), insertedRows, expectedRows, new WhereCondition("LTRIM(ooid)", "=", "'o2'"));
    }

    @Test
    public void countDescCompositePK() throws Exception {
        printTestDescription();
        String ddl = null;
        if (tgtPH()) ddl = "CREATE TABLE " + TABLE + " (oid CHAR(2) NOT NULL, code INTEGER NOT NULL constraint pk primary key (oid DESC, code ASC))";
        else if (tgtSQ()||tgtTR()) ddl = "CREATE TABLE " + TABLE + " (ooid CHAR(2) NOT NULL, code INTEGER NOT NULL, constraint pk primary key (ooid DESC, code ASC))";

        Object[][] insertedRows = new Object[][]{{"o1", 1}, {"o2", 2}, {"o3", 3}};
        Object[][] expectedRows = new Object[][]{{3l}};

        if (tgtPH()) runQueryTest(ddl, upsert("oid", "code"), select("COUNT(oid)"), insertedRows, expectedRows);
        else if (tgtSQ()||tgtTR()) runQueryTest(ddl, upsert("ooid", "code"), select("COUNT(ooid)"), insertedRows, expectedRows);
    }
    
    @Test
    public void sumDescCompositePK() throws Exception {
        printTestDescription();
        String ddl = null;
        if (tgtPH()) ddl = "CREATE TABLE " + TABLE + " (n1 INTEGER NOT NULL, n2 DECIMAL(10, 2) NOT NULL, n3 BIGINT NOT NULL " + 
            "constraint pk primary key (n1 DESC, n2 DESC, n3 DESC))";
        else if (tgtSQ()||tgtTR()) ddl = "CREATE TABLE " + TABLE + " (n1 INTEGER NOT NULL, n2 DECIMAL(10, 1) NOT NULL, n3 BIGINT NOT NULL " +
            ", constraint pk primary key (n1 DESC, n2 DESC, n3 DESC))";

        Object[][] insertedRows = new Object[][]{{10, bdec(10.2), 21l}, {20, bdec(20.2), 32l}, {30, bdec(30.2), 43l}};
        Object[][] expectedRows = new Object[][]{{60l, bdec(60.6), 96l}};

        runQueryTest(ddl, upsert("n1", "n2", "n3"), select("SUM(n1), SUM(n2), SUM(n3)"), insertedRows, expectedRows);
    }    
    
    @Test
    public void avgDescCompositePK() throws Exception {
        printTestDescription();
        String ddl = null;
        if (tgtPH()) ddl = "CREATE TABLE " + TABLE + " (n1 INTEGER NOT NULL, n2 DECIMAL(10, 2) NOT NULL, n3 BIGINT NOT NULL " + 
            "constraint pk primary key (n1 DESC, n2 DESC, n3 DESC))";
        else if (tgtSQ()||tgtTR()) ddl = "CREATE TABLE " + TABLE + " (n1 INTEGER NOT NULL, n2 DECIMAL(10, 1) NOT NULL, n3 BIGINT NOT NULL " +
            ", constraint pk primary key (n1 DESC, n2 DESC, n3 DESC))";

        Object[][] insertedRows = new Object[][]{{10, bdec(10.2), 21l}, {20, bdec(20.2), 32l}, {30, bdec(30.2), 43l}};
        Object[][] expectedRows = null;
        if (tgtPH()) expectedRows = new Object[][]{{new BigDecimal(bint(2), -1), bdec(20.2), BigDecimal.valueOf(32)}};
        else if (tgtSQ()||tgtTR()) expectedRows = new Object[][]{{(new BigDecimal(bint(2), -1)).longValue(), bdec(20.2), BigDecimal.valueOf(32)}};

        runQueryTest(ddl, upsert("n1", "n2", "n3"), select("AVG(n1), AVG(n2), AVG(n3)"), insertedRows, expectedRows);

    }
    
    @Test
    public void minDescCompositePK() throws Exception {
        printTestDescription();
        String ddl = null;
        if (tgtPH()) ddl = "CREATE TABLE " + TABLE + " (n1 INTEGER NOT NULL, n2 DECIMAL(10, 2) NOT NULL, n3 BIGINT NOT NULL " + 
            "constraint pk primary key (n1 DESC, n2 DESC, n3 DESC))";
        else if (tgtSQ()||tgtTR()) ddl = "CREATE TABLE " + TABLE + " (n1 INTEGER NOT NULL, n2 DECIMAL(10, 1) NOT NULL, n3 BIGINT NOT NULL " +
            ", constraint pk primary key (n1 DESC, n2 DESC, n3 DESC))";

        Object[][] insertedRows = new Object[][]{{10, bdec(10.2), 21l}, {20, bdec(20.2), 32l}, {30, bdec(30.2), 43l}};
        Object[][] expectedRows = new Object[][]{{10, bdec(10.2), 21l}};
        runQueryTest(ddl, upsert("n1", "n2", "n3"), select("MIN(n1), MIN(n2), MIN(n3)"), insertedRows, expectedRows);
    }
    
    @Test
    public void maxDescCompositePK() throws Exception {
        printTestDescription();
        String ddl = null;
        if (tgtPH()) ddl = "CREATE TABLE " + TABLE + " (n1 INTEGER NOT NULL, n2 DECIMAL(10, 2) NOT NULL, n3 BIGINT NOT NULL " + 
            "constraint pk primary key (n1 DESC, n2 DESC, n3 DESC))";
        else if (tgtSQ()||tgtTR()) ddl = "CREATE TABLE " + TABLE + " (n1 INTEGER NOT NULL, n2 DECIMAL(10, 1) NOT NULL, n3 BIGINT NOT NULL " +
            ", constraint pk primary key (n1 DESC, n2 DESC, n3 DESC))";

        Object[][] insertedRows = new Object[][]{{10, bdec(10.2), 21l}, {20, bdec(20.2), 32l}, {30, bdec(30.2), 43l}};
        Object[][] expectedRows = new Object[][]{{30, bdec(30.2), 43l}};
        runQueryTest(ddl, upsert("n1", "n2", "n3"), select("MAX(n1), MAX(n2), MAX(n3)"), insertedRows, expectedRows);
    }
    
    @Test
    public void havingSumDescCompositePK() throws Exception {
        printTestDescription();
        String ddl = null;
        if (tgtPH()) ddl = "CREATE TABLE " + TABLE + " (name CHAR(1) NOT NULL, code INTEGER NOT NULL " + 
            "constraint pk primary key (name DESC, code DESC))";
        else if (tgtSQ()||tgtTR()) ddl = "CREATE TABLE " + TABLE + " (name CHAR(1) NOT NULL, code INTEGER NOT NULL " +
            ", constraint pk primary key (name DESC, code DESC))";

        Object[][] insertedRows = new Object[][]{{"a", 10}, {"a", 20}, {"b", 100}}; 
        Object[][] expectedRows = new Object[][]{{"a", 30l}};
        runQueryTest(ddl, upsert("name", "code"), select("name", "SUM(code)"), insertedRows, expectedRows, 
            new HavingCondition("name", "SUM(code) = 30"));
    }
    
    @Test
    public void queryDescDateWithExplicitOrderBy() throws Exception {
        printTestDescription();
        String ddl = null;
        if (tgtPH()) ddl = "CREATE TABLE " + TABLE + " (c1 CHAR(1) NOT NULL, c2 CHAR(1) NOT NULL, d1 DATE NOT NULL, c3 CHAR(1) NOT NULL " + 
            "constraint pk primary key (c1, c2, d1 DESC, c3))";
        else if (tgtSQ()||tgtTR()) ddl = "CREATE TABLE " + TABLE + " (c1 CHAR(1) NOT NULL, c2 CHAR(1) NOT NULL, d1 DATE NOT NULL, c3 CHAR(1) NOT NULL " +
            ", constraint pk primary key (c1, c2, d1 DESC, c3))";

        Object[] row1 = {"1", "2", date(10, 11, 2001), "3"};
        Object[] row2 = {"1", "2", date(10, 11, 2003), "3"};
        Object[][] insertedRows = new Object[][]{row1, row2};
        runQueryTest(ddl, upsert("c1", "c2", "d1", "c3"), select("c1, c2, d1", "c3"), insertedRows, new Object[][]{row2, row1},
            null, null, new OrderBy("d1", OrderBy.Direction.DESC));
    }    
    
    @Test
    public void additionOnDescCompositePK() throws Exception {
        printTestDescription();
        String ddl = null;
        if (tgtPH()) ddl = "CREATE TABLE " + TABLE + " (n1 INTEGER NOT NULL, n2 DECIMAL(10, 2) NOT NULL, n3 BIGINT NOT NULL, d1 DATE NOT NULL " + 
            "constraint pk primary key (n1 DESC, n2 DESC, n3 DESC, d1 DESC))";
        else if (tgtSQ()||tgtTR()) ddl = "CREATE TABLE " + TABLE + " (n1 INTEGER NOT NULL, n2 DECIMAL(10, 1) NOT NULL, n3 BIGINT NOT NULL, d1 DATE NOT NULL " +
            ", constraint pk primary key (n1 DESC, n2 DESC, n3 DESC, d1 DESC))";

        Object[][] insertedRows = new Object[][]{
            {10, bdec(10.2), 21l, date(1, 10, 2001)}, {20, bdec(20.2), 32l, date(2, 6, 2001)}, {30, bdec(30.2), 43l, date(3, 1, 2001)}};
        Object[][] expectedRows = new Object[][]{
            {31l, bdec(32.2), 46l, date(3, 5, 2001)}, {21l, bdec(22.2), 35l, date(2, 10, 2001)}, {11l, bdec(12.2), 24l, date(1, 14, 2001)}};

        SQ_order_by_desc = true;
        runQueryTest(ddl, upsert("n1", "n2", "n3", "d1"), select("n1+1, n2+2, n3+3", "d1+4"), insertedRows, expectedRows);
        SQ_order_by_desc = false;
    }
    
    @Test 
    public void subtractionOnDescCompositePK() throws Exception {
        printTestDescription();
        String ddl = null;
        if (tgtPH()) ddl = "CREATE TABLE " + TABLE + " (n1 INTEGER NOT NULL, n2 DECIMAL(10, 2) NOT NULL, n3 BIGINT NOT NULL, d1 DATE NOT NULL " + 
            "constraint pk primary key (n1 DESC, n2 DESC, n3 DESC, d1 DESC))";
        else if (tgtSQ()||tgtTR()) ddl = "CREATE TABLE " + TABLE + " (n1 INTEGER NOT NULL, n2 DECIMAL(10, 1) NOT NULL, n3 BIGINT NOT NULL, d1 DATE NOT NULL " +
            ", constraint pk primary key (n1 DESC, n2 DESC, n3 DESC, d1 DESC))";

        Object[][] insertedRows = new Object[][]{
            {10, bdec(10.2), 21l, date(1, 10, 2001)}, {20, bdec(20.2), 32l, date(2, 6, 2001)}, {30, bdec(30.2), 43l, date(3, 10, 2001)}};
        Object[][] expectedRows = new Object[][]{
            {29l, bdec(28.2), 40l, date(3, 6, 2001)}, {19l, bdec(18.2), 29l, date(2, 2, 2001)}, {9l, bdec(8.2), 18l, date(1, 6, 2001)}};

        SQ_order_by_desc = true;
        runQueryTest(ddl, upsert("n1", "n2", "n3", "d1"), select("n1-1, n2-2, n3-3", "d1-4"), insertedRows, expectedRows);
        SQ_order_by_desc = false;
    }
    
    @Test
    public void lessThanLeadingDescCompositePK() throws Exception {
        printTestDescription();
        String ddl = null;
        if (tgtPH()) ddl = "CREATE TABLE " + TABLE + " (id INTEGER NOT NULL, date DATE NOT NULL constraint pk primary key (id DESC, date))";
        else if (tgtSQ()||tgtTR()) ddl = "CREATE TABLE " + TABLE + " (id INTEGER NOT NULL, date1 DATE NOT NULL, constraint pk primary key (id DESC, date1))";

        Object[][] insertedRows = new Object[][]{{1, date(1, 1, 2012)}, {3, date(1, 1, 2013)}, {2, date(1, 1, 2011)}};
        Object[][] expectedRows = new Object[][]{{1, date(1, 1, 2012)}};
        if (tgtPH()) runQueryTest(ddl, upsert("id", "date"), insertedRows, expectedRows, new WhereCondition("id", "<", "2"));
        else if (tgtSQ()||tgtTR()) runQueryTest(ddl, upsert("id", "date1"), insertedRows, expectedRows, new WhereCondition("id", "<", "2"));
    }
    
    @Test
    public void lessThanTrailingDescCompositePK() throws Exception {
        printTestDescription();
        String ddl = null;
        if (tgtPH()) ddl = "CREATE TABLE " + TABLE + " (id INTEGER NOT NULL, date DATE NOT NULL constraint pk primary key (id DESC, date))";
        else if (tgtSQ()||tgtTR()) ddl = "CREATE TABLE " + TABLE + " (id INTEGER NOT NULL, date1 DATE NOT NULL, constraint pk primary key (id DESC, date1))";

        Object[][] insertedRows = new Object[][]{{1, date(1, 1, 2002)}, {3, date(1, 1, 2003)}, {2, date(1, 1, 2001)}};
        Object[][] expectedRows = new Object[][]{{2, date(1, 1, 2001)}};

        if (tgtPH()) runQueryTest(ddl, upsert("id", "date"), insertedRows, expectedRows, new WhereCondition("date", "<", "TO_DATE('02-02-2001','mm-dd-yyyy')"));
        else if (tgtSQ()||tgtTR()) runQueryTest(ddl, upsert("id", "date1"), insertedRows, expectedRows, new WhereCondition("date1", "<", "DATE '02/02/2001'"));
    }
    
    private void runQueryTest(String ddl, String columnName, Object[][] rows, Object[][] expectedRows) throws Exception {
        runQueryTest(ddl, new String[]{columnName}, rows, expectedRows, null);
    }
    
    private void runQueryTest(String ddl, String[] columnNames, Object[][] rows, Object[][] expectedRows) throws Exception {
        runQueryTest(ddl, columnNames, rows, expectedRows, null);
    }
    
    private void runQueryTest(String ddl, String[] columnNames, Object[][] rows, Object[][] expectedRows, WhereCondition condition) throws Exception {
        runQueryTest(ddl, columnNames, columnNames, rows, expectedRows, condition, null, null);
    }
    
    private void runQueryTest(String ddl, String[] columnNames, String[] projections, Object[][] rows, Object[][] expectedRows) throws Exception {
        runQueryTest(ddl, columnNames, projections, rows, expectedRows, null, null, null);
    }
    
    private void runQueryTest(String ddl, String[] columnNames, String[] projections, Object[][] rows, Object[][] expectedRows, HavingCondition havingCondition) throws Exception {
        runQueryTest(ddl, columnNames, projections, rows, expectedRows, null, havingCondition, null);
    }
    

    private static void runQueryTest(
        String ddl, 
        String[] columnNames, 
        String[] projections, 
        Object[][] rows, Object[][] expectedRows, 
        WhereCondition whereCondition, 
        HavingCondition havingCondition,
        OrderBy orderBy) 
        throws Exception 
    {
        try {
            conn.createStatement().execute(ddl);

            String columns = appendColumns(columnNames);
            String placeholders = appendPlaceholders(columnNames);
            String dml = null;
            if (tgtPH()||tgtTR()) dml = "UPSERT INTO " + TABLE + " (" + columns + ") VALUES(" + placeholders +")";
            else if (tgtSQ()) dml = "INSERT INTO " + TABLE + " (" + columns + ") VALUES(" + placeholders +")";

            PreparedStatement stmt = conn.prepareStatement(dml);

            for (int row = 0; row < rows.length; row++) {
                for (int col = 0; col < rows[row].length; col++) {
                    Object value = rows[row][col];
                    stmt.setObject(col + 1, value);
                }
                stmt.execute();
            }
            
            String selectClause = "SELECT " + appendColumns(projections) + " FROM " + TABLE;

            for (WhereCondition whereConditionClause : new WhereCondition[]{whereCondition, WhereCondition.reverse(whereCondition)}) {
                String query = WhereCondition.appendWhere(whereConditionClause, selectClause);
                query = HavingCondition.appendHaving(havingCondition, query);
                query = OrderBy.appendOrderBy(orderBy, query);
                runQuery(conn, query, expectedRows);
            }
            
            if (orderBy != null) {
                orderBy = OrderBy.reverse(orderBy);
                String query = WhereCondition.appendWhere(whereCondition, selectClause);
                query = HavingCondition.appendHaving(havingCondition, query);
                query = OrderBy.appendOrderBy(orderBy, query);
                runQuery(conn, query, reverse(expectedRows));
            }
        } finally {
        }
    }
    
    private static String appendColumns(String[] columnNames) {
        String appendedColumns = "";
        for (int i = 0; i < columnNames.length; i++) {                
            appendedColumns += columnNames[i];
            if (i < columnNames.length - 1) {
                appendedColumns += ",";
            }
        }
        return appendedColumns;
    }
    
    private static String appendPlaceholders(String[] columnNames) {
        String placeholderList = "";
        for (int i = 0; i < columnNames.length; i++) {                
            placeholderList += "?";
            if (i < columnNames.length - 1) {
                placeholderList += ",";
            }
        }
        return placeholderList;        
    }
    
    private static void runQuery(Connection connection, String query, Object[][] expectedValues) throws Exception {

        if (tgtSQ()||tgtTR()) {
            if (! query.toLowerCase().contains("order by")) {
                query += " order by 1";
                if (SQ_order_by_desc)
                    query += " desc";
            }
        }

        PreparedStatement stmt = connection.prepareStatement(query);
        ResultSet rs = stmt.executeQuery();
        int rowCounter = 0;
        while (rs.next()) {
            if (rowCounter == expectedValues.length) {
                Assert.assertEquals("Exceeded number of expected rows for query" + query, expectedValues.length, rowCounter+1);
            }
            Object[] cols = new Object[expectedValues[rowCounter].length];
            for (int colCounter = 0; colCounter < expectedValues[rowCounter].length; colCounter++) {
                cols[colCounter] = rs.getObject(colCounter+1);
            }
            /* TRAF: For some reason, the following code does not work for
             * DATE data type.  The comparision returns false even if 2
             * dates are the same.  I think it has something to do with 
             * Equals() acutally compares the object references when it comes
             * to an object?  Replace it with the new comparision method.
            Assert.assertArrayEquals("Unexpected result for query " + query, expectedValues[rowCounter], cols);
             */
            /* TRAF */
            for (int colCounter = 0; colCounter < expectedValues[rowCounter].length; colCounter++) {
                /* Do string comparision. */
                Assert.assertTrue("Unexpected result for query " + query + " expected " + expectedValues[rowCounter][colCounter].toString() + ", was " + cols[colCounter].toString(), expectedValues[rowCounter][colCounter].toString().equals(cols[colCounter].toString()));
            }
            /* end of TRAF */

            rowCounter++;
        }
        Assert.assertEquals("Unexpected number of rows for query " + query, expectedValues.length, rowCounter);
    }
    
    private static Object[][] reverse(Object[][] rows) {
        Object[][] reversedArray = new Object[rows.length][];
        System.arraycopy(rows, 0, reversedArray, 0, rows.length);
        ArrayUtils.reverse(reversedArray);
        return reversedArray;
    }
    
    private Date date(int month, int day, int year) {
        Calendar cal = new GregorianCalendar();
        cal.set(Calendar.MONTH, month-1);
        cal.set(Calendar.DAY_OF_MONTH, day);
        cal.set(Calendar.YEAR, year);
        cal.set(Calendar.HOUR_OF_DAY, 10);
        cal.set(Calendar.MINUTE, 2);
        cal.set(Calendar.SECOND, 5);
        cal.set(Calendar.MILLISECOND, 101);
        Date d = new Date(cal.getTimeInMillis()); 
        return d;
    }
        
    private String[] upsert(String...args) {
        return args;
    }
    
    private String[] select(String...args) {
        return args;
    }
    
    private BigDecimal bdec(double d) {
        return BigDecimal.valueOf(d);
    }
    
    private BigInteger bint(long l) {
        return BigInteger.valueOf(l);
    }    
    
    private static class WhereCondition {
        final String lhs;
        final String operator;
        final String rhs;
    
        WhereCondition(String lhs, String operator, String rhs) {
            this.lhs = lhs;
            this.operator = operator;
            this.rhs = rhs;
        }
        
        static WhereCondition reverse(WhereCondition whereCondition) {
            
            if (whereCondition == null) {
                return null; 
            }
            
            if (whereCondition.operator.equalsIgnoreCase("IN") || whereCondition.operator.equalsIgnoreCase("LIKE")) {
                return whereCondition;
            } else {
                return new WhereCondition(whereCondition.rhs, whereCondition.getReversedOperator(), whereCondition.lhs);
            }
        }
        
        static String appendWhere(WhereCondition whereCondition, String query) {
             if (whereCondition == null) {
                 return query;
             }
            return query + " WHERE " + whereCondition.lhs + " " + whereCondition.operator + " " + whereCondition.rhs;
        }
        
        private String getReversedOperator() {
            if (operator.equals("<")) {
                return ">";
            } else if (operator.equals(">")) {
                return "<";
            }
            return operator;
        }
    }
    
    private static class HavingCondition {
        
        private String groupby;
        private String having;
        
        HavingCondition(String groupby, String having) {
            this.groupby = groupby;
            this.having = having;
        }
        
        static String appendHaving(HavingCondition havingCondition, String query) {
            if (havingCondition == null) {
                return query;
            }
            return query + " GROUP BY " + havingCondition.groupby + " HAVING " + havingCondition.having + " ";
        }
    }
    
    private static class OrderBy {
        
        enum Direction {
            
            ASC, DESC;
            
            Direction reverse() {
                if (this == ASC) {
                    return DESC;
                }
                return ASC;
            }
        }
        
        private List<String> columnNames = Lists.newArrayList();
        private List<Direction> directions = Lists.newArrayList();
        
        OrderBy() {            
        }
        
        OrderBy(String columnName, Direction orderBy) {
            add(columnName, orderBy);
        }
        
        void add(String columnName, Direction direction) {
            columnNames.add(columnName);
            directions.add(direction);
        }
        
        static OrderBy reverse(OrderBy orderBy) {
            
            if (orderBy == null) {
                return null;
            }
            
            List<Direction> reversedDirections = Lists.newArrayList();
            for (Direction dir : orderBy.directions) {
                reversedDirections.add(dir.reverse());
            }
            OrderBy reversedOrderBy = new OrderBy();
            reversedOrderBy.columnNames = orderBy.columnNames;
            reversedOrderBy.directions = reversedDirections;
            return reversedOrderBy;
        }
        
        static String appendOrderBy(OrderBy orderBy, String query) {
            if (orderBy == null || orderBy.columnNames.isEmpty()) {
                return query;
            }
            query += " ORDER BY ";
            for (int i = 0; i < orderBy.columnNames.size(); i++) {
                query += orderBy.columnNames.get(i) + " " + orderBy.directions.get(i).toString() + " ";
            }
          
            if (tgtPH() || tgtTR()) 
                query += " LIMIT 1000 ";

            return query;
        }        
    }
}