clh@tfic.bc.ca (Chris Hermansen) (11/23/90)
It's gratifying for a long-time Informix (ISQL) user to see the interest Informix people take in this newsgroup, and also the generally high quality of their responses. With that in mind, I have a small question to pose to those folk, and anyone else who feels interested enough to comment. 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. I brought this issue up with some nice person in a service call one day, and was advised to send a letter to some high muckey-muck at Informix. Presumably my letter got filed in the round file; I certainly never received a response. To sum up, please don't say "4GL does it" or "ESQL-C" does it, because ISQL is a good solution (in terms of price, user-friendliness, etc) for our technical folks who spend long days prodding data around. It's just missing that extra little bit... Thanks for your consideration, everyone. Chris Hermansen Timberline Forest Inventory Consultants Voice: 1 604 733 0731 302 - 958 West 8th Avenue FAX: 1 604 733 0634 Vancouver B.C. CANADA clh@tfic.bc.ca V5Z 1E5 C'est ma facon de parler.
jfr@locus.com (Jon Rosen) (11/27/90)
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
mao@stinson (Mike Olson) (11/27/90)
In <19917@oolong.la.locus.com>, jfr@locus.com (Jon Rosen) discusses the need for complex functions generally, and transcendentals specifically, inside his database engine. he points out that sql doesn't provide much that's useful for serious number-crunchers. He then says > 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. which is right; stonebraker's postgres system uses the postquel query language, which supports user-defined types and procedures. we've gone to a fair amount of trouble to guarantee closure, so functions can take tuples and return tuples (or sets of tuples, in a future release). this is to keep the theoreticians out there from foaming at the mouth. similar extensions are possible in other query languages. it isn't very hard to come up with syntax to permit invocations of user functions. what is hard (and what i suspect tripped up the sql committee) is how to store functions, load them, provide strict access controls (very hard if the user's function runs in the engine's address space), check types rigorously, and so on. letting the user write code that the db engine executes is a major value to add to a system, and that sort of value doesn't come cheaply. consider this example from rosen's message: > 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) this is a monster to plan and optimize. you have no idea what this mysterious EXP function is going to do, assuming that a user wrote it for you. it may do a sequential scan of the ten biggest tables in your db for every tuple you call it with; it may just return immediately. it may compute ackerman's function on tuesdays. you'd like to know whether you can cache the return value, so you don't have to call it every time. does it have side effects? obviously, if you're going to do this right, you have to solve a lot of programming language problems, and a lot of database system problems. now it's soapbox time, so you might want to skip this paragraph: the reason that sql isn't extensible is that sql is designed to be the least common denominator among query languages. its syntax and semantics were designed by a committee made up of the major players in the market. if some set of them didn't feel they could implement a feature (or didn't want to give their competitors, who could implement it faster, any competitive advantage), then the feature was excluded from the language. those of us in academia can all wear white dresses when we get married, because we choose our query languages for their power and expressiveness, and not for the fact that they come in blue boxes. all those files out there with the group by's and the having's and the correlated subqueries drive me nuts. of course, i understand the trade-off. i like postquel a lot, but that means that i have a whole lot of scripts that aren't going to run on anybody else's database system. mike olson postgres research group uc berkeley mao@postgres.berkeley.edu
clh@tfic.bc.ca (Chris Hermansen) (11/29/90)
In article <19917@oolong.la.locus.com> jfr@locus.com (Jon Rosen) writes: >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. I guess what bugs me generally is that ISQL does have lots of extensions to ANSI SQL, otherwise (as you have noted in previous postings), it would be more or less useless. What's more, ISQL 4.xxx has several functions included already, including "DATE" functions. According to the Informix "Green Card" (boy, I sure dated myself there!), these are already extensions to ANSI Standard SQL. DATE functions, for !@#$%^ sake!!! Yessir, let's let the user convert that character string to a date. That'll make 'em really happy; talk about convenience. > >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 Ahem; Turbo Pascal isn't much like Pascal in one sense; it's MISSING some crucial things from standard Pascal (like GET(), PUT(), lazy I/O). But your point is well taken. I say, put the extensions in and let the user beware. Just don't take standard things out! As a side point, a nice person from Informix dropped me a line saying that there is a pipeline that these kinds of things can get into, so LISSEN UP OUT THERE!!!! If you're an Informix user and you're cranky because you CAN convert "5/4/80" into a date but CAN'T compute LN(2.718), SAY SOMETHING!!!! Chris Hermansen Timberline Forest Inventory Consultants Voice: 1 604 733 0731 302 - 958 West 8th Avenue FAX: 1 604 733 0634 Vancouver B.C. CANADA clh@tfic.bc.ca V5Z 1E5 C'est ma facon de parler.
jfr@locus.com (Jon Rosen) (11/30/90)
In article <1990Nov28.222220.20110@tfic.bc.ca> clh@tacitus.UUCP (Chris Hermansen) writes: > >(Stuff deleted about SQL and SQL functions) > >DATE functions, for !@#$%^ sake!!! Yessir, let's let the user convert >that character string to a date. That'll make 'em really happy; talk >about convenience. > >But your point is well taken. I say, put the extensions in and let the >user beware. Just don't take standard things out! > One more point... Your comment on date functions is reasonable from your prospective as a person interested in numeric processing... However, the SQL standards mostly came from IBM's work in the 70's and into the 80's in DB2 and as most of us know, IBM's bias is commercial data processing, not numerical analysis... After all, that is where they sell most of their iron... IBM added a complete set of DATE and TIME functions to DB2 pretty early... This allows commercial users to do things like compare dates accurately, add days to a date (for things like payment aging and checking if accounts are delinquent) which are about as important to commercial users as transcendental functions are to engineers and scientists... So, while I understand your frustration with most SQL's (and particularly Informix's) lack of numeric functions, don't flame the poor commercial user because s/he just might respond: "Cosine? Oh sure, just what we need, a way to turn a number from 0-360 into a number from 0-1!" The reality is we need ALL KINDS of function capabilities in SQL. I hope the standards guys are listening... Jon Rosen
clh@tfic.bc.ca (Chris Hermansen) (12/01/90)
In article <20081@oolong.la.locus.com> jfr@locus.com (Jon Rosen) writes: >In article <1990Nov28.222220.20110@tfic.bc.ca> clh@tacitus.UUCP (Chris Hermansen) writes: >> >>(Stuff deleted about SQL and SQL functions) >> >>DATE functions, for !@#$%^ sake!!! Yessir, let's let the user convert [stuff deleted] >One more point... Your comment on date functions is reasonable from your >prospective as a person interested in numeric processing... However, >the SQL standards mostly came from IBM's work in the 70's and into the >80's in DB2 and as most of us know, IBM's bias is commercial data >processing, not numerical analysis... After all, that is where they >sell most of their iron... IBM added a complete set of DATE and TIME >functions to DB2 pretty early... This allows commercial users to do >things like compare dates accurately, add days to a date (for things >like payment aging and checking if accounts are delinquent) which are >about as important to commercial users as transcendental functions are >to engineers and scientists... So, while I understand your frustration >with most SQL's (and particularly Informix's) lack of numeric functions, >don't flame the poor commercial user because s/he just might respond: >"Cosine? Oh sure, just what we need, a way to turn a number from 0-360 >into a number from 0-1!" I agree to some extent; I must admit I've never seen a financial type compute a cosine. However, there are PLENTY of financial types of analysis that require the use of exp() or ln() or most especially sqrt(). >The reality is we need ALL KINDS of function capabilities in SQL. I hope >the standards guys are listening... Exactly! The message to get across to DBMS designers is that ad-hoc query tools like ISQL (as opposed to full-fledged design systems like 4GL) are going to get used in an ad-hoc fashion, and flexibility is the key to that use. I think the focus is important, too. One of the things I like most about Informix's products is that they don't try to make one "all things to all people" product - thus I don't have to learn all the ins and outs of a super-duper backend just to muck with some data (for example). So: look at SQL. It's a relatively simple (primitive??) query language. Let's consider its suitability for use by a wider audience, but let's not confuse that with turning it into a real-time programming language suitable for tracking missiles. >Jon Rosen Thanks for your time & interest, people. Chris Hermansen Timberline Forest Inventory Consultants Voice: 1 604 733 0731 302 - 958 West 8th Avenue FAX: 1 604 733 0634 Vancouver B.C. CANADA clh@tfic.bc.ca V5Z 1E5 C'est ma facon de parler.
segel@balr.com (Michael Segel) (12/01/90)
In article <1990Nov28.222220.20110@tfic.bc.ca> clh@tacitus.UUCP (Chris Hermansen) writes: >As a side point, a nice person from Informix dropped me a line saying that >there is a pipeline that these kinds of things can get into, so > > >LISSEN UP OUT THERE!!!! > > >If you're an Informix user and you're cranky because you CAN convert >"5/4/80" into a date but CAN'T compute LN(2.718), SAY SOMETHING!!!! Well, Comerical Data Bases have been know to be lax on the MATH side of things. For example, you now have BLOBS. Yet you don't have the math to take advantage. C'est La Vie. These products offer a C interface, so you can get to the math functions. Now if you want a gripe, Informix needs to fix their C interface just a tad. (I wouldn't call them bugs, just design deficiencies) -Mike #include <std.disclaimer>