Package org.voltdb.regressionsuites

Source Code of org.voltdb.regressionsuites.TestGroupBySuite

/* This file is part of VoltDB.
* Copyright (C) 2008-2014 VoltDB Inc.
*
* Permission is hereby granted, free of charge, to any person obtaining
* a copy of this software and associated documentation files (the
* "Software"), to deal in the Software without restriction, including
* without limitation the rights to use, copy, modify, merge, publish,
* distribute, sublicense, and/or sell copies of the Software, and to
* permit persons to whom the Software is furnished to do so, subject to
* the following conditions:
*
* The above copyright notice and this permission notice shall be
* included in all copies or substantial portions of the Software.
*
* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
* EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
* MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
* IN NO EVENT SHALL THE AUTHORS BE LIABLE FOR ANY CLAIM, DAMAGES OR
* OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE,
* ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
* OTHER DEALINGS IN THE SOFTWARE.
*/

package org.voltdb.regressionsuites;

import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;

import org.voltdb.BackendTarget;
import org.voltdb.VoltTable;
import org.voltdb.VoltTableRow;
import org.voltdb.VoltType;
import org.voltdb.client.Client;
import org.voltdb.client.NoConnectionsException;
import org.voltdb.client.ProcCallException;
import org.voltdb.client.SyncCallback;
import org.voltdb.compiler.VoltProjectBuilder;
import org.voltdb.planner.TestPlansGroupBy;

/*
* Functional tests of the statements compiled in the test suite
* org.voltdb.planner.TestPlansGroupBy.
*/

public class TestGroupBySuite extends RegressionSuite {

    static final Class<?>[] PROCEDURES = {
        org.voltdb_testprocs.regressionsuites.plansgroupbyprocs.CountT1A1.class,
        org.voltdb_testprocs.regressionsuites.plansgroupbyprocs.InsertF.class,
        org.voltdb_testprocs.regressionsuites.plansgroupbyprocs.InsertDims.class,
        org.voltdb_testprocs.regressionsuites.plansgroupbyprocs.SumGroupSingleJoin.class };

    /** Load 1 1's, 2 2's, 3 3's .. 10 10's and 1 11 */
    private int loaderNxN(Client client, int pkey) throws ProcCallException,
    IOException, NoConnectionsException {
        VoltTable vt;
        //String qs;
        // Insert some known data. Insert {1, 2, 2, 3, 3, 3, ... }
        for (int i = 1; i <= 10; i++) {
            for (int j = 0; j < i; j++) {
                //qs = "INSERT INTO T1 VALUES (" + pkey++ + ", " + i + ");";
                vt = client.callProcedure("T1Insert", pkey++, i).getResults()[0];
                assertTrue(vt.getRowCount() == 1);
                // assertTrue(vt.asScalarLong() == 1);
            }
        }
        // also add a single "11" to make verification a bit saner
        // (so that the table results of "count" and "group by" can be
        // distinguished)
        vt = client.callProcedure("@AdHoc", "insert into t1 values (" + pkey++
                + ",11);").getResults()[0];
        assertTrue(vt.getRowCount() == 1);
        // assertTrue(vt.asScalarLong() == 1);
        return pkey;
    }

    /** Load 1 1's, 2 2's, 3 3's .. 10 10's and 1 11 */
    private int loaderNxNb(Client client, int pkey) throws ProcCallException,
    IOException, NoConnectionsException {
        VoltTable vt;
        //String qs;
        // Insert some known data. Insert {1, 2, 2, 3, 3, 3, ... }
        for (byte i = 1; i <= 10; i++) {
            for (byte j = 0; j < i; j++) {
                // "INSERT INTO B VALUES (" + pkey++ + ", " + [i,i,0,0,i,i] + ");";
                byte b[] = { i, i, 0, 0, i, i };
                vt = client.callProcedure("BInsert", pkey++, b).getResults()[0];
                assertTrue(vt.getRowCount() == 1);
            }
        }
        // also add a single "11" to make verification a bit saner
        // (so that the table results of "count" and "group by" can be
        // distinguished)
        vt = client.callProcedure("@AdHoc", "insert into B values (" + pkey++
                + ",'0B0B00000B0B');").getResults()[0];
        assertTrue(vt.getRowCount() == 1);
        return pkey;
    }

