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.sha...@esgyn.com> Authored: Thu Aug 2 21:31:08 2018 +0000 Committer: Anoop Sharma <anoop.sha...@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;