You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by hz...@apache.org on 2016/01/22 02:08:22 UTC
[03/15] incubator-trafodion git commit: 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
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 <er...@esgyn.com>
Authored: Thu Jan 7 01:25:54 2016 +0000
Committer: Eric Owhadi <er...@esgyn.com>
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);
+