    /** load known data to F without loading the Dimension tables
     * @throws InterruptedException */
    private int loadF(Client client, int pkey) throws NoConnectionsException,
    ProcCallException, IOException, InterruptedException {
        VoltTable vt;

        // if you want to test synchronous latency, this
        //  is a good variable to change
        boolean async = true;

        // val1 = constant value 2
        // val2 = i * 10
        // val3 = 0 for even i, 1 for odd i

        for (int i = 0; i < 1000; i++) {

            int f_d1 = i % 10; // 10 unique dim1s
            int f_d2 = i % 50; // 50 unique dim2s
            int f_d3 = i % 100; // 100 unique dim3s

            boolean done;
            SyncCallback cb = new SyncCallback();
            do {
                done = client.callProcedure(cb, "InsertF", pkey++, f_d1, f_d2, f_d3,
                                            2, (i * 10), (i % 2));
                if (!done) {
                    client.backpressureBarrier();
                }
            } while (!done);


            if (!async) {
                cb.waitForResponse();
                vt = cb.getResponse().getResults()[0];
                assertTrue(vt.getRowCount() == 1);
                // assertTrue(vt.asScalarLong() == 1);
            }
        }

        client.drain();

        return pkey;
    }

    /** load the dimension tables */
    private void loadDims(Client client) throws NoConnectionsException,
    ProcCallException, IOException {
        client.callProcedure("InsertDims");
    }

    /** select A1 from T1 group by A1 */
    public void testSelectAGroupbyA() throws IOException, ProcCallException {
        Client client = this.getClient();
        VoltTable vt;

        loaderNxN(client, 0);

        vt = client.callProcedure("@AdHoc", "Select * from T1").getResults()[0];
        System.out.println("T1-*:" + vt);

        // execute the query
        vt = client.callProcedure("@AdHoc", "SELECT A1 from T1 group by A1").getResults()[0];

        // one row per unique value of A1
        System.out.println("testSelectAGroubyA: " + vt);
        assertTrue(vt.getRowCount() == 11);

        // Selecting A1 - should get values 1 through 11
        // once each. These results aren't necessarily ordered.
        int found[] = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
        while (vt.advanceRow()) {
            Integer A1 = (Integer) vt.get(0, VoltType.INTEGER);
            assertTrue(A1 <= 11);
            assertTrue(A1 > 0);
            found[A1.intValue()] += 1;
        }
        assertEquals(0, found[0]);
        for (int i = 1; i < 12; i++) {
            assertEquals(1, found[i]);
        }
    }

    /** select B_VAL1 from B group by B_VAL1 */
    public void testSelectGroupbyVarbinary() throws IOException, ProcCallException {
        Client client = this.getClient();
        VoltTable vt;

        loaderNxNb(client, 0);

        vt = client.callProcedure("@AdHoc", "Select * from B").getResults()[0];
        System.out.println("B-*:" + vt);

        // execute the query
        vt = client.callProcedure("@AdHoc", "SELECT B_VAL1 from B group by B_VAL1").getResults()[0];

        // one row per unique value of A1
        System.out.println("testSelectGroubyVarbinary: " + vt);
        assertTrue(vt.getRowCount() == 11);

        // Selecting B_VAL1 - should get byte values "1,1,1,1,1,1" through "11,11,11,11,11,11"
        // once each. These results aren't necessarily ordered.
        byte found[] = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
        while (vt.advanceRow()) {
            byte[] b_val1 = vt.getVarbinary(0);
            assertTrue(b_val1.length == 6);
            assertTrue(b_val1[0] <= 11);
            assertTrue(b_val1[5] <= 11);
            assertTrue(b_val1[0] > 0);
            assertTrue(b_val1[5] > 0);
            found[b_val1[0]] += 1;
        }
        assertEquals(0, found[0]);
        for (int i = 1; i < 12; i++) {
            assertEquals(1, found[i]);
        }
    }

    /** select count(A1) from T1 group by A1 */
    public void testSelectCountAGroupbyA() throws IOException,
    ProcCallException {
        Client client = this.getClient();
        VoltTable vt;

        loaderNxN(client, 0);

        vt = client.callProcedure("@AdHoc",
        "select count(A1), A1 from T1 group by A1").getResults()[0];
        System.out.println("testSelectCountAGroupbyA result: " + vt);
        assertTrue(vt.getRowCount() == 11);

        // Selecting count(A1) - should get two counts of 1 and one count each
        // of 2-10: (1, 1, 2, 3, 4, .. 10).
        // These results aren't necessarily ordered
        int found[] = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
        while (vt.advanceRow()) {
            Integer A1 = (Integer) vt.get(0, VoltType.INTEGER);
            assertTrue(A1 <= 10);
            assertTrue(A1 > 0);
            found[A1.intValue()] += 1;
        }
        assertEquals(0, found[0]);
        assertEquals(2, found[1]);
        for (int i = 2; i < 11; i++) {
            assertEquals(1, found[i]);
        }
    }

