[ https://issues.apache.org/jira/browse/TRAFODION-3155?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16564754#comment-16564754 ]
ASF GitHub Bot commented on TRAFODION-3155: ------------------------------------------- Github user zlei929 commented on a diff in the pull request: https://github.com/apache/trafodion/pull/1658#discussion_r206758851 --- 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 -- Hi, @zellerh May be you are right, i tried to test it on PostgreSql and here is the result: ``` postgres=# select 1 from t2 where (date'2000-01-01', date'2000-01-03') overlaps (date'2000-01-02', null); ?column? ---------- 1 (1 row) postgres=# ``` > 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)