The “OVERLAPS” Predicate

I was browsing a pretty interesting “e-book” this evening: “Developing Time-Oriented Database Applications in SQL” By Richard T. Snodgrass. I quietly bemoaned the missing functionality of Oracle in some regards, for instance the rather neat OVERLAPS predicate that returns true when two intervals … well, overlap. We tend to jump through hoops a little to achieve this sometimes, I felt.

But you never know, things have moved on since the Oracle 8, so I searched the 10g and 11g documentation to see if I’dbeen negligent in not reading the New Features Guide. Alas, nothing in the SQL Reference, but I did turn up this interesting entry:

ORA-30085: syntax error was found in overlaps predicate 
Cause: A syntax error was found during parsing an overlaps predicate.
Action: Correct the syntax.

“How”, I asked myself, “can there be an error message relating to a non-existent feature”?

“What if”, I continued, “said feature is not missing, but merely undocumented?”

So I tested it.

And it works.

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 12 23:14:03 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Enter user-name: XXX/XXX@XXX
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, Oracle Label Security and Data Mining Scoring Engine options
SQL> select * from dual
  2  where          (date '2007-01-01', date '2008-01-01')
  3        overlaps (date '2005-01-01', date '2006-01-01')
  4  /
no rows selected
SQL>
SQL> select * from dual
  2  where          (date '2007-01-01', date '2008-01-01')
  3        overlaps (date '2005-01-01', date '2007-01-02')
  4  /
D
-
X
SQL>
SQL> select * from dual
  2  where          (date '2007-01-01', interval '5' year)
  3        overlaps (date '2005-01-01', date '2007-01-02')
  4  /
D
-
X
SQL>
SQL> select * from dual
  2  where          (date '2007-01-01', interval '5' year)
  3        overlaps (date '2005-01-01', interval '10' year)
  4  /
D
-
X
SQL>
SQL> select * from dual
  2  where NOT         (date '2007-01-01', date '2008-01-01')
  3           overlaps (date '2005-01-01', date '2006-01-01')
  4  /
D
-
X
SQL>
SQL>
SQL> with test_data as
  2  (select date '2007-01-01' from_dt, date '2007-12-01' to_dt from dual union all
  3   select date '2007-04-01' from_dt, date '2007-08-01' to_dt from dual union all
  4   select date '2007-07-01' from_dt, date '2007-08-01' to_dt from dual union all
  5   select date '2007-10-01' from_dt, date '2008-02-01' to_dt from dual union all
  6   select date '2008-01-01' from_dt, date '2008-12-01' to_dt from dual)
  7  select *
  8  from   test_data t1,
  9         test_data t2
 10  where  (t1.from_dt,t1.to_dt) overlaps (t2.from_dt,t2.to_dt)
 11  /
FROM_DT   TO_DT     FROM_DT   TO_DT
--------- --------- --------- ---------
01-JAN-07 01-DEC-07 01-JAN-07 01-DEC-07
01-JAN-07 01-DEC-07 01-APR-07 01-AUG-07
01-JAN-07 01-DEC-07 01-JUL-07 01-AUG-07
01-JAN-07 01-DEC-07 01-OCT-07 01-FEB-08
01-APR-07 01-AUG-07 01-JAN-07 01-DEC-07
01-APR-07 01-AUG-07 01-APR-07 01-AUG-07
01-APR-07 01-AUG-07 01-JUL-07 01-AUG-07
01-JUL-07 01-AUG-07 01-JAN-07 01-DEC-07
01-JUL-07 01-AUG-07 01-APR-07 01-AUG-07
01-JUL-07 01-AUG-07 01-JUL-07 01-AUG-07
01-OCT-07 01-FEB-08 01-JAN-07 01-DEC-07
FROM_DT   TO_DT     FROM_DT   TO_DT
--------- --------- --------- ---------
01-OCT-07 01-FEB-08 01-OCT-07 01-FEB-08
01-OCT-07 01-FEB-08 01-JAN-08 01-DEC-08
01-JAN-08 01-DEC-08 01-OCT-07 01-FEB-08
01-JAN-08 01-DEC-08 01-JAN-08 01-DEC-08
15 rows selected.
SQL>
SQL>  with test_data as
  2  (select date '2007-01-01' from_dt, date '2007-12-01' to_dt from dual union all
  3   select date '2007-04-01' from_dt, date '2007-08-01' to_dt from dual union all
  4   select date '2007-07-01' from_dt, date '2007-08-01' to_dt from dual union all
  5   select date '2007-10-01' from_dt, date '2008-02-01' to_dt from dual union all
  6   select date '2008-01-01' from_dt, date '2008-12-01' to_dt from dual)
  7  select *
  8  from   test_data t1,
  9         test_data t2
 10  where  NOT (t1.from_dt,t1.to_dt) overlaps (t2.from_dt,t2.to_dt)
 11  /