    /** select A1, sum(A1) from T1 group by A1 */
    public void testSelectSumAGroupbyA() throws IOException, ProcCallException {
        VoltTable vt;
        Client client = this.getClient();
        loaderNxN(client, 0);

        String qs = "select A1, sum(A1) from T1 group by A1";

        vt = client.callProcedure("@AdHoc", qs).getResults()[0];
        System.out.println("testSelectSumAGroupbyA result: " + vt);
        assertEquals(11, vt.getRowCount());

        int found[] = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
        while (vt.advanceRow()) {
            Integer a1 = (Integer) vt.get(0, VoltType.INTEGER);
            Integer sum = (Integer) vt.get(1, VoltType.INTEGER);
            found[a1.intValue()] += 1;
            // A1 = 11 is a special case
            if (a1.intValue() == 11)
                assertEquals(11, sum.intValue());
            // every other n appears n times. The sum is therefore n x n.
            else
                assertEquals(a1.intValue() * a1.intValue(), sum.intValue());
        }
        assertEquals(0, found[0]);
        for (int i = 1; i < 12; i++)
            assertEquals(found[i], 1)// one result for each unique A1
    }

    /** select count(distinct A1) from T1 */
    public void testSelectCountDistinct() throws IOException, ProcCallException {
        VoltTable vt;
        Client client = getClient();
        loaderNxN(client, 0);
        vt = client
        .callProcedure("@AdHoc", "select count(distinct A1) from T1").getResults()[0];
        assertTrue(vt.getRowCount() == 1);

        // there are 11 distinct values for A1
        while (vt.advanceRow()) {
            Integer A1 = (Integer) vt.get(0, VoltType.INTEGER);
            assertEquals(11, A1.intValue());
        }
    }

    /** select count(A1) from T1 */
    public void testSelectCount() throws IOException, ProcCallException {
        VoltTable vt;
        Client client = getClient();
        loaderNxN(client, 0);
        vt = client.callProcedure("@AdHoc", "select count(A1) from T1").getResults()[0];
        assertTrue(vt.getRowCount() == 1);

        // there are 56 rows in the table 1 + 2 + 3 + .. + 10 + 1
        while (vt.advanceRow()) {
            Integer A1 = (Integer) vt.get(0, VoltType.INTEGER);
            System.out.println("select count = " + A1.intValue());
            assertEquals(56, A1.intValue());
        }
    }

    /** select distinct a1 from t1 */
    public void testSelectDistinctA() throws IOException, ProcCallException {
        Client client = this.getClient();
        VoltTable vt;

        loaderNxN(client, 0);

        vt = client.callProcedure("@AdHoc", "select distinct a1 from t1").getResults()[0];
        System.out.println("testSelectDistinctA result row("
                + vt.getColumnName(0) + ") " + vt);

        // valid result is the set {1,2,...,11}
        int found[] = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
        while (vt.advanceRow()) {
            Integer A1 = (Integer) vt.get(0, VoltType.INTEGER);
            System.out.println("\tdistinct value: " + A1.intValue());
            assertEquals("A1", vt.getColumnName(0));
            assertTrue(A1 <= 11);
            assertTrue(A1 > 0);
            found[A1.intValue()] += 1;
        }
        assertEquals(0, found[0]);
        for (int i = 1; i < 12; i++) {
            assertEquals(1, found[i]);
        }
    }

    /**
     * distributed sums of a partitioned table
     * select sum(F_VAL1), sum(F_VAL2), sum(F_VAL3) from F
     * @throws InterruptedException
     */
    public void testDistributedSum() throws IOException, ProcCallException, InterruptedException {
        VoltTable vt;
        Client client = getClient();
        loadF(client, 0);

        String qs = "select sum(F_VAL1), sum(F_VAL2), sum(F_VAL3) from F";

        vt = client.callProcedure("@AdHoc", qs).getResults()[0];
        System.out.println("testDistributedSum result: " + vt);
        assertTrue(vt.getRowCount() == 1);
        vt.advanceRow();
        Integer sum1 = (Integer) vt.get(0, VoltType.INTEGER);
        assertEquals(2000, sum1.intValue());
        Integer sum2 = (Integer) vt.get(1, VoltType.INTEGER);
        assertEquals(4995000, sum2.intValue());
        Integer sum3 = (Integer) vt.get(2, VoltType.INTEGER);
        assertEquals(500, sum3.intValue());

        // Also, regression test ENG-199 -- duplicate aggregation column.
        vt = client.callProcedure("@AdHoc", "select sum(F_VAL1), sum(F_VAL1) from F").getResults()[0];
        System.out.println("testDistributedSum result: " + vt);
        assertTrue(vt.getRowCount() == 1);
        vt.advanceRow();
        sum1 = (Integer) vt.get(0, VoltType.INTEGER);
        assertEquals(2000, sum1.intValue());
        try {
            sum2 = (Integer) vt.get(1, VoltType.INTEGER);
            assertEquals(2000, sum2.intValue());
        } catch ( Exception exc ) {
            fail("Apparently failing like ENG-199 with: " + exc);
        }
    }

