trafodion-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From ansha...@apache.org
Subject [1/4] trafodion git commit: TRAFODION-3146 Support ANSI OVERLAY function
Date Fri, 03 Aug 2018 18:14:43 GMT
Repository: trafodion
Updated Branches:
  refs/heads/master 88388c754 -> f4a072170


TRAFODION-3146 Support ANSI OVERLAY function

OVERLAY modifies a source string by replacing a given
substring of the string, which is specified by a given numeric starting
position and a given numeric length, with a replacement string).

When the length of the substring is zero, nothing is removed
from the source string and the string returned by the function is the
result of inserting the replacement string into the source string at the
starting position.

STUFF is syntactic variation of OVERLAY.

Example:
  overlay ('source original string' placing 'modified ' from 8 for 9)
  stuff ('source original string', 8, 9, 'modified ')
    will return:

  'source modified string'

  overlay ('source original string' placing 'modified ' from 8 for 0)
    will return:

  'source modified original string'


Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/ab38cbc9
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/ab38cbc9
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/ab38cbc9

Branch: refs/heads/master
Commit: ab38cbc907036e88a2f53092738e119b2acc19b9
Parents: 2b96f4c
Author: Anoop Sharma <anoop.sharma@esgyn.com>
Authored: Thu Aug 2 21:31:08 2018 +0000
Committer: Anoop Sharma <anoop.sharma@esgyn.com>
Committed: Thu Aug 2 21:31:08 2018 +0000

----------------------------------------------------------------------
 core/sql/bin/SqlciErrors.txt          |   1 +
 core/sql/common/OperTypeEnum.h        |   6 +-
 core/sql/exp/ExpErrorEnums.h          |   6 ++
 core/sql/exp/exp_function.cpp         |  23 ++++--
 core/sql/exp/exp_function.h           |  23 ++++--
 core/sql/generator/GenItemFunc.cpp    |  35 +++++----
 core/sql/optimizer/BindItemExpr.cpp   | 110 +++++++++++++++++++++++++-
 core/sql/optimizer/ItemExpr.cpp       |  36 +++++----
 core/sql/optimizer/ItemFunc.h         |  33 ++++++--
 core/sql/optimizer/SynthType.cpp      |  18 +++--
 core/sql/parser/ParKeyWords.cpp       |   3 +
 core/sql/parser/sqlparser.y           |  25 +++++-
 core/sql/regress/charsets/EXPECTED315 | 120 +++++++++++++++++++++++++++++
 core/sql/regress/charsets/TEST315     |  20 +++++
 14 files changed, 398 insertions(+), 61 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/trafodion/blob/ab38cbc9/core/sql/bin/SqlciErrors.txt
----------------------------------------------------------------------
diff --git a/core/sql/bin/SqlciErrors.txt b/core/sql/bin/SqlciErrors.txt
index 3437cef..dc21543 100644
--- a/core/sql/bin/SqlciErrors.txt
+++ b/core/sql/bin/SqlciErrors.txt
@@ -1535,6 +1535,7 @@ $1~String1 --------------------------------
 8142 ZZZZZ 99999 ADVANCED INFRM LOGONLY An error was artificially injected, to test error
handling. Testpoint $0~string0, Value $1~string1.
 8143 ZZZZZ 99999 ADVANCED INFRM LOGONLY The requested operation stopped $0~Int0 server processes.
 8144 ZZZZZ 99999 ADVANCED MAJOR DIALOUT Corruption is detected in table $0~string0$1~string1.
+8145 ZZZZZ 99999 BEGINNER MAJOR DBADMIN This statement is not supported or incorrect options
were specified. Reason: $0~string0
 8300 ZZZZZ 99999 BEGINNER MINOR LOGONLY Late name resolution failed for $0~String0 $1~String1.
 8301 ZZZZZ 99999 ADVANCED MAJOR DBADMIN File system error $0~NSKCode occurred on anchor file
$1~String0.
 8302 ZZZZZ 99999 BEGINNER MAJOR DBADMIN All partitions of $0~String0 $1~TableName are unavailable.

