Yet another problematic query

Our environment : Sun SparcServer 10/512 + Solaris 2.3 + OnLine dynamic server 6.00.UE1 A week or so ago, I posted a question regarding some query, with multiple levels of nesting, returning an incorrect result. (I got only one reaction of someone who tested the query in SE 5.03 on a HP, and got the correct answer ...). Now, I came across another query, of a totally different kind, which also yields a completely wrong result. Below, I included the query, and the table involved : TABLE USED (only the relevant columns are shown) : ---------- deliveries : jno gno quantity o235 g001 3 o235 g008 3 o235 g004 2 o235 g005 1 o236 g011 5 o236 g013 3 o236 g011 2 o236 g006 1 o236 g009 1 o237 g002 5 o237 g007 5 o238 g003 10 o238 g005 2 o238 g006 1 o239 g007 3 o239 g008 2 o240 g001 1 o240 g009 1 o240 g011 2 jno (char(4)) : job# address (....) : address where goods have to be delivered (not shown here) (possibly multiple addresses for each job) gno (char(4)) : goods# (the same goods# can appear more than once for the same job#!) quantity (smallint) : quantity of goods# to be deliverd at address QUERY : ----- select jno, gno, sum (quantity) from deliveries dv group by jno, gno having sum (quantity) >= all (select sum (quantity) from deliveries where jno = dv.jno group by gno) RESULT : ------ jno gno (sum) o235 g001 3 o235 g008 3 o235 g004 2 <-- o236 g011 7 o236 g013 3 <-- o237 g002 5 o237 g007 5 o238 g003 10 o238 g005 2 <-- o239 g007 3 o239 g008 2 <-- o240 g011 2 The query should give, for each job, the job# and the goods# of the goods of which the largest *total* quantiy has to be delivered (possibly more than one goods# for a given job#, but then necessarily with the same total quantity! It is clear that the rows marked with "<--" above, should NOT appear in the result!? Moreover, if I delete some jobs from the table 'deliveries', some of the redundant rows (corresponding to other jobs) disappear from the result. I also tested with some extra rows in 'deliveries' (extra jobs) and then some (other) jobs just disappeared from the result (although each job should appear in it at least once). Isn't this a strange behaviour ?! Did any one else experience problems with such kind of queries (queries with grouping and a correlated subquery in the having-clause)? Is this perhaps a known bug ? (I do know that I can formulate another query to obtain the result I want, but this seemed to be the shortest and most straightforward one to me) Thanks in advance for any reactions. Ria. ----------------------------------------------------------- Ria Vandenberghe Ria.Vandenberghe@rug.ac.be UNIVERSITY OF GHENT (BELGIUM) - Computer Science Laboratory Technologiepark-Zwijnaarde 9, B-9052 ZWIJNAARDE, BELGIUM tel: +32/9/264.55.10 fax: +32/9/264.58.42