    /**
     * distributed sums of a view
     * select sum(V.SUM_V1), sum(V.SUM_V2), sum(V.SUM_V3) from V
     * @throws InterruptedException
     */
    public void testDistributedSum_View() throws IOException, ProcCallException, InterruptedException {
        VoltTable vt;
        Client client = getClient();
        loadF(client, 0);

        String qs = "select sum(V.SUM_v1), sum(V.SUM_V2), sum(V.SUM_V3) from V";

        vt = client.callProcedure("@AdHoc", qs).getResults()[0];
        System.out.println("testDistributedSum_View result: " + vt);
        assertTrue(vt.getRowCount() == 1);
        while (vt.advanceRow()) {
            Integer sum1 = (Integer) vt.get(0, VoltType.INTEGER);
            assertEquals(2000, sum1.intValue());
            Integer sum2 = (Integer) vt.get(1, VoltType.INTEGER);
            assertEquals(4995000, sum2.intValue());
            Integer sum3 = (Integer) vt.get(2, VoltType.INTEGER);
            assertEquals(500, sum3.intValue());
        }
    }

    /**
     * distributed sums of a view (REDUNDANT GROUP BY)
     * select V.D1_PKEY, sum(V.SUM_V1), sum(V.SUM_V2), sum(V.SUM_V3)
     * from V group by V.V_D1_PKEY
     * @throws InterruptedException
     */
    public void testDistributedSumAndGroup() throws NoConnectionsException,
    ProcCallException, IOException, InterruptedException {
        VoltTable vt;
        Client client = getClient();
        loadF(client, 0);

        String qs = "select V.V_D1_PKEY, sum(V.SUM_V1), sum(V.SUM_V2), sum(V.SUM_V3) "
            + "from V group by V.V_D1_PKEY";

        vt = client.callProcedure("@AdHoc", qs).getResults()[0];
        System.out.println("testDistributedSumAndJoin result: " + vt);
        assert (vt.getRowCount() == 10); // 10 unique values for dim1 which is
        // the grouping col

        int found[] = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
        while (vt.advanceRow()) {
            Integer d1 = (Integer) vt.get(0, VoltType.INTEGER);
            Integer s1 = (Integer) vt.get(1, VoltType.INTEGER);
            Integer s2 = (Integer) vt.get(2, VoltType.INTEGER);
            Integer s3 = (Integer) vt.get(3, VoltType.INTEGER);

            // track that 10 dim1s are in the final group
            found[d1.intValue()] += 1;
            // sum1 is const 2. 100 dim1 instances / group
            assertEquals(200, s1.intValue());
            // sum of every 10th i * 10 in this range
            assertTrue(495000 <= s2.intValue() && 504000 >= s2.intValue());
            // sum3 alternates 0|1. There are 100 dim1 instances / group
            if ((d1.intValue() % 2) == 0)
                assertEquals(s3.intValue(), 0);
            else
                assertEquals(s3.intValue(), 100);

        }
        for (int i = 0; i < 10; i++)
            assertEquals(1, found[i]);

    }