FROM_DT   TO_DT     FROM_DT   TO_DT
--------- --------- --------- ---------
01-JAN-07 01-DEC-07 01-JAN-08 01-DEC-08
01-APR-07 01-AUG-07 01-OCT-07 01-FEB-08
01-APR-07 01-AUG-07 01-JAN-08 01-DEC-08
01-JUL-07 01-AUG-07 01-OCT-07 01-FEB-08
01-JUL-07 01-AUG-07 01-JAN-08 01-DEC-08
01-OCT-07 01-FEB-08 01-APR-07 01-AUG-07
01-OCT-07 01-FEB-08 01-JUL-07 01-AUG-07
01-JAN-08 01-DEC-08 01-JAN-07 01-DEC-07
01-JAN-08 01-DEC-08 01-APR-07 01-AUG-07
01-JAN-08 01-DEC-08 01-JUL-07 01-AUG-07
10 rows selected.
SQL> explain plan for
  2  with test_data as
  3  (select date '2007-01-01' from_dt, date '2007-12-01' to_dt from dual union all
  4   select date '2007-04-01' from_dt, date '2007-08-01' to_dt from dual union all
  5   select date '2007-07-01' from_dt, date '2007-08-01' to_dt from dual union all
  6   select date '2007-10-01' from_dt, date '2008-02-01' to_dt from dual union all
  7   select date '2008-01-01' from_dt, date '2008-12-01' to_dt from dual)
  8  select *
  9  from   test_data t1,
 10         test_data t2
 11  where  (t1.from_dt,t1.to_dt) overlaps (t2.from_dt,t2.to_dt)
 12  /