http://git-wip-us.apache.org/repos/asf/trafodion/blob/ab38cbc9/core/sql/common/OperTypeEnum.h
----------------------------------------------------------------------
diff --git a/core/sql/common/OperTypeEnum.h b/core/sql/common/OperTypeEnum.h
index dda4074..fd3776c 100644
--- a/core/sql/common/OperTypeEnum.h
+++ b/core/sql/common/OperTypeEnum.h
@@ -544,8 +544,6 @@ enum OperatorTypeEnum {
                         ITM_RIGHT = 2271,
                         ITM_CONVERTTOHEX = 2272,
                         ITM_CONVERTFROMHEX = 2273,
-                        ITM_TOKENSTR = 2292,
-                        ITM_REVERSE = 2294,
 
                         // UNICODE/DOUBLEBYTE charsets built-in functions
                         ITM_SUBSTR_DOUBLEBYTE = 2274,
@@ -569,10 +567,14 @@ enum OperatorTypeEnum {
                         ITM_ROWSETARRAY_ROWID = 2287,
                         ITM_ROWSETARRAY_INTO  = 2288,
 
+                        // more string functions
                         ITM_LEFT = 2289,
                         ITM_SPACE = 2290,
                         ITM_ODBC_LENGTH = 2291,
+                        ITM_TOKENSTR = 2292,
                         ITM_CODE_VALUE = 2293,
+                        ITM_REVERSE = 2294,
+                        ITM_OVERLAY = 2295,
 
                         // datetime functions
                         ITM_CONVERTTIMESTAMP = 2300,

http://git-wip-us.apache.org/repos/asf/trafodion/blob/ab38cbc9/core/sql/exp/ExpErrorEnums.h
----------------------------------------------------------------------
diff --git a/core/sql/exp/ExpErrorEnums.h b/core/sql/exp/ExpErrorEnums.h
index ef17b71..7d8a0be 100644
--- a/core/sql/exp/ExpErrorEnums.h
+++ b/core/sql/exp/ExpErrorEnums.h
@@ -108,6 +108,12 @@ enum ExeErrorCode
   EXE_CLEANUP_ESP                       = 8143,
   EXE_CORRUPT_PARTITION                 = 8144,
 
+  // ---------------------------------------------------------------------
+  // generic error for the specified statement/feature/option.
+  // Error detail included in string param.
+  // ---------------------------------------------------------------------
+  EXE_STMT_NOT_SUPPORTED                = 8145,
+
   //----------------------------------------------------------------------
   // Late-name resolution and late-binding/similarity check errors.
   //----------------------------------------------------------------------

http://git-wip-us.apache.org/repos/asf/trafodion/blob/ab38cbc9/core/sql/exp/exp_function.cpp
----------------------------------------------------------------------
diff --git a/core/sql/exp/exp_function.cpp b/core/sql/exp/exp_function.cpp
index b859956..38a8a43 100644
--- a/core/sql/exp/exp_function.cpp
+++ b/core/sql/exp/exp_function.cpp
@@ -786,13 +786,18 @@ ExpRaiseErrorFunction::ExpRaiseErrorFunction (Attributes **attr,
 					      NABoolean raiseError,
 					      const char *constraintName,
 					      const char *tableName,
-						  const NABoolean hasStringExp)  // -- Triggers
+                                              const NABoolean hasStringExp,  // -- Triggers
+                                              const char * optionalStr)
 : ex_function_clause (ITM_RAISE_ERROR, (hasStringExp ? 2 : 1), attr, space),
-    theSQLCODE_(sqlCode),
-    constraintName_((char *)constraintName),
-    tableName_((char *)tableName)
+  theSQLCODE_(sqlCode),
+  constraintName_((char *)constraintName),
+  tableName_((char *)tableName)
 {
-    setRaiseError(raiseError);
+  setRaiseError(raiseError);
+
+
+  strncpy(optionalStr_, optionalStr, MAX_OPTIONAL_STR_LEN);
+  optionalStr_[MAX_OPTIONAL_STR_LEN] = 0;
 };
 
 ExFunctionRandomNum::ExFunctionRandomNum(OperatorTypeEnum opType,
@@ -5778,9 +5783,13 @@ ex_expr::exp_return_type ExpRaiseErrorFunction::eval(char *op_data[],
   // Create a DiagsArea to return the SQLCODE and the ConstraintName
   // and TableName.
   if (raiseError())
-    ExRaiseSqlError(heap, diagsArea, (ExeErrorCode)getSQLCODE());
+    ExRaiseSqlError(heap, diagsArea, (ExeErrorCode)getSQLCODE(),
+                    NULL, NULL, NULL, NULL,
+                    getOptionalStr());
   else
-    ExRaiseSqlWarning(heap, diagsArea, (ExeErrorCode)getSQLCODE());
+    ExRaiseSqlWarning(heap, diagsArea, (ExeErrorCode)getSQLCODE(),
+                      NULL, NULL, NULL, NULL,
+                      getOptionalStr());
 
   // SQLCODE correspoding to Triggered Action Exception
   if (getSQLCODE() == ComDiags_TrigActionExceptionSQLCODE) 

http://git-wip-us.apache.org/repos/asf/trafodion/blob/ab38cbc9/core/sql/exp/exp_function.h
----------------------------------------------------------------------
diff --git a/core/sql/exp/exp_function.h b/core/sql/exp/exp_function.h
index 6debaba..b4031ad 100644
--- a/core/sql/exp/exp_function.h
+++ b/core/sql/exp/exp_function.h
@@ -3017,12 +3017,13 @@ class  ExpRaiseErrorFunction : public ex_function_clause {
 
 public:
   ExpRaiseErrorFunction (Attributes ** attr,
-				    Space *space,
-				    Lng32 sqlCode,
-				    NABoolean raiseError = TRUE,
-				    const char *constraintName=NULL,
-				    const char *tableName=NULL,
-					const NABoolean hasStringExp=FALSE);  // -- Triggers
+                         Space *space,
+                         Lng32 sqlCode,
+                         NABoolean raiseError = TRUE,
+                         const char *constraintName=NULL,
+                         const char *tableName=NULL,
+                         const NABoolean hasStringExp=FALSE,  // -- Triggers
+                         const char *optionalStr = NULL);
   ExpRaiseErrorFunction();
  
  
@@ -3041,6 +3042,8 @@ public:
   void setTableName(const char * tableName) 
   { tableName_ = (char*)tableName;};
 
+  const char * getOptionalStr() {return optionalStr_; }
+
   // ---------------------------------------------------------------------
   // Redefinition of methods inherited from NAVersionedObject.
   // ---------------------------------------------------------------------
@@ -3074,17 +3077,23 @@ private:
     RAISE_ERROR	=0x00000001	// Raise Error if set, or it's warning
   };
 
+  enum {MAX_OPTIONAL_STR_LEN = 1023};
+
   NABasicPtr /*const char* */ constraintName_; // 00-07
   NABasicPtr /*const char* */ tableName_;      // 08-15
   Int32                       theSQLCODE_;     // 16-19
   Int32                       flags_;          // 20-23
   // TRUE, raise error. FALSE, raise warning.
+
+  // one byte for null terminator.
+  char  optionalStr_[MAX_OPTIONAL_STR_LEN+1];      // 24-1047
+
   // ---------------------------------------------------------------------
   // Fillers for potential future extensions without changing class size.
   // When a new member is added, size of this filler should be reduced so
   // that the size of the object remains the same (and is modulo 8).
   // ---------------------------------------------------------------------
-  char          fillers_[8];                   // 24-31
+  char          fillers_[8];                   // 1048-1055
 
 };
 

http://git-wip-us.apache.org/repos/asf/trafodion/blob/ab38cbc9/core/sql/generator/GenItemFunc.cpp
----------------------------------------------------------------------
diff --git a/core/sql/generator/GenItemFunc.cpp b/core/sql/generator/GenItemFunc.cpp
index 5594136..4faff64 100644
--- a/core/sql/generator/GenItemFunc.cpp
+++ b/core/sql/generator/GenItemFunc.cpp
@@ -933,30 +933,37 @@ short RaiseError::codeGen(Generator *generator) {
   
   if (generator->getExpGenerator()->genItemExpr(this, &attr, 1 + getArity(), -1)
== 1)
     return 0;
-
+  
   const char * constraintName = NULL;
   const char * tableName  = NULL;
+  const char * optionalStr = NULL;
 
- if (!getConstraintName().isNull()) {
-      constraintName = generator->getSpace()->AllocateAndCopyToAlignedSpace(
-	  			getConstraintName(), 0);
-   }
-
- if (!getTableName().isNull()) {
-      tableName  = generator->getSpace()->AllocateAndCopyToAlignedSpace(
-	    			getTableName(), 0);
-   }
+  if (!getConstraintName().isNull()) {
+    constraintName = generator->getSpace()->AllocateAndCopyToAlignedSpace(
+         getConstraintName(), 0);
+  }
   
- // make raiseError a field in this class. TBD.
- NABoolean raiseError = ((getSQLCODE() > 0) ? TRUE : FALSE);
- ex_clause * function_clause =
+  if (!getTableName().isNull()) {
+    tableName  = generator->getSpace()->AllocateAndCopyToAlignedSpace(
+         getTableName(), 0);
+  }
+  
+  if (!optionalStr_.isNull()) {
+    optionalStr  = generator->getSpace()->allocateAndCopyToAlignedSpace(
+         optionalStr_.data(), optionalStr_.length(), 0);
+  }
+  
+  // make raiseError a field in this class. TBD.
+  NABoolean raiseError = ((getSQLCODE() > 0) ? TRUE : FALSE);
+  ex_clause * function_clause =
     new(generator->getSpace()) ExpRaiseErrorFunction (attr, 
 						      generator->getSpace(),
 						      (raiseError ? getSQLCODE() : - getSQLCODE()),
 						      raiseError,
 						      constraintName,
 						      tableName,
-							  (getArity()==1) ? TRUE : FALSE);  // -- Triggers
+                                                      (getArity()==1) ? TRUE : FALSE,  //
-- Triggers
+                                                      optionalStr);
   
   generator->getExpGenerator()->linkClause(this, function_clause);
   

http://git-wip-us.apache.org/repos/asf/trafodion/blob/ab38cbc9/core/sql/optimizer/BindItemExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/BindItemExpr.cpp b/core/sql/optimizer/BindItemExpr.cpp
index f8d7b67..aaafe52 100644
--- a/core/sql/optimizer/BindItemExpr.cpp
+++ b/core/sql/optimizer/BindItemExpr.cpp
@@ -12328,6 +12328,114 @@ ItemExpr *ZZZBinderFunction::bindNode(BindWA *bindWA)
       }
       break;
 