    /**
     * distributed sum of a view with a group by and join on a replicated table.
     * (REDUNDANT GROUP BY)
     * select D1.D1_NAME, sum(V.SUM_V1), sum(V.SUM_V2), sum(V.SUM_V3)
     * from D1, V where D1.D1_PKEY = V.V_D1_PKEY group by D1.D1_NAME
     * @throws InterruptedException
     */
    public void testDistributedSumGroupSingleJoin()
    throws NoConnectionsException, ProcCallException, IOException, InterruptedException {
        VoltTable vt;
        Client client = getClient();

        loadF(client, 0);
        loadDims(client);

        vt = client.callProcedure("SumGroupSingleJoin").getResults()[0];
        assertTrue(vt.getRowCount() == 10);

        int found[] = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
        while (vt.advanceRow()) {
            String d1 = (String) vt.get(0, VoltType.STRING);
            Integer s1 = (Integer) vt.get(1, VoltType.INTEGER);
            Integer s2 = (Integer) vt.get(2, VoltType.INTEGER);
            Integer s3 = (Integer) vt.get(3, VoltType.INTEGER);
            // sum1 is const 2; 100 dim1 instances per group.
            assertEquals(200, s1.intValue());
            assertTrue(495000 <= s2.intValue() && 504000 >= s2.intValue());
            assertTrue(s3.intValue() == 0 || s3.intValue() == 100);

            Integer di = Integer.valueOf(d1.substring(3));
            found[di.intValue()] += 1;
        }
        for (int i = 0; i < 10; i++)
            assertEquals(1, found[i]);
    }

    /**
     * distributed sum of a view with a join on a replicated table for one dim value
     * (REDUNDANT GROUP BY)
     * select D1.D1_NAME, sum(V.SUM_V1), sum(V.SUM_V2), sum(V.SUM_V3)
     * from D1, V where D1.D1_PKEY = V.V_D1_PKEY and D1.D1_PKEY = ?
     * group by D1_NAME
     * @throws InterruptedException
     */
    public void testDistributedSumGroupSingleJoinOneDim() throws IOException,
    ProcCallException, InterruptedException {
        VoltTable vt;
        Client client = getClient();
        loadF(client, 0);
        loadDims(client);

        String qs = "select D1.D1_NAME, sum(V.SUM_V1), sum(V.SUM_V2), sum(V.SUM_V3) "
            + " from D1, V where D1.D1_PKEY = V.V_D1_PKEY and D1.D1_PKEY = 5"
            + " group by D1.D1_NAME";

        vt = client.callProcedure("@AdHoc", qs).getResults()[0];
        assertTrue(vt.getRowCount() == 1);
        System.out.println("testDistributedSumGroupSingleJoinOneDim: " + vt);
        while (vt.advanceRow()) {
            String d1 = (String) vt.get(0, VoltType.STRING);
            Integer s1 = (Integer) vt.get(1, VoltType.INTEGER);
            Integer s2 = (Integer) vt.get(2, VoltType.INTEGER);
            Integer s3 = (Integer) vt.get(3, VoltType.INTEGER);

            assertEquals(d1, "D1_5");             // name is D1_%d where %d is pkey
            assertEquals(200, s1.intValue());     // dim1 present 100 times. s1 == 2.
            assertEquals(500000, s2.intValue())// verified in hsql.
            assertEquals(100, s3.intValue());     // odd dim1 == 1 in s3 ( x100 )
        }
    }

    /**
     * distributed sum of a view with 3-way join on replicated tables
     * (REDUNDANT GROUP BY)
     * select D1.D1_NAME, D2.D2_NAME, sum(V.SUM_V1), sum(V.SUM_V2), sum(V.SUM_V3)
     * from D1, D2, V where V.V_D1_PKEY = D1.D1_PKEY and V.V_D2_PKEY = D2.D2_PKEY
     * group by D1_NAME, D2_NAME
     * @throws InterruptedException
     */
    public void testDistributedSumGroupMultiJoin() throws IOException,
    ProcCallException, InterruptedException {
        VoltTable vt;
        Client client = getClient();
        loadF(client, 0);
        loadDims(client);

        String qs = "select D1.D1_NAME, D2.D2_NAME, sum(V.SUM_V1), sum(V.SUM_V2), sum(V.SUM_V3) "
            + "from V, D1, D2 "
            + "where V.V_D1_PKEY = D1.D1_PKEY and V.V_D2_PKEY = D2.D2_PKEY "
            + "group by D1.D1_NAME, D2.D2_NAME";

        vt = client.callProcedure("@AdHoc", qs).getResults()[0];
        System.out.println("DistributedSumGroupMultiJoin: " + vt);

        // sort the output by d2's value
        ArrayList<VoltTableRow> sorted = new ArrayList<VoltTableRow>();
        while (vt.advanceRow()) {
            String d1 = (String) vt.get(0, VoltType.STRING);
            String d2 = (String) vt.get(1, VoltType.STRING);
            System.out.println("Adding Row: " + d1 + ", " + d2);
            // this will add the active row of vt
            sorted.add(vt.cloneRow());
        }
        System.out.println("DSGMJonedim"); debug(sorted);
        Collections.sort(sorted, new VRowComparator<VoltTableRow>());
        System.out.println("DSGMJonedim: "); debug(sorted);

        // 5 unique d2's for each of 10 d1's (so 10 * 5 rows)
        assertEquals(50, vt.getRowCount());
        Integer i = 0, j = 0;
        for (VoltTableRow row : sorted) {
            String d1_name = "D1_" + i;
            String d2_name = "D2_" + (i + (j * 10));
            int v3 = (i % 2) * 20;   // 20 unique combinations of d1, d2, d3

            String d1 = (String)   row.get(0, VoltType.STRING);
            String d2 = (String)   row.get(1, VoltType.STRING);
            Integer s1 = (Integer) row.get(2, VoltType.INTEGER);
            Integer s3 = (Integer) row.get(4, VoltType.INTEGER);

            assertEquals(d1, d1_name);
            assertEquals(d2, d2_name);
            assertEquals(s1.intValue(), 40);
            assertEquals(s3.intValue(), v3);
            j++;  if (j == 5) { i++; j = 0; }
        }
    }

