Repository: trafodion Updated Branches: refs/heads/master d5b166852 -> 802e4fe71
trafodion-3118 Improve on parts of EXTRACT Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/b90abceb Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/b90abceb Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/b90abceb Branch: refs/heads/master Commit: b90abcebc664aa27968c9378c91b416abc71eea4 Parents: dcbc9f2 Author: kakaxi3019 <[email protected]> Authored: Mon Jun 25 17:09:16 2018 +0800 Committer: kakaxi3019 <[email protected]> Committed: Mon Jun 25 17:09:16 2018 +0800 ---------------------------------------------------------------------- core/sql/bin/SqlciErrors.txt | 1 + core/sql/common/DTICommonType.cpp | 16 ++ core/sql/common/dfs2rec.h | 9 +- core/sql/exp/ExpPCodeClauseGen.cpp | 1 + core/sql/exp/exp_function.cpp | 236 ++++++++++++++++++++++++++++++ core/sql/exp/exp_function.h | 4 +- core/sql/optimizer/SynthType.cpp | 55 +++++-- core/sql/parser/ParKeyWords.cpp | 6 + core/sql/parser/sqlparser.y | 38 +++++ core/sql/regress/seabase/EXPECTED030 | 38 +++++ core/sql/regress/seabase/TEST030 | 5 + 11 files changed, 397 insertions(+), 12 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/trafodion/blob/b90abceb/core/sql/bin/SqlciErrors.txt ---------------------------------------------------------------------- diff --git a/core/sql/bin/SqlciErrors.txt b/core/sql/bin/SqlciErrors.txt index 7c2ae4e..d860ee0 100644 --- a/core/sql/bin/SqlciErrors.txt +++ b/core/sql/bin/SqlciErrors.txt @@ -1449,6 +1449,7 @@ $1~String1 -------------------------------- 4493 ZZZZZ 99999 BEGINNER MINOR LOGONLY Stored Descriptor Status: $0~String0 4494 ZZZZZ 99999 BEGINNER MINOR LOGONLY LOAD is not supported on a table with LOB columns. Table $0~TableName has LOB column $1~ColumnName. 4495 ZZZZZ 99999 BEGINNER MINOR LOGONLY UNLOAD is not supported on a SELECT with LOB columns. $0~ColumnName is a LOB column. +4496 42000 99999 BEGINNER MAJOR DBADMIN $0~String0 is supported only with datetime fields. 5000 ZZZZZ 99999 ADVANCED MAJOR DBADMIN Internal error in the query normalizer. 5001 ZZZZZ 99999 ADVANDED MINOR LOGONLY Common subexpression $0~String0 will not be shared among multiple consumers. Reason: $1~String1. 6000 ZZZZZ 99999 ADVANCED MAJOR DBADMIN Internal error in the query optimizer. http://git-wip-us.apache.org/repos/asf/trafodion/blob/b90abceb/core/sql/common/DTICommonType.cpp ---------------------------------------------------------------------- diff --git a/core/sql/common/DTICommonType.cpp b/core/sql/common/DTICommonType.cpp index e8c11df..44fe2da 100644 --- a/core/sql/common/DTICommonType.cpp +++ b/core/sql/common/DTICommonType.cpp @@ -73,6 +73,22 @@ const char* DatetimeIntervalCommonType::getFieldName(rec_datetime_field field) return "YEARMONTHD"; case REC_DATE_YEARWEEK_D_EXTRACT: return "YEARWEEKD"; + case REC_DATE_CENTURY: + return "CENTURY"; + case REC_DATE_DECADE: + return "DECADE"; + case REC_DATE_WEEK: + return "WEEK"; + case REC_DATE_QUARTER: + return "QUARTER"; + case REC_DATE_EPOCH: + return "EPOCH"; + case REC_DATE_DOW: + return "DOW"; + case REC_DATE_DOY: + return "DOY"; + case REC_DATE_WOM: + return "WOM"; default: return NULL; } http://git-wip-us.apache.org/repos/asf/trafodion/blob/b90abceb/core/sql/common/dfs2rec.h ---------------------------------------------------------------------- diff --git a/core/sql/common/dfs2rec.h b/core/sql/common/dfs2rec.h index 910cda3..b87c960 100644 --- a/core/sql/common/dfs2rec.h +++ b/core/sql/common/dfs2rec.h @@ -231,7 +231,14 @@ enum rec_datetime_field { , REC_DATE_MINUTE , REC_DATE_SECOND , REC_DATE_FRACTION_MP // Used in MP only! - +, REC_DATE_CENTURY +, REC_DATE_DECADE +, REC_DATE_WEEK +, REC_DATE_QUARTER +, REC_DATE_EPOCH +, REC_DATE_DOW +, REC_DATE_DOY +, REC_DATE_WOM , REC_DATE_MAX_SINGLE_FIELD // other datetime fields, not used in FS2 and DDL , REC_DATE_YEARQUARTER_EXTRACT = 1000 // Used for EXTRACT (DATE_PART) function only! http://git-wip-us.apache.org/repos/asf/trafodion/blob/b90abceb/core/sql/exp/ExpPCodeClauseGen.cpp ---------------------------------------------------------------------- diff --git a/core/sql/exp/ExpPCodeClauseGen.cpp b/core/sql/exp/ExpPCodeClauseGen.cpp index 04d88e0..2d07a84 100644 --- a/core/sql/exp/ExpPCodeClauseGen.cpp +++ b/core/sql/exp/ExpPCodeClauseGen.cpp @@ -4797,6 +4797,7 @@ ex_function_extract::pCodeGenerate(Space *space, UInt32 f) // fractional precision - this requires slightly more work. if ((attrs[1]->getDatatype() != REC_DATETIME) || (getExtractField() > REC_DATE_MAX_SINGLE_FIELD) || + (getExtractField()>=REC_DATE_CENTURY && getExtractField()<=REC_DATE_WOM) || ((getExtractField() == REC_DATE_SECOND) && (attrs[1]->getScale() > 0))) return ex_clause::pCodeGenerate(space, f); http://git-wip-us.apache.org/repos/asf/trafodion/blob/b90abceb/core/sql/exp/exp_function.cpp ---------------------------------------------------------------------- diff --git a/core/sql/exp/exp_function.cpp b/core/sql/exp/exp_function.cpp index 4a936cb..a5afbc8 100644 --- a/core/sql/exp/exp_function.cpp +++ b/core/sql/exp/exp_function.cpp @@ -69,6 +69,7 @@ #include "ComDiags.h" #include "ComAnsiNamePart.h" #include "ComSqlId.h" +#include "ComCextdecs.h" #include "ex_globals.h" #include "NAUserId.h" @@ -3001,6 +3002,225 @@ ex_expr::exp_return_type ex_function_dayofweek::eval(char *op_data[], return ex_expr::EXPR_OK; } +static Int64 lcl_dayofweek(Int64 totaldays) +{ + return (unsigned short)((totaldays + 1) % 7) + 1; +} + +static int lcl_date2Julian(int y, int m ,int d) +{ + int myjulian = 0; + int mycentury = 0; + if ( m <= 2) + { + m = m+13; + y = y+4799; + } + else + { + m = m+1; + y = y+4800; + } + + myjulian = y / 100; + myjulian = y * 365 - 32167; + myjulian += y/4 - mycentury + mycentury / 4; + myjulian += 7834 * m / 256 + d; + return myjulian; +} + +static Int64 lcl_dayofyear(char year, char month, char day) +{ + return (lcl_date2Julian(year,month,day)-lcl_date2Julian(year,1,1)+1); +} + +#define DAYS_PER_YEAR 365.25 /*consider leap year every four years*/ +#define MONTHS_PER_YEAR 12 +#define DAYS_PER_MONTH 30 +#define HOURS_PER_DAY 24 +#define SECONDS_PER_MINUTE 60 +#define SECONDS_PER_HOUR 3600 +#define SECONDS_PER_DAY 86400 + +static Int64 lcl_interval(rec_datetime_field eField, Lng32 eCode, char *opdata, UInt32 nLength) +{ + if (!opdata) + return 0; + if ( REC_DATE_DECADE == eField && REC_INT_YEAR == eCode ) + { + short nValue; + str_cpy_all((char *) &nValue, opdata, sizeof(nValue)); + return nValue / 10; + } + if ( REC_DATE_QUARTER == eField && REC_INT_MONTH == eCode ) + { + short nValue; + str_cpy_all((char *) &nValue, opdata, sizeof(nValue)); + if( nValue<=0 ) + return 0; + return (nValue-1)/3+1; + } + if ( REC_DATE_EPOCH == eField ) + { + size_t n = 0; + if ( SQL_SMALL_SIZE==nLength ) + n = sizeof(short); + else if ( SQL_INT_SIZE==nLength ) + n = sizeof(Lng32); + else if ( SQL_LARGE_SIZE==nLength ) + n = sizeof(Int64); + + Int64 nVal = 0; + str_cpy_all((char *) &nVal, opdata, n); + + if ( REC_INT_YEAR==eCode ) + return nVal*DAYS_PER_YEAR*SECONDS_PER_DAY; + else if ( REC_INT_MONTH==eCode + || REC_INT_YEAR_MONTH==eCode) + { + double result = (double)(nVal/MONTHS_PER_YEAR) * DAYS_PER_YEAR * SECONDS_PER_DAY; + result += (double)(nVal%MONTHS_PER_YEAR) * DAYS_PER_MONTH * SECONDS_PER_DAY; + return Int64(result); + } + else if ( REC_INT_DAY==eCode ) + return nVal*SECONDS_PER_DAY; + else if ( REC_INT_HOUR==eCode + || REC_INT_DAY_HOUR==eCode ) + return nVal*SECONDS_PER_HOUR; + else if ( REC_INT_MINUTE==eCode + || REC_INT_HOUR_MINUTE==eCode + || REC_INT_DAY_MINUTE==eCode) + return nVal*SECONDS_PER_MINUTE; + else if ( REC_INT_SECOND==eCode + || REC_INT_MINUTE_SECOND==eCode + || REC_INT_HOUR_SECOND==eCode + || REC_INT_DAY_SECOND==eCode ) + return nVal; + } + return 0; +} + +Int64 ex_function_extract::getExtraTimeValue(rec_datetime_field eField, Lng32 eCode, char *dateTime) +{ + short year; + char month; + char day; + char hour = 0; + char minute = 0; + char second = 0; + char millisencond = 0; + if (eField < REC_DATE_CENTURY || eField > REC_DATE_WOM) + return 0; + if (eCode != REC_DTCODE_DATE && eCode != REC_DTCODE_TIMESTAMP) + return 0; + + ExpDatetime *datetimeOpType = (ExpDatetime *) getOperand(1); + if (!datetimeOpType) + return 0; + + rec_datetime_field eEndFiled = REC_DATE_DAY; + if ( REC_DTCODE_TIMESTAMP == eCode ) + eEndFiled = REC_DATE_SECOND; + size_t n = strlen(dateTime); + for (Int32 field = REC_DATE_YEAR; field <= eEndFiled; field++) + { + switch (field) + { + case REC_DATE_YEAR: + { + str_cpy_all((char *) &year, dateTime, sizeof(year)); + dateTime += sizeof(year); + } + break; + case REC_DATE_MONTH: + { + month = *dateTime++; + } + break; + case REC_DATE_DAY: + { + day = *dateTime; + if ( REC_DATE_SECOND == eEndFiled ) + dateTime++; + } + break; + case REC_DATE_HOUR: + { + hour = *dateTime++; + } + break; + case REC_DATE_MINUTE: + { + minute = *dateTime++; + } + break; + case REC_DATE_SECOND: + { + second = *dateTime; + if (n>7)// 2018-06-20 20:30:15.12 length = 8 + { + dateTime++; + millisencond = *dateTime; + } + } + break; + } + } + switch (eField) + { + case REC_DATE_DOW: + {//same with built-in function dayofweek ex_function_dayofweek::eval + Int64 interval = datetimeOpType->getTotalDays(year, month, day); + return lcl_dayofweek(interval); + } + case REC_DATE_DOY: + { + return lcl_dayofyear(year,month,day); + } + case REC_DATE_WOM: + { + /*Int64 interval = datetimeOpType->getTotalDays(year, month, 1); + Int64 firstdayinmonthofweek = lcl_dayofweek(interval); + int exceptday = 0; + if (firstdayinmonthofweek != 2) + exceptday = 7 - firstdayinmonthofweek + 2; + + return (day - exceptday)/7 + (day < exceptday ? 0 : 1);*/ + return ((day-1)/7+1); + } + case REC_DATE_CENTURY: + { + return (year+99)/100; + } + case REC_DATE_DECADE: + { + return year/10; + } + case REC_DATE_WEEK: + {//same with built-in function week ITM_WEEK + Int64 interval = datetimeOpType->getTotalDays(year, 1, 1); + Int64 dayofweek = lcl_dayofweek(interval); + Int64 dayofyear = lcl_dayofyear(year,month,day); + return (dayofyear-1+dayofweek-1)/7+1; + } + case REC_DATE_QUARTER: + { + return (month-1)/3+1; + } + case REC_DATE_EPOCH: + { + Int64 ndays = datetimeOpType->getTotalDays(year, month, day); + Int64 nJuliandays = datetimeOpType->getTotalDays(1970, 1, 1); + ndays = ndays - nJuliandays; + Int64 ntimestamp = ndays*86400+hour*3600+minute*60+second; + if ( 0!=millisencond ) + ntimestamp = ntimestamp*100+millisencond; + return ntimestamp; + } + } + return 0; +} + ex_expr::exp_return_type ex_function_extract::eval(char *op_data[], CollHeap *heap, ComDiagsArea** diagsArea) @@ -3014,6 +3234,13 @@ ex_expr::exp_return_type ex_function_extract::eval(char *op_data[], rec_datetime_field extractStartField = getExtractField(); rec_datetime_field extractEndField = extractStartField; + if ( extractStartField >=REC_DATE_CENTURY && extractStartField<=REC_DATE_WOM ) + { + result = getExtraTimeValue(extractStartField, datetimeOpType->getPrecision(), datetimeOpData); + copyInteger (op_data[0], getOperand(0)->getLength(), &result, sizeof(result)); + return ex_expr::EXPR_OK; + } + if (extractStartField > REC_DATE_MAX_SINGLE_FIELD) { extractStartField = REC_DATE_YEAR; if (extractEndField == REC_DATE_YEARQUARTER_EXTRACT || @@ -3103,6 +3330,15 @@ ex_expr::exp_return_type ex_function_extract::eval(char *op_data[], } } } else { + if (getExtractField() == REC_DATE_DECADE + || getExtractField() == REC_DATE_QUARTER + || getExtractField() == REC_DATE_EPOCH) + { + ExpDatetime *datetimeOpType = (ExpDatetime *) getOperand(1); + result = lcl_interval(getExtractField(),getOperand(1)->getDatatype(),op_data[1],getOperand(1)->getLength()); + copyInteger (op_data[0], getOperand(0)->getLength(), &result, sizeof(result)); + return ex_expr::EXPR_OK; + } Int64 interval; switch (getOperand(1)->getLength()) { case SQL_SMALL_SIZE: { http://git-wip-us.apache.org/repos/asf/trafodion/blob/b90abceb/core/sql/exp/exp_function.h ---------------------------------------------------------------------- diff --git a/core/sql/exp/exp_function.h b/core/sql/exp/exp_function.h index 3f6bd0c..c107606 100644 --- a/core/sql/exp/exp_function.h +++ b/core/sql/exp/exp_function.h @@ -2804,7 +2804,9 @@ public: ex_expr::exp_return_type pCodeGenerate(Space *space, UInt32 f); ex_expr::exp_return_type eval(char *op_data[], CollHeap*, - ComDiagsArea** = 0); + ComDiagsArea** = 0); + + Int64 getExtraTimeValue(rec_datetime_field eField, Lng32 eCode, char *dateTime); Long pack(void *); // --------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/trafodion/blob/b90abceb/core/sql/optimizer/SynthType.cpp ---------------------------------------------------------------------- diff --git a/core/sql/optimizer/SynthType.cpp b/core/sql/optimizer/SynthType.cpp index f993aa6..c678aa8 100644 --- a/core/sql/optimizer/SynthType.cpp +++ b/core/sql/optimizer/SynthType.cpp @@ -4416,6 +4416,22 @@ const NAType *Extract::synthesizeType() return NULL; } + if ( type != NA_DATETIME_TYPE ) + { + enum rec_datetime_field eField = getExtractField(); + NAString sErr; + if ( REC_DATE_WEEK == eField + || REC_DATE_DOW == eField + || REC_DATE_DOY == eField + || REC_DATE_WOM == eField + || REC_DATE_CENTURY == eField) + sErr = dti.getFieldName(eField); + if (sErr.length() > 0) + { + *CmpCommon::diags() << DgSqlCode(-4496) << DgString0(sErr); + return NULL; + } + } // ANSI 6.6 SR 3a. enum rec_datetime_field extractStartField = getExtractField(); enum rec_datetime_field extractEndField = extractStartField; @@ -4439,16 +4455,19 @@ const NAType *Extract::synthesizeType() } } - if (dti.getStartField() > extractStartField || - dti.getEndField() < extractEndField || - !dti.isSupportedType()) { - // 4037 cannot extract field from type - *CmpCommon::diags() << DgSqlCode(-4037) - << DgString0(dti.getFieldName(getExtractField())) - << DgString1(dti.getTypeSQLname(TRUE /*terse*/)); + if ( !(extractStartField >=REC_DATE_CENTURY && extractStartField<=REC_DATE_WOM) ) + { + if (dti.getStartField() > extractStartField || + dti.getEndField() < extractEndField || + !dti.isSupportedType()) { + // 4037 cannot extract field from type + *CmpCommon::diags() << DgSqlCode(-4037) + << DgString0(dti.getFieldName(getExtractField())) + << DgString1(dti.getTypeSQLname(TRUE /*terse*/)); - return NULL; - } + return NULL; + } + } // ANSI 6.6 SR 4. Precision is implementation-defined: // EXTRACT(YEAR from datetime): result precision is 4 + scale // EXTRACT(other from datetime): result precision is 2 + scale @@ -4470,15 +4489,31 @@ const NAType *Extract::synthesizeType() else if (getExtractField() == REC_DATE_YEARWEEK_EXTRACT || getExtractField() == REC_DATE_YEARWEEK_D_EXTRACT) prec = 6; // YEARMWEEK is yyyyww + else if (getExtractField() == REC_DATE_DECADE || + getExtractField() == REC_DATE_DOY) + prec = 3; + else if (getExtractField() == REC_DATE_QUARTER || + getExtractField() == REC_DATE_DOW) + prec = 1; + else if (getExtractField() == REC_DATE_EPOCH) + prec = 10; else prec = 2; // else max of 12, 31, 24, 59 if (getExtractField() == REC_DATE_SECOND) { prec += dti.getFractionPrecision(); scale += dti.getFractionPrecision(); } + if (getExtractField() == REC_DATE_EPOCH) + { + prec += dti.getFractionPrecision(); + scale += dti.getFractionPrecision(); + } + NABoolean bNegValue = FALSE; + if ( getExtractField() >= REC_DATE_CENTURY && extractStartField <= REC_DATE_WOM ) + bNegValue = TRUE; const Int16 disAmbiguate = 0; // added for 64bit project return new HEAP - SQLNumeric(HEAP, type == NA_INTERVAL_TYPE, /*allowNegValues*/ + SQLNumeric(HEAP, (type == NA_INTERVAL_TYPE) || bNegValue, /*allowNegValues*/ prec, scale, disAmbiguate, http://git-wip-us.apache.org/repos/asf/trafodion/blob/b90abceb/core/sql/parser/ParKeyWords.cpp ---------------------------------------------------------------------- diff --git a/core/sql/parser/ParKeyWords.cpp b/core/sql/parser/ParKeyWords.cpp index 0a152ce..c36d772 100644 --- a/core/sql/parser/ParKeyWords.cpp +++ b/core/sql/parser/ParKeyWords.cpp @@ -169,6 +169,7 @@ ParKeyWord ParKeyWords::keyWords_[] = { ParKeyWord("CATCHUP", TOK_CATCHUP, FLAGSNONE_), ParKeyWord("CEIL", TOK_CEIL, NONRESTOKEN_), ParKeyWord("CEILING", TOK_CEILING, NONRESTOKEN_), + ParKeyWord("CENTURY", TOK_CENTURY, NONRESTOKEN_), ParKeyWord("CHANGED", TOK_CHANGED, NONRESTOKEN_), ParKeyWord("CHANGES", TOK_CHANGES, FLAGSNONE_), ParKeyWord("CHAR", TOK_CHAR, ANS_|RESWORD_|MPWORD_), @@ -320,6 +321,7 @@ ParKeyWord ParKeyWords::keyWords_[] = { ParKeyWord("DEBUG", TOK_DEBUG, NONRESTOKEN_), ParKeyWord("DEC", TOK_DECIMAL, ANS_|RESWORD_|MPWORD_), ParKeyWord("DECIMAL", TOK_DECIMAL, ANS_|RESWORD_|MPWORD_), + ParKeyWord("DECADE", TOK_DECADE, NONRESTOKEN_), ParKeyWord("DECODE", TOK_DECODE, NONRESTOKEN_), ParKeyWord("DECLARE", TOK_DECLARE, ANS_|RESWORD_|MPWORD_), ParKeyWord("DEFAULT", TOK_DEFAULT, SECOND_|ANS_|RESWORD_|MPWORD_), @@ -360,6 +362,8 @@ ParKeyWord ParKeyWords::keyWords_[] = { ParKeyWord("DOMAIN", IDENTIFIER, ANS_|RESWORD_), ParKeyWord("DOUBLE", TOK_DOUBLE, ANS_|RESWORD_|NONRESTOKEN_), ParKeyWord("DOUBLE_IEEE", TOK_DOUBLE_IEEE, NONRESTOKEN_), + ParKeyWord("DOW", TOK_DOW, NONRESTOKEN_), + ParKeyWord("DOY", TOK_DOY, NONRESTOKEN_), ParKeyWord("DROP", TOK_DROP, ANS_|RESWORD_), ParKeyWord("DROPPABLE", TOK_DROPPABLE, SECOND_|NONRESTOKEN_), ParKeyWord("DROP_LIBRARY", TOK_DROP_LIBRARY, NONRESTOKEN_), @@ -393,6 +397,7 @@ ParKeyWord ParKeyWords::keyWords_[] = { ParKeyWord("ENVVAR", TOK_ENVVAR, NONRESTOKEN_), ParKeyWord("ENVVARS", TOK_ENVVARS, NONRESTOKEN_), ParKeyWord("EOF", TOK_EOF, NONRESTOKEN_), + ParKeyWord("EPOCH", TOK_EPOCH, NONRESTOKEN_), ParKeyWord("EQUALS", IDENTIFIER, POTANS_|RESWORD_), ParKeyWord("ERROR", TOK_ERROR, NONRESTOKEN_), ParKeyWord("ESCAPE", TOK_ESCAPE, ANS_|RESWORD_|MPWORD_), @@ -1203,6 +1208,7 @@ ParKeyWord ParKeyWords::keyWords_[] = { ParKeyWord("WITH", TOK_WITH, SECOND_|ANS_|RESWORD_|MPWORD_), ParKeyWord("WITHOUT", TOK_WITHOUT, SECOND_|POTANS_|RESWORD_), ParKeyWord("WORK", TOK_WORK, ANS_|RESWORD_|MPWORD_), + ParKeyWord("WOM", TOK_WOM, NONRESTOKEN_), ParKeyWord("WRITE", TOK_WRITE, ANS_|RESWORD_), ParKeyWord("XMLAGG", TOK_XMLAGG, NONRESTOKEN_), ParKeyWord("XMLELEMENT", TOK_XMLELEMENT, NONRESTOKEN_), http://git-wip-us.apache.org/repos/asf/trafodion/blob/b90abceb/core/sql/parser/sqlparser.y ---------------------------------------------------------------------- diff --git a/core/sql/parser/sqlparser.y b/core/sql/parser/sqlparser.y index b0042e7..4dbb8a0 100755 --- a/core/sql/parser/sqlparser.y +++ b/core/sql/parser/sqlparser.y @@ -500,6 +500,7 @@ static void enableMakeQuotedStringISO88591Mechanism() %token <tokval> TOK_CARDINALITY %token <tokval> TOK_CASE %token <tokval> TOK_CAST +%token <tokval> TOK_CENTURY %token <tokval> TOK_TYPECAST %token <tokval> TOK_CATCHUP // MV %token <tokval> TOK_TRANSLATE @@ -622,6 +623,9 @@ static void enableMakeQuotedStringISO88591Mechanism() %token <tokval> TOK_DYNAMIC %token <tokval> TOK_DYNAMIC_FUNCTION /* ANSI SQL non-reserved word */ %token <tokval> TOK_D_RANK /* Tandem extension non-reserved word */ +%token <tokval> TOK_DECADE +%token <tokval> TOK_DOW +%token <tokval> TOK_DOY %token <tokval> TOK_EACH %token <tokval> TOK_EID %token <tokval> TOK_ELSEIF @@ -656,6 +660,7 @@ static void enableMakeQuotedStringISO88591Mechanism() %token <tokval> TOK_EXTRACT %token <tokval> TOK_EXTRACT_SOURCE %token <tokval> TOK_EXTRACT_TARGET +%token <tokval> TOK_EPOCH %token <tokval> TOK_FALSE %token <tokval> TOK_FAMILY %token <tokval> TOK_FEATURE_VERSION_INFO /* Versioning. Non-reserved */ @@ -1173,6 +1178,7 @@ static void enableMakeQuotedStringISO88591Mechanism() %token <tokval> TOK_WAITEDIO %token <tokval> TOK_WCHAR %token <tokval> TOK_WEEK +%token <tokval> TOK_WOM %token <tokval> TOK_WHEN %token <tokval> TOK_WHENEVER %token <tokval> TOK_WHERE @@ -12480,6 +12486,38 @@ non_second_datetime_field : TOK_YEAR { $$ = REC_DATE_MINUTE; } + | TOK_CENTURY + { + $$ = REC_DATE_CENTURY; + } + | TOK_DECADE + { + $$ = REC_DATE_DECADE; + } + | TOK_WEEK + { + $$ = REC_DATE_WEEK; + } + | TOK_QUARTER + { + $$ = REC_DATE_QUARTER; + } + | TOK_EPOCH + { + $$ = REC_DATE_EPOCH; + } + | TOK_DOW + { + $$ = REC_DATE_DOW; + } + | TOK_DOY + { + $$ = REC_DATE_DOY; + } + | TOK_WOM + { + $$ = REC_DATE_WOM; + } /* type datetimeField */ datetime_field : non_second_datetime_field http://git-wip-us.apache.org/repos/asf/trafodion/blob/b90abceb/core/sql/regress/seabase/EXPECTED030 ---------------------------------------------------------------------- diff --git a/core/sql/regress/seabase/EXPECTED030 b/core/sql/regress/seabase/EXPECTED030 index b5385a7..003b615 100644 --- a/core/sql/regress/seabase/EXPECTED030 +++ b/core/sql/regress/seabase/EXPECTED030 @@ -549,6 +549,44 @@ March 01, 2016, 10:11 2 --- 1 row(s) selected. +>>select extract(week from date '2005-01-01') from (values(1)) as t(a); + +(EXPR) +------ + + 1 + +--- 1 row(s) selected. +>>select extract(epoch from timestamp '2000-12-30 20:38:40.12') from (values(1)) as t(a); + +(EXPR) +--------------------- + + 978208720.12 + +--- 1 row(s) selected. +>>select extract(dow from timestamp '2018-06-21 20:38:40') from (values(1)) as t(a); + +(EXPR) +------ + + 5 + +--- 1 row(s) selected. +>>select extract(doy from timestamp '2018-06-21 20:38:40') from (values(1)) as t(a); + +(EXPR) +------ + 172 + +--- 1 row(s) selected. +>>select extract(wom from timestamp '2018-06-21 20:38:40') from (values(1)) as t(a); + +(EXPR) +------ + 3 + +--- 1 row(s) selected. >> >>drop table if exists t030t1; http://git-wip-us.apache.org/repos/asf/trafodion/blob/b90abceb/core/sql/regress/seabase/TEST030 ---------------------------------------------------------------------- diff --git a/core/sql/regress/seabase/TEST030 b/core/sql/regress/seabase/TEST030 index b214860..ef9af49 100644 --- a/core/sql/regress/seabase/TEST030 +++ b/core/sql/regress/seabase/TEST030 @@ -98,6 +98,11 @@ select HOUR(interval '5:2:15:36.33' day to second(2)) from (values(1)) as t(a); select MINUTE(interval '5:13:25:2.12' day to second(2)) from (values(1)) as t(a); select extract (year from INTERVAL '97-02' YEAR TO MONTH) from (values (1)) as t(a); select interval '8' year / 4 from dual; +select extract(week from date '2005-01-01') from (values(1)) as t(a); +select extract(epoch from timestamp '2000-12-30 20:38:40.12') from (values(1)) as t(a); +select extract(dow from timestamp '2018-06-21 20:38:40') from (values(1)) as t(a); +select extract(doy from timestamp '2018-06-21 20:38:40') from (values(1)) as t(a); +select extract(wom from timestamp '2018-06-21 20:38:40') from (values(1)) as t(a); drop table if exists t030t1; create table t030t1 (a date, b char(30), c varchar(30), d timestamp);
