Re: INTERVAL type: SQL92 implementation

(back on list)

> As far as I can see, it is the same. My examples come from Cannan and Otten
> on SQL92, but I read the spec for SQL99 and I can't see any obvious
> change, except that INTERVAL YEAR TO YEAR (and any other X TO X) is no
> longer allowed. (I take it you have a copy of SQL99?)

We have a copy of an SQL99 draft which seems to be reasonably complete.
afaik we haven't come across an actual released version. Let me know if
you want me to forward it; perhaps it is on the ftp or web site?

> >o We need to figure out how to parse it in gram.y. I looked at it a
> >little bit (a couple of hours?) and it was not obvious how to get rid of
> >shift/reduce problems.
> I don't have any deep knowledge of yacc/bison...yet.

Oh, you will... ;)

> I feel unhappy about multiplying interval types like that. I would rather
> restrict it to interval (as now), intervalym (YEAR TO MONTH) and intervalds
> (DAY TO SECOND), with the parameters determining the interval range.

But that means (perhaps?) that you can't define a column INTERVAL DAY,
since internally everything would accept all values DAY TO SECOND. I
know you proposed setting an internal mask, but that would be per-value,
not per-column, so it doesn't help. The attribute system may not be much
help here either, unless we somehow generalize it (to allow types to
keep their own impure storage?).

> otherwise we would have 13 new types and would need to make conversion
> functions for all of them. SQL99 says that YEAR TO MONTH and DAY TO SECOND
> are incompatible; the results of other combinations give the combined
> maximum range: DAY TO HOUR + HOUR TO SECOND = DAY TO SECOND, but I don't
> see this as being outside the capabilities of the 2 new types I propose.
> Is there some reason in the internals why it would be necessary to create all
> 13 new types?

3 for YEAR/MONTH, and 10 for DAY/HOUR/MIN/SEC to get all the
combinations. If you convert to a "super interval" for internal
operations, then you may only need the I/O and conversion functions,
which would be easy.

My example still holds as a test case to evaluate an implementation
afaik:

create table t (id interval day);
insert into t(id) select interval '2' day + interval '05' minute;

will need to be stored with only the day field non-zero. Certainly that
column can not be allowed to end up holding quantities other than
integral days, right?

Also, the column defined above has no ability to enforce the "day only"
character of the column if we are using only a single type and without
help from the type or attribute system already in place.

> As I said above, I feel that this is to over-complicate things...

Hmm, but it may be a required minimum level of complication to meet the
spec. Given the arcane syntax and limited functionality (note the
gratuitous editorializing ;) it probably isn't worth doing unless it
gets us on an obvious path to SQL99-compliant functionality.

Also, it is one of the edge cases for SQL99, so even if it is a pain to
do we are only doing it once. They couldn't possibly come up with
anything uglier for SQL0x, could they? Please say no...

...
> the distinction between YEAR TO MONTH and DAY TO SECOND is one that is
> present in the existing interval type, so perhaps we could even get away with
> only one new type?

Not sure what you mean here. The existing type does keep years/months
stored separately from the days/hours/minutes/seconds (a total of two
internal fields) but SQL99 asks that these be kept completely away from
each other from what you've said. Does it define any arithmetic between
the two kinds of intervals?

- Thomas