You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by an...@apache.org on 2016/03/12 20:30:58 UTC
[1/6] incubator-trafodion git commit: datetime functions: commit #1
Repository: incubator-trafodion
Updated Branches:
refs/heads/master 1eb60514a -> 6b9dea5c1
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/7526b83d/core/sql/optimizer/ItemFunc.h
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/ItemFunc.h b/core/sql/optimizer/ItemFunc.h
index d794a3d..6747c9f 100644
--- a/core/sql/optimizer/ItemFunc.h
+++ b/core/sql/optimizer/ItemFunc.h
@@ -1703,22 +1703,23 @@ public:
class DateFormat : public CacheableBuiltinFunction
{
public:
+ enum FormatType
+ {
+ FORMAT_GENERIC = 0,
+ FORMAT_TO_DATE,
+ FORMAT_TO_CHAR
+ };
+
enum
{
- DEFAULT, // YYYY-MM-DD
- USA, // MM/DD/YYYY AM|PM
- EUROPEAN, // DD.MM.YYYY
- DATE_FORMAT_STR, // formatStr_ contains date format
- TIME_FORMAT_STR, // formatStr_ contains time format
- TIMESTAMP_FORMAT_STR // formatStr_ contains time format
+ DATE_FORMAT_NONE = 0,
+ DATE_FORMAT_STR, // formatStr_ contains date format
+ TIME_FORMAT_STR, // formatStr_ contains time format
+ TIMESTAMP_FORMAT_STR // formatStr_ contains timestamp format
};
- DateFormat(ItemExpr *val1Ptr, ItemExpr *formatStrPtr,
- Int32 dateFormat)
- : CacheableBuiltinFunction(ITM_DATEFORMAT,
- 2, val1Ptr, formatStrPtr),
- dateFormat_(dateFormat)
- { allowsSQLnullArg() = FALSE; }
+ DateFormat(ItemExpr *val1Ptr, const NAString &formatStr,
+ Lng32 formatType, NABoolean wasDateformat = FALSE);
// virtual destructor
virtual ~DateFormat();
@@ -1726,6 +1727,8 @@ public:
// accessor functions
Int32 getDateFormat() const { return dateFormat_; }
+ Int32 getExpDatetimeFormat() const { return frmt_; }
+
// do not change format literals of DateFormat into constant parameters
virtual ItemExpr* normalizeForCache(CacheWA& cwa, BindWA& bindWA)
{ return this; }
@@ -1733,6 +1736,12 @@ public:
// append an ascii-version of ItemExpr into cachewa.qryText_
virtual void generateCacheKey(CacheWA& cwa) const;
+ NABoolean errorChecks(Lng32 frmt, BindWA *bindWA, const NAType* opType);
+
+ ItemExpr * quickDateFormatOpt(BindWA * bindWA);
+
+ ItemExpr * bindNode(BindWA * bindWA);
+
// a virtual function for type propagating the node
virtual const NAType * synthesizeType();
@@ -1742,7 +1751,11 @@ public:
virtual ItemExpr * copyTopNode(ItemExpr *derivedNode = NULL,
CollHeap* outHeap = 0);
-
+ void unparse(NAString &result,
+ PhaseEnum phase,
+ UnparseFormatEnum form,
+ TableDesc * tabId) const;
+
virtual NABoolean hasEquivalentProperties(ItemExpr * other);
virtual QR::ExprElement getQRExprElem() const
@@ -1751,8 +1764,20 @@ public:
}
private:
+ // string contains user specified format string
+ NAString formatStr_;
+ // TO_DATE or TO_CHAR
+ Lng32 formatType_;
+
+ // DATE, TIME or TIMESTAMP
Int32 dateFormat_;
+
+ // original function was DATEFORMAT
+ NABoolean wasDateformat_;
+
+ // actual datetime format (defined in class ExpDatetime in exp_datetime.h)
+ Lng32 frmt_;
}; // class DateFormat
class DayOfWeek : public CacheableBuiltinFunction
@@ -2664,6 +2689,8 @@ public:
virtual ItemExpr * copyTopNode(ItemExpr *derivedNode = NULL,
CollHeap* outHeap = 0);
+ ItemExpr * quickDateFormatOpt(BindWA * bindWA);
+
const NAString& getFormatStr() const
{
return formatStr_;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/7526b83d/core/sql/optimizer/QRDescGenerator.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/QRDescGenerator.cpp b/core/sql/optimizer/QRDescGenerator.cpp
index 2a412a3..f8bb9f6 100644
--- a/core/sql/optimizer/QRDescGenerator.cpp
+++ b/core/sql/optimizer/QRDescGenerator.cpp
@@ -543,22 +543,11 @@ static void addFunctionParameters(ItemExpr* ie,
case ITM_DATEFORMAT:
{
- // Only include the parameter if it is one of the predefined values
- // DEFAULT, USA, or EUROPEAN. The other possible values indicate that
- // a format string is used to determine the format, and this takes
- // the form of an explicit operand in the ItemExpr tree.
DateFormat* dateFormatFn = static_cast<DateFormat*>(ie);
- Int32 dateFormat = dateFormatFn->getDateFormat();
- if (dateFormat == DateFormat::DEFAULT ||
- dateFormat == DateFormat::USA ||
- dateFormat == DateFormat::EUROPEAN)
- {
- param->setName("dateFormat");
- param->setValue(dateFormat);
- function->addHiddenParam(param);
- }
- else
- deletePtr(param);
+ Int32 dateFormat = dateFormatFn->getExpDatetimeFormat();
+ param->setName("dateFormat");
+ param->setValue(dateFormat);
+ function->addHiddenParam(param);
}
break;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/7526b83d/core/sql/optimizer/SynthType.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/SynthType.cpp b/core/sql/optimizer/SynthType.cpp
index fc0f12d..24507f3 100644
--- a/core/sql/optimizer/SynthType.cpp
+++ b/core/sql/optimizer/SynthType.cpp
@@ -43,6 +43,7 @@
#include "CmpErrors.h"
#include "ComSqlId.h"
#include "OptimizerSimulator.h"
+#include "exp_datetime.h"
// For TRIGGERS_STATUS_VECTOR_SIZE and SIZEOF_UNIQUE_EXECUTE_ID
#include "Triggers.h"
@@ -2782,8 +2783,6 @@ const NAType *CurrentTimestampRunning::synthesizeType()
// -----------------------------------------------------------------------
// member functions for class DateFormat
// -----------------------------------------------------------------------
-
-#pragma nowarn(1506) // warning elimination
const NAType *DateFormat::synthesizeType()
{
//
@@ -2797,17 +2796,6 @@ const NAType *DateFormat::synthesizeType()
//
if (!vid.getType().isSupportedType()) {
// 4071 The operand of a DATEFORMAT function must be a datetime.
-// LCOV_EXCL_START - mp
- *CmpCommon::diags() << DgSqlCode(-4071) << DgString0(getTextUpper());
- return NULL;
-// LCOV_EXCL_STOP
- }
-
- if (((getDateFormat() == DEFAULT) ||
- (getDateFormat() == USA) ||
- (getDateFormat() == EUROPEAN)) &&
- (vid.getType().getTypeQualifier() != NA_DATETIME_TYPE)) {
- // 4071 The operand of a DATEFORMAT function must be a datetime.
*CmpCommon::diags() << DgSqlCode(-4071) << DgString0(getTextUpper());
return NULL;
}
@@ -2837,154 +2825,31 @@ const NAType *DateFormat::synthesizeType()
NABoolean formatAsDate = FALSE;
NABoolean formatAsTimestamp = FALSE;
NABoolean formatAsTime = FALSE;
+
if (vid.getType().getTypeQualifier() == NA_DATETIME_TYPE)
{
- // This code is now identical for all DatetimeTypes
- const DatetimeType& operand = (DatetimeType &)vid.getType();
- //
- // Return the result.
- //
- if (getDateFormat() == DATE_FORMAT_STR)
- {
- if (child(1)->castToItemExpr()->getOperatorType() == ITM_CONSTANT)
- {
- ConstValue * cv = (ConstValue*)(child(1)->castToItemExpr());
- length = cv->getStorageSize();
- }
- else
- {
- // must be a const for now.
- CMPASSERT(child(1)->castToItemExpr()->getOperatorType() == ITM_CONSTANT);
- }
- }
- else if (getDateFormat() == TIMESTAMP_FORMAT_STR)
- {
- if (child(1)->castToItemExpr()->getOperatorType() == ITM_CONSTANT)
- {
- ConstValue * cv = (ConstValue*)(child(1)->castToItemExpr());
- length = cv->getStorageSize();
-
- if ((NAString((char*)(cv->getConstValue()), cv->getStorageSize())
- == "YYYYMMDDHH24MISS") ||
- (NAString((char*)(cv->getConstValue()), cv->getStorageSize())
- == "YYYYMMDD:HH24:MI:SS") ||
- (NAString((char*)(cv->getConstValue()), cv->getStorageSize())
- == "DD.MM.YYYY:HH24:MI:SS") ||
- (NAString((char*)(cv->getConstValue()), cv->getStorageSize())
- == "YYYY-MM-DD HH24:MI:SS"))
- {
- // length includes 2 extra bytes for "24" that was specified
- // in the format.
- length -= 2;
- }
- }
- else if (getDateFormat() == TIME_FORMAT_STR)
- {
- if (child(1)->castToItemExpr()->getOperatorType() == ITM_CONSTANT)
- {
- ConstValue * cv = (ConstValue*)(child(1)->castToItemExpr());
- length = cv->getStorageSize();
-
- if (NAString((char*)(cv->getConstValue()), cv->getStorageSize())
- == "HH24:MI:SS")
- {
- // length includes 2 extra bytes for "24" that was specified
- // in the format.
- length -= 2;
- }
- }
- }
- else
- {
- // must be a const for now.
- CMPASSERT(child(1)->castToItemExpr()->getOperatorType() == ITM_CONSTANT);
- }
- }
- else
- {
- length = operand.getDisplayLength();
- if(operand.containsField(REC_DATE_HOUR) && (getDateFormat() == USA))
- length += 3; // add 3 for a blank and "am" or "pm"
- }
+ Lng32 frmt = ExpDatetime::getFormat(formatStr_.data());
+ length = ExpDatetime::getAsciiFormatMaxLen(frmt);
}
else if (vid.getType().getTypeQualifier() == NA_CHARACTER_TYPE)
{
- //
- // Return the result.
- //
+ length = formatStr_.length();
if (getDateFormat() == DATE_FORMAT_STR)
{
formatAsDate = TRUE;
- if (child(1)->castToItemExpr()->getOperatorType() == ITM_CONSTANT)
- {
- ConstValue * cv = (ConstValue*)(child(1)->castToItemExpr());
- length = cv->getStorageSize();
- }
- else
- {
- // must be a const for now.
- CMPASSERT(child(1)->castToItemExpr()->getOperatorType() == ITM_CONSTANT);
- }
}
else if (getDateFormat() == TIMESTAMP_FORMAT_STR)
{
formatAsTimestamp = TRUE;
- if (child(1)->castToItemExpr()->getOperatorType() == ITM_CONSTANT)
- {
- ConstValue * cv = (ConstValue*)(child(1)->castToItemExpr());
- length = cv->getStorageSize();
- }
- else
- {
- // must be a const for now.
- CMPASSERT(child(1)->castToItemExpr()->getOperatorType() == ITM_CONSTANT);
- }
}
else if (getDateFormat() == TIME_FORMAT_STR)
{
formatAsTime = TRUE;
- if (child(1)->castToItemExpr()->getOperatorType() == ITM_CONSTANT)
- {
- ConstValue * cv = (ConstValue*)(child(1)->castToItemExpr());
- length = cv->getStorageSize();
- }
- else
- {
- // must be a const for now.
- CMPASSERT(child(1)->castToItemExpr()->getOperatorType() == ITM_CONSTANT);
- }
}
}
else if (vid.getType().getTypeQualifier() == NA_NUMERIC_TYPE)
{
- const NumericType &numeric = (NumericType&)vid.getType();
- if ((numeric.isExact()) &&
- (NOT numeric.isBigNum()) &&
- (numeric.getScale() == 0))
- {
- if (child(1)->castToItemExpr()->getOperatorType() == ITM_CONSTANT)
- {
- ConstValue * cv = (ConstValue*)(child(1)->castToItemExpr());
- length = cv->getStorageSize();
- }
- else
- {
- // must be a const for now.
- CMPASSERT(child(1)->castToItemExpr()->getOperatorType() == ITM_CONSTANT);
- }
- }
- else
- {
- // 4047 Arguments of USER function must have a scale of 0.
- *CmpCommon::diags() << DgSqlCode(-4047) << DgString0(getTextUpper());
- return NULL;
- }
- }
- else
- {
- // 4071 The operand of a DATEFORMAT function must be a datetime.
- *CmpCommon::diags() << DgSqlCode(-4071) << DgString0(getTextUpper());
- return NULL;
+ length = formatStr_.length();
}
if (formatAsDate)
@@ -2996,7 +2861,6 @@ const NAType *DateFormat::synthesizeType()
else
return new HEAP SQLChar(length, vid.getType().supportsSQLnullLogical());
}
-#pragma warn(1506) // warning elimination
// -----------------------------------------------------------------------
// member functions for class DayOfWeek
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/7526b83d/core/sql/parser/ParKeyWords.cpp
----------------------------------------------------------------------
diff --git a/core/sql/parser/ParKeyWords.cpp b/core/sql/parser/ParKeyWords.cpp
index b46660e..4f46b2a 100644
--- a/core/sql/parser/ParKeyWords.cpp
+++ b/core/sql/parser/ParKeyWords.cpp
@@ -302,7 +302,6 @@ ParKeyWord ParKeyWords::keyWords_[] = {
ParKeyWord("DATE_TRUNC", TOK_DATE_TRUNC, NONRESTOKEN_),
ParKeyWord("DATEDIFF", TOK_DATEDIFF, NONRESTOKEN_),
ParKeyWord("DATE_SUB", TOK_DATE_SUB, NONRESTOKEN_),
- ParKeyWord("DATEFMT_INTN", TOK_DATEFMT_INTN, NONRESTOKEN_),
ParKeyWord("DATEFORMAT", TOK_DATEFORMAT, NONRESTOKEN_),
ParKeyWord("DATETIME", TOK_DATETIME, COMPAQ_|RESWORD_|MPWORD_),
ParKeyWord("DATETIME_CODE", TOK_DATETIME_CODE, NONRESTOKEN_),
@@ -1089,9 +1088,10 @@ ParKeyWord ParKeyWords::keyWords_[] = {
ParKeyWord("TO_CHAR", TOK_TO_CHAR, NONRESTOKEN_),
ParKeyWord("TO_DATE", TOK_TO_DATE, NONRESTOKEN_),
ParKeyWord("TO_NUMBER", TOK_TO_NUMBER, NONRESTOKEN_),
- ParKeyWord("TO_TIMESTAMP", TOK_TO_TIMESTAMP, NONRESTOKEN_),
+ ParKeyWord("TO_TIME", TOK_TO_TIME, NONRESTOKEN_),
+ ParKeyWord("TO_TIMESTAMP", TOK_TO_TIMESTAMP, NONRESTOKEN_),
ParKeyWord("TOKENSTR", TOK_TOKENSTR, NONRESTOKEN_),
- ParKeyWord("TRAFODION", TOK_TRAFODION, NONRESTOKEN_),
+ ParKeyWord("TRAFODION", TOK_TRAFODION, NONRESTOKEN_),
ParKeyWord("TRAILING", TOK_TRAILING, ANS_|RESWORD_|MPWORD_),
ParKeyWord("TRANSACTION", TOK_TRANSACTION, ANS_|RESWORD_),
ParKeyWord("TRANSFORM", TOK_TRANSFORM, NONRESTOKEN_),
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/7526b83d/core/sql/parser/sqlparser.y
----------------------------------------------------------------------
diff --git a/core/sql/parser/sqlparser.y b/core/sql/parser/sqlparser.y
index e565f14..72bae4c 100755
--- a/core/sql/parser/sqlparser.y
+++ b/core/sql/parser/sqlparser.y
@@ -182,6 +182,7 @@ using namespace std;
#include "exp_expr.h"
#include "exp_clause_derived.h"
+#include "exp_datetime.h"
#include "Analyzer.h"
@@ -589,7 +590,6 @@ static void enableMakeQuotedStringISO88591Mechanism()
%token <tokval> TOK_DATE_TRUNC
%token <tokval> TOK_DATEDIFF
%token <tokval> TOK_DATEFORMAT /* Tandem extension */
-%token <tokval> TOK_DATEFMT_INTN /* Tandem extension */
%token <tokval> TOK_DATETIME /* Tandem extension non-reserved word */
%token <tokval> TOK_DAY
%token <tokval> TOK_DAYNAME
@@ -1113,6 +1113,7 @@ static void enableMakeQuotedStringISO88591Mechanism()
%token <tokval> TOK_TO_CHAR
%token <tokval> TOK_TO_DATE
%token <tokval> TOK_TO_NUMBER
+%token <tokval> TOK_TO_TIME
%token <tokval> TOK_TO_TIMESTAMP
%token <tokval> TOK_TOKENSTR
%token <tokval> TOK_TRAILING
@@ -2007,7 +2008,7 @@ static void enableMakeQuotedStringISO88591Mechanism()
%type <item> insert_obj_to_lob_function
%type <item> update_obj_to_lob_function
%type <item> select_lob_to_obj_function
-%type <tokval> date_format
+%type <stringval> date_format
%type <tokval> trim_spec
%type <na_type> proc_arg_data_type
%type <na_type> data_type
@@ -3217,7 +3218,8 @@ literal : numeric_literal
$$ = new (PARSERHEAP()) ConstValue
(*$1, getStringCharSet(&$1));
SqlParser_CurrentParser->collectItem4HQC($$);
- $$ = new (PARSERHEAP()) Format($$, *$6, TRUE);
+ // $$ = new (PARSERHEAP()) Format($$, *$6, TRUE);
+ $$ = new (PARSERHEAP()) DateFormat($$, *$6, DateFormat::FORMAT_TO_DATE);
}
else
{
@@ -3225,7 +3227,8 @@ literal : numeric_literal
if (! $$) YYERROR;
SqlParser_CurrentParser->collectItem4HQC($$);
restoreInferCharsetState();
- $$ = new (PARSERHEAP()) Format($$, *$6, FALSE);
+ // $$ = new (PARSERHEAP()) Format($$, *$6, FALSE);
+ $$ = new (PARSERHEAP()) DateFormat($$, *$6, DateFormat::FORMAT_TO_CHAR);
}
}
@@ -7912,7 +7915,8 @@ primary : '(' value_expression ')'
| dml_column_reference TOK_LPAREN_BEFORE_DATE_COMMA_AND_FORMAT TOK_DATE ',' TOK_FORMAT character_string_literal ')'
{
// DEFAULT_CHARSET has no effect on character_string_literal in this context
- $$ = new (PARSERHEAP()) Format($1, *$6, FALSE);
+ // $$ = new (PARSERHEAP()) Format($1, *$6, FALSE);
+ $$ = new (PARSERHEAP()) DateFormat($1, *$6, DateFormat::FORMAT_TO_CHAR);
}
| hostvar_expression
@@ -8460,23 +8464,11 @@ datetime_misc_function : TOK_CONVERTTIMESTAMP '(' value_expression ')'
}
| TOK_DATEFORMAT '(' value_expression ',' date_format ')'
{
- if ( (SqlParser_DEFAULT_CHARSET == CharInfo::ISO88591) ||
- (CmpCommon::getDefault(ALLOW_IMPLICIT_CHAR_CASTING) == DF_ON) )
- {
- $$ = new (PARSERHEAP())
- DateFormat($3, NULL, $5);
- }
- else
- {
- $$ = new (PARSERHEAP()) ZZZBinderFunction( ITM_DATEFMT,
- $3, new (PARSERHEAP()) ConstValue($5) );
- }
+ $$ = new (PARSERHEAP())
+ DateFormat($3, *$5,
+ DateFormat::FORMAT_TO_CHAR,
+ TRUE);
}
- | TOK_DATEFMT_INTN '(' value_expression ',' date_format ')'
- {
- $$ = new (PARSERHEAP())
- DateFormat($3, NULL, $5);
- }
| TOK_DAYOFWEEK '(' value_expression ')'
{
@@ -8583,40 +8575,40 @@ datetime_misc_function : TOK_CONVERTTIMESTAMP '(' value_expression ')'
}
| TOK_TO_CHAR '(' value_expression ',' character_string_literal ')'
{
- CheckModeSpecial3;
-
$$ = new (PARSERHEAP())
- Format($3, *$5, FALSE, Format::FORMAT_TO_CHAR);
+ DateFormat($3, *$5, DateFormat::FORMAT_TO_CHAR);
}
| TOK_TO_CHAR '(' value_expression ')'
{
- CheckModeSpecial4;
- $$ = $3;
+ $$ = new (PARSERHEAP()) DateFormat
+ ($3, "YYYY-MM-DD", DateFormat::FORMAT_TO_CHAR);
+
}
| TOK_TO_DATE '(' value_expression ',' character_string_literal ')'
{
- CheckModeSpecial3;
-
- $$ = new (PARSERHEAP())
- Format($3, *$5, TRUE, Format::FORMAT_TO_DATE);
+ $$ = new (PARSERHEAP())
+ DateFormat($3, *$5, DateFormat::FORMAT_TO_DATE);
}
| TOK_TO_DATE '(' value_expression ')'
{
- CheckModeSpecial4;
-
$$ = new (PARSERHEAP())
- Format($3, "YYYY-MM-DD", TRUE, Format::FORMAT_TO_DATE);
+ DateFormat($3, "YYYY-MM-DD", DateFormat::FORMAT_TO_DATE);
}
| TOK_TO_NUMBER '(' value_expression ')'
{
- CheckModeSpecial3;
+ // CheckModeSpecial3;
$$ = new (PARSERHEAP())
ZZZBinderFunction(ITM_TO_NUMBER, $3);
}
+ | TOK_TO_TIME '(' value_expression ',' character_string_literal ')'
+ {
+ $$ = new (PARSERHEAP())
+ DateFormat($3, *$5, DateFormat::FORMAT_TO_DATE);
+ }
| TOK_TO_TIMESTAMP '(' value_expression ')'
{
- CheckModeSpecial4;
+ // CheckModeSpecial4;
$$ = new (PARSERHEAP())
ZZZBinderFunction(ITM_TO_TIMESTAMP, $3);
@@ -10321,10 +10313,19 @@ trim_spec : TOK_LEADING { $$ = (Int32) Trim::LEADING; }
-/* type uint */
-date_format : TOK_DEFAULT { $$ = (Int32) DateFormat::DEFAULT; }
- | TOK_EUROPEAN { $$ = (Int32) DateFormat::EUROPEAN; }
- | TOK_USA { $$ = (Int32) DateFormat::USA; }
+/* type stringval */
+date_format : TOK_DEFAULT
+ {
+ $$ = new (PARSERHEAP()) NAString(ExpDatetime::getFormatStr(ExpDatetime::DATETIME_FORMAT_DEFAULT));
+ }
+ | TOK_EUROPEAN
+ {
+ $$ = new (PARSERHEAP()) NAString(ExpDatetime::getFormatStr(ExpDatetime::DATETIME_FORMAT_EUROPEAN));
+ }
+ | TOK_USA
+ {
+ $$ = new (PARSERHEAP()) NAString(ExpDatetime::getFormatStr(ExpDatetime::DATETIME_FORMAT_USA));
+ }
/* type item */
@@ -33209,7 +33210,6 @@ nonreserved_func_word: TOK_ABS
| TOK_D_RANK
| TOK_DATABASE
| TOK_DATEFORMAT
- | TOK_DATEFMT_INTN
| TOK_DAYNAME
| TOK_DAYOFMONTH
| TOK_DAYOFWEEK
@@ -33305,6 +33305,7 @@ nonreserved_func_word: TOK_ABS
| TOK_TO_CHAR
| TOK_TO_DATE
| TOK_TO_NUMBER
+ | TOK_TO_TIME
| TOK_TO_TIMESTAMP
| TOK_TRUNC
| TOK_TRUNCATE
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/7526b83d/core/sql/regress/compGeneral/EXPECTED006.SB
----------------------------------------------------------------------
diff --git a/core/sql/regress/compGeneral/EXPECTED006.SB b/core/sql/regress/compGeneral/EXPECTED006.SB
index 3d1bf98..96cd90c 100644
--- a/core/sql/regress/compGeneral/EXPECTED006.SB
+++ b/core/sql/regress/compGeneral/EXPECTED006.SB
@@ -275,7 +275,7 @@
*** ERROR[8822] The statement was not prepared.
>>
->>control query default MODE_SPECIAL_3 'ON';
+>>control query default MODE_SPECIAL_4 'ON';
--- SQL operation complete.
>>prepare xx from select * from t006t1 where to_date(A, '99:99:99:99') = A;
@@ -284,7 +284,7 @@
*** ERROR[8822] The statement was not prepared.
->>control query default MODE_SPECIAL_3 reset;
+>>control query default MODE_SPECIAL_4 reset;
--- SQL operation complete.
>>
@@ -548,7 +548,7 @@
>>
>>-- Error 4065
->>control query default MODE_SPECIAL_3 'ON';
+>>control query default MODE_SPECIAL_4 'ON';
--- SQL operation complete.
>>prepare xx from select * from t006t1 where to_date(A, '201005') = A;
@@ -559,11 +559,11 @@
>>prepare xx from select * from t006t2 where to_date(A, 'YYYYMM') = A;
-*** ERROR[4065] The format, "YYYYMM", specified in the TO_DATE function is not supported.
+*** ERROR[4041] Type DATE cannot be compared with type INTEGER.
*** ERROR[8822] The statement was not prepared.
->>control query default MODE_SPECIAL_3 reset;
+>>control query default MODE_SPECIAL_4 reset;
--- SQL operation complete.
>>
@@ -723,13 +723,13 @@
--- SQL command prepared.
>>
->>control query default MODE_SPECIAL_3 'ON';
+>>control query default MODE_SPECIAL_4 'ON';
--- SQL operation complete.
>>prepare xx from select * from t006t2 where to_date(A, '99:99:99:99') = A;
--- SQL command prepared.
->>control query default MODE_SPECIAL_3 reset;
+>>control query default MODE_SPECIAL_4 reset;
--- SQL operation complete.
>>
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/7526b83d/core/sql/regress/compGeneral/TEST006
----------------------------------------------------------------------
diff --git a/core/sql/regress/compGeneral/TEST006 b/core/sql/regress/compGeneral/TEST006
index 06a0ff4..e560192 100644
--- a/core/sql/regress/compGeneral/TEST006
+++ b/core/sql/regress/compGeneral/TEST006
@@ -159,9 +159,9 @@ prepare xx from select user(a) from t006t1;
prepare xx from select ~a from t006t1;
prepare xx from select sin(a) from t006t1;
-control query default MODE_SPECIAL_3 'ON';
+control query default MODE_SPECIAL_4 'ON';
prepare xx from select * from t006t1 where to_date(A, '99:99:99:99') = A;
-control query default MODE_SPECIAL_3 reset;
+control query default MODE_SPECIAL_4 reset;
-- Error 4046
prepare xx from select ~(a) from t006t7;
@@ -233,10 +233,10 @@ control query default MODE_SPECIAL_3 reset;
prepare xx from select a**a from t006t1;
-- Error 4065
-control query default MODE_SPECIAL_3 'ON';
+control query default MODE_SPECIAL_4 'ON';
prepare xx from select * from t006t1 where to_date(A, '201005') = A;
prepare xx from select * from t006t2 where to_date(A, 'YYYYMM') = A;
-control query default MODE_SPECIAL_3 reset;
+control query default MODE_SPECIAL_4 reset;
-- Error 4067
prepare xx from select * from table (explain(NULL, 5));
@@ -295,9 +295,9 @@ prepare xx from select queryid_extract(a, b) from t006t4;
prepare xx from select round(3.224, 3, 2) from t006t1;
prepare xx from select user(a) from t006t1;
-control query default MODE_SPECIAL_3 'ON';
+control query default MODE_SPECIAL_4 'ON';
prepare xx from select * from t006t2 where to_date(A, '99:99:99:99') = A;
-control query default MODE_SPECIAL_3 reset;
+control query default MODE_SPECIAL_4 reset;
prepare xx from select current_running from t006t1;
prepare xx from select position(a in a) from t006t4;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/7526b83d/core/sql/regress/core/EXPECTED001.SB
----------------------------------------------------------------------
diff --git a/core/sql/regress/core/EXPECTED001.SB b/core/sql/regress/core/EXPECTED001.SB
index 2cbd15d..06bdac1 100644
--- a/core/sql/regress/core/EXPECTED001.SB
+++ b/core/sql/regress/core/EXPECTED001.SB
@@ -13,8 +13,8 @@
>>
>>invoke t001t1;
--- Definition of Seabase table SEABASE.SCH.T001T1
--- Definition current Wed Jul 31 14:25:27 2013
+-- Definition of Trafodion table TRAFODION.SCH.T001T1
+-- Definition current Mon Mar 7 19:30:10 2016
(
A INT DEFAULT NULL
@@ -30,8 +30,8 @@
>>#ifMX
>>invoke $$TEST_SCHEMA$$.t001ut1;
--- Definition of Seabase table SEABASE.SCH.T001UT1
--- Definition current Wed Jul 31 14:25:27 2013
+-- Definition of Trafodion table TRAFODION.SCH.T001UT1
+-- Definition current Mon Mar 7 19:30:10 2016
(
A INT DEFAULT NULL
@@ -184,19 +184,19 @@ A
>>
>>select b || NULL from t001t1;
-*** ERROR[4097] A NULL operand is not allowed in function (SEABASE.SCH.T001T1.B || NULL).
+*** ERROR[4097] A NULL operand is not allowed in function (TRAFODION.SCH.T001T1.B || NULL).
*** ERROR[8822] The statement was not prepared.
>>select NULL || b from t001t1;
-*** ERROR[4097] A NULL operand is not allowed in function (NULL || SEABASE.SCH.T001T1.B).
+*** ERROR[4097] A NULL operand is not allowed in function (NULL || TRAFODION.SCH.T001T1.B).
*** ERROR[8822] The statement was not prepared.
>>select a from t001t1 where c between 2 and null;
-*** ERROR[4099] A NULL operand is not allowed in predicate (SEABASE.SCH.T001T1.C BETWEEN 2 AND NULL).
+*** ERROR[4099] A NULL operand is not allowed in predicate (TRAFODION.SCH.T001T1.C BETWEEN 2 AND NULL).
*** ERROR[8822] The statement was not prepared.
@@ -208,13 +208,13 @@ A
>>select a from t001t1 where b like null;
-*** ERROR[4099] A NULL operand is not allowed in predicate (SEABASE.SCH.T001T1.B LIKE NULL).
+*** ERROR[4099] A NULL operand is not allowed in predicate (TRAFODION.SCH.T001T1.B LIKE NULL).
*** ERROR[8822] The statement was not prepared.
>>select a from t001t1 where b like 'x' escape null;
-*** ERROR[4099] A NULL operand is not allowed in predicate (SEABASE.SCH.T001T1.B like 'x' escape NULL).
+*** ERROR[4099] A NULL operand is not allowed in predicate (TRAFODION.SCH.T001T1.B like 'x' escape NULL).
*** ERROR[8822] The statement was not prepared.
@@ -261,7 +261,7 @@ A
>>select DateFormat (null, usa) from t001t1;
-*** ERROR[4097] A NULL operand is not allowed in function (DATEFORMAT(NULL)).
+*** ERROR[4097] A NULL operand is not allowed in function (DATEFORMAT(NULL, USA)).
*** ERROR[8822] The statement was not prepared.
@@ -345,6 +345,18 @@ A
*** ERROR[8822] The statement was not prepared.
+>>select To_char (null, 'YYYYMMDD') from t001t1;
+
+*** ERROR[4097] A NULL operand is not allowed in function (TO_CHAR(NULL, 'YYYYMMDD')).
+
+*** ERROR[8822] The statement was not prepared.
+
+>>select To_date (null, 'YYYYMMDD') from t001t1;
+
+*** ERROR[4097] A NULL operand is not allowed in function (TO_DATE(NULL, 'YYYYMMDD')).
+
+*** ERROR[8822] The statement was not prepared.
+
>>select Trim (null) from t001t1;
*** ERROR[4097] A NULL operand is not allowed in function TRIM, LTRIM or RTRIM.
@@ -379,7 +391,7 @@ A
>>select a from t001t1 where c in (1,null,2);
-*** ERROR[4099] A NULL operand is not allowed in predicate (SEABASE.SCH.T001T1.C = NULL).
+*** ERROR[4099] A NULL operand is not allowed in predicate (TRAFODION.SCH.T001T1.C = NULL).
*** ERROR[8822] The statement was not prepared.
@@ -398,13 +410,13 @@ A
>>select (case a when null then 1 else 2 end) from t001t1;
-*** ERROR[4099] A NULL operand is not allowed in predicate (SEABASE.SCH.T001T1.A = NULL).
+*** ERROR[4099] A NULL operand is not allowed in predicate (TRAFODION.SCH.T001T1.A = NULL).
*** ERROR[8822] The statement was not prepared.
>>select (case a when 1 then 1 when null then 2 else 3 end) from t001t1;
-*** ERROR[4099] A NULL operand is not allowed in predicate (SEABASE.SCH.T001T1.A = NULL).
+*** ERROR[4099] A NULL operand is not allowed in predicate (TRAFODION.SCH.T001T1.A = NULL).
*** ERROR[8822] The statement was not prepared.
@@ -485,8 +497,8 @@ A B C D (EXPR)
A B C D
----------- --------- ----------- ----
- 20 defg 30 wx
101 abc 20 xy
+ 20 defg 30 wx
--- 2 row(s) selected.
>>
@@ -501,8 +513,8 @@ A B C D
A B C D
----------- --------- ----------- ----
- 20 defg 30 wx
101 abc 20 xy
+ 20 defg 30 wx
--- 2 row(s) selected.
>>
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/7526b83d/core/sql/regress/core/TEST001
----------------------------------------------------------------------
diff --git a/core/sql/regress/core/TEST001 b/core/sql/regress/core/TEST001
index 023ac31..cd0ec0a 100755
--- a/core/sql/regress/core/TEST001
+++ b/core/sql/regress/core/TEST001
@@ -111,6 +111,8 @@ select Position (b in null) from t001t1;
select rtrim (null) from t001t1;
select Substring (null from 2) from t001t1;
select Substring (b from null) from t001t1;
+select To_char (null, 'YYYYMMDD') from t001t1;
+select To_date (null, 'YYYYMMDD') from t001t1;
select Trim (null) from t001t1;
select Upper (null) from t001t1;
select Upshift (null) from t001t1;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/7526b83d/core/sql/regress/seabase/EXPECTED030
----------------------------------------------------------------------
diff --git a/core/sql/regress/seabase/EXPECTED030 b/core/sql/regress/seabase/EXPECTED030
new file mode 100644
index 0000000..ee6d5b5
--- /dev/null
+++ b/core/sql/regress/seabase/EXPECTED030
@@ -0,0 +1,498 @@
+>>
+>>drop table if exists t030t1;
+
+--- SQL operation complete.
+>>create table t030t1 (a date);
+
+--- SQL operation complete.
+>>insert into t030t1 values (date '2016-03-01');
+
+--- 1 row(s) inserted.
+>>
+>>select to_date('2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a);
+
+(EXPR)
+----------
+
+2016-03-01
+
+--- 1 row(s) selected.
+>>select to_date('03/01/2016', 'MM/DD/YYYY') from (values(1)) x(a);
+
+(EXPR)
+----------
+
+2016-03-01
+
+--- 1 row(s) selected.
+>>select to_date('01.03.2016', 'DD.MM.YYYY') from (values(1)) x(a);
+
+(EXPR)
+----------
+
+2016-03-01
+
+--- 1 row(s) selected.
+>>select to_date('2016-03', 'YYYY-MM') from (values(1)) x(a);
+
+(EXPR)
+----------
+
+2016-03-01
+
+--- 1 row(s) selected.
+>>select to_date('2016/03/01', 'YYYY/MM/DD') from (values(1)) x(a);
+
+(EXPR)
+----------
+
+2016-03-01
+
+--- 1 row(s) selected.
+>>select to_date('20160301', 'YYYYMMDD') from (values(1)) x(a);
+
+(EXPR)
+----------
+
+2016-03-01
+
+--- 1 row(s) selected.
+>>select to_date('16/03/01', 'YY/MM/DD') from (values(1)) x(a);
+
+(EXPR)
+----------
+
+0016-03-01
+
+--- 1 row(s) selected.
+>>select to_date('03/01/16', 'MM/DD/YY') from (values(1)) x(a);
+
+(EXPR)
+----------
+
+0016-03-01
+
+--- 1 row(s) selected.
+>>select to_date('03-01-2016', 'MM-DD-YYYY') from (values(1)) x(a);
+
+(EXPR)
+----------
+
+2016-03-01
+
+--- 1 row(s) selected.
+>>select to_date('201603', 'YYYYMM') from (values(1)) x(a);
+
+(EXPR)
+----------
+
+2016-03-01
+
+--- 1 row(s) selected.
+>>select to_date('01-03-2016', 'DD-MM-YYYY') from (values(1)) x(a);
+
+(EXPR)
+----------
+
+2016-03-01
+
+--- 1 row(s) selected.
+>>select to_date('01-MAR-2016', 'DD-MON-YYYY') from (values(1)) x(a);
+
+(EXPR)
+----------
+
+2016-03-01
+
+--- 1 row(s) selected.
+>>select to_date('01MAR2016', 'DDMONYYYY') from (values(1)) x(a);
+
+(EXPR)
+----------
+
+2016-03-01
+
+--- 1 row(s) selected.
+>>select to_date('20160301101112', 'YYYYMMDDHH24MISS') from (values(1)) x(a);
+
+(EXPR)
+--------------------------
+
+2016-03-01 10:11:12.000000
+
+--- 1 row(s) selected.
+>>select to_date('01.03.2016:10:11:12', 'DD.MM.YYYY:HH24:MI:SS') from (values(1)) x(a);
+
+(EXPR)
+--------------------------
+
+2016-03-01 10:11:12.000000
+
+--- 1 row(s) selected.
+>>select to_date('2016-03-01 10:11:12', 'YYYY-MM-DD HH24:MI:SS') from (values(1)) x(a);
+
+(EXPR)
+--------------------------
+
+2016-03-01 10:11:12.000000
+
+--- 1 row(s) selected.
+>>select to_date('03/01/2016 10:11:12', 'MM/DD/YYYY HH24:MI:SS') from (values(1)) x(a);
+
+(EXPR)
+--------------------------
+
+2016-03-01 10:11:12.000000
+
+--- 1 row(s) selected.
+>>select to_date('01-MAR-2016 10:11:12', 'DD-MON-YYYY HH:MI:SS') from (values(1)) x(a);
+
+(EXPR)
+--------------------------
+
+2016-03-01 10:11:12.000000
+
+--- 1 row(s) selected.
+>>select to_date('March 01, 2016, 10:11', 'MONTH DD, YYYY, HH:MI') from (values(1)) x(a);
+
+(EXPR)
+--------------------------
+
+2016-03-01 10:11:00.000000
+
+--- 1 row(s) selected.
+>>select to_date ('10:23:34', 'HH24:MI:SS') from (values(1)) x(a);
+
+(EXPR)
+--------
+
+10:23:34
+
+--- 1 row(s) selected.
+>>select to_date ('10:23:34', 'HH:MI:SS') from (values(1)) x(a);
+
+(EXPR)
+--------
+
+10:23:34
+
+--- 1 row(s) selected.
+>>select to_time ('10:23:34', 'HH24:MI:SS') from (values(1)) x(a);
+
+(EXPR)
+--------
+
+10:23:34
+
+--- 1 row(s) selected.
+>>select to_time ('10:23:34', 'HH:MI:SS') from (values(1)) x(a);
+
+(EXPR)
+--------
+
+10:23:34
+
+--- 1 row(s) selected.
+>>
+>>select to_char(DATE '2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a);
+
+(EXPR)
+----------
+
+2016-03-01
+
+--- 1 row(s) selected.
+>>select to_char(DATE '2016-03-01', 'MM/DD/YYYY') from (values(1)) x(a);
+
+(EXPR)
+----------
+
+03/01/2016
+
+--- 1 row(s) selected.
+>>select to_char(DATE '2016-03-01', 'DD.MM.YYYY') from (values(1)) x(a);
+
+(EXPR)
+----------
+
+01.03.2016
+
+--- 1 row(s) selected.
+>>select to_char(DATE '2016-03-01', 'YYYY-MM') from (values(1)) x(a);
+
+(EXPR)
+-------
+
+2016-03
+
+--- 1 row(s) selected.
+>>select to_char(DATE '2016-03-01', 'YYYY/MM/DD') from (values(1)) x(a);
+
+(EXPR)
+----------
+
+2016/03/01
+
+--- 1 row(s) selected.
+>>select to_char(DATE '2016-03-01', 'YYYYMMDD') from (values(1)) x(a);
+
+(EXPR)
+--------
+
+20160301
+
+--- 1 row(s) selected.
+>>select to_char(DATE '2016-03-01', 'YY/MM/DD') from (values(1)) x(a);
+
+(EXPR)
+--------
+
+16/03/01
+
+--- 1 row(s) selected.
+>>select to_char(DATE '2016-03-01', 'MM/DD/YY') from (values(1)) x(a);
+
+(EXPR)
+--------
+
+03/01/16
+
+--- 1 row(s) selected.
+>>select to_char(DATE '2016-03-01', 'MM-DD-YYYY') from (values(1)) x(a);
+
+(EXPR)
+----------
+
+03-01-2016
+
+--- 1 row(s) selected.
+>>select to_char(DATE '2016-03-01', 'YYYYMM') from (values(1)) x(a);
+
+(EXPR)
+------
+
+201603
+
+--- 1 row(s) selected.
+>>select to_char(DATE '2016-03-01', 'DD-MM-YYYY') from (values(1)) x(a);
+
+(EXPR)
+----------
+
+01-03-2016
+
+--- 1 row(s) selected.
+>>select to_char(DATE '2016-03-01', 'DD-MON-YYYY') from (values(1)) x(a);
+
+(EXPR)
+-----------
+
+01-MAR-2016
+
+--- 1 row(s) selected.
+>>select to_char(DATE '2016-03-01', 'DDMONYYYY') from (values(1)) x(a);
+
+(EXPR)
+---------
+
+01MAR2016
+
+--- 1 row(s) selected.
+>>select to_char(DATE '2016-03-01', 'YYYYMMDDHH24MISS') from (values(1)) x(a);
+
+(EXPR)
+--------------
+
+2016030100:00:
+
+--- 1 row(s) selected.
+>>select to_char(DATE '2016-03-01', 'DD.MM.YYYY:HH24:MI:SS') from (values(1)) x(a);
+
+(EXPR)
+-------------------
+
+01.03.2016:00:00:00
+
+--- 1 row(s) selected.
+>>select to_char(DATE '2016-03-01', 'YYYY-MM-DD HH24:MI:SS') from (values(1)) x(a);
+
+(EXPR)
+-------------------
+
+2016-03-01 00:00:00
+
+--- 1 row(s) selected.
+>>select to_char(DATE '2016-03-01', 'MM/DD/YYYY HH24:MI:SS') from (values(1)) x(a);
+
+(EXPR)
+-------------------
+
+03/01/2016 00:00:00
+
+--- 1 row(s) selected.
+>>select to_char(DATE '2016-03-01', 'DD-MON-YYYY HH:MI:SS') from (values(1)) x(a);
+
+(EXPR)
+--------------------
+
+01-MAR-2016 00:00:00
+
+--- 1 row(s) selected.
+>>select to_char(TIMESTAMP '2016-03-01 10:11:12', 'YYYYMMDDHH24MISS') from (values(1)) x(a);
+
+(EXPR)
+--------------
+
+20160301101112
+
+--- 1 row(s) selected.
+>>select to_char(TIMESTAMP '2016-03-01 10:11:12', 'DD.MM.YYYY:HH24:MI:SS') from (values(1)) x(a);
+
+(EXPR)
+-------------------
+
+01.03.2016:10:11:12
+
+--- 1 row(s) selected.
+>>select to_char(TIMESTAMP '2016-03-01 10:11:12', 'YYYY-MM-DD HH24:MI:SS') from (values(1)) x(a);
+
+(EXPR)
+-------------------
+
+2016-03-01 10:11:12
+
+--- 1 row(s) selected.
+>>select to_char(TIMESTAMP '2016-03-01 10:11:12', 'MM/DD/YYYY HH24:MI:SS') from (values(1)) x(a);
+
+(EXPR)
+-------------------
+
+03/01/2016 10:11:12
+
+--- 1 row(s) selected.
+>>select to_char(TIMESTAMP '2016-03-01 10:11:12', 'DD-MON-YYYY HH:MI:SS') from (values(1)) x(a);
+
+(EXPR)
+--------------------
+
+01-MAR-2016 10:11:12
+
+--- 1 row(s) selected.
+>>select to_char(TIMESTAMP '2016-03-01 10:11:12', 'MONTH DD, YYYY, HH:MI') from (values(1)) x(a);
+
+(EXPR)
+-------------------------
+
+March 01, 2016, 10:11:12
+
+--- 1 row(s) selected.
+>>select to_char (time '10:23:34', 'HH24:MI:SS') from (values(1)) x(a);
+
+(EXPR)
+--------
+
+10:23:34
+
+--- 1 row(s) selected.
+>>select to_char (time '10:23:34', 'HH:MI:SS') from (values(1)) x(a);
+
+(EXPR)
+--------
+
+10:23:34
+
+--- 1 row(s) selected.
+>>
+>>select to_char(a, 'YYYYMMDD') from t030t1;
+
+(EXPR)
+--------
+
+20160301
+
+--- 1 row(s) selected.
+>>select a (date, format 'YYYYMMDD') from t030t1;
+
+(EXPR)
+--------
+
+20160301
+
+--- 1 row(s) selected.
+>>
+>>-- negative tests
+>>select to_date('2016-03-01', 'YYYYMM-DD') from (values(1)) x(a);
+
+*** ERROR[4065] The format, "YYYYMM-DD", specified in the TO_DATE function is not supported.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>select to_date(DATE '2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a);
+
+*** ERROR[4043] The operand of function TO_DATE must be character.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>select to_date('0103.2016', 'DD.MM.YYYY') from (values(1)) x(a);
+
+*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted.
+
+--- 0 row(s) selected.
+>>select to_char('2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a);
+
+*** ERROR[4071] The first operand of function TO_CHAR must be a datetime.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>select to_date(20160301, 'YYYYMMDD') from (values(1)) x(a);
+
+*** ERROR[4043] The operand of function TO_DATE must be character.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>
+>>-- some formats only enabled in special mode. Not externalized.
+>>cqd mode_special_4 'ON';
+
+--- SQL operation complete.
+>>select to_date(20160301, 'YYYYMMDD') from (values(1)) x(a);
+
+(EXPR)
+----------
+
+2016-03-01
+
+--- 1 row(s) selected.
+>>select to_date(12345678, '99:99:99:99') from (values(1)) x(a);
+
+(EXPR)
+-----------
+
+12:34:56:78
+
+--- 1 row(s) selected.
+>>select to_date(-12345678, '-99:99:99:99') from (values(1)) x(a);
+
+(EXPR)
+------------
+
+-12:34:56:78
+
+--- 1 row(s) selected.
+>>-- error case
+>>select to_date(123456789, '99:99:99:99') from (values(1)) x(a);
+
+*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted.
+
+*** ERROR[8001] An internal executor error occurred.
+
+--- 0 row(s) selected.
+>>select to_date(-12345678, '99:99:99:99') from (values(1)) x(a);
+
+*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted.
+
+*** ERROR[8001] An internal executor error occurred.
+
+--- 0 row(s) selected.
+>>
+>>log;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/7526b83d/core/sql/regress/seabase/TEST030
----------------------------------------------------------------------
diff --git a/core/sql/regress/seabase/TEST030 b/core/sql/regress/seabase/TEST030
new file mode 100644
index 0000000..ba28d9b
--- /dev/null
+++ b/core/sql/regress/seabase/TEST030
@@ -0,0 +1,101 @@
+-- @@@ 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 @@@
+--
+
+-- tests for TO_DATE, TO_CHAR, TO_TIMESTAMP, TO_NUMBER functions
+
+log LOG030 clear;
+
+drop table if exists t030t1;
+create table t030t1 (a date);
+insert into t030t1 values (date '2016-03-01');
+
+select to_date('2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a);
+select to_date('03/01/2016', 'MM/DD/YYYY') from (values(1)) x(a);
+select to_date('01.03.2016', 'DD.MM.YYYY') from (values(1)) x(a);
+select to_date('2016-03', 'YYYY-MM') from (values(1)) x(a);
+select to_date('2016/03/01', 'YYYY/MM/DD') from (values(1)) x(a);
+select to_date('20160301', 'YYYYMMDD') from (values(1)) x(a);
+select to_date('16/03/01', 'YY/MM/DD') from (values(1)) x(a);
+select to_date('03/01/16', 'MM/DD/YY') from (values(1)) x(a);
+select to_date('03-01-2016', 'MM-DD-YYYY') from (values(1)) x(a);
+select to_date('201603', 'YYYYMM') from (values(1)) x(a);
+select to_date('01-03-2016', 'DD-MM-YYYY') from (values(1)) x(a);
+select to_date('01-MAR-2016', 'DD-MON-YYYY') from (values(1)) x(a);
+select to_date('01MAR2016', 'DDMONYYYY') from (values(1)) x(a);
+select to_date('20160301101112', 'YYYYMMDDHH24MISS') from (values(1)) x(a);
+select to_date('01.03.2016:10:11:12', 'DD.MM.YYYY:HH24:MI:SS') from (values(1)) x(a);
+select to_date('2016-03-01 10:11:12', 'YYYY-MM-DD HH24:MI:SS') from (values(1)) x(a);
+select to_date('03/01/2016 10:11:12', 'MM/DD/YYYY HH24:MI:SS') from (values(1)) x(a);
+select to_date('01-MAR-2016 10:11:12', 'DD-MON-YYYY HH:MI:SS') from (values(1)) x(a);
+select to_date('March 01, 2016, 10:11', 'MONTH DD, YYYY, HH:MI') from (values(1)) x(a);
+select to_date ('10:23:34', 'HH24:MI:SS') from (values(1)) x(a);
+select to_date ('10:23:34', 'HH:MI:SS') from (values(1)) x(a);
+select to_time ('10:23:34', 'HH24:MI:SS') from (values(1)) x(a);
+select to_time ('10:23:34', 'HH:MI:SS') from (values(1)) x(a);
+
+select to_char(DATE '2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a);
+select to_char(DATE '2016-03-01', 'MM/DD/YYYY') from (values(1)) x(a);
+select to_char(DATE '2016-03-01', 'DD.MM.YYYY') from (values(1)) x(a);
+select to_char(DATE '2016-03-01', 'YYYY-MM') from (values(1)) x(a);
+select to_char(DATE '2016-03-01', 'YYYY/MM/DD') from (values(1)) x(a);
+select to_char(DATE '2016-03-01', 'YYYYMMDD') from (values(1)) x(a);
+select to_char(DATE '2016-03-01', 'YY/MM/DD') from (values(1)) x(a);
+select to_char(DATE '2016-03-01', 'MM/DD/YY') from (values(1)) x(a);
+select to_char(DATE '2016-03-01', 'MM-DD-YYYY') from (values(1)) x(a);
+select to_char(DATE '2016-03-01', 'YYYYMM') from (values(1)) x(a);
+select to_char(DATE '2016-03-01', 'DD-MM-YYYY') from (values(1)) x(a);
+select to_char(DATE '2016-03-01', 'DD-MON-YYYY') from (values(1)) x(a);
+select to_char(DATE '2016-03-01', 'DDMONYYYY') from (values(1)) x(a);
+select to_char(DATE '2016-03-01', 'YYYYMMDDHH24MISS') from (values(1)) x(a);
+select to_char(DATE '2016-03-01', 'DD.MM.YYYY:HH24:MI:SS') from (values(1)) x(a);
+select to_char(DATE '2016-03-01', 'YYYY-MM-DD HH24:MI:SS') from (values(1)) x(a);
+select to_char(DATE '2016-03-01', 'MM/DD/YYYY HH24:MI:SS') from (values(1)) x(a);
+select to_char(DATE '2016-03-01', 'DD-MON-YYYY HH:MI:SS') from (values(1)) x(a);
+select to_char(TIMESTAMP '2016-03-01 10:11:12', 'YYYYMMDDHH24MISS') from (values(1)) x(a);
+select to_char(TIMESTAMP '2016-03-01 10:11:12', 'DD.MM.YYYY:HH24:MI:SS') from (values(1)) x(a);
+select to_char(TIMESTAMP '2016-03-01 10:11:12', 'YYYY-MM-DD HH24:MI:SS') from (values(1)) x(a);
+select to_char(TIMESTAMP '2016-03-01 10:11:12', 'MM/DD/YYYY HH24:MI:SS') from (values(1)) x(a);
+select to_char(TIMESTAMP '2016-03-01 10:11:12', 'DD-MON-YYYY HH:MI:SS') from (values(1)) x(a);
+select to_char(TIMESTAMP '2016-03-01 10:11:12', 'MONTH DD, YYYY, HH:MI') from (values(1)) x(a);
+select to_char (time '10:23:34', 'HH24:MI:SS') from (values(1)) x(a);
+select to_char (time '10:23:34', 'HH:MI:SS') from (values(1)) x(a);
+
+select to_char(a, 'YYYYMMDD') from t030t1;
+select a (date, format 'YYYYMMDD') from t030t1;
+
+-- negative tests
+select to_date('2016-03-01', 'YYYYMM-DD') from (values(1)) x(a);
+select to_date(DATE '2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a);
+select to_date('0103.2016', 'DD.MM.YYYY') from (values(1)) x(a);
+select to_char('2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a);
+select to_date(20160301, 'YYYYMMDD') from (values(1)) x(a);
+
+-- some formats only enabled in special mode. Not externalized.
+cqd mode_special_4 'ON';
+select to_date(20160301, 'YYYYMMDD') from (values(1)) x(a);
+select to_date(12345678, '99:99:99:99') from (values(1)) x(a);
+select to_date(-12345678, '-99:99:99:99') from (values(1)) x(a);
+-- error case
+select to_date(123456789, '99:99:99:99') from (values(1)) x(a);
+select to_date(-12345678, '99:99:99:99') from (values(1)) x(a);
+
+log;
[3/6] incubator-trafodion git commit: Merge remote branch
'origin/master' into ansharma_functions_br
Posted by an...@apache.org.
Merge remote branch 'origin/master' into ansharma_functions_br
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/5c1e4ba0
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/5c1e4ba0
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/5c1e4ba0
Branch: refs/heads/master
Commit: 5c1e4ba0a9fea95709dc39099ab0e67006afccb7
Parents: 7526b83 57d3ebd
Author: Cloud User <ce...@ansharma-2.novalocal>
Authored: Mon Mar 7 22:06:12 2016 +0000
Committer: Cloud User <ce...@ansharma-2.novalocal>
Committed: Mon Mar 7 22:06:12 2016 +0000
----------------------------------------------------------------------
core/sqf/sql/scripts/genstats.sh | 43 ++
core/sql/SqlCompilerDebugger/mk.sh | 5 +
core/sql/executor/ExStats.cpp | 13 +-
core/sql/executor/hiveHook.cpp | 2 +-
core/sql/nskgmake/SqlCompilerDebugger/Makefile | 5 +-
core/sql/ustat/hs_cli.cpp | 22 +-
core/sql/ustat/hs_globals.cpp | 22 +-
.../src/asciidoc/_chapters/about.adoc | 1 +
.../src/asciidoc/_chapters/odb.adoc | 14 +-
.../src/asciidoc/_chapters/about.adoc | 1 +
docs/cqd_reference/pom.xml | 291 +++++++++++
.../src/asciidoc/_chapters/about.adoc | 179 +++++++
.../src/asciidoc/_chapters/histograms.adoc | 215 ++++++++
.../src/asciidoc/_chapters/introduction.adoc | 111 ++++
.../_chapters/operational_controls.adoc | 110 ++++
.../src/asciidoc/_chapters/query_plans.adoc | 515 +++++++++++++++++++
.../asciidoc/_chapters/runtime_controls.adoc | 83 +++
.../src/asciidoc/_chapters/schema_controls.adoc | 49 ++
.../src/asciidoc/_chapters/transactions.adoc | 94 ++++
.../src/asciidoc/_chapters/update_stats.adoc | 92 ++++
docs/cqd_reference/src/asciidoc/index.adoc | 69 +++
docs/load_transform/pom.xml | 289 +++++++++++
.../src/asciidoc/_chapters/about.adoc | 186 +++++++
.../src/asciidoc/_chapters/bulk_load.adoc | 291 +++++++++++
.../src/asciidoc/_chapters/bulk_unload.adoc | 73 +++
.../src/asciidoc/_chapters/introduction.adoc | 117 +++++
.../src/asciidoc/_chapters/monitor.adoc | 77 +++
.../src/asciidoc/_chapters/tables_indexes.adoc | 208 ++++++++
.../src/asciidoc/_chapters/trickle_load.adoc | 496 ++++++++++++++++++
.../src/asciidoc/_chapters/troubleshoot.adoc | 159 ++++++
docs/load_transform/src/asciidoc/index.adoc | 68 +++
.../src/asciidoc/_chapters/about.adoc | 3 +
docs/odb_user/src/asciidoc/_chapters/about.adoc | 3 +
docs/odb_user/src/asciidoc/_chapters/load.adoc | 48 +-
.../src/asciidoc/_chapters/about.adoc | 2 +
.../src/asciidoc/_chapters/about.adoc | 2 +
.../src/asciidoc/_chapters/cqds.adoc | 20 -
.../src/asciidoc/_chapters/sql_statements.adoc | 8 +-
.../src/asciidoc/_chapters/sql_utilities.adoc | 117 ++++-
docs/src/site/markdown/download.md | 18 +-
docs/src/site/markdown/index.md | 12 +-
docs/src/site/markdown/logo.md | 56 ++
docs/src/site/resources/css/site.css | 18 +-
.../site/resources/images/carousel/scale.png | Bin 93783 -> 127571 bytes
.../site/resources/images/carousel/stack.png | Bin 102472 -> 170554 bytes
.../resources/images/logos/pb-Trafodion.png | Bin 0 -> 49089 bytes
.../resources/images/logos/pb-trafodion.jpg | Bin 0 -> 80904 bytes
.../images/logos/trafodion-dragon-large.jpg | Bin 0 -> 50000 bytes
.../images/logos/trafodion-dragon-large.png | Bin 0 -> 20595 bytes
.../resources/images/logos/trafodion-dragon.png | Bin 0 -> 12786 bytes
.../resources/images/logos/trafodion-logo.jpg | Bin 18953 -> 58953 bytes
docs/src/site/site.xml | 20 +-
install/installer/dcs_installer | 8 +-
install/installer/parseHBaseSite.py | 55 ++
install/installer/traf_add_sudoAccess | 5 +-
install/installer/traf_config_check | 8 +-
install/installer/traf_hortonworks_mods98 | 26 -
install/installer/traf_sqgen | 2 +-
install/installer/trafodion_install | 17 +-
59 files changed, 4170 insertions(+), 178 deletions(-)
----------------------------------------------------------------------
[4/6] incubator-trafodion git commit: Merge remote branch
'origin/master' into ansharma_functions_br
Posted by an...@apache.org.
Merge remote branch 'origin/master' into ansharma_functions_br
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/ccb3e444
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/ccb3e444
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/ccb3e444
Branch: refs/heads/master
Commit: ccb3e4441db6389a170651f0b4c2ce39b39e23f2
Parents: 5c1e4ba df0f847
Author: Cloud User <ce...@ansharma-2.novalocal>
Authored: Tue Mar 8 14:43:20 2016 +0000
Committer: Cloud User <ce...@ansharma-2.novalocal>
Committed: Tue Mar 8 14:43:20 2016 +0000
----------------------------------------------------------------------
core/sqf/Makefile | 2 +-
core/sql/generator/GenPreCode.cpp | 4 +-
core/sql/optimizer/BindRI.cpp | 45 +++++++++++++++-
core/sql/optimizer/ValueDesc.cpp | 5 +-
core/sql/optimizer/ValueDesc.h | 6 +--
core/sql/regress/compGeneral/EXPECTED013.SB | 48 +++++++++++++++++
core/sql/regress/compGeneral/TEST013 | 28 ++++++++++
core/sql/regress/core/EXPECTED056.SB | 6 ---
core/sql/regress/core/TEST029 | 1 -
core/sql/regress/core/TEST056 | 2 -
core/sql/regress/tools/runregr_udr.ksh | 65 ++----------------------
11 files changed, 132 insertions(+), 80 deletions(-)
----------------------------------------------------------------------
[6/6] incubator-trafodion git commit: Merge [TRAFODION-1873] PR-371
Externalize to_date, to_char
Posted by an...@apache.org.
Merge [TRAFODION-1873] PR-371 Externalize to_date, to_char
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/6b9dea5c
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/6b9dea5c
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/6b9dea5c
Branch: refs/heads/master
Commit: 6b9dea5c1aff5b192c0bf256e5ec926f0f032f5b
Parents: 1eb6051 5fc9ce5
Author: Anoop Sharma <an...@edev06.esgyn.local>
Authored: Sat Mar 12 19:29:42 2016 +0000
Committer: Anoop Sharma <an...@edev06.esgyn.local>
Committed: Sat Mar 12 19:29:42 2016 +0000
----------------------------------------------------------------------
core/sql/exp/exp_datetime.cpp | 1228 +++++++++-------------
core/sql/exp/exp_datetime.h | 186 ++--
core/sql/exp/exp_function.cpp | 22 +-
core/sql/generator/GenItemFunc.cpp | 134 +--
core/sql/optimizer/BindItemExpr.cpp | 584 +++++-----
core/sql/optimizer/ItemCache.cpp | 5 +-
core/sql/optimizer/ItemExpr.cpp | 52 +-
core/sql/optimizer/ItemFunc.h | 53 +-
core/sql/optimizer/QRDescGenerator.cpp | 19 +-
core/sql/optimizer/SynthType.cpp | 148 +--
core/sql/parser/ParKeyWords.cpp | 6 +-
core/sql/parser/sqlparser.y | 74 +-
core/sql/regress/compGeneral/EXPECTED006.SB | 14 +-
core/sql/regress/compGeneral/TEST006 | 12 +-
core/sql/regress/core/EXPECTED001.SB | 42 +-
core/sql/regress/core/TEST001 | 2 +
core/sql/regress/seabase/EXPECTED030 | 550 ++++++++++
core/sql/regress/seabase/TEST030 | 110 ++
18 files changed, 1759 insertions(+), 1482 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/6b9dea5c/core/sql/generator/GenItemFunc.cpp
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/6b9dea5c/core/sql/optimizer/ItemFunc.h
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/6b9dea5c/core/sql/parser/ParKeyWords.cpp
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/6b9dea5c/core/sql/parser/sqlparser.y
----------------------------------------------------------------------
[2/6] incubator-trafodion git commit: datetime functions: commit #1
Posted by an...@apache.org.
datetime functions: commit #1
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/7526b83d
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/7526b83d
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/7526b83d
Branch: refs/heads/master
Commit: 7526b83d21b8f7404a7f1527a83c23eaa8a178d3
Parents: eb036aa
Author: Cloud User <ce...@ansharma-2.novalocal>
Authored: Mon Mar 7 22:05:20 2016 +0000
Committer: Cloud User <ce...@ansharma-2.novalocal>
Committed: Mon Mar 7 22:05:20 2016 +0000
----------------------------------------------------------------------
core/sql/exp/exp_datetime.cpp | 1210 +++++++++-------------
core/sql/exp/exp_datetime.h | 182 ++--
core/sql/exp/exp_function.cpp | 18 +-
core/sql/generator/GenItemFunc.cpp | 134 +--
core/sql/optimizer/BindItemExpr.cpp | 573 +++++-----
core/sql/optimizer/ItemCache.cpp | 5 +-
core/sql/optimizer/ItemExpr.cpp | 52 +-
core/sql/optimizer/ItemFunc.h | 53 +-
core/sql/optimizer/QRDescGenerator.cpp | 19 +-
core/sql/optimizer/SynthType.cpp | 148 +--
core/sql/parser/ParKeyWords.cpp | 6 +-
core/sql/parser/sqlparser.y | 81 +-
core/sql/regress/compGeneral/EXPECTED006.SB | 14 +-
core/sql/regress/compGeneral/TEST006 | 12 +-
core/sql/regress/core/EXPECTED001.SB | 42 +-
core/sql/regress/core/TEST001 | 2 +
core/sql/regress/seabase/EXPECTED030 | 498 +++++++++
core/sql/regress/seabase/TEST030 | 101 ++
18 files changed, 1676 insertions(+), 1474 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/7526b83d/core/sql/exp/exp_datetime.cpp
----------------------------------------------------------------------
diff --git a/core/sql/exp/exp_datetime.cpp b/core/sql/exp/exp_datetime.cpp
index 781f2dd..6b93d1e 100644
--- a/core/sql/exp/exp_datetime.cpp
+++ b/core/sql/exp/exp_datetime.cpp
@@ -91,6 +91,50 @@ copyDatetimeFields(rec_datetime_field startField,
char *dstData,
NABoolean *roundedDownFlag);
+//////////////////////////////////////////////
+// Defined in exp_datetime.h
+//
+// struct DatetimeFormatInfo
+// {
+// Lng32 format;
+// const char * str;
+// Lng32 minLen;
+// Lng32 maxLen
+// };
+//////////////////////////////////////////////
+const ExpDatetime::DatetimeFormatInfo ExpDatetime::datetimeFormat[] =
+ {
+ {ExpDatetime::DATETIME_FORMAT_DEFAULT, "YYYY-MM-DD", 10, 10},
+ {ExpDatetime::DATETIME_FORMAT_USA, "MM/DD/YYYY", 10, 10},
+ {ExpDatetime::DATETIME_FORMAT_EUROPEAN, "DD.MM.YYYY", 10, 10},
+ {ExpDatetime::DATETIME_FORMAT_DEFAULT2, "YYYY-MM", 7, 7},
+ {ExpDatetime::DATETIME_FORMAT_USA2, "MM/DD/YYYY", 10, 10},
+ {ExpDatetime::DATETIME_FORMAT_USA3, "YYYY/MM/DD", 10, 10},
+ {ExpDatetime::DATETIME_FORMAT_USA4, "YYYYMMDD", 8, 8},
+ {ExpDatetime::DATETIME_FORMAT_USA5, "YY/MM/DD", 8, 8},
+ {ExpDatetime::DATETIME_FORMAT_USA6, "MM/DD/YY", 8, 8},
+ {ExpDatetime::DATETIME_FORMAT_USA7, "MM-DD-YYYY", 10, 10},
+ {ExpDatetime::DATETIME_FORMAT_USA8, "YYYYMM", 6, 6},
+ {ExpDatetime::DATETIME_FORMAT_EUROPEAN2, "DD-MM-YYYY", 10, 10},
+ {ExpDatetime::DATETIME_FORMAT_EUROPEAN3, "DD-MON-YYYY", 11, 11},
+ {ExpDatetime::DATETIME_FORMAT_EUROPEAN4, "DDMONYYYY", 9, 9},
+
+ {ExpDatetime::DATETIME_FORMAT_TS4, "HH24:MI:SS", 8, 8},
+
+ {ExpDatetime::DATETIME_FORMAT_TS1, "YYYYMMDDHH24MISS", 14, 14},
+ {ExpDatetime::DATETIME_FORMAT_TS2, "DD.MM.YYYY:HH24:MI:SS", 19, 19},
+ {ExpDatetime::DATETIME_FORMAT_TS3, "YYYY-MM-DD HH24:MI:SS", 19, 19},
+ {ExpDatetime::DATETIME_FORMAT_TS5, "YYYYMMDD:HH24:MI:SS", 17, 17},
+ {ExpDatetime::DATETIME_FORMAT_TS6, "MMDDYYYY HH24:MI:SS", 17, 17},
+ {ExpDatetime::DATETIME_FORMAT_TS7, "MM/DD/YYYY HH24:MI:SS", 19, 19},
+ {ExpDatetime::DATETIME_FORMAT_TS8, "DD-MON-YYYY HH:MI:SS", 20, 20},
+ {ExpDatetime::DATETIME_FORMAT_TS9, "MONTH DD, YYYY, HH:MI", 19, 25},
+
+ {ExpDatetime::DATETIME_FORMAT_NUM1, "99:99:99:99", 11, 11},
+ {ExpDatetime::DATETIME_FORMAT_NUM2, "-99:99:99:99", 12, 12}
+
+ };
+
ExpDatetime::ExpDatetime()
{
setClassID(SimpleTypeID);
@@ -2302,9 +2346,9 @@ ExpDatetime::convAsciiToDatetime(char *srcData,
heap, diagsArea, flags);
}
-NA_EIDPROC
-static NABoolean
-convertStrToMonth(char * value, char *result)
+static NABoolean convertStrToMonth(char* &srcData, char *result,
+ const char * nextByte,
+ CollHeap * heap, ComDiagsArea** diagsArea)
{
const char * months[] =
{
@@ -2325,10 +2369,59 @@ convertStrToMonth(char * value, char *result)
for (Int32 i = 0; i < 12; i++)
{
char upVal[3];
- str_cpy_convert(upVal, value, 3, 1);
+ str_cpy_convert(upVal, srcData, 3, 1);
if (memcmp(upVal, months[i], 3) == 0)
{
*result = (char)(i + 1);
+
+ srcData += 3;
+
+ if (nextByte)
+ {
+ if (*srcData != *nextByte)
+ {
+ // string contains non-digit
+ ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
+ return FALSE; // error
+ }
+ srcData++;
+ }
+
+ return TRUE;
+ }
+ } // for
+
+ // error
+ return FALSE;
+}
+
+static NABoolean
+convertStrToMonthLongFormat(char* &value, char *result)
+{
+ const char * months[] =
+ {
+ "JANUARY",
+ "FEBRUARY",
+ "MARCH",
+ "APRIL",
+ "MAY",
+ "JUNE",
+ "JULY",
+ "AUGUST",
+ "SEPTEMBER",
+ "OCTOBER",
+ "NOVEMBER",
+ "DECEMBER"
+ };
+
+ for (Int32 i = 0; i < 12; i++)
+ {
+ char upVal[10];
+ str_cpy_convert(upVal, value, strlen(months[i]), 1);
+ if (memcmp(upVal, months[i], strlen(months[i])) == 0)
+ {
+ *result = (char)(i + 1);
+ value += strlen(months[i]);
return TRUE;
}
}
@@ -2337,6 +2430,47 @@ convertStrToMonth(char * value, char *result)
return FALSE;
}
+static short convSrcDataToDst(Lng32 numSrcBytes, char* &srcData,
+ Lng32 numTgtBytes, char *dstData,
+ const char * nextByte,
+ CollHeap * heap, ComDiagsArea** diagsArea)
+{
+ Lng32 src = 0;
+ Lng32 val = 0;
+ for (val = 0, src = 0; src < numSrcBytes && isDigit8859_1(*srcData);
+ src++, srcData++)
+ val = val * 10 + (*srcData - '0');
+
+ if (src < numSrcBytes)
+ {
+ // string contains non-digit
+ //
+ ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
+ return -1;
+ }
+
+ if (numTgtBytes == 2)
+ *(short*)dstData = val;
+ else if (numTgtBytes == 1)
+ *(char*)dstData = val;
+ else
+ return -1;
+
+ if (nextByte && (strlen(nextByte) > 0))
+ {
+ if (*srcData != *nextByte)
+ {
+ // string contains non-digit
+ ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
+ return -1;
+ }
+
+ srcData++;
+ }
+
+ return 0;
+}
+
// ExpDatetime::convAsciiToDate() ================================
// This method is used to convert the given ASCII string
// to a datetime date value.
@@ -2356,8 +2490,6 @@ convertStrToMonth(char * value, char *result)
// only. The size of destination buffer should be just enough to hold
// internal representation of the date value, i.e. 4 bytes.
//
-// This method was added as part of the IMPORT performance improvement
-// project.
// =====================================================================
//
short
@@ -2410,773 +2542,363 @@ ExpDatetime::convAsciiToDate(char *srcData,
};
switch (srcFormat) {
- case DATETIME_FORMAT_DEFAULT:
- case DATETIME_FORMAT_DEFAULT2:
- case DATETIME_FORMAT_TS3:
+ case DATETIME_FORMAT_DEFAULT: // YYYY-MM-DD
{
- // this is default format: yyyy-mm-dd. first, the year
- for (year = 0, i = 0; i < 4 && isDigit8859_1(*srcData); i++, srcData++)
-#pragma nowarn(1506) // warning elimination
- year = year * 10 + *srcData - '0';
-#pragma warn(1506) // warning elimination
+ // the year
+ if (convSrcDataToDst(4, srcData, 2, dstData, "-", heap, diagsArea))
+ return -1;
- if (i < 4 || *srcData != '-') {
- // string contains non-digit or invalid delimiter
- //
- if (i < 4)
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
+ // the month
+ if (convSrcDataToDst(2, srcData, 1, &dstData[2], "-", heap, diagsArea))
+ return -1;
+
+ // the day
+ if (convSrcDataToDst(2, srcData, 1, &dstData[3], NULL, heap, diagsArea))
+ return -1;
+ };
+ break;
+
+ case DATETIME_FORMAT_DEFAULT2: // YYYY-MM
+ {
+ // the year
+ if (convSrcDataToDst(4, srcData, 2, dstData, "-", heap, diagsArea))
return -1;
- }
- str_cpy_all(dstData, (char *)&year, sizeof(year));
- srcData++; // move on to the next field
// the month
- if (isDigit8859_1(*srcData) && isDigit8859_1(*(srcData+1))) {
-#pragma nowarn(1506) // warning elimination
- dstData[2] = char (*srcData - '0') * 10 + (*(srcData+1) - '0');
-#pragma warn(1506) // warning elimination
- } else {
- // string contains non-digit charecter(s)
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
+ if (convSrcDataToDst(2, srcData, 1, &dstData[2], NULL, heap, diagsArea))
+ return -1;
+
+ // the day
+ // day is not specified, fill in as '1' (first day of month).
+ dstData[3] = 1;
+ };
+ break;
+
+ case DATETIME_FORMAT_TS3: // YYYY-MM-DD HH24:MI:SS
+ {
+ // the year
+ if (convSrcDataToDst(4, srcData, 2, dstData, "-", heap, diagsArea))
return -1;
- }
- srcData += 2;
+ // the month
+ if (convSrcDataToDst(2, srcData, 1, &dstData[2], "-", heap, diagsArea))
+ return -1;
// the day
- if (srcFormat == DATETIME_FORMAT_DEFAULT2)
- {
- // day is not specified, fill in as '1' (first day of month).
- dstData[3] = 1;
- }
- else
- {
- if (*srcData != '-') {
- // string contains invalid delimiter
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
- srcData++; // move on to the next field
-
- if (isDigit8859_1(*srcData) && isDigit8859_1(*(srcData+1))) {
-#pragma nowarn(1506) // warning elimination
- dstData[3] = char (*srcData - '0') * 10 + (*(srcData+1) - '0');
-#pragma warn(1506) // warning elimination
- } else {
- // string contains non-digit charecter(s)
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
- srcData += 2;
- }
+ if (convSrcDataToDst(2, srcData, 1, &dstData[3], " ", heap, diagsArea))
+ return -1;
+
+ // the hour
+ if (convSrcDataToDst(2, srcData, 1, &dstData[4], ":", heap, diagsArea))
+ return -1;
- if (srcFormat == DATETIME_FORMAT_TS3)
- {
- if (*srcData != ' ') {
- // string contains invalid delimiter
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
- srcData++; // move on to the next field
+ // the minute
+ if (convSrcDataToDst(2, srcData, 1, &dstData[5], ":", heap, diagsArea))
+ return -1;
- // the hour
- if (isDigit8859_1(*srcData) && isDigit8859_1(*(srcData+1))) {
-#pragma nowarn(1506) // warning elimination
- dstData[4] = char (*srcData - '0') * 10 + (*(srcData+1) - '0');
-#pragma warn(1506) // warning elimination
- } else {
- // string contains non-digit charecter(s)
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
-
- srcData += 2;
+ // the second
+ if (convSrcDataToDst(2, srcData, 1, &dstData[6], NULL, heap, diagsArea))
+ return -1;
- if (*srcData != ':') {
- // string contains invalid delimiter
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
- srcData++; // move on to the next field
-
- // the minute
- if (isDigit8859_1(*srcData) && isDigit8859_1(*(srcData+1))) {
-#pragma nowarn(1506) // warning elimination
- dstData[5] = char (*srcData - '0') * 10 + (*(srcData+1) - '0');
-#pragma warn(1506) // warning elimination
- } else {
- // string contains non-digit charecter(s)
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
-
- srcData += 2;
- if (*srcData != ':') {
- // string contains invalid delimiter
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
- srcData++; // move on to the next field
-
- // the second
- if (isDigit8859_1(*srcData) && isDigit8859_1(*(srcData+1))) {
-#pragma nowarn(1506) // warning elimination
- dstData[6] = char (*srcData - '0') * 10 + (*(srcData+1) - '0');
-#pragma warn(1506) // warning elimination
- } else {
- // string contains non-digit charecter(s)
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
-
- srcData += 2;
-
- dstData[7] = 0;
- dstData[8] = 0;
- dstData[9] = 0;
- dstData[10] = 0;
- }
- }; // case DEFAULT format
+ dstData[7] = 0;
+ dstData[8] = 0;
+ dstData[9] = 0;
+ dstData[10] = 0;
+ };
break;
- case DATETIME_FORMAT_USA:
+ case DATETIME_FORMAT_USA: // MM/DD/YYYY AM|PM
case DATETIME_FORMAT_USA2: // MM/DD/YYYY
case DATETIME_FORMAT_USA6: // MM/DD/YY
case DATETIME_FORMAT_USA7: // MM-DD-YYYY
- case DATETIME_FORMAT_TS6: // MMDDYYYY HH24:MI:SS
- case DATETIME_FORMAT_TS7: // MM/DD/YYYY HH24:MI:SS
{
- // this is USA format: mm/dd/yyyy. first, the month
- if (isDigit8859_1(*srcData) && isDigit8859_1(*(srcData+1))) {
-#pragma nowarn(1506) // warning elimination
- dstData[2] = char (*srcData - '0') * 10 + (*(srcData+1) - '0');
-#pragma warn(1506) // warning elimination
- } else {
- // string contains non-digit charecter(s)
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
+ char sep = (srcFormat == DATETIME_FORMAT_USA7 ? '-' : '/');
- char delim = (srcFormat == DATETIME_FORMAT_USA7 ? '-'
- : (srcFormat == DATETIME_FORMAT_TS6 ? 0 : '/'));
- srcData += 2; // move on to the delimiter field
- if (delim != 0)
- {
- if (*srcData != delim) {
- // string contains invalid delimiter
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
- srcData++; // move on to the next field
- }
+ // the month
+ if (convSrcDataToDst(2, srcData, 1, &dstData[2], &sep, heap,diagsArea))
+ return -1;
// the day
- if (isDigit8859_1(*srcData) && isDigit8859_1(*(srcData+1))) {
-#pragma nowarn(1506) // warning elimination
- dstData[3] = char (*srcData - '0') * 10 + (*(srcData+1) - '0');
-#pragma warn(1506) // warning elimination
- } else {
- // string contains non-digit charecter(s)
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
+ if (convSrcDataToDst(2, srcData, 1, &dstData[3], &sep, heap, diagsArea))
return -1;
- }
- srcData += 2; // move on to the delimiter field
+ // the year
+ Int32 numOfYdigits = (srcFormat == DATETIME_FORMAT_USA6 ? 2 : 4);
+ if (convSrcDataToDst(numOfYdigits, srcData, 2, dstData, NULL, heap, diagsArea))
+ return -1;
+ };
+ break;
+
+ case DATETIME_FORMAT_TS6: // MMDDYYYY HH24:MI:SS
+ case DATETIME_FORMAT_TS7: // MM/DD/YYYY HH24:MI:SS
+ {
+ char sep = '/';
+ char * septr = (srcFormat == DATETIME_FORMAT_TS7 ? &sep : NULL);
- if (delim != 0)
- {
- if (*srcData != delim) {
- // string contains invalid delimiter
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
- srcData++; // move on to the next field
- }
+ // the month
+ if (convSrcDataToDst(2, srcData, 1, &dstData[2], septr, heap, diagsArea))
+ return -1;
+ // the day
+ if (convSrcDataToDst(2, srcData, 1, &dstData[3], septr, heap, diagsArea))
+ return -1;
+
// the year
- Int32 numOfYdigits = (srcFormat == DATETIME_FORMAT_USA6 ? 2 : 4);
- for (year = 0, i = 0; i < numOfYdigits && isDigit8859_1(*srcData); i++, srcData++)
-#pragma nowarn(1506) // warning elimination
- year = year * 10 + *srcData - '0';
-#pragma warn(1506) // warning elimination
+ if (convSrcDataToDst(4, srcData, 2, dstData, " ", heap, diagsArea))
+ return -1;
- if (i < numOfYdigits) {
- // string contains non-digit
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
+ // the hour
+ if (convSrcDataToDst(2, srcData, 1, &dstData[4], ":", heap, diagsArea))
return -1;
- }
- str_cpy_all(dstData, (char *)&year, sizeof(year));
- if ((srcFormat == DATETIME_FORMAT_TS6) ||
- (srcFormat == DATETIME_FORMAT_TS7))
- {
- if (*srcData != ' ') {
- // string contains invalid delimiter
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
- srcData++; // move on to the next field
+ // the minute
+ if (convSrcDataToDst(2, srcData, 1, &dstData[5], ":", heap, diagsArea))
+ return -1;
- // the hour
- if (isDigit8859_1(*srcData) && isDigit8859_1(*(srcData+1))) {
-#pragma nowarn(1506) // warning elimination
- dstData[4] = char (*srcData - '0') * 10 + (*(srcData+1) - '0');
-#pragma warn(1506) // warning elimination
- } else {
- // string contains non-digit charecter(s)
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
-
- srcData += 2;
+ // the second
+ if (convSrcDataToDst(2, srcData, 1, &dstData[6], NULL, heap, diagsArea))
+ return -1;
- if (*srcData != ':') {
- // string contains invalid delimiter
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
- srcData++; // move on to the next field
-
- // the minute
- if (isDigit8859_1(*srcData) && isDigit8859_1(*(srcData+1))) {
-#pragma nowarn(1506) // warning elimination
- dstData[5] = char (*srcData - '0') * 10 + (*(srcData+1) - '0');
-#pragma warn(1506) // warning elimination
- } else {
- // string contains non-digit charecter(s)
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
-
- srcData += 2;
- if (*srcData != ':') {
- // string contains invalid delimiter
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
- srcData++; // move on to the next field
-
- // the second
- if (isDigit8859_1(*srcData) && isDigit8859_1(*(srcData+1))) {
-#pragma nowarn(1506) // warning elimination
- dstData[6] = char (*srcData - '0') * 10 + (*(srcData+1) - '0');
-#pragma warn(1506) // warning elimination
- } else {
- // string contains non-digit charecter(s)
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
-
- srcData += 2;
-
- dstData[7] = 0;
- dstData[8] = 0;
- dstData[9] = 0;
- dstData[10] = 0;
- }
- }; // case USA format
+ dstData[7] = 0;
+ dstData[8] = 0;
+ dstData[9] = 0;
+ dstData[10] = 0;
+ };
break;
- case DATETIME_FORMAT_USA3:
- case DATETIME_FORMAT_USA4:
- case DATETIME_FORMAT_USA5:
- case DATETIME_FORMAT_USA8:
+ case DATETIME_FORMAT_USA3: // YYYY/MM/DD
+ case DATETIME_FORMAT_USA4: // YYYYMMDD
+ case DATETIME_FORMAT_USA5: // YY/MM/DD
{
- // this is USA format: See exp_datetime.h for details.
+ // the year
+ Lng32 numYearDigits = (srcFormat == DATETIME_FORMAT_USA5 ? 2 : 4);
+ char sep = '/';
+ char * septr = (srcFormat == DATETIME_FORMAT_USA4 ? NULL : &sep);
// the year
- Lng32 numYearDigits = 4;
- if (srcFormat == DATETIME_FORMAT_USA5)
- numYearDigits = 2;
- for (year = 0, i = 0;
- i < numYearDigits && isDigit8859_1(*srcData); i++, srcData++)
-#pragma nowarn(1506) // warning elimination
- year = year * 10 + *srcData - '0';
-#pragma warn(1506) // warning elimination
+ if (convSrcDataToDst(numYearDigits, srcData, 2, dstData, septr, heap, diagsArea))
+ return -1;
- if (i < numYearDigits) {
- // string contains non-digit
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
+ // the month
+ if (convSrcDataToDst(2, srcData, 1, &dstData[2], septr, heap, diagsArea))
return -1;
- }
- str_cpy_all(dstData, (char *)&year, sizeof(year));
+ // the day
+ if (convSrcDataToDst(2, srcData, 1, &dstData[3], NULL, heap, diagsArea))
+ return -1;
- if ((srcFormat != DATETIME_FORMAT_USA4) &&
- (srcFormat != DATETIME_FORMAT_USA8)) {
- if (*srcData != '/') {
- // string contains invalid delimiter
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
- srcData++; // move on to the next field
- }
+ };
+ break;
- if (isDigit8859_1(*srcData) && isDigit8859_1(*(srcData+1))) {
-#pragma nowarn(1506) // warning elimination
- dstData[2] = char (*srcData - '0') * 10 + (*(srcData+1) - '0');
-#pragma warn(1506) // warning elimination
- } else {
- // string contains non-digit charecter(s)
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
+ case DATETIME_FORMAT_USA8: // YYYYMM
+ {
+ // the year
+ if (convSrcDataToDst(4, srcData, 2, dstData, NULL, heap, diagsArea))
return -1;
- }
- srcData += 2; // move on to the delimiter field
- if ((srcFormat != DATETIME_FORMAT_USA4) &&
- (srcFormat != DATETIME_FORMAT_USA8)) {
- if (*srcData != '/') {
- // string contains invalid delimiter
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
- srcData++; // move on to the next field
- }
+ // the month
+ if (convSrcDataToDst(2, srcData, 1, &dstData[2], NULL, heap, diagsArea))
+ return -1;
// the day
- if (srcFormat == DATETIME_FORMAT_USA8)
- {
- // day is not specified, fill in as '1' (first day of month).
- dstData[3] = 1;
- }
- else
- {
- if (isDigit8859_1(*srcData) && isDigit8859_1(*(srcData+1))) {
-#pragma nowarn(1506) // warning elimination
- dstData[3] = char (*srcData - '0') * 10 + (*(srcData+1) - '0');
-#pragma warn(1506) // warning elimination
- } else {
- // string contains non-digit charecter(s)
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
-
- srcData += 2;
- }
- }; // case USA format
+ // day is not specified, fill in as '1' (first day of month).
+ dstData[3] = 1;
+ };
break;
- case DATETIME_FORMAT_TS1:
- case DATETIME_FORMAT_TS5:
+ case DATETIME_FORMAT_TS1: // YYYYMMDDHH24MISS
+ case DATETIME_FORMAT_TS5: // YYYYMMDD:HH24:MI:SS
{
- // this is TS1 format: YYYYMMDDHH24MISS
- // TS5 format: YYYYMMDD:HH24:MI:SS
- // See exp_datetime.h for details.
-
+ char sep = ':';
+ char * septr = (srcFormat == DATETIME_FORMAT_TS1 ? NULL : &sep);
+
// the year
- Lng32 numYearDigits = 4;
- for (year = 0, i = 0;
- i < numYearDigits && isDigit8859_1(*srcData); i++, srcData++)
-#pragma nowarn(1506) // warning elimination
- year = year * 10 + *srcData - '0';
-#pragma warn(1506) // warning elimination
-
- if (i < numYearDigits) {
- // string contains non-digit
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
+ if (convSrcDataToDst(4, srcData, 2, dstData, NULL, heap, diagsArea))
return -1;
- }
-
- str_cpy_all(dstData, (char *)&year, sizeof(year));
- if (isDigit8859_1(*srcData) && isDigit8859_1(*(srcData+1))) {
-#pragma nowarn(1506) // warning elimination
- dstData[2] = char (*srcData - '0') * 10 + (*(srcData+1) - '0');
-#pragma warn(1506) // warning elimination
- } else {
- // string contains non-digit charecter(s)
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
+ // the month
+ if (convSrcDataToDst(2, srcData, 1, &dstData[2], NULL, heap, diagsArea))
return -1;
- }
-
- srcData += 2; // move on to the delimiter field
// the day
- if (isDigit8859_1(*srcData) && isDigit8859_1(*(srcData+1))) {
-#pragma nowarn(1506) // warning elimination
- dstData[3] = char (*srcData - '0') * 10 + (*(srcData+1) - '0');
-#pragma warn(1506) // warning elimination
- } else {
- // string contains non-digit charecter(s)
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
+ if (convSrcDataToDst(2, srcData, 1, &dstData[3], septr, heap, diagsArea))
return -1;
- }
-
- srcData += 2;
-
- if (srcFormat == DATETIME_FORMAT_TS5)
- {
- if (*srcData != ':') {
- // string contains invalid delimiter
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
- srcData++; // move on to the next field
- }
-
+
// the hour
- if (isDigit8859_1(*srcData) && isDigit8859_1(*(srcData+1))) {
-#pragma nowarn(1506) // warning elimination
- dstData[4] = char (*srcData - '0') * 10 + (*(srcData+1) - '0');
-#pragma warn(1506) // warning elimination
- } else {
- // string contains non-digit charecter(s)
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
+ if (convSrcDataToDst(2, srcData, 1, &dstData[4], septr, heap, diagsArea))
return -1;
- }
-
- srcData += 2;
-
- if (srcFormat == DATETIME_FORMAT_TS5)
- {
- if (*srcData != ':') {
- // string contains invalid delimiter
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
- srcData++; // move on to the next field
- }
// the minute
- if (isDigit8859_1(*srcData) && isDigit8859_1(*(srcData+1))) {
-#pragma nowarn(1506) // warning elimination
- dstData[5] = char (*srcData - '0') * 10 + (*(srcData+1) - '0');
-#pragma warn(1506) // warning elimination
- } else {
- // string contains non-digit charecter(s)
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
+ if (convSrcDataToDst(2, srcData, 1, &dstData[5], septr, heap, diagsArea))
return -1;
- }
- srcData += 2;
+ // the second
+ if (convSrcDataToDst(2, srcData, 1, &dstData[6], NULL, heap, diagsArea))
+ return -1;
- if (srcFormat == DATETIME_FORMAT_TS5)
- {
- if (*srcData != ':') {
- // string contains invalid delimiter
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
- srcData++; // move on to the next field
- }
+ dstData[7] = 0;
+ dstData[8] = 0;
+ dstData[9] = 0;
+ dstData[10] = 0;
+ };
+ break;
- // the second
- if (isDigit8859_1(*srcData) && isDigit8859_1(*(srcData+1))) {
-#pragma nowarn(1506) // warning elimination
- dstData[6] = char (*srcData - '0') * 10 + (*(srcData+1) - '0');
-#pragma warn(1506) // warning elimination
- } else {
- // string contains non-digit charecter(s)
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
+ case DATETIME_FORMAT_TS9: // MONTH DD, YYYY, HH:MI AM|PM
+ {
+ // the month
+ char * prevSrcData = srcData;
+ if (! convertStrToMonthLongFormat(srcData, &dstData[2])) {
ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
return -1;
}
+ minLength += (srcData - prevSrcData);
+ srcData += 1; // skip blank after "Month"
- srcData += 2;
+ // the day
+ if (convSrcDataToDst(2, srcData, 1, &dstData[3], ",", heap, diagsArea))
+ return -1;
+ srcData++; // skip over blank
+ // the year
+ if (convSrcDataToDst(4, srcData, 2, dstData, ",", heap, diagsArea))
+ return -1;
+ srcData++; // skip over blank
+
+ // the hour
+ if (convSrcDataToDst(2, srcData, 1, &dstData[4], ":", heap, diagsArea))
+ return -1;
+
+ // the minute
+ if (convSrcDataToDst(2, srcData, 1, &dstData[5], NULL, heap, diagsArea))
+ return -1;
+
+ dstData[6] = 0;
dstData[7] = 0;
dstData[8] = 0;
dstData[9] = 0;
dstData[10] = 0;
- }; // case TS1 format
+ }
break;
- case DATETIME_FORMAT_EUROPEAN:
- case DATETIME_FORMAT_EUROPEAN2:
- case DATETIME_FORMAT_EUROPEAN3:
- case DATETIME_FORMAT_EUROPEAN4:
- case DATETIME_FORMAT_TS2:
+ case DATETIME_FORMAT_EUROPEAN: // DD.MM.YYYY
+ case DATETIME_FORMAT_EUROPEAN2: // DD-MM-YYYY
{
- // this is European format: dd.mm.yyyy. first, the day
- if (isDigit8859_1(*srcData) && isDigit8859_1(*(srcData+1))) {
-#pragma nowarn(1506) // warning elimination
- dstData[3] = char (*srcData - '0') * 10 + (*(srcData+1) - '0');
-#pragma warn(1506) // warning elimination
- } else {
- // string contains non-digit charecter(s)
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
+ char sep = (srcFormat == DATETIME_FORMAT_EUROPEAN ? '.' : '-');
- srcData += 2; // move on to the delimiter field
- if ((((srcFormat == DATETIME_FORMAT_EUROPEAN) ||
- (srcFormat == DATETIME_FORMAT_TS2)) &&
- (*srcData != '.')) ||
- (((srcFormat == DATETIME_FORMAT_EUROPEAN2) ||
- (srcFormat == DATETIME_FORMAT_EUROPEAN3)) &&
- (*srcData != '-'))) {
- // string contains invalid delimiter
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
+ // the day
+ if (convSrcDataToDst(2, srcData, 1, &dstData[3], &sep, heap, diagsArea))
return -1;
- }
-
- if (srcFormat != DATETIME_FORMAT_EUROPEAN4)
- srcData++; // move on to the next field
+
+ // the month
+ if (convSrcDataToDst(2, srcData, 1, &dstData[2], &sep, heap, diagsArea))
+ return -1;
+
+ // the year
+ if (convSrcDataToDst(4, srcData, 2, dstData, NULL, heap, diagsArea))
+ return -1;
+ };
+ break;
+ case DATETIME_FORMAT_EUROPEAN3: // DD-MON-YYYY
+ case DATETIME_FORMAT_EUROPEAN4: // DDMONYYYY
+ {
+ char sep = '-';
+ char * septr = (srcFormat == DATETIME_FORMAT_EUROPEAN3 ? &sep : NULL);
+
+ // the day
+ if (convSrcDataToDst(2, srcData, 1, &dstData[3], septr, heap, diagsArea))
+ return -1;
+
// the month
- if ((srcFormat == DATETIME_FORMAT_EUROPEAN3) ||
- (srcFormat == DATETIME_FORMAT_EUROPEAN4))
- {
- if (! convertStrToMonth(srcData, &dstData[2])) {
- // string contains non-digit charecter(s)
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
- srcData += 3; // move on to the delimiter field
- }
- else
- {
- if (isDigit8859_1(*srcData) && isDigit8859_1(*(srcData+1))) {
-#pragma nowarn(1506) // warning elimination
- dstData[2] = char (*srcData - '0') * 10 + (*(srcData+1) - '0');
-#pragma warn(1506) // warning elimination
- } else {
- // string contains non-digit charecter(s)
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
- srcData += 2; // move on to the delimiter field
- }
+ if (! convertStrToMonth(srcData, &dstData[2], septr, heap, diagsArea))
+ return -1;
- if ((((srcFormat == DATETIME_FORMAT_EUROPEAN) ||
- (srcFormat == DATETIME_FORMAT_TS2)) &&
- (*srcData != '.')) ||
- (((srcFormat == DATETIME_FORMAT_EUROPEAN2) ||
- (srcFormat == DATETIME_FORMAT_EUROPEAN3)) &&
- (*srcData != '-'))) {
- // string contains invalid delimiter
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
+ // the year
+ if (convSrcDataToDst(4, srcData, 2, dstData, NULL, heap, diagsArea))
return -1;
- }
-
- if (srcFormat != DATETIME_FORMAT_EUROPEAN4)
- srcData++; // move on to the next field
+ };
+ break;
+ case DATETIME_FORMAT_TS2: // DD.MM.YYYY:HH24:MI:SS
+ {
+ // the day
+ if (convSrcDataToDst(2, srcData, 1, &dstData[3], ".", heap, diagsArea))
+ return -1;
+
+ // the month
+ if (convSrcDataToDst(2, srcData, 1, &dstData[2], ".", heap, diagsArea))
+ return -1;
+
// the year
- for (year = 0, i = 0; i < 4 && isDigit8859_1(*srcData); i++, srcData++)
-#pragma nowarn(1506) // warning elimination
- year = year * 10 + *srcData - '0';
-#pragma warn(1506) // warning elimination
+ if (convSrcDataToDst(4, srcData, 2, dstData, ":", heap, diagsArea))
+ return -1;
- if (i < 4) {
- // string contains non-digit
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
+ // the hour
+ if (convSrcDataToDst(2, srcData, 1, &dstData[4], ":", heap, diagsArea))
return -1;
- }
- str_cpy_all(dstData, (char *)&year, sizeof(year));
+ // the minute
+ if (convSrcDataToDst(2, srcData, 1, &dstData[5], ":", heap, diagsArea))
+ return -1;
- if (srcFormat == DATETIME_FORMAT_TS2)
- {
- if (*srcData != ':') {
- // string contains invalid delimiter
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
- srcData++; // move on to the next field
+ // the second
+ if (convSrcDataToDst(2, srcData, 1, &dstData[6], NULL, heap, diagsArea))
+ return -1;
- // the hour
- if (isDigit8859_1(*srcData) && isDigit8859_1(*(srcData+1))) {
-#pragma nowarn(1506) // warning elimination
- dstData[4] = char (*srcData - '0') * 10 + (*(srcData+1) - '0');
-#pragma warn(1506) // warning elimination
- } else {
- // string contains non-digit charecter(s)
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
-
- srcData += 2;
+ dstData[7] = 0;
+ dstData[8] = 0;
+ dstData[9] = 0;
+ dstData[10] = 0;
+ };
+ break;
- if (*srcData != ':') {
- // string contains invalid delimiter
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
- srcData++; // move on to the next field
-
- // the minute
- if (isDigit8859_1(*srcData) && isDigit8859_1(*(srcData+1))) {
-#pragma nowarn(1506) // warning elimination
- dstData[5] = char (*srcData - '0') * 10 + (*(srcData+1) - '0');
-#pragma warn(1506) // warning elimination
- } else {
- // string contains non-digit charecter(s)
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
-
- srcData += 2;
- if (*srcData != ':') {
- // string contains invalid delimiter
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
- srcData++; // move on to the next field
-
- // the second
- if (isDigit8859_1(*srcData) && isDigit8859_1(*(srcData+1))) {
-#pragma nowarn(1506) // warning elimination
- dstData[6] = char (*srcData - '0') * 10 + (*(srcData+1) - '0');
-#pragma warn(1506) // warning elimination
- } else {
- // string contains non-digit charecter(s)
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
-
- srcData += 2;
-
- dstData[7] = 0;
- dstData[8] = 0;
- dstData[9] = 0;
- dstData[10] = 0;
- }
+ case DATETIME_FORMAT_TS8: // DD-MON-YYYY HH:MI:SS
+ {
+ // the day
+ if (convSrcDataToDst(2, srcData, 1, &dstData[3], "-", heap, diagsArea))
+ return -1;
- }; // case EUROPEAN format
+ // the month
+ if (! convertStrToMonth(srcData, &dstData[2], "-", heap, diagsArea))
+ return -1;
+
+ // the year
+ if (convSrcDataToDst(4, srcData, 2, dstData, " ", heap, diagsArea))
+ return -1;
+
+ // the hour
+ if (convSrcDataToDst(2, srcData, 1, &dstData[4], ":", heap, diagsArea))
+ return -1;
+
+ // the minute
+ if (convSrcDataToDst(2, srcData, 1, &dstData[5], ":", heap, diagsArea))
+ return -1;
+
+ // the second
+ if (convSrcDataToDst(2, srcData, 1, &dstData[6], NULL, heap, diagsArea))
+ return -1;
+
+ dstData[7] = 0;
+ dstData[8] = 0;
+ dstData[9] = 0;
+ dstData[10] = 0;
+ };
break;
- case DATETIME_FORMAT_TS4:
+ case DATETIME_FORMAT_TS4: // HH24:MI:SS
{
// the hour
- if (isDigit8859_1(*srcData) && isDigit8859_1(*(srcData+1))) {
-#pragma nowarn(1506) // warning elimination
- dstData[0] = char (*srcData - '0') * 10 + (*(srcData+1) - '0');
-#pragma warn(1506) // warning elimination
- } else {
- // string contains non-digit charecter(s)
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
-
- srcData += 2;
-
- if (*srcData != ':') {
- // string contains invalid delimiter
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
- srcData++; // move on to the next field
-
+ if (convSrcDataToDst(2, srcData, 1, &dstData[0], ":", heap, diagsArea))
+ return -1;
+
// the minute
- if (isDigit8859_1(*srcData) && isDigit8859_1(*(srcData+1))) {
-#pragma nowarn(1506) // warning elimination
- dstData[1] = char (*srcData - '0') * 10 + (*(srcData+1) - '0');
-#pragma warn(1506) // warning elimination
- } else {
- // string contains non-digit charecter(s)
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
-
- srcData += 2;
- if (*srcData != ':') {
- // string contains invalid delimiter
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
- srcData++; // move on to the next field
-
+ if (convSrcDataToDst(2, srcData, 1, &dstData[1], ":", heap, diagsArea))
+ return -1;
+
// the second
- if (isDigit8859_1(*srcData) && isDigit8859_1(*(srcData+1))) {
-#pragma nowarn(1506) // warning elimination
- dstData[2] = char (*srcData - '0') * 10 + (*(srcData+1) - '0');
-#pragma warn(1506) // warning elimination
- } else {
- // string contains non-digit charecter(s)
- //
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_STRING_ERROR);
- ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
- return -1;
- }
-
- srcData += 2;
-
- dstData[3] = 0;
- dstData[4] = 0;
- dstData[5] = 0;
- dstData[6] = 0;
-
+ if (convSrcDataToDst(2, srcData, 1, &dstData[2], NULL, heap, diagsArea))
+ return -1;
};
break;
@@ -3293,6 +3015,32 @@ convertMonthToStr(Lng32 value, char *&result, UInt32 width)
result += width;
}
+static void
+convertMonthToStrLongFormat(Lng32 value, char *&result, UInt32 width)
+{
+ const char * months[] =
+ {
+ "January",
+ "February",
+ "March",
+ "April",
+ "May",
+ "June",
+ "July",
+ "August",
+ "September",
+ "October",
+ "November",
+ "December"
+ };
+
+ strcpy(result, months[value-1]);
+
+ // Update result pointer to point to end of string.
+ //
+ result += strlen(months[value-1]);
+}
+
Lng32 ExpDatetime::getDatetimeFormatLen(Lng32 format, NABoolean to_date,
rec_datetime_field startField,
rec_datetime_field endField)
@@ -3305,13 +3053,8 @@ Lng32 ExpDatetime::getDatetimeFormatLen(Lng32 format, NABoolean to_date,
{
if (to_date)
{
- if (format == DATETIME_FORMAT_DEFAULT)
- return DATETIME_FORMAT_DEFAULT_LEN;
- else if (format == DATETIME_FORMAT_USA)
- return DATETIME_FORMAT_USA_LEN;
- else //if (format == DATETIME_FORMAT_EUROPEAN)
- return DATETIME_FORMAT_EUROPEAN_LEN;
- }
+ return ExpDatetime::getAsciiFormatLen(format);
+ }
else
{
Lng32 minReqDstLen = 0;
@@ -3348,34 +3091,12 @@ Lng32 ExpDatetime::getDatetimeFormatLen(Lng32 format, NABoolean to_date,
}
}
break;
- case DATETIME_FORMAT_DEFAULT2: return DATETIME_FORMAT_DEFAULT2_LEN;
-
- case DATETIME_FORMAT_USA2: return DATETIME_FORMAT_USA2_LEN;
- case DATETIME_FORMAT_USA3: return DATETIME_FORMAT_USA3_LEN;
- case DATETIME_FORMAT_USA4: return DATETIME_FORMAT_USA4_LEN;
- case DATETIME_FORMAT_USA5: return DATETIME_FORMAT_USA5_LEN;
- case DATETIME_FORMAT_USA6: return DATETIME_FORMAT_USA6_LEN;
- case DATETIME_FORMAT_USA7: return DATETIME_FORMAT_USA7_LEN;
- case DATETIME_FORMAT_USA8: return DATETIME_FORMAT_USA8_LEN;
-
- case DATETIME_FORMAT_EUROPEAN2: return DATETIME_FORMAT_EUROPEAN2_LEN;
- case DATETIME_FORMAT_EUROPEAN3: return DATETIME_FORMAT_EUROPEAN3_LEN;
- case DATETIME_FORMAT_EUROPEAN4: return DATETIME_FORMAT_EUROPEAN4_LEN;
-
- case DATETIME_FORMAT_TIME1: return DATETIME_FORMAT_TIME1_LEN;
- case DATETIME_FORMAT_TIME2: return DATETIME_FORMAT_TIME2_LEN;
-
- case DATETIME_FORMAT_TS1: return DATETIME_FORMAT_TS1_LEN;
- case DATETIME_FORMAT_TS2: return DATETIME_FORMAT_TS2_LEN;
- case DATETIME_FORMAT_TS3: return DATETIME_FORMAT_TS3_LEN;
- case DATETIME_FORMAT_TS4: return DATETIME_FORMAT_TS4_LEN;
- case DATETIME_FORMAT_TS5: return DATETIME_FORMAT_TS5_LEN;
- case DATETIME_FORMAT_TS6: return DATETIME_FORMAT_TS6_LEN;
- case DATETIME_FORMAT_TS7: return DATETIME_FORMAT_TS7_LEN;
- case DATETIME_FORMAT_TS8: return DATETIME_FORMAT_TS8_LEN;
-
- default: return -1;
+
+ default:
+ return ExpDatetime::getAsciiFormatLen(format);
}
+
+ return 0;
}
// convDatetimeToASCII() ============================================
@@ -3572,6 +3293,20 @@ ExpDatetime::convDatetimeToASCII(char *srcData,
}
break;
+ case DATETIME_FORMAT_TS9:
+ {
+ convertMonthToStrLongFormat(month, dstDataPtr, 3);
+ *dstDataPtr++ = ' ';
+
+ convertToAscii(day, dstDataPtr, 2);
+ *dstDataPtr++ = ',';
+ *dstDataPtr++ = ' ';
+
+ convertToAscii(year, dstDataPtr, 4);
+ *dstDataPtr++ = ',';
+ }
+ break;
+
case DATETIME_FORMAT_TS4:
{
// do nothing for date part.
@@ -3707,6 +3442,36 @@ ExpDatetime::convDatetimeToASCII(char *srcData,
dstDataPtr += 3;
}
+ // if format includes time field but source is a DATE datatype, extend
+ // the returned string with zeroes
+ if ((format == DATETIME_FORMAT_TS1) ||
+ (format == DATETIME_FORMAT_TS2) ||
+ (format == DATETIME_FORMAT_TS3) ||
+ (format == DATETIME_FORMAT_TS5) ||
+ (format == DATETIME_FORMAT_TS6) ||
+ (format == DATETIME_FORMAT_TS7) ||
+ (format == DATETIME_FORMAT_TS8) ||
+ (format == DATETIME_FORMAT_TS9))
+ {
+ if (format == DATETIME_FORMAT_TS1)
+ {
+ }
+ else if ((format == DATETIME_FORMAT_TS2) ||
+ (format == DATETIME_FORMAT_TS5))
+ {
+ *dstDataPtr = ':';
+ dstDataPtr++;
+ }
+ else
+ {
+ *dstDataPtr = ' ';
+ dstDataPtr++;
+ }
+
+ str_cpy_all(dstDataPtr, "00:00:00", 8);
+ dstDataPtr += 8;
+ }
+
// Return the actual number of bytes formatted.
//
return dstDataPtr - dstData;
@@ -3745,15 +3510,15 @@ ExpDatetime::convNumericTimeToASCII(char *srcData,
CollHeap *heap,
ComDiagsArea** diagsArea)
{
- if ((format != DATETIME_FORMAT_TIME1) &&
- (format != DATETIME_FORMAT_TIME2))
+ if ((format != DATETIME_FORMAT_NUM1) &&
+ (format != DATETIME_FORMAT_NUM2))
return -1;
- if ((format == DATETIME_FORMAT_TIME1) &&
+ if ((format == DATETIME_FORMAT_NUM1) &&
(dstLen < 11))
return -1;
- if ((format == DATETIME_FORMAT_TIME2) &&
+ if ((format == DATETIME_FORMAT_NUM2) &&
(dstLen < 12))
return -1;
@@ -3761,9 +3526,16 @@ ExpDatetime::convNumericTimeToASCII(char *srcData,
NABoolean negative = FALSE;
if (temp < 0)
{
+ // cannot convert negative number with NUM1 format
+ if (format == DATETIME_FORMAT_NUM1)
+ {
+ ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
+ return -1;
+ }
+
temp = -temp;
- if (format == DATETIME_FORMAT_TIME2)
+ if (format == DATETIME_FORMAT_NUM2)
{
negative = TRUE;
}
@@ -3792,7 +3564,13 @@ ExpDatetime::convNumericTimeToASCII(char *srcData,
part1 = (Lng32)(temp - (temp/100)*100);
temp = temp/100;
- if (format == DATETIME_FORMAT_TIME2)
+ if (temp > 0)
+ {
+ ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
+ return -1;
+ }
+
+ if (format == DATETIME_FORMAT_NUM2)
{
if (negative)
str_sprintf(dstData, "-%02d:%02d:%02d:%02d", part1, part2, part3, part4);
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/7526b83d/core/sql/exp/exp_datetime.h
----------------------------------------------------------------------
diff --git a/core/sql/exp/exp_datetime.h b/core/sql/exp/exp_datetime.h
index 28ec909..b6dd799 100644
--- a/core/sql/exp/exp_datetime.h
+++ b/core/sql/exp/exp_datetime.h
@@ -51,65 +51,61 @@ class SQLEXP_LIB_FUNC ExpDatetime : public SimpleType {
public:
enum asciiFormats
{
- DATETIME_FORMAT_DEFAULT = 0, // YYYY-MM-DD
- DATETIME_FORMAT_USA = 1, // MM/DD/YYYY AM|PM
- DATETIME_FORMAT_EUROPEAN = 2, // DD.MM.YYYY
- DATETIME_FORMAT_DEFAULT2, // YYYY-MM
- DATETIME_FORMAT_USA2, // MM/DD/YYYY
- DATETIME_FORMAT_USA3, // YYYY/MM/DD
- DATETIME_FORMAT_USA4, // YYYYMMDD
- DATETIME_FORMAT_USA5, // YY/MM/DD
- DATETIME_FORMAT_USA6, // MM/DD/YY
- DATETIME_FORMAT_USA7, // MM-DD-YYYY
- DATETIME_FORMAT_USA8, // YYYYMM
- DATETIME_FORMAT_EUROPEAN2, // DD-MM-YYYY
- DATETIME_FORMAT_EUROPEAN3, // DD-MON-YYYY
- DATETIME_FORMAT_EUROPEAN4, // DDMONYYYY
- DATETIME_FORMAT_TIME1, // 99:99:99:99
- DATETIME_FORMAT_TIME2, // -99:99:99:99
- DATETIME_FORMAT_TS1, // YYYYMMDDHH24MISS
- DATETIME_FORMAT_TS2, // DD.MM.YYYY:HH24:MI:SS
- DATETIME_FORMAT_TS3, // YYYY-MM-DD HH24:MI:SS
- DATETIME_FORMAT_TS4, // HH24:MI:SS
- DATETIME_FORMAT_TS5, // YYYYMMDD:HH24:MI:SS
- DATETIME_FORMAT_TS6, // MMDDYYYY HH24:MI:SS
- DATETIME_FORMAT_TS7, // MM/DD/YYYY HH24:MI:SS
- DATETIME_FORMAT_TS8, // DD-MON-YYYY HH:MI:SS
- DATETIME_FORMAT_DATE_STR, // format in str
- DATETIME_FORMAT_TIME_STR, // format in str
+ DATETIME_FORMAT_MIN = 0,
+ DATETIME_FORMAT_MIN_DATE = DATETIME_FORMAT_MIN,
+ DATETIME_FORMAT_DEFAULT = DATETIME_FORMAT_MIN_DATE, // YYYY-MM-DD
+ DATETIME_FORMAT_USA, // MM/DD/YYYY AM|PM
+ DATETIME_FORMAT_EUROPEAN, // DD.MM.YYYY
+ DATETIME_FORMAT_DEFAULT2, // YYYY-MM
+ DATETIME_FORMAT_USA2, // MM/DD/YYYY
+ DATETIME_FORMAT_USA3, // YYYY/MM/DD
+ DATETIME_FORMAT_USA4, // YYYYMMDD
+ DATETIME_FORMAT_USA5, // YY/MM/DD
+ DATETIME_FORMAT_USA6, // MM/DD/YY
+ DATETIME_FORMAT_USA7, // MM-DD-YYYY
+ DATETIME_FORMAT_USA8, // YYYYMM
+ DATETIME_FORMAT_EUROPEAN2, // DD-MM-YYYY
+ DATETIME_FORMAT_EUROPEAN3, // DD-MON-YYYY
+ DATETIME_FORMAT_EUROPEAN4, // DDMONYYYY
+ DATETIME_FORMAT_MAX_DATE = DATETIME_FORMAT_EUROPEAN4,
+
+ DATETIME_FORMAT_MIN_TIME,
+ DATETIME_FORMAT_TS4 = DATETIME_FORMAT_MIN_TIME, // HH24:MI:SS
+ DATETIME_FORMAT_MAX_TIME = DATETIME_FORMAT_TS4,
+
+ DATETIME_FORMAT_MIN_TS,
+ DATETIME_FORMAT_TS1 = DATETIME_FORMAT_MIN_TS, // YYYYMMDDHH24MISS
+ DATETIME_FORMAT_TS2, // DD.MM.YYYY:HH24:MI:SS
+ DATETIME_FORMAT_TS3, // YYYY-MM-DD HH24:MI:SS
+ DATETIME_FORMAT_TS5, // YYYYMMDD:HH24:MI:SS
+ DATETIME_FORMAT_TS6, // MMDDYYYY HH24:MI:SS
+ DATETIME_FORMAT_TS7, // MM/DD/YYYY HH24:MI:SS
+ DATETIME_FORMAT_TS8, // DD-MON-YYYY HH:MI:SS
+ DATETIME_FORMAT_TS9, // MONTH DD, YYYY, HH:MI AM|PM
+ DATETIME_FORMAT_MAX_TS = DATETIME_FORMAT_TS9,
+
+ DATETIME_FORMAT_MAX = DATETIME_FORMAT_MAX_TS,
+
+ DATETIME_FORMAT_MIN_NUM = DATETIME_FORMAT_MAX,
+ DATETIME_FORMAT_NUM1, // 99:99:99:99
+ DATETIME_FORMAT_NUM2, // -99:99:99:99
+ DATETIME_FORMAT_MAX_NUM = DATETIME_FORMAT_NUM2,
+
+ DATETIME_FORMAT_DATE_STR, // format in str
+ DATETIME_FORMAT_TIME_STR, // format in str
DATETIME_FORMAT_NONE,
- DATETIME_FORMAT_ERROR
+ DATETIME_FORMAT_ERROR = -1
};
- enum asciiFormtLen {
- DATETIME_FORMAT_DEFAULT_LEN = 10, // YYYY-MM-DD
- DATETIME_FORMAT_USA_LEN = 10, // MM/DD/YYYY AM|PM
- DATETIME_FORMAT_EUROPEAN_LEN = 10, // DD.MM.YYYY
- DATETIME_FORMAT_DEFAULT2_LEN = 7, // YYYY-MM
- DATETIME_FORMAT_USA2_LEN = 10, // MM/DD/YYYY
- DATETIME_FORMAT_USA3_LEN = 10, // YYYY/MM/DD
- DATETIME_FORMAT_USA4_LEN = 8, // YYYYMMDD
- DATETIME_FORMAT_USA5_LEN = 8, // YY/MM/DD
- DATETIME_FORMAT_USA6_LEN = 8, // MM/DD/YY
- DATETIME_FORMAT_USA7_LEN = 10, // MM-DD-YYYY
- DATETIME_FORMAT_USA8_LEN = 6, // YYYYMM
- DATETIME_FORMAT_EUROPEAN2_LEN = 10, // DD-MM-YYYY
- DATETIME_FORMAT_EUROPEAN3_LEN = 11, // DD-MON-YYYY
- DATETIME_FORMAT_EUROPEAN4_LEN = 9, // DDMONYYYY
- DATETIME_FORMAT_TIME1_LEN = 11, // 99:99:99:99
- DATETIME_FORMAT_TIME2_LEN = 12, // -99:99:99:99
- DATETIME_FORMAT_TS1_LEN = 14, // YYYYMMDDHH24MISS
- DATETIME_FORMAT_TS2_LEN = 19, // DD.MM.YYYY:HH24:MI:SS
- DATETIME_FORMAT_TS3_LEN = 19, // YYYY-MM-DD HH24:MI:SS
- DATETIME_FORMAT_TS4_LEN = 8, // HH24:MI:SS
- DATETIME_FORMAT_TS5_LEN = 17, // YYYYMMDD:HH24:MI:SS
- DATETIME_FORMAT_TS6_LEN = 17, // MMDDYYYY HH24:MI:SS
- DATETIME_FORMAT_TS7_LEN = 19, // MM/DD/YYYY HH24:MI:SS
- DATETIME_FORMAT_TS8_LEN = 20, // DD-MON-YYYY HH:MI:SS
- DATETIME_FORMAT_DATE_STR_LEN = -1, // format in str
- DATETIME_FORMAT_TIME_STR_LEN = -1, // format in str
- DATETIME_FORMAT_NONE_LEN = -1,
- DATETIME_FORMAT_ERROR_LEN = -1
+
+ struct DatetimeFormatInfo
+ {
+ Lng32 format;
+ const char * str;
+ Lng32 minLen;
+ Lng32 maxLen;
};
+
+ static const DatetimeFormatInfo datetimeFormat[];
enum { DATETIME_MAX_NUM_FIELDS = 7 };
enum { MAX_DATETIME_SIZE = 11 };
@@ -302,9 +298,7 @@ static
CollHeap *heap,
ComDiagsArea** diagsArea);
-#ifndef __EID
char *getDefaultStringValue(CollHeap *heap);
-#endif
// ---------------------------------------------------------------------
// Redefinition of methods inherited from NAVersionedObject.
@@ -323,11 +317,81 @@ static
NA_EIDPROC virtual short getClassSize() { return (short)sizeof(*this); }
// ---------------------------------------------------------------------
+ static const char * getFormatStr(Lng32 frmt)
+ {
+ return datetimeFormat[frmt].str;
+ }
+
+ static const Lng32 getFormat(const char * formatStr)
+ {
+ for (Lng32 i = DATETIME_FORMAT_MIN; i <= DATETIME_FORMAT_MAX; i++)
+ {
+ if (strcmp(formatStr, datetimeFormat[i].str) == 0)
+ {
+ if (datetimeFormat[i].format != i)
+ return -1;
+
+ return i;
+ }
+ }
+
+ for (Lng32 i = DATETIME_FORMAT_MIN_NUM; i <= DATETIME_FORMAT_MAX_NUM; i++)
+ {
+ if (strcmp(formatStr, datetimeFormat[i].str) == 0)
+ {
+ if (datetimeFormat[i].format != i)
+ return -1;
+
+ return i;
+ }
+ }
+
+ return -1;
+ }
+
+ static NABoolean isDateTimeFormat(Lng32 frmt)
+ {
+ return ((frmt >= DATETIME_FORMAT_MIN) &&
+ (frmt <= DATETIME_FORMAT_MAX));
+ }
+
+ static NABoolean isDateFormat(Lng32 frmt)
+ {
+ return ((frmt >= DATETIME_FORMAT_MIN_DATE) &&
+ (frmt <= DATETIME_FORMAT_MAX_DATE));
+ }
+
+ static NABoolean isTSFormat(Lng32 frmt)
+ {
+ return ((frmt >= DATETIME_FORMAT_MIN_TS) &&
+ (frmt <= DATETIME_FORMAT_MAX_TS));
+ }
+
+ static NABoolean isTimeFormat(Lng32 frmt)
+ {
+ return ((frmt >= DATETIME_FORMAT_MIN_TIME) &&
+ (frmt <= DATETIME_FORMAT_MAX_TIME));
+ }
+
+ static NABoolean isNumericFormat(Lng32 frmt)
+ {
+ return ((frmt == DATETIME_FORMAT_NUM1) || (frmt == DATETIME_FORMAT_NUM2));
+ }
+
+ static Lng32 getAsciiFormatLen(Lng32 frmt)
+ {
+ return datetimeFormat[frmt].minLen;
+ }
+
+ static Lng32 getAsciiFormatMaxLen(Lng32 frmt)
+ {
+ return datetimeFormat[frmt].maxLen;
+ }
+
private:
};
-
#pragma warning ( default : 4251 )
#endif
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/7526b83d/core/sql/exp/exp_function.cpp
----------------------------------------------------------------------
diff --git a/core/sql/exp/exp_function.cpp b/core/sql/exp/exp_function.cpp
index 97be8c5..df1f807 100644
--- a/core/sql/exp/exp_function.cpp
+++ b/core/sql/exp/exp_function.cpp
@@ -2534,9 +2534,8 @@ ex_expr::exp_return_type ex_function_dateformat::eval(char *op_data[],
char *formatStr = op_data[2];
char *result = op_data[0];
- if ((getDateFormat() == ExpDatetime::DATETIME_FORMAT_TIME1) ||
- (getDateFormat() == ExpDatetime::DATETIME_FORMAT_TIME2) ||
- (getDateFormat() == ExpDatetime::DATETIME_FORMAT_TIME_STR))
+ if ((getDateFormat() == ExpDatetime::DATETIME_FORMAT_NUM1) ||
+ (getDateFormat() == ExpDatetime::DATETIME_FORMAT_NUM2))
{
// numeric to TIME conversion.
if(ExpDatetime::convNumericTimeToASCII(opData,
@@ -2573,10 +2572,13 @@ ex_expr::exp_return_type ex_function_dateformat::eval(char *op_data[],
diagsArea,
0) < 0) {
- ExRaiseFunctionSqlError(heap, diagsArea, EXE_INTERNAL_ERROR,
- derivedFunction(),
- origFunctionOperType());
-
+ if (diagsArea && (*diagsArea) &&
+ (*diagsArea)->getNumber(DgSqlCode::ERROR_) == 0)
+ {
+ ExRaiseFunctionSqlError(heap, diagsArea, EXE_INTERNAL_ERROR,
+ derivedFunction(),
+ origFunctionOperType());
+ }
return ex_expr::EXPR_ERROR;
}
}
@@ -2584,7 +2586,7 @@ ex_expr::exp_return_type ex_function_dateformat::eval(char *op_data[],
{
ExpDatetime *datetimeOpType = (ExpDatetime *) getOperand(1);
if(datetimeOpType->convDatetimeToASCII(opData,
- result,
+ result,
getOperand(0)->getLength(),
getDateFormat(),
formatStr,
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/7526b83d/core/sql/generator/GenItemFunc.cpp
----------------------------------------------------------------------
diff --git a/core/sql/generator/GenItemFunc.cpp b/core/sql/generator/GenItemFunc.cpp
index 8c8f7e6..b40dc2c 100644
--- a/core/sql/generator/GenItemFunc.cpp
+++ b/core/sql/generator/GenItemFunc.cpp
@@ -1851,143 +1851,11 @@ short DateFormat::codeGen(Generator * generator)
-1) == 1)
return 0;
- Int32 expDateFormat = ExpDatetime::DATETIME_FORMAT_ERROR;
- switch (getDateFormat())
- {
- case DEFAULT:
- expDateFormat = ExpDatetime::DATETIME_FORMAT_DEFAULT;
- break;
-
- case USA:
- expDateFormat = ExpDatetime::DATETIME_FORMAT_USA;
- break;
-
- case EUROPEAN:
- expDateFormat = ExpDatetime::DATETIME_FORMAT_EUROPEAN;
- break;
-
- case DATE_FORMAT_STR:
- {
- if (child(1)->castToItemExpr()->getOperatorType() == ITM_CONSTANT)
- {
- ConstValue * cv = (ConstValue*)(child(1)->castToItemExpr());
- if (NAString((char*)(cv->getConstValue()), cv->getStorageSize())
- == "YYYY-MM-DD")
- expDateFormat = ExpDatetime::DATETIME_FORMAT_DEFAULT;
- else if (NAString((char*)(cv->getConstValue()), cv->getStorageSize())
- == "YYYY-MM")
- expDateFormat = ExpDatetime::DATETIME_FORMAT_DEFAULT2;
- else if (NAString((char*)(cv->getConstValue()), cv->getStorageSize())
- == "MM/DD/YYYY")
- expDateFormat = ExpDatetime::DATETIME_FORMAT_USA2;
- else if (NAString((char*)(cv->getConstValue()), cv->getStorageSize())
- == "YYYY/MM/DD")
- expDateFormat = ExpDatetime::DATETIME_FORMAT_USA3;
- else if (NAString((char*)(cv->getConstValue()), cv->getStorageSize())
- == "YYYYMMDD")
- expDateFormat = ExpDatetime::DATETIME_FORMAT_USA4;
- else if (NAString((char*)(cv->getConstValue()), cv->getStorageSize())
- == "YY/MM/DD")
- expDateFormat = ExpDatetime::DATETIME_FORMAT_USA5;
- else if (NAString((char*)(cv->getConstValue()), cv->getStorageSize())
- == "MM/DD/YY")
- expDateFormat = ExpDatetime::DATETIME_FORMAT_USA6;
- else if (NAString((char*)(cv->getConstValue()), cv->getStorageSize())
- == "MM-DD-YYYY")
- expDateFormat = ExpDatetime::DATETIME_FORMAT_USA7;
- else if (NAString((char*)(cv->getConstValue()), cv->getStorageSize())
- == "YYYYMM")
- expDateFormat = ExpDatetime::DATETIME_FORMAT_USA8;
- else if (NAString((char*)(cv->getConstValue()), cv->getStorageSize())
- == "DD.MM.YYYY")
- expDateFormat = ExpDatetime::DATETIME_FORMAT_EUROPEAN;
- else if (NAString((char*)(cv->getConstValue()), cv->getStorageSize())
- == "DD-MM-YYYY")
- expDateFormat = ExpDatetime::DATETIME_FORMAT_EUROPEAN2;
- else if ((NAString((char*)(cv->getConstValue()), cv->getStorageSize())
- == "DD-MMM-YYYY") ||
- (NAString((char*)(cv->getConstValue()), cv->getStorageSize())
- == "DD-MON-YYYY"))
- expDateFormat = ExpDatetime::DATETIME_FORMAT_EUROPEAN3;
- else if (NAString((char*)(cv->getConstValue()), cv->getStorageSize())
- == "DDMONYYYY")
- expDateFormat = ExpDatetime::DATETIME_FORMAT_EUROPEAN4;
- else
- expDateFormat = ExpDatetime::DATETIME_FORMAT_DATE_STR;
- }
- else
- {
- expDateFormat = ExpDatetime::DATETIME_FORMAT_DATE_STR;
- }
- }
- break;
-
- case TIME_FORMAT_STR:
- {
- if (child(1)->castToItemExpr()->getOperatorType() == ITM_CONSTANT)
- {
- ConstValue * cv = (ConstValue*)(child(1)->castToItemExpr());
- if (NAString((char*)(cv->getConstValue()), cv->getStorageSize())
- == "99:99:99:99")
- expDateFormat = ExpDatetime::DATETIME_FORMAT_TIME1;
- else if (NAString((char*)(cv->getConstValue()), cv->getStorageSize())
- == "-99:99:99:99")
- expDateFormat = ExpDatetime::DATETIME_FORMAT_TIME2;
- else if (NAString((char*)(cv->getConstValue()), cv->getStorageSize())
- == "HH24:MI:SS")
- expDateFormat = ExpDatetime::DATETIME_FORMAT_TS4;
- else
- expDateFormat = ExpDatetime::DATETIME_FORMAT_TIME_STR;
- }
- else
- {
- expDateFormat = ExpDatetime::DATETIME_FORMAT_TIME_STR;
- }
- }
- break;
-
- case TIMESTAMP_FORMAT_STR:
- {
- if (child(1)->castToItemExpr()->getOperatorType() == ITM_CONSTANT)
- {
- ConstValue * cv = (ConstValue*)(child(1)->castToItemExpr());
-
- if (NAString((char*)(cv->getConstValue()), cv->getStorageSize())
- == "YYYYMMDDHH24MISS")
- expDateFormat = ExpDatetime::DATETIME_FORMAT_TS1;
- else if (NAString((char*)(cv->getConstValue()), cv->getStorageSize())
- == "DD.MM.YYYY:HH24:MI:SS")
- expDateFormat = ExpDatetime::DATETIME_FORMAT_TS2;
- else if (NAString((char*)(cv->getConstValue()), cv->getStorageSize())
- == "YYYY-MM-DD HH24:MI:SS")
- expDateFormat = ExpDatetime::DATETIME_FORMAT_TS3;
- else if (NAString((char*)(cv->getConstValue()), cv->getStorageSize())
- == "YYYYMMDD:HH24:MI:SS")
- expDateFormat = ExpDatetime::DATETIME_FORMAT_TS5;
- else if (NAString((char*)(cv->getConstValue()), cv->getStorageSize())
- == "MMDDYYYY HH24:MI:SS")
- expDateFormat = ExpDatetime::DATETIME_FORMAT_TS6;
- else if (NAString((char*)(cv->getConstValue()), cv->getStorageSize())
- == "MM/DD/YYYY HH24:MI:SS")
- expDateFormat = ExpDatetime::DATETIME_FORMAT_TS7;
- else if (NAString((char*)(cv->getConstValue()), cv->getStorageSize())
- == "DD-MON-YYYY HH:MI:SS")
- expDateFormat = ExpDatetime::DATETIME_FORMAT_TS8;
- else
- {
- expDateFormat = ExpDatetime::DATETIME_FORMAT_DATE_STR;
- }
- }
- }
- break;
-
- };
-
ex_clause * function_clause =
new(generator->getSpace()) ex_function_dateformat(getOperatorType(),
attr,
generator->getSpace(),
- expDateFormat);
+ getExpDatetimeFormat());
generator->getExpGenerator()->linkClause(this, function_clause);
return 0;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/7526b83d/core/sql/optimizer/BindItemExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/BindItemExpr.cpp b/core/sql/optimizer/BindItemExpr.cpp
index 3fd854e..6e85e69 100644
--- a/core/sql/optimizer/BindItemExpr.cpp
+++ b/core/sql/optimizer/BindItemExpr.cpp
@@ -77,6 +77,9 @@
#include "ItemFuncUDF.h"
#include "CmpSeabaseDDL.h"
#include "QCache.h"
+
+#include "exp_datetime.h"
+
#include <stack>
@@ -1236,6 +1239,7 @@ Int32 ItemExpr::shouldPushTranslateDown(CharInfo::CharSet chrset) const
case ITM_CAST: // a) output is of a specific charset
case ITM_CAST_CONVERT: // a) internal node, too late to do ICAT
case ITM_CAST_TYPE:
+ case ITM_DATEFORMAT:
return -1;
case ITM_LEFT: // b) counts characters
@@ -3708,19 +3712,10 @@ ItemExpr * ExtractOdbc::bindNode(BindWA * bindWA)
return ie ;
}
-ItemExpr * Format::bindNode(BindWA * bindWA)
+ItemExpr * DateFormat::quickDateFormatOpt(BindWA * bindWA)
{
- // if (nodeIsBound())
- // return this;
-
- bindSelf(bindWA);
- if (bindWA->errStatus())
- return this;
-
const NAType *naType0 = &child(0)->getValueId().getType();
- const NumericType * nType0 = NULL;
- // a quick optimization for the date format.
if ((CmpCommon::getDefault(MODE_SPECIAL_4) == DF_ON) &&
(child(0)->getOperatorType() == ITM_CONSTANT) &&
(formatStr_ == "DD-MON-YYYY") &&
@@ -3801,188 +3796,29 @@ ItemExpr * Format::bindNode(BindWA * bindWA)
bindWA->resetErrStatus();
}
+ return NULL;
+}
+
+ItemExpr * Format::bindNode(BindWA * bindWA)
+{
+ bindChildren(bindWA);
+ if (bindWA->errStatus())
+ return this;
+
+ const NAType *naType0 = &child(0)->getValueId().getType();
+ const NumericType * nType0 = NULL;
- Int32 dateFormat = DateFormat::DEFAULT;
- NABoolean formatAsDate = FALSE;
NABoolean formatX = FALSE;
NABoolean format9 = FALSE;
NABoolean formatExtract = FALSE;
Lng32 dotPos = 0;
NABoolean formatNumericAsX = FALSE;
NABoolean formatStringAsX = FALSE;
- if ((formatStr_ == "YYYY-MM-DD") ||
- (formatStr_ == "MM/DD/YYYY") ||
- (formatStr_ == "YY/MM/DD") ||
- (formatStr_ == "YYYY/MM/DD") ||
- (formatStr_ == "MM/DD/YY") ||
- (formatStr_ == "YYYYMMDD") ||
- (formatStr_ == "DD.MM.YYYY") ||
- (formatStr_ == "DD-MM-YYYY") ||
- (formatStr_ == "DD-MMM-YYYY") ||
- (formatStr_ == "DD-MON-YYYY") ||
- (formatStr_ == "DDMONYYYY") ||
- (formatStr_ == "YYYYMMDDHH24MISS") ||
- (formatStr_ == "YYYYMMDD:HH24:MI:SS") ||
- (formatStr_ == "DD.MM.YYYY:HH24:MI:SS") ||
- (formatStr_ == "YYYY-MM-DD HH24:MI:SS") ||
- (formatStr_ == "MMDDYYYY HH24:MI:SS") ||
- (formatStr_ == "MM/DD/YYYY HH24:MI:SS") ||
- (formatStr_ == "DD-MON-YYYY HH:MI:SS") ||
- (formatStr_ == "HH24:MI:SS") ||
- (formatStr_ == "YYYYMM") ||
- (formatStr_ == "YYYY-MM") ||
- (formatStr_ == "SYYYYMM") ||
- (formatStr_ == "MM-DD-YYYY"))
- {
- if (formatType_ == FORMAT_TO_CHAR)
- {
- if (naType0->getTypeQualifier() != NA_DATETIME_TYPE)
- {
- *CmpCommon::diags() << DgSqlCode(-4071) << DgString0("TO_CHAR");
- bindWA->setErrStatus();
- return this;
- }
-
- if ((formatStr_ == "HH24:MI:SS") &&
- (naType0->getPrecision() != SQLDTCODE_TIMESTAMP) &&
- (naType0->getPrecision() != SQLDTCODE_TIME))
- {
- *CmpCommon::diags() << DgSqlCode(-4072) << DgString0("TO_CHAR") << DgString1("time");;
- bindWA->setErrStatus();
- return this;
- }
- }
-
- if ((formatType_ == FORMAT_TO_DATE) &&
- ((naType0->getTypeQualifier() != NA_CHARACTER_TYPE) &&
- (naType0->getTypeQualifier() != NA_NUMERIC_TYPE)))
- {
- *CmpCommon::diags() << DgSqlCode(-4043) << DgString0("TO_DATE");
- bindWA->setErrStatus();
- return this;
- }
-
- if ((naType0->getTypeQualifier() == NA_CHARACTER_TYPE) &&
- (formatCharToDate_ == FALSE))
- {
- *CmpCommon::diags() << DgSqlCode(-4065) << DgString0(formatStr_)
- << DgString1(formatType_ == FORMAT_GENERIC ? "FORMAT"
- : (formatType_ == FORMAT_TO_CHAR ? "TO_CHAR" : "TO_DATE"));
-
- bindWA->setErrStatus();
- return this;
- }
-
- if (((formatStr_ == "YYYYMMDDHH24MISS") ||
- (formatStr_ == "YYYYMMDD:HH24:MI:SS") ||
- (formatStr_ == "DD.MM.YYYY:HH24:MI:SS") ||
- (formatStr_ == "YYYY-MM-DD HH24:MI:SS") ||
- (formatStr_ == "MMDDYYYY HH24:MI:SS") ||
- (formatStr_ == "MM/DD/YYYY HH24:MI:SS") ||
- (formatStr_ == "HH24:MI:SS") ||
- (formatStr_ == "MM/DD/YY") ||
- (formatStr_ == "YYYYMM") ||
- (formatStr_ == "YYYY-MM") ||
- (formatStr_ == "SYYYYMM") ||
- (formatStr_ == "DDMONYYYY") ||
- (formatStr_ == "MM-DD-YYYY")) &&
- (CmpCommon::getDefault(MODE_SPECIAL_3) == DF_OFF))
- {
- *CmpCommon::diags() << DgSqlCode(-4065) << DgString0(formatStr_)
- << DgString1(formatType_ == FORMAT_GENERIC ? "FORMAT"
- : (formatType_ == FORMAT_TO_CHAR ? "TO_CHAR" : "TO_DATE"));
- bindWA->setErrStatus();
- return this;
- }
-
- if (formatStr_ == "SYYYYMM")
- formatStr_ = "YYYYMM";
-
- NAString newStr;
- if (((formatStr_ == "YYYYMM") ||
- (formatStr_ == "YYYY-MM")) &&
- (CmpCommon::getDefault(MODE_SPECIAL_3) == DF_ON) &&
- (formatCharToDate_) &&
- (naType0->getTypeQualifier() == NA_NUMERIC_TYPE))
- {
- // currently only supports formatting of a numeric constant string literal
- // of the form '200906'. Missing day is filled with value '01' and
- // a new constant is added as child(0).
- ConstValue * cv = (ConstValue *)child(0)->castToItemExpr();
- if ((cv->getOperatorType() != ITM_CONSTANT) ||
- (NOT cv->canGetExactNumericValue()))
- {
- *CmpCommon::diags() << DgSqlCode(-4065) << DgString0(formatStr_)
- << DgString1(formatType_ == FORMAT_GENERIC ? "FORMAT"
- : (formatType_ == FORMAT_TO_CHAR ? "TO_CHAR" : "TO_DATE"));
- bindWA->setErrStatus();
- return this;
- }
-
- Lng32 val = (Lng32)cv->getExactNumericValue();
- char nBuf[40];
- sprintf(nBuf, "%d", val);
- newStr = nBuf;
-
- ConstValue *newChild =
- new (bindWA->wHeap()) ConstValue(newStr, bindWA->wHeap());
- setChild(0, newChild->bindNode(bindWA));
-
- naType0 = &child(0)->getValueId().getType();
- } // if YYYYMM
-
- if (naType0->getTypeQualifier() == NA_NUMERIC_TYPE)
- {
- if (formatCharToDate_ == FALSE)
- {
- *CmpCommon::diags() << DgSqlCode(-4065) << DgString0(formatStr_)
- << DgString1(formatType_ == FORMAT_GENERIC ? "FORMAT"
- : (formatType_ == FORMAT_TO_CHAR ? "TO_CHAR" : "TO_DATE"));
- bindWA->setErrStatus();
- return this;
- }
-
- // convert number to char before formatting.
- // Length of target char is equal to formatStr_.
- ItemExpr * newChild =
- new (bindWA->wHeap())
- Cast(child(0),
- new (bindWA->wHeap())
- SQLChar(formatStr_.length(),
- child(0)->castToItemExpr()->
- getValueId().getType().supportsSQLnull()));
- setChild(0, newChild->bindNode(bindWA));
- naType0 = &child(0)->getValueId().getType();
- }
-
- if ((CmpCommon::getDefault(MODE_SPECIAL_2) == DF_OFF) &&
- (formatCharToDate_ == FALSE) &&
- (naType0->getTypeQualifier() != NA_DATETIME_TYPE))
- {
- // 4071 The operand of a DATEFORMAT function must be a datetime.
- *CmpCommon::diags() << DgSqlCode(-4071) << DgString0(getTextUpper());
- return NULL;
- }
-
- formatAsDate = TRUE;
- if ((formatStr_ == "YYYYMMDDHH24MISS") ||
- (formatStr_ == "YYYYMMDD:HH24:MI:SS") ||
- (formatStr_ == "DD.MM.YYYY:HH24:MI:SS") ||
- (formatStr_ == "YYYY-MM-DD HH24:MI:SS") ||
- (formatStr_ == "MMDDYYYY HH24:MI:SS") ||
- (formatStr_ == "MM/DD/YYYY HH24:MI:SS") ||
- (formatStr_ == "DD-MON-YYYY HH:MI:SS"))
- dateFormat = DateFormat::TIMESTAMP_FORMAT_STR;
- else if (formatStr_ == "HH24:MI:SS")
- dateFormat = DateFormat::TIME_FORMAT_STR;
- else
- dateFormat = DateFormat::DATE_FORMAT_STR;
- }
- else if ((formatStr_ == "HH24") ||
- (formatStr_ == "D") ||
- (formatStr_ == "MM") ||
- (formatStr_ == "YYYY"))
+ if ((formatStr_ == "HH24") ||
+ (formatStr_ == "D") ||
+ (formatStr_ == "MM") ||
+ (formatStr_ == "YYYY"))
{
if (CmpCommon::getDefault(MODE_SPECIAL_4) == DF_OFF)
{
@@ -4011,22 +3847,6 @@ ItemExpr * Format::bindNode(BindWA * bindWA)
formatExtract = TRUE;
}
-
- else if ((formatStr_ == "99:99:99:99") ||
- (formatStr_ == "-99:99:99:99"))
- {
- if (naType0->getTypeQualifier() == NA_NUMERIC_TYPE)
- {
- formatAsDate = TRUE;
- dateFormat = DateFormat::TIME_FORMAT_STR;
- }
- else
- {
- *CmpCommon::diags() << DgSqlCode(-4045) << DgString0("FORMAT");
- bindWA->setErrStatus();
- return this;
- }
- }
else
{
formatX = TRUE;
@@ -4073,88 +3893,7 @@ ItemExpr * Format::bindNode(BindWA * bindWA)
}
ItemExpr * newIE= NULL;
- if (formatAsDate)
- {
- ConstValue * cv = new(bindWA->wHeap()) SystemLiteral(formatStr_);
-
- if ((dateFormat == DateFormat::TIME_FORMAT_STR) &&
- (formatStr_ != "HH24:MI:SS"))
- {
- if (naType0->getTypeName() != LiteralLargeInt)
- {
- // convert to largeint. We have already verified that
- // child is exact with scale of 0.
- ItemExpr * newChild =
- new (bindWA->wHeap())
- Cast(child(0),
- new (bindWA->wHeap())
- SQLLargeInt(TRUE,
- child(0)->castToItemExpr()->
- getValueId().getType().supportsSQLnull()));
- setChild(0, newChild->bindNode(bindWA));
- }
- }
-
- ItemExpr * newChild = NULL;
-
- if ((CmpCommon::getDefault(MODE_SPECIAL_3) == DF_ON) &&
- (formatCharToDate_ == FALSE) &&
- (naType0->getPrecision() == SQLDTCODE_TIMESTAMP))
- {
- if (formatStr_ == "HH24:MI:SS")
- {
- newChild =
- new (bindWA->wHeap())
- Cast(child(0),
- new (bindWA->wHeap())
- SQLTime(child(0)->castToItemExpr()->
- getValueId().getType().supportsSQLnull(),
- 0));
- }
- else if ((formatStr_ == "YYYYMMDDHH24MISS") ||
- (formatStr_ == "YYYYMMDD:HH24:MI:SS") ||
- (formatStr_ == "DD.MM.YYYY:HH24:MI:SS") ||
- (formatStr_ == "YYYY-MM-DD HH24:MI:SS") ||
- (formatStr_ == "MMDDYYYY HH24:MI:SS") ||
- (formatStr_ == "MM/DD/YYYY HH24:MI:SS") ||
- (formatStr_ == "DD-MON-YYYY HH:MI:SS"))
- {
- // do nothing
- }
- else
- {
- newChild =
- new (bindWA->wHeap())
- Cast(child(0),
- new (bindWA->wHeap())
- SQLDate(child(0)->castToItemExpr()->
- getValueId().getType().supportsSQLnull()));
- }
-
- if (newChild)
- setChild(0, newChild->bindNode(bindWA));
- }
-
- naType0 = &child(0)->getValueId().getType();
- if (DFS2REC::isAnyVarChar(naType0->getFSDatatype()))
- {
- // convert to fixed char.
- newChild =
- new (bindWA->wHeap())
- Cast(child(0),
- new (bindWA->wHeap())
- SQLChar(naType0->getNominalSize(),
- naType0->supportsSQLnull()));
- setChild(0, newChild->bindNode(bindWA));
- }
-
- newIE=
- new (bindWA->wHeap()) DateFormat(child(0), cv, dateFormat);
- newIE = newIE->bindNode(bindWA);
- if (bindWA->errStatus())
- return NULL;
- }
- else if ((formatX) || (format9) || (formatExtract))
+ if ((formatX) || (format9) || (formatExtract))
{
Parser parser(bindWA->currentCmpContext());
char buf[200];
@@ -4240,21 +3979,252 @@ ItemExpr * Format::bindNode(BindWA * bindWA)
else
{
if (CmpCommon::getDefault(MODE_SPECIAL_1) == DF_OFF)
- {
- *CmpCommon::diags() << DgSqlCode(-4065) << DgString0(formatStr_)
- << DgString1(formatType_ == FORMAT_GENERIC ? "FORMAT"
- : (formatType_ == FORMAT_TO_CHAR ? "TO_CHAR" : "TO_DATE"));
- bindWA->setErrStatus();
- return this;
- }
-
+ {
+ *CmpCommon::diags() << DgSqlCode(-4065) << DgString0(formatStr_)
+ << DgString1(formatType_ == FORMAT_GENERIC ? "FORMAT"
+ : (formatType_ == FORMAT_TO_CHAR ? "TO_CHAR" : "TO_DATE"));
+ bindWA->setErrStatus();
+ return this;
+ }
+
// In mode_special_1, ignore this format and return the child pointer.
newIE = child(0);
}
-
+
return newIE;
}
+//
+// <format>: date(year to day), time(hour to sec), timestamp(year to sec)
+//
+// TO_DATE(<char>, <format>)
+//
+NABoolean DateFormat::errorChecks(Lng32 frmt, BindWA *bindWA,
+ const NAType* opType)
+{
+ Lng32 error = 0;
+
+ NABoolean tc = (formatType_ == FORMAT_TO_CHAR);
+ NABoolean td = (formatType_ == FORMAT_TO_DATE);
+ NABoolean df = ExpDatetime::isDateFormat(frmt);
+ NABoolean tf = ExpDatetime::isTimeFormat(frmt);
+ NABoolean tsf = ExpDatetime::isTSFormat(frmt);
+ NABoolean nf = ExpDatetime::isNumericFormat(frmt);
+ NABoolean ms4 = (CmpCommon::getDefault(MODE_SPECIAL_4) == DF_ON);
+
+ if (NOT (df || tf || tsf || nf))
+ error = 1; // error 4065
+ else if ((NOT ms4) && nf)
+ error = 1; // error 4065
+ if (!error && tc)
+ {
+ if (opType->getTypeQualifier() != NA_DATETIME_TYPE)
+ error = 2; // error 4071
+ else if (tf && (opType->getPrecision() == SQLDTCODE_DATE))
+ error = 3; // error 4072
+ }
+ if (!error && td)
+ {
+ if ((opType->getTypeQualifier() != NA_CHARACTER_TYPE) &&
+ (opType->getTypeQualifier() != NA_NUMERIC_TYPE))
+ error = 4; //error 4043
+ else if ((NOT ms4) && (opType->getTypeQualifier() != NA_CHARACTER_TYPE))
+ error = 4; // error 4043
+ }
+ if (!error && ms4 && nf)
+ {
+ if ((opType->getTypeQualifier() != NA_NUMERIC_TYPE) ||
+ (NOT ((NumericType*)opType)->isExact()) ||
+ (NOT((NumericType*)opType)->getScale() == 0))
+ error = 5; // error 4045
+ }
+
+ if (error)
+ {
+ switch (error)
+ {
+ case 1:
+ {
+ *CmpCommon::diags() << DgSqlCode(-4065) << DgString0(formatStr_)
+ << DgString1((formatType_ == FORMAT_TO_CHAR
+ ? "TO_CHAR" : "TO_DATE"));
+ bindWA->setErrStatus();
+ }
+ break;
+
+ case 2:
+ {
+ *CmpCommon::diags() << DgSqlCode(-4071) << DgString0("TO_CHAR");
+ bindWA->setErrStatus();
+ }
+ break;
+
+ case 3:
+ {
+ *CmpCommon::diags() << DgSqlCode(-4072) << DgString0("TO_CHAR") << DgString1("time");;
+ bindWA->setErrStatus();
+ }
+ break;
+
+ case 4:
+ {
+ *CmpCommon::diags() << DgSqlCode(-4043) << DgString0("TO_DATE");
+ bindWA->setErrStatus();
+ }
+ break;
+
+ case 5:
+ {
+ *CmpCommon::diags() << DgSqlCode(-4045) << DgString0("FORMAT");
+ bindWA->setErrStatus();
+ }
+ break;
+ } // switch
+
+ return TRUE;
+ }
+
+ return FALSE;
+}
+
+DateFormat::DateFormat(ItemExpr *val1Ptr, const NAString &formatStr,
+ Lng32 formatType, NABoolean wasDateformat)
+ : CacheableBuiltinFunction(ITM_DATEFORMAT,
+ 1, val1Ptr),
+ formatStr_(formatStr),
+ wasDateformat_(wasDateformat),
+ formatType_(formatType),
+ frmt_(-1),
+ dateFormat_(DATE_FORMAT_NONE)
+{
+ allowsSQLnullArg() = FALSE;
+
+ if (formatStr_ == "SYYYYMM")
+ formatStr_ = "YYYYMM";
+ else if (formatStr_ == "HH:MI:SS")
+ formatStr_ = "HH24:MI:SS";
+
+ frmt_ = ExpDatetime::getFormat(formatStr_.data());
+}
+
+ItemExpr * DateFormat::bindNode(BindWA * bindWA)
+{
+ if (checkForSQLnullChild(bindWA, this, allowsSQLnullArg(), FUNCTION_))
+ return this;
+
+ bindChildren(bindWA);
+ if (bindWA->errStatus())
+ return this;
+
+ const NAType *naType0 = &child(0)->getValueId().getType();
+ const NumericType * nType0 = NULL;
+
+ // a quick optimization for the date format.
+ ItemExpr *newNode = quickDateFormatOpt(bindWA);
+ if (newNode)
+ return newNode;
+
+ if (errorChecks(frmt_, bindWA, naType0))
+ {
+ return this;
+ }
+
+ dateFormat_ = DateFormat::DATE_FORMAT_NONE;
+
+ if (ExpDatetime::isDateTimeFormat(frmt_))
+ {
+ if (ExpDatetime::isTSFormat(frmt_))
+ dateFormat_ = DateFormat::TIMESTAMP_FORMAT_STR;
+ else if (ExpDatetime::isTimeFormat(frmt_))
+ dateFormat_ = DateFormat::TIME_FORMAT_STR;
+ else
+ dateFormat_ = DateFormat::DATE_FORMAT_STR;
+
+ if (naType0->getTypeQualifier() == NA_NUMERIC_TYPE)
+ {
+ // convert number to char before formatting.
+ // Length of target char is equal to formatStr_.
+ ItemExpr * newChild =
+ new (bindWA->wHeap())
+ Cast(child(0),
+ new (bindWA->wHeap())
+ SQLChar(formatStr_.length(),
+ child(0)->castToItemExpr()->
+ getValueId().getType().supportsSQLnull()));
+ setChild(0, newChild->bindNode(bindWA));
+ naType0 = &child(0)->getValueId().getType();
+ }
+ }
+ else if (ExpDatetime::isNumericFormat(frmt_))
+ {
+ dateFormat_ = DateFormat::TIME_FORMAT_STR;
+
+ if (naType0->getTypeName() != LiteralLargeInt)
+ {
+ // convert to largeint. We have already verified that
+ // child is exact with scale of 0.
+ ItemExpr * newChild =
+ new (bindWA->wHeap())
+ Cast(child(0),
+ new (bindWA->wHeap())
+ SQLLargeInt(TRUE,
+ child(0)->castToItemExpr()->
+ getValueId().getType().supportsSQLnull()));
+ setChild(0, newChild->bindNode(bindWA));
+ }
+ }
+ else
+ {
+ CMPASSERT(FALSE); // should not reach here
+ }
+
+ ItemExpr * newChild = NULL;
+ if (naType0->getPrecision() == SQLDTCODE_TIMESTAMP)
+ {
+ if (ExpDatetime::isTimeFormat(frmt_))
+ {
+ newChild =
+ new (bindWA->wHeap())
+ Cast(child(0),
+ new (bindWA->wHeap())
+ SQLTime(child(0)->castToItemExpr()->
+ getValueId().getType().supportsSQLnull(),
+ 0));
+ }
+ else if (ExpDatetime::isDateFormat(frmt_))
+ {
+ newChild =
+ new (bindWA->wHeap())
+ Cast(child(0),
+ new (bindWA->wHeap())
+ SQLDate(child(0)->castToItemExpr()->
+ getValueId().getType().supportsSQLnull()));
+ }
+
+ if (newChild)
+ setChild(0, newChild->bindNode(bindWA));
+
+ naType0 = &child(0)->getValueId().getType();
+ if (DFS2REC::isAnyVarChar(naType0->getFSDatatype()))
+ {
+ // convert to fixed char.
+ newChild =
+ new (bindWA->wHeap())
+ Cast(child(0),
+ new (bindWA->wHeap())
+ SQLChar(naType0->getNominalSize(),
+ naType0->supportsSQLnull()));
+ setChild(0, newChild->bindNode(bindWA));
+ }
+ }
+
+ BuiltinFunction::bindNode(bindWA);
+ if (bindWA->errStatus())
+ return this;
+
+ return getValueId().getItemExpr();
+}
+
ItemExpr *Trim::bindNode(BindWA *bindWA)
{
if (nodeIsBound())
@@ -11829,27 +11799,6 @@ ItemExpr *ZZZBinderFunction::bindNode(BindWA *bindWA)
}
break;
- case ITM_DATEFMT:
- {
- Lng32 datefmt;
- ItemExpr *secondOpExpr = child(1)->castToItemExpr();
- secondOpExpr->bindNode(bindWA);
- if (bindWA->errStatus())
- return this;
-
- datefmt = (Lng32)((ConstValue*)secondOpExpr)->getExactNumericValue();
-
- strcpy(buf, "TRANSLATE(DATEFMT_INTN");
- if (datefmt == DateFormat::USA)
- strcat(buf, "(@A1,USA)");
-
- else if (datefmt == DateFormat::EUROPEAN)
- strcat(buf, "(@A1,EUROPEAN)");
- else
- strcat(buf, "(@A1,DEFAULT)");
- strcat(buf, " USING ISO88591ToUCS2);");
- }
- break;
case ITM_CURRNT_USER:
{
strcpy(buf, "TRANSLATE(CURRNT_USR_INTN USING ISO88591ToUCS2);");
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/7526b83d/core/sql/optimizer/ItemCache.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/ItemCache.cpp b/core/sql/optimizer/ItemCache.cpp
index 9bdd830..703f9f8 100644
--- a/core/sql/optimizer/ItemCache.cpp
+++ b/core/sql/optimizer/ItemCache.cpp
@@ -714,7 +714,10 @@ void DateFormat::generateCacheKey(CacheWA& cwa) const
{
BuiltinFunction::generateCacheKey(cwa);
cwa += " fmt:";
- char dFmt[20]; str_itoa(dateFormat_, dFmt);
+ char dFmt[20];
+ str_itoa(dateFormat_, dFmt);
+ cwa += dFmt;
+ str_itoa(frmt_, dFmt);
cwa += dFmt;
}
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/7526b83d/core/sql/optimizer/ItemExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/ItemExpr.cpp b/core/sql/optimizer/ItemExpr.cpp
index d68768e..a76ddb4 100644
--- a/core/sql/optimizer/ItemExpr.cpp
+++ b/core/sql/optimizer/ItemExpr.cpp
@@ -49,7 +49,7 @@
#include "exp_function.h" // for calling ExHDPHash::hash(data, len)
#include "ItemFuncUDF.h"
#include "CmpStatement.h"
-//#include "ItmFlowControlFunction.h"
+#include "exp_datetime.h"
#include "OptRange.h"
@@ -8241,12 +8241,16 @@ DateFormat::~DateFormat() {}
ItemExpr * DateFormat::copyTopNode(ItemExpr *derivedNode,
CollHeap* outHeap)
{
- ItemExpr *result;
+ DateFormat *result;
if (derivedNode == NULL)
- result = new (outHeap) DateFormat(child(0), child(1), getDateFormat());
+ result = new (outHeap) DateFormat(child(0),
+ formatStr_, formatType_,
+ wasDateformat_);
else
- result = derivedNode;
+ result = (DateFormat*)derivedNode;
+
+ frmt_ = result->frmt_;
return BuiltinFunction::copyTopNode(result,outHeap);
@@ -8265,6 +8269,46 @@ NABoolean DateFormat::hasEquivalentProperties(ItemExpr * other)
return
(this->dateFormat_ == df->dateFormat_);
}
+
+void DateFormat::unparse(NAString &result,
+ PhaseEnum phase,
+ UnparseFormatEnum form,
+ TableDesc * tabId) const
+{
+ if (wasDateformat_)
+ result += "DATEFORMAT(";
+ else if (formatType_ == FORMAT_TO_DATE)
+ result += "TO_DATE(";
+ else if (formatType_ == FORMAT_TO_CHAR)
+ result += "TO_CHAR(";
+ else
+ result += "unknown(";
+
+ child(0)->unparse(result, phase, form, tabId);
+
+ result += ", ";
+
+ if (wasDateformat_)
+ {
+ if (frmt_ == ExpDatetime::DATETIME_FORMAT_DEFAULT)
+ result += "DEFAULT";
+ else if (frmt_ == ExpDatetime::DATETIME_FORMAT_USA)
+ result += "USA";
+ else if (frmt_ == ExpDatetime::DATETIME_FORMAT_EUROPEAN)
+ result += "EUROPEAN";
+ else
+ result += "unknown";
+ }
+ else
+ {
+ result += "'";
+ result += formatStr_;
+ result += "'";
+ }
+
+ result += ")";
+}
+
// -----------------------------------------------------------------------
// member functions for class DayOfWeek
// -----------------------------------------------------------------------
[5/6] incubator-trafodion git commit: JIRA TRAFODION-1873 Support for
to_date, to_char functions
Posted by an...@apache.org.
JIRA TRAFODION-1873 Support for to_date, to_char functions
Support for to_date, to_char functions.
Code cleanup and simplification.
Bug fixes.
New test regress/seabase/TEST030.
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/5fc9ce5f
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/5fc9ce5f
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/5fc9ce5f
Branch: refs/heads/master
Commit: 5fc9ce5fd412ca3ecac0fe149f863121c9dcb8f7
Parents: ccb3e44
Author: Cloud User <ce...@ansharma-2.novalocal>
Authored: Tue Mar 8 16:57:50 2016 +0000
Committer: Cloud User <ce...@ansharma-2.novalocal>
Committed: Tue Mar 8 16:57:50 2016 +0000
----------------------------------------------------------------------
core/sql/exp/exp_datetime.cpp | 42 +++++++++--------
core/sql/exp/exp_datetime.h | 20 ++++----
core/sql/exp/exp_function.cpp | 4 +-
core/sql/optimizer/BindItemExpr.cpp | 77 ++++++++++++++++++-------------
core/sql/optimizer/SynthType.cpp | 4 +-
core/sql/parser/sqlparser.y | 13 ++----
core/sql/regress/seabase/EXPECTED030 | 74 ++++++++++++++++++++++++-----
core/sql/regress/seabase/TEST030 | 19 ++++++--
8 files changed, 164 insertions(+), 89 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5fc9ce5f/core/sql/exp/exp_datetime.cpp
----------------------------------------------------------------------
diff --git a/core/sql/exp/exp_datetime.cpp b/core/sql/exp/exp_datetime.cpp
index 6b93d1e..e1f00bb 100644
--- a/core/sql/exp/exp_datetime.cpp
+++ b/core/sql/exp/exp_datetime.cpp
@@ -1634,7 +1634,7 @@ ExpDatetime::extractDatetime(rec_datetime_field srcStartField,
//
NA_EIDPROC
static
-ExpDatetime::asciiFormats
+ExpDatetime::DatetimeFormats
determineFormat(char *src,
rec_datetime_field startField,
rec_datetime_field endField)
@@ -2449,9 +2449,11 @@ static short convSrcDataToDst(Lng32 numSrcBytes, char* &srcData,
return -1;
}
- if (numTgtBytes == 2)
+ if (numTgtBytes == sizeof(Lng32))
+ *(Lng32*)dstData = val;
+ else if (numTgtBytes == sizeof(short))
*(short*)dstData = val;
- else if (numTgtBytes == 1)
+ else if (numTgtBytes == sizeof(char))
*(char*)dstData = val;
else
return -1;
@@ -2471,6 +2473,7 @@ static short convSrcDataToDst(Lng32 numSrcBytes, char* &srcData,
return 0;
}
+//////////////////////////////////////////////////////////////////////////
// ExpDatetime::convAsciiToDate() ================================
// This method is used to convert the given ASCII string
// to a datetime date value.
@@ -2479,22 +2482,26 @@ static short convSrcDataToDst(Lng32 numSrcBytes, char* &srcData,
// 'dstData'. This buffer must be allocated by the caller and it
// must be large enough to hold the result.
//
-// The ASCII string can be in one of three formats:
-//
-// Default : yyyy-mm-dd
-// USA : mm/dd/yyyy
-// European: dd.mm.yyyy
-//
// This method is called assuming the correct source format. The source
// string must contain date and, possibly, leading and trailing blanks
// only. The size of destination buffer should be just enough to hold
// internal representation of the date value, i.e. 4 bytes.
//
+// target dstData has the format:
+// Timestamp:
+// dstData[0..1] 2-bytes for year.
+// dstData[2] .. dstData[6] 1-byte for month through second.
+// dstData[7..10] 4-bytes for fraction.
+// Date:
+// dstData[0..1] 2-bytes for year.
+// dstData[2] .. dstData[3] 1-byte for month through day.
+// Time:
+// dstData[0] .. dstData[2] 1-byte for hour through second.
// =====================================================================
//
short
ExpDatetime::convAsciiToDate(char *srcData,
- Lng32 srcLen,
+ Lng32 inSrcLen,
char *dstData,
Lng32 dstLen,
Int32 format,
@@ -2508,6 +2515,7 @@ ExpDatetime::convAsciiToDate(char *srcData,
Lng32 srcFormat, i;
NABoolean LastDayPrevMonth = FALSE;
+ Lng32 srcLen = inSrcLen;
if (*srcData == ' ') {
// skip leading blanks and adjust srcData and srcLen accordingly
//
@@ -3053,7 +3061,7 @@ Lng32 ExpDatetime::getDatetimeFormatLen(Lng32 format, NABoolean to_date,
{
if (to_date)
{
- return ExpDatetime::getAsciiFormatLen(format);
+ return ExpDatetime::getDatetimeFormatLen(format);
}
else
{
@@ -3093,7 +3101,7 @@ Lng32 ExpDatetime::getDatetimeFormatLen(Lng32 format, NABoolean to_date,
break;
default:
- return ExpDatetime::getAsciiFormatLen(format);
+ return ExpDatetime::getDatetimeFormatLen(format);
}
return 0;
@@ -3444,14 +3452,7 @@ ExpDatetime::convDatetimeToASCII(char *srcData,
// if format includes time field but source is a DATE datatype, extend
// the returned string with zeroes
- if ((format == DATETIME_FORMAT_TS1) ||
- (format == DATETIME_FORMAT_TS2) ||
- (format == DATETIME_FORMAT_TS3) ||
- (format == DATETIME_FORMAT_TS5) ||
- (format == DATETIME_FORMAT_TS6) ||
- (format == DATETIME_FORMAT_TS7) ||
- (format == DATETIME_FORMAT_TS8) ||
- (format == DATETIME_FORMAT_TS9))
+ if (isTimestampFormat(format))
{
if (format == DATETIME_FORMAT_TS1)
{
@@ -3564,6 +3565,7 @@ ExpDatetime::convNumericTimeToASCII(char *srcData,
part1 = (Lng32)(temp - (temp/100)*100);
temp = temp/100;
+ // if more digits left in input, error out.
if (temp > 0)
{
ExRaiseSqlError(heap, diagsArea, EXE_CONVERT_DATETIME_ERROR);
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5fc9ce5f/core/sql/exp/exp_datetime.h
----------------------------------------------------------------------
diff --git a/core/sql/exp/exp_datetime.h b/core/sql/exp/exp_datetime.h
index b6dd799..ba110d0 100644
--- a/core/sql/exp/exp_datetime.h
+++ b/core/sql/exp/exp_datetime.h
@@ -49,7 +49,9 @@
class SQLEXP_LIB_FUNC ExpDatetime : public SimpleType {
public:
- enum asciiFormats
+ // these enums must be in the same order as the datetimeFormat[] array
+ // (defined in exp_datetime.cpp).
+ enum DatetimeFormats
{
DATETIME_FORMAT_MIN = 0,
DATETIME_FORMAT_MIN_DATE = DATETIME_FORMAT_MIN,
@@ -99,9 +101,9 @@ public:
struct DatetimeFormatInfo
{
- Lng32 format;
- const char * str;
- Lng32 minLen;
+ Lng32 format; // defined by enum DatetimeFormats
+ const char * str; // string representation of datetime format
+ Lng32 minLen; // minimum length to hold this format
Lng32 maxLen;
};
@@ -317,12 +319,12 @@ static
NA_EIDPROC virtual short getClassSize() { return (short)sizeof(*this); }
// ---------------------------------------------------------------------
- static const char * getFormatStr(Lng32 frmt)
+ static const char * getDatetimeFormatStr(Lng32 frmt)
{
return datetimeFormat[frmt].str;
}
- static const Lng32 getFormat(const char * formatStr)
+ static const Lng32 getDatetimeFormat(const char * formatStr)
{
for (Lng32 i = DATETIME_FORMAT_MIN; i <= DATETIME_FORMAT_MAX; i++)
{
@@ -361,7 +363,7 @@ static
(frmt <= DATETIME_FORMAT_MAX_DATE));
}
- static NABoolean isTSFormat(Lng32 frmt)
+ static NABoolean isTimestampFormat(Lng32 frmt)
{
return ((frmt >= DATETIME_FORMAT_MIN_TS) &&
(frmt <= DATETIME_FORMAT_MAX_TS));
@@ -378,12 +380,12 @@ static
return ((frmt == DATETIME_FORMAT_NUM1) || (frmt == DATETIME_FORMAT_NUM2));
}
- static Lng32 getAsciiFormatLen(Lng32 frmt)
+ static Lng32 getDatetimeFormatLen(Lng32 frmt)
{
return datetimeFormat[frmt].minLen;
}
- static Lng32 getAsciiFormatMaxLen(Lng32 frmt)
+ static Lng32 getDatetimeFormatMaxLen(Lng32 frmt)
{
return datetimeFormat[frmt].maxLen;
}
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5fc9ce5f/core/sql/exp/exp_function.cpp
----------------------------------------------------------------------
diff --git a/core/sql/exp/exp_function.cpp b/core/sql/exp/exp_function.cpp
index df1f807..8d12e4c 100644
--- a/core/sql/exp/exp_function.cpp
+++ b/core/sql/exp/exp_function.cpp
@@ -2562,9 +2562,11 @@ ex_expr::exp_return_type ex_function_dateformat::eval(char *op_data[],
if ((DFS2REC::isAnyCharacter(getOperand(1)->getDatatype())) &&
(DFS2REC::isDateTime(getOperand(0)->getDatatype())))
{
+ Lng32 sourceLen = getOperand(1)->getLength(op_data[-MAX_OPERANDS+1]);
+
ExpDatetime *datetimeOpType = (ExpDatetime *) getOperand(0);
if(datetimeOpType->convAsciiToDate(opData,
- getOperand(1)->getLength(),
+ sourceLen,
result,
getOperand(0)->getLength(),
getDateFormat(),
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5fc9ce5f/core/sql/optimizer/BindItemExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/BindItemExpr.cpp b/core/sql/optimizer/BindItemExpr.cpp
index 6e85e69..ba7595f 100644
--- a/core/sql/optimizer/BindItemExpr.cpp
+++ b/core/sql/optimizer/BindItemExpr.cpp
@@ -3994,11 +3994,9 @@ ItemExpr * Format::bindNode(BindWA * bindWA)
return newIE;
}
-//
-// <format>: date(year to day), time(hour to sec), timestamp(year to sec)
-//
-// TO_DATE(<char>, <format>)
-//
+///////////////////////////////////////////////////////
+// various error checks, details below.
+//////////////////////////////////////////////////////
NABoolean DateFormat::errorChecks(Lng32 frmt, BindWA *bindWA,
const NAType* opType)
{
@@ -4008,35 +4006,51 @@ NABoolean DateFormat::errorChecks(Lng32 frmt, BindWA *bindWA,
NABoolean td = (formatType_ == FORMAT_TO_DATE);
NABoolean df = ExpDatetime::isDateFormat(frmt);
NABoolean tf = ExpDatetime::isTimeFormat(frmt);
- NABoolean tsf = ExpDatetime::isTSFormat(frmt);
+ NABoolean tsf = ExpDatetime::isTimestampFormat(frmt);
NABoolean nf = ExpDatetime::isNumericFormat(frmt);
NABoolean ms4 = (CmpCommon::getDefault(MODE_SPECIAL_4) == DF_ON);
if (NOT (df || tf || tsf || nf))
- error = 1; // error 4065
+ {
+ // format must be date, time, timestamp or numeric
+ error = 1; // error 4065
+ }
else if ((NOT ms4) && nf)
- error = 1; // error 4065
+ {
+ // format can only be numeric in mode_special_4
+ error = 1; // error 4065
+ }
+
if (!error && tc)
{
+ // source must be datetime with to_char function
if (opType->getTypeQualifier() != NA_DATETIME_TYPE)
error = 2; // error 4071
+
+ // cannot convert date source to time format
else if (tf && (opType->getPrecision() == SQLDTCODE_DATE))
error = 3; // error 4072
}
+
if (!error && td)
{
+ // source must be char or numeric with to_date
if ((opType->getTypeQualifier() != NA_CHARACTER_TYPE) &&
(opType->getTypeQualifier() != NA_NUMERIC_TYPE))
error = 4; //error 4043
+
+ // source can only be numeric in mode_special_4
else if ((NOT ms4) && (opType->getTypeQualifier() != NA_CHARACTER_TYPE))
error = 4; // error 4043
- }
- if (!error && ms4 && nf)
- {
- if ((opType->getTypeQualifier() != NA_NUMERIC_TYPE) ||
- (NOT ((NumericType*)opType)->isExact()) ||
- (NOT((NumericType*)opType)->getScale() == 0))
- error = 5; // error 4045
+
+ // numeric must be exact with scale of 0
+ else if (ms4 && (opType->getTypeQualifier() == NA_NUMERIC_TYPE))
+ {
+ if (NOT ((NumericType*)opType)->isExact())
+ error = 5; // error 4046
+ else if (NOT ((NumericType*)opType)->getScale() == 0)
+ error = 6; // error 4047
+ }
}
if (error)
@@ -4075,18 +4089,26 @@ NABoolean DateFormat::errorChecks(Lng32 frmt, BindWA *bindWA,
case 5:
{
- *CmpCommon::diags() << DgSqlCode(-4045) << DgString0("FORMAT");
+ *CmpCommon::diags() << DgSqlCode(-4046) << DgString0("TO_DATE");
+ bindWA->setErrStatus();
+ }
+ break;
+
+ case 6:
+ {
+ *CmpCommon::diags() << DgSqlCode(-4047) << DgString0("TO_DATE");
bindWA->setErrStatus();
}
break;
} // switch
-
+
return TRUE;
}
-
+
return FALSE;
}
+// used for TO_DATE, TO_CHAR, TO_TIME, DATEFORMAT functions.
DateFormat::DateFormat(ItemExpr *val1Ptr, const NAString &formatStr,
Lng32 formatType, NABoolean wasDateformat)
: CacheableBuiltinFunction(ITM_DATEFORMAT,
@@ -4104,7 +4126,7 @@ DateFormat::DateFormat(ItemExpr *val1Ptr, const NAString &formatStr,
else if (formatStr_ == "HH:MI:SS")
formatStr_ = "HH24:MI:SS";
- frmt_ = ExpDatetime::getFormat(formatStr_.data());
+ frmt_ = ExpDatetime::getDatetimeFormat(formatStr_.data());
}
ItemExpr * DateFormat::bindNode(BindWA * bindWA)
@@ -4133,7 +4155,7 @@ ItemExpr * DateFormat::bindNode(BindWA * bindWA)
if (ExpDatetime::isDateTimeFormat(frmt_))
{
- if (ExpDatetime::isTSFormat(frmt_))
+ if (ExpDatetime::isTimestampFormat(frmt_))
dateFormat_ = DateFormat::TIMESTAMP_FORMAT_STR;
else if (ExpDatetime::isTimeFormat(frmt_))
dateFormat_ = DateFormat::TIME_FORMAT_STR;
@@ -4178,6 +4200,8 @@ ItemExpr * DateFormat::bindNode(BindWA * bindWA)
CMPASSERT(FALSE); // should not reach here
}
+ // if source is a timestamp and target is date or time, extract
+ // date or time part from source before formatting.
ItemExpr * newChild = NULL;
if (naType0->getPrecision() == SQLDTCODE_TIMESTAMP)
{
@@ -4203,19 +4227,6 @@ ItemExpr * DateFormat::bindNode(BindWA * bindWA)
if (newChild)
setChild(0, newChild->bindNode(bindWA));
-
- naType0 = &child(0)->getValueId().getType();
- if (DFS2REC::isAnyVarChar(naType0->getFSDatatype()))
- {
- // convert to fixed char.
- newChild =
- new (bindWA->wHeap())
- Cast(child(0),
- new (bindWA->wHeap())
- SQLChar(naType0->getNominalSize(),
- naType0->supportsSQLnull()));
- setChild(0, newChild->bindNode(bindWA));
- }
}
BuiltinFunction::bindNode(bindWA);
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5fc9ce5f/core/sql/optimizer/SynthType.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/SynthType.cpp b/core/sql/optimizer/SynthType.cpp
index 24507f3..dbb2459 100644
--- a/core/sql/optimizer/SynthType.cpp
+++ b/core/sql/optimizer/SynthType.cpp
@@ -2828,8 +2828,8 @@ const NAType *DateFormat::synthesizeType()
if (vid.getType().getTypeQualifier() == NA_DATETIME_TYPE)
{
- Lng32 frmt = ExpDatetime::getFormat(formatStr_.data());
- length = ExpDatetime::getAsciiFormatMaxLen(frmt);
+ Lng32 frmt = ExpDatetime::getDatetimeFormat(formatStr_.data());
+ length = ExpDatetime::getDatetimeFormatMaxLen(frmt);
}
else if (vid.getType().getTypeQualifier() == NA_CHARACTER_TYPE)
{
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5fc9ce5f/core/sql/parser/sqlparser.y
----------------------------------------------------------------------
diff --git a/core/sql/parser/sqlparser.y b/core/sql/parser/sqlparser.y
index 72bae4c..6206f68 100755
--- a/core/sql/parser/sqlparser.y
+++ b/core/sql/parser/sqlparser.y
@@ -3218,7 +3218,6 @@ literal : numeric_literal
$$ = new (PARSERHEAP()) ConstValue
(*$1, getStringCharSet(&$1));
SqlParser_CurrentParser->collectItem4HQC($$);
- // $$ = new (PARSERHEAP()) Format($$, *$6, TRUE);
$$ = new (PARSERHEAP()) DateFormat($$, *$6, DateFormat::FORMAT_TO_DATE);
}
else
@@ -3227,7 +3226,6 @@ literal : numeric_literal
if (! $$) YYERROR;
SqlParser_CurrentParser->collectItem4HQC($$);
restoreInferCharsetState();
- // $$ = new (PARSERHEAP()) Format($$, *$6, FALSE);
$$ = new (PARSERHEAP()) DateFormat($$, *$6, DateFormat::FORMAT_TO_CHAR);
}
@@ -7915,7 +7913,6 @@ primary : '(' value_expression ')'
| dml_column_reference TOK_LPAREN_BEFORE_DATE_COMMA_AND_FORMAT TOK_DATE ',' TOK_FORMAT character_string_literal ')'
{
// DEFAULT_CHARSET has no effect on character_string_literal in this context
- // $$ = new (PARSERHEAP()) Format($1, *$6, FALSE);
$$ = new (PARSERHEAP()) DateFormat($1, *$6, DateFormat::FORMAT_TO_CHAR);
}
@@ -8596,7 +8593,7 @@ datetime_misc_function : TOK_CONVERTTIMESTAMP '(' value_expression ')'
}
| TOK_TO_NUMBER '(' value_expression ')'
{
- // CheckModeSpecial3;
+ CheckModeSpecial3;
$$ = new (PARSERHEAP())
ZZZBinderFunction(ITM_TO_NUMBER, $3);
@@ -8608,7 +8605,7 @@ datetime_misc_function : TOK_CONVERTTIMESTAMP '(' value_expression ')'
}
| TOK_TO_TIMESTAMP '(' value_expression ')'
{
- // CheckModeSpecial4;
+ CheckModeSpecial4;
$$ = new (PARSERHEAP())
ZZZBinderFunction(ITM_TO_TIMESTAMP, $3);
@@ -10316,15 +10313,15 @@ trim_spec : TOK_LEADING { $$ = (Int32) Trim::LEADING; }
/* type stringval */
date_format : TOK_DEFAULT
{
- $$ = new (PARSERHEAP()) NAString(ExpDatetime::getFormatStr(ExpDatetime::DATETIME_FORMAT_DEFAULT));
+ $$ = new (PARSERHEAP()) NAString(ExpDatetime::getDatetimeFormatStr(ExpDatetime::DATETIME_FORMAT_DEFAULT));
}
| TOK_EUROPEAN
{
- $$ = new (PARSERHEAP()) NAString(ExpDatetime::getFormatStr(ExpDatetime::DATETIME_FORMAT_EUROPEAN));
+ $$ = new (PARSERHEAP()) NAString(ExpDatetime::getDatetimeFormatStr(ExpDatetime::DATETIME_FORMAT_EUROPEAN));
}
| TOK_USA
{
- $$ = new (PARSERHEAP()) NAString(ExpDatetime::getFormatStr(ExpDatetime::DATETIME_FORMAT_USA));
+ $$ = new (PARSERHEAP()) NAString(ExpDatetime::getDatetimeFormatStr(ExpDatetime::DATETIME_FORMAT_USA));
}
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5fc9ce5f/core/sql/regress/seabase/EXPECTED030
----------------------------------------------------------------------
diff --git a/core/sql/regress/seabase/EXPECTED030 b/core/sql/regress/seabase/EXPECTED030
index ee6d5b5..b065ee3 100644
--- a/core/sql/regress/seabase/EXPECTED030
+++ b/core/sql/regress/seabase/EXPECTED030
@@ -1,14 +1,4 @@
>>
->>drop table if exists t030t1;
-
---- SQL operation complete.
->>create table t030t1 (a date);
-
---- SQL operation complete.
->>insert into t030t1 values (date '2016-03-01');
-
---- 1 row(s) inserted.
->>
>>select to_date('2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a);
(EXPR)
@@ -403,6 +393,33 @@ March 01, 2016, 10:11:12
--- 1 row(s) selected.
>>
+>>select to_char(TIMESTAMP '2016-03-01 10:11:12', 'YYYY-MM-DD') from (values(1)) x(a);
+
+(EXPR)
+----------
+
+2016-03-01
+
+--- 1 row(s) selected.
+>>select to_char(TIMESTAMP '2016-03-01 10:11:12', 'HH:MI:SS') from (values(1)) x(a);
+
+(EXPR)
+--------
+
+10:11:12
+
+--- 1 row(s) selected.
+>>
+>>drop table if exists t030t1;
+
+--- SQL operation complete.
+>>create table t030t1 (a date, b char(30), c varchar(30));
+
+--- SQL operation complete.
+>>insert into t030t1 values (date '2016-03-01', '2016-03-01', '2016-03-01');
+
+--- 1 row(s) inserted.
+>>
>>select to_char(a, 'YYYYMMDD') from t030t1;
(EXPR)
@@ -419,6 +436,22 @@ March 01, 2016, 10:11:12
20160301
--- 1 row(s) selected.
+>>select to_date(b, 'YYYY-MM-DD') from t030t1;
+
+(EXPR)
+----------
+
+2016-03-01
+
+--- 1 row(s) selected.
+>>select to_date(c, 'YYYY-MM-DD') from t030t1;
+
+(EXPR)
+----------
+
+2016-03-01
+
+--- 1 row(s) selected.
>>
>>-- negative tests
>>select to_date('2016-03-01', 'YYYYMM-DD') from (values(1)) x(a);
@@ -450,6 +483,12 @@ March 01, 2016, 10:11:12
*** ERROR[8822] The statement was not prepared.
+>>select to_char(date '2016-03-01', 'HH:MI:SS') from (values(1)) x(a);
+
+*** ERROR[4072] The operand of function TO_CHAR must be a datetime containing a time.
+
+*** ERROR[8822] The statement was not prepared.
+
>>
>>-- some formats only enabled in special mode. Not externalized.
>>cqd mode_special_4 'ON';
@@ -479,7 +518,8 @@ March 01, 2016, 10:11:12
-12:34:56:78
--- 1 row(s) selected.
->>-- error case
+>>
+>>-- ms4 error cases
>>select to_date(123456789, '99:99:99:99') from (values(1)) x(a);
*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted.
@@ -494,5 +534,17 @@ March 01, 2016, 10:11:12
*** ERROR[8001] An internal executor error occurred.
--- 0 row(s) selected.
+>>select to_date(1e0, '99:99:99:99') from (values(1)) x(a);
+
+*** ERROR[4046] The operands of function TO_DATE must be exact numeric.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>select to_date(1.2, '99:99:99:99') from (values(1)) x(a);
+
+*** ERROR[4047] The operands of function TO_DATE must have a scale of 0.
+
+*** ERROR[8822] The statement was not prepared.
+
>>
>>log;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5fc9ce5f/core/sql/regress/seabase/TEST030
----------------------------------------------------------------------
diff --git a/core/sql/regress/seabase/TEST030 b/core/sql/regress/seabase/TEST030
index ba28d9b..e75d96f 100644
--- a/core/sql/regress/seabase/TEST030
+++ b/core/sql/regress/seabase/TEST030
@@ -24,10 +24,6 @@
log LOG030 clear;
-drop table if exists t030t1;
-create table t030t1 (a date);
-insert into t030t1 values (date '2016-03-01');
-
select to_date('2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a);
select to_date('03/01/2016', 'MM/DD/YYYY') from (values(1)) x(a);
select to_date('01.03.2016', 'DD.MM.YYYY') from (values(1)) x(a);
@@ -79,8 +75,17 @@ select to_char(TIMESTAMP '2016-03-01 10:11:12', 'MONTH DD, YYYY, HH:MI') from (v
select to_char (time '10:23:34', 'HH24:MI:SS') from (values(1)) x(a);
select to_char (time '10:23:34', 'HH:MI:SS') from (values(1)) x(a);
+select to_char(TIMESTAMP '2016-03-01 10:11:12', 'YYYY-MM-DD') from (values(1)) x(a);
+select to_char(TIMESTAMP '2016-03-01 10:11:12', 'HH:MI:SS') from (values(1)) x(a);
+
+drop table if exists t030t1;
+create table t030t1 (a date, b char(30), c varchar(30));
+insert into t030t1 values (date '2016-03-01', '2016-03-01', '2016-03-01');
+
select to_char(a, 'YYYYMMDD') from t030t1;
select a (date, format 'YYYYMMDD') from t030t1;
+select to_date(b, 'YYYY-MM-DD') from t030t1;
+select to_date(c, 'YYYY-MM-DD') from t030t1;
-- negative tests
select to_date('2016-03-01', 'YYYYMM-DD') from (values(1)) x(a);
@@ -88,14 +93,18 @@ select to_date(DATE '2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a);
select to_date('0103.2016', 'DD.MM.YYYY') from (values(1)) x(a);
select to_char('2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a);
select to_date(20160301, 'YYYYMMDD') from (values(1)) x(a);
+select to_char(date '2016-03-01', 'HH:MI:SS') from (values(1)) x(a);
-- some formats only enabled in special mode. Not externalized.
cqd mode_special_4 'ON';
select to_date(20160301, 'YYYYMMDD') from (values(1)) x(a);
select to_date(12345678, '99:99:99:99') from (values(1)) x(a);
select to_date(-12345678, '-99:99:99:99') from (values(1)) x(a);
--- error case
+
+-- ms4 error cases
select to_date(123456789, '99:99:99:99') from (values(1)) x(a);
select to_date(-12345678, '99:99:99:99') from (values(1)) x(a);
+select to_date(1e0, '99:99:99:99') from (values(1)) x(a);
+select to_date(1.2, '99:99:99:99') from (values(1)) x(a);
log;