Friday, January 04, 2008

Not so overlapping OVERLAPS!

The OVERLAPS operator is counter-intuitive when it comes to the same date:
SQL> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod

SQL> select 'x' from dual
2 where (trunc(sysdate),trunc(sysdate)+24)
3 overlaps (trunc(sysdate)+24,trunc(sysdate)+42);

Ingen rækker er valgt


But apparently this is according to the standard (see sql1992.txt) where it says (under "8.11 <overlaps predicate>"):
( S1 > S2 AND NOT ( S1 >= T2 AND T1 >= T2 ) )
OR
( S2 > S1 AND NOT ( S2 >= T1 AND T2 >= T1 ) )
OR
( S1 = S2 AND ( T1 <> T2 OR T1 = T2 ) )


Notice the equal in the S2 >= T1 comparison that is negated (i.e. NOT). So be careful out there - know thou standards!

0 Comments:

Post a Comment

<< Home