Explained.
SQL> set linesize 200
SQL> select * from table(dbms_xplan.display)
  2  /
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
----------------------------------------
Plan hash value: 1640239735
----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time  |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |     1 |    36 |    22  (37)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |       |
|   2 |   LOAD AS SELECT           |                             |       |       |            |       |
|   3 |    UNION-ALL               |                             |       |       |            |       |
|   4 |     FAST DUAL              |                             |     1 |       |     2   (0)| 00:00:01 |
|   5 |     FAST DUAL              |                             |     1 |       |     2   (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
----------------------------------------
|   6 |     FAST DUAL              |                             |     1 |       |     2   (0)| 00:00:01 |
|   7 |     FAST DUAL              |                             |     1 |       |     2   (0)| 00:00:01 |
|   8 |     FAST DUAL              |                             |     1 |       |     2   (0)| 00:00:01 |
|   9 |   NESTED LOOPS             |                             |     1 |    36 |    12   (0)| 00:00:01 |
|  10 |    VIEW                    |                             |     5 |    90 |     2   (0)| 00:00:01 |
|  11 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6602_EE7BE7EC |     5 |    90 |     2   (0)| 00:00:01 |
|* 12 |    VIEW                    |                             |     1 |    18 |     2   (0)| 00:00:01 |
|  13 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6602_EE7BE7EC |     5 |    90 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
----------------------------------------
---------------------------------------------------
  12 - filter((INTERNAL_FUNCTION("T1"."FROM_DT"),INTERNAL_FUNCTION("T1"."TO_DT"))OVERLAPS(INTERNAL
              _FUNCTION("T2"."FROM_DT"),INTERNAL_FUNCTION("T2"."TO_DT")))
26 rows selected.

So not only does it let you check two pairs of dates to see if they overlap, it lets you check if they do not overlap (pretty simple stuff), and it also accepts a date and interval expression in place of date pairs.

It doesn’t work for pairs of numbers though, it seems:

SQL> select * from dual
  2  where          (1,5)
  3        overlaps (3,8)
  4  /
where          (1,5)
                *
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

SQL>

Still, very cool! Undocumented, but still cool.

Does it make it more cool if it is undocumented?

About these ads

26 thoughts on “The “OVERLAPS” Predicate

  1. Makes it more unusable :) There’s an equivalent in Workspace Manager though.

    select wm_intersection (wm_period (sysdate-10,sysdate+10),
    wm_period (sysdate-1 ,sysdate+1 )) from dual
    where wm_overlaps (wm_period (sysdate-10,sysdate+10),
    wm_period (sysdate-1 ,sysdate+1 )) =1;

    http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28396/long_vt.htm#i1010273
    There’s also a WM_CONTAINS (period 1 within period 2) and a WM_INTERSECTION function (which returns the period of the overlap).

  2. The only other reference I found (did not look far) is in the Reserved words list. It seems somebody had a good project going over at Oracle but did not get the time to finish it.
    Very cool indeed.

  3. The code i’ve been using lately for check range A1-A2 and B1-B2 is:

    WHERE A2 BETWEEN B1 AND B2
    OR B2 BETWEEN A1 AND A2

    In our case, A1-A2 is a one month range, and instead of using the last moment of the month, we are using the first moment of the next month, in order to get around any granularity issues. This requires a final check:

    WHERE A2 BETWEEN B1 AND B2
    OR (B2 BETWEEN A1 AND A2 AND B2 < A2)

    But this FUNCTION would sure be welcome, if only to make it clearer.

  4. For many years I have had an OVERLAP_CNT(start_date1 date, end_date1 date, start_date2 date, end_date2 date) function in my CMP package, which is available on my website. This returns a 1 if the date range represented by the first two dates overlaps the date range represented by the second two dates, and a 0 if it does not. This allows you to use it in a sum() to count how many rows have an overlapping date range, or a where clause.

  5. Would you consider using OVERLAPS in production code? It might be useful when doing analysis on your data, but certainly if it’s undocumented there could be bugs associated with the implementation.

    I’m really curious about opinions on my question because 50% of the data transformation in the current ETL project I’m on involves comparing sets of records for time interval overlaps.

    Thanks!


  6. Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.6.0 - Production

    SQL> with test_data as
    2 (select date '2007-01-01' from_dt, date '2007-12-01' to_dt from dual union all
    3 select date '2007-04-01' from_dt, date '2007-08-01' to_dt from dual union all
    4 select date '2007-07-01' from_dt, date '2007-08-01' to_dt from dual union all
    5 select date '2007-10-01' from_dt, date '2008-02-01' to_dt from dual union all
    6 select date '2008-01-01' from_dt, date '2008-12-01' to_dt from dual)
    7 select *
    8 from test_data t1,
    9 test_data t2
    10 where (t1.from_dt,t1.to_dt) overlaps (t2.from_dt,t2.to_dt)
    11 /

    FROM_DT TO_DT FROM_DT TO_DT
    --------- --------- --------- ---------
    01-JAN-07 01-DEC-07 01-JAN-07 01-DEC-07
    01-APR-07 01-AUG-07 01-JAN-07 01-DEC-07
    01-JUL-07 01-AUG-07 01-JAN-07 01-DEC-07
    01-OCT-07 01-FEB-08 01-JAN-07 01-DEC-07
    01-JAN-07 01-DEC-07 01-APR-07 01-AUG-07
    01-APR-07 01-AUG-07 01-APR-07 01-AUG-07
    01-JUL-07 01-AUG-07 01-APR-07 01-AUG-07
    01-JAN-07 01-DEC-07 01-JUL-07 01-AUG-07
    01-APR-07 01-AUG-07 01-JUL-07 01-AUG-07
    01-JUL-07 01-AUG-07 01-JUL-07 01-AUG-07
    01-JAN-07 01-DEC-07 01-OCT-07 01-FEB-08

    FROM_DT TO_DT FROM_DT TO_DT
    --------- --------- --------- ---------
    01-OCT-07 01-FEB-08 01-OCT-07 01-FEB-08
    01-JAN-08 01-DEC-08 01-OCT-07 01-FEB-08
    01-OCT-07 01-FEB-08 01-JAN-08 01-DEC-08
    01-JAN-08 01-DEC-08 01-JAN-08 01-DEC-08

    15 rows selected.

  7. See section 14.1.5 in the FIPS standard. The O8 SQL reference notes partial compliance, but not that particular transitional. Which, oddly enough, includes a reference to timestamp, which many people are just now starting to struggle with.

    So I bet someone at Oracle has been thinking about this at least since the draft 1992 ANSI SQL standard started circulating.

    SQL standards. There’s a job I could not do.

  8. Pingback: Log Buffer #102: a Carnival of the Vanities for DBAs

  9. Pingback: Cool Undocumented OVERLAPS Predicate | Oracle

  10. I agree with Tim: it’s much cooler if it’s undocumented!

    As for using something like that in production… well, it depends on how clumsy or available the “official” options are. For example, I used COMMITSCN in production:
    http://tinyurl.com/2rmj3z
    However, I warned management that it was the only way to achieve their “must have” goal on an Oracle 7.3.4 database!

    “You can always tell the pioneers by the arrows in their backs”: I’ve had to pull a few outta my backside over the years.

  11. This is very cool thing.
    i am going to use it in 6 years old classic asp application.
    Good work.

  12. Example of same query as in message 8, but without useing overlaps, also you can define rules of overlapping by setting different compare rules (less or less&equal, above or above&equal):

    with test_data as
    (select date ‘2007-01-01′ from_dt, date ‘2007-12-01′ to_dt from dual union all
    select date ‘2007-04-01′ from_dt, date ‘2007-08-01′ to_dt from dual union all
    select date ‘2007-07-01′ from_dt, date ‘2007-08-01′ to_dt from dual union all
    select date ‘2007-10-01′ from_dt, date ‘2008-02-01′ to_dt from dual union all
    select date ‘2008-01-01′ from_dt, date ‘2008-12-01′ to_dt from dual)
    select *
    from test_data t1,
    test_data t2
    where t1.from_dt LESS_THAN t2.to_dt and t1.to_dt ABOVE_THAN t2.from_dt

    Plese delete my above messages -)

  13. Is it possible that the overlaps predicate is not supported by the driver, and gives an oracle internal error (ORA-00600)?

  14. What Oracle RDBMS really needs is a special built-in index (kind of like a unique index, but not quite) which would be able to reject records with overlapping time ranges.

  15. Pingback: Post Production

  16. overlaps is not really needed..

    with test_data as
    (select date ’2007-01-01′ from_dt, date ’2007-12-01′ to_dt from dual union all
    select date ’2007-04-01′ from_dt, date ’2007-08-01′ to_dt from dual union all
    select date ’2007-07-01′ from_dt, date ’2007-08-01′ to_dt from dual union all
    select date ’2007-10-01′ from_dt, date ’2008-02-01′ to_dt from dual union all
    select date ’2008-01-01′ from_dt, date ’2008-12-01′ to_dt from dual)
    select *
    from test_data t1,
    test_data t2
    where t1.from_dt t2.from_dt;

  17. with test_data as
    (select date ’2007-01-01′ from_dt, date ’2007-12-01′ to_dt from dual union all
    select date ’2007-04-01′ from_dt, date ’2007-08-01′ to_dt from dual union all
    select date ’2007-07-01′ from_dt, date ’2007-08-01′ to_dt from dual union all
    select date ’2007-10-01′ from_dt, date ’2008-02-01′ to_dt from dual union all
    select date ’2008-01-01′ from_dt, date ’2008-12-01′ to_dt from dual)
    select *
    from test_data t1,
    test_data t2
    where t1.from_dt < t2.to_dt and t1.to_dt > t2.from_dt;

  18. Joining the party late here, but… when I need to test whether a overlaps b, I use the predicate “greatest (a.start_date, b.start_date) ≤ least (a.end_date, b.end_date)”.

  19. The “problem” with OVERLAPS, and perhaps the reason it’s remained undocumented and unsupported for at least five years, is in the precise definition of “overlapping time intervals”. Or, more to the point, the LACK of precision in the definition of “overlapping time intervals”. Tell me – if two intervals intersect at an endpoint (for example, the interval (sysdate – interval ’1′ hour’, sysdate) and (sysdate, sysdate + interval ’1′ hour)) do they overlap or not? Quick, no fair peeking at other references – DO THEY or DO THEY NOT overlap??? Well, actually, the answer to this question depends on who you’re talking to, what their requirements are, the phase of the moon, the outside temperature, atmospheric pressure, humidity, whether a Democrat or Republican occupies the White House, AND…well, you get my point. To implement this, Oracle had to make A Hard-And-Fast Decision – and the Decision seems to have come down on the side of “these intervals don’t overlap”. Fine decision, I can’t complain about that decision, some of my best friends have made that decision – but doubtless out there in SoftwareLand SOMEBODY is going to pitch an unceasing bitch about That D*&#*#@ Decision, and decide to drop Oracle like a hot potato. And the one thing that Oracle loves more than anything is MONEY. REVENUE! LICENSE SALES! GOBS AND GOBS OF GLORIOUS MOULAH!!!! BU-WAH-HAH-HAH-HAH-HAH-HAH-HAH!!!! Ahem… And if someone could, let alone Would, drop Oracle for some (sound of teeth grinding) Other Product, just because of one little Decision – well, Oracle then aint gonna be a-makin’ that Decision! And thus, OVERLAPS remains in limbo – implemented but undocumented, alive but unseen, operational but unusable. Poor little feature…condemned to survive in obscurity… (sob!) OH, THE HUMANITY…!!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s