+    ////////////////////////////////////////////////////////////////////////
+    // OVERLAY ( src_str PLACING replace_str FROM start_pos [ FOR length ]
+    // is equivalent to:
+    // If 'FOR length' is specified:
+    //   SUBSTRING(src_str FROM 1 FOR start_pos - 1 ) 
+    //   || replace_str 
+    //   || SUBSTRING(src_str FROM start_pos + length )
+    // Otherwise:
+    //   SUBSTRING(src_str FROM 1 FOR start_pos - 1 ) 
+    //   || replace_str 
+    //   || SUBSTRING(src_str FROM start_pos + CHAR_LENGTH(replace_str) )
+    //
+    //
+    // STUFF (srcStr, startPos, length, replaceStr)
+    /////////////////////////////////////////////////////////////////////////
+    case ITM_OVERLAY:
+      {
+	bindChildren(bindWA);
+	if (bindWA->errStatus()) 
+	  return this;
+
+        NAString funcName = (overlayFuncWasStuff() ? "STUFF" : getTextUpper());
+
+        NAString errReason;
+        if (child(0)->getValueId().getType().getTypeQualifier() != NA_CHARACTER_TYPE)
+          {
+            errReason = "Source string specified in function " + funcName + " must be of
character datatype.";
+	    *CmpCommon::diags() << DgSqlCode(-3242)
+                                << DgString0(errReason);
+	    bindWA->setErrStatus();
+	    return this;
+          }
+
+        if (child(1)->getValueId().getType().getTypeQualifier() != NA_CHARACTER_TYPE)
+          {
+            errReason = "Replacement string specified in function " + funcName + " must be
of character datatype.";
+	    *CmpCommon::diags() << DgSqlCode(-3242)
+                                << DgString0(errReason);
+	    bindWA->setErrStatus();
+	    return this;
+          }
+
+        if (child(2))
+          {
+            const NumericType &ntyp2 =
+              (NumericType &) child(2)->getValueId().getType();
+            if (NOT ((ntyp2.getTypeQualifier() == NA_NUMERIC_TYPE) &&
+                     (ntyp2.isExact()) && (ntyp2.getScale() == 0)))
+              {
+                errReason = "Start position of replacement string specified in function "
+ funcName + " must be of numeric datatype with scale of 0.";
+                *CmpCommon::diags() << DgSqlCode(-3242)
+                                    << DgString0(errReason);
+                bindWA->setErrStatus();
+                return this;
+              }
+
+            // this error will be caught at execution time based on actual
+            // values
+            errReason = "Start position of replacement string specified in function " + funcName
+ " cannot be less than or equal to zero.";
+            RaiseError *ire = 
+              new (bindWA->wHeap()) 
+              RaiseError((Lng32)EXE_STMT_NOT_SUPPORTED, "", "", errReason, 
+                         &child(2)->getValueId().getType());
+            ire->bindNode(bindWA);
+            if (bindWA->errStatus())
+              return this;
+            
+            setChild(4, ire);
+          }
+
+        if (child(3))
+          {
+            const NumericType &ntyp3 =
+              (NumericType &) child(3)->getValueId().getType();
+            if (NOT ((ntyp3.getTypeQualifier() == NA_NUMERIC_TYPE) &&
+                     (ntyp3.isExact()) && (ntyp3.getScale() == 0)))
+              {
+                errReason = "Number of characters to replace specified in function " + funcName
+ " must be of numeric datatype with scale of 0.";
+                *CmpCommon::diags() << DgSqlCode(-3242)
+                                    << DgString0(errReason);
+                bindWA->setErrStatus();
+                return this;
+              }
+
+            // this error will be caught at execution time based on actual
+            // values
+            errReason = "Number of characters to replace specified in function " + funcName
+ " cannot be less than zero.";
+            RaiseError *ire = 
+              new (bindWA->wHeap()) 
+              RaiseError((Lng32)EXE_STMT_NOT_SUPPORTED, "", "", errReason, 
+                         &child(3)->getValueId().getType());
+            ire->bindNode(bindWA);
+            if (bindWA->errStatus())
+              return this;
+            
+            setChild(5, ire);
+          }
+
+        if (child(3))
+          // @A5(child4) and @A6(child5) are RaiseError operators that will
+          // be evaluated at runtime if that error condition occurs.
+          strcpy(buf, "SUBSTRING(@A1 FROM 1 FOR case when @A3 <= 0 then @A5 else @A3 end
- 1) || @A2 || SUBSTRING (@A1 FROM @A3 + case when @A4 < 0 THEN @A6 else @A4 end); ");

+        else
+          strcpy(buf, "SUBSTRING(@A1 FROM 1 FOR case when @A3 <= 0 then @A5 else @A3 end
- 1) || @A2 || SUBSTRING (@A1 FROM @A3 + char_length(@A2) ); "); 
+      }
+      break;
+      
+      
     default:
       {
 	bindWA->setErrStatus();
@@ -12342,7 +12450,7 @@ ItemExpr *ZZZBinderFunction::bindNode(BindWA *bindWA)
 
   if (strlen(buf) > 0)
     {
-      parseTree = parser.getItemExprTree(buf, strlen(buf), BINDITEMEXPR_STMTCHARSET, 5, child(0),
child(1), child(2), child(3), child(4));
+      parseTree = parser.getItemExprTree(buf, strlen(buf), BINDITEMEXPR_STMTCHARSET, 6, child(0),
child(1), child(2), child(3), child(4), child(5));
     }
 
  if (parseTree) {

http://git-wip-us.apache.org/repos/asf/trafodion/blob/ab38cbc9/core/sql/optimizer/ItemExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/ItemExpr.cpp b/core/sql/optimizer/ItemExpr.cpp
index b1638d4..c9c7bb9 100644
--- a/core/sql/optimizer/ItemExpr.cpp
+++ b/core/sql/optimizer/ItemExpr.cpp
@@ -3029,7 +3029,7 @@ ItemExpr::removeInverseFromExprTree( NABoolean & invExists,
 ItemExpr *ZZZBinderFunction::copyTopNode(ItemExpr *derivedNode,
                                          CollHeap *outHeap)
 {
-  ItemExpr *result=0;
+  ZZZBinderFunction *result=0;
 
   if (derivedNode == NULL)
    {
@@ -3059,9 +3059,11 @@ ItemExpr *ZZZBinderFunction::copyTopNode(ItemExpr *derivedNode,
    }
   else
    {
-    result = derivedNode;
+     result = (ZZZBinderFunction*)derivedNode;
    }
 
+  result->flags_ = flags_;
+
   return BuiltinFunction::copyTopNode(result, outHeap);
 }
 
@@ -7551,6 +7553,8 @@ const NAString BuiltinFunction::getText() const
       return "nullifzero";
     case ITM_NVL:
       return "nvl";
+    case ITM_OVERLAY:
+      return "overlay";
     case ITM_JSONOBJECTFIELDTEXT:
       return "json_object_field_text";
     case ITM_QUERYID_EXTRACT:
@@ -12507,21 +12511,23 @@ ItemExpr * RaiseError::copyTopNode (ItemExpr *derivedNode, CollHeap*
outHeap)
   ItemExpr *result;
 
   if (derivedNode == NULL)
-  {
-	if (getArity() > 0) // Do we have string expressions?
-	  result = new (outHeap) RaiseError(getSQLCODE(),
-					    getConstraintName(),
-					    child(0)->copyTree(outHeap),
-					    outHeap);
-	else
-	  result = new (outHeap) RaiseError(getSQLCODE(),
-					    getConstraintName(),
-					    getTableName(),
-					    outHeap);
-  }
+    {
+      if (getArity() > 0) // Do we have string expressions?
+        result = new (outHeap) RaiseError(getSQLCODE(),
+                                          getConstraintName(),
+                                          child(0)->copyTree(outHeap),
+                                          outHeap);
+      else
+        result = new (outHeap) RaiseError(getSQLCODE(),
+                                          getConstraintName(),
+                                          getTableName(),
+                                          optionalStr_,
+                                          type_,
+                                          outHeap);
+    }
   else
     result = derivedNode;
-
+  
   return BuiltinFunction::copyTopNode(result, outHeap);
 }
 

http://git-wip-us.apache.org/repos/asf/trafodion/blob/ab38cbc9/core/sql/optimizer/ItemFunc.h
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/ItemFunc.h b/core/sql/optimizer/ItemFunc.h
index 249c09b..8b09396 100644
--- a/core/sql/optimizer/ItemFunc.h
+++ b/core/sql/optimizer/ItemFunc.h
@@ -2512,8 +2512,8 @@ public:
   // This Ctor is used for SIGNAL statements with a string expression.
   RaiseError (Lng32 sqlcode,
 	      NAString SqlState,
-		  ItemExpr *messageExpr,
-          CollHeap * h=0)
+              ItemExpr *messageExpr,
+              CollHeap * h=0)
     : BuiltinFunction(ITM_RAISE_ERROR,  CmpCommon::statementHeap(),
                       1, messageExpr),
       theSQLCODE_(sqlcode),
@@ -2524,11 +2524,15 @@ public:
   RaiseError (Lng32 sqlcode = 0,
 	      const NAString & constraintName = "",
 	      const NAString & tableName = "",
+              const NAString & optionalStr = "",
+              const NAType *type = NULL,
               CollHeap * h=0)
     : BuiltinFunction(ITM_RAISE_ERROR),
       theSQLCODE_(sqlcode),
       constraintName_(constraintName, h),
-      tableName_(tableName, h)
+      tableName_(tableName, h),
+      optionalStr_(optionalStr, h),
+      type_(type)
     {};
 
   // copy ctor
@@ -2547,10 +2551,12 @@ public:
   void setSQLCODE(Lng32 sqlcode) 		{ theSQLCODE_ = sqlcode; }
 
 private:
-  Lng32     theSQLCODE_;
+  Lng32    theSQLCODE_;
   NAString constraintName_;
   NAString tableName_;
 
+  NAString optionalStr_;
+  const NAType * type_;
 }; // class RaiseError
 
 
@@ -4675,9 +4681,11 @@ public:
   ZZZBinderFunction(OperatorTypeEnum oper,
 		    ItemExpr *val1Ptr = NULL, ItemExpr *val2Ptr = NULL,
 		    ItemExpr *val3Ptr = NULL, ItemExpr *val4Ptr = NULL,
-		    ItemExpr *val5Ptr = NULL)
-       : BuiltinFunction(oper, CmpCommon::statementHeap(), 5,
-			 val1Ptr, val2Ptr, val3Ptr, val4Ptr, val5Ptr) {}
+		    ItemExpr *val5Ptr = NULL, ItemExpr *val6Ptr = NULL)
+       : BuiltinFunction(oper, CmpCommon::statementHeap(), 6,
+			 val1Ptr, val2Ptr, val3Ptr, val4Ptr, val5Ptr, val6Ptr),
+         flags_(0)
+  {}
 
   // a virtual function for performing name binding within the query tree
   virtual ItemExpr * bindNode(BindWA *bindWA);
