You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by db...@apache.org on 2018/08/03 00:05:52 UTC
[1/3] trafodion git commit: 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.
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_...@163.com>
Authored: Mon Jul 30 11:55:05 2018 +0800
Committer: zlei929 <z_...@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;
[2/3] trafodion git commit: modified:
core/sql/generator/GenPreCode.cpp modified: core/sql/optimizer/ItemExpr.cpp
modified: core/sql/regress/qat/eqatdml03
Posted by db...@apache.org.
modified: core/sql/generator/GenPreCode.cpp
modified: core/sql/optimizer/ItemExpr.cpp
modified: core/sql/regress/qat/eqatdml03
Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/5f09acda
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/5f09acda
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/5f09acda
Branch: refs/heads/master
Commit: 5f09acdad81b8a70af70218d79a63908f2d36ea1
Parents: ad74d25
Author: zlei929 <z_...@163.com>
Authored: Wed Aug 1 15:54:54 2018 +0800
Committer: zlei929 <z_...@163.com>
Committed: Wed Aug 1 15:54:54 2018 +0800
----------------------------------------------------------------------
core/sql/generator/GenPreCode.cpp | 95 +++++++++++++++++++++++++---------
core/sql/optimizer/ItemExpr.cpp | 2 +-
core/sql/regress/qat/eqatdml03 | 6 ++-
3 files changed, 75 insertions(+), 28 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/trafodion/blob/5f09acda/core/sql/generator/GenPreCode.cpp
----------------------------------------------------------------------
diff --git a/core/sql/generator/GenPreCode.cpp b/core/sql/generator/GenPreCode.cpp
index d12af33..b8f5028 100644
--- a/core/sql/generator/GenPreCode.cpp
+++ b/core/sql/generator/GenPreCode.cpp
@@ -7524,39 +7524,84 @@ ItemExpr *Overlaps::preCodeGen(Generator *generator)
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));
+ if (child(i))
+ {
+ const NAType &type =
+ child(i)->getValueId().getType();
+ const DatetimeType *operand = (DatetimeType *)&type;
- child(i)->bindNode(generator->getBindWA());
- }
+ 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());
+ }
+ }
}
- }
+
+ //General Rules:
+ //1) ... 2) ... 3) ...
+ //4) if D1(child(0)) is the null value or if E1(child(1))<D1,
+ // then let S1 = E1 and let T1 = D1.
+ // Otherwise, let S1 = D1 and let T1 = E1.
+ //
+ ItemExpr *S1 = NULL;
+ ItemExpr *T1 = NULL;
+ S1 = generator->getExpGenerator()->createExprTree(
+ "CASE WHEN (@A2<@A1 OR @A1 IS NULL) THEN @A2 ELSE @A1 END"
+ , 0
+ , 2
+ , child(0), child(1));
+ T1 = generator->getExpGenerator()->createExprTree(
+ "CASE WHEN (@A2<@A1 OR @A1 IS NULL) THEN @A1 ELSE @A2 END"
+ , 0
+ , 2
+ , child(0), child(1));
+
+ child(0) = S1->bindNode(generator->getBindWA());
+ child(1) = T1->bindNode(generator->getBindWA());
+
+
+ //General Rules:
+ //1) ... 2) ... 3) ... 4) ... 5) ...
+ //6) if D2(child(2)) is the null value or if E2(child(3))<D2,
+ // then let S2 = E2 and let T2 = D2.
+ // Otherwise, let S2 = D2 and let T2 = E2.
+ //
+ ItemExpr *S2 = NULL;
+ ItemExpr *T2 = NULL;
+ S2 = generator->getExpGenerator()->createExprTree(
+ "CASE WHEN (@A2<@A1 OR @A1 IS NULL) THEN @A2 ELSE @A1 END"
+ , 0
+ , 2
+ , child(2), child(3));
+ T2 = generator->getExpGenerator()->createExprTree(
+ "CASE WHEN (@A2<@A1 OR @A1 IS NULL) THEN @A1 ELSE @A2 END"
+ , 0
+ , 2
+ , child(2), child(3));
+
+ child(2) = S2->bindNode(generator->getBindWA());
+ child(3) = T2->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)))"
+ //General Rules:
+ //1) ... 2) ... 3) ... 4) ... 5) ... 6) ...
+ //7) The result of the <overlaps predicate> is
+ // the result of the following expression:
+ "(@A1 > @A3 AND NOT (@A1 >= @A4 AND @A2 >= @A4))"
+ " OR "
+ "(@A3 > @A1 AND NOT (@A3 >= @A2 AND @A4 >= @A2))"
+ " OR "
+ "(@A1 = @A3 AND (@A2 <> @A4 OR @A2=@A4))"
, 0
- , 4, child(0), child(1), child(2), child(3));
+ , 4
+ , child(0), child(1), child(2), child(3));
newExpr->bindNode(generator->getBindWA());
setReplacementExpr(newExpr->preCodeGen(generator));
http://git-wip-us.apache.org/repos/asf/trafodion/blob/5f09acda/core/sql/optimizer/ItemExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/ItemExpr.cpp b/core/sql/optimizer/ItemExpr.cpp
index fff5607..7ae9b9d 100644
--- a/core/sql/optimizer/ItemExpr.cpp
+++ b/core/sql/optimizer/ItemExpr.cpp
@@ -11982,7 +11982,7 @@ ItemExpr * Overlaps::copyTopNode(ItemExpr *derivedNode, CollHeap* outHeap)
else
result - derivedNode;
- return BuiltinFunction::copyTopNode(result, outHeap);
+ return CacheableBuiltinFunction::copyTopNode(result, outHeap);
}
void Overlaps::unparse(NAString &result
http://git-wip-us.apache.org/repos/asf/trafodion/blob/5f09acda/core/sql/regress/qat/eqatdml03
----------------------------------------------------------------------
diff --git a/core/sql/regress/qat/eqatdml03 b/core/sql/regress/qat/eqatdml03
index cee285d..048bc98 100755
--- a/core/sql/regress/qat/eqatdml03
+++ b/core/sql/regress/qat/eqatdml03
@@ -2529,6 +2529,7 @@ COL_DATE
----------
2018-02-03
+?
1997-05-13
1988-08-17
2017-09-29
@@ -2536,7 +2537,7 @@ COL_DATE
2010-07-27
2018-02-03
---- 7 row(s) selected.
+--- 8 row(s) selected.
>>
>>-- <ufi-input>
>> select col_date
@@ -2749,9 +2750,10 @@ COL_TIME COL_HTOS
-------- ----------------
02:23:00 1:02:03.000000
+03:53:00 ?
04:26:10 1:02:03.000000
---- 2 row(s) selected.
+--- 3 row(s) selected.
>>
>>-- <comment> hybird test
>>-- <ufi-input>
[3/3] trafodion git commit: Merge [TRAFODION-3155] PR 1658 Implement
OVERLAPS predicate
Posted by db...@apache.org.
Merge [TRAFODION-3155] PR 1658 Implement OVERLAPS predicate
Project: http://git-wip-us.apache.org/repos/asf/trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/trafodion/commit/88388c75
Tree: http://git-wip-us.apache.org/repos/asf/trafodion/tree/88388c75
Diff: http://git-wip-us.apache.org/repos/asf/trafodion/diff/88388c75
Branch: refs/heads/master
Commit: 88388c7540c326ba16bca16e6d275201d5814d22
Parents: e66e39b 5f09acd
Author: Dave Birdsall <db...@apache.org>
Authored: Fri Aug 3 00:04:21 2018 +0000
Committer: Dave Birdsall <db...@apache.org>
Committed: Fri Aug 3 00:04:21 2018 +0000
----------------------------------------------------------------------
core/sql/generator/GenPreCode.cpp | 92 +++++++
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 | 453 +++++++++++++++++++++++++++++++
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, 1043 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/trafodion/blob/88388c75/core/sql/optimizer/ItemExpr.cpp
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/trafodion/blob/88388c75/core/sql/optimizer/SynthType.cpp
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/trafodion/blob/88388c75/core/sql/parser/sqlparser.y
----------------------------------------------------------------------