[
https://issues.apache.org/jira/browse/TRAFODION-3155?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16564522#comment-16564522
]
ASF GitHub Bot commented on TRAFODION-3155:
-------------------------------------------
Github user zellerh commented on a diff in the pull request:
https://github.com/apache/trafodion/pull/1658#discussion_r206719109
--- Diff: 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(
--- End diff --
When I look at the ANSI/ISO standard, it has some cases where the OVERLAPS
predicate returns TRUE, even though some of the values are NULL. Example:
```(date '2000-01-01', date '2000-01-03') overlaps (date '2000-01-02',
cast(null as date))``` should return TRUE, as far as I understand the General
Rules:
- S1: 1-01
- T1: 1-03
- S2: 1-02
- T2: null
So, the predicate ```S2 > S1 and not ( S2 >= T1 and T2 >= T1 )``` becomes
``` 2 > 1 and not ( 2 >= 3 and null >= 3)``` which is TRUE.
I don't think the formula below would ever return TRUE if any of the 4
arguments were NULL.
> New feature: Implementing the OVERLAPS predicate
> ------------------------------------------------
>
> Key: TRAFODION-3155
> URL: https://issues.apache.org/jira/browse/TRAFODION-3155
> Project: Apache Trafodion
> Issue Type: New Feature
> Reporter: zhang.lei
> Assignee: zhang.lei
> Priority: Major
>
> This feature is implemented on the basis of [<ANSI SQL-2003>8.13 overlaps
> predicate, page
> 407|http://grigoras.perso.enseeiht.fr/ens/bd/5WD-02-Foundation-2003-09.pdf],
> Here are some descriptions of it:
> *Function*: Specify a test for an overlap between two datetime periods.
> *Format*:
> <overlaps predicate> ::= <overlaps predicate part 1> <overlaps predicate part
> 2>
> <overlaps predicate part 1> ::= <row value predicand 1>
> <overlaps predicate part 2> ::= OVERLAPS <row value predicand 2>
> <row value predicand 1> ::= <row value predicand>
> <row value predicand 2> ::= <row value predicand>
> *Syntax Rules:*
> 1) The degrees of <row value predicand 1> and <row value predicand 2> shall
> both be 2.
> 2) The declared types of the first field of <row value predicand 1> and the
> first field of <row value predicand
> 2> shall both be datetime data types and these data types shall be
> comparable.
> NOTE 195 — Two datetimes are comparable only if they have the same <primary
> datetime field>s; see Subclause 4.6.2, “Datetimes”.
> 3) The declared type of the second field of each <row value predicand> shall
> be a datetime data type or
> INTERVAL.
> 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>.
> *Example:*
> *--<comment> SELECT WHERE (date, date) OVERLAPS (date, date)*
> >> select 1 from dual
> +> where (date'2018-07-19', date'2018-08-19')
> +> overlaps (date'2018-07-20', date'2018-08-20');
> (EXPR)
> ------
> 1
> — 1 row(s) selected.
>
> *--<comment> SELECT WHERE(date, interval) OVERLAPS (date, date)*
> >> select 1 from dual
> +> where (date'2018-07-19', interval '01-02' year to month)
> +> overlaps (date'2018-07-20', date'2018-08-20');
> (EXPR)
> ------
> 1
> — 1 row(s) selected.
> >> select 1 from dual;
> +> where (date'2018-07-19', -interval '03-02' year to month)
> +> overlaps (date'2018-07-19', date'2018-08-18');
> — 0 row(s) selected.
> *>>-- <comment> SELECT WHERE (date, interval) OVERLAPS (date, interval)*
> >> select 1 from dual;
> +> where (date'2018-07-20', interval '1' month)
> +> overlaps (date'2018-07-19', interval '01-02' year to month);
> (EXPR)
> ------
> 1
> — 1 row(s) selected.
>
> *>>-- <comment> SELECT WHERE (time, time) OVERLAPS (time, time);*
> >> select 1 from dual
> +> where (time'01:02:03', time'02:03:04')
> +> overlaps (time'03:04:05', time'02:01:01');
> (EXPR)
> ------
> 1
> --- 1 row(s) selected.
> >>
> *>>-- <comment> SELECT WHERE (time, interval) OVERLAPS (time, interval);*
> >> select 0 from dual
> +> where (time'03:04:06', interval '04:03:04' hour to second )
> +> overlaps (time'03:04:05', -interval'02:01:01' hour to second);
>
> *>>--<comment> SELECT WHERE (timestamp, timerstamp) OVERLAPS (timestamp,
> timestamp);*
> >> select 1 from btsel01
> +> where (timestamp'2018-07-19 10:00:00', timestamp'2018-08-19 01:00:00')
> +> overlaps (timestamp'2018-07-19 01:00:00', current_timestamp)
> (EXPR)
> ------
> 1
> --- 1 row(s) selected.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