@@ -4706,6 +4714,17 @@ public:
 
   static ItemExpr *tryToUndoBindTransformation(ItemExpr *expr);
 
+  // OVERLAY clause was created for STUFF syntax.
+  NABoolean overlayFuncWasStuff()   { return (flags_ & WAS_STUFF_) != 0; }
+  void setOverlayFuncWasStuff(NABoolean v)
+  { (v ? flags_ |= WAS_STUFF_ : flags_ &= ~WAS_STUFF_); }
+
+private:
+  enum {
+    WAS_STUFF_ = 0x0001
+  };
+
+  Int64 flags_;
 };
 
 // --------------------------------------------------------------------------

http://git-wip-us.apache.org/repos/asf/trafodion/blob/ab38cbc9/core/sql/optimizer/SynthType.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/SynthType.cpp b/core/sql/optimizer/SynthType.cpp
index 13f87da..dc10a9b 100644
--- a/core/sql/optimizer/SynthType.cpp
+++ b/core/sql/optimizer/SynthType.cpp
@@ -2338,16 +2338,20 @@ const NAType *BoolVal::synthesizeType()
 //------------------------------------------------------------------
 const NAType *RaiseError::synthesizeType()
 {
-	// -- Triggers
+  // -- Triggers
   if (getArity() == 1)
-  {  // Verify the string expression is of character type.
-	if (child(0)->getValueId().getType().getTypeQualifier() != NA_CHARACTER_TYPE)
+    {  // Verify the string expression is of character type.
+      if (child(0)->getValueId().getType().getTypeQualifier() != NA_CHARACTER_TYPE)
 	{
-		//  parameter 3 must be of type string.
-		*CmpCommon::diags() << DgSqlCode(-3185);
-		return NULL;
+          //  parameter 3 must be of type string.
+          *CmpCommon::diags() << DgSqlCode(-3185);
+          return NULL;
 	}
-  }
+    }
+
+  if (type_)
+    return type_;
+
   return new HEAP SQLBooleanRelat(FALSE);	// can be overridden in IfThenElse
 }
 

