Path: utzoo!utgpu!news-server.csri.toronto.edu!rutgers!mailrus!uunet!dev!dgis!jkrueger
From: jkrueger@dgis.dtic.dla.mil (Jon)
Newsgroups: comp.databases
Subject: Re: Ease of SQL with an ugly schema
Message-ID: <923@dgis.dtic.dla.mil>
Date: 9 Jul 90 21:23:09 GMT
References: <10698@chaph.usc.edu>
Organization: Defense Technical Information Center (DTIC), Alexandria VA
Lines: 46

ajayshah@aludra.usc.edu (Ajay Shah) writes (my rewrites):

> table containing fields X Y and FLAG.  FLAG
> takes values zero or one.  One wishes to create the
> summation over the table of a variable Z defined as:
>	If (flag = 0) then
>		Z := X - Y
>	else
>		Z := X + Y

One way to do it:

	select sum(x + (y * ((2 * z) - 1))) from t;

The expression (y * ((2 * z) - 1)) is of course the 
transform usually written in algebraic form 2z-1, which
maps flag values {0, 1} to {1, -1}.  This takes a boolean
flag column and plugs it into a mathematical evaluation.

This is a kludge.  It works because of a convention for
representing boolean values in integers; break the convention
and the database will be none the wiser, but the result of
the query becomes unpredictable.

An additional penalty is paid in performance; the query as written
hides a useful boolean expression:

	((flag) and (z = (x - y)) or ((flag) and (z = (x + y))))

in some useless arithmetic calculations (above).  Given the math, the
query optimiser is not likely to find any shortcuts.  Although
aggregates without selects aren't likely to get optimized much.

But the performance problem is a symptom of the correctness problem.
Expressing the query need not be so prone to kludges.  Improved
database design might represent things without flags.  Or the problem
may be a poor fit for the data model.  Or the object may be susceptible
to representing as an ADT which returns values without all the math
or logic in each query.  The point here is, yes you can write the
query, no it's not a good solution, but without knowing more about
your problem one can't say how to get a better solution.

-- Jon
-- 
Jonathan Krueger    jkrueger@dtic.dla.mil   uunet!dgis!jkrueger
Drop in next time you're in the tri-planet area!