    /**
     * distributed sum of a view with 3-way join on replicated table for
     * specific dim1 (REDUNDANT GROUP BY)
     * select D1.D1_NAME, D2.D2_NAME, sum(V.SUM_V1),
     * sum(V.SUM_V2), sum(V.SUM_V3) from D1, D2, V where V.V_D1_PKEY =
     * D1.D1_PKEY and V.V_D2_PKEY = D2.D2_PKEY and D1.D1_PKEY = ?
     * group by D1_NAME, D2_NAME
     * @throws InterruptedException
     */
    public void testDistributedSumGroupMultiJoinOneDim() throws IOException,
    ProcCallException, InterruptedException {
        VoltTable vt;
        Client client = getClient();
        loadF(client, 0);
        loadDims(client);

        String qs = "select D1.D1_NAME, D2.D2_NAME, sum(V.SUM_V1), sum(V.SUM_V2), sum(V.SUM_V3) "
            + "from D1, D2, V "
            + "where V.V_D1_PKEY = D1.D1_PKEY and V.V_D2_PKEY = D2.D2_PKEY and D1.D1_PKEY = 6 "
            + "group by D1.D1_NAME, D2.D2_NAME;";

        vt = client.callProcedure("@AdHoc", qs).getResults()[0];
        // 5 unique values of d2 for each value of d1 (and a single d1 value is selected above)
        assertEquals(vt.getRowCount(), 5);

        // sort the output by d2's value
        ArrayList<VoltTableRow> sorted = new ArrayList<VoltTableRow>();
        while (vt.advanceRow()) {
            // this will add the active row of vt
            sorted.add(vt.cloneRow());
        }
        System.out.println("DSGMJonedim"); debug(sorted);
        Collections.sort(sorted, new VRowComparator<VoltTableRow>());
        System.out.println("DSGMJonedim: "); debug(sorted);

        int i = 0;
        for (VoltTableRow row : sorted) {
            String d2_name = "D2_" + ((i * 10) + 6);

            String d1 = (String)   row.get(0, VoltType.STRING);
            String d2 = (String)   row.get(1, VoltType.STRING);
            Integer s1 = (Integer) row.get(2, VoltType.INTEGER);
            Integer s3 = (Integer) row.get(4, VoltType.INTEGER);

            System.out.println("D2 expected: " + d2_name + " actual: " + d2);

            assertEquals(d1, "D1_6");
            assertEquals(d2, d2_name);
            assertEquals(s1.intValue(), 40)// 20 unique combinations * 2.
            assertEquals(s3.intValue(), 0);   // all even d1's are 0 in s3
            i++;
        }
    }

