[GitHub] trafodion pull request #1658: [TRAFODION-3155] implemented the OVERLAPS pred...

[zellerh <gi...@git.apache.org>]

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.


---