void arithmeticExpression_1()
{
cout << "\narithmetic expression: " << endl;
string exp("substr(a)+ 100.00 * sum(tpch.part.p_type) / sum(tpch.lineitem.l_extendedprice *(1-tpch.lineitem.l_discount))");
cout << exp << endl;
ArithmeticColumn a(exp, 0);
ParseTree* pt = const_cast<ParseTree*>(a.expression());
if (pt != NULL)
{
pt->walk(walkfnString);
pt->drawTree("arithmeticExpression_1.dot");
}
cout << " --- end of test 6 ---" << endl;
}
void Q1() {
string sql = "\
select\
c_custkey,\
c_name,\
sum(l_extendedprice * (1 - l_discount)) as revenue,\
c_acctbal,\
n_name,\
c_address,\
c_phone,\
c_comment\
from\
customer,\
orders,\
lineitem,\
nation\
where\
c_custkey = o_custkey\
and l_orderkey = o_orderkey\
and o_orderdate >= date ':1'\
and o_orderdate < date ':1' + interval '3' month\
and l_returnflag = 'R'\
and c_nationkey = n_nationkey\
group by\
c_custkey,\
c_name,\
c_acctbal,\
c_phone,\
n_name,\
c_address,\
c_comment\
order by\
revenue desc;";
CalpontSelectExecutionPlan csep;
// Returned columns
CalpontSelectExecutionPlan::ReturnedColumnList returnedColumnList;
SimpleColumn *c1 = new SimpleColumn("tpch.customer.l_returnflag");
returnedColumnList.push_back(c1);
SimpleColumn *c2 = new SimpleColumn("tpch.customer.c_name");
returnedColumnList.push_back(c2);
ArithmeticColumn *c3 = new ArithmeticColumn("sum(tpch.lineitem.l_extendedprice*(1-tpch.lineitem.l_discount))");
c3->alias("revenue");
returnedColumnList.push_back(c3);
SimpleColumn *c4 = new SimpleColumn("tpch.customer.c_acctbal");
returnedColumnList.push_back(c4);
SimpleColumn *c5 = new SimpleColumn("tpch.nation.n_name");
returnedColumnList.push_back(c5);
SimpleColumn *c6 = new SimpleColumn("tpch.customer.c_address");
returnedColumnList.push_back(c6);
SimpleColumn *c7 = new SimpleColumn("tpch.customer.c_phone");
returnedColumnList.push_back(c7);
SimpleColumn *c8 = new SimpleColumn("tpch.costomer.c_comment");
returnedColumnList.push_back(c8);
csep.returnedCols(returnedColumnList);
// Filters
CalpontSelectExecutionPlan::FilterTokenList filterTokenList;
SimpleFilter *f1 = new SimpleFilter (new Operator("="),
new SimpleColumn("tpch.customer.c_custkey"),
new SimpleColumn("tpch.orders.o_custkey"));
filterTokenList.push_back(f1);
filterTokenList.push_back( new Operator ("and"));
SimpleFilter *f2 = new SimpleFilter (new Operator("="),
new SimpleColumn("tpch.lineitem.l_orderkey"),
new SimpleColumn("tpch.orders.o_orderkey"));
filterTokenList.push_back(f2);
filterTokenList.push_back( new Operator ("and"));
SimpleFilter *f3 = new SimpleFilter (new Operator(">="),
new SimpleColumn("tpch.orders.o_orderdate"),
new ArithmeticColumn("date(':1')"));
filterTokenList.push_back(f3);
filterTokenList.push_back( new Operator ("and"));
SimpleFilter *f4 = new SimpleFilter (new Operator("<"),
new SimpleColumn("tpch.orders.o_orderdate"),
new ArithmeticColumn("date(':1') + interval ('3', month)"));
filterTokenList.push_back(f4);
filterTokenList.push_back( new Operator ("and"));
SimpleFilter *f5 = new SimpleFilter (new Operator("="),
new SimpleColumn("tpch.lineitem.l_returnflag"),
new ConstantColumn("R"));
filterTokenList.push_back(f5);
filterTokenList.push_back( new Operator ("and"));
SimpleFilter *f6 = new SimpleFilter (new Operator("="),
new SimpleColumn("tpch.customer.c_nationkey"),
new SimpleColumn("tpch.nation.n_nationkey"));
filterTokenList.push_back(f6);
csep.filterTokenList(filterTokenList);
ParseTree *pt = const_cast<ParseTree*>(csep.filters());
pt->drawTree ("q10.dot");
// Group by
CalpontSelectExecutionPlan::GroupByColumnList groupByList;
groupByList.push_back(c1->clone());
groupByList.push_back(c2->clone());
groupByList.push_back(c4->clone());
groupByList.push_back(c7->clone());
groupByList.push_back(c5->clone());
groupByList.push_back(c6->clone());
groupByList.push_back(c8->clone());
csep.groupByCols (groupByList);
// Order by
CalpontSelectExecutionPlan::OrderByColumnList orderByList;
ArithmeticColumn *o1 = new ArithmeticColumn(*c3);
o1->asc(false);
orderByList.push_back(o1);
csep.orderByCols(orderByList);
cout << csep;
}
void Q1() {
string sql = "\
select\
o_year,\
sum(decode(nation, ':1', volumn, 0)) / sum(volume) as mkt_share\
from\
(\
select\
extract(year from o_orderdate) as o_year,\
l_extendedprice * (1 - l_discount) as volume,\
n2.n_name as nation\
from\
part,\
supplier,\
lineitem,\
orders,\
customer,\
nation n1,\
nation n2,\
region\
where\
p_partkey = l_partkey\
and s_suppkey = l_suppkey\
and l_orderkey = o_orderkey\
and o_custkey = c_custkey\
and c_nationkey = n1.n_nationkey\
and n1.n_regionkey = r_regionkey\
and r_name = ':2'\
and s_nationkey = n2.n_nationkey\
and o_orderdate between date '1995-01-01' and date '1996-12-31'\
and p_type = ':3'\
) as all_nations\
group by\
o_year\
order by\
o_year;";
erydbSelectExecutionPlan csep;
// Returned columns
erydbSelectExecutionPlan::ReturnedColumnList returnedColumnList;
// these columns are from the temp table of FROM clause.
// I hereby give schema name "erydb", table name "FROMTABLE",
SimpleColumn *c1 = new SimpleColumn("erydb.FROMTABLE.o_year");
returnedColumnList.push_back(c1);
ArithmeticColumn *c2 = new ArithmeticColumn("sum(decode(erydb.FROMTABLE.nation, ':1', erydb.FROMTABLE.volumn, 0))/sum(tpch.FROMTABLE.volumn)");
returnedColumnList.push_back(c2);
csep.returnedCols(returnedColumnList);
// subselect in FROM clause
erydbSelectExecutionPlan *subsep =
new erydbSelectExecutionPlan(erydbSelectExecutionPlan::FROM);
// subselect returned columns
erydbSelectExecutionPlan::ReturnedColumnList subReturnedColList;
ArithmeticColumn *sc1 = new ArithmeticColumn("extract(year from tpch.orders.o_orderdate)");
sc1->alias ("o_year");
subReturnedColList.push_back(sc1);
ArithmeticColumn *sc2 = new ArithmeticColumn("tpch.lineitem.l_extendeprice * (1-tpch.lineitem.l_discount)");
sc2->alias("volume");
subReturnedColList.push_back(sc2);
SimpleColumn *sc3 = new SimpleColumn("tpch.nation.n_name");
sc3->tableAlias("n2");
subReturnedColList.push_back(sc3);
subsep->returnedCols(subReturnedColList);
// subselect filters
erydbSelectExecutionPlan::FilterTokenList subFilterTokenList;
SimpleFilter *sf1 = new SimpleFilter (new Operator("="),
new SimpleColumn("tpch.part.p_partkey"),
new SimpleColumn("tpch.lineitem.l_partkey"));
subFilterTokenList.push_back(sf1);
subFilterTokenList.push_back(new Operator("and"));
SimpleFilter *sf2 = new SimpleFilter (new Operator("="),
new SimpleColumn("tpch.supplier.s_suppkey"),
new SimpleColumn("tpch.lineitem.l_suppkey"));
subFilterTokenList.push_back(sf2);
subFilterTokenList.push_back(new Operator("and"));
SimpleFilter *sf3 = new SimpleFilter (new Operator("="),
new SimpleColumn("tpch.lineitem.l_ordertkey"),
new SimpleColumn("tpch.orders.o_orderkey"));
subFilterTokenList.push_back(sf3);
subFilterTokenList.push_back(new Operator("and"));
SimpleFilter *sf4 = new SimpleFilter (new Operator("="),
new SimpleColumn("tpch.orders.o_custkey"),
new SimpleColumn("tpch.customer.c_custkey"));
subFilterTokenList.push_back(sf4);
subFilterTokenList.push_back(new Operator("and"));
SimpleColumn *n1_nationkey = new SimpleColumn("tpch.nation.n_nationkey");
n1_nationkey->tableAlias("n1");
SimpleFilter *sf5 = new SimpleFilter (new Operator("="),
new SimpleColumn("tpch.customer.c_nationkey"),
n1_nationkey);
subFilterTokenList.push_back(sf5);
subFilterTokenList.push_back(new Operator("and"));
SimpleColumn *n1_regionkey = new SimpleColumn("tpch.nation.n_regionkey");
n1_regionkey->tableAlias ("n1");
SimpleFilter *sf6 = new SimpleFilter ( new Operator("="),
n1_regionkey,
new SimpleColumn("tpch.region.r_regionkey"));
subFilterTokenList.push_back(sf6);
subFilterTokenList.push_back(new Operator("and"));
SimpleFilter *sf7 = new SimpleFilter ( new Operator("="),
new SimpleColumn("tpch.region.r_name"),
new ConstantColumn (":2"));
subFilterTokenList.push_back(sf7);
subFilterTokenList.push_back(new Operator("and"));
SimpleColumn *n2_nationkey = new SimpleColumn("tpch.nation.n_nationkey");
n2_nationkey->tableAlias("n2");
SimpleFilter *sf9 = new SimpleFilter ( new Operator("="),
new SimpleColumn("tpch.supplier.s_nationkey"),
n2_nationkey);
subFilterTokenList.push_back(sf9);
subFilterTokenList.push_back(new Operator("and"));
SimpleFilter *sf10 = new SimpleFilter (new Operator (">="),
new SimpleColumn ("tpch.orders.o_orderdate"),
new ConstantColumn ("1995-01-01"));
subFilterTokenList.push_back(sf10);
subFilterTokenList.push_back(new Operator("and"));
SimpleFilter *sf11 = new SimpleFilter (new Operator ("<="),
new SimpleColumn ("tpch.orders.o_orderdate"),
new ConstantColumn ("1995-01-06"));
subFilterTokenList.push_back(sf11);
subFilterTokenList.push_back(new Operator("and"));
SimpleFilter *sf12 = new SimpleFilter (new Operator ("="),
new SimpleColumn ("tpch.part.p_type"),
new ConstantColumn ("3"));
subFilterTokenList.push_back(sf12);
subsep->filterTokenList(subFilterTokenList);
subsep->tableAlias("all_nations");
// end of subselect in FROM. push FROM subselect to selectList
// NOTE: only FROM subselect needs to be pushed into selectList.
// Subselects in WHERE or HAVING clause are in where or having
// filter parse tree. It may make more sense to change the member
// fSelectList of CSEP class to fFromSubSelect (type CSEP*)
erydbSelectExecutionPlan::SelectList fromSubSelectList;
fromSubSelectList.push_back(subsep);
csep.subSelects(fromSubSelectList);
ParseTree* pt = const_cast<ParseTree*>(subsep->filters());
pt->drawTree("q8.dot");
// Group by
erydbSelectExecutionPlan::GroupByColumnList groupByList;
SimpleColumn *g1 = new SimpleColumn (*c1);
groupByList.push_back (g1);
csep.groupByCols(groupByList);
// Order by
erydbSelectExecutionPlan::OrderByColumnList orderByList;
SimpleColumn *o1 = new SimpleColumn(*c1);
orderByList.push_back(o1);
csep.orderByCols(orderByList);
cout << csep;
}
void selectExecutionPlan_1() {
cout << "SQL: select r_regionkey from region, nation where n_regionkey = r_regionkey and n_regionkey = 2;" << endl;
CalpontSelectExecutionPlan csep;
CalpontSelectExecutionPlan::ReturnedColumnList colList;
ParseTree* filterList;
// returned columns
SimpleColumn r_regionkey("tpch.region.r_regionkey");
SimpleColumn n_regionkey("tpch.nation.n_regionkey");
SimpleColumn p_partkey("tpch.part.p_partkey");
SimpleColumn n_name("tpch.nation.n_name");
SimpleColumn c_custkey("tpch.customer.c_custkey");
colList.push_back(new SimpleColumn(r_regionkey));
// filters
CalpontSelectExecutionPlan::Parser parser;
std::vector<Token> tokens;
//tokens.push_back(Token(new Operator("(")));
//tokens.push_back(Token(new Operator(")")));
tokens.push_back(Token(new SimpleFilter(new Operator("="),
new SimpleColumn(r_regionkey),
new SimpleColumn(n_regionkey))));
tokens.push_back(Token(new Operator("and")));
tokens.push_back(Token(new SimpleFilter(new Operator("="),
new SimpleColumn(r_regionkey),
new SimpleColumn(c_custkey))));
tokens.push_back(Token(new Operator("and")));
tokens.push_back(Token(new Operator("(")));
tokens.push_back(Token(new SimpleFilter(new Operator("="),
new SimpleColumn(n_regionkey),
new ConstantColumn("779"))));
tokens.push_back(Token(new Operator("or")));
tokens.push_back(Token(new SimpleFilter(new Operator("!="),
new SimpleColumn(n_name),
new ConstantColumn("'ASIA'"))));
tokens.push_back(Token(new Operator(")")));
tokens.push_back(Token(new Operator ("and")));
tokens.push_back(Token(new Operator("(")));
tokens.push_back(Token(new SimpleFilter(new Operator("<"),
new SimpleColumn(n_regionkey),
new ConstantColumn("77"))));
tokens.push_back(Token(new Operator("or")));
tokens.push_back(Token(new SimpleFilter(new Operator(">"),
new SimpleColumn(p_partkey),
new ConstantColumn("7007"))));
tokens.push_back(Token(new Operator(")")));
filterList = parser.parse(tokens.begin(), tokens.end());
// draw filterList tree
filterList->drawTree("selectExecutionPlan_1.dot");
// calpont execution plan
csep.returnedCols (colList);
csep.filters (filterList);
cout << "\nCalpont Execution Plan:" << endl;
cout << csep << endl;
cout << " --- end of test 1 ---" << endl;
filterList->walk(walkfnString);
}
void Q1() {
string sql = "\
select\
supp_nation,\
cust_nation,\
l_year,\
sum(volume) as revenue\
from\
(\
select\
n1.n_name as supp_nation,\
n2.n_name as cust_nation,\
extract(year from l_shipdate) as l_year,\
l_extendedprice * (1 - l_discount) as volume\
from\
supplier,\
lineitem,\
orders,\
customer,\
nation n1,\
nation n2\
where\
s_suppkey = l_suppkey\
and o_orderkey = l_orderkey\
and c_custkey = o_custkey\
and s_nationkey = n1.n_nationkey\
and c_nationkey = n2.n_nationkey\
and (\
(n1.n_name = ':1' and n2.n_name = ':2')\
or (n1.n_name = ':2' and n2.n_name = ':1')\
)\
and l_shipdate between date '1995-01-01' and date '1996-12-31'\
) as shipping\
group by\
supp_nation,\
cust_nation,\
l_year\
order by\
supp_nation,\
cust_nation,\
l_year;";
CalpontSelectExecutionPlan csep;
// Returned columns
CalpontSelectExecutionPlan::ReturnedColumnList returnedColumnList;
// these columns are from the temp table of FROM clause.
// I hereby give schema name "calpont", table name "FROMTABLE",
// and alias name "shipping"
SimpleColumn *c1 = new SimpleColumn("calpont.FROMTABLE.supp_nation");
c1->tableAlias ("shipping");
returnedColumnList.push_back(c1);
SimpleColumn *c2 = new SimpleColumn("calpont.FROMTABLE.cust_nation");
c2->tableAlias ("shipping");
returnedColumnList.push_back(c2);
SimpleColumn *c3 = new SimpleColumn("tpch.lineitem.l_year");
returnedColumnList.push_back(c3);
ArithmeticColumn *c4 = new ArithmeticColumn("sum(volumn)");
c4->alias("revenue");
returnedColumnList.push_back(c4);
csep.returnedCols(returnedColumnList);
// from subselect
CalpontSelectExecutionPlan *subsep =
new CalpontSelectExecutionPlan(CalpontSelectExecutionPlan::FROM);
// subselect returned columns
CalpontSelectExecutionPlan::ReturnedColumnList subReturnedColList;
SimpleColumn *sc1 = new SimpleColumn("tpch.nation.n_name");
sc1->alias ("supp_nation");
sc1->tableAlias ("n1");
subReturnedColList.push_back(sc1);
SimpleColumn *sc2 = new SimpleColumn("tpch.nation.n_name");
sc1->alias ("cust_nation");
sc1->tableAlias ("n2");
subReturnedColList.push_back(sc2);
ArithmeticColumn *sc3 = new ArithmeticColumn("extract(year from tpch.lineitem.l_shipdate)");
sc3->alias("l_year");
subReturnedColList.push_back(sc3);
ArithmeticColumn *sc4 = new ArithmeticColumn("tpch.lineitem.l_extendeprice * (1-tpch.lineitem.l_discount)");
sc3->alias("volume");
subReturnedColList.push_back(sc4);
subsep->returnedCols(subReturnedColList);
// subselect filters
CalpontSelectExecutionPlan::FilterTokenList subFilterTokenList;
SimpleFilter *sf1 = new SimpleFilter (new Operator("="),
new SimpleColumn("tpch.supplier.s_suppkey"),
new SimpleColumn("tpch.lineitem.l_suppkey"));
subFilterTokenList.push_back(sf1);
subFilterTokenList.push_back(new Operator("and"));
SimpleFilter *sf2 = new SimpleFilter (new Operator("="),
new SimpleColumn("tpch.orders.o_orderkey"),
new SimpleColumn("tpch.lineitem.l_orderkey"));
subFilterTokenList.push_back(sf2);
subFilterTokenList.push_back(new Operator("and"));
SimpleFilter *sf3 = new SimpleFilter (new Operator("="),
new SimpleColumn("tpch.customer.c_custkey"),
new SimpleColumn("tpch.orders.o_custkey"));
subFilterTokenList.push_back(sf3);
subFilterTokenList.push_back(new Operator("and"));
SimpleColumn *n1 = new SimpleColumn ("tpch.nation.n_nationkey");
n1->tableAlias("n1");
SimpleFilter *sf4 = new SimpleFilter (new Operator("="),
new SimpleColumn("tpch.supplier.s_nationkey"),
n1);
subFilterTokenList.push_back(sf4);
subFilterTokenList.push_back(new Operator("and"));
SimpleColumn *n2 = new SimpleColumn ("tpch.nation.n_nationkey");
n2->tableAlias("n2");
SimpleFilter *sf5 = new SimpleFilter (new Operator("="),
new SimpleColumn("tpch.customer.c_nationkey"),
n2);
subFilterTokenList.push_back(sf5);
subFilterTokenList.push_back(new Operator("and"));
// ((n1.n_name = ':1' and n2.n_name = ':2')
// or (n1.n_name = ':2' and n2.n_name = ':1'))
subFilterTokenList.push_back(new Operator("("));
subFilterTokenList.push_back(new Operator("("));
SimpleColumn *n1_name = new SimpleColumn("tpch.nation.n_name");
n1_name->tableAlias ("n1");
SimpleFilter *sf6 = new SimpleFilter ( new Operator("="),
n1_name,
new ConstantColumn (":1"));
subFilterTokenList.push_back(sf6);
subFilterTokenList.push_back(new Operator("and"));
SimpleColumn *n2_name = new SimpleColumn("tpch.nation.n_name");
n1_name->tableAlias ("n2");
SimpleFilter *sf7 = new SimpleFilter ( new Operator("="),
n2_name,
new ConstantColumn (":2"));
subFilterTokenList.push_back(sf7);
subFilterTokenList.push_back(new Operator (")"));
subFilterTokenList.push_back(new Operator("or"));
subFilterTokenList.push_back(new Operator("("));
SimpleFilter *sf8 = new SimpleFilter ( new Operator("="),
new SimpleColumn(*n1_name),
new ConstantColumn (":2"));
subFilterTokenList.push_back(sf8);
subFilterTokenList.push_back(new Operator("and"));
SimpleFilter *sf9 = new SimpleFilter ( new Operator("="),
new SimpleColumn(*n2_name),
new ConstantColumn (":1"));
subFilterTokenList.push_back(sf9);
subFilterTokenList.push_back(new Operator (")"));
subFilterTokenList.push_back(new Operator (")"));
subFilterTokenList.push_back(new Operator("and"));
SimpleFilter *sf10 = new SimpleFilter (new Operator (">="),
new SimpleColumn ("tpch.lineitem.l_shipdate"),
new ConstantColumn ("1995-01-01"));
subFilterTokenList.push_back(sf10);
subFilterTokenList.push_back(new Operator("and"));
SimpleFilter *sf11 = new SimpleFilter (new Operator ("<="),
new SimpleColumn ("tpch.lineitem.l_shipdate"),
new ConstantColumn ("1995-01-06"));
subFilterTokenList.push_back(sf11);
subsep->filterTokenList(subFilterTokenList);
// end of subselect in FROM. push FROM subselect to selectList
// NOTE: only FROM subselect needs to be pushed into selectList.
// Subselects in WHERE or HAVING clause are in where or having
// filter parse tree. It may make more sense to change the member
// fSelectList of CSEP class to fFromSubSelect (type CSEP*)
CalpontSelectExecutionPlan::SelectList fromSubSelectList;
fromSubSelectList.push_back(subsep);
csep.subSelects(fromSubSelectList);
ParseTree* pt = const_cast<ParseTree*>(subsep->filters());
pt->drawTree("q7.dot");
// Group by
CalpontSelectExecutionPlan::GroupByColumnList groupByList;
SimpleColumn *g1 = new SimpleColumn (*c1);
groupByList.push_back (g1);
SimpleColumn *g2 = new SimpleColumn (*c2);
groupByList.push_back (g2);
SimpleColumn *g3 = new SimpleColumn (*c3);
groupByList.push_back (g3);
csep.groupByCols(groupByList);
// Order by
CalpontSelectExecutionPlan::OrderByColumnList orderByList;
SimpleColumn *o1 = new SimpleColumn(*c1);
orderByList.push_back(o1);
SimpleColumn *o2 = new SimpleColumn(*c2);
orderByList.push_back(o2);
SimpleColumn *o3 = new SimpleColumn(*c3);
orderByList.push_back(o3);
csep.orderByCols(orderByList);
cout << csep;
}
void Q1() {
string sql = "\
select\
l_orderkey,\
sum(l_extendedprice * (1 - l_discount)) as revenue,\
o_orderdate,\
o_shippriority\
from\
customer,\
orders,\
lineitem\
where\
c_mktsegment = ':1'\
and c_custkey = o_custkey\
and l_orderkey = o_orderkey\
and o_orderdate < date ':2'\
and l_shipdate > date ':2'\
group by\
l_orderkey,\
o_orderdate,\
o_shippriority\
order by\
revenue desc,\
o_orderdate;";
erydbSelectExecutionPlan csep;
// Returned columns
erydbSelectExecutionPlan::ReturnedColumnList returnedColumnList;
SimpleColumn *c1 = new SimpleColumn("tpch.lineitem.l_orderkey");
returnedColumnList.push_back(c1);
ArithmeticColumn *c2 = new ArithmeticColumn
("sum(tpch.lineitem.l_extendedprice * (1 - tpch.lineitem.l_discount))");
c2->alias("revenue");
returnedColumnList.push_back(c2);
SimpleColumn *c3 = new SimpleColumn("tpch.orders.o_orderdate");
returnedColumnList.push_back(c3);
SimpleColumn *c4 = new SimpleColumn("tpch.orders.o_shippriority");
returnedColumnList.push_back(c4);
csep.returnedCols(returnedColumnList);
// Filters
erydbSelectExecutionPlan::FilterTokenList filterTokenList;
SimpleFilter *f1 = new SimpleFilter (new Operator("="),
new SimpleColumn("tpch.customer.c_mktsegment"),
new ConstantColumn(":1"));
filterTokenList.push_back(f1);
filterTokenList.push_back(new Operator("and"));
SimpleFilter *f2 = new SimpleFilter (new Operator("="),
new SimpleColumn("tpch.customer.c_custkey"),
new SimpleColumn("tpch.orders.o_custkey"));
filterTokenList.push_back(f2);
filterTokenList.push_back(new Operator("and"));
SimpleFilter *f3 = new SimpleFilter (new Operator("="),
new SimpleColumn("tpch.lineitem.l_orderkey"),
new SimpleColumn("tpch.orders.o_orderkey"));
filterTokenList.push_back(f3);
filterTokenList.push_back(new Operator("and"));
SimpleFilter *f4 = new SimpleFilter (new Operator("<"),
new SimpleColumn("tpch.orders.o_orderdate"),
new ArithmeticColumn("date(':2')"));
filterTokenList.push_back(f4);
filterTokenList.push_back(new Operator("and"));
SimpleFilter *f5 = new SimpleFilter (new Operator(">"),
new SimpleColumn("tpch.lineitem.l_shipdate"),
new ArithmeticColumn("date(':2')"));
filterTokenList.push_back(f5);
csep.filterTokenList(filterTokenList);
ParseTree* pt = const_cast<ParseTree*>(csep.filters());
pt->drawTree("q3.dot");
// Group by
erydbSelectExecutionPlan::GroupByColumnList groupByList;
SimpleColumn *g1 = new SimpleColumn(*c1);
groupByList.push_back(g1);
SimpleColumn *g2 = new SimpleColumn(*c3);
groupByList.push_back(g2);
SimpleColumn *g3 = new SimpleColumn(*c4);
groupByList.push_back(g3);
csep.groupByCols(groupByList);
// Order by
erydbSelectExecutionPlan::OrderByColumnList orderByList;
//ArithmeticColumn *test = new ArithmeticColumn("a+b");
ArithmeticColumn *o1 = new ArithmeticColumn(*c2);
o1->asc(false);
orderByList.push_back(o1);
SimpleColumn *o2 = new SimpleColumn(*c3);
orderByList.push_back(o2);
csep.orderByCols(orderByList);
cout << csep;
}
void serializeCSEP()
{
/*
* erydbSelectExecutionPlan
* This is a large class; it makes more sense to write == operators
* for everything than to write a giant equivalance test here.
* For now this is mostly a regression test.
*/
erydbSelectExecutionPlan csep1, csep2;
erydbSelectExecutionPlan::ReturnedColumnList colList;
ParseTree* filterList;
erydbExecutionPlan *cep;
ByteStream b;
cep = &csep2;
CPPUNIT_ASSERT(csep1 == csep2);
CPPUNIT_ASSERT(!(csep1 != csep2));
CPPUNIT_ASSERT(csep1 == cep);
CPPUNIT_ASSERT(!(csep1 != cep));
// returned columns
SimpleColumn *sc = new SimpleColumn("tpch.region.r_regionkey");
colList.push_back(sc);
// filters
erydbSelectExecutionPlan::Parser parser;
std::vector<Token> tokens;
Token t;
SimpleFilter *sf = new SimpleFilter();
SimpleColumn *lhs = new SimpleColumn(*sc);
SimpleColumn *rhs = new SimpleColumn("tpch.nation.n_regionkey");
Operator *op = new Operator("=");
sf->op(op);
sf->lhs(lhs);
sf->rhs(rhs);
t.value = sf;
tokens.push_back(t);
Operator *op1 = new Operator ("and");
t.value = op1;
tokens.push_back(t);
SimpleFilter *sf1 = new SimpleFilter();
SimpleColumn *lhs1 = new SimpleColumn (*rhs);
ConstantColumn *constCol = new ConstantColumn("3", ConstantColumn::NUM);
Operator *op2 = new Operator("!=");
sf1->op(op2);
sf1->lhs(lhs1);
sf1->rhs(constCol);
t.value = sf1;
tokens.push_back(t);
filterList = parser.parse(tokens.begin(), tokens.end());
// draw filterList tree
filterList->drawTree("selectExecutionPlan_1.dot");
// erydb execution plan
csep1.returnedCols (colList);
csep1.filters (filterList);
CPPUNIT_ASSERT(csep1 != csep2);
CPPUNIT_ASSERT(!(csep1 == csep2));
CPPUNIT_ASSERT(csep1 != cep);
CPPUNIT_ASSERT(!(csep1 == cep));
csep1.serialize(b);
csep2.unserialize(b);
CPPUNIT_ASSERT(b.length() == 0);
CPPUNIT_ASSERT(csep1 == csep2);
CPPUNIT_ASSERT(!(csep1 != csep2));
CPPUNIT_ASSERT(csep1 == cep);
CPPUNIT_ASSERT(!(csep1 != cep));
erydbSelectExecutionPlan csep3, csep4;
// subselect
erydbSelectExecutionPlan *subselect = new erydbSelectExecutionPlan;
subselect->location(erydbSelectExecutionPlan::WHERE);
subselect->dependent (false);
CPPUNIT_ASSERT (subselect->location() == erydbSelectExecutionPlan::WHERE);
CPPUNIT_ASSERT (subselect->dependent() == false);
erydbSelectExecutionPlan::SelectList selectList;
selectList.push_back(subselect);
csep3.subSelects(selectList);
// exist filter
erydbSelectExecutionPlan* cep1 = new erydbSelectExecutionPlan();
ExistsFilter *filter = new ExistsFilter();
delete filter;
filter = new ExistsFilter(cep1);
filter->exists(cep1);
//erydbSelectExecutionPlan* cep2 = const_cast<erydbSelectExecutionPlan*>(filter->exists());
erydbSelectExecutionPlan::Parser parser1;
std::vector<Token> tokens1;
Token t1;
t1.value = filter;
tokens1.push_back(t1);
csep3.filters(parser1.parse(tokens1.begin(), tokens1.end()));
csep3.serialize(b);
csep4.unserialize(b);
CPPUNIT_ASSERT(csep3 == csep4);
CPPUNIT_ASSERT(!(csep3 != csep4));
}
void selectExecutionPlan_1() {
cout << "SQL: select region.r_regionkey from region, nation where nation.n_regionkey = region.r_regionkey and nation.n_regionkey != 3;" << endl;
erydbSelectExecutionPlan csep;
CPPUNIT_ASSERT (csep.location() == erydbSelectExecutionPlan::MAIN);
CPPUNIT_ASSERT (csep.dependent() == false);
CPPUNIT_ASSERT (csep.subSelects().size() == 0);
// returned columns
erydbSelectExecutionPlan::ReturnedColumnList colList;
SimpleColumn *sc = new SimpleColumn("tpch.region.r_regionkey", 0);
colList.push_back(sc);
ArithmeticColumn *ac = new ArithmeticColumn("a+sum(r_regionkey)", 0);
colList.push_back(ac);
csep.returnedCols (colList);
CPPUNIT_ASSERT(csep.returnedCols().size() == 2);
// filters
erydbSelectExecutionPlan::FilterTokenList filterTokenList;
SimpleFilter *sf = new SimpleFilter();
SimpleColumn *lhs = new SimpleColumn();
*lhs = *sc;
SimpleColumn *rhs = new SimpleColumn("tpch.nation.n_regionkey", 0);
CPPUNIT_ASSERT (*lhs == *sc);
CPPUNIT_ASSERT (*rhs != *lhs);
Operator *op = new Operator("=");
sf->op(op);
sf->lhs(lhs);
sf->rhs(rhs);
filterTokenList.push_back (sf);
filterTokenList.push_back( new Operator ("And") );
SimpleFilter *sf1 = new SimpleFilter (new Operator("="), sc->clone(), ac->clone());
filterTokenList.push_back (sf1);
csep.filterTokenList (filterTokenList);
ParseTree *filterList = const_cast<ParseTree*> (csep.filters());
// draw filterList tree
filterList->drawTree("selectExecutionPlan_1.dot");
csep.filters (filterList);
// Group by
erydbSelectExecutionPlan::GroupByColumnList groupByList;
groupByList.push_back(sc->clone());
csep.groupByCols (groupByList);
CPPUNIT_ASSERT(csep.groupByCols().size() == 1);
// Having
erydbSelectExecutionPlan::FilterTokenList havingTokenList;
SimpleFilter *having = new SimpleFilter( new Operator("="),
new ArithmeticColumn("sum(volumn)", 0),
new ConstantColumn(8));
havingTokenList.push_back (having);
csep.havingTokenList (havingTokenList);
CPPUNIT_ASSERT (*sf1 != *having);
CPPUNIT_ASSERT (csep.havingTokenList().size() == 1);
// Order by
erydbSelectExecutionPlan::OrderByColumnList orderByList;
ArithmeticColumn *o1 = new ArithmeticColumn(*ac);
o1->asc(false);
orderByList.push_back(o1);
csep.orderByCols(orderByList);
CPPUNIT_ASSERT(csep.orderByCols().size() == 1);
// another csep
erydbSelectExecutionPlan *newcsep = new erydbSelectExecutionPlan(erydbSelectExecutionPlan::FROM);
erydbSelectExecutionPlan::ReturnedColumnList ncolList;
SimpleColumn *newsc = new SimpleColumn("tpch.region.r_regionkey", 0);
ncolList.push_back(newsc);
newcsep->returnedCols (ncolList);
erydbSelectExecutionPlan::FilterTokenList nfilterTokenList;
SimpleFilter *newsf = new SimpleFilter ( new Operator (">"),
sc->clone(),
newsc->clone());
nfilterTokenList.push_back(newsf);
newcsep->filterTokenList (nfilterTokenList);
erydbSelectExecutionPlan::FilterTokenList nhavingTokenList;
SimpleFilter *newhaving = new SimpleFilter ( new Operator (">"),
sc->clone(),
newsc->clone());
CPPUNIT_ASSERT (*newsf == *newhaving);
nhavingTokenList.push_back(newhaving);
newcsep->havingTokenList (nhavingTokenList);
CPPUNIT_ASSERT (*newcsep != csep);
CPPUNIT_ASSERT (*newcsep->filters() == *newcsep->having());
ByteStream b;
csep.serialize (b);
newcsep->unserialize (b);
CPPUNIT_ASSERT (csep == *newcsep);
erydbSelectExecutionPlan::SelectList selectList;
selectList.push_back(newcsep);
csep.subSelects(selectList);
cout << "\nerydb Execution Plan:" << endl;
cout << csep;
cout << " --- end of test 1 ---" << endl;
}
