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 <[email protected]>
Authored: Mon Jul 30 11:55:05 2018 +0800
Committer: zlei929 <[email protected]>
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;
