Re: IDS Feature Request List (including potential new requests).

bozon wrote: > In the hopes of improving my OTPR (On Topic Posting Ratio): > > >>Serge Said >>SELECT last_name, first_name, MI, UID >> FROM employee >> WHERE (last_name, first_name, MI, UID) > ("Bozon", "Utah", "Carl", 1023) >> ORDER BY last_name, first_name, MI, UID >> FETCH FIRST ROW ONLY> > >>(or use TOP, ROWNUM or whataver the DBMS supports to limit rows) > > >>Cheers >>Serge > > > Thank You, that is in one way around it, but does this work when the > min is in the subselect such as: > > select > * > from > employee_plans ep > where > employee_id = ? and > (plan_type, effective_date ) = (select min(plan_type,effective_date) > from employee_plans ep_s where ep_s.employee_id = ep.employee_id and > termination_date >= today ) ; Well, I would not use a join to begisn with. The reason for self joins in the presence of aggregates is typically because you wanttopick up columns which are nether groups, nor aggregated. The case you have here is very typical. Especially w.r.t. queue processing (ORDER table). SELECT * FROM employee_plans ep WHERE employee_id = ? AND termination_date >= CURRENT DATE ORDER BY plan_type, effective_date FETCH FIRST ROW ONLY But let's leave the self join for educational purposes: SELECT * FROM employee_plans ep WHERE employee_id = ? AND (plan_type, effective_date ) = (SELECT plan_type,effective_date FROM employee_plans ep_s WHERE ep_s.employee_id = ep.employee_id AND termination_date >= CURRENT DATE ORDER BY plan_type,effective_date FETCH FIRST ROW ONLY) will work in DB2 for LUW (DB2 supports tuple equivalency (=)) as well as ORDER BY and FETCH FIRST in subqueries). What about the plan? Well, as you note, it all depends on the indexing. Given an index on (termination_date, plan_type, effective date) you get away with one I/O. If you don't have that index a sort will need to be done. That sound worse than it is because it will be a "truncated sort" which, for 1 row returned is just as efficient as your MIN(). > Furthermore, I always think of "fetch first row only" and constructs > like it, as hacks (concatenating in the min is definitely a hack) that > are work arounds of limitations in "TRUE" SQL. When ever I see them or > use them, I am suspiscious of two things: that the developer doesn't > know what he/she is doing, and/or there really is something hard or > impossible to express in standard SQL. This is when I start thinking of > enhancements. In this case "fetch current valid plan excluding future > plans" is something hard to express in SQL. As you saw above it's not. ORDER BY and FETCH FIRST (or TOP, ...) have a stigma in SQL which is unjustified. The assumption is that when a user uses ORDER BY they expect processing in a certain ORDER (which is nonsense in an SMP and or MPP system) or that the nested result set is ordered (which is also nonsense with the exception of ORDER returned by teh cursor). FETCH FIRST has the assumption that some rows MUST be better than others and that "give me any row" is an evil request. Either way the combination of ORDER BY and FETCH FIRST is very powerful and IMHO not a workaround at all. > > Can others come up with queries that tuple comparisons and tuples in > aggreate functions would be helpful or is it just me? The standard example that I keep hearing is a hand woven scrollable cursor where I want to walk resubmit the same query over and over offset my the pagesize (#lines on by screen). My answer to that is typically to use a scrollable cursor to begin with. Cheers Serge -- Serge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab

Serge Said >The standard example that I keep hearing is a hand woven scrollable >cursor where I want to walk resubmit the same query over and over offset >my the pagesize (#lines on by screen). >My answer to that is typically to use a scrollable cursor to begin with. We can't use scrollable cursors because we have a stateless web application. We can't be the only one that has this kind of setup. Once a query is executed we never know which connection we will use to process the next user request. > serge said But let's leave the self join for educational purposes: SELECT * FROM employee_plans ep WHERE employee_id = ? AND (plan_type, effective_date ) = (SELECT plan_type,effective_date FROM employee_plans ep_s WHERE ep_s.employee_id = ep.employee_id AND termination_date >= CURRENT DATE ORDER BY plan_type,effective_date FETCH FIRST ROW ONLY) >> end serge we can't do this in Informix as far as I know you can't use first row only in the sub-select. (Well you can't use "fetch first row only" at all in Informix because that is not how it was implemented in informix. We use select first 1 * from <table>, but I know what you mean ;-) ) >serge As you saw above it's not. ORDER BY and FETCH FIRST (or TOP, ...) have a stigma in SQL which is unjustified. >end serge Can't do it in Informix as I stated above. The stigma is well deserved because it is different in every database and every database has its own rules about where and when it can be used. Also, it really bastardizes the whole mathmatics behind SQL. Where as, I think of tuple comparison as a natural part of relational calculus and operating on tuples a natural extension of that. Also, I am not sure how this bastardization affects proofs of correctness for SQL. >serge What about the plan? Well, as you note, it all depends on the indexing. Given an index on (termination_date, plan_type, effective date) you get away with one I/O. If you don't have that index a sort will need to be done. That sound worse than it is because it will be a "truncated sort" which, for 1 row returned is just as efficient as your MIN(). >end serge Sorry, I don't actually belileve this because sorting takes nlog(n) but scanning for the min only takes n. Since you tell it to sort I would think that it would have to sort and then get the first record. Of course the engine could recognize this construct and short circuit the sort and just scan for the min not being in a position to know this I can't comment on this. Thanks for your comments.

Reply to Serge: Also, row comparison is standard in SQL-92. >From the book "SQL for Smarties: Advanced SQL Programming" by Joe Celko (Couldn't find a "SQL for Bozon's") >>BEGIN QUOTE SQL-92 generalized the theta operators so they would work on row expressions and not just on scalars. This is not a popular feature yet, but it is very handy for situations where a key is made from more than one column, and so forth. This makes SQL more orthogonal, and it has an intuitive feel to it. >>END QUOTE If it is good enough for Joe, its good enough for me. :-p Plus it is part of the standard. And I think of max and min on row expressions as another handy natural extension to this. Finally, looking in the index of the book I don't see a single thing about "FETCH FIRST" which leads me to believe this is a non-standard construct which even enhances my "bastardization" arguement. Thanks (really, even if I am sounding harse it isn't meant that way and I do appreciate your expertise) Curtis

bozon wrote: > Reply to Serge: > > Also, row comparison is standard in SQL-92. > >>From the book "SQL for Smarties: Advanced SQL Programming" by Joe Celko > (Couldn't find a "SQL for Bozon's") > > >>>BEGIN QUOTE > > SQL-92 generalized the theta operators so they would work on row > expressions and not just on scalars. This is not a popular feature yet, > but it is very handy for situations where a key is made from more than > one column, and so forth. This makes SQL more orthogonal, and it has an > intuitive feel to it. > >>>END QUOTE > > > If it is good enough for Joe, its good enough for me. :-p Plus it is > part of the standard. > > And I think of max and min on row expressions as another handy natural > extension to this. > > Finally, looking in the index of the book I don't see a single thing > about "FETCH FIRST" which leads me to believe this is a non-standard > construct which even enhances my "bastardization" arguement. > > Thanks (really, even if I am sounding harse it isn't meant that way and > I do appreciate your expertise) Hang on, we have a misunderstanding here. There are two feature requests here. One is or generalized tuple comparison as defined in SQL-92 (I have no issue with that). The other request that was brought up was thsi funny MIN((tuple)) construct which is not in the SQL standard and nothing remotely similar exists. That is the one I do have a problem with because there are well known constructs that do the job. Also not ethat it is not teh problem of SQL that vendors sometiems choose proprietary yntax or support different features. By your argument 60% of all Informix customesr bastardize SQL because they use non ANSI databases. Also JOIN's would be bastardization of SQL given tehmany proprietary Syntax variations. How is an operation that truncates a set to the n-smallest/biggest tuples according to some rule non relational? It takes an unordered set of tuples and it returns an unordered set of tupples. How is it different from e.g. DISTINCT which also truncates a set, just in a different way.... Looks sound to me... Cheers Serge -- Serge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab

Serge Said> Hang on, we have a misunderstanding here. There are two feature requests here. One is or generalized tuple comparison as defined in SQL-92 (I have no issue with that). >End Serge Said Oh, I thought you were against both. Sorry about that. Still it seems like min((tuple)) is a natural consequence of the first feature, because essentially min is a short-cut for "compare" all of the values and give me the lowest one. >serge That is the one I do have a problem with because there are well known constructs that do the job. >end serge Nothing I see that is as elegant as integrating a tuple more closely with the language by allowing it to be used in more places where in the past you could only use a scalar value. >serge By your argument 60% of all Informix customesr bastardize SQL because they use non ANSI databases. >end serge I thought Informix non ANSI databases had more to do with logging and locking modes and transactions than SQL differences. I don't think 60% of my SQL wouldn't run on other platforms. Except if you are talking about DDL and then I agree that seems to really be where differences exist. >serge How is an operation that truncates a set to the n-smallest/biggest tuples according to some rule non relational? It takes an unordered set of tuples and it returns an unordered set of tupples. >end serge Let me try to explain what I mean by this. Partly because it is implemented differently in every SQL (see a post by Model-Bosch, Tilman which explains one problem http://groups.google.com/group/comp.databases.informix/browse_frm/thread/0de5ebbc0ff2a8b2?hl=en). Another thing that I find wrong with it is that it is needlessly limited because it isn't well integrated in most SQL's, such as in Informix where you can't put it in a subselect. <Putting on my abestos underwear> I actually prefer the way Oracle does this by having a virtual column. This integrates well because you have the full power of the language to operate on this column. If I only want to bring back the odd rows from a selection I can. I can basically do any comparison against row_number (as I think it is called) that my little brain can imagine. Of course having a virtual column is a hack but it works well because it is more orthoganal than "select first" constructs seem to be. Can you bring back only the odd records with DB2's construct? <Taking off my abestos underwear, I begin enjoying the nicely toasted marshmellows I thoughtfully put in my pockets before being inevitably flamed for saying something nice about Oracle on c.d.i.> >serge How is it different from e.g. DISTINCT which also truncates a set, just in a different way... >end serge Don't even get me started on DISTINCT as in "select DISTINCT". Ooops, you are too late now you are in trouble. ;-) < firing up flame thrower ;-)> I have seen more bad queries that were just wrong hidden by using the keyword DISTINCT. In fact at my last job I just assumed that the developer didn't know what he was doing when I saw "select distinct". I found it hiding so many cross products and bad joins or incomplete understanding of exactly what they wanted back from the database that I immediately reworked any query that I saw with that construct. One query was running in 10 minutes (64 way sun 10000 with an EMC SAN, so you had to really screw up to get an OLTP query to run in 10 minutes.) I picked it apart and found that the "select distinct" was hidding the fact that the developer didn't join properly and was returning the same rows 1000's of times but the "select distinct" made the developer think that he had written the query correctly. I took this person's SQL privileges away so he couldn't screw up my database anymore. I wrote his SQL for him until his boss realized that he was an idiot and gave him less work to do. (I think it was 100% less work to do.) So, I don't think it is much different because I still very carefully evaluate any query I see with that construct because it can so easily be misused and hide errors. <I suddenly run out of fuel.> Thanks for your input.

bozon wrote: > Serge Said > >>The standard example that I keep hearing is a hand woven scrollable >>cursor where I want to walk resubmit the same query over and over offset >>my the pagesize (#lines on by screen). >>My answer to that is typically to use a scrollable cursor to begin with. > > > We can't use scrollable cursors because we have a stateless web > application. We can't be the only one that has this kind of setup. Once > a query is executed we never know which connection we will use to > process the next user request. <SNIP> This is a perfect use for the 'Global Cursor ID' concept I proposed the other day. It would let you create the global cursor set once in one sessions and access it from any session that knows the ID. Art S. Kagel

Art S. Kagel > This is a perfect use for the 'Global Cursor ID' concept I proposed the > other day. It would let you create the global cursor set once in one > sessions and access it from any session that knows the ID. Server Side sharable cursors? That would be cool. Put that on my list of wants. It would save a bunch of problems.