TEST_F(TestAggregate, TestCreateAggregate) {
hawq::test::SQLUtility util;
// create a basic aggregate
util.execute("CREATE AGGREGATE newavg (sfunc = int8_avg_accum,"
" basetype = int8, stype = bytea, finalfunc = int8_avg,"
" initcond1 = '{0}');", true);
// create a full featured aggregate including schema.
util.execute("create schema testaggregateschema;");
util.execute("CREATE AGGREGATE testaggregateschema.newavg ("
"sfunc = int8_avg_accum, basetype = int8, stype = bytea,"
" finalfunc = int8_avg, initcond1 = '{0}');", true);
util.execute("drop schema testaggregateschema cascade;");
// multi argument aggregate
util.execute("create function sum3(int8,int8,int8) returns int8 as "
"'select \\$1 + \\$2 + \\$3' language sql strict immutable;",true);
util.execute("create ordered aggregate sum2(int8,int8) ( sfunc = sum3, "
"stype = int8, initcond = '0');", true);
// test basic aggregate
util.execute("drop table if exists t");
hawq::test::DataGenerator dGen(&util);
dGen.genSimpleTable("t");
util.query("select sum2(a,b) as absum from t group by a,b order by absum","4|\n29|\n113|\n");
util.query("select newavg(b) as bavg from t group by a order by bavg","3|\n15|\n62|\n");
}
TEST_F(TestParquet, TestMultipleType) {
SQLUtility util;
util.execute("drop table if exists t1");
DataGenerator dGen(&util);
dGen.genTableWithFullTypes("t1", true, "parquet");
util.query(
"select * from t1",
"2147483647|||00:00:00||ff:89:71:45:ae:01|2000-01-01 "
"07:00:00+08||32767|t|192.168.1.255/"
"32|<(1,2),3>|[(0,0),(6,6)]|-178000000 "
"years|0|-$21,474,836.48|(200,400),(100,200)||<aa>bb</"
"aa>||123456789a|2001:db8:85a3:8d3:1319:8a2e:370:7344/"
"64|||1|0|(1,2)|4277-12-31|128|\n0|((100,123),(5,10),(7,2),(4,5))|hello "
"world||04:45:05.0012+08:40|||bbccddeeff|128||2001:db8:85a3:8d3:1319:"
"8a2e:370:7344/128|<(1,2),3>|[(0,0),(6,6)]||1|$0.00|(2,3),(0,1)|hello "
"world|<aa>bb</aa>||aaaa|2001:db8:85a3:8d3:1319:8a2e:370:7344/"
"64|0||2147483647|-Infinity|(1,2)|4277-12-31|Infinity|\n||abcd|15:01:03||"
"|2000-01-01 07:00:00+08|||t|||[(0,0),(6,6)]|-178000000 "
"years|0|-$21,474,836.48|(200,400),(100,200)||<aa>bb</"
"aa>||123456789a|2001:db8:85a3:8d3:1319:8a2e:370:7344/"
"64|||1|0|(1,2)|4277-12-31|128|\n0|((100,123),(5,10),(7,2),(4,5))|hello "
"world||04:45:05.0012+08:40|||bbccddeeff|128||2001:db8:85a3:8d3:1319:"
"8a2e:370:7344/128|<(1,2),3>||||$0.00||hello "
"world|||aaaa|2001:db8:85a3:8d3:1319:8a2e:370:7344/"
"64|0||2147483647|-Infinity|(1,2)|4277-12-31|Infinity|\n0|((100,123),(5,"
"10),(7,2),(4,5))|hello "
"world||04:45:05.0012+08:40|||bbccddeeff|128||2001:db8:85a3:8d3:1319:"
"8a2e:370:7344/128|<(1,2),3>||||$0.00||hello "
"world|||||0||2147483647|-Infinity|(1,2)|4277-12-31|Infinity|\n0|((100,"
"123),(5,10),(7,2),(4,5))|hello "
"world||04:45:05.0012+08:40|||bbccddeeff|128||2001:db8:85a3:8d3:1319:"
"8a2e:370:7344/128|<(1,2),3>||||||hello world|||||0||34|||||\n");
}
TEST_F(TestAggregate, TestAggregateWithGroupingsets) {
hawq::test::SQLUtility util;
util.execute("drop table if exists t");
hawq::test::DataGenerator dGen(&util);
dGen.genAggregateTable("t");
util.query("SELECT a, b, sum(c) sum_c FROM (SELECT a, b, c FROM t F1 "
"LIMIT 3) F2 GROUP BY GROUPING SETS((a, b), (b)) ORDER BY a, b,"
" sum_c;", "1|aa|10|\n1|bb|20|\n2|cc|20|\n|aa|10|\n|bb|20|\n|cc|20|\n");
}
TEST_F(TestAggregate, TestAggregateWithNull) {
hawq::test::SQLUtility util;
util.execute("drop table if exists t");
hawq::test::DataGenerator dGen(&util);
dGen.genTableWithNull("t");
util.query(
"select SUM(CASE WHEN a = 15 THEN 1 ELSE 0 END), b ,c from t group by "
"b,c",
"1||aa|\n0||WET|\n0|51||\n");
}
TEST_F(TestCommonLib, TestDataGenerator) {
hawq::test::SQLUtility util;
hawq::test::DataGenerator dGen(&util);
dGen.genSimpleTable("simpleAO");
dGen.genSimpleTable("simpleParquet", true, "parquet");
dGen.genTableWithFullTypes("fullTypeAO");
dGen.genTableWithSeries("tSeries");
dGen.genTableWithNull("tNull");
}
TEST_F(TestParquet, TestCompression) {
SQLUtility util;
util.execute("drop table if exists t21");
util.execute("drop table if exists t22");
DataGenerator dGen(&util);
dGen.genTableWithNull("t21", true, "parquet", "gzip", 9);
dGen.genTableWithNull("t22", true, "parquet", "snappy");
util.query("select * from t21,t22",
"15||aa|15||aa|\n15||aa|||WET|\n15||aa||51||\n||WET|15||aa|\n||"
"WET|||WET|\n||WET||51||\n|51||15||aa|\n|51||||WET|\n|51|||51||"
"\n");
}
