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 2015/12/11 00:23:11 UTC
[1/7] incubator-trafodion git commit: [TRAFODION-1695] Optimize ORDER
BY and GROUP BY with salt and divisioning
Repository: incubator-trafodion
Updated Branches:
refs/heads/master 99091d7a3 -> d61ac8a1c
[TRAFODION-1695] Optimize ORDER BY and GROUP BY with salt and divisioning
For queries that access only a single salt bucket or a single division,
we should be able to produce the rows of the table in order of the
primary key. This change adds some optimizations to do that.
Predicates on computed columns are used to find queries that access only
a single salt or division value. The method to match actual with required
orders recognizes these predicates and also stores them in the group
attributes, so that later checks again recognize the optimization.
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/680900cd
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/680900cd
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/680900cd
Branch: refs/heads/master
Commit: 680900cd6e2f5daa959b449d12cda3aa7d8a7853
Parents: 07b73d9
Author: Hans Zeller <ze...@dev02.trafodion.org>
Authored: Tue Sep 8 20:48:05 2015 +0000
Committer: Hans Zeller <hz...@apache.org>
Committed: Wed Dec 9 02:47:37 2015 +0000
----------------------------------------------------------------------
core/sql/common/OperTypeEnum.h | 23 +--
core/sql/optimizer/GroupAttr.cpp | 153 ++++++++++++++++++++
core/sql/optimizer/GroupAttr.h | 4 +
core/sql/optimizer/ImplRule.cpp | 16 +--
core/sql/optimizer/ItemConstr.h | 39 ++++++
core/sql/optimizer/ItemExpr.cpp | 36 ++++-
core/sql/optimizer/ItemExpr.h | 4 +-
core/sql/optimizer/OptPhysRelExpr.cpp | 3 +-
core/sql/optimizer/PhyProp.cpp | 2 +-
core/sql/optimizer/ValueDesc.cpp | 194 ++++++++++++++++----------
core/sql/optimizer/ValueDesc.h | 14 +-
core/sql/regress/compGeneral/EXPECTED071 | 47 +++++++
core/sql/regress/compGeneral/TEST071 | 22 +++
core/sql/regress/seabase/EXPECTED010 | 84 +++++++++++
core/sql/regress/seabase/TEST010 | 4 +
core/sql/regress/seabase/TEST014 | 1 +
16 files changed, 548 insertions(+), 98 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/680900cd/core/sql/common/OperTypeEnum.h
----------------------------------------------------------------------
diff --git a/core/sql/common/OperTypeEnum.h b/core/sql/common/OperTypeEnum.h
index d6bb8cf..6e5d2ae 100644
--- a/core/sql/common/OperTypeEnum.h
+++ b/core/sql/common/OperTypeEnum.h
@@ -715,27 +715,28 @@ enum OperatorTypeEnum {
ITM_REF_CONSTRAINT = 2503,
ITM_UNIQUE_OPT_CONSTRAINT = 2504,
ITM_FUNC_DEPEND_CONSTRAINT = 2505,
- ITM_REF_OPT_CONSTRAINT = 2506,
- ITM_COMP_REF_OPT_CONSTRAINT = 2507,
+ ITM_CHECK_OPT_CONSTRAINT,
+ ITM_REF_OPT_CONSTRAINT,
+ ITM_COMP_REF_OPT_CONSTRAINT,
// lookup a column in a native hbase table being accessed in row format
- ITM_HBASE_COLUMN_LOOKUP = 2508,
+ ITM_HBASE_COLUMN_LOOKUP,
// display hbase columns being accessed in row format
- ITM_HBASE_COLUMNS_DISPLAY = 2509,
+ ITM_HBASE_COLUMNS_DISPLAY,
- ITM_HBASE_COLUMN_CREATE = 2510,
+ ITM_HBASE_COLUMN_CREATE,
// generate sequence numbers
- ITM_SEQUENCE_VALUE = 2511,
+ ITM_SEQUENCE_VALUE,
// return number of the row being returned. Starts at 1
- ITM_ROWNUM = 2512,
+ ITM_ROWNUM,
- ITM_HBASE_TIMESTAMP = 2513,
- ITM_HBASE_TIMESTAMP_REF = 2514,
- ITM_HBASE_VERSION = 2515,
- ITM_HBASE_VERSION_REF = 2516,
+ ITM_HBASE_TIMESTAMP,
+ ITM_HBASE_TIMESTAMP_REF,
+ ITM_HBASE_VERSION,
+ ITM_HBASE_VERSION_REF,
// list of item expressions
ITM_ITEM_LIST = 2550,
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/680900cd/core/sql/optimizer/GroupAttr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/GroupAttr.cpp b/core/sql/optimizer/GroupAttr.cpp
index 90989be..d027745 100644
--- a/core/sql/optimizer/GroupAttr.cpp
+++ b/core/sql/optimizer/GroupAttr.cpp
@@ -327,6 +327,38 @@ void GroupAttributes::addConstraint(ItemExpr *c)
}
break;
+ case ITM_CHECK_OPT_CONSTRAINT:
+ {
+ CheckOptConstraint *cc = (CheckOptConstraint *) c;
+
+ // check existing uniqueness constraints whether they are similar
+ // and combine uniqueness constraints if possible
+ for (ValueId occ = constraints_.init();
+ constraints_.next(occ);
+ constraints_.advance(occ))
+ {
+ if (occ.getItemExpr()->getOperatorType() ==
+ ITM_CHECK_OPT_CONSTRAINT)
+ {
+ const ValueIdSet &occPreds =
+ ((CheckOptConstraint *) occ.getItemExpr())->getCheckPreds();
+
+ if (occPreds.contains(cc->getCheckPreds()))
+ {
+ // this is no news, delete this useless new constraint
+ duplicateConstraint = TRUE;
+ }
+ else if(cc->getCheckPreds().contains(cc->getCheckPreds()))
+ {
+ // we are improving an existing check constraint,
+ // take the existing one out
+ constraints_ -= occ;
+ }
+ } // this is an existing uniqueness constraint
+ } // for each existing constraint
+ }
+ break;
+
case ITM_UNIQUE_CONSTRAINT:
DCMPASSERT("Wrong constraint type used in GA" == 0); // LCOV_EXCL_LINE
break;
@@ -580,6 +612,127 @@ void GroupAttributes::addSuitableCompRefOptConstraints(
} // loop over constraints
}
+// a const method for validating eliminated columns in a sort order,
+// usually called for validating an earlier result created with
+// the next method below, tryToEliminateOrderColumnBasedOnEqualsPred()
+NABoolean GroupAttributes::canEliminateOrderColumnBasedOnEqualsPred(
+ ValueId col) const
+{
+ // cast away const-ness and call the non-const method without
+ // predicates, which will mean it won't side-effect "this"
+ return const_cast<GroupAttributes *>(this)->
+ tryToEliminateOrderColumnBasedOnEqualsPred(col, NULL);
+}
+
+// This and the previous method are used to match required sort orders
+// or arrangements to an actual key ordering of a table, in cases
+// where some columns are equated to a constant, like this, with the
+// clustering key being (a,b,c)
+//
+// select ...
+// from t
+// where a = 5
+// order by b
+//
+// Note that for VEGs, this is handled differently (see
+// ValueIdList::satisfiesReqdOrder), this code is only for non-VEG
+// cases (usually computed columns, also varchar).
+//
+// If we eliminate a column based on a supplied predicate, then
+// this predicate is added as an Optimizer check constraint to the
+// group attributes, so that future calls will continue to accept
+// the simplified sort order.
+NABoolean GroupAttributes::tryToEliminateOrderColumnBasedOnEqualsPred(
+ ValueId col,
+ const ValueIdSet *preds)
+{
+ NABoolean result = FALSE;
+
+ if (preds || hasConstraintOfType(ITM_CHECK_OPT_CONSTRAINT))
+ {
+ // Comparison failed. If the caller provided predicates,
+ // then we can try something similar to what we did with
+ // group attributes above. If the predicate equate the
+ // column with a constant, then we can eliminate it as
+ // well. Note that we don't expect the requirements to
+ // omit such columns, since the parent will usually not
+ // know about such predicates, so we check this condition
+ // only after trying the regular method.
+ //
+ // This situation typically happens with computed columns
+ // where predicates are added after VEGs are formed
+
+ ValueIdSet checkConstraints;
+ ValueIdSet checkPreds;
+
+ // look for predicates we remembered from earlier calls
+ getConstraintsOfType(ITM_CHECK_OPT_CONSTRAINT,
+ checkConstraints);
+
+ for (ValueId c = checkConstraints.init();
+ checkConstraints.next(c);
+ checkConstraints.advance(c))
+ checkPreds += static_cast<CheckOptConstraint *>(
+ c.getItemExpr())->getCheckPreds();
+
+ // also use newly provided predicates
+ if (preds)
+ checkPreds += *preds;
+
+ // if the column is descending, then get rid of the Inverse
+ // operator for the next check
+ if (col.getItemExpr()->getOperatorType() == ITM_INVERSE)
+ col = col.getItemExpr()->child(0).getValueId();
+
+ // convert col from a VEGRef to a base column, if needed,
+ // the ScanKey method below wants a real column as input
+ if (col.getItemExpr()->getOperatorType() == ITM_VEG_REFERENCE)
+ {
+ const ValueIdSet &vegMembers =
+ static_cast<VEGReference *>(col.getItemExpr())->
+ getVEG()->getAllValues();
+ for (ValueId b=vegMembers.init();
+ vegMembers.next(b);
+ vegMembers.advance(b))
+ if (b.getItemExpr()->getOperatorType() == ITM_BASECOLUMN)
+ col = b;
+ }
+
+ for (ValueId p = checkPreds.init();
+ checkPreds.next(p);
+ checkPreds.advance(p))
+ {
+ ValueId dummy1, dummy2;
+
+ if (p.getItemExpr()->getOperatorType() == ITM_EQUAL &&
+ ScanKey::isAKeyPredicateForColumn(
+ p,
+ dummy1, dummy2,
+ col,
+ getCharacteristicInputs()))
+ {
+ // this is a predicate of the form col = const
+ // and col is our current ValueId in tempThis,
+ // therefore skip over it and try again
+ result = TRUE;
+
+ // if we used a newly provided predicate, then
+ // remember it in the constraints, so that when
+ // the search engine validates our physical
+ // property later, it will come to the same
+ // conclusion
+ if (preds && preds->contains(p))
+ addConstraint(
+ new(CmpCommon::statementHeap()) CheckOptConstraint(
+ ValueIdSet(p)));
+ }
+ }
+ }
+
+ return result;
+}
+
+
// -----------------------------------------------------------------------
// Low-level utility for merging Group Attributes.
// -----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/680900cd/core/sql/optimizer/GroupAttr.h
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/GroupAttr.h b/core/sql/optimizer/GroupAttr.h
index bcb3861..0eb9d3f 100644
--- a/core/sql/optimizer/GroupAttr.h
+++ b/core/sql/optimizer/GroupAttr.h
@@ -246,6 +246,10 @@ public:
Cardinality getMaxNumOfRows() const
{ Cardinality x,y; hasCardConstraint(x,y); return y; }
+ NABoolean canEliminateOrderColumnBasedOnEqualsPred(ValueId col) const;
+ NABoolean tryToEliminateOrderColumnBasedOnEqualsPred(ValueId col,
+ const ValueIdSet *preds);
+
// ---------------------------------------------------------------------
// Methods on group persistent estimates
// ---------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/680900cd/core/sql/optimizer/ImplRule.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/ImplRule.cpp b/core/sql/optimizer/ImplRule.cpp
index c1ea07c..9f9f84a 100644
--- a/core/sql/optimizer/ImplRule.cpp
+++ b/core/sql/optimizer/ImplRule.cpp
@@ -1296,17 +1296,16 @@ RelExpr * generateScanSubstitutes(RelExpr * before,
if (resultNeedsToBeOrdered)
{
ValueIdList sortKey = idesc->getOrderOfKeyValues();
- // Remove from the sort key any columns that are covered by
- // constants or input values.
- sortKey.removeCoveredExprs(
- before->getGroupAttr()->getCharacteristicInputs());
- // make sure it satisfies the required order and also
- // determine the scan direction
+ // Make sure it satisfies the required order and also
+ // determine the scan direction. If computed column predicates
+ // select only one salt or division value, for example,
+ // then make use of this to satisfy order requirements.
if ((rppForMe->getSortKey() != NULL) AND
((oc = sortKey.satisfiesReqdOrder(
*rppForMe->getSortKey(),
- before->getGroupAttr())) == DIFFERENT_ORDER))
+ before->getGroupAttr(),
+ &bef->getComputedPredicates())) == DIFFERENT_ORDER))
indexQualifies = FALSE;
// hbase does not support inverse order scan
@@ -1318,7 +1317,8 @@ RelExpr * generateScanSubstitutes(RelExpr * before,
(rppForMe->getArrangedCols() != NULL) AND
NOT sortKey.satisfiesReqdArrangement(
*rppForMe->getArrangedCols(),
- before->getGroupAttr()))
+ before->getGroupAttr(),
+ &bef->getComputedPredicates()))
indexQualifies = FALSE;
}
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/680900cd/core/sql/optimizer/ItemConstr.h
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/ItemConstr.h b/core/sql/optimizer/ItemConstr.h
index b248515..147a0c7 100644
--- a/core/sql/optimizer/ItemConstr.h
+++ b/core/sql/optimizer/ItemConstr.h
@@ -376,6 +376,45 @@ private:
};
// -----------------------------------------------------------------------
+// Internal check constraint, e.g. a predicate used in synthesizing
+// physical properties like a sort order. These may be needed to validate
+// that a given sort order does indeed satisfy a requirement.
+// -----------------------------------------------------------------------
+class CheckOptConstraint : public OptConstraint
+{
+ // ITM_CHECK_OPT_CONSTRAINT
+public:
+
+ // ctor for unique constraints discovered by the optimizer
+ CheckOptConstraint(const ValueIdSet &checkPreds)
+ : OptConstraint(ITM_CHECK_OPT_CONSTRAINT), checkPreds_(checkPreds) {}
+
+ // convert the other kind of u.c. into this internal kind
+ //##useful extra info for Optimizer at some future point...
+
+ virtual ~CheckOptConstraint();
+
+ // get the degree of this node (it is a leaf).
+ virtual Int32 getArity() const;
+
+ virtual ItemExpr * copyTopNode(ItemExpr *derivedNode = NULL,
+ CollHeap* outHeap = 0);
+
+ // accessor functions
+ const ValueIdSet &getCheckPreds() { return checkPreds_; }
+
+ // get a printable string that identifies the operator
+ const NAString getText() const;
+ void unparse(NAString &result,PhaseEnum phase,UnparseFormatEnum form,
+ TableDesc * tabId = NULL) const;
+
+private:
+
+ ValueIdSet checkPreds_;
+
+}; // CheckOptConstraint
+
+// -----------------------------------------------------------------------
//
// Auxiliary classes and definitions used by the
// referential integrity constraints, UniqueConstraint and RefConstraint.
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/680900cd/core/sql/optimizer/ItemExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/ItemExpr.cpp b/core/sql/optimizer/ItemExpr.cpp
index 3bc1dcd..cde3298 100644
--- a/core/sql/optimizer/ItemExpr.cpp
+++ b/core/sql/optimizer/ItemExpr.cpp
@@ -714,7 +714,7 @@ ItemExpr* ItemExpr::getConstantInVEG()
ItemExpr * ItemExpr::createMirrorPred(ItemExpr *compColPtr,
ItemExpr * compColExprPtr,
- ValueIdSet underlyingCols)
+ const ValueIdSet &underlyingCols)
{
CMPASSERT(compColPtr->getOperatorType() == ITM_BASECOLUMN);
ValueIdSet eics = ((BaseColumn *)compColPtr)->getEIC();
@@ -5945,6 +5945,40 @@ void FuncDependencyConstraint::unparse(NAString &result,
// -----------------------------------------------------------------------
+// member functions for class CheckOptConstraint
+// -----------------------------------------------------------------------
+CheckOptConstraint::~CheckOptConstraint() {}
+
+Int32 CheckOptConstraint::getArity() const { return 0; }
+
+ItemExpr * CheckOptConstraint::copyTopNode(ItemExpr *derivedNode,
+ CollHeap* outHeap)
+{
+ ItemExpr *result;
+
+ if (derivedNode == NULL)
+ result = new (outHeap) CheckOptConstraint(checkPreds_);
+ else
+ result = derivedNode;
+
+ return OptConstraint::copyTopNode(result, outHeap);
+}
+
+const NAString CheckOptConstraint::getText() const
+{
+ return "CheckOptConstraint";
+}
+
+void CheckOptConstraint::unparse(NAString &result,
+ PhaseEnum phase,
+ UnparseFormatEnum form,
+ TableDesc * tabId) const
+{
+ result += "CheckOptConstraint";
+ checkPreds_.unparse(result,phase,form);
+}
+
+// -----------------------------------------------------------------------
// member functions for class RefOptConstraint
// -----------------------------------------------------------------------
Int32 RefOptConstraint::getArity() const { return 0;}
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/680900cd/core/sql/optimizer/ItemExpr.h
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/ItemExpr.h b/core/sql/optimizer/ItemExpr.h
index 71518e4..c8364a1 100644
--- a/core/sql/optimizer/ItemExpr.h
+++ b/core/sql/optimizer/ItemExpr.h
@@ -601,8 +601,8 @@ public:
ItemExpr* constFold();
ItemExpr * createMirrorPred(ItemExpr *compColPtr,
- ItemExpr *compColExprPtr,
- ValueIdSet underlyingCols);
+ ItemExpr *compColExprPtr,
+ const ValueIdSet &underlyingCols);
//Does 'this' ItemExpr evaluate to constant e.g.
//If we go by the strict definition then
//Sin(Cos(1)+1) will return TRUE.
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/680900cd/core/sql/optimizer/OptPhysRelExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/OptPhysRelExpr.cpp b/core/sql/optimizer/OptPhysRelExpr.cpp
index e31c295..35229aa 100644
--- a/core/sql/optimizer/OptPhysRelExpr.cpp
+++ b/core/sql/optimizer/OptPhysRelExpr.cpp
@@ -15171,7 +15171,8 @@ FileScan::synthPhysicalProperty(const Context* myContext,
// ---------------------------------------------------------------------
sortOrderVEG.removeCoveredExprs(getGroupAttr()->getCharacteristicInputs());
sortOrderVEG.complifyAndRemoveUncoveredSuffix(
- getGroupAttr()->getCharacteristicOutputs()) ;
+ getGroupAttr()->getCharacteristicOutputs(),
+ getGroupAttr());
// ---------------------------------------------------------------------
// if this is a reverse scan, apply an inversion function to
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/680900cd/core/sql/optimizer/PhyProp.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/PhyProp.cpp b/core/sql/optimizer/PhyProp.cpp
index 6860b3e..1262a9a 100644
--- a/core/sql/optimizer/PhyProp.cpp
+++ b/core/sql/optimizer/PhyProp.cpp
@@ -262,7 +262,7 @@ PhysicalProperty::enforceCoverageByGroupAttributes (const GroupAttributes * grou
// complifyAndRemoveUncoveredSuffix() had only characteristic output
// in the argument. Now characteristic input is also added.
coveringSet += groupAttr->getCharacteristicOutputs();
- newSortKey.complifyAndRemoveUncoveredSuffix(coveringSet);
+ newSortKey.complifyAndRemoveUncoveredSuffix(coveringSet, groupAttr);
if (newSortKey.isEmpty())
{
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/680900cd/core/sql/optimizer/ValueDesc.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/ValueDesc.cpp b/core/sql/optimizer/ValueDesc.cpp
index a23d2c9..372da1e 100644
--- a/core/sql/optimizer/ValueDesc.cpp
+++ b/core/sql/optimizer/ValueDesc.cpp
@@ -931,7 +931,8 @@ ValueIdList ValueIdList::findNJEquiJoinCols(
// Otherwise, return the length of the prefix seen so far. If there
// were no matches, then we will return 0.
// -----------------------------------------------------------------------
-Int32 ValueIdList::complifyAndCheckPrefixCovered (const ValueIdSet& vidSet)
+Int32 ValueIdList::complifyAndCheckPrefixCovered (const ValueIdSet& vidSet,
+ const GroupAttributes *ga)
{
// if the set's empty, clearly it doesn't cover anything!
if ( vidSet.entries() == 0 ) return 0 ;
@@ -1024,6 +1025,12 @@ Int32 ValueIdList::complifyAndCheckPrefixCovered (const ValueIdSet& vidSet)
(*this)[i] = inverseCol->getValueId();
}
} // end if the simp. set contains the simp. expresssion
+ else if (ga && ga->canEliminateOrderColumnBasedOnEqualsPred(thisVid))
+ {
+ // if this column is constrained to a single value, then
+ // simply remove it from "this"
+ removeAt(i--);
+ }
else
return i;
}
@@ -1040,10 +1047,11 @@ Int32 ValueIdList::complifyAndCheckPrefixCovered (const ValueIdSet& vidSet)
// corresponding versions in the provided set.
// For the remaining suffix, remove those items from this list.
// ---------------------------------------------------------------------
-void ValueIdList::complifyAndRemoveUncoveredSuffix (const ValueIdSet& vidSet)
+void ValueIdList::complifyAndRemoveUncoveredSuffix (const ValueIdSet& vidSet,
+ const GroupAttributes *ga)
{
// last covered id in this list
- Int32 index = complifyAndCheckPrefixCovered(vidSet);
+ Int32 index = complifyAndCheckPrefixCovered(vidSet, ga);
Int32 i = (Int32)entries() - 1; // index of last key column
// Remove all entries following the index
@@ -1263,52 +1271,72 @@ void ValueIdList::replaceVEGExpressions
} // ValueIdList::replaceVEGExpressions()
OrderComparison ValueIdList::satisfiesReqdOrder(const ValueIdList & reqdOrder,
- const GroupAttributes *) const
+ GroupAttributes *ga,
+ const ValueIdSet *preds) const
{
- NAUnsigned numCols = reqdOrder.entries();
+ NAUnsigned numReqdEntries = reqdOrder.entries();
OrderComparison allCols = SAME_ORDER; // sort order comparison of all columns together
OrderComparison oneCol; // one column compared with its counterpart
- // is the actual sort key as long as the required one?
- //
- // Note the following comparison does not consider duplications.
- // such as this=[a, c] and reqOrder=[a, a, c]
- // See OptPhysRelExpr.cpp for a fix for a case regarding union sort keys
- // (search for 10-020913-1676)
- if (entries() < numCols)
- return DIFFERENT_ORDER;
-
- //Orignal condition: if (!OSIM_isNTbehavior() || (OSIM_isNTbehavior() && OSIM_runningSimulation()))
- //For new OSIM, there won't be OSIM_isNTbehavior(), OSIM_isLinuxbehavior() or OSIM_isNSKbehavior(),
- //we will assume OSIM_isLinuxbehavior() is always TRUE.
- //
- //The else block below never gets executed,
- //if it is to be executed under certain conditions,
- //please separate it from this if-conditon.
- if (TRUE)
- {
- ItemExpr *ie = (*this)[0].getItemExpr();
- ItemExpr * reqdExpr = reqdOrder[0].getItemExpr();
- CollIndex i = 0;
- CollIndex j = 0;
- CollIndex numEntries = entries();
+ CollIndex thisIx = 0;
+ CollIndex reqdIx = 0;
+
+ ValueIdList tempThis(*this);
+
+ // if group attributes are provided, remove any constant expressions,
+ // since those should not appear in the requirements and since they
+ // are not relevant to the ordering
+ if (ga)
+ tempThis.removeCoveredExprs(ga->getCharacteristicInputs());
+
+ CollIndex numActEntries = tempThis.entries();
- if (numEntries < numCols)
+ // we assume that reqdOrder went through a RequirementGenerator
+ // and is therefore already optimized (e.g. no duplicate columns)
+
+ // is the actual sort key at least as long as the required one?
+ if (numActEntries < numReqdEntries)
return DIFFERENT_ORDER;
- for (CollIndex startCol = 0; startCol < numCols; startCol++)
+ for (CollIndex reqdIx = 0; reqdIx < numReqdEntries; reqdIx++)
{
- if ((reqdOrder[j] == (*this)[i]))
- {
- oneCol = SAME_ORDER;
- }
- else
+ NABoolean done = FALSE;
+
+ // compare the next required order column with one or more
+ // ValueIds in tempThis
+ while (!done)
{
- oneCol = reqdOrder[j].getItemExpr()->
- sameOrder((*this)[i].getItemExpr());
+ // make sure we have enough columns in "tempThis"
+ if (thisIx >= numActEntries)
+ return DIFFERENT_ORDER;
+
+ // compare the next column of tempThis with the required order
+ oneCol = reqdOrder[reqdIx].getItemExpr()->sameOrder(
+ tempThis[thisIx].getItemExpr());
+
+ done = TRUE;
+
+ // If we didn't find the column, then try to find an equals
+ // predicate that equates the column to a constant, so that
+ // we can skip over it. This typically happens with computed
+ // columns such as salt or division that don't have associated
+ // VEGs, which are handled by tempThis.removeCoveredExprs() above.
+ if (oneCol == DIFFERENT_ORDER &&
+ ga &&
+ (numActEntries-thisIx) > (numReqdEntries-reqdIx) && // extra cols in this
+ ga->tryToEliminateOrderColumnBasedOnEqualsPred(tempThis[thisIx],
+ preds))
+ {
+ // this is a predicate of the form col = const
+ // and col is our current ValueId in tempThis,
+ // therefore skip over it and try again
+ thisIx++;
+ done = FALSE;
+ }
}
- if (startCol == 0)
+
+ if (reqdIx == 0)
allCols = oneCol;
else
allCols = combineOrderComparisons(allCols,oneCol);
@@ -1316,43 +1344,15 @@ OrderComparison ValueIdList::satisfiesReqdOrder(const ValueIdList & reqdOrder,
if (allCols == DIFFERENT_ORDER)
return allCols;
- i++; j++;
- }
- }
- else{
- for (CollIndex i = 0; i < numCols; i++)
- {
- if ((reqdOrder[i] == (*this)[i]))
- {
- oneCol = SAME_ORDER;
- }
- else
- {
- // if the expressions are not exactly the same, try to match similar
- // expressions that result in the same sorting order, like a and a+1
- oneCol = reqdOrder[i].getItemExpr()->
- sameOrder((*this)[i].getItemExpr());
- }
-
- // the first column determines whether we do the same order or the
- // inverse order or nothing, all other columns have to follow that
- // decision
- if (i == 0)
- allCols = oneCol;
- else
- allCols = combineOrderComparisons(allCols,oneCol);
-
- // did we already loose it?
- if (allCols == DIFFERENT_ORDER)
- return allCols;
- }
+ thisIx++;
}
return allCols;
}
NABoolean ValueIdList::satisfiesReqdArrangement(
const ValueIdSet &reqdArrangement,
- const GroupAttributes * ga) const
+ GroupAttributes * ga,
+ const ValueIdSet *preds) const
{
// ---------------------------------------------------------------------
// check requirement for arrangement of data (check whether the required
@@ -1364,6 +1364,12 @@ NABoolean ValueIdList::satisfiesReqdArrangement(
NABoolean found = TRUE;
CollIndex startCol = 0;
+ // if group attributes are provided, remove any constant expressions,
+ // since those should not appear in the requirements and since they
+ // are not relevant to the ordering
+ if (ga)
+ arrCols.removeCoveredExprs(ga->getCharacteristicInputs());
+
// walk along the sort key, as long as all columns in the sort
// keys are part of the required set of arranged columns
@@ -1401,6 +1407,16 @@ NABoolean ValueIdList::satisfiesReqdArrangement(
arrCols -= x;
}
}
+
+ if ((NOT found) && ga)
+ {
+ // if this column is constrained to a single value,
+ // we can skip over it and continue (we set found to
+ // true but don't remove anything from arrCols)
+ found = ga->tryToEliminateOrderColumnBasedOnEqualsPred(
+ (*this)[i],
+ preds);
+ }
}
}
@@ -2930,6 +2946,7 @@ ValueIdSet ValueIdSet::createMirrorPreds(ValueId &computedCol,
CMPASSERT( iePtr->getOperatorType() == ITM_BASECOLUMN );
ItemExpr *compExpr = ((BaseColumn *) iePtr)->getComputedColumnExpr().getItemExpr();
+ ItemExpr *prevPred = NULL;
for (ValueId kpv = init(); next(kpv); advance(kpv))
{
@@ -2947,7 +2964,44 @@ ValueIdSet ValueIdSet::createMirrorPreds(ValueId &computedCol,
{
ItemExpr * newPred = piePtr->createMirrorPred(iePtr, compExpr, underlyingCols);
if (newPred != NULL)
- newComputedPreds += newPred->getValueId();
+ {
+ // look for the following pattern, which is common
+ // when a range of values falls within one division
+ // col1 >= const1 AND col1 <= const1
+ // and transform this into the single predicate col1 = const1
+ if (prevPred)
+ {
+ OperatorTypeEnum prevOp = prevPred->getOperatorType();
+ OperatorTypeEnum newOp = newPred->getOperatorType();
+ NABoolean neg1, neg2;
+ ConstValue *prevConst = prevPred->child(1)->castToConstValue(neg1);
+ ConstValue *newConst = newPred->child(1)->castToConstValue(neg2);
+
+ if ((prevOp == ITM_GREATER_EQ && newOp == ITM_LESS_EQ ||
+ newOp == ITM_GREATER_EQ && prevOp == ITM_LESS_EQ) &&
+ prevPred->child(0) == newPred->child(0) &&
+ prevConst && newConst &&
+ prevConst->duplicateMatch(*newConst) && neg1 == neg2 &&
+ static_cast<BiRelat *>(prevPred)->getSpecialNulls() ==
+ static_cast<BiRelat *>(newPred)->getSpecialNulls())
+ {
+ // make the <= or >= predicate of the pattern into an = predicate
+ BiRelat *newEqualPred = new(CmpCommon::statementHeap())
+ BiRelat(ITM_EQUAL,
+ newPred->child(0),
+ newPred->child(1));
+ newEqualPred->setSpecialNulls(
+ static_cast<BiRelat *>(newPred)->getSpecialNulls());
+ newEqualPred->synthTypeAndValueId();
+
+ newComputedPreds -= prevPred->getValueId();
+ newPred = newEqualPred;
+ }
+ }
+
+ newComputedPreds += newPred->getValueId();
+ prevPred = newPred;
+ }
break;
}
case ITM_ASSIGN:
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/680900cd/core/sql/optimizer/ValueDesc.h
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/ValueDesc.h b/core/sql/optimizer/ValueDesc.h
index 7a7a625..50e4634 100644
--- a/core/sql/optimizer/ValueDesc.h
+++ b/core/sql/optimizer/ValueDesc.h
@@ -469,7 +469,8 @@ public:
// list with the one in the original provided set - i.e. "complify"
// it.
// ---------------------------------------------------------------------
- Int32 complifyAndCheckPrefixCovered (const ValueIdSet& vidSet);
+ Int32 complifyAndCheckPrefixCovered (const ValueIdSet& vidSet,
+ const GroupAttributes *ga);
// ---------------------------------------------------------------------
// Check whether a prefix of this list is covered by the provided set.
@@ -477,7 +478,8 @@ public:
// their counterparts in the provided set.
// For the remaining suffix, remove those items from this list.
// ---------------------------------------------------------------------
- void complifyAndRemoveUncoveredSuffix (const ValueIdSet& vidSet);
+ void complifyAndRemoveUncoveredSuffix (const ValueIdSet& vidSet,
+ const GroupAttributes *ga);
// ---------------------------------------------------------------------
// simplifyOrderExpr()
@@ -512,11 +514,15 @@ public:
// ---------------------------------------------------------------------
// If a table is ordered by the expressions described in this list,
// does the ordering satisfy a required order or arrangement of columns
+ // (GroupAttributes and predicates can be provided to allow more matches
+ // by applying some optimizations)
// ---------------------------------------------------------------------
OrderComparison satisfiesReqdOrder(const ValueIdList &reqdOrder,
- const GroupAttributes *ga = NULL) const;
+ GroupAttributes *ga = NULL,
+ const ValueIdSet *preds = NULL) const;
NABoolean satisfiesReqdArrangement(const ValueIdSet &reqdArrangement,
- const GroupAttributes *ga = NULL) const;
+ GroupAttributes *ga = NULL,
+ const ValueIdSet *preds = NULL) const;
// ---------------------------------------------------------------------
// Calculate the length of the row containing all the value ids
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/680900cd/core/sql/regress/compGeneral/EXPECTED071
----------------------------------------------------------------------
diff --git a/core/sql/regress/compGeneral/EXPECTED071 b/core/sql/regress/compGeneral/EXPECTED071
index 5fd689b..a5a4d39 100644
--- a/core/sql/regress/compGeneral/EXPECTED071
+++ b/core/sql/regress/compGeneral/EXPECTED071
@@ -668,6 +668,53 @@ TRAFODION_VSBB_DELETE TRAFODION.MTD.MTD2
--- 0 row(s) selected.
>>
+>>-- test elimination of division column for single-division queries with order by
+>>explain options 'f'
++>select *
++>from mtd1
++>where sale_date between date '2000-01-01' and date '2000-01-21'
++>order by store_id;
+
+LC RC OP OPERATOR OPT DESCRIPTION CARD
+---- ---- ---- -------------------- -------- -------------------- ---------
+
+1 . 2 root 1.10E+001
+. . 1 trafodion_scan MTD1 1.10E+001
+
+--- SQL operation complete.
+>>-- expect no sort operator, for a single division, table is sorted on store_id
+>>
+>>explain options 'f'
++>select *
++>from mtd2
++>where sale_date between date '2000-01-01' and date '2000-01-21'
++>order by store_id;
+
+LC RC OP OPERATOR OPT DESCRIPTION CARD
+---- ---- ---- -------------------- -------- -------------------- ---------
+
+1 . 2 root 1.10E+001
+. . 1 trafodion_scan MTD2 1.10E+001
+
+--- SQL operation complete.
+>>-- expect no sort operator, for a single division, table is sorted on store_id
+>>
+>>explain options 'f'
++>select *
++>from mtd2
++>where sale_date between date '2000-01-01' and date '2000-02-21'
++>order by store_id;
+
+LC RC OP OPERATOR OPT DESCRIPTION CARD
+---- ---- ---- -------------------- -------- -------------------- ---------
+
+2 . 3 root 1.10E+001
+1 . 2 sort 1.10E+001
+. . 1 trafodion_scan MTD2 1.10E+001
+
+--- SQL operation complete.
+>>-- expect a sort operator, since we are querying more than one division
+>>
>>-- some simple join queries, validate min/max optimization for divisioned table
>>
>>prepare min_max_opt_query from
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/680900cd/core/sql/regress/compGeneral/TEST071
----------------------------------------------------------------------
diff --git a/core/sql/regress/compGeneral/TEST071 b/core/sql/regress/compGeneral/TEST071
index 41dac6b..cf42391 100644
--- a/core/sql/regress/compGeneral/TEST071
+++ b/core/sql/regress/compGeneral/TEST071
@@ -376,6 +376,28 @@ execute s;
select *, "_DIVISION_1_" from mtd2 order by 1, 2, 3;
+-- test elimination of division column for single-division queries with order by
+explain options 'f'
+select *
+from mtd1
+where sale_date between date '2000-01-01' and date '2000-01-21'
+order by store_id;
+-- expect no sort operator, for a single division, table is sorted on store_id
+
+explain options 'f'
+select *
+from mtd2
+where sale_date between date '2000-01-01' and date '2000-01-21'
+order by store_id;
+-- expect no sort operator, for a single division, table is sorted on store_id
+
+explain options 'f'
+select *
+from mtd2
+where sale_date between date '2000-01-01' and date '2000-02-21'
+order by store_id;
+-- expect a sort operator, since we are querying more than one division
+
-- some simple join queries, validate min/max optimization for divisioned table
prepare min_max_opt_query from
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/680900cd/core/sql/regress/seabase/EXPECTED010
----------------------------------------------------------------------
diff --git a/core/sql/regress/seabase/EXPECTED010 b/core/sql/regress/seabase/EXPECTED010
index b4cbf66..726905c 100644
--- a/core/sql/regress/seabase/EXPECTED010
+++ b/core/sql/regress/seabase/EXPECTED010
@@ -192,6 +192,20 @@ LC RC OP OPERATOR OPT DESCRIPTION CARD
--- SQL operation complete.
>>
+>>-- should see no sort operator when selecting from one salt bucket
+>>prepare xx from select * from t010t3 where (a,b) = (1,'b') order by c;
+
+--- SQL command prepared.
+>>explain options 'f' xx;
+
+LC RC OP OPERATOR OPT DESCRIPTION CARD
+---- ---- ---- -------------------- -------- -------------------- ---------
+
+1 . 2 root 1.00E+000
+. . 1 trafodion_scan T010T3 1.00E+000
+
+--- SQL operation complete.
+>>
>>-- selectPred should present due to the 2nd disjunct b='1'
>>prepare xx from select * from t010t1 where a=1 or b='1';
@@ -1843,6 +1857,20 @@ LC RC OP OPERATOR OPT DESCRIPTION CARD
--- SQL operation complete.
>>
+>>-- should see no sort operator when selecting from one salt bucket
+>>prepare xx from select * from t010t3 where (a,b) = (1,'b') order by c;
+
+--- SQL command prepared.
+>>explain options 'f' xx;
+
+LC RC OP OPERATOR OPT DESCRIPTION CARD
+---- ---- ---- -------------------- -------- -------------------- ---------
+
+1 . 2 root 1.00E+000
+. . 1 trafodion_scan T010T3 1.00E+000
+
+--- SQL operation complete.
+>>
>>-- selectPred should present due to the 2nd disjunct b='1'
>>prepare xx from select * from t010t1 where a=1 or b='1';
@@ -3515,6 +3543,20 @@ LC RC OP OPERATOR OPT DESCRIPTION CARD
--- SQL operation complete.
>>
+>>-- should see no sort operator when selecting from one salt bucket
+>>prepare xx from select * from t010t3 where (a,b) = (1,'b') order by c;
+
+--- SQL command prepared.
+>>explain options 'f' xx;
+
+LC RC OP OPERATOR OPT DESCRIPTION CARD
+---- ---- ---- -------------------- -------- -------------------- ---------
+
+1 . 2 root 1.00E+000
+. . 1 trafodion_scan T010T3 1.00E+000
+
+--- SQL operation complete.
+>>
>>-- selectPred should present due to the 2nd disjunct b='1'
>>prepare xx from select * from t010t1 where a=1 or b='1';
@@ -5172,6 +5214,20 @@ LC RC OP OPERATOR OPT DESCRIPTION CARD
--- SQL operation complete.
>>
+>>-- should see no sort operator when selecting from one salt bucket
+>>prepare xx from select * from t010t3 where (a,b) = (1,'b') order by c;
+
+--- SQL command prepared.
+>>explain options 'f' xx;
+
+LC RC OP OPERATOR OPT DESCRIPTION CARD
+---- ---- ---- -------------------- -------- -------------------- ---------
+
+1 . 2 root 1.00E+000
+. . 1 trafodion_scan T010T3 1.00E+000
+
+--- SQL operation complete.
+>>
>>-- selectPred should present due to the 2nd disjunct b='1'
>>prepare xx from select * from t010t1 where a=1 or b='1';
@@ -6835,6 +6891,20 @@ LC RC OP OPERATOR OPT DESCRIPTION CARD
--- SQL operation complete.
>>
+>>-- should see no sort operator when selecting from one salt bucket
+>>prepare xx from select * from t010t3 where (a,b) = (1,'b') order by c;
+
+--- SQL command prepared.
+>>explain options 'f' xx;
+
+LC RC OP OPERATOR OPT DESCRIPTION CARD
+---- ---- ---- -------------------- -------- -------------------- ---------
+
+1 . 2 root 1.00E+000
+. . 1 trafodion_scan T010T3 1.00E+000
+
+--- SQL operation complete.
+>>
>>-- selectPred should present due to the 2nd disjunct b='1'
>>prepare xx from select * from t010t1 where a=1 or b='1';
@@ -8484,6 +8554,20 @@ LC RC OP OPERATOR OPT DESCRIPTION CARD
--- SQL operation complete.
>>
+>>-- should see no sort operator when selecting from one salt bucket
+>>prepare xx from select * from t010t3 where (a,b) = (1,'b') order by c;
+
+--- SQL command prepared.
+>>explain options 'f' xx;
+
+LC RC OP OPERATOR OPT DESCRIPTION CARD
+---- ---- ---- -------------------- -------- -------------------- ---------
+
+1 . 2 root 1.00E+000
+. . 1 trafodion_scan T010T3 1.00E+000
+
+--- SQL operation complete.
+>>
>>-- selectPred should present due to the 2nd disjunct b='1'
>>prepare xx from select * from t010t1 where a=1 or b='1';
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/680900cd/core/sql/regress/seabase/TEST010
----------------------------------------------------------------------
diff --git a/core/sql/regress/seabase/TEST010 b/core/sql/regress/seabase/TEST010
index ed89468..4efc187 100644
--- a/core/sql/regress/seabase/TEST010
+++ b/core/sql/regress/seabase/TEST010
@@ -133,6 +133,10 @@ explain options 'f' xx;
prepare xx from select * from t010t1 order by b;
explain options 'f' xx;
+-- should see no sort operator when selecting from one salt bucket
+prepare xx from select * from t010t3 where (a,b) = (1,'b') order by c;
+explain options 'f' xx;
+
-- selectPred should present due to the 2nd disjunct b='1'
prepare xx from select * from t010t1 where a=1 or b='1';
explain xx;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/680900cd/core/sql/regress/seabase/TEST014
----------------------------------------------------------------------
diff --git a/core/sql/regress/seabase/TEST014 b/core/sql/regress/seabase/TEST014
index 6ca61fe..3fdab85 100644
--- a/core/sql/regress/seabase/TEST014
+++ b/core/sql/regress/seabase/TEST014
@@ -40,6 +40,7 @@ cqd query_cache '0';
obey TEST014(test_ddl_disable_partition);
log;
+obey TEST014(clean_up);
exit;
?section clean_up
[5/7] incubator-trafodion git commit: [TRAFODION-1581] TMUDF for JDBC
queries
Posted by hz...@apache.org.
[TRAFODION-1581] TMUDF for JDBC queries
Other JIRAs fixed with this commit:
[TRAFODION-1582] Optional Drill install for local hadoop
This new built-in TMUDF takes arguments that describe a
JDBC connection and a list of SQL statements and returns
the result of the one SQL statement in the list that
produces results:
select ... from udf(JDBC(
<name of JDBC driver jar>,
<name of JDBC driver class in the jar>,
<connection string>,
<user name>,
<password>,
<statement_type>,
<sql statement 1>
[ , <sql statements 2 ...n> ] )) ...
The first 7 arguments are required and must be
string literals that are available at compile
time.
Statement type:
'source': This statement produces a result
(only type allowed at this time)
(may support "target" to insert
into a table via JDBC later)
Note that only one of the SQL statements can be
a select or other result-producing statements.
The others can perform setup and cleanup
operations, if necessary (e.g. create table,
insert, select, drop table).
For an example, see file
core/sql/regress/udr/TEST002.
Note that this UDF is still a prototype, it needs more
testing.
Other small fixes:
- install_local_hadoop now picks ports in the non-ephemeral
range when doing install_local_hadoop -p fromDisplay.
Port number range starts at 24000 + 200*display number.
Stay below 42 for your display to avoid the ephemeral range
and pick a different display # if you run into port conflicts.
- Setupdir step (this includes building libhdfs, if needed)
now logs its output with a suffix ##(setupdir) like most
other components
core/Makefile
- Fixed a bug causing a core when a TMUDF produced no
output columns and another bug with a VARCHAR parameter
at the beginning of a parameter list.
core/optimizer/UdfDllInteraction.cpp
- Fixed a bug in parsing two consecutive patterns in sqlci
input, like $$a$$$$b$$
core/sqlci/SqlCmd.cpp
- Small fixes to doxygen documentation: With the new web
page structure, make doxygen version match the Trafodion
version (at least the initial release that applies), also
fix links to wiki to point to the Apache wiki.
core/sql/sqludr/doxygen_tmudr.1.6.config
core/sql/sqludr/sqludr.cpp
core/sql/sqludr/sqludr.h
- Make hive-exec dependency for SQL explicit (would sometimes
produce an error depending on the sequence in which things
are built)
core/sql/pom.xml
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/a3477192
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/a3477192
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/a3477192
Branch: refs/heads/master
Commit: a34771927c1afd3348fb4609b4c2944f5c47507a
Parents: 849de1a
Author: Hans Zeller <hz...@apache.org>
Authored: Wed Dec 9 17:17:25 2015 +0000
Committer: Hans Zeller <hz...@apache.org>
Committed: Wed Dec 9 17:31:11 2015 +0000
----------------------------------------------------------------------
core/Makefile | 4 +-
core/sqf/sql/scripts/install_local_drill | 114 +++
core/sqf/sql/scripts/install_local_hadoop | 41 +-
core/sql/bin/SqlciErrors.txt | 1 +
core/sql/common/ExprNode.cpp | 1 +
core/sql/common/OperTypeEnum.h | 1 +
core/sql/optimizer/RelRoutine.cpp | 55 +-
core/sql/optimizer/UdfDllInteraction.cpp | 10 +-
core/sql/pom.xml | 5 +
core/sql/regress/tools/runregr_udr.ksh | 7 +
core/sql/regress/udr/EXPECTED002 | 48 +-
core/sql/regress/udr/TEST002 | 18 +
core/sql/sqlci/SqlCmd.cpp | 1 +
core/sql/sqludr/doxygen_tmudr.1.6.config | 4 +-
core/sql/sqludr/sqludr.cpp | 2 +-
core/sql/sqludr/sqludr.h | 16 +-
.../trafodion/sql/udr/UDRInvocationInfo.java | 17 +-
.../org/trafodion/sql/udr/predef/JDBCUDR.java | 690 +++++++++++++++++++
18 files changed, 972 insertions(+), 63 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a3477192/core/Makefile
----------------------------------------------------------------------
diff --git a/core/Makefile b/core/Makefile
index ab62c49..b947b1a 100644
--- a/core/Makefile
+++ b/core/Makefile
@@ -101,7 +101,7 @@ cleantests:
cd ../dcs/src/test/pytests && $(RM) -r odbc* tox.ini config.ini .tox .testrep* env.sh test_p2.pyc
cd ../dcs/src/test/jdbc_test && $(RM) -r jdbcprop pom.xml target
cd ../tests/phx && $(RM) -r jdbcprop *.log pom.xml target
-
+
clean: sqroot
cd $(MPI_TARGET) && $(MAKE) clean-local
cd $(SEAMONSTER_TARGET)/src && $(MAKE) clean
@@ -158,7 +158,7 @@ version:
# create links and files needed as prerequisite for the build
sqroot:
./bldenvchk.sh;
- cd sqf && $(MAKE) setupdir
+ cd sqf && $(MAKE) setupdir 2>&1 | sed -e "s/$$/ ##(setupdir)/";exit $${PIPESTATUS[0]}
# Check for absolute filenames used as dynamic linked libraries
find-absolute-dlls:
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a3477192/core/sqf/sql/scripts/install_local_drill
----------------------------------------------------------------------
diff --git a/core/sqf/sql/scripts/install_local_drill b/core/sqf/sql/scripts/install_local_drill
new file mode 100755
index 0000000..bc2b3ca
--- /dev/null
+++ b/core/sqf/sql/scripts/install_local_drill
@@ -0,0 +1,114 @@
+#!/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 @@@
+#
+##############################################################################
+##
+## Install a sand-boxed version of Apache Drill, to be used together
+## with the setup created in install_local_hadoop.
+## This will share the zookeeper instance with HBase and it will
+## use custom ports determined by install_local_hadoop.
+##
+##############################################################################
+
+# location of local Hadoop components, we will add Drill here
+MY_SW_ROOT=$MY_SQROOT/sql/local_hadoop
+
+# Download info for Drill
+DRILL_VERSION=1.2.0
+DRILL_MIRROR_URL=http://archive.apache.org/dist/drill/drill-${DRILL_VERSION}
+DRILL_ID=apache-drill-${DRILL_VERSION}
+DRILL_TAR=${DRILL_ID}.tar.gz
+
+if [[ -d ${MY_SW_ROOT}/${DRILL_TAR} ]]; then
+ echo "Drill is already downloaded to ${MY_SW_ROOT}/${DRILL_TAR}, exiting..."
+ exit 0
+fi
+
+if [[ ! -d "$MY_SW_ROOT" ]]; then
+ echo "Could not find directory $MY_SW_ROOT"
+ exit 1
+fi
+
+cd $MY_SW_ROOT
+
+if [ -f $MY_LOCAL_SW_DIST/${DRILL_TAR} ]; then
+ cp $MY_LOCAL_SW_DIST/${DRILL_TAR} .
+ echo "Copied Drill tar file from: $MY_LOCAL_SW_DIST/${DRILL_TAR}"
+else
+ wget ${DRILL_MIRROR_URL}/${DRILL_TAR}
+ echo "Downloaded Drill tar file: ${DRILL_MIRROR_URL}/${DRILL_TAR}"
+fi
+
+if [[ ! -f $DRILL_TAR ]]; then
+ echo "Unable to download Drill tar file ${DRILL_MIRROR_URL}/${DRILL_TAR}"
+ exit 1
+fi
+
+tar -xzf ${DRILL_TAR}
+ln -s ${DRILL_ID} drill
+
+cat <<EOF >${MY_SQROOT}/sql/scripts/swdrill
+#!/bin/sh
+$MY_SW_ROOT/drill/bin/drill-conf "\$@"
+EOF
+
+cat <<EOF >${MY_SQROOT}/sql/scripts/swstartdrill
+#!/bin/sh
+${MY_SW_ROOT}/drill/bin/drillbit.sh --config $MY_SW_ROOT/drill/conf start
+EOF
+
+cat <<EOF >${MY_SQROOT}/sql/scripts/swstopdrill
+#!/bin/sh
+${MY_SW_ROOT}/drill/bin/drillbit.sh --config $MY_SW_ROOT/drill/conf stop
+EOF
+
+chmod +x ${MY_SQROOT}/sql/scripts/sw*drill
+
+# pick up environment variables with the relevant port numbers
+. ${MY_SQROOT}/sql/scripts/sw_env.sh
+
+# customize the embedded Drill configuration to use custom
+# port numbers and files local to ${MY_SW_ROOT}
+cd $MY_SW_ROOT/drill/conf
+mv drill-override.conf drill-override.conf.orig
+cat <<EOF >drill-override.conf
+drill.exec: {
+ cluster-id: "local_hadoop_drill",
+ zk.connect: "localhost:${MY_HBASE_ZOOKEEPER_PROPERTY_CLIENTPORT_NUM}",
+ sys.store.provider.local.path: "${MY_SW_ROOT}/data/drill",
+ sys.store.provider.zk.blobroot: "file://${MY_SW_ROOT}/data/drill/blobroot",
+ tmp.directories: "${MY_SW_ROOT}/data/drill/tmp",
+ trace.directory: "${MY_SW_ROOT}/data/drill/log",
+ http.port: "${MY_DRILL_INFO_PORT_NUM}",
+ rpc.user.server.port: "${MY_DRILL_RPC_PORT_NUM}",
+ rpc.bit.server.port: "${MY_DRILL_BIT_PORT_NUM}"
+}
+EOF
+
+# copy the Drill JDBC driver into the external_libs folder for UDRs
+mkdir -p ${MY_SQROOT}/udr/external_libs
+cp ${MY_SW_ROOT}/drill/jars/jdbc-driver/drill-jdbc-all-${DRILL_VERSION}.jar ${MY_SQROOT}/udr/external_libs
+
+echo "Installed a local copy of Drill. Start a drillbit with the"
+echo "swstartdrill command, connect with swdrill."
+
+exit 0
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a3477192/core/sqf/sql/scripts/install_local_hadoop
----------------------------------------------------------------------
diff --git a/core/sqf/sql/scripts/install_local_hadoop b/core/sqf/sql/scripts/install_local_hadoop
index 97d9976..951f417 100755
--- a/core/sqf/sql/scripts/install_local_hadoop
+++ b/core/sqf/sql/scripts/install_local_hadoop
@@ -385,10 +385,13 @@ else
# display :34.0 would result in starting port 53400 (instead of default 50000 range)
MY_START_PORT=${DISPLAY/*:/}
MY_START_PORT=${MY_START_PORT/.*/}
- MY_START_PORT=`expr 50000 + $MY_START_PORT '*' 100`
+ # Pick a port range starting at 24000 + 200 * display number.
+ # Choose a display number of up to 42 to stay below the
+ # ephemeral port range which typically starts at 32768.
+ MY_START_PORT=`expr 24000 + $MY_START_PORT '*' 200`
elif [ $MY_START_PORT == "rand" ]; then
- # pick a random number between 9000 and 49000 that is divisible by 200
- MY_START_PORT=`expr $RANDOM '%' 200 '*' 200 + 9000`
+ # pick a random number between 12000 and 24000 that is divisible by 200
+ MY_START_PORT=`expr $RANDOM '%' 50 '*' 200 + 12000`
fi
echo "# Using non-standard port range from MY_START_PORT env var: $MY_START_PORT..."
fi
@@ -445,6 +448,12 @@ if [ $USE_DEFAULT_PORTS == 'yes' ]; then
MY_DB_MGR_PORT=4205
# database.mgr.secure.port
MY_DB_MGR_SECURE_PORT=4206
+ # drill.exec.http.port
+ MY_DRILL_INFO_PORT_NUM=8047
+ # drill.exec.rpc.user.server.port
+ MY_DRILL_RPC_PORT_NUM=31010
+ # drill.exec.rpc.bit.server.port
+ MY_DRILL_BIT_PORT_NUM=31011
else
# fs.default.name
MY_HADOOP_HDFS_PORT_NUM=$MY_START_PORT
@@ -475,6 +484,11 @@ else
MY_REST_SERVER_SECURE_PORT=`expr $MY_START_PORT + 194`
MY_DB_MGR_PORT=`expr $MY_START_PORT + 195`
MY_DB_MGR_SECURE_PORT=`expr $MY_START_PORT + 196`
+
+ # Apache Drill, embedded configuration
+ MY_DRILL_INFO_PORT_NUM=`expr $MY_START_PORT + 147`
+ MY_DRILL_RPC_PORT_NUM=`expr $MY_START_PORT + 148`
+ MY_DRILL_BIT_PORT_NUM=`expr $MY_START_PORT + 149`
fi
# handle ports in the range of 50000 to 50199
@@ -695,6 +709,9 @@ export MY_REST_SERVER_PORT=${MY_REST_SERVER_PORT}
export MY_REST_SERVER_SECURE_PORT=${MY_REST_SERVER_SECURE_PORT}
export MY_DB_MGR_PORT=${MY_DB_MGR_PORT}
export MY_DB_MGR_SECURE_PORT=${MY_DB_MGR_SECURE_PORT}
+export MY_DRILL_INFO_PORT_NUM=${MY_DRILL_INFO_PORT_NUM}
+export MY_DRILL_RPC_PORT_NUM=${MY_DRILL_RPC_PORT_NUM}
+export MY_DRILL_BIT_PORT_NUM=${MY_DRILL_BIT_PORT_NUM}
EOF
# now source in this script
@@ -796,7 +813,7 @@ EOF
# command to run hadoop
. $MY_SW_SCRIPTS_DIR/sw_env.sh
-${MY_SW_ROOT}/hadoop/bin/hadoop \$*
+${MY_SW_ROOT}/hadoop/bin/hadoop "\$@"
EOF
cat <<EOF >$MY_SW_SCRIPTS_DIR/swyarn
@@ -804,7 +821,7 @@ EOF
# command to run yarn
. $MY_SW_SCRIPTS_DIR/sw_env.sh
-${MY_SW_ROOT}/hadoop/bin/yarn \$*
+${MY_SW_ROOT}/hadoop/bin/yarn "\$@"
EOF
cat <<EOF >$MY_SW_SCRIPTS_DIR/swhdfs
@@ -812,7 +829,7 @@ EOF
# command to run hadoop
. $MY_SW_SCRIPTS_DIR/sw_env.sh
-${MY_SW_ROOT}/hadoop/bin/hdfs \$*
+${MY_SW_ROOT}/hadoop/bin/hdfs "\$@"
EOF
cat <<EOF >$MY_SW_SCRIPTS_DIR/swmysql
@@ -820,7 +837,7 @@ EOF
# command to run mysql
. $MY_SW_SCRIPTS_DIR/sw_env.sh
-${MY_SW_ROOT}/mysql/bin/mysql --defaults-file=${MY_SQL_CONFIG_FILE} --user=${MY_SQL_USER} --password=${MY_SQL_USER_PASSWD} --database=${MY_SQL_METASTORE_DB} \$*
+${MY_SW_ROOT}/mysql/bin/mysql --defaults-file=${MY_SQL_CONFIG_FILE} --user=${MY_SQL_USER} --password=${MY_SQL_USER_PASSWD} --database=${MY_SQL_METASTORE_DB} "\$@"
EOF
cat <<EOF >$MY_SW_SCRIPTS_DIR/swmysqladmin
@@ -828,7 +845,7 @@ EOF
# command to run mysqladmin as root user
. $MY_SW_SCRIPTS_DIR/sw_env.sh
-${MY_SW_ROOT}/mysql/bin/mysqladmin --defaults-file=${MY_SQL_CONFIG_FILE} --user=${MY_SQL_ADMIN} --password=${MY_SQL_ADMIN_PASSWD} \$*
+${MY_SW_ROOT}/mysql/bin/mysqladmin --defaults-file=${MY_SQL_CONFIG_FILE} --user=${MY_SQL_ADMIN} --password=${MY_SQL_ADMIN_PASSWD} "\$@"
EOF
cat <<EOF >$MY_SW_SCRIPTS_DIR/swhive
@@ -838,7 +855,7 @@ EOF
# Pick up MySQL JDBC driver
. $MY_SW_SCRIPTS_DIR/sw_env.sh
export HADOOP_HOME=${MY_SW_ROOT}/hadoop
-${MY_SW_ROOT}/hive/bin/hive \$*
+${MY_SW_ROOT}/hive/bin/hive "\$@"
EOF
cat <<EOF >$MY_SW_SCRIPTS_DIR/swhbase
@@ -846,7 +863,7 @@ EOF
# command to run hbase shell
. $MY_SW_SCRIPTS_DIR/sw_env.sh
-${MY_SW_ROOT}/hbase/bin/hbase shell \$*
+${MY_SW_ROOT}/hbase/bin/hbase shell "\$@"
EOF
cat <<EOF >$MY_SW_SCRIPTS_DIR/swuninstall_local_hadoop
@@ -882,6 +899,7 @@ EOF
<a href="http://${MY_HOST_1}:${MY_YARN_HTTP_PORT_NUM}">Yarn</a>
<a href="http://${MY_HOST_1}:${MY_HBASE_MASTER_INFO_PORT_NUM}">HBase Master</a>
<a href="http://${MY_HOST_1}:${MY_DCS_MASTER_INFO_PORT}">DCS Master</a>
+<a href="http://${MY_HOST_1}:${MY_DRILL_INFO_PORT_NUM}">Drill Web Console (while shell is running)</a>
</pre>
</BODY>
</HTML>
@@ -1251,6 +1269,9 @@ else
echo "Unpacking MySQL JDBC tar file ${MYSQL_JDBC_TAR} ..."
tar -xf ${MYSQL_JDBC_TAR}
ln -s `dirname mysql-connector-java-*/mysql-connector-java-*.jar` mysql-connector-java
+ # copy the MySQL JDBC driver to the udr directory, so UDRs can use it
+ mkdir -p ${MY_SQROOT}/udr/external_libs
+ cp ${MY_SW_ROOT}/mysql-connector-java/mysql-connector-java*.jar ${MY_SQROOT}/udr/external_libs
fi
# end of MySQL JDBC setup
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a3477192/core/sql/bin/SqlciErrors.txt
----------------------------------------------------------------------
diff --git a/core/sql/bin/SqlciErrors.txt b/core/sql/bin/SqlciErrors.txt
index f012c2e..de0959f 100644
--- a/core/sql/bin/SqlciErrors.txt
+++ b/core/sql/bin/SqlciErrors.txt
@@ -2033,6 +2033,7 @@ drop the default context
11152 ZZZZZ 99999 BEGINNER MINOR LOGONLY Unable to convert TypeInfo $0~int0 for column or parameter number $1~int1 from UDF compiler interface into an SQL internal type. Details: $2~string0.
11153 ZZZZZ 99999 BEGINNER MINOR LOGONLY Unable to produce a query plan. This could be related to a TMUDF that failed to negotiate a query plan with the optimizer. One possible reason: $0~string0.
11154 ZZZZZ 99999 BEGINNER MINOR LOGONLY Ordinal number $0~int0 used in $1~string0 clause of a UDF must be an integer ranging from 1 to the number of columns ($2~int1 in this case).
+11155 ZZZZZ 99999 BEGINNER MINOR LOGONLY A TMUDF must produce at least one output column.
11200 ZZZZZ 99999 UUUUUUUU UUUUU UUUUUUU -----First Language Manager Error -----
11201 ZZZZZ 99999 ADVANCED MAJOR DBADMIN Language Manager initialization failed. Details: Errors occurred while loading Java class $0~String0.
11202 ZZZZZ 99999 ADVANCED MAJOR DBADMIN Language Manager initialization failed. Details: Unable to initialize the Java virtual machine. $0~string0
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a3477192/core/sql/common/ExprNode.cpp
----------------------------------------------------------------------
diff --git a/core/sql/common/ExprNode.cpp b/core/sql/common/ExprNode.cpp
index fce46c9..7c96759 100644
--- a/core/sql/common/ExprNode.cpp
+++ b/core/sql/common/ExprNode.cpp
@@ -685,6 +685,7 @@ NABoolean OperatorType::match(OperatorTypeEnum wildcard) const
case REL_TABLE_MAPPING_UDF:
case REL_TABLE_MAPPING_BUILTIN_LOG_READER:
case REL_TABLE_MAPPING_BUILTIN_TIMESERIES:
+ case REL_TABLE_MAPPING_BUILTIN_JDBC:
return TRUE;
default:
return FALSE;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a3477192/core/sql/common/OperTypeEnum.h
----------------------------------------------------------------------
diff --git a/core/sql/common/OperTypeEnum.h b/core/sql/common/OperTypeEnum.h
index 6e5d2ae..12cc0cf 100644
--- a/core/sql/common/OperTypeEnum.h
+++ b/core/sql/common/OperTypeEnum.h
@@ -92,6 +92,7 @@ enum OperatorTypeEnum {
REL_TABLE_MAPPING_UDF,
REL_TABLE_MAPPING_BUILTIN_LOG_READER,
REL_TABLE_MAPPING_BUILTIN_TIMESERIES,
+ REL_TABLE_MAPPING_BUILTIN_JDBC,
REL_ANY_TABLE_MAPPING_UDF,
REL_ISOLATED_NON_TABLE_UDR,
REL_ISOLATED_SCALAR_UDF,
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a3477192/core/sql/optimizer/RelRoutine.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/RelRoutine.cpp b/core/sql/optimizer/RelRoutine.cpp
index f430e79..b7a0385 100644
--- a/core/sql/optimizer/RelRoutine.cpp
+++ b/core/sql/optimizer/RelRoutine.cpp
@@ -1032,6 +1032,8 @@ OperatorTypeEnum PredefinedTableMappingFunction::nameIsAPredefinedTMF(const Corr
return REL_TABLE_MAPPING_BUILTIN_LOG_READER;
else if (funcName == "TIMESERIES")
return REL_TABLE_MAPPING_BUILTIN_TIMESERIES;
+ else if (funcName == "JDBC")
+ return REL_TABLE_MAPPING_BUILTIN_JDBC;
else
// none of the built-in names matched, so it must be a UDF
return REL_TABLE_MAPPING_UDF;
@@ -1045,6 +1047,8 @@ const NAString PredefinedTableMappingFunction::getText() const
return "event_log_reader";
case REL_TABLE_MAPPING_BUILTIN_TIMESERIES:
return "timeseries";
+ case REL_TABLE_MAPPING_BUILTIN_JDBC:
+ return "jdbc_udf";
default:
CMPASSERT(0);
@@ -1081,33 +1085,37 @@ NARoutine * PredefinedTableMappingFunction::getRoutineMetadata(
BindWA *bindWA)
{
NARoutine *result = NULL;
+ ComRoutineLanguage lang = COM_LANGUAGE_CPP;
+ ComRoutineParamStyle paramStyle = COM_STYLE_CPP_OBJ;
+ const char *externalName = NULL;
+ // By default, predefined UDRs share a DLL.
+ const char *libraryFileName = "libudr_predef.so";
NAString libraryPath;
// the libraries for predefined UDRs are in the regular
// library directory $MY_SQROOT/export/lib${SQ_MBTYPE}
libraryPath += getenv("MY_SQROOT");
libraryPath += "/export/lib";
- libraryPath += getenv("SQ_MBTYPE");
switch (getOperatorType())
{
case REL_TABLE_MAPPING_BUILTIN_LOG_READER:
+ externalName = "TRAF_CPP_EVENT_LOG_READER";
+ libraryPath += getenv("SQ_MBTYPE");
+ break;
+
case REL_TABLE_MAPPING_BUILTIN_TIMESERIES:
- {
- // produce a very simple NARoutine, most of the
- // error checking and determination of output
- // columns is done by the compiler interface of
- // this predefined table mapping function
- result = new(bindWA->wHeap()) NARoutine(routineName,
- bindWA->wHeap());
- if (getOperatorType() == REL_TABLE_MAPPING_BUILTIN_LOG_READER)
- result->setExternalName("TRAF_CPP_EVENT_LOG_READER");
- else
- result->setExternalName("TRAF_CPP_TIMESERIES");
- result->setLanguage(COM_LANGUAGE_CPP);
- result->setRoutineType(COM_TABLE_UDF_TYPE);
- result->setParamStyle(COM_STYLE_CPP_OBJ);
- }
+ externalName = "TRAF_CPP_TIMESERIES";
+ libraryPath += getenv("SQ_MBTYPE");
+ break;
+
+ case REL_TABLE_MAPPING_BUILTIN_JDBC:
+ lang = COM_LANGUAGE_JAVA;
+ paramStyle = COM_STYLE_JAVA_OBJ;
+ externalName = "org.trafodion.sql.udr.predef.JDBCUDR";
+ libraryPath += "/trafodion-sql-";
+ libraryPath += getenv("TRAFODION_VER");
+ libraryPath += ".jar";
break;
default:
@@ -1118,9 +1126,18 @@ NARoutine * PredefinedTableMappingFunction::getRoutineMetadata(
return NULL;
}
- // by default, predefined UDRs share a DLL
-
- result->setFile("libudr_predef.so");
+ // Produce a very simple NARoutine, most of the
+ // error checking and determination of output
+ // columns is done by the compiler interface of
+ // this predefined table mapping function.
+
+ result = new(bindWA->wHeap()) NARoutine(routineName,
+ bindWA->wHeap());
+ result->setExternalName(externalName);
+ result->setLanguage(lang);
+ result->setRoutineType(COM_TABLE_UDF_TYPE);
+ result->setParamStyle(paramStyle);
+ result->setFile(libraryFileName);
result->setExternalPath(libraryPath);
return result;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a3477192/core/sql/optimizer/UdfDllInteraction.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/UdfDllInteraction.cpp b/core/sql/optimizer/UdfDllInteraction.cpp
index edeebe3..939c3ce 100644
--- a/core/sql/optimizer/UdfDllInteraction.cpp
+++ b/core/sql/optimizer/UdfDllInteraction.cpp
@@ -205,6 +205,12 @@ NABoolean TMUDFDllInteraction::describeParamsAndMaxOutputs(
bindWA->setErrStatus();
return FALSE;
}
+ if (outColArray->entries() == 0)
+ {
+ *(CmpCommon::diags()) << DgSqlCode(-11155);
+ bindWA->setErrStatus();
+ return FALSE;
+ }
tmudfNode->setOutputParams(outColArray);
@@ -1037,8 +1043,8 @@ tmudr::UDRInvocationInfo *TMUDFInternalSetup::createInvocationInfoFromRelExpr(
constVal->getOffsetsInBuffer(nullIndOffset, vcLenOffset, dataOffset);
result->nonConstActualParameters().getColumn(i).getType().setOffsets(
- (nullIndOffset > 0 ? nextOffset + nullIndOffset : -1),
- (vcLenOffset > 0 ? nextOffset + vcLenOffset : -1),
+ (nullIndOffset >= 0 ? nextOffset + nullIndOffset : -1),
+ (vcLenOffset >= 0 ? nextOffset + vcLenOffset : -1),
nextOffset + dataOffset);
nextOffset += totalSize;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a3477192/core/sql/pom.xml
----------------------------------------------------------------------
diff --git a/core/sql/pom.xml b/core/sql/pom.xml
index 579a3ab..50616e4 100755
--- a/core/sql/pom.xml
+++ b/core/sql/pom.xml
@@ -64,6 +64,11 @@
<version>${hive.version}</version>
</dependency>
<dependency>
+ <groupId>org.apache.hive</groupId>
+ <artifactId>hive-exec</artifactId>
+ <version>${hive.version}</version>
+ </dependency>
+ <dependency>
<groupId>org.apache.thrift</groupId>
<artifactId>libthrift</artifactId>
<version>${thrift.version}</version>
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a3477192/core/sql/regress/tools/runregr_udr.ksh
----------------------------------------------------------------------
diff --git a/core/sql/regress/tools/runregr_udr.ksh b/core/sql/regress/tools/runregr_udr.ksh
index e2c1a47..5852c15 100755
--- a/core/sql/regress/tools/runregr_udr.ksh
+++ b/core/sql/regress/tools/runregr_udr.ksh
@@ -850,6 +850,13 @@ if [ $LINUX -ne 0 ]; then
export CLASSPATH=$MY_SQROOT/export/lib/hpt4jdbc.jar:$CLASSPATH:.
fi
+export JDBC_T4_URL="jdbc:t4jdbc://localhost:23400/:"
+if [ -r $MY_SQROOT/sql/scripts/swenv.sh ]; then
+ # use a custom port for the JDBC Type 4 driver
+ . $MY_SQROOT/sql/scripts/swenv.sh
+ export JDBC_T4_URL="jdbc:t4jdbc://localhost:${MY_DCS_MASTER_PORT}/:"
+fi
+
# if the regressions are running on NSK, start the ODBC server if
# we are not running diffs_only
if [ $USE_NDCS -ne 0 -a $DIFFS_ONLY -eq 0 -a $SEABASE_REGRESS -eq 0 ]; then
[7/7] incubator-trafodion git commit: [TRAFODION-1581] TMUDF for JDBC
queries
Posted by hz...@apache.org.
[TRAFODION-1581] TMUDF for JDBC queries
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/d61ac8a1
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/d61ac8a1
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/d61ac8a1
Branch: refs/heads/master
Commit: d61ac8a1c06c83e4f384448686ad542e5d1cc2c9
Parents: 99091d7 59ce2b7
Author: Hans Zeller <hz...@apache.org>
Authored: Thu Dec 10 23:21:21 2015 +0000
Committer: Hans Zeller <hz...@apache.org>
Committed: Thu Dec 10 23:21:21 2015 +0000
----------------------------------------------------------------------
core/Makefile | 4 +-
core/sqf/sql/scripts/install_local_drill | 114 +++
core/sqf/sql/scripts/install_local_hadoop | 41 +-
core/sql/bin/SqlciErrors.txt | 1 +
core/sql/common/ExprNode.cpp | 1 +
core/sql/common/OperTypeEnum.h | 24 +-
core/sql/optimizer/GroupAttr.cpp | 179 +++++
core/sql/optimizer/GroupAttr.h | 4 +
core/sql/optimizer/ImplRule.cpp | 16 +-
core/sql/optimizer/ItemConstr.h | 39 ++
core/sql/optimizer/ItemExpr.cpp | 36 +-
core/sql/optimizer/ItemExpr.h | 4 +-
core/sql/optimizer/OptPhysRelExpr.cpp | 3 +-
core/sql/optimizer/PhyProp.cpp | 2 +-
core/sql/optimizer/RelRoutine.cpp | 55 +-
core/sql/optimizer/UdfDllInteraction.cpp | 10 +-
core/sql/optimizer/ValueDesc.cpp | 194 ++++--
core/sql/optimizer/ValueDesc.h | 14 +-
core/sql/pom.xml | 5 +
core/sql/regress/compGeneral/EXPECTED071 | 47 ++
core/sql/regress/compGeneral/TEST071 | 22 +
core/sql/regress/executor/EXPECTED130 | 28 +-
core/sql/regress/executor/TEST016 | 5 +
core/sql/regress/executor/TEST130 | 7 +-
core/sql/regress/seabase/EXPECTED010 | 84 +++
core/sql/regress/seabase/TEST010 | 4 +
core/sql/regress/seabase/TEST014 | 1 +
core/sql/regress/tools/runregr_udr.ksh | 7 +
core/sql/regress/udr/EXPECTED002 | 48 +-
core/sql/regress/udr/TEST002 | 18 +
core/sql/sqlci/SqlCmd.cpp | 1 +
core/sql/sqludr/doxygen_tmudr.1.6.config | 4 +-
core/sql/sqludr/sqludr.cpp | 2 +-
core/sql/sqludr/sqludr.h | 16 +-
.../trafodion/sql/udr/UDRInvocationInfo.java | 17 +-
.../org/trafodion/sql/udr/predef/JDBCUDR.java | 690 +++++++++++++++++++
36 files changed, 1569 insertions(+), 178 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/d61ac8a1/core/sql/bin/SqlciErrors.txt
----------------------------------------------------------------------
[3/7] incubator-trafodion git commit: [TRAFODION-1581] TMUDF for JDBC
queries
Posted by hz...@apache.org.
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a3477192/core/sql/regress/udr/TEST002
----------------------------------------------------------------------
diff --git a/core/sql/regress/udr/TEST002 b/core/sql/regress/udr/TEST002
index 97fb254..79d8250 100644
--- a/core/sql/regress/udr/TEST002
+++ b/core/sql/regress/udr/TEST002
@@ -230,6 +230,24 @@ from udf(timeseries(table(select * from t002_Timeseries
'VAL2', 'FLi',
'VAL2', 'LLi'));
+-- JDBC queries through a TMUDF. Note that this requires the Trafodion T4
+-- driver jar to exist in $MY_SQROOT/udr/external_libs.
+sh mkdir -p $$MY_SQROOT$$/udr/external_libs;
+sh cp $$MY_SQROOT$$/export/lib/jdbcT2.jar $$MY_SQROOT$$/udr/external_libs;
+
+-- with default ports, $$QUOTE$$$$JDBC_T4_URL$$$$QUOTE$$ should be
+-- 'jdbc:t4jdbc://localhost:23400/:'
+prepare s_traf from
+select *
+from udf(jdbc('jdbcT4.jar',
+ 'org.trafodion.jdbc.t4.T4Driver',
+ $$QUOTE$$$$JDBC_T4_URL$$$$QUOTE$$,
+ 'any', -- no user id
+ 'any', -- no password
+ 'source',
+ 'select * from (values (''Hello'', ''World''), (''Hallo'', ''Welt'')) T(a,b)'));
+execute s_traf;
+
-- negative tests
select * from udf(timeseries());
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a3477192/core/sql/sqlci/SqlCmd.cpp
----------------------------------------------------------------------
diff --git a/core/sql/sqlci/SqlCmd.cpp b/core/sql/sqlci/SqlCmd.cpp
index 855652b..6c09c0d 100644
--- a/core/sql/sqlci/SqlCmd.cpp
+++ b/core/sql/sqlci/SqlCmd.cpp
@@ -642,6 +642,7 @@ char * SqlCmd::replacePattern(SqlciEnv * sqlci_env, char * str)
}
j = 0;
+ skipChar = FALSE;
state = CONSUME_CHAR;
break;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a3477192/core/sql/sqludr/doxygen_tmudr.1.6.config
----------------------------------------------------------------------
diff --git a/core/sql/sqludr/doxygen_tmudr.1.6.config b/core/sql/sqludr/doxygen_tmudr.1.6.config
index b795389..158b1f6 100644
--- a/core/sql/sqludr/doxygen_tmudr.1.6.config
+++ b/core/sql/sqludr/doxygen_tmudr.1.6.config
@@ -31,14 +31,14 @@ PROJECT_NAME = tmudr
# This could be handy for archiving the generated documentation or
# if some version control system is used.
-PROJECT_NUMBER = 2.0
+PROJECT_NUMBER = 1.3.0
# The OUTPUT_DIRECTORY tag is used to specify the (relative or absolute)
# base path where the generated documentation will be put.
# If a relative path is entered, it will be relative to the location
# where doxygen was started. If left blank the current directory will be used.
-OUTPUT_DIRECTORY = tmudr_2.0
+OUTPUT_DIRECTORY = tmudr_1.3.0
# If the CREATE_SUBDIRS tag is set to YES, then doxygen will create
# 4096 sub-directories (in 2 levels) under the output directory of each output
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a3477192/core/sql/sqludr/sqludr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/sqludr/sqludr.cpp b/core/sql/sqludr/sqludr.cpp
index 85e4259..f1741b9 100644
--- a/core/sql/sqludr/sqludr.cpp
+++ b/core/sql/sqludr/sqludr.cpp
@@ -7761,7 +7761,7 @@ void UDR::processData(UDRInvocationInfo &info,
*
* This method is called in debug Trafodion builds when certain
* flags are set in the UDR_DEBUG_FLAGS CQD (CONTROL QUERY DEFAULT).
- * See https://wiki.trafodion.org/wiki/index.php/Tutorial:_The_object-oriented_UDF_interface#Debugging_UDF_code
+ * See https://cwiki.apache.org/confluence/display/TRAFODION/Tutorial%3A+The+object-oriented+UDF+interface#Tutorial:Theobject-orientedUDFinterface-DebuggingUDFcode
* for details.
*
* The default implementation prints out the process id and then
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a3477192/core/sql/sqludr/sqludr.h
----------------------------------------------------------------------
diff --git a/core/sql/sqludr/sqludr.h b/core/sql/sqludr/sqludr.h
index 6e094a7..ba90eab 100644
--- a/core/sql/sqludr/sqludr.h
+++ b/core/sql/sqludr/sqludr.h
@@ -464,7 +464,7 @@ typedef void (*SQLUDR_EmitRow) (char *rowData, /*IN*/
* values of parameters, UDR name, etc.
*
* For an introduction, see
- * https://wiki.trafodion.org/wiki/index.php/Tutorial:_The_object-oriented_UDF_interface
+ * https://cwiki.apache.org/confluence/display/TRAFODION/Tutorial%3A+The+object-oriented+UDF+interface
*
*/
@@ -1489,14 +1489,16 @@ namespace tmudr
///< carry any state between rows it reads from its
///< table-valued inputs. It produces zero or more output
///< rows per input row. Because no state is kept between
- ///< rows, the Trafodion compiler can automatically push
- ///< predicates down to the table-valued inputs.
+ ///< rows, the Trafodion compiler can automatically
+ ///< parallelize execution and push predicates down to
+ ///< the table-valued inputs.
REDUCER ///< A reducer requires the data to be partitioned on
///< a set of columns. The UDF does not carry any state
///< between groups of rows with the same partition column
///< values, but it may carry state within such groups.
- ///< This allows the compiler to push predicates on the
- ///< partitioning column(s) down to table-valued inputs.
+ ///< This allows the compiler to parallelize execution and
+ ///< to push predicates on the partitioning column(s) down
+ ///< to table-valued inputs.
};
/**
@@ -1571,7 +1573,7 @@ namespace tmudr
*
* use cqd UDR_DEBUG_FLAGS 'num' in SQL to set these, add up
* the flags (in decimal) that you want to set. See
- * https://wiki.trafodion.org/wiki/index.php/Tutorial:_The_object-oriented_UDF_interface#Debugging_UDF_code
+ * https://cwiki.apache.org/confluence/display/TRAFODION/Tutorial%3A+The+object-oriented+UDF+interface#Tutorial:Theobject-orientedUDFinterface-DebuggingUDFcode
* for details.
*/
enum DebugFlags
@@ -1802,7 +1804,7 @@ namespace tmudr
* UDR writers can create a derived class and implement these methods
* for their specific UDR. The base class also has default methods
* for all but the runtime call. See
- * https://wiki.trafodion.org/wiki/index.php/Tutorial:_The_object-oriented_UDF_interface
+ * https://cwiki.apache.org/confluence/display/TRAFODION/Tutorial%3A+The+object-oriented+UDF+interface
* for examples.
*
* To use this interface, the UDR writer must provide a function
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a3477192/core/sql/src/main/java/org/trafodion/sql/udr/UDRInvocationInfo.java
----------------------------------------------------------------------
diff --git a/core/sql/src/main/java/org/trafodion/sql/udr/UDRInvocationInfo.java b/core/sql/src/main/java/org/trafodion/sql/udr/UDRInvocationInfo.java
index 0c16ad1..478a993 100644
--- a/core/sql/src/main/java/org/trafodion/sql/udr/UDRInvocationInfo.java
+++ b/core/sql/src/main/java/org/trafodion/sql/udr/UDRInvocationInfo.java
@@ -51,15 +51,17 @@ public class UDRInvocationInfo extends TMUDRSerializableObject
* carry any state between rows it reads from its
* table-valued inputs. It produces zero or more output
* rows per input row. Because no state is kept between
- * rows, the Trafodion compiler can automatically push
- * predicates down to the table-valued inputs. */
+ * rows, the Trafodion compiler can automatically
+ * parallelize execution and push predicates down to
+ * the table-valued inputs. */
MAPPER,
/** A reducer requires the data to be partitioned on
* a set of columns. The UDF does not carry any state
* between groups of rows with the same partition column
* values, but it may carry state within such groups.
- * This allows the compiler to push predicates on the
- * partitioning column(s) down to table-valued inputs. */
+ * This allows the compiler to parallelize execution and
+ * to push predicates on the partitioning column(s) down
+ * to table-valued inputs. */
REDUCER;
private static FuncType[] allValues = values();
@@ -950,12 +952,7 @@ public class UDRInvocationInfo extends TMUDRSerializableObject
CallPhase.COMPILER_PLAN_CALL,
"UDRInvocationInfo::setUDRWriterCompileTimeData()");
- // for now we can't allow this
- throw new UDRException(
- 38912,
- "UDRInvocationInfo::setUDRWriterCompileTimeData() not yet supported");
-
- // udrWriterCompileTimeData_ = compileTimeData;
+ udrWriterCompileTimeData_ = compileTimeData;
}
/**
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a3477192/core/sql/src/main/java/org/trafodion/sql/udr/predef/JDBCUDR.java
----------------------------------------------------------------------
diff --git a/core/sql/src/main/java/org/trafodion/sql/udr/predef/JDBCUDR.java b/core/sql/src/main/java/org/trafodion/sql/udr/predef/JDBCUDR.java
new file mode 100644
index 0000000..31fb72a
--- /dev/null
+++ b/core/sql/src/main/java/org/trafodion/sql/udr/predef/JDBCUDR.java
@@ -0,0 +1,690 @@
+/**********************************************************************
+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.
+**********************************************************************/
+
+/***************************************************
+ * A TMUDF that executes a generic JDBC query
+ * and returns the result of the one SQL statement
+ * in the list that produces results as a table-valued
+ * output
+ *
+ * Invocation (all arguments are strings):
+ *
+ * select ... from udf(JDBC(
+ * <name of JDBC driver jar>,
+ * <name of JDBC driver class in the jar>,
+ * <connection string>,
+ * <user name>,
+ * <password>,
+ * <statement_type>,
+ * <sql statement 1>
+ * [ , <sql statements 2 ...n> ] )) ...
+ *
+ * The first 7 arguments are required and must be
+ * string literals that are available at compile
+ * time.
+ * Statement type:
+ * 'source': This statement produces a result
+ * (only type allowed at this time)
+ * (may support "target" to insert
+ * into a table via JDBC later)
+ *
+ * Note that only one of the SQL statements can be
+ * a select or other result-producing statements.
+ * The others can perform setup and cleanup
+ * operations, if necessary (e.g. create table,
+ * insert, select, drop table).
+ *
+ * For an example, see file
+ * core/sql/regress/udr/TEST002.
+ ***************************************************/
+
+package org.trafodion.sql.udr.predef;
+
+import org.trafodion.sql.udr.*;
+import java.sql.*;
+import java.util.Vector;
+import java.lang.Math;
+import java.net.URL;
+import java.net.URLClassLoader;
+import java.nio.file.Path;
+import java.nio.file.Paths;
+import java.io.PrintStream;
+import java.util.Properties;
+import java.util.logging.Logger;
+
+class JDBCUDR extends UDR
+{
+ // class used to connect, both at compile and at runtime
+ static class JdbcConnectionInfo
+ {
+ String driverJar_;
+ String driverClassName_;
+ String connectionString_;
+ String username_;
+ String password_;
+ boolean debug_;
+
+ Connection conn_;
+
+ public void setJar(String jar)
+ { driverJar_ = jar; }
+ public void setClass(String className)
+ { driverClassName_ = className; }
+ public void setConnString(String connString)
+ { connectionString_ = connString; }
+ public void setUsername(String userName)
+ { username_ = userName; }
+ public void setPassword(String password)
+ { password_ = password; }
+ public void setDebug(boolean debug) { debug_ = debug; }
+
+ public Connection connect() throws UDRException
+ {
+ try {
+ Path driverJarPath = Paths.get(driverJar_);
+ Path sandBoxPath = Paths.get(System.getenv("MY_SQROOT"), "udr", "external_libs");
+ URLClassLoader jdbcJarLoader = null;
+ URL jarClassPath[] = new URL[1];
+
+ // for security reasons, we sandbox the allowed driver jars
+ // into $MY_SQROOT/export/lib/udr/external_libs
+ driverJarPath = driverJarPath.normalize();
+ if (driverJarPath.isAbsolute())
+ {
+ if (! driverJarPath.startsWith(sandBoxPath))
+ throw new UDRException(
+ 38010,
+ "The jar name of the JDBC driver must be a name relative to %s, got %s",
+ System.getenv("MY_SQROOT")+"/udr/external_libs",
+ driverJar_);
+ }
+ else
+ driverJarPath = sandBoxPath.resolve(driverJarPath);
+
+ // Create a class loader that can access the
+ // jar file specified by the caller.
+ jarClassPath[0] = driverJarPath.toUri().toURL();
+ jdbcJarLoader = new URLClassLoader(
+ jarClassPath,
+ this.getClass().getClassLoader());
+
+ // go through an intermediary driver, since the DriverManager
+ // will not accept classes that are not loaded by the default
+ // class loader
+ Driver d = (Driver) Class.forName(driverClassName_, true, jdbcJarLoader).newInstance();
+ DriverManager.registerDriver(new URLDriver(d));
+ conn_ = DriverManager.getConnection(connectionString_,
+ username_,
+ password_);
+ return conn_;
+ }
+ catch(Exception e) {
+ if (debug_)
+ {
+ System.out.println("Debug: Exception during connect:");
+ try { e.printStackTrace(System.out); }
+ catch (Exception e2) {}
+ }
+ throw new UDRException(
+ 38020,
+ "Exception during connect: %s",
+ e.getMessage());
+ }
+ }
+
+ public Connection getConnection() { return conn_; }
+
+ public void disconnect() throws SQLException
+ {
+ conn_.close();
+ conn_ = null;
+ }
+ };
+
+ // list of SQL statements to execute
+ static class SQLStatementInfo
+ {
+ // list of SQL statements to execute
+ Vector<String> sqlStrings_;
+
+ // which of the above is the one that
+ // produces the table-valued result?
+ int resultStatementIndex_;
+
+ // prepared result-producing statement
+ PreparedStatement resultStatement_;
+
+ SQLStatementInfo()
+ {
+ sqlStrings_ = new Vector<String>();
+ resultStatementIndex_ = -1;
+ }
+
+ void addStatementText(String sqlText)
+ {
+ sqlStrings_.add(sqlText);
+ }
+
+ void addResultProducingStatement(PreparedStatement preparedStmt,
+ int resultStatementIndex)
+ {
+ resultStatement_ = preparedStmt;
+ resultStatementIndex_ = resultStatementIndex;
+ }
+
+ String getStatementText(int ix) { return sqlStrings_.get(ix); }
+ PreparedStatement getResultStatement(){ return resultStatement_; }
+ int getNumStatements() { return sqlStrings_.size(); }
+ int getResultStatementIndex() { return resultStatementIndex_; }
+ };
+
+ // Define data that gets passed between compiler phases
+ static class JdbcCompileTimeData extends UDRWriterCompileTimeData
+ {
+ JdbcConnectionInfo jci_;
+ SQLStatementInfo sqi_;
+
+ JdbcCompileTimeData()
+ {
+ jci_ = new JdbcConnectionInfo();
+ sqi_ = new SQLStatementInfo();
+ }
+ };
+
+ static class URLDriver implements Driver {
+ private Driver driver_;
+ URLDriver(Driver d) { driver_ = d; }
+ public boolean acceptsURL(String u) throws SQLException {
+ return driver_.acceptsURL(u);
+ }
+ public Connection connect(String u, Properties p) throws SQLException {
+ return driver_.connect(u, p);
+ }
+ public int getMajorVersion() {
+ return driver_.getMajorVersion();
+ }
+ public int getMinorVersion() {
+ return driver_.getMinorVersion();
+ }
+ public DriverPropertyInfo[] getPropertyInfo(String u, Properties p) throws SQLException {
+ return driver_.getPropertyInfo(u, p);
+ }
+ public boolean jdbcCompliant() {
+ return driver_.jdbcCompliant();
+ }
+ public Logger getParentLogger() throws SQLFeatureNotSupportedException {
+ return driver_.getParentLogger();
+ }
+ }
+
+ JdbcConnectionInfo getConnectionInfo(UDRInvocationInfo info) throws UDRException
+ {
+ return ((JdbcCompileTimeData) info.getUDRWriterCompileTimeData()).jci_;
+ }
+
+ SQLStatementInfo getSQLStatementInfo(UDRInvocationInfo info) throws UDRException
+ {
+ return ((JdbcCompileTimeData) info.getUDRWriterCompileTimeData()).sqi_;
+ }
+
+
+ // default constructor
+ public JDBCUDR()
+ {}
+
+ // a method to process the input parameters, this is
+ // used both at compile time and at runtime
+ private void handleInputParams(UDRInvocationInfo info,
+ JdbcConnectionInfo jci,
+ SQLStatementInfo sqi,
+ boolean isCompileTime)
+ throws UDRException
+ {
+ int numInParams = info.par().getNumColumns();
+
+ // Right now we don't support table inputs
+ if (isCompileTime && info.getNumTableInputs() != 0)
+ throw new UDRException(
+ 38300,
+ "%s must be called with no table-valued inputs",
+ info.getUDRName());
+
+ if (numInParams < 7)
+ throw new UDRException(
+ 38310,
+ "Expecting at least 7 parameters for %s UDR",
+ info.getUDRName());
+
+ // loop over scalar input parameters
+ for (int p=0; p<numInParams; p++)
+ {
+ if (isCompileTime &&
+ ! info.par().isAvailable(p))
+ throw new UDRException(
+ 38320,
+ "Parameter %d of %s must be a compile time constant",
+ p+1,
+ info.getUDRName());
+
+ String paramValue = info.par().getString(p);
+
+ switch (p)
+ {
+ case 0:
+ jci.setJar(paramValue);
+ break;
+
+ case 1:
+ jci.setClass(paramValue);
+ break;
+
+ case 2:
+ jci.setConnString(paramValue);
+ break;
+
+ case 3:
+ jci.setUsername(paramValue);
+ break;
+
+ case 4:
+ jci.setPassword(paramValue);
+ break;
+
+ case 5:
+ // Only statement type supported
+ // so far is select, we may support insert later
+ if (paramValue.compareToIgnoreCase("source") != 0)
+ throw new UDRException(
+ 38330,
+ "The only statement type supported so far is 'source' in parameter 6 of %s",
+ info.getUDRName());
+ break;
+
+ default:
+ // SQL statement (there could be multiple)
+ sqi.addStatementText(paramValue);
+ break;
+
+ }
+
+ if (isCompileTime)
+ // add the actual parameter as a formal parameter
+ // (the formal parameter list is initially empty)
+ info.addFormalParameter(info.par().getColumn(p));
+ }
+
+ jci.setDebug(info.getDebugFlags() != 0);
+
+ // Prepare each provided statement. We will verify that
+ // only one of these statements produces result rows,
+ // which will become our table-valued output.
+ int numSQLStatements = sqi.getNumStatements();
+
+ // sanity check
+ if (numSQLStatements != numInParams-6)
+ throw new UDRException(383400, "internal error");
+
+ if (numSQLStatements < 1)
+ throw new UDRException(383500, "At least one SQL statement must be given in parameters 6 and following");
+
+ if (isCompileTime)
+ {
+ // walk through all statements, check whether they are
+ // valid by preparing them, and determine which one is
+ // the one that generates a result set
+ String currentStmtText = "";
+ try
+ {
+ jci.connect();
+
+ for (int s=0; s<numSQLStatements; s++)
+ {
+ currentStmtText = sqi.getStatementText(s);
+ // System.out.printf("Statement to prepare: %s\n", currentStmtText);
+ PreparedStatement preparedStmt =
+ jci.getConnection().prepareStatement(currentStmtText);
+ // if (preparedStmt != null)
+ // System.out.printf("Prepare was successful\n");
+ ParameterMetaData pmd = preparedStmt.getParameterMetaData();
+ if (pmd != null && pmd.getParameterCount() != 0)
+ throw new UDRException(
+ 38360,
+ "Statement %s requires %d input parameters, which is not supported",
+ currentStmtText, pmd.getParameterCount());
+ ResultSetMetaData desc = preparedStmt.getMetaData();
+
+ int numResultCols = desc.getColumnCount();
+ // System.out.printf("Number of output columns: %d", numResultCols);
+
+ if (numResultCols > 0)
+ {
+ if (sqi.getResultStatementIndex() >= 0)
+ throw new UDRException(
+ 38370,
+ "More than one of the statements provided produce output, this is not supported (%d and %d)",
+ sqi.getResultStatementIndex()+1,
+ s+1);
+
+ // we found the statement that is producing the result
+ sqi.addResultProducingStatement(preparedStmt, s);
+
+ // now add the output columns
+ for (int c=0; c<numResultCols; c++)
+ {
+ String colName = desc.getColumnLabel(c+1);
+ TypeInfo udrType = getUDRTypeFromJDBCType(desc, c+1);
+ info.out().addColumn(new ColumnInfo(colName, udrType));
+ }
+ }
+ }
+ jci.disconnect();
+ }
+ catch (SQLException e)
+ {
+ throw new UDRException(
+ 38380,
+ "SQL Exception when preparing SQL statement %s. Exception text: %s",
+ currentStmtText, e.getMessage());
+ }
+ }
+ }
+
+ TypeInfo getUDRTypeFromJDBCType(ResultSetMetaData desc,
+ int colNumOneBased) throws UDRException
+ {
+ TypeInfo result;
+
+ final int maxLength = 100000;
+
+ int colJDBCType;
+
+ // the ingredients to make a UDR type and their default values
+ TypeInfo.SQLTypeCode sqlType = TypeInfo.SQLTypeCode.UNDEFINED_SQL_TYPE;
+ int length = 0;
+ boolean nullable = false;
+ int scale = 0;
+ TypeInfo.SQLCharsetCode charset = TypeInfo.SQLCharsetCode.CHARSET_UCS2;
+ TypeInfo.SQLIntervalCode intervalCode = TypeInfo.SQLIntervalCode.UNDEFINED_INTERVAL_CODE;
+ int precision = 0;
+ TypeInfo.SQLCollationCode collation = TypeInfo.SQLCollationCode.SYSTEM_COLLATION;
+
+ try {
+ colJDBCType = desc.getColumnType(colNumOneBased);
+ nullable = (desc.isNullable(colNumOneBased) != ResultSetMetaData.columnNoNulls);
+
+ // map the JDBC type to a Trafodion UDR parameter type
+ switch (colJDBCType)
+ {
+ case java.sql.Types.SMALLINT:
+ case java.sql.Types.TINYINT:
+ case java.sql.Types.BOOLEAN:
+ if (desc.isSigned(colNumOneBased))
+ sqlType = TypeInfo.SQLTypeCode.SMALLINT;
+ else
+ sqlType = TypeInfo.SQLTypeCode.SMALLINT_UNSIGNED;
+ break;
+
+ case java.sql.Types.INTEGER:
+ if (desc.isSigned(colNumOneBased))
+ sqlType = TypeInfo.SQLTypeCode.INT;
+ else
+ sqlType = TypeInfo.SQLTypeCode.INT_UNSIGNED;
+ break;
+
+ case java.sql.Types.BIGINT:
+ sqlType = TypeInfo.SQLTypeCode.LARGEINT;
+ break;
+
+ case java.sql.Types.DECIMAL:
+ case java.sql.Types.NUMERIC:
+ if (desc.isSigned(colNumOneBased))
+ sqlType = TypeInfo.SQLTypeCode.NUMERIC;
+ else
+ sqlType = TypeInfo.SQLTypeCode.NUMERIC_UNSIGNED;
+ precision = desc.getPrecision(colNumOneBased);
+ scale = desc.getScale(colNumOneBased);
+ break;
+
+ case java.sql.Types.REAL:
+ sqlType = TypeInfo.SQLTypeCode.REAL;
+ break;
+
+ case java.sql.Types.DOUBLE:
+ case java.sql.Types.FLOAT:
+ sqlType = TypeInfo.SQLTypeCode.DOUBLE_PRECISION;
+ break;
+
+ case java.sql.Types.CHAR:
+ case java.sql.Types.NCHAR:
+ sqlType = TypeInfo.SQLTypeCode.CHAR;
+ length = Math.min(desc.getPrecision(colNumOneBased), maxLength);
+ charset = TypeInfo.SQLCharsetCode.CHARSET_UCS2;
+ break;
+
+ case java.sql.Types.VARCHAR:
+ case java.sql.Types.NVARCHAR:
+ sqlType = TypeInfo.SQLTypeCode.VARCHAR;
+ length = Math.min(desc.getPrecision(colNumOneBased), maxLength);
+ charset = TypeInfo.SQLCharsetCode.CHARSET_UCS2;
+ break;
+
+ case java.sql.Types.DATE:
+ sqlType = TypeInfo.SQLTypeCode.DATE;
+ break;
+
+ case java.sql.Types.TIME:
+ sqlType = TypeInfo.SQLTypeCode.TIME;
+ break;
+
+ case java.sql.Types.TIMESTAMP:
+ sqlType = TypeInfo.SQLTypeCode.TIMESTAMP;
+ scale = 3;
+ break;
+
+ // BLOB - not supported yet, map to varchar
+ // case java.sql.Types.BLOB:
+ // sqlType = TypeInfo.SQLTypeCode.BLOB;
+ // break;
+
+ // CLOB - not supported yet, map to varchar
+ // case java.sql.Types.CLOB:
+ // sqlType = TypeInfo.SQLTypeCode.CLOB;
+ // break;
+
+ case java.sql.Types.ARRAY:
+ case java.sql.Types.BINARY:
+ case java.sql.Types.BIT:
+ case java.sql.Types.BLOB:
+ case java.sql.Types.DATALINK:
+ case java.sql.Types.DISTINCT:
+ case java.sql.Types.JAVA_OBJECT:
+ case java.sql.Types.LONGVARBINARY:
+ case java.sql.Types.NULL:
+ case java.sql.Types.OTHER:
+ case java.sql.Types.REF:
+ case java.sql.Types.STRUCT:
+ case java.sql.Types.VARBINARY:
+ // these types produce a binary result, represented
+ // as varchar(n) character set iso88591
+ sqlType = TypeInfo.SQLTypeCode.VARCHAR;
+ length = Math.min(desc.getPrecision(colNumOneBased), maxLength);
+ charset = TypeInfo.SQLCharsetCode.CHARSET_ISO88591;
+ break;
+
+ case java.sql.Types.LONGVARCHAR:
+ case java.sql.Types.LONGNVARCHAR:
+ case java.sql.Types.CLOB:
+ case java.sql.Types.NCLOB:
+ case java.sql.Types.ROWID:
+ case java.sql.Types.SQLXML:
+ // these types produce a varchar(n) character set utf8 result
+ sqlType = TypeInfo.SQLTypeCode.VARCHAR;
+ length = Math.min(desc.getPrecision(colNumOneBased), maxLength);
+ charset = TypeInfo.SQLCharsetCode.CHARSET_UCS2;
+ break;
+ }
+ } catch (SQLException e) {
+ throw new UDRException(
+ 38500,
+ "Error determinging the type of output column %d: ",
+ colNumOneBased,
+ e.getMessage());
+ }
+
+ result = new TypeInfo(
+ sqlType,
+ length,
+ nullable,
+ scale,
+ charset,
+ intervalCode,
+ precision,
+ collation);
+
+ return result;
+ }
+
+ // determine output columns dynamically at compile time
+ @Override
+ public void describeParamsAndColumns(UDRInvocationInfo info)
+ throws UDRException
+ {
+ // create an object with common info for this
+ // UDF invocation that we will carry through the
+ // compilation phases
+ info.setUDRWriterCompileTimeData(new JdbcCompileTimeData());
+
+ // retrieve the compile time data, we will do this for
+ // every compile phase
+ JdbcConnectionInfo jci = getConnectionInfo(info);
+ SQLStatementInfo sqi = getSQLStatementInfo(info);
+
+ // process input parameters
+ handleInputParams(info, jci, sqi, true);
+ }
+
+ // override the runtime method
+ @Override
+ public void processData(UDRInvocationInfo info,
+ UDRPlanInfo plan)
+ throws UDRException
+ {
+ // retrieve the compile time data, we will do this for
+ // every compile phase
+ JdbcConnectionInfo jci = new JdbcConnectionInfo();
+ SQLStatementInfo sqi = new SQLStatementInfo();
+ int numCols = info.out().getNumColumns();
+
+ // process input parameters (again, now at runtime)
+ handleInputParams(info, jci, sqi, false);
+
+ int numSQLStatements = sqi.getNumStatements();
+ int numSQLResultSets = 0;
+ String stmtText = null;
+
+ try {
+ Connection conn = jci.connect();
+ Statement stmt = conn.createStatement();
+
+ for (int s=0; s<numSQLStatements; s++)
+ {
+ stmtText = sqi.getStatementText(s);
+
+ boolean hasResultSet = stmt.execute(stmtText);
+
+ if (hasResultSet)
+ {
+ ResultSet rs = stmt.getResultSet();
+ numSQLResultSets++;
+
+ if (numSQLResultSets > 1)
+ throw new UDRException(
+ 38700,
+ "More than one result set returned by UDF %s",
+ info.getUDRName());
+
+ if (rs.getMetaData().getColumnCount() != numCols)
+ throw new UDRException(
+ 38702,
+ "Number of columns returned by UDF %s (%d) differs from the number determined at compile time (%d)",
+ info.getUDRName(),
+ rs.getMetaData().getColumnCount(),
+ numCols);
+
+ while (rs.next())
+ {
+ for (int c=0; c<numCols; c++)
+ {
+ TypeInfo typ = info.out().getColumn(c).getType();
+
+ switch (typ.getSQLTypeSubClass())
+ {
+ case FIXED_CHAR_TYPE:
+ case VAR_CHAR_TYPE:
+ info.out().setString(c, rs.getString(c+1));
+ break;
+
+ case EXACT_NUMERIC_TYPE:
+ info.out().setLong(c, rs.getLong(c+1));
+ break;
+
+ case APPROXIMATE_NUMERIC_TYPE:
+ info.out().setDouble(c, rs.getDouble(c+1));
+ break;
+
+ case DATE_TYPE:
+ info.out().setTime(c, rs.getDate(c+1));
+ break;
+
+ case TIME_TYPE:
+ info.out().setTime(c, rs.getTime(c+1));
+ break;
+
+ case TIMESTAMP_TYPE:
+ info.out().setTime(c, rs.getTimestamp(c+1));
+ break;
+
+ case LOB_SUB_CLASS:
+ throw new UDRException(38710, "LOB parameters not yet supported");
+
+ default:
+ throw new UDRException(38720, "Unexpected data type encountered");
+
+ } // switch
+
+ if (rs.wasNull())
+ info.out().setNull(c);
+ } // loop over columns
+
+ // produce a result row
+ emitRow(info);
+
+ } // loop over result rows
+ } // statement produces a result set
+ } // loop over statements
+ jci.disconnect();
+ } catch (SQLException e) {
+ throw new UDRException(
+ 38730,
+ "Error preparing statement %s at runtime: %s",
+ stmtText,
+ e.getMessage());
+ }
+ }
+};
[4/7] incubator-trafodion git commit: [TRAFODION-1581] TMUDF for JDBC
queries
Posted by hz...@apache.org.
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a3477192/core/sql/regress/udr/EXPECTED002
----------------------------------------------------------------------
diff --git a/core/sql/regress/udr/EXPECTED002 b/core/sql/regress/udr/EXPECTED002
index 79603dd..0245a11 100644
--- a/core/sql/regress/udr/EXPECTED002
+++ b/core/sql/regress/udr/EXPECTED002
@@ -6570,17 +6570,17 @@ NUM_EVENTS NUM_LINES NUM_QUERY_IDS NUM_PARSE_ERRORS
+> and (log_file_line in (1,3,8,16,23) or parse_status <> ' ')
+>order by log_file_line;
-LOG_TS SEVERITY COMPONENT NODE_NUMBER CPU PIN PROCESS_NAME SQL_CODE QUERY_ID MESSAGE
LOG_FILE_NODE LOG_FILE_NAME LOG_FILE_LINE PARSE_STATUS
--------------------------- ---------- ------------------------ ----------- ----------- ----------- ------------ ----------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------- ------------
+LOG_TS SEVERITY COMPONENT NODE_NUMBER CPU PIN PROCESS_NAME SQL_CODE QUERY_ID MESSAGE
LOG_FILE_NODE LOG_FILE_NAME LOG_FILE_LINE PARSE_STATUS
+-------------------------- ---------- -------------------------------------------------- ----------- ----------- ----------- ------------ ----------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------- ------------
-2015-02-03 00:08:03.480000 INFO SQL.EXE 0 0 25816 $Z000L2L ? ? An executor process is launched.
0 master_exec_regr_999_99999.log 1
-2015-02-03 00:08:11.037000 INFO SQL.COMP 0 0 25952 $Z000L6H ? ? A compiler process is launched.
0 master_exec_regr_999_99999.log 3
-2015-02-03 00:08:31.884000 ERROR SQL.EXE 0 0 25816 $Z000L2L 11252 MXID11000025816212289682083477587000000000206U3333300_1438___SQLCI_DML_LAST__ *** ERROR[11252] Column not found (SQLSTATE 38900)
0 master_exec_regr_999_99999.log 8
-2015-02-03 00:08:33.418000 ERROR SQL.EXE 0 0 25816 $Z000L2L 11246 MXID11000025816212289682083477587000000000206U3333300_1593___SQLCI_DML_LAST__ *** ERROR[11246] An error occurred locating function 'SESSIONIZE_NON_EXISTENT' in library '/opt/home/zellerh/trafodion/core/sqf/rundir/udr/TEST001.dll'. 2015-02-03 00:this is a bad timestamp and should be appended to the previous message
0 master_exec_regr_999_99999.log 16
-2015-02-03 00:08:33.500000 ERROR ? ? ? ? ? ? ? this should produce a parse error with this message
0 master_exec_regr_999_99999.log 20 E
-2015-02-03 00:08:34.114000 ERROR SQL.EXE 0 0 25816 $Z000L2L 15001 MXID11000025816212289682083477587000000000206U3333300_1710___SQLCI_DML_LAST__ *** ERROR[15001] A syntax error occurred at or before: select * from udf(sessionize_dynamic(TABLE(SELECT userid, JULIANTIMESTAMP(ts) a s TS FROM clicks PARTITION BY userid ORDER BY ts), TABLE(SELECT userid, JULIANTIMESTAMP(ts) as TS FROM clicks PARTITION BY userid ORDER BY ts), cast('TS' as char(2)), 'USERID', 60000000)) XOX; ^ (409 characters from start of SQL statement)
0 master_exec_regr_999_99999.log 23
-2015-02-03 00:08:34.461000 ERROR SQL.EXE 0 0 25816 $Z000L2L 11252 MXID11000025816212289682083477587000000000206U3333300_1711___SQLCI_DML_LAST__ Some Chinese UTF8 characters: ??????????? and invalid UTF-8 characters: ??hould see replacement characters to the left
0 master_exec_regr_999_99999.log 30 C
-2015-02-03 00:08:34.461000 ERROR SQL.EXE 0 0 25816 $Z000L2L 11252 MXID11000025816212289682083477587000000000206U3333300_1711___SQLCI_DML_LAST__ Parse error in continuation line: Invalid UTF-8 characters: ??hould see replacement characters to the left
0 master_exec_regr_999_99999.log 31 C
+2015-02-03 00:08:03.480000 INFO SQL.EXE 0 0 25816 $Z000L2L ? ? An executor process is launched.
0 master_exec_regr_999_99999.log 1
+2015-02-03 00:08:11.037000 INFO SQL.COMP 0 0 25952 $Z000L6H ? ? A compiler process is launched.
0 master_exec_regr_999_99999.log 3
+2015-02-03 00:08:31.884000 ERROR SQL.EXE 0 0 25816 $Z000L2L 11252 MXID11000025816212289682083477587000000000206U3333300_1438___SQLCI_DML_LAST__ *** ERROR[11252] Column not found (SQLSTATE 38900)
0 master_exec_regr_999_99999.log 8
+2015-02-03 00:08:33.418000 ERROR SQL.EXE 0 0 25816 $Z000L2L 11246 MXID11000025816212289682083477587000000000206U3333300_1593___SQLCI_DML_LAST__ *** ERROR[11246] An error occurred locating function 'SESSIONIZE_NON_EXISTENT' in library '/opt/home/zellerh/trafodion/core/sqf/rundir/udr/TEST001.dll'. 2015-02-03 00:this is a bad timestamp and should be appended to the previous message
0 master_exec_regr_999_99999.log 16
+2015-02-03 00:08:33.500000 ERROR ? ? ? ? ? ? ? this should produce a parse error with this message
0 master_exec_regr_999_99999.log 20 E
+2015-02-03 00:08:34.114000 ERROR SQL.EXE 0 0 25816 $Z000L2L 15001 MXID11000025816212289682083477587000000000206U3333300_1710___SQLCI_DML_LAST__ *** ERROR[15001] A syntax error occurred at or before: select * from udf(sessionize_dynamic(TABLE(SELECT userid, JULIANTIMESTAMP(ts) a s TS FROM clicks PARTITION BY userid ORDER BY ts), TABLE(SELECT userid, JULIANTIMESTAMP(ts) as TS FROM clicks PARTITION BY userid ORDER BY ts), cast('TS' as char(2)), 'USERID', 60000000)) XOX; ^ (409 characters from start of SQL statement)
0 master_exec_regr_999_99999.log 23
+2015-02-03 00:08:34.461000 ERROR SQL.EXE 0 0 25816 $Z000L2L 11252 MXID11000025816212289682083477587000000000206U3333300_1711___SQLCI_DML_LAST__ Some Chinese UTF8 characters: ??????????? and invalid UTF-8 characters: ??hould see replacement characters to the left
0 master_exec_regr_999_99999.log 30 C
+2015-02-03 00:08:34.461000 ERROR SQL.EXE 0 0 25816 $Z000L2L 11252 MXID11000025816212289682083477587000000000206U3333300_1711___SQLCI_DML_LAST__ Parse error in continuation line: Invalid UTF-8 characters: ??hould see replacement characters to the left
<TRUNCATED>
[6/7] incubator-trafodion git commit: Rework for pull request 218
Posted by hz...@apache.org.
Rework for pull request 218
Fixed computation of ports for install_local_hadoop -p rand
Fixed conversion of VEGRef to base or index column when getting
ready to eliminate a column from a required order.
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/59ce2b79
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/59ce2b79
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/59ce2b79
Branch: refs/heads/master
Commit: 59ce2b79cd652e43f71183859df53077050c4c2a
Parents: a347719
Author: Hans Zeller <hz...@apache.org>
Authored: Thu Dec 10 00:29:27 2015 +0000
Committer: Hans Zeller <hz...@apache.org>
Committed: Thu Dec 10 00:29:27 2015 +0000
----------------------------------------------------------------------
core/sqf/sql/scripts/install_local_hadoop | 2 +-
core/sql/optimizer/GroupAttr.cpp | 36 ++++++++++++++++++++++----
2 files changed, 32 insertions(+), 6 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/59ce2b79/core/sqf/sql/scripts/install_local_hadoop
----------------------------------------------------------------------
diff --git a/core/sqf/sql/scripts/install_local_hadoop b/core/sqf/sql/scripts/install_local_hadoop
index 951f417..732c17d 100755
--- a/core/sqf/sql/scripts/install_local_hadoop
+++ b/core/sqf/sql/scripts/install_local_hadoop
@@ -391,7 +391,7 @@ else
MY_START_PORT=`expr 24000 + $MY_START_PORT '*' 200`
elif [ $MY_START_PORT == "rand" ]; then
# pick a random number between 12000 and 24000 that is divisible by 200
- MY_START_PORT=`expr $RANDOM '%' 50 '*' 200 + 12000`
+ MY_START_PORT=`expr $RANDOM '%' 60 '*' 200 + 12000`
fi
echo "# Using non-standard port range from MY_START_PORT env var: $MY_START_PORT..."
fi
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/59ce2b79/core/sql/optimizer/GroupAttr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/GroupAttr.cpp b/core/sql/optimizer/GroupAttr.cpp
index d027745..5df31c3 100644
--- a/core/sql/optimizer/GroupAttr.cpp
+++ b/core/sql/optimizer/GroupAttr.cpp
@@ -343,6 +343,13 @@ void GroupAttributes::addConstraint(ItemExpr *c)
const ValueIdSet &occPreds =
((CheckOptConstraint *) occ.getItemExpr())->getCheckPreds();
+ // Note that the check for inclusion of value ids of
+ // the predicates is not very useful. A more useful
+ // check would be whether the existing predicates
+ // imply the new ones or vice versa, but that would be
+ // much more complicated code and is not required at
+ // this point.
+
if (occPreds.contains(cc->getCheckPreds()))
{
// this is no news, delete this useless new constraint
@@ -684,18 +691,37 @@ NABoolean GroupAttributes::tryToEliminateOrderColumnBasedOnEqualsPred(
if (col.getItemExpr()->getOperatorType() == ITM_INVERSE)
col = col.getItemExpr()->child(0).getValueId();
- // convert col from a VEGRef to a base column, if needed,
- // the ScanKey method below wants a real column as input
+ // Convert col from a VEGRef to a base column, if needed,
+ // the ScanKey method below wants a real column as input.
+ // Make sure to pick the base column that is actually
+ // referenced in the check predicates, in case there are
+ // multiple base columns in the VEG.
if (col.getItemExpr()->getOperatorType() == ITM_VEG_REFERENCE)
{
const ValueIdSet &vegMembers =
static_cast<VEGReference *>(col.getItemExpr())->
getVEG()->getAllValues();
+ ValueId dummy;
+
for (ValueId b=vegMembers.init();
vegMembers.next(b);
vegMembers.advance(b))
- if (b.getItemExpr()->getOperatorType() == ITM_BASECOLUMN)
- col = b;
+ if (checkPreds.referencesTheGivenValue(b, dummy))
+ {
+ // Use this column for comparison. Note that
+ // we can have a situation with a VEG(T1.a, T2.b)
+ // and a check predicate T1.a = const. The (computed)
+ // check predicate got added later, so "const" is
+ // not a VEG member. This should not cause trouble,
+ // however, since an operator must ensure that
+ // a) the VEG members it produces are equal (it needs to
+ // make sure the comparison pred is evaluated), and
+ // b) that the predicate applies.
+ // So, we cannot have the situation where T1.a=const
+ // and T2.b != const in the output of this operator.
+ col = b;
+ break;
+ }
}
for (ValueId p = checkPreds.init();
@@ -727,7 +753,7 @@ NABoolean GroupAttributes::tryToEliminateOrderColumnBasedOnEqualsPred(
ValueIdSet(p)));
}
}
- }
+ } // predicates or check constraints are supplied
return result;
}
[2/7] incubator-trafodion git commit: [TRAFODION-1672] Failures when
running regressions twice
Posted by hz...@apache.org.
[TRAFODION-1672] Failures when running regressions twice
Fixing tests executor/TEST009 and executor/TEST130 so they
clean up correctly at the end and can be run more than once.
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/849de1a3
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/849de1a3
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/849de1a3
Branch: refs/heads/master
Commit: 849de1a32c34c11e16ace6d9a4d4064148d594be
Parents: 680900c
Author: Hans Zeller <hz...@apache.org>
Authored: Wed Dec 9 16:58:39 2015 +0000
Committer: Hans Zeller <hz...@apache.org>
Committed: Wed Dec 9 16:58:39 2015 +0000
----------------------------------------------------------------------
core/sql/regress/executor/EXPECTED130 | 28 ++++++++++++++--------------
core/sql/regress/executor/TEST016 | 5 +++++
core/sql/regress/executor/TEST130 | 7 ++++---
3 files changed, 23 insertions(+), 17 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/849de1a3/core/sql/regress/executor/EXPECTED130
----------------------------------------------------------------------
diff --git a/core/sql/regress/executor/EXPECTED130 b/core/sql/regress/executor/EXPECTED130
index cea117a..0e2ee61 100644
--- a/core/sql/regress/executor/EXPECTED130
+++ b/core/sql/regress/executor/EXPECTED130
@@ -1,8 +1,8 @@
>>obey TEST130(setup);
->>create schema trafodion.lob;
+>>create schema trafodion.lob130;
--- SQL operation complete.
->>set schema trafodion.lob;
+>>set schema trafodion.lob130;
--- SQL operation complete.
>>create table t130lob1 (c1 blob);
@@ -53,9 +53,9 @@ C1
C1 C2
----------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------
- 1 LOBH00000200010882713693210510862219414564511952143513218212313794957530571017"TRAFODION"."LOB"
- 2 LOBH00000200010882713693210510862219414564511952725470718212313794971638403017"TRAFODION"."LOB"
- 3 LOBH00000200010882713693210510862219414564511953242068818212313794976885710017"TRAFODION"."LOB"
+ 1 LOBH00000200010279963438587423229119541341101965990251118212316399994956986020"TRAFODION"."LOB130"
+ 2 LOBH00000200010279963438587423229119541341101966300063218212316400003010310020"TRAFODION"."LOB130"
+ 3 LOBH00000200010279963438587423229119541341101966584793118212316400005859531020"TRAFODION"."LOB130"
--- 3 row(s) selected.
>>
@@ -333,7 +333,7 @@ And the dish ran away with the fork !
>>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'tlob130_txt1.txt');/g" >> t130_extract_command;
>>
>>obey t130_extract_command;
->>extract lobtofile(LOB 'LOBH00000200010882713693210514378719414564511984818875018212313795291895359017"TRAFODION"."LOB" ' , 'tlob130_txt1.txt');
+>>extract lobtofile(LOB 'LOBH00000200010279963438587425446719541341101986101346918212316400201031850020"TRAFODION"."LOB130" ' , 'tlob130_txt1.txt');
Success. Targetfile :tlob130_txt1.txt Length : 19
--- SQL operation complete.
@@ -349,7 +349,7 @@ Success. Targetfile :tlob130_txt1.txt Length : 19
>>sh rm t130_extract_command;
>>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'tlob130_deep.jpg');/g" >> t130_extract_command;
>>obey t130_extract_command;
->>extract lobtofile(LOB 'LOBH00000200010882713693210514444119414564511990648251418212313795350940159017"TRAFODION"."LOB"
' , 'tlob130_deep.jpg');
+>>extract lobtofile(LOB 'LOBH00000200010279963438587425504419541341101988962025518212316400229655406020"TRAFODION"."LOB130"
' , 'tlob130_deep.jpg');
Success. Targetfile :tlob130_deep.jpg Length : 159018
--- SQL operation complete.
@@ -365,7 +365,7 @@ Success. Targetfile :tlob130_deep.jpg Length : 159018
>>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'tlob130_anoush.jpg');/g" >> t130_extract_command;
>>
>>obey t130_extract_command;
->>extract lobtofile(LOB 'LOBH00000200010882713693210514444119414564511990648251418212313795350940159017"TRAFODION"."LOB"
' , 'tlob130_anoush.jpg');
+>>extract lobtofile(LOB 'LOBH00000200010279963438587425504419541341101988962025518212316400229655406020"TRAFODION"."LOB130"
' , 'tlob130_anoush.jpg');
Success. Targetfile :tlob130_anoush.jpg Length : 230150
--- SQL operation complete.
@@ -484,7 +484,7 @@ And the dish ran away with the fork !
>>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'hdfs:\/\/\/lobs\/tlob130_txt2.txt');/g" >> t130_extract_command;
>>
>>obey t130_extract_command;
->>extract lobtofile(LOB 'LOBH00000200010882713693210515418219414564511997894836018212313795423154729017"TRAFODION"."LOB" ' , 'hdfs:///lobs/tlob130_txt2.txt');
+>>extract lobtofile(LOB 'LOBH00000200010279963438587426033019541341101993803370618212316400277653681020"TRAFODION"."LOB130" ' , 'hdfs:///lobs/tlob130_txt2.txt');
Success. Targetfile :hdfs:///lobs/tlob130_txt2.txt Length : 19
--- SQL operation complete.
@@ -500,7 +500,7 @@ Success. Targetfile :hdfs:///lobs/tlob130_txt2.txt Length : 19
>>sh rm t130_extract_command;
>>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'hdfs:\/\/\/lobs\/tlob130_deep.jpg');/g" >> t130_extract_command;
>>obey t130_extract_command;
->>extract lobtofile(LOB 'LOBH00000200010882713693210515488319414564512003657955318212313795480776063017"TRAFODION"."LOB"
' , 'hdfs:///lobs/tlob130_deep.jpg');
+>>extract lobtofile(LOB 'LOBH00000200010279963438587426095919541341101996655440218212316400306584607020"TRAFODION"."LOB130"
' , 'hdfs:///lobs/tlob130_deep.jpg');
Success. Targetfile :hdfs:///lobs/tlob130_deep.jpg Length : 159018
--- SQL operation complete.
@@ -516,7 +516,7 @@ Success. Targetfile :hdfs:///lobs/tlob130_deep.jpg Length : 159018
>>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'hdfs:\/\/\/lobs\/tlob130_anoush.jpg');/g" >> t130_extract_command;
>>
>>obey t130_extract_command;
->>extract lobtofile(LOB 'LOBH00000200010882713693210514444119414564511990648251418212313795350940159017"TRAFODION"."LOB"
' , 'hdfs:///lobs/tlob130_anoush.jpg');
+>>extract lobtofile(LOB 'LOBH00000200010279963438587425504419541341101988962025518212316400229655406020"TRAFODION"."LOB130"
' , 'hdfs:///lobs/tlob130_anoush.jpg');
Success. Targetfile :hdfs:///lobs/tlob130_anoush.jpg Length : 230150
--- SQL operation complete.
@@ -566,7 +566,7 @@ And the dish ran away with the spoon.
>>sh rm t130_extract_command;
>>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'tlob130_deep2.jpg');/g" >> t130_extract_command;
>>obey t130_extract_command;
->>extract lobtofile(LOB 'LOBH00000200020882713693210517156319414564512012287022618212313795567334522017"TRAFODION"."LOB"
' , 'tlob130_deep2.jpg');
+>>extract lobtofile(LOB 'LOBH00000200020279963438587427046219541341102002085013918212316400360882889020"TRAFODION"."LOB130"
' , 'tlob130_deep2.jpg');
Success. Targetfile :tlob130_deep2.jpg Length : 159018
--- SQL operation complete.
@@ -575,7 +575,7 @@ Success. Targetfile :tlob130_deep2.jpg Length : 159018
>>sh rm t130_extract_command;
>>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'hdfs:\/\/\/lobs\/tlob130_anoush2.jpg');/g" >> t130_extract_command;
>>obey t130_extract_command;
->>extract lobtofile(LOB 'LOBH00000200030882713693210517156319414564512012826755818212313795572444089017"TRAFODION"."LOB"
' , 'hdfs:///lobs/tlob130_anoush2.jpg');
+>>extract lobtofile(LOB 'LOBH00000200030279963438587427046219541341102002340817618212316400363467910020"TRAFODION"."LOB130"
' , 'hdfs:///lobs/tlob130_anoush2.jpg');
Success. Targetfile :hdfs:///lobs/tlob130_anoush2.jpg Length : 230150
--- SQL operation complete.
@@ -602,7 +602,7 @@ Hey diddle diddle,
>>sh rm t130_extract_command;
>>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'tlob130_anoush3.jpg',create,truncate);/g" >> t130_extract_command;
>>obey t130_extract_command;
->>extract lobtofile(LOB 'LOBH00000200030882713693210517507719414564512016516238718212313795609661744017"TRAFODION"."LOB" ' , 'tlob130_anoush3.jpg',create,truncate);
+>>extract lobtofile(LOB 'LOBH00000200030279963438587427288519541341102004608530918212316400386132885020"TRAFODION"."LOB130" ' , 'tlob130_anoush3.jpg',create,truncate);
Success. Targetfile :tlob130_anoush3.jpg Length : 230150
--- SQL operation complete.
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/849de1a3/core/sql/regress/executor/TEST016
----------------------------------------------------------------------
diff --git a/core/sql/regress/executor/TEST016 b/core/sql/regress/executor/TEST016
index fc1085b..0d314dd 100644
--- a/core/sql/regress/executor/TEST016
+++ b/core/sql/regress/executor/TEST016
@@ -27,6 +27,8 @@
-- To do:
-- Revision history:
+?section cleanup
+
drop table t016t1 cascade;
drop table t016t2 cascade;
drop table t016t3 cascade;
@@ -41,6 +43,8 @@ drop mvgroup mvg_016;
drop procedure rs016;
+?section tests
+
#ifNT
control query default ARKCMP_FAKE_HW 'ON';
control query default DEF_NUM_SMP_CPUS '2';
@@ -315,3 +319,4 @@ drop table t016_2535_3;
log;
+obey TEST016(cleanup);
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/849de1a3/core/sql/regress/executor/TEST130
----------------------------------------------------------------------
diff --git a/core/sql/regress/executor/TEST130 b/core/sql/regress/executor/TEST130
index 6bf031e..2503121 100755
--- a/core/sql/regress/executor/TEST130
+++ b/core/sql/regress/executor/TEST130
@@ -45,6 +45,7 @@ obey TEST130(setup);
?section clnup
+set schema trafodion.lob130;
drop table t130lob1 cascade;
drop table t130lob2 cascade;
drop table t130lob3 cascade;
@@ -52,8 +53,8 @@ drop table t130lob4 cascade;
?section setup
-create schema trafodion.lob;
-set schema trafodion.lob;
+create schema trafodion.lob130;
+set schema trafodion.lob130;
create table t130lob1 (c1 blob);
create table t130lob2 (c1 int not null, c2 blob , primary key (c1));
create table t130lob3 (c1 int not null,
@@ -427,7 +428,7 @@ drop table tlob130bin2;
sh rm TMP130;
sh rm tlob130txt2;
-drop schema trafodion.lob;
+drop schema trafodion.lob130 cascade;
exit;