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 derivedTableOptimization(SCSEP& csep) { // @bug5634. replace the unused column with ConstantColumn from derived table column list, // ExeMgr will not project ConstantColumn. Only count for local derived column. // subquery may carry main query derived table list for column reference, those // derived tables are not checked for optimization in this scope. CalpontSelectExecutionPlan::SelectList derivedTbList = csep->derivedTableList(); // @bug6156. Skip horizontal optimization for no table union. bool horizontalOptimization = true; for (uint i = 0; i < derivedTbList.size(); i++) { CalpontSelectExecutionPlan* plan = dynamic_cast<CalpontSelectExecutionPlan*>(derivedTbList[i].get()); CalpontSelectExecutionPlan::ReturnedColumnList cols = plan->returnedCols(); vector<CalpontSelectExecutionPlan::ReturnedColumnList> unionColVec; // only do vertical optimization for union all // @bug6134. Also skip the vertical optimization for select distinct // because all columns need to be projected to check the distinctness. bool verticalOptimization = false; if (plan->distinctUnionNum() == 0 && !plan->distinct()) { verticalOptimization = true; for (uint j = 0; j < plan->unionVec().size(); j++) { unionColVec.push_back( dynamic_cast<CalpontSelectExecutionPlan*>(plan->unionVec()[j].get())->returnedCols()); } } if (plan->tableList().empty()) horizontalOptimization = false; for (uint j = 0; j < plan->unionVec().size(); j++) { if (dynamic_cast<CalpontSelectExecutionPlan*>(plan->unionVec()[j].get())->tableList().empty()) { horizontalOptimization = false; break; } } if (verticalOptimization) { int64_t val = 1; for (uint i = 0; i < cols.size(); i++) { //if (cols[i]->derivedTable().empty()) if (cols[i]->refCount() == 0) { if (cols[i]->derivedRefCol()) cols[i]->derivedRefCol()->decRefCount(); cols[i].reset(new ConstantColumn(val)); for (uint j = 0; j < unionColVec.size(); j++) unionColVec[j][i].reset(new ConstantColumn(val)); } } // set back plan->returnedCols(cols); for (uint j = 0; j < unionColVec.size(); j++) dynamic_cast<CalpontSelectExecutionPlan*>(plan->unionVec()[j].get())->returnedCols(unionColVec[j]); } } /* * @bug5635. Move filters that only belongs to a derived table to inside the derived table. * 1. parse tree walk to populate derivedTableFilterMap and set null candidate on the tree. * 2. remove the null filters * 3. and the filters of derivedTableFilterMap and append to the WHERE filter of the derived table * * Note: * 1. Subquery filters is ignored because derived table can not be in subquery * 2. While walking tree, whenever a single derive simplefilter is encountered, * this filter is pushed to the corresponding stack * 2. Whenever an OR operator is encountered, all the filter stack of * that OR involving derived table are emptied and null candidate of each * stacked filter needs to be reset (not null) */ ParseTree* pt = csep->filters(); map<string, ParseTree*> derivedTbFilterMap; if (horizontalOptimization && pt) { pt->walk(setDerivedTable); setDerivedFilter(pt, derivedTbFilterMap, derivedTbList); csep->filters(pt); } // AND the filters of individual stack to the derived table filter tree // @todo union filters. // @todo outer join complication map<string, ParseTree*>::iterator mapIt; for (uint i = 0; i < derivedTbList.size(); i++) { CalpontSelectExecutionPlan* plan = dynamic_cast<CalpontSelectExecutionPlan*>(derivedTbList[i].get()); CalpontSelectExecutionPlan::ReturnedColumnList derivedColList = plan->returnedCols(); mapIt = derivedTbFilterMap.find(plan->derivedTbAlias()); if (mapIt != derivedTbFilterMap.end()) { // replace all derived column of this filter with real column from // derived table projection list. ParseTree* mainFilter = new ParseTree(); mainFilter->copyTree(*(mapIt->second)); replaceRefCol(mainFilter, derivedColList); ParseTree* derivedFilter = plan->filters(); if (derivedFilter) { LogicOperator* op = new LogicOperator("and"); ParseTree* filter = new ParseTree(op); filter->left(derivedFilter); filter->right(mainFilter); plan->filters(filter); } else { plan->filters(mainFilter); } // union filter handling for (uint j = 0; j < plan->unionVec().size(); j++) { CalpontSelectExecutionPlan* unionPlan = dynamic_cast<CalpontSelectExecutionPlan*>(plan->unionVec()[j].get()); CalpontSelectExecutionPlan::ReturnedColumnList unionColList = unionPlan->returnedCols(); ParseTree* mainFilterForUnion = new ParseTree(); mainFilterForUnion->copyTree(*(mapIt->second)); replaceRefCol(mainFilterForUnion, unionColList); ParseTree* unionFilter = unionPlan->filters(); if (unionFilter) { LogicOperator* op = new LogicOperator("and"); ParseTree* filter = new ParseTree(op); filter->left(unionFilter); filter->right(mainFilterForUnion); unionPlan->filters(filter); } else { unionPlan->filters(mainFilterForUnion); } } } } // clean derivedTbFilterMap because all the filters are copied for ( mapIt = derivedTbFilterMap.begin(); mapIt != derivedTbFilterMap.end(); ++mapIt ) delete (*mapIt).second; // recursively process the nested derived table for (uint i = 0; i < csep->subSelectList().size(); i++) { SCSEP subselect(boost::dynamic_pointer_cast<CalpontSelectExecutionPlan>(csep->subSelectList()[i])); derivedTableOptimization(subselect); } }
int main(int argc, char** argv) { vflg = false; uint32_t tlvl = 0; bool dflg = false; int c; int32_t sid = -1; bool Bflg = false; opterr = 0; while ((c = getopt(argc, argv, "vt:ds:Bh")) != EOF) switch (c) { case 't': tlvl = static_cast<uint32_t>(strtoul(optarg, 0, 0)); break; case 'v': vflg = true; break; case 'd': dflg = true; break; case 's': sid = static_cast<int32_t>(strtol(optarg, 0, 0)); break; case 'B': Bflg = true; break; case 'h': case '?': default: usage(); return (c == 'h' ? 0 : 1); break; } if (dflg) vflg = true; if ((argc - optind) < 1) { usage(); return 1; } ifstream inputf; ByteStream bs; ByteStream dbs; ByteStream eoq; ByteStream tbs; ByteStream statsStream; ByteStream::quadbyte q = 0; eoq << q; uint32_t sessionid; time_t t; SJLP jl; DeliveredTableMap tm; DeliveredTableMap::iterator iter; DeliveredTableMap::iterator end; CalpontSelectExecutionPlan csep; struct timeval start_time; struct timeval end_time; MessageQueueClient* mqc = 0; if (!dflg) mqc = new MessageQueueClient("ExeMgr1"); if (sid == -1) { time(&t); sessionid = static_cast<uint32_t>(t); } else { sessionid = static_cast<uint32_t>(sid); } sessionid &= 0x7fffffff; logging::ErrorCodes errorCodes; for ( ; optind < argc; optind++) { inputf.open(argv[optind]); if (!inputf.good()) { cerr << "error opening plan stream " << argv[optind] << endl; return 1; } bs.reset(); inputf >> bs; inputf.close(); csep.unserialize(bs); csep.sessionID(sessionid); SessionManager sm; csep.verID(sm.verID()); csep.traceFlags(0); ResourceManager rm; jl = JobListFactory::makeJobList(&csep, rm); csep.traceFlags(tlvl); if (vflg) { if (dflg) cout << endl << "Query:" << endl; else { cout << endl << "Session: " << sessionid << ", Sending Query"; if (Bflg) cout << " (" << argv[optind] << ')'; cout << ':' << endl; } if (!Bflg) cout << csep.data() << endl << endl; } if (dflg) continue; try { dbs.reset(); csep.serialize(dbs); gettimeofday(&start_time, 0); //try tuples first, but expect the worst... bool expectTuples = false; ByteStream tbs; ByteStream::quadbyte tqb = 4; tbs << tqb; mqc->write(tbs); //send the CSEP mqc->write(dbs); //read the response to the tuple request tbs = mqc->read(); idbassert(tbs.length() == 4); tbs >> tqb; if (tqb == 4) expectTuples = true; if (!expectTuples) cout << "Using TableBand I/F" << endl; else cout << "Using tuple I/F" << endl; tm = jl->deliveredTables(); iter = tm.begin(); end = tm.end(); OID toid; uint64_t rowTot; bool reported = false; bool needRGCtor = true; while (iter != end) { toid = iter->first; q = static_cast<ByteStream::quadbyte>(toid); tbs.reset(); tbs << q; mqc->write(tbs); ByteStream tbbs; TableBand tb; RowGroup rg; rowTot = 0; uint16_t status = 0; TableBand::VBA::size_type rc; ofstream out; for (;;) { tbbs = mqc->read(); #if 0 cout << tbbs.length() << endl; out.open("bs1.dat"); idbassert(out.good()); out << tbbs; out.close(); tbbs = mqc->read(); cout << tbbs.length() << endl; out.open("bs2.dat"); idbassert(out.good()); out << tbbs; out.close(); tbbs = mqc->read(); cout << tbbs.length() << endl; out.open("bs3.dat"); idbassert(out.good()); out << tbbs; out.close(); #endif if(tbbs.length()) { if (!expectTuples) tb.unserialize(tbbs); else { if (needRGCtor) { rg.deserialize(tbbs); needRGCtor = false; tbbs = mqc->read(); } rg.setData((uint8_t*)tbbs.buf()); } } else { //@bug 1346 if (!status) status = logging::makeJobListErr; break; } if (!expectTuples) { rc = tb.getRowCount(); status = tb.getStatus(); } else { rc = rg.getRowCount(); status = rg.getStatus(); if (rc == 0) status = 0; } if (rc == 0) break; rowTot += rc; } BatchPrimitive* step = dynamic_cast<BatchPrimitive*>( iter->second.get() ); if (vflg && step) { cout << "For table " << step->tableName(); if (!Bflg) cout << " " << toid; cout << ": read " << rowTot << " rows" << endl; } if (status && !reported) { cout << "### Query failed: " << errorCodes.errorString(status) << " Check crit.log\n"; reported = true; } if (!step && !reported) { cout << "### Query failed: Did not return project BatchPrimitive. Check crit.log\n"; reported = true; } ++iter; } if (vflg) { gettimeofday(&end_time, 0); cout << "Query time: " << fixed << setprecision(1) << tm_diff(&start_time, &end_time) << " secs" << endl; //...Ask for query stats through special table id of 3 const OID TABLE_ID_TO_GET_QUERY_STATS = 3; if (!Bflg) cout << "Retrieving stats..." << endl; toid = TABLE_ID_TO_GET_QUERY_STATS; q = static_cast<ByteStream::quadbyte>(toid); statsStream.reset(); statsStream << q; mqc->write(statsStream); ByteStream bs_statsString; bs_statsString = mqc->read(); string statsString; bs_statsString >> statsString; string printStatsString; struct timeval startRunTime; parseStatsString (statsString, printStatsString, startRunTime); cout << printStatsString << "; QuerySetupTime-" << tm_diff(&start_time, &startRunTime) << "secs" << endl; } //...Close this query/session mqc->write(eoq); jl.reset(); } catch(const exception& ex) { cout << "### SendPlan caught an exception: " << ex.what() << endl; } } // jl.reset(); CalpontSystemCatalog::removeCalpontSystemCatalog( sessionid ); config::Config::deleteInstanceMap(); delete mqc; return 0; }
ParseTree* setDerivedFilter(ParseTree*& n, map<string, ParseTree*>& filterMap, CalpontSelectExecutionPlan::SelectList& derivedTbList) { if (!(n->derivedTable().empty())) { // @todo replace virtual column of n to real column // all simple columns should belong to the same derived table CalpontSelectExecutionPlan* csep = NULL; for (uint i = 0; i < derivedTbList.size(); i++) { CalpontSelectExecutionPlan* plan = dynamic_cast<CalpontSelectExecutionPlan*>(derivedTbList[i].get()); if (plan->derivedTbAlias() == n->derivedTable()) { csep = plan; break; } } // should never be null; if null then give up optimization. if (!csep) return n; // 2. push the filter to the derived table filter stack, or 'and' with // the filters in the stack map<string, ParseTree*>::iterator mapIter = filterMap.find(n->derivedTable()); if ( mapIter == filterMap.end()) { filterMap.insert(pair<string, ParseTree*>(n->derivedTable(), n)); } else { ParseTree* pt = new ParseTree(new LogicOperator("and")); pt->left(mapIter->second); pt->right(n); mapIter->second = pt; } int64_t val = 1; n = new ParseTree(new ConstantColumn(val)); } else { Operator* op = dynamic_cast<Operator*>(n->data()); if (op && (op->op() == OP_OR || op->op() == OP_XOR)) { return n; } else { ParseTree* lhs = n->left(); ParseTree* rhs = n->right(); if (lhs) n->left(setDerivedFilter(lhs, filterMap, derivedTbList)); if (rhs) n->right(setDerivedFilter(rhs, filterMap, derivedTbList)); } } return n; }
/** @brief the wrapper function to Oracle stored function cal_get_explain_plan */ int getPlan( OCIExtProcContext* ctx, int sessionId, const char* curSchemaName, CalpontSelectExecutionPlan &plan, BindValueSet* bindValList) { pu::myCtx = ctx; pu::sessionid = sessionId; strcpy(pu::curschema, curSchemaName); string stmt = get_sql_text(sessionId); plan.data(stmt); // @bug 1331 string sqlCommand = stmt.substr(0, stmt.find_first_of(' ', 0)); if (strcasecmp(sqlCommand.c_str(), "CREATE") == 0) { string tmpsql = stmt; boost::to_upper(tmpsql); string::size_type sPos = tmpsql.find("SELECT"); if (sPos != string::npos) stmt = stmt.substr(sPos); } string newStmt = stmt; #if BIND_VARIABLE_SUPPORT BindValue *elem; BindValue_ind *elem_ind; boolean j; sb4 size; pu::checkerr (pu::errhp, OCICollSize ( envhp, pu::errhp, bindValList, &size )); char sql[SQL_MAX_SIZE]; char bindVar[SQL_MAX_SIZE]; char bindVal[SQL_MAX_SIZE]; for (sb4 i = 0; i < size; i++) { pu::checkerr(pu::errhp, OCICollGetElem ( envhp, pu::errhp, bindValList, i, &j,(void**)&elem, (void**)&elem_ind )); if (!elem->sqltext || !elem->bindVar || !elem->bindValue) continue; strcpy(sql, (char*) OCIStringPtr(envhp, elem->sqltext)); strcpy(bindVar, (char*) OCIStringPtr(envhp, elem->bindVar)); strcpy(bindVal, (char*) OCIStringPtr(envhp, elem->bindValue)); // replace bind variable with bind values if sql stmt matches if (strcmp(sql, stmt.c_str()) == 0) { string bindName = ":"; bindName.append(bindVar); string::size_type pos = newStmt.find(bindName); newStmt.replace(pos, bindName.length(), bindVal); } } #endif strcpy(sqltext, newStmt.c_str()); pu::checkerr(pu::errhp, OCIStmtExecute(svchp, stmthp, pu::errhp, (ub4)1, (ub4)0, (OCISnapshot *)NULL, (OCISnapshot *)NULL, (ub4)OCI_DEFAULT)); // fetch result and convert pu::getPlanRecords(stmthp1); pu::doConversion(plan); return 0; }
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;"; 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; }