    /**
     * distributed sum of a view with 4-way join on replicated tables for
     * specific dim1, dim2 (REDUNDANT GROUP BY)
     * select D1.D1_NAME, D2.D2_NAME, sum(V.SUM_V1), sum(V.SUM_V2), sum(V.SUM_V3)
     * from D1, D2, V where V.V_D1_PKEY = D1.D1_PKEY and V.V_D2_PKEY = D2.D2_PKEY
     * and D1.D1_PKEY = ? and D2.D2_PKEY = ? group by D1_NAME, D2_NAME
     * @throws InterruptedException
     */
    public void testDistributedSumGroupMultiJoinTwoDims() throws IOException, ProcCallException, InterruptedException {
        VoltTable vt;
        Client client = getClient();
        loadF(client, 0);
        loadDims(client);

        String qs = "select D1.D1_NAME, D2.D2_NAME, D3.D3_NAME, sum(V.SUM_V1), sum(V.SUM_V2), sum(V.SUM_V3) "
            + "from D1, D2, D3, V "
            + "where V.V_D1_PKEY = D1.D1_PKEY and V.V_D2_PKEY = D2.D2_PKEY and V.V_D3_PKEY = D3.D3_PKEY "
            +        "and D1.D1_PKEY = 6 and D2.D2_PKEY = 26 "
            + "group by D1.D1_NAME, D2.D2_NAME, D3.D3_NAME;";

        vt = client.callProcedure("@AdHoc", qs).getResults()[0];
        System.out.println("MultiJoin3Dims: " + vt);

        // output looks like this - in either ordering
        // D1_6, D2_26, D3_76, 20, 52600, 0,
        // D1_6, D2_26, D3_26, 20, 47600, 0,
        while (vt.advanceRow()) {
            String d1 = (String)   vt.get(0, VoltType.STRING);
            String d2 = (String)   vt.get(1, VoltType.STRING);
            String d3 = (String)   vt.get(2, VoltType.STRING);
            Integer s1 = (Integer) vt.get(3, VoltType.INTEGER);
            Integer s3 = (Integer) vt.get(4, VoltType.INTEGER);

            assertEquals("D1_6", d1);
            assertEquals("D2_26", d2);
            assertEquals(20, s1.intValue());
            if (d3.equals("D3_26"))
                assertEquals(47600, s3.intValue());
            else if (d3.equals("D3_76"))
                assertEquals(52600, s3.intValue());
            else
                fail();
        }
    }

    // Fix bug: serial grouping by an inline varchar field only has one group
    public void testENG6732_serialAggInlineVarchar() throws IOException, ProcCallException, InterruptedException {
        System.out.println("STARTING serial/parital aggregate test.....");
        String sql;
        VoltTable vt;

        Client client = this.getClient();

        String[] tbNames = {"VOTES", "VOTESBYTES"};

        for (String tbName : tbNames) {
            String proc = tbName + ".insert";
            client.callProcedure(proc, 1, "MA", 1);
            client.callProcedure(proc, 2, "RI", 2);
            client.callProcedure(proc, 3, "CA", 1);
            client.callProcedure(proc, 4, "MA", 2);
            client.callProcedure(proc, 5, "CA", 1);


            sql = "select state, count(*) from " + tbName + " group by state order by 1, 2";
            vt = client.callProcedure("@Explain", sql).getResults()[0];
            assertTrue(vt.toString().toLowerCase().contains("serial"));

            vt = client.callProcedure("@AdHoc", sql).getResults()[0];
            assertEquals(3, vt.getRowCount());
            vt.advanceRow(); assertEquals("CA", vt.getString(0)); assertEquals(2, vt.getLong(1));
            vt.advanceRow(); assertEquals("MA", vt.getString(0)); assertEquals(2, vt.getLong(1));
            vt.advanceRow(); assertEquals("RI", vt.getString(0)); assertEquals(1, vt.getLong(1));

            // test partial serial aggregate
            sql = " select state, contestant_number, count(*) from  " + tbName +
                  " group by state, contestant_number order by 1, 2";
            vt = client.callProcedure("@Explain", sql).getResults()[0];
            assertTrue(vt.toString().toLowerCase().contains("partial"));

            vt = client.callProcedure("@AdHoc", sql).getResults()[0];
            assertEquals(4, vt.getRowCount());
            vt.advanceRow(); assertEquals("CA", vt.getString(0)); assertEquals(1, vt.getLong(1)); assertEquals(2, vt.getLong(2));
            vt.advanceRow(); assertEquals("MA", vt.getString(0)); assertEquals(1, vt.getLong(1)); assertEquals(1, vt.getLong(2));
            vt.advanceRow(); assertEquals("MA", vt.getString(0)); assertEquals(2, vt.getLong(1)); assertEquals(1, vt.getLong(2));
            vt.advanceRow(); assertEquals("RI", vt.getString(0)); assertEquals(2, vt.getLong(1)); assertEquals(1, vt.getLong(2));
        }
    }


