Repository: trafodion Updated Branches: refs/heads/master e66e39b50 -> 88388c754
modified: core/sql/generator/GenPreCode.cpp modified: core/sql/optimizer/BindItemExpr.cpp modified: core/sql/optimizer/ItemExpr.cpp modified: core/sql/optimizer/ItemFunc.h modified: core/sql/optimizer/SynthType.cpp modified: core/sql/parser/sqlparser.y modified: core/sql/regress/qat/eqatddl06 modified: core/sql/regress/qat/eqatddl09 modified: core/sql/regress/qat/eqatdml03 modified: core/sql/regress/qat/qatddl00 modified: core/sql/regress/qat/qatddl06 modified: core/sql/regress/qat/qatddl09 modified: core/sql/regress/qat/qatdml03 Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/ad74d257 Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/ad74d257 Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/ad74d257 Branch: refs/heads/master Commit: ad74d2573339f96ef6fecaf77d1ea778b3290998 Parents: 20724db Author: zlei929 <z_lei...@163.com> Authored: Mon Jul 30 11:55:05 2018 +0800 Committer: zlei929 <z_lei...@163.com> Committed: Mon Jul 30 11:55:05 2018 +0800 ---------------------------------------------------------------------- core/sql/generator/GenPreCode.cpp | 47 ++++ core/sql/optimizer/BindItemExpr.cpp | 51 ++++ core/sql/optimizer/ItemExpr.cpp | 37 +++ core/sql/optimizer/ItemFunc.h | 24 ++ core/sql/optimizer/SynthType.cpp | 74 +++++ core/sql/parser/sqlparser.y | 20 ++ core/sql/regress/qat/eqatddl06 | 22 ++ core/sql/regress/qat/eqatddl09 | 17 ++ core/sql/regress/qat/eqatdml03 | 451 +++++++++++++++++++++++++++++++ core/sql/regress/qat/qatddl00 | 2 + core/sql/regress/qat/qatddl06 | 20 ++ core/sql/regress/qat/qatddl09 | 15 + core/sql/regress/qat/qatdml03 | 216 +++++++++++++++ 13 files changed, 996 insertions(+) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/trafodion/blob/ad74d257/core/sql/generator/GenPreCode.cpp ---------------------------------------------------------------------- diff --git a/core/sql/generator/GenPreCode.cpp b/core/sql/generator/GenPreCode.cpp index 88edc4b..d12af33 100644 --- a/core/sql/generator/GenPreCode.cpp +++ b/core/sql/generator/GenPreCode.cpp @@ -7518,6 +7518,53 @@ ItemExpr * AggrMinMax::preCodeGen(Generator * generator) return this; } // AggrMinMax::preCodeGen() +ItemExpr *Overlaps::preCodeGen(Generator *generator) +{ + if (nodeIsPreCodeGenned()) + return getReplacementExpr(); + + for (Int32 i = 0; i < getArity(); ++i) + { + if (child(i)) + { + const NAType &type = + child(i)->getValueId().getType(); + const DatetimeType *operand = (DatetimeType *)&type; + + if (type.getTypeQualifier() == NA_DATETIME_TYPE + && (operand->getPrecision() == SQLDTCODE_DATE)) + { + child(i) = new (generator->wHeap()) + Cast(child(i), new (generator->wHeap()) + SQLTimestamp(generator->wHeap(), TRUE)); + + child(i)->bindNode(generator->getBindWA()); + } + + } + } + + ItemExpr *newExpr = + generator->getExpGenerator()->createExprTree( + "(@A1<@A2 AND @A3<@A4 AND ((@A2>@A3 AND @A2<=@A4) OR (@A4>@A1 AND @A4<=@A2))) OR" + "(@A1<@A2 AND @A3>@A4 AND ((@A2>@A4 AND @A2<=@A3) OR (@A3>@A1 AND @A3<=@A2))) OR" + "(@A1<@A2 AND @A3=@A4 AND (@A3>=@A1 AND @A3<@A2)) OR" + "(@A1=@A2 AND @A3<@A4 AND (@A1>=@A3 AND @A1<@A4)) OR" + "(@A1=@A2 AND @A3>@A4 AND (@A1>=@A4 AND @A1<@A3)) OR" + "(@A1=@A2 AND @A3=@A4 AND @A1=@A3) OR" + "(@A1>@A2 AND @A3>@A4 AND ((@A1>@A4 AND @A1<=@A3) OR (@A3>@A2 AND @A3<=@A1)))OR" + "(@A1>@A2 AND @A3=@A4 AND (@A3>=@A2 AND @A3<@A1)) OR" + "(@A1>@A2 AND @A3<@A4 AND ((@A1>@A3 AND @A1<=@A4) OR (@A4>@A2 AND @A4<=@A1)))" + , 0 + , 4, child(0), child(1), child(2), child(3)); + + newExpr->bindNode(generator->getBindWA()); + setReplacementExpr(newExpr->preCodeGen(generator)); + markAsPreCodeGenned(); + return getReplacementExpr(); +} + + ItemExpr * Between::preCodeGen(Generator * generator) { if (nodeIsPreCodeGenned()) http://git-wip-us.apache.org/repos/asf/trafodion/blob/ad74d257/core/sql/optimizer/BindItemExpr.cpp ---------------------------------------------------------------------- diff --git a/core/sql/optimizer/BindItemExpr.cpp b/core/sql/optimizer/BindItemExpr.cpp index f8d7b67..3ab35da 100644 --- a/core/sql/optimizer/BindItemExpr.cpp +++ b/core/sql/optimizer/BindItemExpr.cpp @@ -2823,6 +2823,57 @@ ItemExpr *Function::bindNode(BindWA *bindWA) return boundExpr; } // Function::bindNode() + +ItemExpr *Overlaps::bindNode(BindWA *bindWA) +{ + if (nodeIsBound()) + return getValueId().getItemExpr(); + + bindChildren(bindWA); + if (bindWA->errStatus()) + return this; + //Syntax Rules: + // 1) ... 2)... + // 3)... + // Case: + // a) If the declared type is INTERVAL, then the precision of the declared type + // shall be such that the interval can be added to the datetime data type of + // the first column of the <row value predicand>. + // b) If the declared type is a datetime data type, then it shall be comparable + // with the datetime data type of the first column of the <row value predicand>. + const NAType &type1 = + child(1)->castToItemExpr()->getValueId().getType(); + + if (type1.getTypeQualifier() == NA_INTERVAL_TYPE) + { + ItemExpr * newChild = new (bindWA->wHeap()) + BiArith(ITM_PLUS, child(0), child(1)); + child(1) = newChild->bindNode(bindWA); + if (bindWA->errStatus()) + return this; + } + + const NAType &type3 = + child(3)->castToItemExpr()->getValueId().getType(); + if (type3.getTypeQualifier() == NA_INTERVAL_TYPE) + { + ItemExpr * newChild = new (bindWA->wHeap()) + BiArith(ITM_PLUS, child(2), child(3)); + child(3) = newChild->bindNode(bindWA); + if (bindWA->errStatus()) + return this; + } + + + + BuiltinFunction::bindNode(bindWA); + if (bindWA->errStatus()) + return this; + + return getValueId().getItemExpr(); +} + + ItemExpr *Between::bindNode(BindWA *bindWA) { //changes for HistIntRed http://git-wip-us.apache.org/repos/asf/trafodion/blob/ad74d257/core/sql/optimizer/ItemExpr.cpp ---------------------------------------------------------------------- diff --git a/core/sql/optimizer/ItemExpr.cpp b/core/sql/optimizer/ItemExpr.cpp index f0c3825..fff5607 100644 --- a/core/sql/optimizer/ItemExpr.cpp +++ b/core/sql/optimizer/ItemExpr.cpp @@ -11972,6 +11972,43 @@ Exists::copyTopNode(ItemExpr *derivedNode, CollHeap* outHeap) // -------------------------------------------------------------- +// member functions for Overlaps operator +// -------------------------------------------------------------- +ItemExpr * Overlaps::copyTopNode(ItemExpr *derivedNode, CollHeap* outHeap) +{ + ItemExpr *result; + if (derivedNode == NULL) + result = new (outHeap) Overlaps(child(0), child(1), child(2), child(3)); + else + result - derivedNode; + + return BuiltinFunction::copyTopNode(result, outHeap); +} + +void Overlaps::unparse(NAString &result + , PhaseEnum phase + , UnparseFormatEnum form + , TableDesc * tabId) const +{ + result += "("; + child(0)->unparse(result,phase,form,tabId); + result += ", "; + child(1)->unparse(result,phase,form,tabId); + result += ") "; + + NAString kwd(getText(), CmpCommon::statementHeap()); + if (form == USER_FORMAT_DELUXE) kwd.toUpper(); + result += kwd; + + result += " ("; + child(2)->unparse(result,phase,form,tabId); + result += ", "; + child(3)->unparse(result,phase,form,tabId); + result += ")"; +} + + +// -------------------------------------------------------------- // member functions for Between operator // -------------------------------------------------------------- ItemExpr * Between::copyTopNode(ItemExpr *derivedNode, CollHeap* outHeap) http://git-wip-us.apache.org/repos/asf/trafodion/blob/ad74d257/core/sql/optimizer/ItemFunc.h ---------------------------------------------------------------------- diff --git a/core/sql/optimizer/ItemFunc.h b/core/sql/optimizer/ItemFunc.h index 249c09b..fb4bf55 100644 --- a/core/sql/optimizer/ItemFunc.h +++ b/core/sql/optimizer/ItemFunc.h @@ -1198,6 +1198,30 @@ private: }; // class Between + +class Overlaps: public CacheableBuiltinFunction +{ +public: + Overlaps(ItemExpr* d1, ItemExpr* e1, ItemExpr* d2, ItemExpr* e2) + : CacheableBuiltinFunction(ITM_OVERLAPS, 4, d1, e1, d2, e2) + {} + + virtual ~Overlaps(){}; + + virtual ItemExpr *bindNode(BindWA * bindWA); + virtual const NAType *synthesizeType(); + virtual ItemExpr *copyTopNode(ItemExpr *derivedNode = NULL + , CollHeap *outHeap = 0); + virtual NABoolean isAPredicate() const {return true;} + virtual void unparse(NAString &result + , PhaseEnum phase = DEFAULT_PHASE + , UnparseFormatEnum form = USER_FORMAT + , TableDesc *tabId = NULL) const; + + virtual ItemExpr *preCodeGen(Generator*); + +};// class Overlaps + class BoolResult : public BuiltinFunction { // Evaluates the final boolean result value for a predicate. http://git-wip-us.apache.org/repos/asf/trafodion/blob/ad74d257/core/sql/optimizer/SynthType.cpp ---------------------------------------------------------------------- diff --git a/core/sql/optimizer/SynthType.cpp b/core/sql/optimizer/SynthType.cpp index 9efcb41..d4a9950 100644 --- a/core/sql/optimizer/SynthType.cpp +++ b/core/sql/optimizer/SynthType.cpp @@ -2088,6 +2088,80 @@ const NAType *Between::synthesizeType() } // ----------------------------------------------------------------------- +// member functions for class Overlaps +// ----------------------------------------------------------------------- + +const NAType *Overlaps::synthesizeType() +{ + const NAType &type1 = child(0)->getValueId().getType(); + const NAType &type2 = child(1)->getValueId().getType(); + const NAType &type3 = child(2)->getValueId().getType(); + const NAType &type4 = child(3)->getValueId().getType(); + + //Syntax Rules: + // ...... + //2) The declared types of the first field of <row value predicand 1> + // and the first field of <row value predicand2> shall both be datetime + // data types and these data types shall be comparable. + //3) The declared type of the second field of each <row value predicand> + // shall be a datetime data type or INTERVAL. + if (type1.getTypeQualifier() != NA_DATETIME_TYPE) + { + *CmpCommon::diags() << DgSqlCode(-4497) << DgString0("first") + << DgString1("overlaps part1") + << DgString2("datetime"); + return NULL; + } + + if ((type2.getTypeQualifier() != NA_DATETIME_TYPE) + && (type2.getTypeQualifier() != NA_INTERVAL_TYPE)) + { + *CmpCommon::diags() << DgSqlCode(-4497) << DgString0("second") + << DgString1("overlaps part1") + << DgString2("datetime or interval"); + return NULL; + } + + if (type3.getTypeQualifier() != NA_DATETIME_TYPE) + { + *CmpCommon::diags() << DgSqlCode(-4497) << DgString0("first") + << DgString1("overlaps part2") + << DgString2("datetime"); + return NULL; + } + + if ((type4.getTypeQualifier() != NA_DATETIME_TYPE) + && (type4.getTypeQualifier() != NA_INTERVAL_TYPE)) + { + *CmpCommon::diags() << DgSqlCode(-4497) << DgString0("second") + << DgString1("overlaps part2") + << DgString2("datetime or interval"); + return NULL; + } + + UInt32 allowIncompOper = NAType::ALLOW_INCOMP_OPER; + if (NOT type1.isCompatible(type2, &allowIncompOper)) + { + emitDyadicTypeSQLnameMsg(-4041, type1, type2); + return NULL; + } + if (NOT type1.isCompatible(type3, &allowIncompOper)) + { + emitDyadicTypeSQLnameMsg(-4041, type1, type3); + return NULL; + } + + if (NOT type3.isCompatible(type4, &allowIncompOper)) + { + emitDyadicTypeSQLnameMsg(-4041, type3, type4); + return NULL; + } + + return new HEAP SQLBooleanRelat(HEAP, TRUE); +} + + +// ----------------------------------------------------------------------- // member functions for class BiArith // ----------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/trafodion/blob/ad74d257/core/sql/parser/sqlparser.y ---------------------------------------------------------------------- diff --git a/core/sql/parser/sqlparser.y b/core/sql/parser/sqlparser.y index 74a6b03..1abfeb4 100755 --- a/core/sql/parser/sqlparser.y +++ b/core/sql/parser/sqlparser.y @@ -2154,6 +2154,7 @@ static void enableMakeQuotedStringISO88591Mechanism() %type <item> in_predicate %type <item> like_predicate %type <tokval> not_like +%type <item> overlaps_predicate %type <item> quantified_predicate %type <item> search_condition %type <item> boolean_term @@ -18964,6 +18965,7 @@ rel_subquery : '(' query_expression order_by_clause optional_limit_spec ')' /* type item */ predicate : directed_comparison_predicate | key_comparison_predicate + | overlaps_predicate | between_predicate predicate_selectivity_hint { if ($2) @@ -19431,6 +19433,24 @@ exists_predicate : TOK_EXISTS rel_subquery } /* type item */ +overlaps_predicate : value_expression_list_paren TOK_OVERLAPS value_expression_list_paren + { + ItemExprList exprList1($1, PARSERHEAP()); + ItemExprList exprList2($3, PARSERHEAP()); + //Syntax Rules: + // 1) The degrees of <row value predicand 1> and <row value predicand 2> shall both be 2. + if ((exprList1.entries() != 2) + || (exprList1.entries() != exprList2.entries())) + { + *SqlParser_Diags << DgSqlCode(-4077) + << DgString0("OVERLAPS"); + YYERROR; //CHANGE TO YYABORT + } + + $$ = new (PARSERHEAP()) Overlaps((*$1)[0], (*$1)[1], (*$3)[0], (*$3)[1]); + } + +/* type item */ search_condition : boolean_term ; | search_condition TOK_OR boolean_term http://git-wip-us.apache.org/repos/asf/trafodion/blob/ad74d257/core/sql/regress/qat/eqatddl06 ---------------------------------------------------------------------- diff --git a/core/sql/regress/qat/eqatddl06 b/core/sql/regress/qat/eqatddl06 index 1b4448f..e8669cc 100755 --- a/core/sql/regress/qat/eqatddl06 +++ b/core/sql/regress/qat/eqatddl06 @@ -109,6 +109,7 @@ >>-- btsel26 key SYSKEY yes yes large >>-- numerics >>-- btsel27 key SYSKEY yes no +>>-- btsel28 key SYSKEY yes no >>--------------------------------------------------------------------- >> >>--------------------------------------------------------------------- @@ -857,4 +858,25 @@ --- SQL operation complete. >>--------------------------------------------------------------------- +>> +>>--------------------------------------------------------------------- +>>-- Table btsel28 +>>--------------------------------------------------------------------- +>> +>> CREATE TABLE btsel28 ( ++> col_date date ++> , col_time time ++> , col_timestamp timestamp ++> , col_ytom interval year to month ++> , col_htos interval hour to second ++> ) ++> ++>-- Defaults to SYSKEY ++> -- AUDIT ++> ; + +--- SQL operation complete. +>>--------------------------------------------------------------------- +>> +>> >>LOG; http://git-wip-us.apache.org/repos/asf/trafodion/blob/ad74d257/core/sql/regress/qat/eqatddl09 ---------------------------------------------------------------------- diff --git a/core/sql/regress/qat/eqatddl09 b/core/sql/regress/qat/eqatddl09 index 59d41a5..a24c2ff 100755 --- a/core/sql/regress/qat/eqatddl09 +++ b/core/sql/regress/qat/eqatddl09 @@ -983,4 +983,21 @@ >>-- ------------------------------------------------------------------ >>-- update statistics for table btsel27 on every column; >>-- ------------------------------------------------------------------ +>> +>>insert into btsel28 values ++>('2018-02-03', '12:23:00', '2018-02-03 12:23:00', '01-03', '1:2:3'), ++>( null , '02:23:00', '2017-02-03 12:23:00', '01-03', '1:2:3'), ++>('1997-05-13', '11:00:00', '2016-02-03 12:23:00', null , '1:2:3'), ++>('1988-08-17', '03:53:00', '2015-02-03 12:23:00', '01-03', null), ++>('2017-09-29', '04:26:10', '2014-02-03 12:23:00', '01-03', '1:2:3'), ++>('2003-12-04', null , '2013-02-03 12:23:00', '01-03', '1:2:3'), ++>('2010-07-27', '17:09:00', '2012-02-03 12:23:00', '01-03', -interval '1:2:3' hour to second), ++>('2018-02-03', null , '2011-02-03 12:23:00', -interval'01-03'year to month, '1:2:3'); + +--- 8 row(s) inserted. +>>-- ------------------------------------------------------------------ +>>-- update statistics for table btsel28 on every column; +>>-- ------------------------------------------------------------------ +>> +>> >>LOG; http://git-wip-us.apache.org/repos/asf/trafodion/blob/ad74d257/core/sql/regress/qat/eqatdml03 ---------------------------------------------------------------------- diff --git a/core/sql/regress/qat/eqatdml03 b/core/sql/regress/qat/eqatdml03 index e40b428..cee285d 100755 --- a/core/sql/regress/qat/eqatdml03 +++ b/core/sql/regress/qat/eqatdml03 @@ -26,6 +26,8 @@ >>-- testcase A6: use of for read committed access, for read committed >>access >>-- and for read committed access >>-- testcase A7: SELECT aggregate functions with where predicate +>>-- testcase A8: SELECT WHERE datetime are overlaps with other datetime +>>-- or with expressions. >> >>-- All testcases are documented further below. >> @@ -2363,7 +2365,456 @@ B >> >>-- <end-input> >> +>>-- <testcase A8> >> +>>-- <detail> +>>-- select normal where clause test case - this tests the use of +>>-- a OVERLAPS predicate in a where clause. +>> +>>-- <switches> +>>-- pat-file uoutpat <mypat> +>> +>>-- <templates> +>>-- US00 +>> +>>-- <comment> base test +>>-- <ufi-input> +>> select 'FAILED' ++> from btsel28 ++> where (date'1992-03-05', date'1995-03-07') ++> overlaps (date'1996-03-07', date'1998-06-04') ++> group by 1; + +--- 0 row(s) selected. +>> +>>-- <ufi-input> +>> select 'FAILED' ++> from btsel28 ++> where (date'1992-03-05', date'1996-03-07') ++> overlaps (date'1996-03-07', date'1998-06-04') ++> group by 1; + +--- 0 row(s) selected. +>> +>>-- <ufi-input> +>> select 'SUCCESS' ++> from btsel28 ++> where (date'1992-03-05', date'1997-03-07') ++> overlaps (date'1996-03-07', date'1998-06-04') ++> group by 1; + +(EXPR) +------- + +SUCCESS + +--- 1 row(s) selected. +>> +>>-- <ufi-input> +>> select 'SUCCESS' ++> from btsel28 ++> where (date'1992-03-05', date'1998-06-04') ++> overlaps (date'1996-03-07', date'1998-06-04') ++> group by 1; + +(EXPR) +------- + +SUCCESS + +--- 1 row(s) selected. +>> +>>-- <ufi-input> +>> select 'FAILED' ++> from btsel28 ++> where (date'1998-06-04', date'1998-06-04') ++> overlaps (date'1996-03-07', date'1998-06-04') ++> group by 1; + +--- 0 row(s) selected. +>> +>>-- <ufi-input> +>> select 'FAILED' ++> from btsel28 ++> where (date'1996-06-04', date'1998-06-04') ++> overlaps (date'1998-06-04', date'1998-06-04') ++> group by 1; + +--- 0 row(s) selected. +>> +>>-- <ufi-input> +>> select 'SUCCESS' ++> from btsel28 ++> where (date'1999-06-04', date'1998-06-04') ++> overlaps (date'1998-06-04', date'1998-06-04') ++> group by 1; + +(EXPR) +------- + +SUCCESS + +--- 1 row(s) selected. +>> +>>-- <ufi-input> +>> select 'SUCCESS' ++> from btsel28 ++> where (date'1998-06-04', date'1998-06-04') ++> overlaps (date'1999-03-07', date'1998-06-04') ++> group by 1; + +(EXPR) +------- + +SUCCESS + +--- 1 row(s) selected. +>> +>>-- <ufi-input> +>> select 'SUCCESS' ++> from btsel28 ++> where (date'1999-03-07', date'1998-06-04') ++> overlaps (date'1999-03-07', date'1998-06-04') ++> group by 1; + +(EXPR) +------- + +SUCCESS + +--- 1 row(s) selected. +>> +>>-- <ufi-input> +>> select 'SUCCESS' ++> from btsel28 ++> where (date'1999-03-07', date'1999-06-04') ++> overlaps (date'1999-03-07', date'2000-06-04') ++> group by 1; + +(EXPR) +------- + +SUCCESS + +--- 1 row(s) selected. +>> +>>-- <comment> SELECT WHERE date are overlaps with date; +>>-- <ufi-input> +>> select col_date ++> from btsel28 ++> where (date'1995-01-01', date'2000-01-01') ++> overlaps (date'1990-01-01', date'2010-01-01'); + +COL_DATE +---------- + +2018-02-03 +? +1997-05-13 +1988-08-17 +2017-09-29 +2003-12-04 +2010-07-27 +2018-02-03 + +--- 8 row(s) selected. +>> +>>-- <ufi-input> +>> select col_date ++> from btsel28 ++> where (col_date, date'2000-01-01') ++> overlaps (date'1990-01-01', date'2010-01-01'); + +COL_DATE +---------- + +2018-02-03 +1997-05-13 +1988-08-17 +2017-09-29 +2003-12-04 +2010-07-27 +2018-02-03 + +--- 7 row(s) selected. +>> +>>-- <ufi-input> +>> select col_date ++> from btsel28 ++> where (col_date, date'1995-01-03') ++> overlaps (date'2019-03-01', col_date); + +COL_DATE +---------- + +1988-08-17 + +--- 1 row(s) selected. +>> +>>-- <ufi-input> +>> select col_date ++> from btsel28 ++> where (date'2018-02-03', col_date) ++> overlaps (col_date, date'1988-08-17'); + +COL_DATE +---------- + +1988-08-17 + +--- 1 row(s) selected. +>> +>>-- <comment> SELECT WHERE date are overlaps with timestamp +>>-- <ufi-input> +>> select col_date, col_timestamp ++> from btsel28 ++> where (date'1995-01-01', timestamp'2000-02-03 00:00:00') ++> overlaps (date'2000-01-01', timestamp'2019-01-01 12:00:00'); + +COL_DATE COL_TIMESTAMP +---------- -------------------------- + +2018-02-03 2018-02-03 12:23:00.000000 +? 2017-02-03 12:23:00.000000 +1997-05-13 2016-02-03 12:23:00.000000 +1988-08-17 2015-02-03 12:23:00.000000 +2017-09-29 2014-02-03 12:23:00.000000 +2003-12-04 2013-02-03 12:23:00.000000 +2010-07-27 2012-02-03 12:23:00.000000 +2018-02-03 2011-02-03 12:23:00.000000 + +--- 8 row(s) selected. +>> +>>-- <ufi-input> +>> select col_date, col_timestamp ++> from btsel28 ++> where (col_date, col_timestamp) ++> overlaps (date'2000-01-01', timestamp'2015-01-01 12:00:00'); + +COL_DATE COL_TIMESTAMP +---------- -------------------------- + +1997-05-13 2016-02-03 12:23:00.000000 +1988-08-17 2015-02-03 12:23:00.000000 +2017-09-29 2014-02-03 12:23:00.000000 +2003-12-04 2013-02-03 12:23:00.000000 +2010-07-27 2012-02-03 12:23:00.000000 +2018-02-03 2011-02-03 12:23:00.000000 + +--- 6 row(s) selected. +>> +>>-- <ufi-input> +>> select col_date, col_timestamp ++> from btsel28 ++> where (date'1997-03-08', timestamp'2010-01-01 13:00:00') ++> overlaps (timestamp'2017-01-01 00:00:00', col_timestamp); + +--- 0 row(s) selected. +>> +>>-- <comment> SELECT WHERE date are overlaps with interval +>>-- <ufi-input> +>> select col_date, col_ytom ++> from btsel28 ++> where (date'1988-01-01', interval '30' year) ++> overlaps (date'2028-03-02', -interval '11-02' year to month); + +COL_DATE COL_YTOM +---------- -------- + +2018-02-03 1-03 +? 1-03 +1997-05-13 ? +1988-08-17 1-03 +2017-09-29 1-03 +2003-12-04 1-03 +2010-07-27 1-03 +2018-02-03 - 1-03 + +--- 8 row(s) selected. +>> +>>-- <ufi-input> +>> select col_date, col_ytom ++> from btsel28 ++> where (col_date, col_ytom) ++> overlaps (date'2028-03-02', -interval '11-02' year to month); + +COL_DATE COL_YTOM +---------- -------- + +2018-02-03 1-03 +2017-09-29 1-03 +2018-02-03 - 1-03 + +--- 3 row(s) selected. +>> +>>-- <ufi-input> +>> select col_date, col_ytom ++> from btsel28 ++> where (col_date, date'1972-01-03') ++> overlaps (date'2018-03-02', interval '11-02' year to month); + +--- 0 row(s) selected. +>> +>>-- <comment> SELECT WHERE time are overlaps with time +>>-- <ufi-input> +>> select col_time ++> from btsel28 ++> where (time'01:02:03', time'02:03:04') ++> overlaps (time'03:04:05', time'02:01:01'); + +COL_TIME +-------- + +12:23:00 +02:23:00 +11:00:00 +03:53:00 +04:26:10 +? +17:09:00 +? + +--- 8 row(s) selected. +>> +>>-- <ufi-input> +>> select col_time ++> from btsel28 ++> where (col_time, time'03:23:11') ++> overlaps (time'13:23:32', time'05:00:00'); + +COL_TIME +-------- + +12:23:00 +11:00:00 +17:09:00 + +--- 3 row(s) selected. +>> +>>-- <ufi-input> +>> select col_time ++> from btsel28 ++> where (col_time, time'03:23:11') ++> overlaps (time'13:23:32', col_time); + +COL_TIME +-------- + +02:23:00 +17:09:00 + +--- 2 row(s) selected. +>> +>>-- <comment> SELECT WHERE time are overlaps with interval +>>-- <ufi-input> +>> select col_time, col_htos ++> from btsel28 ++> where (time'01:02:03', interval '01:02:03' hour to second) ++> overlaps (time'02:00:00', -interval'02:01' minute to second); + +COL_TIME COL_HTOS +-------- ---------------- + +12:23:00 1:02:03.000000 +02:23:00 1:02:03.000000 +11:00:00 1:02:03.000000 +03:53:00 ? +04:26:10 1:02:03.000000 +? 1:02:03.000000 +17:09:00 - 1:02:03.000000 +? 1:02:03.000000 + +--- 8 row(s) selected. +>> +>>-- <ufi-input> +>> select col_time, col_htos ++> from btsel28 ++> where (col_time, col_htos) ++> overlaps (time'12:00:00', -interval '03:00' hour to minute); + +COL_TIME COL_HTOS +-------- ---------------- + +11:00:00 1:02:03.000000 + +--- 1 row(s) selected. +>> +>>-- <ufi-input> +>> select col_time, col_htos ++> from btsel28 ++> where (col_time, col_htos) ++> overlaps (time'01:00:00', interval '09:00' hour to minute); + +COL_TIME COL_HTOS +-------- ---------------- + +02:23:00 1:02:03.000000 +04:26:10 1:02:03.000000 + +--- 2 row(s) selected. +>> +>>-- <comment> hybird test +>>-- <ufi-input> +>> select * ++> from btsel28 ++> where (col_date, col_timestamp) ++> overlaps (col_timestamp, col_htos); + +COL_DATE COL_TIME COL_TIMESTAMP COL_YTOM COL_HTOS +---------- -------- -------------------------- -------- ---------------- + +2017-09-29 04:26:10 2014-02-03 12:23:00.000000 1-03 1:02:03.000000 +2010-07-27 17:09:00 2012-02-03 12:23:00.000000 1-03 - 1:02:03.000000 +2018-02-03 ? 2011-02-03 12:23:00.000000 - 1-03 1:02:03.000000 + +--- 3 row(s) selected. +>> +>>-- <ufi-input> +>> select * ++> from btsel28 ++> where (col_date, col_timestamp) ++> overlaps (col_timestamp, col_ytom); + +COL_DATE COL_TIME COL_TIMESTAMP COL_YTOM COL_HTOS +---------- -------- -------------------------- -------- ---------------- + +2017-09-29 04:26:10 2014-02-03 12:23:00.000000 1-03 1:02:03.000000 + +--- 1 row(s) selected. +>> +>>-- <comment> OVERLAPS predicate error test case +>>-- <ufi-input> +>> select count(*) ++> from btsel28 ++> where ('asa', date'2019-01-01') ++> overlaps (date'2018-03-01', date'2011-12-22'); + +*** ERROR[4497] The first operand of overlaps part1 must be datetime. + +*** ERROR[8822] The statement was not prepared. + +>> +>>-- <ufi-input> +>> select count(*) ++> from btsel28 ++> where (col_date, col_time) ++> overlaps (date'2011-01-01', date'2001-01-01'); + +*** ERROR[4041] Type DATE cannot be compared with type TIME(0). + +*** ERROR[8822] The statement was not prepared. + +>> +>>-- <ufi-input> +>> select count(*) ++> from btsel28 ++> where (col_ytom, col_time) ++> overlaps (col_htos, col_date); + +*** ERROR[4497] The first operand of overlaps part1 must be datetime. + +*** ERROR[8822] The statement was not prepared. + >> >>-- <end-test> >>LOG; http://git-wip-us.apache.org/repos/asf/trafodion/blob/ad74d257/core/sql/regress/qat/qatddl00 ---------------------------------------------------------------------- diff --git a/core/sql/regress/qat/qatddl00 b/core/sql/regress/qat/qatddl00 index 4d0d560..8e9e8a2 100755 --- a/core/sql/regress/qat/qatddl00 +++ b/core/sql/regress/qat/qatddl00 @@ -124,6 +124,8 @@ drop table btsel26; drop table btsel27; +drop table btsel28; + drop index btre201a; drop index btre201b; drop index btre201c; http://git-wip-us.apache.org/repos/asf/trafodion/blob/ad74d257/core/sql/regress/qat/qatddl06 ---------------------------------------------------------------------- diff --git a/core/sql/regress/qat/qatddl06 b/core/sql/regress/qat/qatddl06 index caedcf5..98f136c 100755 --- a/core/sql/regress/qat/qatddl06 +++ b/core/sql/regress/qat/qatddl06 @@ -97,6 +97,7 @@ reset parserflags 1024; -- btsel26 key SYSKEY yes yes large -- numerics -- btsel27 key SYSKEY yes no +-- btsel28 key SYSKEY yes no --------------------------------------------------------------------- --------------------------------------------------------------------- @@ -755,4 +756,23 @@ reset parserflags 1024; -- AUDIT ; --------------------------------------------------------------------- + +--------------------------------------------------------------------- +-- Table btsel28 +--------------------------------------------------------------------- + + CREATE TABLE btsel28 ( + col_date date + , col_time time + , col_timestamp timestamp + , col_ytom interval year to month + , col_htos interval hour to second + ) + +-- Defaults to SYSKEY + -- AUDIT + ; +--------------------------------------------------------------------- + + LOG; http://git-wip-us.apache.org/repos/asf/trafodion/blob/ad74d257/core/sql/regress/qat/qatddl09 ---------------------------------------------------------------------- diff --git a/core/sql/regress/qat/qatddl09 b/core/sql/regress/qat/qatddl09 index c4516c7..48c6458 100755 --- a/core/sql/regress/qat/qatddl09 +++ b/core/sql/regress/qat/qatddl09 @@ -528,4 +528,19 @@ insert into btsel27 values -- ------------------------------------------------------------------ -- update statistics for table btsel27 on every column; -- ------------------------------------------------------------------ + +insert into btsel28 values +('2018-02-03', '12:23:00', '2018-02-03 12:23:00', '01-03', '1:2:3'), +( null , '02:23:00', '2017-02-03 12:23:00', '01-03', '1:2:3'), +('1997-05-13', '11:00:00', '2016-02-03 12:23:00', null , '1:2:3'), +('1988-08-17', '03:53:00', '2015-02-03 12:23:00', '01-03', null), +('2017-09-29', '04:26:10', '2014-02-03 12:23:00', '01-03', '1:2:3'), +('2003-12-04', null , '2013-02-03 12:23:00', '01-03', '1:2:3'), +('2010-07-27', '17:09:00', '2012-02-03 12:23:00', '01-03', -interval '1:2:3' hour to second), +('2018-02-03', null , '2011-02-03 12:23:00', -interval'01-03'year to month, '1:2:3'); +-- ------------------------------------------------------------------ +-- update statistics for table btsel28 on every column; +-- ------------------------------------------------------------------ + + LOG; http://git-wip-us.apache.org/repos/asf/trafodion/blob/ad74d257/core/sql/regress/qat/qatdml03 ---------------------------------------------------------------------- diff --git a/core/sql/regress/qat/qatdml03 b/core/sql/regress/qat/qatdml03 index ef13a39..dda021e 100755 --- a/core/sql/regress/qat/qatdml03 +++ b/core/sql/regress/qat/qatdml03 @@ -27,6 +27,8 @@ LOG aqatdml03 Clear; -- testcase A6: use of for read committed access, for read committed access -- and for read committed access -- testcase A7: SELECT aggregate functions with where predicate +-- testcase A8: SELECT WHERE datetime are overlaps with other datetime +-- or with expressions. -- All testcases are documented further below. @@ -835,7 +837,221 @@ LOG aqatdml03 Clear; -- <end-input> +-- <testcase A8> +-- <detail> +-- select normal where clause test case - this tests the use of +-- a OVERLAPS predicate in a where clause. + +-- <switches> +-- pat-file uoutpat <mypat> + +-- <templates> +-- US00 + +-- <comment> base test +-- <ufi-input> + select 'FAILED' + from btsel28 + where (date'1992-03-05', date'1995-03-07') + overlaps (date'1996-03-07', date'1998-06-04') + group by 1; + +-- <ufi-input> + select 'FAILED' + from btsel28 + where (date'1992-03-05', date'1996-03-07') + overlaps (date'1996-03-07', date'1998-06-04') + group by 1; + +-- <ufi-input> + select 'SUCCESS' + from btsel28 + where (date'1992-03-05', date'1997-03-07') + overlaps (date'1996-03-07', date'1998-06-04') + group by 1; + +-- <ufi-input> + select 'SUCCESS' + from btsel28 + where (date'1992-03-05', date'1998-06-04') + overlaps (date'1996-03-07', date'1998-06-04') + group by 1; + +-- <ufi-input> + select 'FAILED' + from btsel28 + where (date'1998-06-04', date'1998-06-04') + overlaps (date'1996-03-07', date'1998-06-04') + group by 1; + +-- <ufi-input> + select 'FAILED' + from btsel28 + where (date'1996-06-04', date'1998-06-04') + overlaps (date'1998-06-04', date'1998-06-04') + group by 1; + +-- <ufi-input> + select 'SUCCESS' + from btsel28 + where (date'1999-06-04', date'1998-06-04') + overlaps (date'1998-06-04', date'1998-06-04') + group by 1; + +-- <ufi-input> + select 'SUCCESS' + from btsel28 + where (date'1998-06-04', date'1998-06-04') + overlaps (date'1999-03-07', date'1998-06-04') + group by 1; + +-- <ufi-input> + select 'SUCCESS' + from btsel28 + where (date'1999-03-07', date'1998-06-04') + overlaps (date'1999-03-07', date'1998-06-04') + group by 1; + +-- <ufi-input> + select 'SUCCESS' + from btsel28 + where (date'1999-03-07', date'1999-06-04') + overlaps (date'1999-03-07', date'2000-06-04') + group by 1; + +-- <comment> SELECT WHERE date are overlaps with date; +-- <ufi-input> + select col_date + from btsel28 + where (date'1995-01-01', date'2000-01-01') + overlaps (date'1990-01-01', date'2010-01-01'); + +-- <ufi-input> + select col_date + from btsel28 + where (col_date, date'2000-01-01') + overlaps (date'1990-01-01', date'2010-01-01'); + +-- <ufi-input> + select col_date + from btsel28 + where (col_date, date'1995-01-03') + overlaps (date'2019-03-01', col_date); + +-- <ufi-input> + select col_date + from btsel28 + where (date'2018-02-03', col_date) + overlaps (col_date, date'1988-08-17'); + +-- <comment> SELECT WHERE date are overlaps with timestamp +-- <ufi-input> + select col_date, col_timestamp + from btsel28 + where (date'1995-01-01', timestamp'2000-02-03 00:00:00') + overlaps (date'2000-01-01', timestamp'2019-01-01 12:00:00'); + +-- <ufi-input> + select col_date, col_timestamp + from btsel28 + where (col_date, col_timestamp) + overlaps (date'2000-01-01', timestamp'2015-01-01 12:00:00'); + +-- <ufi-input> + select col_date, col_timestamp + from btsel28 + where (date'1997-03-08', timestamp'2010-01-01 13:00:00') + overlaps (timestamp'2017-01-01 00:00:00', col_timestamp); + +-- <comment> SELECT WHERE date are overlaps with interval +-- <ufi-input> + select col_date, col_ytom + from btsel28 + where (date'1988-01-01', interval '30' year) + overlaps (date'2028-03-02', -interval '11-02' year to month); + +-- <ufi-input> + select col_date, col_ytom + from btsel28 + where (col_date, col_ytom) + overlaps (date'2028-03-02', -interval '11-02' year to month); + +-- <ufi-input> + select col_date, col_ytom + from btsel28 + where (col_date, date'1972-01-03') + overlaps (date'2018-03-02', interval '11-02' year to month); + +-- <comment> SELECT WHERE time are overlaps with time +-- <ufi-input> + select col_time + from btsel28 + where (time'01:02:03', time'02:03:04') + overlaps (time'03:04:05', time'02:01:01'); + +-- <ufi-input> + select col_time + from btsel28 + where (col_time, time'03:23:11') + overlaps (time'13:23:32', time'05:00:00'); + +-- <ufi-input> + select col_time + from btsel28 + where (col_time, time'03:23:11') + overlaps (time'13:23:32', col_time); + +-- <comment> SELECT WHERE time are overlaps with interval +-- <ufi-input> + select col_time, col_htos + from btsel28 + where (time'01:02:03', interval '01:02:03' hour to second) + overlaps (time'02:00:00', -interval'02:01' minute to second); + +-- <ufi-input> + select col_time, col_htos + from btsel28 + where (col_time, col_htos) + overlaps (time'12:00:00', -interval '03:00' hour to minute); + +-- <ufi-input> + select col_time, col_htos + from btsel28 + where (col_time, col_htos) + overlaps (time'01:00:00', interval '09:00' hour to minute); + +-- <comment> hybird test +-- <ufi-input> + select * + from btsel28 + where (col_date, col_timestamp) + overlaps (col_timestamp, col_htos); + +-- <ufi-input> + select * + from btsel28 + where (col_date, col_timestamp) + overlaps (col_timestamp, col_ytom); + +-- <comment> OVERLAPS predicate error test case +-- <ufi-input> + select count(*) + from btsel28 + where ('asa', date'2019-01-01') + overlaps (date'2018-03-01', date'2011-12-22'); + +-- <ufi-input> + select count(*) + from btsel28 + where (col_date, col_time) + overlaps (date'2011-01-01', date'2001-01-01'); + +-- <ufi-input> + select count(*) + from btsel28 + where (col_ytom, col_time) + overlaps (col_htos, col_date); -- <end-test> LOG;