/* This file is part of VoltDB.
* Copyright (C) 2008-2017 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.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.voltdb.BackendTarget;
import org.voltdb.VoltTable;
import org.voltdb.client.Client;
import org.voltdb.client.ClientResponse;
import org.voltdb.client.NullCallback;
import org.voltdb.client.ProcCallException;
import org.voltdb.compiler.VoltProjectBuilder;
/*
* Functional tests of the statements compiled in the test suite
* org.voltdb.planner.TestComplexGroupBySuite.
*/
public class TestGroupByComplexSuite extends RegressionSuite {
private final static String [] tbs = {"R1","P1","P2","P3"};
private void loadData(boolean extra) throws IOException, ProcCallException {
Client client = this.getClient();
ClientResponse cr = null;
// Empty data from table.
for (String tb: tbs) {
cr = client.callProcedure("@AdHoc", "delete from " + tb);
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
}
// Insert records into the table.
// id, wage, dept, rate
for (String tb: tbs) {
String proc = tb + ".insert";
client.callProcedure(proc, 1, 10, 1 , "2013-06-18 02:00:00.123457");
client.callProcedure(proc, 2, 20, 1 , "2013-07-18 02:00:00.123457");
client.callProcedure(proc, 3, 30, 1 , "2013-07-18 10:40:01.123457");
client.callProcedure(proc, 4, 40, 2 , "2013-08-18 02:00:00.123457");
client.callProcedure(proc, 5, 50, 2 , "2013-09-18 02:00:00.123457");
if (extra) {
client.callProcedure(proc, 6, 10, 2 , "2013-07-18 02:00:00.123457");
client.callProcedure(proc, 7, 40, 2 , "2013-09-18 02:00:00.123457");
}
}
}
private void strangeCasesAndOrderby() throws IOException, ProcCallException {
loadData(false);
Client client = this.getClient();
ClientResponse cr;
VoltTable vt;
long[][] expected;
for (String tb: tbs) {
// Test group by PRIMARY KEY
// Test pass-through columns, group by primary key
cr = client.callProcedure("@AdHoc", "SELECT dept, count(wage) from " + tb +
" GROUP BY id ORDER BY dept DESC");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] {{2,1}, {2,1}, {1,1}, {1,1}, {1,1} };
validateTableOfLongs(vt, expected);
// Test duplicates, operator expression, group by primary key
cr = client.callProcedure("@AdHoc", "SELECT id, id, dept, dept+5 from " + tb +
" GROUP BY id ORDER BY id");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] {{1,1,1,6}, {2,2,1,6}, {3,3,1,6}, {4,4,2,7}, {5,5,2,7} };
validateTableOfLongs(vt, expected);
// Test function expression with group by primary key
cr = client.callProcedure("@AdHoc", "SELECT id, id + 1, sum(wage)/2, abs(dept-3) from " + tb +
" GROUP BY id ORDER BY id");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] {{1,2,5,2}, {2,3,10,2}, {3,4,15,2}, {4,5,20,1}, {5,6,25,1} };
validateTableOfLongs(vt, expected);
// Test order by alias from display list
cr = client.callProcedure("@AdHoc", "SELECT dept, COUNT(*) as tag, sum(wage) - 1 from " + tb +
" GROUP BY dept ORDER BY tag DESC");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {1, 3, 59} , {2, 2, 89}};
validateTableOfLongs(vt, expected);
}
}
public void testComplexAggsSuite() throws IOException, ProcCallException {
System.out.println("Test complex aggs...");
complexAggs();
complexAggsOrderbySuite();
complexAggsDistinctLimit();
}
private void complexAggs() throws IOException, ProcCallException {
loadData(false);
Client client = getClient();
String query;
ClientResponse cr;
VoltTable vt;
long[][] expected;
for (String tb: tbs) {
// Test normal group by with expressions, addition, division for avg.
query = "SELECT dept, sum(wage), count(wage)+5, " +
"sum(wage)/count(wage) " +
"FROM " + tb + " GROUP BY dept ORDER BY dept DESC;";
cr = client.callProcedure("@AdHoc", query);
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] {{2, 90, 7, 45}, {1, 60, 8, 20} };
validateTableOfLongs(vt, expected);
// Test different group by column order, non-grouped TVE, sum for column, division
query = "SELECT sum(wage)/count(wage) + 1, dept, " +
"SUM(wage+1), SUM(wage)/2 " +
"FROM " + tb + " GROUP BY dept ORDER BY dept";
cr = client.callProcedure("@AdHoc", query);
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] {{21 ,1, 63, 30}, {46, 2, 92, 45}};
validateTableOfLongs(vt, expected);
// Test Complex Agg with functions
query = "SELECT dept, SUM(ABS(wage) - 1) as tag, " +
"(count(*)+sum(dept*2))/2 " +
"FROM " + tb + " GROUP BY dept ORDER BY ABS(dept)";
cr = client.callProcedure("@AdHoc", query);
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] {{1, 57, 4}, {2, 88, 5}};
validateTableOfLongs(vt, expected);
// Test sum()/count(), Addition
query = "SELECT dept, SUM(wage), COUNT(wage), AVG(wage), " +
"MAX(wage), MIN(wage), SUM(wage)/COUNT(wage), " +
"MAX(wage)+MIN(wage)+1 " +
"FROM " + tb + " GROUP BY dept ORDER BY dept";
cr = client.callProcedure("@AdHoc", query);
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] {
{1, 60, 3, 20, 30, 10, 20, 41},
{2, 90, 2, 45, 50, 40, 45, 91}};
validateTableOfLongs(vt, expected);
}
}
private void complexAggsOrderbySuite() throws IOException, ProcCallException {
loadData(false);
Client client = this.getClient();
ClientResponse cr;
VoltTable vt;
long[][] expected;
for (String tb: tbs) {
// (0) Test no group by cases
cr = client.callProcedure("@AdHoc", "SELECT id+dept from " + tb + " ORDER BY ABS(id+dept)");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {2}, {3}, {4}, {6}, {7} };
validateTableOfLongs(vt, expected);
cr = client.callProcedure("@AdHoc", "SELECT id+dept from " + tb + " ORDER BY id+dept");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {2}, {3}, {4}, {6}, {7} };
validateTableOfLongs(vt, expected);
cr = client.callProcedure("@AdHoc", "SELECT id+dept from " + tb + " ORDER BY id+dept, wage");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {2}, {3}, {4}, {6}, {7} };
validateTableOfLongs(vt, expected);
cr = client.callProcedure("@AdHoc", "SELECT id+dept, wage from " + tb + " ORDER BY id+dept, wage");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {2, 10}, {3,20}, {4,30}, {6,40}, {7,50} };
validateTableOfLongs(vt, expected);
// (1) Test Order by COUNT(*) without complex expression
// Test order by agg with tag
cr = client.callProcedure("@AdHoc", "SELECT dept, COUNT(*) as tag, sum(wage) from " + tb +
" GROUP BY dept ORDER BY tag DESC");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {1, 3, 60} , {2, 2, 90}};
validateTableOfLongs(vt, expected);
// (2) Test Order by COUNT(*) with complex expression
// Test order by agg with tag
cr = client.callProcedure("@AdHoc", "SELECT dept, COUNT(*) as tag, sum(wage) - 1 from " + tb +
" GROUP BY dept ORDER BY tag DESC");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {1, 3, 59} , {2, 2, 89}};
validateTableOfLongs(vt, expected);
// (3) Test Order by with FUNCTION expression, no group by column in display columns
// Test Order by with unambiguous alias.
cr = client.callProcedure("@AdHoc", "SELECT ABS(dept) as tag1, SUM(ABS(wage) - 1) as tag2, " +
"(count(*)+sum(dept*2))/2 from " + tb + " GROUP BY dept ORDER BY tag1");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {1, 57, 4} , {2, 88, 5}};
validateTableOfLongs(vt, expected);
// Test Order by without any alias.
cr = client.callProcedure("@AdHoc", "SELECT ABS(dept), SUM(ABS(wage) - 1) as tag, " +
"(count(*)+sum(dept*2))/2 from " + tb + " GROUP BY dept ORDER BY ABS(dept)");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {1, 57, 4} , {2, 88, 5}};
validateTableOfLongs(vt, expected);
}
}
private void complexAggsDistinctLimit() throws IOException, ProcCallException {
loadData(true);
Client client = this.getClient();
ClientResponse cr = null;
VoltTable vt;
long[][] expected;
for (String tb: tbs) {
// Test distinct with complex aggregations.
cr = client.callProcedure("@AdHoc", "SELECT dept, count(wage), sum(distinct wage), sum(wage), " +
"count(distinct wage)+5, sum(wage)/(count(wage)+1) from " + tb + " GROUP BY dept ORDER BY dept DESC;");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] {{2, 4, 100, 140, 8, 28}, {1, 3, 60, 60, 8, 15} };
validateTableOfLongs(vt, expected);
// Test limit with complex aggregation.
cr = client.callProcedure("@AdHoc", "SELECT wage, sum(id)+1, sum(id+1), sum(dept+3)/count(dept) from " + tb +
" GROUP BY wage ORDER BY wage ASC LIMIT 4 ;");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] {{10, 8, 9, 4}, {20, 3, 3, 4}, {30, 4, 4, 4}, {40, 12, 13, 5}};
validateTableOfLongs(vt, expected);
// Test distinct limit together with complex aggregation.
cr = client.callProcedure("@AdHoc", "SELECT wage, sum(id)+1, sum(id+1), sum(dept+3)/count(distinct dept) from " + tb +
" GROUP BY wage ORDER BY wage ASC LIMIT 4 ;");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] {{10, 8, 9, 4}, {20, 3, 3, 4}, {30, 4, 4, 4}, {40, 12, 13, 10}};
validateTableOfLongs(vt, expected);
}
}
public void testComplexGroupbySuite() throws IOException, ProcCallException, ParseException{
System.out.println("Test complex group by...");
complexGroupby();
complexGroupbyDistinctLimit();
complexGroupbyOrderbySuite();
orderbyColumnsNotInDisplayList();
}
private void complexGroupby() throws IOException, ProcCallException, ParseException{
loadData(true);
Client client = this.getClient();
ClientResponse cr = null;
VoltTable vt;
long[][] expected;
for (String tb: tbs) {
// (1) Without extra aggregation expression
// Test complex group-by (Function expression) without complex aggregation.
cr = client.callProcedure("@AdHoc", "SELECT abs(dept) as tag, count(wage) from " + tb +
" GROUP BY abs(dept) ORDER BY tag ");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {1, 3}, {2, 4} };
validateTableOfLongs(vt, expected);
// repeat above test with GROUP BY ALIAS feature
cr = client.callProcedure("@AdHoc", "SELECT abs(dept) as tag, count(wage) from " + tb +
" GROUP BY tag ORDER BY tag ");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {1, 3}, {2, 4} };
validateTableOfLongs(vt, expected);
// Test complex group-by (normal expression) without complex aggregation.
// Actually this AdHoc query has an extra projection node because of the pass-by column dept in order by columns.
// ParameterValueExpression equal function return false. AggResultColumns contains: dept+1, count(wage) and dept.
// If it is a stored procedure, there is no extra projection node. AggResultColumns: dept+1 and count(wage).
cr = client.callProcedure("@AdHoc", "SELECT (dept+1) as tag, count(wage) from " + tb +
" GROUP BY dept+1 ORDER BY tag ");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {2, 3}, {3, 4} };
validateTableOfLongs(vt, expected);
// repeat above test with GROUP BY ALIAS feature
cr = client.callProcedure("@AdHoc", "SELECT (dept+1) as tag, count(wage) from " + tb +
" GROUP BY tag ORDER BY tag ");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {2, 3}, {3, 4} };
validateTableOfLongs(vt, expected);
// test group by alias with constants in expression for stored procedure
cr = client.callProcedure(tb +"_GroupbyAlias1", 1);
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {2, 3}, {3, 4} };
validateTableOfLongs(vt, expected);
// (2) With extra aggregation expression
// Test complex group-by with with complex aggregation.
cr = client.callProcedure("@AdHoc", "SELECT abs(dept) as tag, count(wage)+1 from " + tb +
" GROUP BY abs(dept) ORDER BY tag DESC;");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {2, 5}, {1, 4} };
validateTableOfLongs(vt, expected);
// repeat above test with GROUP BY ALIAS feature
cr = client.callProcedure("@AdHoc", "SELECT abs(dept) as tag, count(wage)+1 from " + tb +
" GROUP BY tag ORDER BY tag DESC;");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {2, 5}, {1, 4} };
validateTableOfLongs(vt, expected);
// Test more complex group-by with with complex aggregation.
cr = client.callProcedure("@AdHoc", "SELECT abs(dept-2) as tag, count(wage)+1 from " + tb +
" GROUP BY abs(dept-2) ORDER BY tag;");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {0, 5}, {1, 4} };
validateTableOfLongs(vt, expected);
// repeat above test with GROUP BY ALIAS feature
cr = client.callProcedure("@AdHoc", "SELECT abs(dept-2) as tag, count(wage)+1 from " + tb +
" GROUP BY tag ORDER BY tag;");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {0, 5}, {1, 4} };
validateTableOfLongs(vt, expected);
// test group by alias with constants in expression for stored procedure
cr = client.callProcedure(tb +"_GroupbyAlias2", -2);
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {0, 5}, {1, 4} };
validateTableOfLongs(vt, expected);
// More hard general test case with multi group by columns and complex aggs
cr = client.callProcedure("@AdHoc", "SELECT abs(dept-2) as tag, wage, wage/2, count(*)*2, " +
"sum(id)/count(id)+1 from " + tb + " GROUP BY abs(dept-2), wage ORDER BY tag, wage;");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {0,10,5,2,7}, {0,40,20,4,6}, {0,50,25,2,6}, {1,10,5,2,2}, {1,20,10,2,3}, {1,30,15,2,4} };
validateTableOfLongs(vt, expected);
// repeat above test with GROUP BY ALIAS feature
cr = client.callProcedure("@AdHoc", "SELECT abs(dept-2) as tag, wage, wage/2, count(*)*2, " +
"sum(id)/count(id)+1 from " + tb + " GROUP BY tag, wage ORDER BY tag, wage;");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {0,10,5,2,7}, {0,40,20,4,6}, {0,50,25,2,6}, {1,10,5,2,2}, {1,20,10,2,3}, {1,30,15,2,4} };
validateTableOfLongs(vt, expected);
if (!isHSQL()) {
// Timestamp function for complex group by
cr = client.callProcedure("@AdHoc", "SELECT truncate(day, tm) as tag, count(id)+1, " +
"sum(wage)/count(wage) from " + tb + " GROUP BY truncate(day, tm) ORDER BY tag;");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
Date time1 = dateFormat.parse("2013-06-18 00:00:00.000");
Date time2 = dateFormat.parse("2013-07-18 00:00:00.000");
Date time3 = dateFormat.parse("2013-08-18 00:00:00.000");
Date time4 = dateFormat.parse("2013-09-18 00:00:00.000");
expected = new long[][] { {time1.getTime()*1000, 2, 10}, {time2.getTime()*1000, 4, 20},
{time3.getTime()*1000, 2, 40},{time4.getTime()*1000, 3, 45},};
validateTableOfLongs(vt, expected);
}
}
}
private void complexGroupbyDistinctLimit() throws IOException, ProcCallException, ParseException {
loadData(true);
Client client = this.getClient();
ClientResponse cr = null;
VoltTable vt;
long[][] expected;
for (String tb: tbs) {
// (1) Without extra aggregation expression
// Test complex group-by (Function expression) without complex aggregation. (Depulicates: two 2s for dept)
// Test distinct
cr = client.callProcedure("@AdHoc", "SELECT abs(wage) as tag, count(distinct dept) from " + tb +
" GROUP BY abs(wage) ORDER BY tag ");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {10, 2}, {20, 1}, {30, 1}, {40, 1}, {50, 1} };
validateTableOfLongs(vt, expected);
// Test limit
cr = client.callProcedure("@AdHoc", "SELECT abs(wage) as tag, count(dept) from " + tb +
" GROUP BY abs(wage) ORDER BY tag limit 4");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {10, 2}, {20, 1}, {30, 1}, {40, 2} };
validateTableOfLongs(vt, expected);
// Test distinct and limit
cr = client.callProcedure("@AdHoc", "SELECT abs(wage) as tag, count(distinct dept) from " + tb +
" GROUP BY abs(wage) ORDER BY tag limit 4");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {10, 2}, {20, 1}, {30, 1}, {40, 1} };
validateTableOfLongs(vt, expected);
// (2) With extra aggregation expression
// Test complex group-by with with complex aggregation. (Depulicates: two 2s for dept)
// Test distinct
cr = client.callProcedure("@AdHoc", "SELECT abs(wage) as tag, count(distinct dept)+1 from " + tb +
" GROUP BY abs(wage) ORDER BY tag ");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {10, 3}, {20, 2}, {30, 2}, {40, 2}, {50, 2} };
validateTableOfLongs(vt, expected);
// Test limit
cr = client.callProcedure("@AdHoc", "SELECT abs(wage) as tag, count(dept)+1 from " + tb +
" GROUP BY abs(wage) ORDER BY tag limit 4");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {10, 3}, {20, 2}, {30, 2}, {40, 3}};
validateTableOfLongs(vt, expected);
// Test distinct and limit
cr = client.callProcedure("@AdHoc", "SELECT abs(wage) as tag, count(distinct dept)+1 from " + tb +
" GROUP BY abs(wage) ORDER BY tag limit 4 ");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {10, 3}, {20, 2}, {30, 2}, {40, 2} };
validateTableOfLongs(vt, expected);
// (3) More hard general test case with multi group by columns and complex aggs (Depulicates: two 40s for wage)
// Test distinct
cr = client.callProcedure("@AdHoc", "SELECT abs(dept-2) as tag, wage, wage/2, count(distinct wage)*2, " +
"sum(id)/count(id)+1 from " + tb + " GROUP BY abs(dept-2), wage ORDER BY tag, wage;");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {0,10,5,2,7}, {0,40,20,2,6}, {0,50,25,2,6}, {1,10,5,2,2}, {1,20,10,2,3}, {1,30,15,2,4} };
validateTableOfLongs(vt, expected);
// Test Limit
cr = client.callProcedure("@AdHoc", "SELECT abs(dept-2) as tag, wage, wage/2, count(wage)*2, " +
"sum(id)/count(id)+1 from " + tb + " GROUP BY abs(dept-2), wage ORDER BY tag, wage LIMIT 5;");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {0,10,5,2,7}, {0,40,20,4,6}, {0,50,25,2,6}, {1,10,5,2,2}, {1,20,10,2,3} };
validateTableOfLongs(vt, expected);
// Test distinct and limit
cr = client.callProcedure("@AdHoc", "SELECT abs(dept-2) as tag, wage, wage/2, count(distinct wage)*2, " +
"sum(id)/count(id)+1 from " + tb + " GROUP BY abs(dept-2), wage ORDER BY tag, wage LIMIT 5;");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {0,10,5,2,7}, {0,40,20,2,6}, {0,50,25,2,6}, {1,10,5,2,2}, {1,20,10,2,3} };
validateTableOfLongs(vt, expected);
}
}
private void complexGroupbyOrderbySuite() throws IOException, ProcCallException {
loadData(true);
Client client = this.getClient();
ClientResponse cr = null;
VoltTable vt;
long[][] expected;
for (String tb: tbs) {
//(1) Test complex group-by with no extra aggregation expressions.
// Test order by with tag
cr = client.callProcedure("@AdHoc", "SELECT abs(dept) as tag, count(wage), sum(id), avg(wage) from " + tb +
" GROUP BY abs(dept) ORDER BY tag ");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {1, 3, 6, 20}, {2, 4, 22, 35} };
validateTableOfLongs(vt, expected);
// Test order by without tag
cr = client.callProcedure("@AdHoc", "SELECT abs(dept) as tag, count(wage), sum(id), avg(wage) from " + tb +
" GROUP BY abs(dept) ORDER BY abs(dept) ");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {1, 3, 6, 20}, {2, 4, 22, 35} };
validateTableOfLongs(vt, expected);
//(2) Test complex group-by with complex aggregation.
// Test order by with tag
cr = client.callProcedure("@AdHoc", "SELECT abs(dept-2) as tag, count(wage)+1, avg(wage)/2 from " + tb +
" GROUP BY abs(dept-2) ORDER BY tag;");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {0, 5, 17}, {1, 4, 10} };
validateTableOfLongs(vt, expected);
// Test order by without tag
cr = client.callProcedure("@AdHoc", "SELECT abs(dept-2) as tag, count(wage)+1, avg(wage)/2 from " + tb +
" GROUP BY abs(dept-2) ORDER BY abs(dept-2);");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {0, 5, 17}, {1, 4, 10} };
validateTableOfLongs(vt, expected);
//(3) More hard general test cases with multi group by columns and complex aggs
// Test order by with tag
cr = client.callProcedure("@AdHoc", "SELECT abs(dept-2) as tag, wage, wage/2, count(*)*2, " +
"sum(id)/count(id)+1 from " + tb + " GROUP BY abs(dept-2), wage ORDER BY tag, wage;");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {0,10,5,2,7}, {0,40,20,4,6}, {0,50,25,2,6}, {1,10,5,2,2}, {1,20,10,2,3}, {1,30,15,2,4} };
validateTableOfLongs(vt, expected);
// Test order by without tag
cr = client.callProcedure("@AdHoc", "SELECT abs(dept-2) as tag, wage, wage/2, count(*)*2, " +
"sum(id)/count(id)+1 from " + tb + " GROUP BY abs(dept-2), wage ORDER BY abs(dept-2), wage;");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {0,10,5,2,7}, {0,40,20,4,6}, {0,50,25,2,6}, {1,10,5,2,2}, {1,20,10,2,3}, {1,30,15,2,4} };
validateTableOfLongs(vt, expected);
// Test order by without tag and not in display columns
cr = client.callProcedure("@AdHoc", "SELECT wage, wage/2, count(*)*2, sum(id)/count(id)+1 from " + tb +
" GROUP BY abs(dept-2), wage ORDER BY abs(dept-2), wage;");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {10,5,2,7}, {40,20,4,6}, {50,25,2,6}, {10,5,2,2}, {20,10,2,3}, {30,15,2,4} };
validateTableOfLongs(vt, expected);
//(4) Other order by expressions (id+dept), expressions on that.
cr = client.callProcedure("@AdHoc", "SELECT id+dept, sum(wage)+1 from " + tb +
" GROUP BY id+dept ORDER BY ABS(id+dept)");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {2, 11}, {3,21}, {4,31}, {6,41}, {7,51}, {8,11}, {9,41} };
validateTableOfLongs(vt, expected);
cr = client.callProcedure("@AdHoc", "SELECT id+dept, avg(wage) from " + tb +
" GROUP BY id+dept ORDER BY ABS(id+dept)");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {2, 10}, {3,20}, {4,30}, {6,40}, {7,50}, {8,10}, {9,40} };
validateTableOfLongs(vt, expected);
cr = client.callProcedure("@AdHoc", "SELECT id+dept from " + tb +
" GROUP BY id+dept ORDER BY ABS(id+dept)");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {2}, {3}, {4}, {6}, {7}, {8}, {9} };
validateTableOfLongs(vt, expected);
cr = client.callProcedure("@AdHoc", "SELECT id+dept, wage from " + tb +
" GROUP BY id+dept, wage ORDER BY ABS(id+dept), abs(wage)");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {2, 10}, {3,20}, {4,30}, {6,40}, {7,50}, {8,10}, {9,40} };
validateTableOfLongs(vt, expected);
// Expressions on the columns from selected list
cr = client.callProcedure("@AdHoc", "SELECT id+dept, avg(wage) as tag from " + tb +
" GROUP BY id+dept ORDER BY ABS(tag), id+dept");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {2, 10}, {8,10}, {3,20}, {4,30}, {6,40}, {9,40}, {7,50}};
validateTableOfLongs(vt, expected);
cr = client.callProcedure("@AdHoc", "SELECT id+dept, avg(wage) as tag from " + tb +
" GROUP BY id+dept ORDER BY ABS(avg(wage)), id+dept");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {2, 10}, {8,10}, {3,20}, {4,30}, {6,40}, {9,40}, {7,50}};
validateTableOfLongs(vt, expected);
cr = client.callProcedure("@AdHoc", "SELECT id+dept, avg(wage) as tag from " + tb +
" GROUP BY id+dept ORDER BY ABS(avg(wage)) + 1, id+dept");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {2, 10}, {8,10}, {3,20}, {4,30}, {6,40}, {9,40}, {7,50}};
validateTableOfLongs(vt, expected);
cr = client.callProcedure("@AdHoc", "SELECT id+dept, avg(wage)+1 as tag from " + tb +
" GROUP BY id+dept ORDER BY ABS(tag), id+dept");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {2, 11}, {8,11}, {3,21}, {4,31}, {6,41}, {9,41}, {7,51}};
validateTableOfLongs(vt, expected);
cr = client.callProcedure("@AdHoc", "SELECT id+dept, avg(wage)+1 as tag from " + tb +
" GROUP BY id+dept ORDER BY ABS(avg(wage)+1), id+dept");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {2, 11}, {8,11}, {3,21}, {4,31}, {6,41}, {9,41}, {7,51}};
validateTableOfLongs(vt, expected);
cr = client.callProcedure("@AdHoc", "SELECT id+dept, avg(wage)+1 as tag from " + tb +
" GROUP BY id+dept ORDER BY ABS(avg(wage)+1) + 1, id+dept");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {2, 11}, {8,11}, {3,21}, {4,31}, {6,41}, {9,41}, {7,51}};
validateTableOfLongs(vt, expected);
}
}
public void testOtherCases() throws IOException, ProcCallException {
System.out.println("Test other cases...");
strangeCasesAndOrderby();
ENG4285();
ENG5016();
supportedCases();
unsupportedCases();
ENG7046();
}
private void ENG4285() throws IOException, ProcCallException {
loadData(false);
Client client = this.getClient();
ClientResponse cr = null;
VoltTable vt;
long[][] expected;
for (String tb: tbs) {
cr = client.callProcedure("@AdHoc", "SELECT dept, sum(wage-id) from " + tb +
" GROUP BY dept ORDER BY dept");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {1, 54} , {2, 81}};
validateTableOfLongs(vt, expected);
cr = client.callProcedure("@AdHoc", "SELECT dept, sum(wage-id), avg(wage-id), " +
"count(*) from " + tb + " GROUP BY dept ORDER BY dept");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {1, 54, 18, 3} , {2, 81, 40, 2}};
validateTableOfLongs(vt, expected);
cr = client.callProcedure("@AdHoc", "SELECT dept, sum(wage-id) + 1, " +
"avg(wage-id), count(*) from " + tb + " GROUP BY dept ORDER BY dept");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {1, 55, 18, 3} , {2, 82, 40, 2}};
validateTableOfLongs(vt, expected);
cr = client.callProcedure("@AdHoc", "SELECT dept, sum(wage-extract(month from tm)), " +
"avg(wage-extract(month from tm)), count(dept) from " + tb +
" GROUP BY dept ORDER BY dept");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {1, 40, 13, 3} , {2, 73, 36, 2}};
validateTableOfLongs(vt, expected);
}
}
// Test group by columns do not have to be in display columns.
private void ENG5016() throws IOException, ProcCallException {
loadData(false);
Client client = this.getClient();
ClientResponse cr = null;
VoltTable vt;
long[][] expected;
for (String tb: tbs) {
cr = client.callProcedure("@AdHoc", "SELECT count(*), sum(wage) from " + tb +
" GROUP BY dept ORDER BY sum(wage)");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {3, 60} , {2, 90}};
validateTableOfLongs(vt, expected);
cr = client.callProcedure("@AdHoc", "SELECT count(*) as tag, sum(wage), sum(wage) from " + tb +
" GROUP BY dept ORDER BY tag");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {2, 90, 90}, {3, 60, 60} };
validateTableOfLongs(vt, expected);
// Demo bug, ENG-5149
// Check column alias for the identical aggregation
assertEquals("C2", vt.getColumnName(1));
assertEquals("C3", vt.getColumnName(2));
assertEquals(1, vt.getColumnIndex("C2"));
assertEquals(2, vt.getColumnIndex("C3"));
cr = client.callProcedure("@AdHoc", "SELECT count(*) as tag, " +
"sum(wage)+1 as NO_BUG, sum(wage)+1 from " + tb +
" GROUP BY dept ORDER BY tag");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {2, 91, 91}, {3, 61, 61} };
validateTableOfLongs(vt, expected);
assertEquals("NO_BUG", vt.getColumnName(1));
assertEquals("C3", vt.getColumnName(2));
assertEquals(1, vt.getColumnIndex("NO_BUG"));
assertEquals(2, vt.getColumnIndex("C3"));
}
}
private void supportedCases() throws IOException, ProcCallException {
loadData(false);
Client client = this.getClient();
ClientResponse cr = null;
VoltTable vt;
long[][] expected;
for (String tb: tbs) {
// Test order by agg without tag
cr = client.callProcedure("@AdHoc", "SELECT dept, count(*), sum(wage) from " + tb +
" GROUP BY dept ORDER BY sum(wage)");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {1, 3, 60} , {2, 2, 90}};
validateTableOfLongs(vt, expected);
cr = client.callProcedure("@AdHoc", "SELECT dept, COUNT(*) as tag, sum(wage) from " + tb +
" GROUP BY dept ORDER BY COUNT(*) DESC");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {1, 3, 60} , {2, 2, 90}};
validateTableOfLongs(vt, expected);
cr = client.callProcedure("@AdHoc", "SELECT dept, COUNT(*), sum(wage) from " + tb +
" GROUP BY dept ORDER BY COUNT(*) DESC");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {1, 3, 60} , {2, 2, 90}};
validateTableOfLongs(vt, expected);
cr = client.callProcedure("@AdHoc", "SELECT dept, COUNT(*) as tag, sum(wage) - 1 from " + tb +
" GROUP BY dept ORDER BY COUNT(*) DESC");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {1, 3, 59} , {2, 2, 89}};
validateTableOfLongs(vt, expected);
}
}
private void ENG7046() throws IOException, ProcCallException {
Client client = this.getClient();
VoltTable vt;
client.callProcedure("TB_STRING.insert", 1, "MA");
if (!isHSQL()) {
// Hsql does not support DECODE function
vt = client.callProcedure("@AdHoc",
"select min(decode(state, upper(state), state, "
+ "state || ' with this kind of rambling string added to it may not be inlinable')) "
+ "from tb_string").getResults()[0];
validateTableColumnOfScalarVarchar(vt, new String[] {"MA"});
}
}
// Test unsupported order by column not in display columns
private void unsupportedCases() throws IOException, ProcCallException {
loadData(false);
Client client = this.getClient();
ClientResponse cr = null;
VoltTable vt;
long[][] expected;
Exception ex = null;
for (String tb: tbs) {
ex = null;
try {
// Test order by agg not in display columns
cr = client.callProcedure("@AdHoc", "SELECT dept, avg(wage), sum(wage) from " + tb +
" GROUP BY dept ORDER BY COUNT(*) DESC");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {1, 20, 60} , {2, 45, 90}};
validateTableOfLongs(vt, expected);
} catch (ProcCallException e) {
ex = e;
} finally {
assertTrue(ex.getMessage().contains("invalid ORDER BY expression"));
}
ex = null;
try {
// Test order by agg not in display columns
cr = client.callProcedure("@AdHoc", "SELECT dept, count(*) from " + tb +
" GROUP BY dept ORDER BY sum(wage) DESC");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {1, 3} , {2, 2}};
validateTableOfLongs(vt, expected);
} catch (ProcCallException e) {
ex = e;
} finally {
assertTrue(ex.getMessage().contains("invalid ORDER BY expression"));
}
ex = null;
try {
// Test group by column not in display columns
cr = client.callProcedure("@AdHoc", "SELECT avg(wage), sum(wage) from " + tb +
" GROUP BY dept ORDER BY COUNT(*) DESC");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {20, 60} , {45, 90}};
validateTableOfLongs(vt, expected);
} catch (ProcCallException e) {
ex = e;
} finally {
assertTrue(ex.getMessage().contains("invalid ORDER BY expression"));
}
ex = null;
try {
// Test order by agg not in display columns
cr = client.callProcedure("@AdHoc", "SELECT dept, avg(wage), sum(wage) - 1 from " + tb +
" GROUP BY dept ORDER BY COUNT(*) DESC");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {1, 20, 59} , {2, 45, 89}};
validateTableOfLongs(vt, expected);
} catch (ProcCallException e) {
ex = e;
} finally {
assertTrue(ex.getMessage().contains("invalid ORDER BY expression"));
}
ex = null;
try {
// Test group by column not in display columns
cr = client.callProcedure("@AdHoc", "SELECT avg(wage), sum(wage) - 1 from " + tb +
" GROUP BY dept ORDER BY COUNT(*) DESC");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {20, 59} , {45, 89}};
validateTableOfLongs(vt, expected);
} catch (ProcCallException e) {
ex = e;
} finally {
assertTrue(ex.getMessage().contains("invalid ORDER BY expression"));
}
ex = null;
try {
// Test order by without tag and not in display columns, and not equal to group by columns
cr = client.callProcedure("@AdHoc", "SELECT count(wage), sum(id), avg(wage) from " + tb +
" GROUP BY abs(dept) ORDER BY count(*) ");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] { {3, 6, 20}, {4, 22, 35} };
validateTableOfLongs(vt, expected);
} catch (ProcCallException e) {
ex = e;
} finally {
assertTrue(ex.getMessage().contains("invalid ORDER BY expression"));
}
}
}
public void testAggregateOnJoin() throws IOException, ProcCallException {
System.out.println("Test aggs on joins...");
loadData(false);
Client client = this.getClient();
VoltTable vt;
String sql;
sql = "SELECT r1.id, count(*) " +
" from r1, p2 where r1.id = p2.dept GROUP BY r1.id ORDER BY 1;";
vt = client.callProcedure("@Explain", sql).getResults()[0];
assertTrue(vt.toString().toLowerCase().contains("inline hash"));
vt = client.callProcedure("@AdHoc", sql).getResults()[0];
validateTableOfLongs(vt, new long[][] {{1,3}, {2,2}});
}
public void testHavingClause() throws IOException, ProcCallException {
System.out.println("test Having clause...");
loadData(false);
Client client = this.getClient();
ClientResponse cr;
VoltTable vt;
long[][] expected;
for (String tb: tbs) {
// Test normal group by with expressions, addition, division for avg.
cr = client.callProcedure("@AdHoc", "SELECT dept, sum(wage), count(wage)+5, " +
"sum(wage)/count(wage) from " + tb + " GROUP BY dept ORDER BY dept DESC;");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] {{2, 90, 7, 45}, {1, 60, 8, 20} };
validateTableOfLongs(vt, expected);
// Test having
cr = client.callProcedure("@AdHoc", "SELECT dept, sum(wage) from " + tb +
" GROUP BY dept HAVING sum(wage) > 60 ORDER BY dept DESC;");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] {{2, 90}};
validateTableOfLongs(vt, expected);
cr = client.callProcedure("@AdHoc", "SELECT dept, sum(wage), count(wage)+5 from " + tb +
" GROUP BY dept HAVING count(wage)+5 <> 7 ORDER BY dept DESC;");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] {{1, 60, 8}};
validateTableOfLongs(vt, expected);
// Test having clause not in display list
cr = client.callProcedure("@AdHoc", "SELECT dept, sum(wage) from " + tb +
" GROUP BY dept HAVING count(wage)+5 <> 7 ORDER BY dept DESC;");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] {{1, 60}};
validateTableOfLongs(vt, expected);
// Test normal group by with expressions, addition, division for avg.
cr = client.callProcedure("@AdHoc", "SELECT dept, sum(wage), count(wage)+5, " +
"sum(wage)/count(wage) from " + tb + " GROUP BY dept HAVING sum(wage) < 80 ORDER BY dept DESC;");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] {{1, 60, 8, 20} };
validateTableOfLongs(vt, expected);
// Test Having with COUNT(*)
cr = client.callProcedure("@AdHoc", "SELECT count(*) from " + tb +
" HAVING count(*) > 60 " +
" ORDER BY 1 DESC;");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
assertTrue(vt.getRowCount() == 0);
// Test Having with AVG
cr = client.callProcedure("@AdHoc", "SELECT AVG(wage) from " + tb +
" HAVING SUM(id) > 20 " +
" ORDER BY 1 DESC;");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
assertTrue(vt.getRowCount() == 0);
}
}
// This test case will trigger temp table "delete as we go" feature on join node
// Turn off this test cases because of valgrind timeout.
public void turnOfftestAggregateOnJoinForMemoryIssue() throws IOException, ProcCallException {
Client client = this.getClient();
ClientResponse cr;
VoltTable vt;
long[][] expected;
// Empty data from table.
for (String tb: tbs) {
cr = client.callProcedure("@AdHoc", "delete from " + tb);
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
}
int scale = 10;
int numOfRecords = scale * 1000;
// Insert records into the table.
// id, wage, dept, rate
String timeStamp = "2013-06-18 02:00:00.123457";
String[] myProcs = {"R1.insert", "P1.insert"};
for (String insertProc: myProcs) {
for (int ii = 1; ii <= numOfRecords; ii++) {
client.callProcedure(new NullCallback(),
insertProc, ii, ii % 1000, ii % 2 , timeStamp);
}
}
try {
client.drain();
} catch (InterruptedException e) {
e.printStackTrace();
}
// Serial aggregation because of no group by
cr = client.callProcedure("@AdHoc", "SELECT sum(R1.wage) " +
" from R1, P1 WHERE R1.id = P1.id ;");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] {{499500 * scale}};
validateTableOfLongs(vt, expected);
// hash aggregation because of no index on group by key
cr = client.callProcedure("@AdHoc", "SELECT R1.dept, sum(R1.wage) " +
" from R1, P1 WHERE R1.id = P1.id Group by R1.dept order by R1.dept;");
assertEquals(ClientResponse.SUCCESS, cr.getStatus());
vt = cr.getResults()[0];
expected = new long[][] {{0, 249500 * scale}, {1, 250000 * scale}};
validateTableOfLongs(vt, expected);
}
public void testDistinctWithGroupby() throws IOException, ProcCallException {
System.out.println("Test Distinct...");
loadData(true);
Client client = this.getClient();
String sql;
VoltTable vt;
for (String tb: tbs) {
sql = "SELECT DISTINCT ID, COUNT(DEPT) FROM " + tb + " GROUP BY ID, WAGE ORDER BY ID, 2 ";
vt = client.callProcedure("@AdHoc", sql).getResults()[0];
validateTableOfLongs(vt, new long[][] {{1,1}, {2,1}, {3,1}, {4,1}, {5,1}, {6,1}, {7,1}});
// test LIMIT/OFFSET
sql = "SELECT DISTINCT ID, COUNT(DEPT) FROM " + tb + " GROUP BY ID, WAGE ORDER BY 1, 2 LIMIT 2 ";
vt = client.callProcedure("@AdHoc", sql).getResults()[0];
validateTableOfLongs(vt, new long[][] {{1,1}, {2,1}});
// query with one column distinct
sql = "SELECT DISTINCT count(*) from " + tb + " GROUP BY abs(dept-2), wage "
+ " ORDER BY 1;";
vt = client.callProcedure("@AdHoc", sql).getResults()[0];
validateTableOfScalarLongs(vt, new long[]{1, 2});
// (1) base query without distinct
sql = "SELECT wage, count(*) from " + tb + " GROUP BY abs(dept-2), wage "
+ " ORDER BY 1, 2;";
vt = client.callProcedure("@AdHoc", sql).getResults()[0];
validateTableOfLongs(vt, new long[][] {{10,1}, {10,1}, {20,1}, {30,1}, {40,2}, {50,1}});
// query with multiple columns distinct
sql = "SELECT DISTINCT wage, count(*) from " + tb + " GROUP BY abs(dept-2), wage "
+ " ORDER BY 1, 2;";
vt = client.callProcedure("@AdHoc", sql).getResults()[0];
validateTableOfLongs(vt, new long[][] {{10,1}, {20,1}, {30,1}, {40,2}, {50,1}});
// test LIMIT/OFFSET
sql = "SELECT DISTINCT wage, count(*) from " + tb + " GROUP BY abs(dept-2), wage "
+ " ORDER BY 1, 2 LIMIT 2;";
vt = client.callProcedure("@AdHoc", sql).getResults()[0];
validateTableOfLongs(vt, new long[][] {{10,1}, {20,1}});
// query with multiple expressions distinct
sql = "SELECT DISTINCT ID, COUNT(DEPT) + 1 FROM " + tb + " GROUP BY ID, WAGE order by 1, 2";
vt = client.callProcedure("@AdHoc", sql).getResults()[0];
validateTableOfLongs(vt, new long[][] {{1,2}, {2,2}, {3,2}, {4,2}, {5,2}, {6,2}, {7,2}});
sql = "SELECT DISTINCT wage, count(*)+1 from " + tb + " GROUP BY abs(dept-2), wage "
+ " ORDER BY 1, 2;";
vt = client.callProcedure("@AdHoc", sql).getResults()[0];
validateTableOfLongs(vt, new long[][] {{10,2}, {20,2}, {30,2}, {40,3}, {50,2}});
// test LIMIT/OFFSET
sql = "SELECT DISTINCT wage, count(*)+1 from " + tb + " GROUP BY abs(dept-2), wage "
+ " ORDER BY 1, 2 LIMIT 2;";
vt = client.callProcedure("@AdHoc", sql).getResults()[0];
validateTableOfLongs(vt, new long[][] {{10,2}, {20,2}});
}
}
private void orderbyColumnsNotInDisplayList() throws IOException, ProcCallException {
System.out.println("Test testOrderbyColumnsNotInDisplayList...");
loadData(true);
Client client = this.getClient();
String sql;
VoltTable vt;
for (String tb: tbs) {
// Test Order by column not in Display columns
sql = "SELECT SUM(ABS(wage) - 1) as tag, " +
"(count(*)+sum(dept*2))/2 from " + tb + " GROUP BY dept ORDER BY ABS(dept)";
vt = client.callProcedure("@AdHoc", sql).getResults()[0];
validateTableOfLongs(vt, new long[][] { {57, 4} , {136, 10}});
sql = "SELECT count(wage), sum(id) from " + tb +
" GROUP BY abs(dept) ORDER BY abs(dept) ";
vt = client.callProcedure("@AdHoc", sql).getResults()[0];
validateTableOfLongs(vt, new long[][] { {3, 6}, {4, 22} });
sql = "SELECT count(wage), sum(id), avg(wage) from " + tb +
" GROUP BY abs(dept) ORDER BY abs(dept) ";
vt = client.callProcedure("@AdHoc", sql).getResults()[0];
validateTableOfLongs(vt, new long[][] { {3, 6, 20}, {4, 22, 35} });
sql = "SELECT count(wage)+1, avg(wage)/2 from " + tb +
" GROUP BY abs(dept-2) ORDER BY abs(dept-2);";
vt = client.callProcedure("@AdHoc", sql).getResults()[0];
validateTableOfLongs(vt, new long[][] { {5, 17}, {4, 10} });
sql = "SELECT COUNT(*) as tag, sum(wage) from " + tb +
" GROUP BY dept ORDER BY abs(dept) DESC";
vt = client.callProcedure("@AdHoc", sql).getResults()[0];
validateTableOfLongs(vt, new long[][] { {4, 140}, {3, 60}});
}
}
//
// Suite builder boilerplate
//
public TestGroupByComplexSuite(String name) {
super(name);
}
static public junit.framework.Test suite() {
VoltServerConfig config = null;
MultiConfigSuiteBuilder builder = new MultiConfigSuiteBuilder(
TestGroupByComplexSuite.class);
String addProcs = "";
for (String tb: tbs) {
addProcs += "CREATE PROCEDURE " + tb + "_GroupbyAlias1 AS "
+ " SELECT (dept+?) as tag, count(wage) from " + tb
+ " GROUP BY tag ORDER BY tag;";
addProcs += "CREATE PROCEDURE " + tb + "_GroupbyAlias2 AS "
+ " SELECT abs(dept+?) as tag, count(wage)+1 from " + tb
+ " GROUP BY tag ORDER BY tag;";
}
VoltProjectBuilder project = new VoltProjectBuilder();
final String literalSchema =
"CREATE TABLE R1 ( " +
"ID INTEGER DEFAULT 0 NOT NULL, " +
"WAGE INTEGER, " +
"DEPT INTEGER, " +
"TM TIMESTAMP DEFAULT NULL, " +
"PRIMARY KEY (ID) );" +
"CREATE TABLE P1 ( " +
"ID INTEGER DEFAULT 0 NOT NULL, " +
"WAGE INTEGER NOT NULL, " +
"DEPT INTEGER NOT NULL, " +
"TM TIMESTAMP DEFAULT NULL, " +
"PRIMARY KEY (ID) );" +
"PARTITION TABLE P1 ON COLUMN ID;" +
"CREATE TABLE P2 ( " +
"ID INTEGER DEFAULT 0 NOT NULL ASSUMEUNIQUE, " +
"WAGE INTEGER NOT NULL, " +
"DEPT INTEGER NOT NULL, " +
"TM TIMESTAMP DEFAULT NULL, " +
"PRIMARY KEY (ID, DEPT) );" +
"PARTITION TABLE P2 ON COLUMN DEPT;" +
"CREATE TABLE P3 ( " +
"ID INTEGER DEFAULT 0 NOT NULL ASSUMEUNIQUE, " +
"WAGE INTEGER NOT NULL, " +
"DEPT INTEGER NOT NULL, " +
"TM TIMESTAMP DEFAULT NULL, " +
"PRIMARY KEY (ID, WAGE) );" +
"PARTITION TABLE P3 ON COLUMN WAGE;" +
"CREATE TABLE TB_STRING ( " +
"ID INTEGER DEFAULT 0 NOT NULL, " +
"STATE VARCHAR(2), " +
"PRIMARY KEY (ID) );" +
"create table t ( a INTEGER NOT NULL, b INTEGER NOT NULL, c INTEGER NOT NULL); " +
addProcs
;
try {
project.addLiteralSchema(literalSchema);
} catch (IOException e) {
assertFalse(true);
}
boolean success;
config = new LocalCluster("groupByComplex-onesite.jar", 1, 1, 0, BackendTarget.NATIVE_EE_JNI);
success = config.compile(project);
assertTrue(success);
builder.addServerConfig(config);
config = new LocalCluster("groupByComplex-hsql.jar", 1, 1, 0, BackendTarget.HSQLDB_BACKEND);
success = config.compile(project);
assertTrue(success);
builder.addServerConfig(config);
// Cluster
config = new LocalCluster("groupByComplex-cluster.jar", 2, 3, 1, BackendTarget.NATIVE_EE_JNI);
success = config.compile(project);
assertTrue(success);
builder.addServerConfig(config);
return builder;
}
}