Re: View challenge

From: J M Davitt <jdavitt_at_aeneas.net>
Date: Sun, 13 Aug 2006 01:59:54 GMT
Message-ID: <uIvDg.48560$vl5.16967@tornado.ohiordc.rr.com>

paul c wrote:

> J M Davitt wrote:
> 

>> Bob Badour wrote:
>>
>>> J M Davitt wrote:
>>>
>>>> frebe73_at_gmail.com wrote:
>>>>
>>>>>> ...
>>>>
>>>> You're asking for more rows - not just different
>>>> representations of existing data in existing rows.
>>>>
>>>> The best row-maker we've got is the cartesian
>>>> product - but that won't "fill-in" any gaps: the
>>>> only values you see "output" are representations
>>>> of the "input."
>>>>
>>>> If you change the requirements so that that the
>>>> interval [valid_from, valid_to] is constant - or,
>>>> at least, limited to some reasonable value - and
>>>> can presume that intervals for individuals don't
>>>> cover each other, you can cobble together a bit
>>>> of cheese that UNIONs a bunch of SELECT-crafted
>>>> rows. Like the way Celko would do it.
>>>
>>>
>>>
>>> Or he could use a closure to calculate the necessary series of
>>> integers or dates.
>>
>>
>> Exactly right. But can it be done in MySQL's SQL?

> 
> 
> Or, is this a good reason for an implementation that supported <NOT> ala 
> TTM, eg., take the product of the complement of two empty relations and 
> theta join with the desired range?  Optimizer/compiler would have to be 
> smart enough to not materialize the complete complements, I guess.
> 
> p

[Paul, you're killing me: the complement of nothing, indeed.]

Here's the rub: there is nothing holding all values of "the specified range." His question described an instant and a period which are to be used to generate some intervals which are then matched to some others.

If there existed a relation holding all the instants and operations on intervals were available, he could do this with query language. But there isn't and there aren't, so he can't. (And the last thing I want to hear is, "Oh, no: SQL has INTERVALs. It says so in the book and the standard!")

Can it be done in procedural language? Absolutely. Given SQL's limits, that's the way to do it. Received on Sat Aug 12 2006 - 20:59:54 CDT