First commit for advanced predicate pushdown feature (also known as pushdown V2) associated JIRA TRAFODION-1662 Predicate push down revisited (V2). The JIRA contains a blueprint document, useful to understand what the code is supposed to do. This code is enabled using CQD hbase_filter_preds '2', and bypassed otherwise. Except for the change implemented in ValueDesc.cpp that is a global bug fix whereValueIdSet are supposed to contain set of valueID ANDed together, and should not contain any ValueID with operator ITM_AND.
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/1c5f243f Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/1c5f243f Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/1c5f243f Branch: refs/heads/master Commit: 1c5f243f7c79e9ceb4a008099e60641d90515037 Parents: ebe876d Author: Eric Owhadi <[email protected]> Authored: Thu Jan 7 01:25:54 2016 +0000 Committer: Eric Owhadi <[email protected]> Committed: Thu Jan 7 01:25:54 2016 +0000 ---------------------------------------------------------------------- core/sql/executor/ExHbaseAccess.cpp | 92 +- core/sql/generator/GenExplain.cpp | 284 +++++- core/sql/generator/GenPreCode.cpp | 587 +++++++++++- core/sql/generator/GenRelScan.cpp | 3 +- core/sql/optimizer/RelScan.h | 10 + core/sql/optimizer/ValueDesc.cpp | 7 +- core/sql/regress/executor/EXPECTED140 | 911 +++++++++++++++++++ core/sql/regress/executor/FILTER140 | 42 + core/sql/regress/executor/TEST140 | 96 ++ core/sql/regress/seabase/EXPECTED010 | 454 +++++---- core/sql/regress/seabase/EXPECTED011 | 22 +- core/sql/regress/seabase/EXPECTED016 | 161 ++-- core/sql/regress/tools/runregr_executor.ksh | 2 +- core/sql/regress/tools/sbdefs | 2 +- core/sql/sqlcomp/DefaultConstants.h | 2 +- core/sql/sqlcomp/nadefaults.cpp | 17 + .../java/org/trafodion/sql/HTableClient.java | 571 +++++++++++- 17 files changed, 2891 insertions(+), 372 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/1c5f243f/core/sql/executor/ExHbaseAccess.cpp ---------------------------------------------------------------------- diff --git a/core/sql/executor/ExHbaseAccess.cpp b/core/sql/executor/ExHbaseAccess.cpp index 6559587..e229d2b 100644 --- a/core/sql/executor/ExHbaseAccess.cpp +++ b/core/sql/executor/ExHbaseAccess.cpp @@ -2808,53 +2808,51 @@ short ExHbaseAccessTcb::setupHbaseFilterPreds() (hbaseAccessTdb().listOfHbaseFilterColNames()->numEntries() == 0)) return 0; - if (! hbaseFilterValExpr()) - return 0; - - ex_queue_entry *pentry_down = qparent_.down->getHeadEntry(); - - workAtp_->getTupp(hbaseAccessTdb().hbaseFilterValTuppIndex_) - .setDataPointer(hbaseFilterValRow_); - - ex_expr::exp_return_type evalRetCode = - hbaseFilterValExpr()->eval(pentry_down->getAtp(), workAtp_); - if (evalRetCode == ex_expr::EXPR_ERROR) - { - return -1; - } - - ExpTupleDesc * hfrTD = - hbaseAccessTdb().workCriDesc_->getTupleDescriptor - (hbaseAccessTdb().hbaseFilterValTuppIndex_); - - hbaseFilterValues_.clear(); - for (Lng32 i = 0; i < hfrTD->numAttrs(); i++) - { - Attributes * attr = hfrTD->getAttr(i); - - if (attr) - { - NAString value(getHeap()); - if (attr->getNullFlag()) - { - char nullValChar = 0; - - short nullVal = *(short*)&hbaseFilterValRow_[attr->getNullIndOffset()]; - - if (nullVal) - nullValChar = -1; - value.append((char*)&nullValChar, sizeof(char)); - } - - char * colVal = &hbaseFilterValRow_[attr->getOffset()]; - - value.append(colVal, - attr->getLength(&hbaseFilterValRow_[attr->getVCLenIndOffset()])); - - hbaseFilterValues_.insert(value); - } - } - + if (hbaseFilterValExpr()){// with pushdown V2 it can be null if we have only unary operation + ex_queue_entry *pentry_down = qparent_.down->getHeadEntry(); + + workAtp_->getTupp(hbaseAccessTdb().hbaseFilterValTuppIndex_) + .setDataPointer(hbaseFilterValRow_); + + ex_expr::exp_return_type evalRetCode = + hbaseFilterValExpr()->eval(pentry_down->getAtp(), workAtp_); + if (evalRetCode == ex_expr::EXPR_ERROR) + { + return -1; + } + + ExpTupleDesc * hfrTD = + hbaseAccessTdb().workCriDesc_->getTupleDescriptor + (hbaseAccessTdb().hbaseFilterValTuppIndex_); + + hbaseFilterValues_.clear(); + for (Lng32 i = 0; i < hfrTD->numAttrs(); i++) + { + Attributes * attr = hfrTD->getAttr(i); + + if (attr) + { + NAString value(getHeap()); + if (attr->getNullFlag()) + { + char nullValChar = 0; + + short nullVal = *(short*)&hbaseFilterValRow_[attr->getNullIndOffset()]; + + if (nullVal) + nullValChar = -1; + value.append((char*)&nullValChar, sizeof(char)); + } + + char * colVal = &hbaseFilterValRow_[attr->getOffset()]; + + value.append(colVal, + attr->getLength(&hbaseFilterValRow_[attr->getVCLenIndOffset()])); + + hbaseFilterValues_.insert(value); + } + } + } setupListOfColNames(hbaseAccessTdb().listOfHbaseFilterColNames(), hbaseFilterColumns_); http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/1c5f243f/core/sql/generator/GenExplain.cpp ---------------------------------------------------------------------- diff --git a/core/sql/generator/GenExplain.cpp b/core/sql/generator/GenExplain.cpp index 28e6c45..e79f9fc 100644 --- a/core/sql/generator/GenExplain.cpp +++ b/core/sql/generator/GenExplain.cpp @@ -660,6 +660,269 @@ FileScan::addSpecificExplainInfo(ExplainTupleMaster *explainTuple, return(explainTuple); } +static void appendListOfColumns(Queue* listOfColNames,ComTdb *tdb, NAString& outNAString){ + + if (((ComTdbHbaseAccess*)tdb)->sqHbaseTable()){// if trafodion table + char buf[1000]; + + listOfColNames->position(); + for (Lng32 j = 0; j < listOfColNames->numEntries(); j++) + { + char * currPtr = (char*)listOfColNames->getCurr(); + + Lng32 currPos = 0; + Lng32 jj = 0; + short colNameLen = *(short*)currPtr; + currPos += sizeof(short); + char colFam[100]; + while (currPtr[currPos] != ':') + { + colFam[jj] = currPtr[currPos]; + currPos++; + jj++; + } + colFam[jj] = ':'; + jj++; + currPos++; + colFam[jj] = 0; + colNameLen -= jj; + + NABoolean withAt = FALSE; + char * colName = &currPtr[currPos]; + if (colName[0] == '@') + { + colNameLen--; + colName++; + withAt = TRUE; + } + + Int64 v; + if (colNameLen == sizeof(char)) + v = *(char*)colName; + else if (colNameLen == sizeof(unsigned short)) + v = *(UInt16*)colName; + else if (colNameLen == sizeof(Lng32)) + v = *(ULng32*)colName; + else + v = 0; + if (j==0) + str_sprintf(buf, "%s%s%Ld", + colFam, + (withAt ? "@" : ""), + v); + else + str_sprintf(buf, ",%s%s%Ld", + colFam, + (withAt ? "@" : ""), + v); + + outNAString += buf; + + listOfColNames->advance(); + } // for + }// trafodion tables + else + {// if hbase native tables + char buf[1000]; + + listOfColNames->position(); + for (Lng32 j = 0; j < listOfColNames->numEntries(); j++) + { + char * currPtr = (char*)listOfColNames->getCurr(); + + char * colNamePtr = NULL; + + Lng32 currPos = 0; + short colNameLen = *(short*)currPtr; + currPos += sizeof(short); + char colName[500]; + + for (Lng32 i = 0; i < colNameLen; i++) + { + colName[i] = currPtr[currPos]; + currPos++; + } + + colName[colNameLen] = 0; + + colNamePtr = colName; + + if (j==0) + str_sprintf(buf, "%s",colNamePtr); + else + str_sprintf(buf, ",%s",colNamePtr); + + + outNAString += buf; + + listOfColNames->advance(); + } // for + + }// hbase native table + outNAString +=" "; +} + +static void appendPushedDownExpression(ComTdb *tdb, NAString& outNAString){ + // in predicate pushdown V2, the hbaseCompareOps list contains a reverse polish set of operation, were operators are + // AND or OR, the rest are operands. this function display the column, operator and replace any constant with ?. it keeps reverse polish format + // this can be improved in the future for better readability. + char buf[1000]; + Queue* reversePolishItems = ((ComTdbHbaseAccess *)tdb)->listOfHbaseCompareOps(); + Queue* pushedDownColumns = ((ComTdbHbaseAccess *)tdb)->listOfHbaseFilterColNames(); + reversePolishItems->position(); + pushedDownColumns->position(); + + for (Lng32 j = 0; j < reversePolishItems->numEntries(); j++){ + char * currPtr = (char*)reversePolishItems->getCurr(); + char buf2[1000]; + if (strcmp(currPtr,"V2")!=0 && strcmp(currPtr,"AND")!=0 && strcmp(currPtr,"OR")!=0){//if an operand (not an operator or V2 marker), get the column name + if (((ComTdbHbaseAccess*)tdb)->sqHbaseTable()){// if trafodion table + char * currPtr2 = (char*)pushedDownColumns->getCurr(); + Lng32 currPos = 0; + Lng32 jj = 0; + short colNameLen = *(short*)currPtr2; + currPos += sizeof(short); + char colFam[100]; + while (currPtr2[currPos] != ':') + { + colFam[jj] = currPtr2[currPos]; + currPos++; + jj++; + } + colFam[jj] = ':'; + jj++; + currPos++; + colFam[jj] = 0; + colNameLen -= jj; + + NABoolean withAt = FALSE; + char * colName = &currPtr2[currPos]; + if (colName[0] == '@') + { + colNameLen--; + colName++; + withAt = TRUE; + } + Int64 v; + if (colNameLen == sizeof(char)) + v = *(char*)colName; + else if (colNameLen == sizeof(unsigned short)) + v = *(UInt16*)colName; + else if (colNameLen == sizeof(Lng32)) + v = *(ULng32*)colName; + else + v = 0; + str_sprintf(buf2, "%s%s%Ld", + colFam, + (withAt ? "@" : ""), + v); + + }else{//native hbase table + char * currPtr2 = (char*)pushedDownColumns->getCurr(); + char * colNamePtr1 = NULL; + Lng32 currPos = 0; + short colNameLen = *(short*)currPtr2; + currPos += sizeof(short); + char colName[500]; + for (Lng32 i = 0; i < colNameLen; i++) + { + colName[i] = currPtr2[currPos]; + currPos++; + } + colName[colNameLen] = 0; + colNamePtr1 = colName; + str_sprintf(buf2, "%s",colNamePtr1); + } + pushedDownColumns->advance(); + } + + + char* colNamePtr = buf2; + if(strcmp(currPtr,"EQUAL")==0){ + str_sprintf(buf, "(%s=?)",colNamePtr); + outNAString += buf; + } + else if (strcmp(currPtr,"NOT_EQUAL")==0){ + str_sprintf(buf, "(%s!=?)",colNamePtr); + outNAString += buf; + } + else if (strcmp(currPtr,"LESS")==0){ + str_sprintf(buf, "(%s<?)",colNamePtr); + outNAString += buf; + } + else if(strcmp(currPtr,"LESS_OR_EQUAL")==0){ + str_sprintf(buf, "(%s<=?)",colNamePtr); + outNAString += buf; + } + else if (strcmp(currPtr,"GREATER")==0){ + str_sprintf(buf, "(%s>?)",colNamePtr); + outNAString += buf; + } + else if (strcmp(currPtr,"GREATER_OR_EQUAL")==0){ + str_sprintf(buf, "(%s>=?)",colNamePtr); + outNAString += buf; + } + else if (strcmp(currPtr,"NO_OP")==0){//should never happen + str_sprintf(buf, "(%s??)",colNamePtr); + outNAString += buf; + } + else if (strcmp(currPtr,"EQUAL_NULL")==0){ + str_sprintf(buf, "(%s=.?)",colNamePtr); + outNAString += buf; + } + else if (strcmp(currPtr,"NOT_EQUAL_NULL")==0){ + str_sprintf(buf, "(%s!=.?)",colNamePtr); + outNAString += buf; + } + else if (strcmp(currPtr,"LESS_NULL")==0){ + str_sprintf(buf, "(%s<.?)",colNamePtr); + outNAString += buf; + } + else if (strcmp(currPtr,"LESS_OR_EQUAL_NULL")==0){ + str_sprintf(buf, "(%s<=.?)",colNamePtr); + outNAString += buf; + } + else if (strcmp(currPtr,"GREATER_NULL")==0){ + str_sprintf(buf, "(%s>.?)",colNamePtr); + outNAString += buf; + } + else if (strcmp(currPtr,"GREATER_OR_EQUAL_NULL")==0){ + str_sprintf(buf, "(%s>=.?)",colNamePtr); + outNAString += buf; + } + else if (strcmp(currPtr,"NO_OP_NULL")==0){ + str_sprintf(buf, "(%s?.?)",colNamePtr);//should never happen + outNAString += buf; + } + else if (strcmp(currPtr,"IS_NULL")==0){ + str_sprintf(buf, "(%s is_null)",colNamePtr); + outNAString += buf; + } + else if (strcmp(currPtr,"IS_NULL_NULL")==0){ + str_sprintf(buf, "(%s is_null.)",colNamePtr); + outNAString += buf; + } + else if (strcmp(currPtr,"IS_NOT_NULL")==0){ + str_sprintf(buf, "(%s is_not_null)",colNamePtr); + outNAString += buf; + } + else if (strcmp(currPtr,"IS_NOT_NULL_NULL")==0){ + str_sprintf(buf, "(%s is_not_null.)",colNamePtr); + outNAString += buf; + } + else if (strcmp(currPtr,"AND")==0) + outNAString += "AND"; + else if (strcmp(currPtr,"OR")==0) + outNAString += "OR"; + + + reversePolishItems->advance(); + } + outNAString +=' '; + } + + + ExplainTuple * HbaseAccess::addSpecificExplainInfo(ExplainTupleMaster *explainTuple, ComTdb * tdb, @@ -754,16 +1017,30 @@ HbaseAccess::addSpecificExplainInfo(ExplainTupleMaster *explainTuple, } + // get column retrieved + if (((ComTdbHbaseAccess *)tdb)->listOfFetchedColNames()){ + description += "column_retrieved: "; + appendListOfColumns(((ComTdbHbaseAccess *)tdb)->listOfFetchedColNames(),tdb,description); + } + // get predicate pushed down in Reverse Polish Notation for the AND / OR operators. + // could transform it standard notation for better readability, but good enough for now... + // could also evaluate the constants instead of hard coded ?, but good enough for now... + if (((ComTdbHbaseAccess *)tdb)->listOfHbaseFilterColNames()){ + description += "pushed_down_rpn: "; + appendPushedDownExpression(tdb, description); + } + // get pushed down predicate - /* + +/* // now get columns_retrieved description += "columns_retrieved: "; - char buf[27]; + //char buf[27]; //sprintf(buf, "%d ", retrievedCols().entries()); sprintf(buf, "%d ", getIndexDesc()->getIndexColumns().entries()); description += buf; - */ +*/ explainTuple->setDescription(description); @@ -2046,3 +2323,4 @@ ExplainTuple *ExeUtilHbaseCoProcAggr::addSpecificExplainInfo( return explainTuple; } + http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/1c5f243f/core/sql/generator/GenPreCode.cpp ---------------------------------------------------------------------- diff --git a/core/sql/generator/GenPreCode.cpp b/core/sql/generator/GenPreCode.cpp index c730c4e..28bff1c 100644 --- a/core/sql/generator/GenPreCode.cpp +++ b/core/sql/generator/GenPreCode.cpp @@ -11222,7 +11222,7 @@ short HbaseAccess::extractHbaseFilterPreds(Generator * generator, ValueIdSet &preds, ValueIdSet &newExePreds) { if (CmpCommon::getDefault(HBASE_FILTER_PREDS) == DF_OFF) - return 0; + return 0; // cannot push preds for aligned format row NABoolean isAlignedFormat = getTableDesc()->getNATable()->isAlignedFormat(getIndexDesc()); @@ -11274,6 +11274,485 @@ short HbaseAccess::extractHbaseFilterPreds(Generator * generator, return 0; } +//////////////////////////////////////////////////////////////////////////// +// To push down, the predicate must have the following form: +// xp:= <column> <op> <value-expr> +// xp:= <column> is not null (no support for hbase lookup) +// xp:= <column> is null (no support for hbase lookup) +// (xp:=<column> like <value-expr> not yet implemented) +// xp:=<xp> OR <xp> (not evaluated in isHbaseFilterPredV2, but by extractHbaseFilterPredV2) +// xp:=<xp> AND <xp>(not evaluated in isHbaseFilterPredV2, but by extractHbaseFilterPredV2) +// +// and all of the following conditions must be met: +// +// <column>: a base table or index column which can be serialized and belong to the table being scanned. +// serialized: either the column doesn't need encoding, like +// an unsigned integer, or the column +// was declared with the SERIALIZED option. +// it also must not be an added column with default non null. +// <op>: eq, ne, gt, ge, lt, le +// <value-expr>: an expression that only contains const or param values, and +// <value-expr>'s datatype is not a superset of <column>'s datatype. +// +///////////////////////////////////////////////////////////////////////////// +NABoolean HbaseAccess::isHbaseFilterPredV2(Generator * generator, ItemExpr * ie, + ValueId &colVID, ValueId &valueVID, + NAString &op) +{ + NABoolean foundBinary = FALSE; + NABoolean foundUnary = FALSE; + NABoolean hbaseLookupPred = FALSE; + NABoolean flipOp = FALSE; // set to TRUE when column is child(1) + + if (ie && + ((ie->getOperatorType() >= ITM_EQUAL) && + (ie->getOperatorType() <= ITM_GREATER_EQ))) //binary operator case + {//begin expression + ItemExpr * child0 = ie->child(0)->castToItemExpr(); + ItemExpr * child1 = ie->child(1)->castToItemExpr(); + + if ((ie->child(0)->getOperatorType() == ITM_BASECOLUMN) && + (NOT hasColReference(ie->child(1)))) + { + foundBinary = TRUE; + colVID = ie->child(0)->getValueId(); + valueVID = ie->child(1)->getValueId(); + } + else if ((ie->child(1)->getOperatorType() == ITM_BASECOLUMN) && + (NOT hasColReference(ie->child(0)))) + { + foundBinary = TRUE; + flipOp = TRUE; + colVID = ie->child(1)->getValueId(); + valueVID = ie->child(0)->getValueId(); + } + else if ((ie->child(0)->getOperatorType() == ITM_INDEXCOLUMN) && + (NOT hasColReference(ie->child(1)))) + { + foundBinary = TRUE; + colVID = ie->child(0)->getValueId(); + valueVID = ie->child(1)->getValueId(); + } + else if ((ie->child(1)->getOperatorType() == ITM_INDEXCOLUMN) && + (NOT hasColReference(ie->child(0)))) + { + foundBinary = TRUE; + flipOp = TRUE; + colVID = ie->child(1)->getValueId(); + valueVID = ie->child(0)->getValueId(); + } + else if ((ie->child(0)->getOperatorType() == ITM_REFERENCE) && + (NOT hasColReference(ie->child(1)))) + { + foundBinary = TRUE; + colVID = ie->child(0)->getValueId(); + valueVID = ie->child(1)->getValueId(); + } + else if ((ie->child(1)->getOperatorType() == ITM_REFERENCE) && + (NOT hasColReference(ie->child(0)))) + { + foundBinary = TRUE; + flipOp = TRUE; + colVID = ie->child(1)->getValueId(); + valueVID = ie->child(0)->getValueId(); + } + else if ((ie->child(0)->getOperatorType() == ITM_HBASE_COLUMN_LOOKUP) && + (NOT hasColReference(ie->child(1)))) + { + HbaseColumnLookup * hcl = (HbaseColumnLookup*)ie->child(0)->castToItemExpr(); + if (hcl->getValueId().getType().getTypeQualifier() == NA_CHARACTER_TYPE) + { + hbaseLookupPred = TRUE; + + ItemExpr * newCV = new(generator->wHeap()) ConstValue(hcl->hbaseCol()); + newCV = newCV->bindNode(generator->getBindWA()); + newCV = newCV->preCodeGen(generator); + + foundBinary = TRUE; + colVID = newCV->getValueId(); + valueVID = ie->child(1)->getValueId(); + } + } + else if ((ie->child(1)->getOperatorType() == ITM_HBASE_COLUMN_LOOKUP) && + (NOT hasColReference(ie->child(0)))) + { + HbaseColumnLookup * hcl = (HbaseColumnLookup*)ie->child(1)->castToItemExpr(); + if (hcl->getValueId().getType().getTypeQualifier() == NA_CHARACTER_TYPE) + { + hbaseLookupPred = TRUE; + + ItemExpr * newCV = new(generator->wHeap()) ConstValue(hcl->hbaseCol()); + newCV = newCV->bindNode(generator->getBindWA()); + newCV = newCV->preCodeGen(generator); + + foundBinary = TRUE; + flipOp = TRUE; + colVID = newCV->getValueId(); + valueVID = ie->child(0)->getValueId(); + } + } + }//end binary operators + else if (ie && ((ie->getOperatorType() == ITM_IS_NULL)||(ie->getOperatorType() == ITM_IS_NOT_NULL))){//check for unary operators + ItemExpr * child0 = ie->child(0)->castToItemExpr(); + if ((ie->child(0)->getOperatorType() == ITM_BASECOLUMN) || + (ie->child(0)->getOperatorType() == ITM_INDEXCOLUMN)|| + (ie->child(0)->getOperatorType() == ITM_REFERENCE)){ + foundUnary = TRUE; + colVID = ie->child(0)->getValueId(); + valueVID = NULL_VALUE_ID; + } + + }//end unary operators + + //check if found columns belong to table being scanned (so is not an input to the scan node) + if (foundBinary || foundUnary){ + ValueId dummyValueId; + if (getGroupAttr()->getCharacteristicInputs().referencesTheGivenValue(colVID,dummyValueId)){ + foundBinary=FALSE; + foundUnary=FALSE; + } + } + //check if not an added column with default non null + if ((foundBinary || foundUnary)&& (NOT hbaseLookupPred)){ + NAColumn * nac; + switch (colVID.getItemExpr()->getOperatorType()){ + case ITM_BASECOLUMN: + nac = ((BaseColumn*)colVID.getItemExpr())->getNAColumn(); + break; + case ITM_INDEXCOLUMN: + nac = ((IndexColumn*)colVID.getItemExpr())->getNAColumn(); + break; + default: + break; + } + if (nac && nac->isAddedColumn() && nac->getDefaultValue()){ + foundBinary=FALSE; + foundUnary=FALSE; + } + } + + if (foundBinary) + { + const NAType &colType = colVID.getType(); + const NAType &valueType = valueVID.getType(); + + NABoolean generateNarrow = FALSE; + if (NOT hbaseLookupPred) + { + generateNarrow = valueType.errorsCanOccur(colType); + if ((generateNarrow) || // value not a superset of column + (NOT columnEnabledForSerialization(colVID.getItemExpr()))) + foundBinary = FALSE; + } + + if (foundBinary) + { + if (colType.getTypeQualifier() == NA_CHARACTER_TYPE) + { + const CharType &charColType = (CharType&)colType; + const CharType &charValType = (CharType&)valueType; + + if ((charColType.isCaseinsensitive() || charValType.isCaseinsensitive()) || + (charColType.isUpshifted() || charValType.isUpshifted())) + foundBinary = FALSE; + } + else if (colType.getTypeQualifier() == NA_NUMERIC_TYPE) + { + const NumericType &numType = (NumericType&)colType; + const NumericType &valType = (NumericType&)valueType; + if (numType.isBigNum() || valType.isBigNum()) + foundBinary = FALSE; + } + } + + if (foundBinary) + { + if ((ie) && (((BiRelat*)ie)->addedForLikePred()) && + (valueVID.getItemExpr()->getOperatorType() == ITM_CONSTANT)) + { + // remove trailing '\0' characters since this is being pushed down to hbase. + ConstValue * cv = (ConstValue*)(valueVID.getItemExpr()); + char * cvv = (char*)cv->getConstValue(); + Lng32 len = cv->getStorageSize() - 1; + while ((len > 0) && (cvv[len] == '\0')) + len--; + + NAString newCVV(cvv, len+1); + + ItemExpr * newCV = new(generator->wHeap()) ConstValue(newCVV); + newCV = newCV->bindNode(generator->getBindWA()); + newCV = newCV->preCodeGen(generator); + valueVID = newCV->getValueId(); + } + + ItemExpr * castValue = NULL; + if (NOT hbaseLookupPred) + castValue = new(generator->wHeap()) Cast(valueVID.getItemExpr(), &colType); + else + { + castValue = new(generator->wHeap()) Cast(valueVID.getItemExpr(), &valueVID.getType()); + } + + if ((NOT hbaseLookupPred) && + (isEncodingNeededForSerialization(colVID.getItemExpr()))) + { + castValue = new(generator->wHeap()) CompEncode + (castValue, FALSE, -1, CollationInfo::Sort, TRUE, FALSE); + } + + castValue = castValue->bindNode(generator->getBindWA()); + castValue = castValue->preCodeGen(generator); + + valueVID = castValue->getValueId(); + + NAString nullType; + + if ((colType.supportsSQLnull()) || + (valueType.supportsSQLnull())) + { + nullType = "_NULL"; + } + else + { + nullType = ""; + } + + // append -NULL to the operator to signify the java code generating pushdown filters to handle NULL semantic logic + if (ie->getOperatorType() == ITM_EQUAL) + op = "EQUAL"+nullType; + else if (ie->getOperatorType() == ITM_NOT_EQUAL) + op = "NOT_EQUAL"+nullType; + else if (ie->getOperatorType() == ITM_LESS){ + if (flipOp) + op = "GREATER"+nullType; + else + op = "LESS"+nullType; + } + else if (ie->getOperatorType() == ITM_LESS_EQ){ + if (flipOp) + op = "GREATER_OR_EQUAL"+nullType; + else + op = "LESS_OR_EQUAL"+nullType; + }else if (ie->getOperatorType() == ITM_GREATER){ + if (flipOp) + op = "LESS"+nullType; + else + op = "GREATER"+nullType; + }else if (ie->getOperatorType() == ITM_GREATER_EQ){ + if (flipOp) + op = "LESS_OR_EQUAL"+nullType; + else + op = "GREATER_OR_EQUAL"+nullType; + }else + op = "NO_OP"+nullType; + } + } + if (foundUnary){ + const NAType &colType = colVID.getType(); + NAString nullType; + + if (colType.supportsSQLnull()) + { + nullType = "_NULL"; + } + else + { + nullType = ""; + } + if (ie->getOperatorType() == ITM_IS_NULL) + op = "IS_NULL"+nullType; + else if (ie->getOperatorType() == ITM_IS_NOT_NULL) + op = "IS_NOT_NULL"+nullType; + } + + return foundBinary || foundUnary; +} +short HbaseAccess::extractHbaseFilterPredsVX(Generator * generator, + ValueIdSet &preds, ValueIdSet &newExePreds){ + //separate the code that should not belong in the recursive function + if (CmpCommon::getDefault(HBASE_FILTER_PREDS) == DF_OFF) + return 0; + // check if initial (version 1) implementation + if (CmpCommon::getDefault(HBASE_FILTER_PREDS) == DF_MINIMUM) + return extractHbaseFilterPreds(generator,preds,newExePreds); + + // if here, we are DF_MEDIUM + // cannot push preds for aligned format row + NABoolean isAlignedFormat = getTableDesc()->getNATable()->isAlignedFormat(getIndexDesc()); + + if (isAlignedFormat) + return 0; + //recursive function call + opList_.insert("V2");//to instruct the java side that we are dealing with predicate pushdown V2 semantic, add "V2" marker + extractHbaseFilterPredsV2(generator,preds,newExePreds,FALSE,TRUE); + return 0; + +} + +// return true if successfull push down of node +NABoolean HbaseAccess::extractHbaseFilterPredsV2(Generator * generator, + ValueIdSet &preds, ValueIdSet &newExePreds, NABoolean checkOnly, NABoolean isFirstAndLayer ) +{ + + // the isFirstAndLayer is used to allow detecting top level predicate that can still be pushed to executor + int addedNode=0; + for (ValueId vid = preds.init(); + (preds.next(vid)); + preds.advance(vid)) + { + ItemExpr * ie = vid.getItemExpr(); + + // if it is AND operation, recurse through left and right children + if (ie->getOperatorType() == ITM_AND) + { + ValueIdSet leftPreds; + ValueIdSet rightPreds; + leftPreds += ie->child(0)->castToItemExpr()->getValueId(); + rightPreds += ie->child(1)->castToItemExpr()->getValueId(); + if (isFirstAndLayer){ + NABoolean leftOK = extractHbaseFilterPredsV2(generator, leftPreds, newExePreds,TRUE, TRUE); + NABoolean rightOK = extractHbaseFilterPredsV2(generator, rightPreds, newExePreds,TRUE, TRUE); + if (leftOK && rightOK){ + if (!checkOnly){ + extractHbaseFilterPredsV2(generator, leftPreds, newExePreds,FALSE, TRUE);//generate tree + extractHbaseFilterPredsV2(generator, rightPreds, newExePreds,FALSE, TRUE);//generate tree + opList_.insert("AND"); // insert an AND node since both side are OK to push down + if (addedNode>0)opList_.insert("AND"); // if it is not the first node addd to the push down, AND it with the rest + addedNode++; // we just pushed it down, so increase the node count pushed down. + } + if (preds.entries()==1) + return TRUE; + } else if (leftOK){ // if only left is OK to push down + if(!checkOnly){ + extractHbaseFilterPredsV2(generator, leftPreds, newExePreds,FALSE, TRUE);//generate left tree + newExePreds.insert(rightPreds); //make sure we add the right child to predicates that needs executor evalvaluation + if (addedNode>0)opList_.insert("AND"); // if it is not the first node addd to the push down, AND it with the rest + addedNode++; // we pushed down left side so mark it + } + if (preds.entries()==1) + return TRUE; + } else if (rightOK){// if only right is OK to push down + if(!checkOnly){ + extractHbaseFilterPredsV2(generator, rightPreds, newExePreds,FALSE, TRUE);//generate right tree + newExePreds.insert(leftPreds);//make sure we add the left child to predicates that needs executor evalvaluation + if (addedNode>0)opList_.insert("AND"); // if it is not the first node addd to the push down, AND it with the rest + addedNode++;// we pushed down right side so mark it + } + if (preds.entries()==1) + return TRUE; + } else{ + if(!checkOnly){ + newExePreds.insert(vid);// we pushed down nothing, make sure the whole node is evaluated by Executor + } + if (preds.entries()==1) + return FALSE; + + } + } + else{//if not first AND layer, both left and right must be pushable to get anything pushed + if(extractHbaseFilterPredsV2(generator, leftPreds, newExePreds, TRUE, FALSE)&& + extractHbaseFilterPredsV2(generator, rightPreds, newExePreds, TRUE, FALSE)){// both left and right child must match + if(!checkOnly){ + extractHbaseFilterPredsV2(generator, leftPreds, newExePreds, FALSE, FALSE);//generate tree + extractHbaseFilterPredsV2(generator, rightPreds, newExePreds, FALSE, FALSE);//generate tree + opList_.insert("AND"); + } + if (preds.entries()==1) + return TRUE; + + } + else{ + if(!checkOnly){ + newExePreds.insert(vid); + } + if (preds.entries()==1) + return FALSE; + } + } + continue; + + // the OR case is easier, as we don t have the case of top level expression that can still be pushed to executor + }//end if AND + else if(ie->getOperatorType() == ITM_OR){ + ValueIdSet leftPreds; + ValueIdSet rightPreds; + leftPreds += ie->child(0)->castToItemExpr()->getValueId(); + rightPreds += ie->child(1)->castToItemExpr()->getValueId(); + if (isFirstAndLayer){ + NABoolean leftOK = extractHbaseFilterPredsV2(generator, leftPreds, newExePreds,TRUE, FALSE); + NABoolean rightOK = extractHbaseFilterPredsV2(generator, rightPreds, newExePreds,TRUE, FALSE); + if (leftOK && rightOK){ + if (!checkOnly){ + extractHbaseFilterPredsV2(generator, leftPreds, newExePreds,FALSE, FALSE);//generate tree + extractHbaseFilterPredsV2(generator, rightPreds, newExePreds,FALSE, FALSE);//generate tree + opList_.insert("OR"); // insert an OR node since both side are OK to push down + if (addedNode>0)opList_.insert("AND"); // if it is not the first node add to the push down, AND it with the rest + addedNode++; // we just pushed it down, so increase the node count pushed down. + } + if (preds.entries()==1) + return TRUE; + + } else{ + if(!checkOnly){ + newExePreds.insert(vid);// we pushed down nothing, make sure the whole node is evaluated by Executor + } + if (preds.entries()==1) + return FALSE; + } + + }else{//if not first AND layer, both left and right must be pushable to get anything pushed + if(extractHbaseFilterPredsV2(generator, leftPreds, newExePreds, TRUE, FALSE)&& + extractHbaseFilterPredsV2(generator, rightPreds, newExePreds, TRUE, FALSE)){// both left and right child must match + if(!checkOnly){ + extractHbaseFilterPredsV2(generator, leftPreds, newExePreds, FALSE, FALSE);//generate tree + extractHbaseFilterPredsV2(generator, rightPreds, newExePreds, FALSE, FALSE);//generate tree + opList_.insert("OR"); + } + if (preds.entries()==1) + return TRUE; + } + else{// if predicate cannot be pushed down + if(!checkOnly){ + newExePreds.insert(vid); + } + if (preds.entries()==1) + return FALSE; + } + } + + continue; + }//end if OR + + + + ValueId colVID; + ValueId valueVID; + + NAString op; + NABoolean isHFP = + isHbaseFilterPredV2(generator, ie, colVID, valueVID, op); + + if (isHFP && !checkOnly){// if pushable, push it + hbaseFilterColVIDlist_.insert(colVID); + if (valueVID != NULL_VALUE_ID) hbaseFilterValueVIDlist_.insert(valueVID);// don't insert valueID for unary operators. + opList_.insert(op); + if(isFirstAndLayer){ + if (addedNode>0)opList_.insert("AND"); // if it is not the first node add to the push down, AND it with the rest + addedNode++; // we just pushed it down, so increase the node count pushed down. + } + }else if (!checkOnly){//if not pushable, pass it for executor evaluation. + newExePreds.insert(vid); + } + if (preds.entries()==1){ + return isHFP; // if we are not on the first call level, where we can have multiple preds, exit returning the pushability + } + + } // end for + + return TRUE;//don't really care, means we are top level. +} + + void HbaseAccess::computeRetrievedCols() { GroupAttributes fakeGA; @@ -11337,6 +11816,38 @@ RelExpr * HbaseAccess::preCodeGen(Generator * generator, if (! FileScan::preCodeGen(generator,externalInputs,pulledNewInputs)) return NULL; + //compute isUnique: + NABoolean isUnique = FALSE; + if (listOfRangeRows_.entries() == 0) + { + if ((searchKey() && searchKey()->isUnique()) && + (listOfUniqueRows_.entries() == 0)) + isUnique = TRUE; + else if ((NOT (searchKey() && searchKey()->isUnique())) && + (listOfUniqueRows_.entries() == 1) && + (listOfUniqueRows_[0].rowIds_.entries() == 1)) + isUnique = TRUE; + } + + // executorPred() contains an ANDed list of predicates. + // if hbase filter preds are enabled, then extracts those preds from executorPred() + // which could be pushed down to hbase. + // Do this only for non-unique scan access. + ValueIdSet newExePreds; + ValueIdSet* originExePreds = new (generator->wHeap())ValueIdSet(executorPred()) ;//saved for futur nullable column check + + if (CmpCommon::getDefault(HBASE_FILTER_PREDS) != DF_MINIMUM){ // the check for V2 and above is moved up before calculating retrieved columns + if ((NOT isUnique) && + (extractHbaseFilterPredsVX(generator, executorPred(), newExePreds))) + return this; + + // if some filter preds were found, then initialize executor preds with new exe preds. + // newExePreds may be empty which means that all predicates were changed into + // hbase preds. In this case, nuke existing exe preds. + if (hbaseFilterColVIDlist_.entries() > 0) + setExecutorPredicates(newExePreds); + } + ValueIdSet colRefSet; computeRetrievedCols(); @@ -11383,6 +11894,7 @@ RelExpr * HbaseAccess::preCodeGen(Generator * generator, // first add all columns referenced in the executor pred. HbaseAccess::addReferenceFromVIDset(executorPred(), TRUE, TRUE, colRefSet); + HbaseAccess::addReferenceFromVIDset (getGroupAttr()->getCharacteristicOutputs(), TRUE, TRUE, colRefSet); @@ -11412,9 +11924,54 @@ RelExpr * HbaseAccess::preCodeGen(Generator * generator, } } - // add all the key columns. If values are missing in hbase, then atleast the key + // add key columns. If values are missing in hbase, then atleast the key // value is needed to retrieve a row. - HbaseAccess::addColReferenceFromVIDlist(getIndexDesc()->getIndexKey(), retColRefSet_); + //only if needed. If there is already a non nullable non added non nullable with default columns in the set, we should not need to add + //any other columns. + if (CmpCommon::getDefault(HBASE_FILTER_PREDS) == DF_MEDIUM){ //only enable column retrieval optimization with DF_MEDIUM + bool needAddingNonNullableColumn = true; //assume we need to add one non nullable column + for (ValueId vid = retColRefSet_.init();// look for each column in th eresult set if one match the criteria non null non added non nullable with default + retColRefSet_.next(vid); + retColRefSet_.advance(vid)) + { + if (originExePreds->isNotNullable(vid)){// it is non nullable + NAColumn * nac; + switch (vid.getItemExpr()->getOperatorType()){ + case ITM_BASECOLUMN: + nac = ((BaseColumn*)vid.getItemExpr())->getNAColumn(); + break; + case ITM_INDEXCOLUMN: + nac = ((IndexColumn*)vid.getItemExpr())->getNAColumn(); + break; + default: + break; + } + if (nac && !(nac->isAddedColumn() && nac->getDefaultValue())){//check if added and with default... notgood + needAddingNonNullableColumn = false; // we found one column meeting all criteria + break; + } + } + } + if (needAddingNonNullableColumn){ // ok now we need to add one key column that is not nullable + bool foundAtLeastOneKeyColumnNotNullable = false; + for(int i=getIndexDesc()->getIndexKey().entries()-1; i>=0;i--)// doing reverse search is making sure we are trying to avoid to use _SALT_ column + // because _SALT_ is physicaly the last column therefore we don't skip columns optimally if using _SALT_ column + { + ValueId vaId = getIndexDesc()->getIndexKey()[i]; + if ( (vaId.getItemExpr()->getOperatorType() == ITM_BASECOLUMN && !((BaseColumn*)vaId.getItemExpr())->getNAColumn()->getType()->supportsSQLnullPhysical())|| + (vaId.getItemExpr()->getOperatorType() == ITM_INDEXCOLUMN && !((IndexColumn*)vaId.getItemExpr())->getNAColumn()->getType()->supportsSQLnullPhysical()) + ){ //found good key column candidate? + HbaseAccess::addReferenceFromItemExprTree(vaId.getItemExpr(),TRUE,FALSE,retColRefSet_); // add it + foundAtLeastOneKeyColumnNotNullable = true; //tag we found it + break; // no need to look further + } + } + if (!foundAtLeastOneKeyColumnNotNullable){//oh well, did not find any key column non nullable, let s add all key columns + HbaseAccess::addColReferenceFromVIDlist(getIndexDesc()->getIndexKey(), retColRefSet_); + } + } + }else //end if DF_MEDIUM + HbaseAccess::addColReferenceFromVIDlist(getIndexDesc()->getIndexKey(), retColRefSet_); } if ((getMdamKeyPtr()) && @@ -11429,18 +11986,6 @@ RelExpr * HbaseAccess::preCodeGen(Generator * generator, // flag for both hive and hbase tables generator->setHdfsAccess(TRUE); - NABoolean isUnique = FALSE; - if (listOfRangeRows_.entries() == 0) - { - if ((searchKey() && searchKey()->isUnique()) && - (listOfUniqueRows_.entries() == 0)) - isUnique = TRUE; - else if ((NOT (searchKey() && searchKey()->isUnique())) && - (listOfUniqueRows_.entries() == 1) && - (listOfUniqueRows_[0].rowIds_.entries() == 1)) - isUnique = TRUE; - } - if (!isUnique) generator->oltOptInfo()->setMultipleRowsReturned(TRUE) ; @@ -11471,17 +12016,17 @@ RelExpr * HbaseAccess::preCodeGen(Generator * generator, // if hbase filter preds are enabled, then extracts those preds from executorPred() // which could be pushed down to hbase. // Do this only for non-unique scan access. - ValueIdSet newExePreds; - - if ((NOT isUnique) && - (extractHbaseFilterPreds(generator, executorPred(), newExePreds))) - return this; + if (CmpCommon::getDefault(HBASE_FILTER_PREDS) == DF_MINIMUM){ //keep the check for pushdown after column retrieval for pushdown V1. + if ((NOT isUnique) && + (extractHbaseFilterPreds(generator, executorPred(), newExePreds))) + return this; // if some filter preds were found, then initialize executor preds with new exe preds. // newExePreds may be empty which means that all predicates were changed into // hbase preds. In this case, nuke existing exe preds. if (hbaseFilterColVIDlist_.entries() > 0) - setExecutorPredicates(newExePreds); + setExecutorPredicates(newExePreds); + }//DF_MINIMUM snpType_ = SNP_NONE; DefaultToken tok = CmpCommon::getDefault(TRAF_TABLE_SNAPSHOT_SCAN); http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/1c5f243f/core/sql/generator/GenRelScan.cpp ---------------------------------------------------------------------- diff --git a/core/sql/generator/GenRelScan.cpp b/core/sql/generator/GenRelScan.cpp index 573873f..a57bf8e 100644 --- a/core/sql/generator/GenRelScan.cpp +++ b/core/sql/generator/GenRelScan.cpp @@ -2643,7 +2643,8 @@ short HbaseAccess::codeGen(Generator * generator) ExpTupleDesc::LONG_FORMAT); // [optional IN] target desc format work_cri_desc->setTupleDescriptor(hbaseFilterValTuppIndex, hbaseFilterValTupleDesc); - + } + if (!hbaseFilterColVIDlist_.isEmpty()){// with unary operator we can have column without value genListOfColNames(generator, getIndexDesc(), hbaseFilterColVIDlist_, hbaseFilterColNames); http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/1c5f243f/core/sql/optimizer/RelScan.h ---------------------------------------------------------------------- diff --git a/core/sql/optimizer/RelScan.h b/core/sql/optimizer/RelScan.h index 5fbaf7f..45c4a53 100644 --- a/core/sql/optimizer/RelScan.h +++ b/core/sql/optimizer/RelScan.h @@ -1453,6 +1453,16 @@ public: short extractHbaseFilterPreds(Generator * generator, ValueIdSet &preds, ValueIdSet &newExePreds); + + NABoolean isHbaseFilterPredV2(Generator * generator, ItemExpr * ie, + ValueId &colVID, ValueId &valueVID, + NAString &op); + + short extractHbaseFilterPredsVX(Generator * generator,ValueIdSet &preds, ValueIdSet &newExePreds); + + NABoolean extractHbaseFilterPredsV2(Generator * generator, ValueIdSet &preds, ValueIdSet &newExePreds, + NABoolean checkOnly, NABoolean isFirstAndLayer ); + NABoolean isSnapshotScanFeasible(LatestSnpSupportEnum snpNotSupported, char * tableName); http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/1c5f243f/core/sql/optimizer/ValueDesc.cpp ---------------------------------------------------------------------- diff --git a/core/sql/optimizer/ValueDesc.cpp b/core/sql/optimizer/ValueDesc.cpp index a23d2c9..2e2b424 100644 --- a/core/sql/optimizer/ValueDesc.cpp +++ b/core/sql/optimizer/ValueDesc.cpp @@ -3111,7 +3111,11 @@ void ValueIdSet::replaceVEGExpressions if (iePtr != exprId.getItemExpr()) // a replacement was done { subtractElement(exprId); // remove existing ValueId - newExpr += iePtr->getValueId(); // replace with a new one + //insert new expression(s) + if (iePtr->getOperatorType() == ITM_AND) + iePtr->convertToValueIdSet(newExpr, NULL, ITM_AND, FALSE, FALSE); + else + newExpr += iePtr->getValueId(); // replace with a new one } } else // delete the ValueId of the VEGPredicate/VEGReference from the set @@ -6327,6 +6331,7 @@ ValueIdSet& ValueIdSet::intersectSetDeep(const ValueIdSet & v) return *this; } + // -------------------------------------------------------------------- // return true iff ValueIdSet has predicates that guarantee // that opd is not nullable http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/1c5f243f/core/sql/regress/executor/EXPECTED140 ---------------------------------------------------------------------- diff --git a/core/sql/regress/executor/EXPECTED140 b/core/sql/regress/executor/EXPECTED140 new file mode 100644 index 0000000..7af0f17 --- /dev/null +++ b/core/sql/regress/executor/EXPECTED140 @@ -0,0 +1,911 @@ +>>obey TEST140(ddl); +>>create table t140helper (a int not null, primary key(a)); + +--- SQL operation complete. +>>insert into t140helper values(1); + +--- 1 row(s) inserted. +>>create table t140 (uniq int not null, uniq2 int not null , a int not null, b int not null, ++> c int not null, an int, bn int, cn int, d varchar(10), primary key (uniq,uniq2)) ; + +--- SQL operation complete. +>>create table t140b (uniq int not null, uniq2 int not null , a int not null, b int not null, ++> c int not null, an int, bn int, cn int, d varchar(10), primary key (uniq,uniq2)) salt using 2 partitions on (uniq,uniq2) ; + +--- SQL operation complete. +>> +>>upsert using load ++>into t140 ++> select x1,x1,10*x1,100*x1,1000*x1,10*x1+1,100*x1+1, 1000*x1+1, 'aaa'||cast(x1 as varchar(10)) ++> ++> from t140helper ++>transpose 0,1,2,3,4,5,6,7,8,9 as x1; + +--- 10 row(s) inserted. +>> +>>insert into t140 values(11,11,12,102,1002,13,103,1003,'nullabs11'); + +--- 1 row(s) inserted. +>>insert into t140 values(12,12,22,202,2002,NULL,NULL,2003,'nullabs12'); + +--- 1 row(s) inserted. +>>insert into t140 values(13,13,32,302,3002,33,303,3003,'nullupd13'); + +--- 1 row(s) inserted. +>>update t140 set an = null, bn=null where uniq=13; + +--- 1 row(s) updated. +>>insert into t140 values(14,14,42,402,4002,43,403,4003,NULL); + +--- 1 row(s) inserted. +>>insert into t140 values(15,15,52,502,5002,53,503,5003,'asd'); + +--- 1 row(s) inserted. +>>update t140 set d = null where uniq = 15; + +--- 1 row(s) updated. +>> +>>obey TEST140(run); +>>-- test returned rows with or without adding key column and test of all pushdown functions with null or non null column +>>-- only one column retrieved +>>explain select a from t140 where b>500; + +------------------------------------------------------------------ PLAN SUMMARY +MODULE_NAME .............. DYNAMICALLY COMPILED +STATEMENT_NAME ........... NOT NAMED +PLAN_ID .................. 212318881178144109 +ROWS_OUT ................ 33 +EST_TOTAL_COST ........... 0.05 +STATEMENT ................ select a from t140 where b>500; + + +------------------------------------------------------------------ NODE LISTING +ROOT ====================================== SEQ_NO 2 ONLY CHILD 1 +REQUESTS_IN .............. 1 +ROWS_OUT ................ 33 +EST_OPER_COST ............ 0 +EST_TOTAL_COST ........... 0.05 +DESCRIPTION + max_card_est .......... 50 + fragment_id ............ 0 + parent_frag ............ (none) + fragment_type .......... master + statement_index ........ 0 + affinity_value ......... 0 + max_max_cardinality ... 33 + total_overflow_size .... 0.00 KB + xn_access_mode ......... read_only + xn_autoabort_interval 0 + auto_query_retry ....... enabled + plan_version ....... 2,600 + embedded_arkcmp ........ used + IS_SQLCI ............... ON + LDAP_USERNAME + TARGET_CODE ............ DEBUG + TARGET_CPU_FREQUENCY 199 + TARGET_IO_SEEK_TIME .... 0.02 + TARGET_IO_SEQ_READ_RATE 2.5 + TARGET_MSG_LOCAL_RATE 10 + TARGET_MSG_LOCAL_TIME 0.01 + TARGET_MSG_REMOTE_RAT 100 + TARGET_MSG_REMOTE_TIME 0.01 + ARKCMP_FAKE_HW ......... ON + SKIP_METADATA_VIEWS .... ON + DEF_NUM_SMP_CPUS ....... 2 + MAX_ESPS_PER_CPU_PER_OP 1 + DEF_NUM_NODES_IN_ACTIVE 1 + POS_ALLOW_NON_PK_TABLES ON + MODE_SEABASE ........... ON + SEABASE_VOLATILE_TABLES ON + HBASE_ASYNC_DROP_TABLE OFF + HBASE_SERIALIZATION .... ON + HBASE_FILTER_PREDS ..... 2 + TRAF_ALIGNED_ROW_FORMAT OFF + TRAF_INDEX_CREATE_OPT ON + SCHEMA ................. TRAFODION.SCH + GENERATE_EXPLAIN ....... ON + ObjectUIDs ............. 5581172586932643718 + select_list ............ TRAFODION.SCH.T140.A + + +TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN +TABLE_NAME ............... T140 +REQUESTS_IN .............. 1 +ROWS_OUT ................ 33 +EST_OPER_COST ............ 0.05 +EST_TOTAL_COST ........... 0.05 +DESCRIPTION + max_card_est .......... 50 + fragment_id ............ 0 + parent_frag ............ (none) + fragment_type .......... master + scan_type .............. subset scan of table TRAFODION.SCH.T140 + object_type ............ Trafodion + columns ................ all + begin_keys(incl) + end_keys(incl) + cache_size ........... 100 + probes ................. 1 + rows_accessed ........ 100 + column_retrieved ....... #1:3 + pushed_down_rpn ........ (#1:4>?) + key_columns ............ UNIQ, UNIQ2 + +--- SQL operation complete. +>>select a from t140 where b>=500; + +A +----------- + + 50 + 60 + 70 + 80 + 90 + 52 + +--- 6 row(s) selected. +>>--verify that the column retrieved for null handling is not the _SALT_ for optimization of skips, salt being the last physical column +>>explain select an from t140b where b<=200; + +------------------------------------------------------------------ PLAN SUMMARY +MODULE_NAME .............. DYNAMICALLY COMPILED +STATEMENT_NAME ........... NOT NAMED +PLAN_ID .................. 212318881179605475 +ROWS_OUT ................ 33 +EST_TOTAL_COST ........... 0.05 +STATEMENT ................ select an from t140b where b<=200; + + +------------------------------------------------------------------ NODE LISTING +ROOT ====================================== SEQ_NO 2 ONLY CHILD 1 +REQUESTS_IN .............. 1 +ROWS_OUT ................ 33 +EST_OPER_COST ............ 0 +EST_TOTAL_COST ........... 0.05 +DESCRIPTION + max_card_est .......... 75 + fragment_id ............ 0 + parent_frag ............ (none) + fragment_type .......... master + statement_index ........ 0 + affinity_value ......... 0 + max_max_cardinality ... 33 + total_overflow_size .... 0.00 KB + xn_access_mode ......... read_only + xn_autoabort_interval 0 + auto_query_retry ....... enabled + plan_version ....... 2,600 + embedded_arkcmp ........ used + IS_SQLCI ............... ON + LDAP_USERNAME + TARGET_CODE ............ DEBUG + TARGET_CPU_FREQUENCY 199 + TARGET_IO_SEEK_TIME .... 0.02 + TARGET_IO_SEQ_READ_RATE 2.5 + TARGET_MSG_LOCAL_RATE 10 + TARGET_MSG_LOCAL_TIME 0.01 + TARGET_MSG_REMOTE_RAT 100 + TARGET_MSG_REMOTE_TIME 0.01 + ARKCMP_FAKE_HW ......... ON + SKIP_METADATA_VIEWS .... ON + DEF_NUM_SMP_CPUS ....... 2 + MAX_ESPS_PER_CPU_PER_OP 1 + DEF_NUM_NODES_IN_ACTIVE 1 + POS_ALLOW_NON_PK_TABLES ON + MODE_SEABASE ........... ON + SEABASE_VOLATILE_TABLES ON + HBASE_ASYNC_DROP_TABLE OFF + HBASE_SERIALIZATION .... ON + HBASE_FILTER_PREDS ..... 2 + TRAF_ALIGNED_ROW_FORMAT OFF + TRAF_INDEX_CREATE_OPT ON + SCHEMA ................. TRAFODION.SCH + GENERATE_EXPLAIN ....... ON + ObjectUIDs ............. 5581172586932643812 + select_list ............ TRAFODION.SCH.T140B.AN + + +TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN +TABLE_NAME ............... T140B +REQUESTS_IN .............. 1 +ROWS_OUT ................ 33 +EST_OPER_COST ............ 0.05 +EST_TOTAL_COST ........... 0.05 +DESCRIPTION + max_card_est .......... 75 + fragment_id ............ 0 + parent_frag ............ (none) + fragment_type .......... master + scan_type .............. subset scan of table TRAFODION.SCH.T140B + object_type ............ Trafodion + columns ................ all + begin_keys(incl) + end_keys(incl) + cache_size ........... 100 + probes ................. 1 + rows_accessed ........ 100 + column_retrieved ....... #1:2,#1:6 + pushed_down_rpn ........ (#1:4<=?) + key_columns ............ _SALT_, UNIQ, UNIQ2 + +--- SQL operation complete. +>>-- we should have 2 columns retrieved since an is nullable +>>explain select an from t140 where b<=200; + +------------------------------------------------------------------ PLAN SUMMARY +MODULE_NAME .............. DYNAMICALLY COMPILED +STATEMENT_NAME ........... NOT NAMED +PLAN_ID .................. 212318881179745708 +ROWS_OUT ................ 33 +EST_TOTAL_COST ........... 0.05 +STATEMENT ................ select an from t140 where b<=200; + + +------------------------------------------------------------------ NODE LISTING +ROOT ====================================== SEQ_NO 2 ONLY CHILD 1 +REQUESTS_IN .............. 1 +ROWS_OUT ................ 33 +EST_OPER_COST ............ 0 +EST_TOTAL_COST ........... 0.05 +DESCRIPTION + max_card_est .......... 75 + fragment_id ............ 0 + parent_frag ............ (none) + fragment_type .......... master + statement_index ........ 0 + affinity_value ......... 0 + max_max_cardinality ... 33 + total_overflow_size .... 0.00 KB + xn_access_mode ......... read_only + xn_autoabort_interval 0 + auto_query_retry ....... enabled + plan_version ....... 2,600 + embedded_arkcmp ........ used + IS_SQLCI ............... ON + LDAP_USERNAME + TARGET_CODE ............ DEBUG + TARGET_CPU_FREQUENCY 199 + TARGET_IO_SEEK_TIME .... 0.02 + TARGET_IO_SEQ_READ_RATE 2.5 + TARGET_MSG_LOCAL_RATE 10 + TARGET_MSG_LOCAL_TIME 0.01 + TARGET_MSG_REMOTE_RAT 100 + TARGET_MSG_REMOTE_TIME 0.01 + ARKCMP_FAKE_HW ......... ON + SKIP_METADATA_VIEWS .... ON + DEF_NUM_SMP_CPUS ....... 2 + MAX_ESPS_PER_CPU_PER_OP 1 + DEF_NUM_NODES_IN_ACTIVE 1 + POS_ALLOW_NON_PK_TABLES ON + MODE_SEABASE ........... ON + SEABASE_VOLATILE_TABLES ON + HBASE_ASYNC_DROP_TABLE OFF + HBASE_SERIALIZATION .... ON + HBASE_FILTER_PREDS ..... 2 + TRAF_ALIGNED_ROW_FORMAT OFF + TRAF_INDEX_CREATE_OPT ON + SCHEMA ................. TRAFODION.SCH + GENERATE_EXPLAIN ....... ON + ObjectUIDs ............. 5581172586932643718 + select_list ............ TRAFODION.SCH.T140.AN + + +TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN +TABLE_NAME ............... T140 +REQUESTS_IN .............. 1 +ROWS_OUT ................ 33 +EST_OPER_COST ............ 0.05 +EST_TOTAL_COST ........... 0.05 +DESCRIPTION + max_card_est .......... 75 + fragment_id ............ 0 + parent_frag ............ (none) + fragment_type .......... master + scan_type .............. subset scan of table TRAFODION.SCH.T140 + object_type ............ Trafodion + columns ................ all + begin_keys(incl) + end_keys(incl) + cache_size ........... 100 + probes ................. 1 + rows_accessed ........ 100 + column_retrieved ....... #1:2,#1:6 + pushed_down_rpn ........ (#1:4<=?) + key_columns ............ UNIQ, UNIQ2 + +--- SQL operation complete. +>>select an from t140 where b<=200; + +AN +----------- + + 1 + 11 + 21 + 13 + +--- 4 row(s) selected. +>>-- should not get back 2 column, only one since predicate says an is not null +>>explain select an from t140 where b=200 and an is not null; + +------------------------------------------------------------------ PLAN SUMMARY +MODULE_NAME .............. DYNAMICALLY COMPILED +STATEMENT_NAME ........... NOT NAMED +PLAN_ID .................. 212318881179866410 +ROWS_OUT ................ 10 +EST_TOTAL_COST ........... 0.05 +STATEMENT ................ select an from t140 where b=200 and an is not null; + + +------------------------------------------------------------------ NODE LISTING +ROOT ====================================== SEQ_NO 2 ONLY CHILD 1 +REQUESTS_IN .............. 1 +ROWS_OUT ................ 10 +EST_OPER_COST ............ 0 +EST_TOTAL_COST ........... 0.05 +DESCRIPTION + max_card_est .......... 99 + fragment_id ............ 0 + parent_frag ............ (none) + fragment_type .......... master + statement_index ........ 0 + affinity_value ......... 0 + max_max_cardinality ... 10 + total_overflow_size .... 0.00 KB + xn_access_mode ......... read_only + xn_autoabort_interval 0 + auto_query_retry ....... enabled + plan_version ....... 2,600 + embedded_arkcmp ........ used + IS_SQLCI ............... ON + LDAP_USERNAME + TARGET_CODE ............ DEBUG + TARGET_CPU_FREQUENCY 199 + TARGET_IO_SEEK_TIME .... 0.02 + TARGET_IO_SEQ_READ_RATE 2.5 + TARGET_MSG_LOCAL_RATE 10 + TARGET_MSG_LOCAL_TIME 0.01 + TARGET_MSG_REMOTE_RAT 100 + TARGET_MSG_REMOTE_TIME 0.01 + ARKCMP_FAKE_HW ......... ON + SKIP_METADATA_VIEWS .... ON + DEF_NUM_SMP_CPUS ....... 2 + MAX_ESPS_PER_CPU_PER_OP 1 + DEF_NUM_NODES_IN_ACTIVE 1 + POS_ALLOW_NON_PK_TABLES ON + MODE_SEABASE ........... ON + SEABASE_VOLATILE_TABLES ON + HBASE_ASYNC_DROP_TABLE OFF + HBASE_SERIALIZATION .... ON + HBASE_FILTER_PREDS ..... 2 + TRAF_ALIGNED_ROW_FORMAT OFF + TRAF_INDEX_CREATE_OPT ON + SCHEMA ................. TRAFODION.SCH + GENERATE_EXPLAIN ....... ON + ObjectUIDs ............. 5581172586932643718 + select_list ............ TRAFODION.SCH.T140.AN + input_variables ........ %(200) + + +TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN +TABLE_NAME ............... T140 +REQUESTS_IN .............. 1 +ROWS_OUT ................ 10 +EST_OPER_COST ............ 0.05 +EST_TOTAL_COST ........... 0.05 +DESCRIPTION + max_card_est .......... 99 + fragment_id ............ 0 + parent_frag ............ (none) + fragment_type .......... master + scan_type .............. subset scan of table TRAFODION.SCH.T140 + object_type ............ Trafodion + columns ................ all + begin_keys(incl) + end_keys(incl) + cache_size ........... 100 + probes ................. 1 + rows_accessed ........ 100 + column_retrieved ....... #1:6 + pushed_down_rpn ........ (#1:4=?)(#1:6 is_not_null.)AND + key_columns ............ UNIQ, UNIQ2 + +--- SQL operation complete. +>>select an from t140 where b=200 and an is not null; + +AN +----------- + + 21 + +--- 1 row(s) selected. +>>-- should only get 2 column back since a is not null, no need to add key column +>>explain select an, a from t140 where b!=500; + +------------------------------------------------------------------ PLAN SUMMARY +MODULE_NAME .............. DYNAMICALLY COMPILED +STATEMENT_NAME ........... NOT NAMED +PLAN_ID .................. 212318881179985030 +ROWS_OUT ................ 67 +EST_TOTAL_COST ........... 0.05 +STATEMENT ................ select an, a from t140 where b!=500; + + +------------------------------------------------------------------ NODE LISTING +ROOT ====================================== SEQ_NO 2 ONLY CHILD 1 +REQUESTS_IN .............. 1 +ROWS_OUT ................ 67 +EST_OPER_COST ............ 0 +EST_TOTAL_COST ........... 0.05 +DESCRIPTION + max_card_est ......... 100 + fragment_id ............ 0 + parent_frag ............ (none) + fragment_type .......... master + statement_index ........ 0 + affinity_value ......... 0 + max_max_cardinality ... 67 + total_overflow_size .... 0.00 KB + xn_access_mode ......... read_only + xn_autoabort_interval 0 + auto_query_retry ....... enabled + plan_version ....... 2,600 + embedded_arkcmp ........ used + IS_SQLCI ............... ON + LDAP_USERNAME + TARGET_CODE ............ DEBUG + TARGET_CPU_FREQUENCY 199 + TARGET_IO_SEEK_TIME .... 0.02 + TARGET_IO_SEQ_READ_RATE 2.5 + TARGET_MSG_LOCAL_RATE 10 + TARGET_MSG_LOCAL_TIME 0.01 + TARGET_MSG_REMOTE_RAT 100 + TARGET_MSG_REMOTE_TIME 0.01 + ARKCMP_FAKE_HW ......... ON + SKIP_METADATA_VIEWS .... ON + DEF_NUM_SMP_CPUS ....... 2 + MAX_ESPS_PER_CPU_PER_OP 1 + DEF_NUM_NODES_IN_ACTIVE 1 + POS_ALLOW_NON_PK_TABLES ON + MODE_SEABASE ........... ON + SEABASE_VOLATILE_TABLES ON + HBASE_ASYNC_DROP_TABLE OFF + HBASE_SERIALIZATION .... ON + HBASE_FILTER_PREDS ..... 2 + TRAF_ALIGNED_ROW_FORMAT OFF + TRAF_INDEX_CREATE_OPT ON + SCHEMA ................. TRAFODION.SCH + GENERATE_EXPLAIN ....... ON + ObjectUIDs ............. 5581172586932643718 + select_list ............ TRAFODION.SCH.T140.AN, TRAFODION.SCH.T140.A + + +TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN +TABLE_NAME ............... T140 +REQUESTS_IN .............. 1 +ROWS_OUT ................ 67 +EST_OPER_COST ............ 0.05 +EST_TOTAL_COST ........... 0.05 +DESCRIPTION + max_card_est ......... 100 + fragment_id ............ 0 + parent_frag ............ (none) + fragment_type .......... master + scan_type .............. subset scan of table TRAFODION.SCH.T140 + object_type ............ Trafodion + columns ................ all + begin_keys(incl) + end_keys(incl) + cache_size ........... 100 + probes ................. 1 + rows_accessed ........ 100 + column_retrieved ....... #1:3,#1:6 + pushed_down_rpn ........ (#1:4<?)(#1:4>?)OR + key_columns ............ UNIQ, UNIQ2 + +--- SQL operation complete. +>>select an, a from t140 where b!=500; + +AN A +----------- ----------- + + 1 0 + 11 10 + 21 20 + 31 30 + 41 40 + 61 60 + 71 70 + 81 80 + 91 90 + 13 12 + ? 22 + ? 32 + 43 42 + 53 52 + +--- 14 row(s) selected. +>>-- see if we handle null logic correctly +>>select a from t140 where bn>=501; + +A +----------- + + 50 + 60 + 70 + 80 + 90 + 52 + +--- 6 row(s) selected. +>>select an from t140 where bn<=201; + +AN +----------- + + 1 + 11 + 21 + 13 + +--- 4 row(s) selected. +>>explain select an from t140 where bn=201 and an is not null; + +------------------------------------------------------------------ PLAN SUMMARY +MODULE_NAME .............. DYNAMICALLY COMPILED +STATEMENT_NAME ........... NOT NAMED +PLAN_ID .................. 212318881180232505 +ROWS_OUT ................ 10 +EST_TOTAL_COST ........... 0.05 +STATEMENT ................ select an from t140 where bn=201 and an is not null; + + +------------------------------------------------------------------ NODE LISTING +ROOT ====================================== SEQ_NO 2 ONLY CHILD 1 +REQUESTS_IN .............. 1 +ROWS_OUT ................ 10 +EST_OPER_COST ............ 0 +EST_TOTAL_COST ........... 0.05 +DESCRIPTION + max_card_est .......... 99 + fragment_id ............ 0 + parent_frag ............ (none) + fragment_type .......... master + statement_index ........ 0 + affinity_value ......... 0 + max_max_cardinality ... 10 + total_overflow_size .... 0.00 KB + xn_access_mode ......... read_only + xn_autoabort_interval 0 + auto_query_retry ....... enabled + plan_version ....... 2,600 + embedded_arkcmp ........ used + IS_SQLCI ............... ON + LDAP_USERNAME + TARGET_CODE ............ DEBUG + TARGET_CPU_FREQUENCY 199 + TARGET_IO_SEEK_TIME .... 0.02 + TARGET_IO_SEQ_READ_RATE 2.5 + TARGET_MSG_LOCAL_RATE 10 + TARGET_MSG_LOCAL_TIME 0.01 + TARGET_MSG_REMOTE_RAT 100 + TARGET_MSG_REMOTE_TIME 0.01 + ARKCMP_FAKE_HW ......... ON + SKIP_METADATA_VIEWS .... ON + DEF_NUM_SMP_CPUS ....... 2 + MAX_ESPS_PER_CPU_PER_OP 1 + DEF_NUM_NODES_IN_ACTIVE 1 + POS_ALLOW_NON_PK_TABLES ON + MODE_SEABASE ........... ON + SEABASE_VOLATILE_TABLES ON + HBASE_ASYNC_DROP_TABLE OFF + HBASE_SERIALIZATION .... ON + HBASE_FILTER_PREDS ..... 2 + TRAF_ALIGNED_ROW_FORMAT OFF + TRAF_INDEX_CREATE_OPT ON + SCHEMA ................. TRAFODION.SCH + GENERATE_EXPLAIN ....... ON + ObjectUIDs ............. 5581172586932643718 + select_list ............ TRAFODION.SCH.T140.AN + input_variables ........ %(201) + + +TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN +TABLE_NAME ............... T140 +REQUESTS_IN .............. 1 +ROWS_OUT ................ 10 +EST_OPER_COST ............ 0.05 +EST_TOTAL_COST ........... 0.05 +DESCRIPTION + max_card_est .......... 99 + fragment_id ............ 0 + parent_frag ............ (none) + fragment_type .......... master + scan_type .............. subset scan of table TRAFODION.SCH.T140 + object_type ............ Trafodion + columns ................ all + begin_keys(incl) + end_keys(incl) + cache_size ........... 100 + probes ................. 1 + rows_accessed ........ 100 + column_retrieved ....... #1:6 + pushed_down_rpn ........ (#1:7=.?)(#1:6 is_not_null.)AND + key_columns ............ UNIQ, UNIQ2 + +--- SQL operation complete. +>>select an from t140 where bn=201 and an is not null; + +AN +----------- + + 21 + +--- 1 row(s) selected. +>>explain select an, a from t140 where bn!=501; + +------------------------------------------------------------------ PLAN SUMMARY +MODULE_NAME .............. DYNAMICALLY COMPILED +STATEMENT_NAME ........... NOT NAMED +PLAN_ID .................. 212318881180365492 +ROWS_OUT ................ 67 +EST_TOTAL_COST ........... 0.05 +STATEMENT ................ select an, a from t140 where bn!=501; + + +------------------------------------------------------------------ NODE LISTING +ROOT ====================================== SEQ_NO 2 ONLY CHILD 1 +REQUESTS_IN .............. 1 +ROWS_OUT ................ 67 +EST_OPER_COST ............ 0 +EST_TOTAL_COST ........... 0.05 +DESCRIPTION + max_card_est ......... 100 + fragment_id ............ 0 + parent_frag ............ (none) + fragment_type .......... master + statement_index ........ 0 + affinity_value ......... 0 + max_max_cardinality ... 67 + total_overflow_size .... 0.00 KB + xn_access_mode ......... read_only + xn_autoabort_interval 0 + auto_query_retry ....... enabled + plan_version ....... 2,600 + embedded_arkcmp ........ used + IS_SQLCI ............... ON + LDAP_USERNAME + TARGET_CODE ............ DEBUG + TARGET_CPU_FREQUENCY 199 + TARGET_IO_SEEK_TIME .... 0.02 + TARGET_IO_SEQ_READ_RATE 2.5 + TARGET_MSG_LOCAL_RATE 10 + TARGET_MSG_LOCAL_TIME 0.01 + TARGET_MSG_REMOTE_RAT 100 + TARGET_MSG_REMOTE_TIME 0.01 + ARKCMP_FAKE_HW ......... ON + SKIP_METADATA_VIEWS .... ON + DEF_NUM_SMP_CPUS ....... 2 + MAX_ESPS_PER_CPU_PER_OP 1 + DEF_NUM_NODES_IN_ACTIVE 1 + POS_ALLOW_NON_PK_TABLES ON + MODE_SEABASE ........... ON + SEABASE_VOLATILE_TABLES ON + HBASE_ASYNC_DROP_TABLE OFF + HBASE_SERIALIZATION .... ON + HBASE_FILTER_PREDS ..... 2 + TRAF_ALIGNED_ROW_FORMAT OFF + TRAF_INDEX_CREATE_OPT ON + SCHEMA ................. TRAFODION.SCH + GENERATE_EXPLAIN ....... ON + ObjectUIDs ............. 5581172586932643718 + select_list ............ TRAFODION.SCH.T140.AN, TRAFODION.SCH.T140.A + + +TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN +TABLE_NAME ............... T140 +REQUESTS_IN .............. 1 +ROWS_OUT ................ 67 +EST_OPER_COST ............ 0.05 +EST_TOTAL_COST ........... 0.05 +DESCRIPTION + max_card_est ......... 100 + fragment_id ............ 0 + parent_frag ............ (none) + fragment_type .......... master + scan_type .............. subset scan of table TRAFODION.SCH.T140 + object_type ............ Trafodion + columns ................ all + begin_keys(incl) + end_keys(incl) + cache_size ........... 100 + probes ................. 1 + rows_accessed ........ 100 + column_retrieved ....... #1:3,#1:6 + pushed_down_rpn ........ (#1:7<.?)(#1:7>.?)OR + key_columns ............ UNIQ, UNIQ2 + +--- SQL operation complete. +>>select an, a from t140 where bn!=501; + +AN A +----------- ----------- + + 1 0 + 11 10 + 21 20 + 31 30 + 41 40 + 61 60 + 71 70 + 81 80 + 91 90 + 13 12 + 43 42 + 53 52 + +--- 12 row(s) selected. +>>select a from t140 where an is null; + +A +----------- + + 22 + 32 + +--- 2 row(s) selected. +>>select a from t140 where an is not null; + +A +----------- + + 0 + 10 + 20 + 30 + 40 + 50 + 60 + 70 + 80 + 90 + 12 + 42 + 52 + +--- 13 row(s) selected. +>>select a from t140 where a between 20 and 40; + +A +----------- + + 20 + 30 + 40 + 22 + 32 + +--- 5 row(s) selected. +>>-- make sure that we only retrieve one column as an cannot be null in the result set. +>>explain select an from t140 where an between 20 and 40; + +------------------------------------------------------------------ PLAN SUMMARY +MODULE_NAME .............. DYNAMICALLY COMPILED +STATEMENT_NAME ........... NOT NAMED +PLAN_ID .................. 212318881180584307 +ROWS_OUT ................ 11 +EST_TOTAL_COST ........... 0.05 +STATEMENT ................ select an from t140 where an between 20 and 40; + + +------------------------------------------------------------------ NODE LISTING +ROOT ====================================== SEQ_NO 2 ONLY CHILD 1 +REQUESTS_IN .............. 1 +ROWS_OUT ................ 11 +EST_OPER_COST ............ 0 +EST_TOTAL_COST ........... 0.05 +DESCRIPTION + max_card_est .......... 75 + fragment_id ............ 0 + parent_frag ............ (none) + fragment_type .......... master + statement_index ........ 0 + affinity_value ......... 0 + max_max_cardinality ... 11 + total_overflow_size .... 0.00 KB + xn_access_mode ......... read_only + xn_autoabort_interval 0 + auto_query_retry ....... enabled + plan_version ....... 2,600 + embedded_arkcmp ........ used + IS_SQLCI ............... ON + LDAP_USERNAME + TARGET_CODE ............ DEBUG + TARGET_CPU_FREQUENCY 199 + TARGET_IO_SEEK_TIME .... 0.02 + TARGET_IO_SEQ_READ_RATE 2.5 + TARGET_MSG_LOCAL_RATE 10 + TARGET_MSG_LOCAL_TIME 0.01 + TARGET_MSG_REMOTE_RAT 100 + TARGET_MSG_REMOTE_TIME 0.01 + ARKCMP_FAKE_HW ......... ON + SKIP_METADATA_VIEWS .... ON + DEF_NUM_SMP_CPUS ....... 2 + MAX_ESPS_PER_CPU_PER_OP 1 + DEF_NUM_NODES_IN_ACTIVE 1 + POS_ALLOW_NON_PK_TABLES ON + MODE_SEABASE ........... ON + SEABASE_VOLATILE_TABLES ON + HBASE_ASYNC_DROP_TABLE OFF + HBASE_SERIALIZATION .... ON + HBASE_FILTER_PREDS ..... 2 + TRAF_ALIGNED_ROW_FORMAT OFF + TRAF_INDEX_CREATE_OPT ON + SCHEMA ................. TRAFODION.SCH + GENERATE_EXPLAIN ....... ON + ObjectUIDs ............. 5581172586932643718 + select_list ............ TRAFODION.SCH.T140.AN + + +TRAFODION_SCAN ============================ SEQ_NO 1 NO CHILDREN +TABLE_NAME ............... T140 +REQUESTS_IN .............. 1 +ROWS_OUT ................ 11 +EST_OPER_COST ............ 0.05 +EST_TOTAL_COST ........... 0.05 +DESCRIPTION + max_card_est .......... 75 + fragment_id ............ 0 + parent_frag ............ (none) + fragment_type .......... master + scan_type .............. subset scan of table TRAFODION.SCH.T140 + object_type ............ Trafodion + columns ................ all + begin_keys(incl) + end_keys(incl) + cache_size ........... 100 + probes ................. 1 + rows_accessed ........ 100 + column_retrieved ....... #1:6 + pushed_down_rpn ........ (#1:6>=.?)(#1:6<=.?)AND + key_columns ............ UNIQ, UNIQ2 + +--- SQL operation complete. +>>select an from t140 where an between 20 and 40; + +AN +----------- + + 21 + 31 + +--- 2 row(s) selected. +>>select an from t140 where an in (21,41,51,61,10); + +AN +----------- + + 21 + 41 + 51 + 61 + +--- 4 row(s) selected. +>> +>>obey TEST140(clnup); +>>drop table t140helper; + +--- SQL operation complete. +>>drop table t140; + +--- SQL operation complete. +>>drop table t140b; + +--- SQL operation complete. +>> +>>exit; + +End of MXCI Session + http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/1c5f243f/core/sql/regress/executor/FILTER140 ---------------------------------------------------------------------- diff --git a/core/sql/regress/executor/FILTER140 b/core/sql/regress/executor/FILTER140 new file mode 100755 index 0000000..0d32084 --- /dev/null +++ b/core/sql/regress/executor/FILTER140 @@ -0,0 +1,42 @@ +#! /bin/sh +# @@@ START COPYRIGHT @@@ +# +# Licensed to the Apache Software Foundation (ASF) under one +# or more contributor license agreements. See the NOTICE file +# distributed with this work for additional information +# regarding copyright ownership. The ASF licenses this file +# to you under the Apache License, Version 2.0 (the +# "License"); you may not use this file except in compliance +# with the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, +# software distributed under the License is distributed on an +# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +# KIND, either express or implied. See the License for the +# specific language governing permissions and limitations +# under the License. +# +# @@@ END COPYRIGHT @@@ + +# Like the pattern-masking of QACOMP, here we filter out such stuff as +# transaction id. Called by the runregr script before doing diff. + +fil=$1 +sed " +s/^\([ ]*affinity_value[ ]*[\.]*\) .*/affinity_value removed/g +s/^\([ ]*max_max_cardinality[ ]*[\.]*\) .*/max_max_cardinality removed/g +s/^\([ ]*PLAN_ID[ ]*[\.]*\) .*/\1 removed/g +s/^\([ ]*HBASE_PORT[ ]*[\.]*\) .*/ HBASE_PORT........ removed/g +s/^\([ ]*HBASE_SERVER[ ]*[\.]*\) .*/\1 removed/g +s/^\([ ]*HBASE_INTERFACE[ ]*[\.]*\) .*/\1 removed/g +s/^\([ ]*HBASE_SERIALIZATION[ ]*[\.]*\) .*/\1 removed/g +s/^\([ ]*HBASE_FILTER_PREDS[ ]*[\.]*\) .*/\1 removed/g +s/^\([ ]*TRAF_ALIGNED_ROW_FORMAT[ ]*[\.]*\) .*/\1 removed/g +s/^\([ ]*TRAF_INDEX_CREATE_OPT[ ]*[\.]*\) .*/\1 removed/g +s/^\([ ]*embedded_arkcmp[ ]*[\.]*\) .*/\1 removed/g +s/^\([ ]*EST_TOTAL_COST[ ]*[\.]*\) .*/\1 removed/g +s/^\([ ]*EST_OPER_COST[ ]*[\.]*\) .*/\1 removed/g +s/VOLATILE_SCHEMA_MXID[0-9]*/VOLATILE_SCHEMA_00/ +" $fil \ No newline at end of file http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/1c5f243f/core/sql/regress/executor/TEST140 ---------------------------------------------------------------------- diff --git a/core/sql/regress/executor/TEST140 b/core/sql/regress/executor/TEST140 new file mode 100644 index 0000000..8325db8 --- /dev/null +++ b/core/sql/regress/executor/TEST140 @@ -0,0 +1,96 @@ +-- ============================================================================ +-- Test: TEST0140 (Executor) +-- @@@ START COPYRIGHT @@@ +-- +-- Licensed to the Apache Software Foundation (ASF) under one +-- or more contributor license agreements. See the NOTICE file +-- distributed with this work for additional information +-- regarding copyright ownership. The ASF licenses this file +-- to you under the Apache License, Version 2.0 (the +-- "License"); you may not use this file except in compliance +-- with the License. You may obtain a copy of the License at +-- +-- http://www.apache.org/licenses/LICENSE-2.0 +-- +-- Unless required by applicable law or agreed to in writing, +-- software distributed under the License is distributed on an +-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +-- KIND, either express or implied. See the License for the +-- specific language governing permissions and limitations +-- under the License. +-- +-- @@@ END COPYRIGHT @@@ +-- +-- Functionality: Advanced predicate pushdown (V2) +-- Expected files: EXPECTED140 +-- Table created: t140 t140b t140helper +-- Limitations: +-- To do: +-- Revision history: +-- ============================================================================ +obey TEST140(clnup); +log LOG140 clear; +obey TEST140(ddl); +obey TEST140(run); +obey TEST140(clnup); +exit; + +?section clnup +drop table t140helper; +drop table t140; +drop table t140b; + +?section ddl +create table t140helper (a int not null, primary key(a)); +insert into t140helper values(1); +create table t140 (uniq int not null, uniq2 int not null , a int not null, b int not null, + c int not null, an int, bn int, cn int, d varchar(10), primary key (uniq,uniq2)) ; +create table t140b (uniq int not null, uniq2 int not null , a int not null, b int not null, + c int not null, an int, bn int, cn int, d varchar(10), primary key (uniq,uniq2)) salt using 2 partitions on (uniq,uniq2) ; + +upsert using load +into t140 + select x1,x1,10*x1,100*x1,1000*x1,10*x1+1,100*x1+1, 1000*x1+1, 'aaa'||cast(x1 as varchar(10)) + + from t140helper +transpose 0,1,2,3,4,5,6,7,8,9 as x1; + +insert into t140 values(11,11,12,102,1002,13,103,1003,'nullabs11'); +insert into t140 values(12,12,22,202,2002,NULL,NULL,2003,'nullabs12'); +insert into t140 values(13,13,32,302,3002,33,303,3003,'nullupd13'); +update t140 set an = null, bn=null where uniq=13; +insert into t140 values(14,14,42,402,4002,43,403,4003,NULL); +insert into t140 values(15,15,52,502,5002,53,503,5003,'asd'); +update t140 set d = null where uniq = 15; + +?section run +-- test returned rows with or without adding key column and test of all pushdown functions with null or non null column +-- only one column retrieved +explain select a from t140 where b>500; +select a from t140 where b>=500; +--verify that the column retrieved for null handling is not the _SALT_ for optimization of skips, salt being the last physical column +explain select an from t140b where b<=200; +-- we should have 2 columns retrieved since an is nullable +explain select an from t140 where b<=200; +select an from t140 where b<=200; +-- should not get back 2 column, only one since predicate says an is not null +explain select an from t140 where b=200 and an is not null; +select an from t140 where b=200 and an is not null; +-- should only get 2 column back since a is not null, no need to add key column +explain select an, a from t140 where b!=500; +select an, a from t140 where b!=500; +-- see if we handle null logic correctly +select a from t140 where bn>=501; +select an from t140 where bn<=201; +explain select an from t140 where bn=201 and an is not null; +select an from t140 where bn=201 and an is not null; +explain select an, a from t140 where bn!=501; +select an, a from t140 where bn!=501; +select a from t140 where an is null; +select a from t140 where an is not null; +select a from t140 where a between 20 and 40; +-- make sure that we only retrieve one column as an cannot be null in the result set. +explain select an from t140 where an between 20 and 40; +select an from t140 where an between 20 and 40; +select an from t140 where an in (21,41,51,61,10); +