    public void testPartialAggregate() throws IOException, ProcCallException, InterruptedException {
        System.out.println("STARTING partial aggregate test.....");
        String sql;
        VoltTable vt;

        Client client = this.getClient();
        loadF(client, 0);

        // Have an index on column F_D1,
        // index keep F_D1 ordered but not enough ordering for serial aggregate for whole query.
        sql = "SELECT F_D1, F_D2, SUM(F_D3) FROM F GROUP BY F_D1, F_D2 ORDER BY 1, 2 LIMIT 5 OFFSET 3";
        vt = client.callProcedure("@Explain", sql).getResults()[0];
        assertTrue(vt.toString().toLowerCase().contains("partial"));
        vt = client.callProcedure("@AdHoc", sql).getResults()[0];
        validateTableOfLongs(vt, new long[][] {{0,30,1100}, {0,40,1300},
            {1,1,520}, {1,11,720},{1,21,920} });

        // Have an index on expression ABS(F_D1)
        // index keep F_D1 ordered but not enough ordering for serial aggregate for whole query.
        sql = "SELECT ABS(F_D1), F_D3, COUNT(*) FROM F GROUP BY ABS(F_D1), F_D3 ORDER BY 1, 2 LIMIT 5 OFFSET 8";
        vt = client.callProcedure("@Explain", sql).getResults()[0];
        assertTrue(vt.toString().toLowerCase().contains("partial"));
        vt = client.callProcedure("@AdHoc", sql).getResults()[0];
        System.err.println(vt);
        validateTableOfLongs(vt, new long[][] {{0,80,10}, {0,90,10},
                {1,1,10}, {1,11,10},{1,21,10} });

        // Joined with aggregation is tested in SQL Coverage tests.
    }


    //
    // Suite builder boilerplate
    //

    public TestGroupBySuite(String name) {
        super(name);
    }

    static public junit.framework.Test suite() {
        VoltServerConfig config = null;
        MultiConfigSuiteBuilder builder = new MultiConfigSuiteBuilder(
                TestGroupBySuite.class);
        VoltProjectBuilder project = new VoltProjectBuilder();

        project.addSchema(TestPlansGroupBy.class
                .getResource("testplans-groupby-ddl.sql"));
        project.addProcedures(PROCEDURES);
        project.addStmtProcedure("T1Insert", "INSERT INTO T1 VALUES (?, ?);");
        project.addStmtProcedure("BInsert", "INSERT INTO B VALUES (?, ?);");

        // config = new LocalSingleProcessServer("plansgroupby-ipc.jar", 1, BackendTarget.NATIVE_EE_IPC);
        // config.compile(project);
        // builder.addServerConfig(config);

        config = new LocalCluster("plansgroupby-onesite.jar", 1, 1, 0, BackendTarget.NATIVE_EE_JNI);
        boolean success = config.compile(project);
        assertTrue(success);
        builder.addServerConfig(config);

        config = new LocalCluster("plansgroupby-hsql.jar", 1, 1, 0, BackendTarget.HSQLDB_BACKEND);
        success = config.compile(project);
        assertTrue(success);
        builder.addServerConfig(config);

        // Cluster
        config = new LocalCluster("plansgroupby-cluster.jar", 2, 3, 1, BackendTarget.NATIVE_EE_JNI);
        success = config.compile(project);
        assertTrue(success);

        return builder;
    }

    public class VRowComparator<T> implements Comparator<VoltTableRow>
    {
        @Override
        public int compare(VoltTableRow r1, VoltTableRow r2) {
            String r1d1 = (String) r1.get(0, VoltType.STRING);
            String r1d2 = (String) r1.get(1, VoltType.STRING);
            String r2d1 = (String) r2.get(0, VoltType.STRING);
            String r2d2 = (String) r2.get(1, VoltType.STRING);

            int r1d1_pos = Integer.valueOf(r1d1.substring(3));
            int r1d2_pos = Integer.valueOf(r1d2.substring(3));
            int r2d1_pos = Integer.valueOf(r2d1.substring(3));
            int r2d2_pos = Integer.valueOf(r2d2.substring(3));

            System.out.printf("comparing (%s, %s) to (%s, %s)\n",
                    r1d1, r1d2, r2d1, r2d2);

            if (r1d1_pos != r2d1_pos)
                return r1d1_pos - r2d1_pos;

            if (r1d2_pos != r2d2_pos)
                return r1d2_pos - r2d2_pos;

            return 0;
        }
    }

    private void debug(ArrayList<VoltTableRow> sorted) {
        for (VoltTableRow row : sorted) {
            String d1 = (String) row.get(0, VoltType.STRING);
            String d2 = (String) row.get(1, VoltType.STRING);
            System.out.println("Row: " + d1 + ", " + d2);
        }
    }
}
TOP

Related Classes of org.voltdb.regressionsuites.TestGroupBySuite

TOP
Copyright © 2018 www.massapi.com. All rights reserved.
All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.