http://git-wip-us.apache.org/repos/asf/trafodion/blob/ab38cbc9/core/sql/parser/ParKeyWords.cpp
----------------------------------------------------------------------
diff --git a/core/sql/parser/ParKeyWords.cpp b/core/sql/parser/ParKeyWords.cpp
index 85b97e7..e5cae2d 100644
--- a/core/sql/parser/ParKeyWords.cpp
+++ b/core/sql/parser/ParKeyWords.cpp
@@ -810,6 +810,7 @@ ParKeyWord ParKeyWords::keyWords_[] = {
   ParKeyWord("OUTPUT",             TOK_OUTPUT,      ANS_|RESWORD_),
   ParKeyWord("OVER",               TOK_OVER,        NONRESTOKEN_),
   ParKeyWord("OVERLAPS",           TOK_OVERLAPS,    ANS_|RESWORD_),
+  ParKeyWord("OVERLAY",            TOK_OVERLAY,     NONRESTOKEN_),
   ParKeyWord("OVERRIDE",           TOK_OVERRIDE,    NONRESTOKEN_),
   ParKeyWord("OVERWRITE",          TOK_OVERWRITE,    NONRESTOKEN_),
   ParKeyWord("PACKED",             TOK_PACKED,      NONRESTOKEN_),
@@ -846,6 +847,7 @@ ParKeyWord ParKeyWords::keyWords_[] = {
   ParKeyWord("PID",                TOK_PID,         NONRESTOKEN_),
   ParKeyWord("PIVOT_GROUP",                TOK_PIVOT_GROUP,         NONRESTOKEN_),
   ParKeyWord("PIVOT",                TOK_PIVOT,         NONRESTOKEN_),
+  ParKeyWord("PLACING",            TOK_PLACING,     NONRESTOKEN_),
   ParKeyWord("POOL",               TOK_POOL,        NONRESTOKEN_),
   ParKeyWord("POPULATE",           TOK_POPULATE,    NONRESTOKEN_),
   ParKeyWord("POSITION",           TOK_POSITION,    ANS_|RESWORD_|NONRESTOKEN_),
@@ -1105,6 +1107,7 @@ ParKeyWord ParKeyWords::keyWords_[] = {
   ParKeyWord("STORED",             TOK_STORED,      NONRESTOKEN_),
   ParKeyWord("STREAM",             TOK_STREAM,      NONRESTOKEN_),
   ParKeyWord("STRUCTURE",          IDENTIFIER,      POTANS_|RESWORD_),
+  ParKeyWord("STUFF",              TOK_STUFF,       NONRESTOKEN_),
   ParKeyWord("STYLE",              TOK_STYLE,       NONRESTOKEN_),
   ParKeyWord("SUBCLASS_ORIGIN",    TOK_SUBCLASS_ORIGIN, NONRESTOKEN_),
   ParKeyWord("SUBSTR",             TOK_SUBSTRING,   NONRESTOKEN_),

http://git-wip-us.apache.org/repos/asf/trafodion/blob/ab38cbc9/core/sql/parser/sqlparser.y
----------------------------------------------------------------------
diff --git a/core/sql/parser/sqlparser.y b/core/sql/parser/sqlparser.y
index b053fb6..e7c9f05 100755
--- a/core/sql/parser/sqlparser.y
+++ b/core/sql/parser/sqlparser.y
@@ -1446,6 +1446,9 @@ static void enableMakeQuotedStringISO88591Mechanism()
 %token <tokval> TOK_ACTIVE           /* Tandem extension non-reserved word */
 %token <tokval> TOK_RMS         /* Tandem extension non-reserved word */
 %token <tokval> TOK_REVERSE
+%token <tokval> TOK_OVERLAY
+%token <tokval> TOK_STUFF      /* same as overlay */
+%token <tokval> TOK_PLACING
 
 %token <tokval> TOK_DATA_OFFSET        /* INTERNAL non-reserved word */
 %token <tokval> TOK_NULL_IND_OFFSET    /* INTERNAL non-reserved word */
@@ -9289,6 +9292,23 @@ string_function :
 	  $$ = new (PARSERHEAP()) 
 	    BuiltinFunction(ITM_REVERSE, CmpCommon::statementHeap(), 1, $3);
         } 
+
+     | TOK_OVERLAY '(' value_expression TOK_PLACING value_expression TOK_FROM value_expression
TOK_FOR value_expression ')'
+                  {
+		    $$ = 
+		      new (PARSERHEAP()) ZZZBinderFunction(ITM_OVERLAY, $3, $5, $7, $9);
+                  }
+     | TOK_OVERLAY '(' value_expression TOK_PLACING value_expression TOK_FROM value_expression
')'
+                  {
+		    $$ = 
+		      new (PARSERHEAP()) ZZZBinderFunction(ITM_OVERLAY, $3, $5, $7);
+                  }
+     | TOK_STUFF '(' value_expression ',' value_expression ',' value_expression ',' value_expression
')'
+                  {
+		    $$ = 
+		      new (PARSERHEAP()) ZZZBinderFunction(ITM_OVERLAY, $3, $9, $5, $7);
+                    ((ZZZBinderFunction*)$$)->setOverlayFuncWasStuff(TRUE);
+                  }
      | TOK_SPLIT_PART '(' value_expression ',' value_expression ',' value_expression ')'
         {                     
                $$ = new (PARSERHEAP()) SplitPart($3, $5, $7);
@@ -29167,7 +29187,7 @@ triggered_after_action: empty   // Empty or any option not listed
below
 		  if ($2 != TransMode::ACCESS_TYPE_NOT_SPECIFIED_)
 		    treeTopPtr->accessOptions().accessType() = $2;
 		}
-              | signal_statement 
+              | signal_statement
 		{
 		  $$ = finalize($1, FALSE);
 		}
@@ -34138,6 +34158,7 @@ nonreserved_word :      TOK_ABORT
 		      | TOK_PHASE // MV REFRESH
 		      | TOK_PID
 		      | TOK_PIPELINE // MV REFRESH
+                      | TOK_PLACING
                       | TOK_POOL
 		      | TOK_POPULATE
                       | TOK_POS
@@ -34649,6 +34670,8 @@ MP_nonreserved_func_word : TOK_CAST
                          | TOK_LOWER
                          | TOK_MIN
                          | TOK_OCTET_LENGTH
+                         | TOK_OVERLAY
+                         | TOK_STUFF
                          | TOK_POSITION
                          | TOK_REVERSE
                          | TOK_TRIM

http://git-wip-us.apache.org/repos/asf/trafodion/blob/ab38cbc9/core/sql/regress/charsets/EXPECTED315
----------------------------------------------------------------------
diff --git a/core/sql/regress/charsets/EXPECTED315 b/core/sql/regress/charsets/EXPECTED315
index 3f15bfc..7f5a8e0 100644
--- a/core/sql/regress/charsets/EXPECTED315
+++ b/core/sql/regress/charsets/EXPECTED315
@@ -1249,6 +1249,126 @@ E0A0A0C3BBC3BAC3B6636261
 
 --- 1 row(s) selected.
 >>
+>>-- tests for OVERLAY function
+>>select overlay('w1234567xyz' placing 'abcde' from 3)  from dual;
+
+(EXPR)                     
+---------------------------
+
+w1abcde7xyz                
+
+--- 1 row(s) selected.
+>>select overlay('w1234567xyz' placing 'abcde' from 3 for 4)  from dual;
+
+(EXPR)                     
+---------------------------
+
+w1abcde67xyz               
+
+--- 1 row(s) selected.
+>>select overlay('w1234567xyz' placing 'abcde' from 3 for 5)  from dual;
+
+(EXPR)                     
+---------------------------
+
+w1abcde7xyz                
+
+--- 1 row(s) selected.
+>>select overlay('w1234567xyz' placing 'abcde' from 3 for 6)  from dual;
+
+(EXPR)                     
+---------------------------
+
+w1abcdexyz                 
+
+--- 1 row(s) selected.
+>>select overlay('w1234567xyz' placing 'abcde' from 30 for 6)  from dual;
+
+(EXPR)                     
+---------------------------
+
+w1234567xyzabcde           
+
+--- 1 row(s) selected.
+>>select overlay(cast(NULL as char(10)) placing 'abcde' from 30 for 6)  from dual;
+
+(EXPR)                   
+-------------------------
+
+?                        
+
+--- 1 row(s) selected.
+>>select converttohex(overlay (uf8 placing uf8v from 4 for 2)) from cs315t4;
+
+(EXPR)
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+
+E0A0A0E0A0A0E0A0A0E0A0A0E0A0A0E0A0A07A7AE0A0A0E0A0A0E0A0A0E0A0A0E0A0A0E0A0A0202020202020202020202020
                                                                                         
 
+E0A0A02033E0A0A020333333E0A0A0E0A0A0E0A0A0E0A0A0E0A0A0E0A0A020202020202020202020202020202020
                                                                                         
         
+E0A0A02034E0A0A020343434E0A0A02020E0A0A020202020202020202020202020202020202020202020    
                                                                                         
             
+2037372037373737373720373720202020202020202020202020202020202020202020202020            
                                                                                         
             
+F7BFBFBFF7BFBFBFF7BFBFBFF7BFBFBFF7BFBFBFF7BFBFBF3333F7BFBFBFF7BFBFBFF7BFBFBFF7BFBFBFF7BFBFBFF7BFBFBF202020202020
                                                                               
+F7BFBFBF2020F7BFBFBF20203535F7BFBFBFF7BFBFBF20F7BFBFBFF7BFBFBF20202020202020202020202020202020
                                                                                         
       
+
+--- 6 row(s) selected.
+>>select converttohex(overlay(_utf8 x'C3B6C3BAC3BBE0A0A0' placing _UTF8 x'C3B6'  from
 2 for 2)) from dual;
+
+(EXPR)                                  
+----------------------------------------
+
+C3B6C3B6E0A0A0                          
+
+--- 1 row(s) selected.
+>>select stuff ('abcdefg', 3, 4, 'xy') from dual;
+
+(EXPR)          
+----------------
+
+abxyg           
+
+--- 1 row(s) selected.
+>>
+>>-- OVERLAY error cases
+>>select overlay('w1234567xyz' placing 'abcde' from 1.2 for 4)  from dual;
+
+*** ERROR[3242] This statement is not supported. Reason: Start position of replacement string
specified in function OVERLAY must be of numeric datatype with scale of 0.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>select overlay('w1234567xyz' placing 'abcde' from 1 for 4.1)  from dual;
+
+*** ERROR[3242] This statement is not supported. Reason: Number of characters to replace
specified in function OVERLAY must be of numeric datatype with scale of 0.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>select overlay('w1234567xyz' placing 'abcde' from 'a' for 4)  from dual;
+
+*** ERROR[3242] This statement is not supported. Reason: Start position of replacement string
specified in function OVERLAY must be of numeric datatype with scale of 0.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>select stuff ('abcdefg', -1, 4, 'xy') from dual;
+
+*** ERROR[8145] This statement is not supported or incorrect options were specified. Reason:
Start position of replacement string specified in function STUFF cannot be less than or equal
to zero.
+
+--- 0 row(s) selected.
+>>select overlay(1 placing 'abcde' from 1 for 4)  from dual;
+
+*** ERROR[3242] This statement is not supported. Reason: Source string specified in function
OVERLAY must be of character datatype.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>select overlay('xy' placing 'abcde' from -1 for 4)  from dual;
+
+*** ERROR[8145] This statement is not supported or incorrect options were specified. Reason:
Start position of replacement string specified in function OVERLAY cannot be less than or
equal to zero.
+
+--- 0 row(s) selected.
+>>select overlay('xy' placing 'abcde' from 0 for -4)  from dual;
+
+*** ERROR[8145] This statement is not supported or incorrect options were specified. Reason:
Start position of replacement string specified in function OVERLAY cannot be less than or
equal to zero.
+
+--- 0 row(s) selected.
+>>
 >>
 >>obey test315(clnup);
 >>

http://git-wip-us.apache.org/repos/asf/trafodion/blob/ab38cbc9/core/sql/regress/charsets/TEST315
----------------------------------------------------------------------
diff --git a/core/sql/regress/charsets/TEST315 b/core/sql/regress/charsets/TEST315
index 26f0bce..15342e7 100644
--- a/core/sql/regress/charsets/TEST315
+++ b/core/sql/regress/charsets/TEST315
@@ -315,6 +315,26 @@ select converttohex(_ucs2 x'515a515b515c515d') from dual;
 select char_length(reverse(_ucs2 x'515a515b515c515d')) from dual;
 select converttohex(reverse(_ucs2 x'515a515b515c515d')) from dual;
 
+-- tests for OVERLAY function
+select overlay('w1234567xyz' placing 'abcde' from 3)  from dual;
+select overlay('w1234567xyz' placing 'abcde' from 3 for 4)  from dual;
+select overlay('w1234567xyz' placing 'abcde' from 3 for 5)  from dual;
+select overlay('w1234567xyz' placing 'abcde' from 3 for 6)  from dual;
+select overlay('w1234567xyz' placing 'abcde' from 30 for 6)  from dual;
+select overlay(cast(NULL as char(10)) placing 'abcde' from 30 for 6)  from dual;
+select converttohex(overlay (uf8 placing uf8v from 4 for 2)) from cs315t4;
+select converttohex(overlay(_utf8 x'C3B6C3BAC3BBE0A0A0' placing _UTF8 x'C3B6'  from  2 for
2)) from dual;
+select stuff ('abcdefg', 3, 4, 'xy') from dual;
+
+-- OVERLAY error cases
+select overlay('w1234567xyz' placing 'abcde' from 1.2 for 4)  from dual;
+select overlay('w1234567xyz' placing 'abcde' from 1 for 4.1)  from dual;
+select overlay('w1234567xyz' placing 'abcde' from 'a' for 4)  from dual;
+select stuff ('abcdefg', -1, 4, 'xy') from dual;
+select overlay(1 placing 'abcde' from 1 for 4)  from dual;
+select overlay('xy' placing 'abcde' from -1 for 4)  from dual;
+select overlay('xy' placing 'abcde' from 0 for -4)  from dual;
+
 ?section clnup
 
 drop schema cs315s cascade;


Mime
View raw message