/* This file is part of VoltDB.
* Copyright (C) 2008-2010 VoltDB L.L.C.
*
* 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.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;
import org.voltdb.BackendTarget;
import org.voltdb.VoltProcedure;
import org.voltdb.VoltTable;
import org.voltdb.VoltTableRow;
import org.voltdb.VoltType;
import org.voltdb.client.Client;
import org.voltdb.client.ProcCallException;
import org.voltdb.compiler.VoltProjectBuilder;
import org.voltdb.regressionsuites.aggregates.*;
import edu.brown.utils.MathUtil;
/**
* System tests for basic aggregate and DISTINCT functionality
*/
public class TestSqlAggregateSuite extends RegressionSuite {
private static final String PREFIX = "sqlaggregate";
/** Procedures used by this suite */
@SuppressWarnings("unchecked")
public static final Class<? extends VoltProcedure> PROCEDURES[] = (Class<? extends VoltProcedure>[])new Class<?>[] {
Insert.class
};
static final int ROWS = 10;
public void testDistinct() throws IOException, ProcCallException {
String[] tables = {"P1", "R1"};
for (String table : tables)
{
Client client = getClient();
for (int i = 0; i < ROWS; ++i)
{
client.callProcedure("Insert", table, i, "desc",
new BigDecimal(10.0), i / 2, 14.5);
}
String query = String.format("select distinct %s.NUM from %s",
table, table);
VoltTable[] results = client.callProcedure("@AdHoc", query).getResults();
// lazy check that we get 5 rows back, put off checking contents
assertEquals(5, results[0].getRowCount());
}
}
public void testMultipleAverages() throws IOException, ProcCallException {
String[] tables = {"P1", "R1"};
Random rand = this.getRandom();
for (String table : tables) {
Client client = getClient();
List<Long> val_nums = new ArrayList<Long>();
List<Double> val_ratios = new ArrayList<Double>();
for (int i = 0; i < ROWS; ++i) {
BigDecimal cash = new BigDecimal(10.0);
long num = i / 2;
double ratio = rand.nextDouble();
client.callProcedure("Insert", table, i, "desc", cash, num, ratio);
val_nums.add(num);
val_ratios.add(ratio);
} // FOR
double expected_results[] = {
MathUtil.arithmeticMean(val_nums),
MathUtil.arithmeticMean(val_ratios),
};
String query = String.format("SELECT AVG(NUM), AVG(RATIO) FROM %s", table);
VoltTable[] results = client.callProcedure("@AdHoc", query).getResults();
assertEquals(1, results.length);
assertEquals(1, results[0].getRowCount());
assertTrue(results[0].advanceRow());
for (int i = 0; i < expected_results.length; ++i) {
double val = ((Number)results[0].get(i, results[0].getColumnType(i))).doubleValue();
assertEquals(table+"."+i, expected_results[i], val, 0.00001);
} // FOR
} // FOR
}
public void testMixedAggregates() throws IOException, ProcCallException {
String[] aggs = {"count", "sum", "min", "max", "avg"};
Object[] expected_results = {ROWS,
(0 + 1 + 2 + 3 + 4) * 2,
0,
4,
2.0};
String[] tables = {"P1", "R1"};
for (String table : tables) {
Client client = getClient();
for (int i = 0; i < ROWS; ++i) {
client.callProcedure("Insert", table, i, "desc",
new BigDecimal(10.0), i / 2, 14.5);
} // FOR
String query = "SELECT ";
for (int i = 0; i < aggs.length; ++i) {
if (i > 0) query += ", ";
query += String.format("%s(%s.NUM)", aggs[i], table);
} // FOR
query += "FROM " + table;
VoltTable[] results = client.callProcedure("@AdHoc", query).getResults();
assertEquals(1, results.length);
assertEquals(1, results[0].getRowCount());
assertTrue(results[0].advanceRow());
for (int i = 0; i < aggs.length; ++i) {
// Do avg separately since the column is a float
if (aggs[i] == "avg") {
double val = ((Number)results[0].get(i, results[0].getColumnType(i))).doubleValue();
assertEquals(table+"."+aggs[i], expected_results[i], val);
} else {
int val = (int)results[0].getLong(i);
int expected = (int)(Integer)expected_results[i];
assertEquals(table+"."+aggs[i], expected, val);
}
} // FOR
} // FOR
}
public void testAggregates() throws IOException, ProcCallException {
String[] aggs = {"count", "sum", "min", "max"};
long[] expected_results = {10,
(0 + 1 + 2 + 3 + 4) * 2,
0,
4};
String[] tables = {"P1", "R1"};
for (String table : tables)
{
Client client = getClient();
for (int i = 0; i < ROWS; ++i)
{
client.callProcedure("Insert", table, i, "desc",
new BigDecimal(10.0), i / 2, 14.5);
}
for (int i = 0; i < aggs.length; ++i)
{
String query = String.format("select %s(%s.NUM) from %s",
aggs[i], table, table);
VoltTable[] results = client.callProcedure("@AdHoc", query).getResults();
assertEquals(expected_results[i], results[0].asScalarLong());
}
// Do avg separately since the column is a float and makes
// asScalarLong() unhappy
String query = String.format("select avg(%s.NUM) from %s",
table, table);
VoltTable[] results = client.callProcedure("@AdHoc", query).getResults();
results[0].advanceRow();
assertEquals(2.0,
((Number)results[0].get(0, results[0].getColumnType(0))).doubleValue());
}
}
public void testAggregatesOnEmptyTable() throws IOException, ProcCallException {
String[] aggs = {"count", "sum", "min", "max"};
String[] tables = {"P1", "R1"};
for (String table : tables)
{
Client client = getClient();
for (int i = 0; i < aggs.length; ++i)
{
String query = String.format("select %s(%s.NUM) from %s",
aggs[i], table, table);
VoltTable[] results = client.callProcedure("@AdHoc", query).getResults();
if (aggs[i].equals("count")) {
assertEquals(0, results[0].asScalarLong());
} else {
final VoltTableRow row = results[0].fetchRow(0);
row.get(0, results[0].getColumnType(0));
if (!isHSQL()) {
assertTrue(row.wasNull());
}
}
}
// Do avg separately since the column is a float and makes
// asScalarLong() unhappy
String query = String.format("select avg(%s.NUM) from %s",
table, table);
VoltTable[] results = client.callProcedure("@AdHoc", query).getResults();
results[0].advanceRow();
@SuppressWarnings("unused")
final double value = ((Number)results[0].get(0, results[0].getColumnType(0))).doubleValue();
if (!isHSQL()) {
assertTrue(results[0].wasNull());
}
}
}
// This test case includes all of the broken cases of sum, min, max, and avg
// which didn't actually do DISTINCT.
// This is only visible for sum and avg, of course
// public void testAggregatesWithDistinct()
// throws IOException, ProcCallException
// {
// String[] aggs = {"count", "sum", "min", "max"};
// long[] expected_results = {5,
// (0 + 1 + 2 + 3 + 4),
// 0,
// 4};
// String[] tables = {"P1", "R1"};
// for (String table : tables)
// {
// Client client = getClient();
// for (int i = 0; i < ROWS; ++i)
// {
// client.callProcedure("Insert", table, i, "desc",
// new BigDecimal(10.0), i / 2, 14.5);
// }
// for (int i = 0; i < aggs.length; ++i)
// {
// String query = String.format("select %s(distinct(%s.NUM)) from %s",
// aggs[i], table, table);
// VoltTable[] results = client.callProcedure("@AdHoc", query).getResults();
// assertEquals(expected_results[i], results[0].asScalarLong());
// }
// // Do avg separately since the column is a float and makes
// // asScalarLong() unhappy
// String query = String.format("select avg(distinct(%s.NUM)) from %s",
// table, table);
// VoltTable[] results = client.callProcedure("@AdHoc", query).getResults();
// results[0].advanceRow();
// assertEquals(2.0,
// ((Number)results[0].get(0, results[0].getColumnType(0))).doubleValue());
// }
// }
public void testStringMinMaxAndCount()
throws IOException, ProcCallException
{
String[] tables = {"P1", "R1"};
for (String table : tables)
{
Client client = getClient();
for (int i = 0; i < ROWS; ++i)
{
client.callProcedure("Insert", table, i, String.valueOf(i),
new BigDecimal(10.0), i / 2, 14.5);
}
for (int i = ROWS; i < ROWS + 5; ++i)
{
client.callProcedure("Insert", table, i, VoltType.NULL_STRING,
new BigDecimal(10.0), i / 2, 14.5);
}
String query = String.format("select MIN(%s.DESC) from %s",
table, table);
VoltTable[] results = client.callProcedure("@AdHoc", query).getResults();
results[0].advanceRow();
assertEquals("0", results[0].getString(0));
query = String.format("select MAX(%s.DESC) from %s",
table, table);
results = client.callProcedure("@AdHoc", query).getResults();
results[0].advanceRow();
assertEquals("9", results[0].getString(0));
query = String.format("select COUNT(%s.DESC) from %s",
table, table);
results = client.callProcedure("@AdHoc", query).getResults();
assertEquals(ROWS, results[0].asScalarLong());
}
}
// public void testAggregatesWithNulls() throws IOException, ProcCallException
// {
// int good_rows = 10;
// int null_rows = 5;
//
// String[] aggs = {"sum", "min", "max", "avg"};
// long[] expected_int_results = {(0 + 1 + 2 + 3 + 4) * 2,
// 0,
// 4,
// 2};
// double[] expected_float_results = {(0 + 0.5 + 1 + 1.5 + 2 + 2.5 + 3 +
// 3.5 + 4 + 4.5),
// 0.0,
// 4.5,
// 2.25};
// String[] tables = {"P1", "R1"};
// for (String table : tables)
// {
// Client client = getClient();
// for (int i = 0; i < good_rows; ++i)
// {
// client.callProcedure("Insert", table, i, "desc",
// new BigDecimal(i / 2.0), i / 2, i / 2.0);
// }
// for (int i = good_rows; i < good_rows + null_rows; ++i)
// {
// client.callProcedure("Insert", table, i, VoltType.NULL_STRING,
// VoltType.NULL_DECIMAL,
// VoltType.NULL_INTEGER,
// VoltType.NULL_FLOAT);
// }
// // do count separately since it's always integer return type
// String query = String.format("select count(%s.CASH) from %s",
// table, table);
// VoltTable[] results = client.callProcedure("@AdHoc", query).getResults();
// assertEquals(good_rows, results[0].asScalarLong());
// query = String.format("select count(%s.NUM) from %s",
// table, table);
// results = client.callProcedure("@AdHoc", query).getResults();
// assertEquals(good_rows, results[0].asScalarLong());
// query = String.format("select count(%s.RATIO) from %s",
// table, table);
// results = client.callProcedure("@AdHoc", query).getResults();
// assertEquals(good_rows, results[0].asScalarLong());
// for (int i = 0; i < aggs.length; ++i)
// {
// query = String.format("select %s(%s.CASH) from %s",
// aggs[i], table, table);
// results = client.callProcedure("@AdHoc", query).getResults();
// results[0].advanceRow();
// assertEquals(expected_float_results[i],
// results[0].getDecimalAsBigDecimal(0).doubleValue());
// query = String.format("select %s(%s.NUM) from %s",
// aggs[i], table, table);
// results = client.callProcedure("@AdHoc", query).getResults();
// assertEquals(expected_int_results[i], results[0].asScalarLong());
// query = String.format("select %s(%s.RATIO) from %s",
// aggs[i], table, table);
// results = client.callProcedure("@AdHoc", query).getResults();
// results[0].advanceRow();
// assertEquals(expected_float_results[i], results[0].getDouble(0));
// }
// // and finish up with count(*) for good measure
// query = String.format("select count(*) from %s", table);
// results = client.callProcedure("@AdHoc", query).getResults();
// results[0].advanceRow();
// assertEquals(good_rows + null_rows, results[0].asScalarLong());
// }
// }
//
// public void testAggregatesWithOnlyNulls() throws IOException, ProcCallException
// {
// int null_rows = 5;
//
// String[] aggs = {"sum", "min", "max", "avg"};
// String[] tables = {"P1", "R1"};
// for (String table : tables)
// {
// Client client = getClient();
// for (int i = 0; i < null_rows; ++i)
// {
// client.callProcedure("Insert", table, i, VoltType.NULL_STRING,
// VoltType.NULL_DECIMAL,
// VoltType.NULL_INTEGER,
// VoltType.NULL_FLOAT);
// }
// // do count separately since it's always integer return type
// String query = String.format("select count(%s.CASH) from %s",
// table, table);
// VoltTable[] results = client.callProcedure("@AdHoc", query).getResults();
// assertEquals(0, results[0].asScalarLong());
// query = String.format("select count(%s.NUM) from %s",
// table, table);
// results = client.callProcedure("@AdHoc", query).getResults();
// assertEquals(0, results[0].asScalarLong());
// query = String.format("select count(%s.RATIO) from %s",
// table, table);
// results = client.callProcedure("@AdHoc", query).getResults();
// assertEquals(0, results[0].asScalarLong());
// for (int i = 0; i < aggs.length; ++i)
// {
// query = String.format("select %s(%s.CASH) from %s",
// aggs[i], table, table);
// results = client.callProcedure("@AdHoc", query).getResults();
// results[0].advanceRow();
// @SuppressWarnings("unused")
// BigDecimal dec_val = results[0].getDecimalAsBigDecimal(0);
// assert(results[0].wasNull());
// query = String.format("select %s(%s.NUM) from %s",
// aggs[i], table, table);
// results = client.callProcedure("@AdHoc", query).getResults();
// results[0].advanceRow();
// @SuppressWarnings("unused")
// long long_val = results[0].getLong(0);
// assert(results[0].wasNull());
// query = String.format("select %s(%s.RATIO) from %s",
// aggs[i], table, table);
// results = client.callProcedure("@AdHoc", query).getResults();
// results[0].advanceRow();
// @SuppressWarnings("unused")
// double doub_val = results[0].getDouble(0);
// assert(results[0].wasNull());
// }
// // and finish up with count(*) for good measure
// query = String.format("select count(*) from %s", table);
// results = client.callProcedure("@AdHoc", query).getResults();
// results[0].advanceRow();
// assertEquals(null_rows, results[0].asScalarLong());
// }
// }
//
// JUnit / RegressionSuite boilerplate
//
public TestSqlAggregateSuite(String name) {
super(name);
}
static public junit.framework.Test suite() {
VoltServerConfig config = null;
MultiConfigSuiteBuilder builder =
new MultiConfigSuiteBuilder(TestSqlAggregateSuite.class);
VoltProjectBuilder project = new VoltProjectBuilder("aggregate");
project.addSchema(Insert.class.getResource("aggregate-sql-ddl.sql"));
project.addTablePartitionInfo("P1", "ID");
project.addProcedures(PROCEDURES);
boolean success;
/////////////////////////////////////////////////////////////
// CONFIG #1: 1 Local Site/Partition
/////////////////////////////////////////////////////////////
config = new LocalSingleProcessServer(PREFIX + "-1part.jar", 1, BackendTarget.NATIVE_EE_JNI);
success = config.compile(project);
assert(success);
builder.addServerConfig(config);
/////////////////////////////////////////////////////////////
// CONFIG #2: 1 Local Site with 2 Partitions running on JNI backend
/////////////////////////////////////////////////////////////
config = new LocalSingleProcessServer(PREFIX + "-2part.jar", 2, BackendTarget.NATIVE_EE_JNI);
success = config.compile(project);
assert(success);
builder.addServerConfig(config);
////////////////////////////////////////////////////////////
// CONFIG #3: cluster of 2 nodes running 2 site each, one replica
////////////////////////////////////////////////////////////
config = new LocalCluster(PREFIX + "-cluster.jar", 2, 2, 1, BackendTarget.NATIVE_EE_JNI);
success = config.compile(project);
assert(success);
builder.addServerConfig(config);
return builder;
}
}