Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!sdd.hp.com!elroy.jpl.nasa.gov!turnkey!orchard.la.locus.com!prodnet.la.locus.com!jfr
From: jfr@locus.com (Jon Rosen)
Newsgroups: comp.databases
Subject: Re: INFORMIX SQL functions
Keywords: informix sql
Message-ID: <19917@oolong.la.locus.com>
Date: 26 Nov 90 20:35:37 GMT
References: <1990Nov20.221658.19496@informix.com> <8335@pbhyf.PacBell.COM> <1990Nov22.210621.7930@tfic.bc.ca>
Organization: Locus Computing Corp, Los Angeles
Lines: 63

In article <1990Nov22.210621.7930@tfic.bc.ca> clh@tacitus.UUCP (Chris Hermansen) writes:
>
>(stuff deleted)
>
>Since ISQL is apparently designed to provide a simple, quick mechanism for
>rooting around in Informix databases, why isn't there a bit more in the
>way of, oh, say transcendental functions available in ISQL?  For example,
>I wouldn't mind being able to execute
>
>	select a, 1.30223 * (1 - exp(-0.023 * a)) ^ 2.39045 from foo
>
>I'm aware that ESQL-C allows one to add things like this to ISQL, but this
>strikes me as a) overkill, b) an expensive way to do it, and c) a way to
>end up with a screwed-up ISQL (or at least one that Informix support might
>look upon with suspicion in the heat of a service call).
>
>I understand that Ingres provides this kind of stuff, and I really don't see
>the problem with allowing users to get at standard UN*X functions like
>exp, log, j0 (yeah, there's one I use every day), etc.
>

Keep in mind that what you are asking for is a modification of the SELECT
statement syntax itself.  It is of course easy to allow either a 3GL or
4GL to apply the appropriate functions against returned data from a SELECT
query by taking each data value and applying the formula.  This could even
be done with ISQL except that ISQL is supposed to represent the exact
SQL syntax that would be used in a program.  I.e., "extending" SQL with
additional functionality is not the purpose of ISQL.  
 
Should additional functions be included in SQL?  Of COURSE!!! But the
*&@#(*($><&& people who defined SQL either knew NOTHING about the way
people actually use databases or didn't care.  Scalar functions such as
Log, Exp, Abs, Cos, etc. would be relatively trivial to add to the
database engine as well as to the language.  More importantly, on some
databases such as servers or database machines like Teradata, having
this functionality in the language is critical to performance.  Your
example is pretty simple since the actual amount of execution time 
to perform the operation is the same whether or not the data base
does the work... But, take this example:
 
   SELECT * FROM BLIVET
    GROUP BY X
    HAVING SUM(1 - EXP(-0.023 * A) > .5  (this may not make sense but 
                                          it is a good example anyway)
 
poses a serious problem.  Here, since the SUM of the complex expression
can't be taken by the database engine (it can't do the EXP function),
you have to retrieve all of the data and let the application do all
the work, i.e., the application becomes a data base engine itself.
 
YUCCCHH!!!
 
Is there hope?  Maybe... Mike Stonebraker at Berkeley has proposed
object-oriented extensions to SQL (no, not making SQL a full OODMBS,
just some simple extensions) that would allow user defined data types
and user defined methods or functions on those data types.  This is
sort of like the enhancement of Turbo Pascal to include objects.  It
is still Pascal but it is much more powerful.  Here, SQL would be
extendible to allow new functions on new datatypes (or old datatypes).
Ingres has already implemented some of these features in their new
release.  Hopefully, we will see more of this.
 
Jon Rosen