Re: Hotel Booking dates subquery

"Steve Kass" <skass_at_drew.edu> wrote:
: Good point - OVERLAPS (if available) is probably more readable, and it
: also handles the use of NULL for end_date to mean a booking from
: start_date that does not end. That might be unlikely for a hotel
: reservation perhaps, but very handy in some situations. As pointed out at
: http://www.pgro.uk7.net/fp2a.htm,
: OVERLAPS should be implemented so that
:
: (ay,dy) OVERLAPS (ax,dx)
:
: is equivalent to
:
: (ay > ax AND (ay < dx OR dy < dx)) OR
: (ax > ay AND (ax < dy OR dx < dy)) OR
: (ay = ax AND ay IS NOT NULL AND ax IS NOT NULL)

Very unusual, IMO. If NULL's are not allowed then dy<dx is excess (ay<dy, so ay<dx implies dy<dx). dx<dy is excess too... If NULL's are allowed (which was your point, I think), then consider the following example (for simplicity, I'll use plain numbers, not dates):

(ay, dy) = (1, NULL)
(ax, dx) = (2, 3)

Obviously, those two intervals do overlap, but

(ay,dy) OVERLAPS (ax,dx)

returns false, What is more peculiar about this, if, say, dx=NULL than both ay<dx and dy<dx are false. Actually, I don't know what exactly SQL92 says about comparing with NULL's, so I might be completely wrong. MSSQL (sorry, I won't do it again :) ) supports two different behaviours. One is some kind of three-state-logic (TRUE, FALSE, UNKNOWN), and the other is "everything < NULL = FALSE". I'm writing this offline, and haven't visited your link yet, but I'll do it as soon as I get connected.

regards,
Damjan S. Vujnovic

University of Belgrade
School of Electrical Engineering
Department of Computer Engineering & Informatics Belgrade, Yugoslavia

http://galeb.etf.bg.ac.yu/~damjan/ Received on Mon Dec 02 2002 - 23:24:58 CET