Re: Extending my question. Was: The relational model and relational algebra - why did SQL become the industry standard?

From: Paul Vernon <paul.vernon_at_ukk.ibmm.comm>
Date: Wed, 26 Feb 2003 18:23:47 -0000
Message-ID: <b3j115$kdq$>

"Paul" <> wrote in message
> "Paul Vernon" <paul.vernon_at_ukk.ibmm.comm> wrote in message
> > > As long as the polar representation has a
> > > representable value in that area, I see no problems. Even if the polar
> > > representation has no value in that area, but a point near that area I'm
> > > still okay with it.
> >
> > But what happens if many polar points are 'near' that area?. If you can
> > a way of having exactly one polar point 'near' every Cartesian point (and
> > versa), then ok.
> When we have domains of "rationals" or "reals" in a database, really
> we're just talking about domains of integers with the scale shifted.
> So for theoretical purposes we can ignore any basic number domain
> except integers.


> So the domain that is the subset of the plane of (cartesian) points
> (n,m) where n,m are integers between some set of values is surely a
> *different* domain than that of (polar) points (r,t) where r,t are
> integers. Here I'm assuming we can specify the angle t to an arbitrary
> scale. In other words they aren't just different representations of
> the same domain (unless you have a logical model that can deal with
> uncountable infinities).


> If the function f maps one represention of a domain to another, then
> surely we want f(a*b) = f(a)*f(b) for any operation *? Can anyone give
> an example of such a mapping between the polar and cartesian domains
> defined above that works for standard addition and multiplication?
> For example the polar domain isn't closed under conventional addition.
> Though neither I suppose is the cartesian domain if you consider that
> standard (non modulo) addition is undefined for large enough values.
> So the question is, should the logical model be allowed to include
> things (like infinities) that can't be realised in the physical
> implementation? Do we need two logical models: one that is purer and
> corresponds closer to "reality" and another slightly modified version
> that corresponds to the limitations of finite machines?

I would not vote for seperate models, however is the notion of an 'abstract' type one way of resolving this?

> For example to our set of integers between -1000 and +1000 we could
> add two more elements +INF and -INF say. Although then things like "a
> + b - b = a" wouldn't necessarily be true but at least we'd have
> closed operations. Are there any other ways to neatly deal with finite
> subsets of integers apart from using modulo arithmetic?

I see two, not incompatable, ways

  1. RAISE_ERROR in the cases where the outer bounds or bounds of precision are broken.
  2. Have 'approxamate' but closed versions for every operator that otherwise sometimes RAISE_ERRORs just too darn often to be used in a given query.

E.g. with type INTEGER_+/-_10^3

OPERATOR / (A INTEGER_+/-_10^3 ), B INTEGER_+/-_10^3 ) RETURNS (INTEGER_+/-_10^3){
/* Real divide. Raises an error if B is not divisable by A */

OPERATOR // (A INTEGER_+/-_10^3 ), B INTEGER_+/-_10^3 ) RETURNS (INTEGER_+/-_10^3)
/* Approximate divide. Returns the integer division of A over B */

then we could declare to the DBMS that

    A / B * B == A

but not that

    A // B * B == A

So for applications where A / B * B == A is important, use the '/' and live with any RAISE_ERRORs, otherwise just use '//'.

As a side issue, I then wonder if it would be OK for an optimiser to replace 8 / 3 * 3 in an expression with the value 8? I say yes. I mean, SQL is fine with optimising away run time errors, so it must be OK ;-):

C:\SQLLIB>db2 "select * from t where 1=1 OR 1/0 = 0" SQL0801N Division by zero was attempted. SQLSTATE=22012

C:\SQLLIB>db2 "select * from t where 1=1 OR ABS(1)/0 = 0"



  1 record(s) selected.

> Maybe we need an implementation of the integer domain in DBMSs that
> will cover ALL integers i.e. given enough machine space any integer
> can be stored. So this would deal with the countably infinite in some
> logical sense, but we'd still have problems with the polar-cartesian
> mapping (which requires uncountable infinities).

I'm with Jan. Our database logical model needs to be discrete and finite. I think all our (non abstract) types need to be finite and our model just need to get over it and deal with it.

For queries, I could comprehend delaying the specification of the size of a type until the query compile time, at which point the DBMS decides upon a maximum amount of storage that the query can consume, and from that calc (plus say requested response time) fixes the size of any types in the query as needed. In other words I could accept a type whose upper bound was 'storage' just as long as each time that type was used, a particular value for that bound was fixed. What I do not accept in our database logical model is any concrete type whose size is 'infinite'. Such a type is just going to RAISE_ERROR (of some kind) when any use of it exceeds the storage capacity of the machine anyway, so there is no logical difference between an 'infinite' type and a 'storage' type fixed at query time.

Paul Vernon
Business Intelligence, IBM Global Services Received on Wed Feb 26 2003 - 19:23:47 CET

Original text of this message