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\ 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\ 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;"; CalpontSelectExecutionPlan csep; // Returned columns CalpontSelectExecutionPlan::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 CalpontSelectExecutionPlan::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 CalpontSelectExecutionPlan::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 CalpontSelectExecutionPlan::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; }