[Info-Ingres] Few questions concerning Ingres 2006

[chris at usenet.invalid]

On Tue, 10 Apr 2007 08:21:08 +0100, "Roy Hann"
<specially at processed.almost.meat> wrote:

><Marek.Patrzek at gmail.com> wrote in message 
>news:1176160545.998886.96220 at p77g2000hsh.googlegroups.com...
>> On 9 Kwi, 21:23, "Roy Hann" <specia... at processed.almost.meat> wrote:
>>> <Marek.Patr... at gmail.com> wrote in message
>>>
>>> news:1176138756.104459.54830 at w1g2000hsg.googlegroups.com...
>>>
>>> >> Subqueries in the FROM clause are supported as of Ingres 2006 Release 
>>> >> 2,
>>> >> and
>>> >> they require the use of the correlation name in the target list.  Try:
>>>
>>> >> SELECT tmp.* FROM...
>>>
>>> >> Roy
>>>
>>> > Thanks for your replay, I will try that on tuesday.
>>>
>>> > However, as I read that in the official sql reference, is using
>>> > aggregate functions in the subquery forbidden ?
>>>
>>> No, definitely not.  Apart from the HAVING clause, a subquery is the most
>>> obvious place to expect to be abke to use an aggregate.  Do you have a
>>> specific query in mind?
>>>
>>> Roy
>>
>> Well I do, however it's hard to put you in the picture without having
>> exact schema. I try to explain giving similar example.
>>
>> Let's assume, that we have table that holds info concerning
>> correspondence (like ID, signature, author etc.), a table that holds
>> attachments to correspondences (ID, correspondence_id, size etc).
>>
>> I want to know how show all data for a specific correspondence and
>> size of its attachments. The simplest way, to that is by doing:
>>
>> SELECT
>> correspondence.*,
>> ata_cnt.cnt
>> FROM
>> correspondence
>> JOIN
>> (
>> SELECT
>> correspondence_id,
>> SUM (size) AS cnt
>> FROM
>> attachment
>> GROUP BY
>> correspondence_id
>> ) ata_cnt ON correspondence.id = ata_cnt.correspondence_id
>
>That is exactly correct.  Here's what happens when I run an indentically 
>formed query on a toy database that I happen to have handy (this should be 
>displayed using a fixed pitch font):
>
>INGRES TERMINAL MONITOR Copyright 2007 Ingres Corporation
>Ingres 2006 Release 2 Microsoft Windows Version II 9.1.0 (int.w32/119) login
>Tue Apr 10 08:00:11 2007
>...
>
>* \p
>select p.*,x.total
>from parts p
>join
>(  select partnr,sum(qty_on_hand) as total
>   from parts_onhand
>   group by partnr) x
>on p.partnr = x.partnr
>continue
>* \g
>Executing . . .
>
>
>+-------+----------------+-----------+------+------+-------+
>|partnr |description     |unit_cost  |produc|catego|total  |
>+-------+----------------+-----------+------+------+-------+
>|     10|Widget          |    124.500|WIDGET|FG    |     23|
>|     15|EuroWidget      |    138.270|WIDGET|FG    |     27|
>|     27|SuperWidget     |           |WIDGET|FG    |      0|
>|     52|Gadget          |    376.000|GADGET|FG    |      2|
>...
>|  27027|Fastener        |      0.025|ALL   |RAWMAT|    855|
>|  27298|Glue            |      0.350|ALL   |RAWMAT|     50|
>|  27312|Zirconium Sheet |     99.950|GADGET|RAWMAT|      5|
>+-------+----------------+-----------+------+------+-------+
>(15 rows)
>continue
>*
>
>> Please, do not focus @ this query's efficiency, as it's not this
>> issue's clue :)
>
>I never get hung up on the "efficiency" of a query.  In *principle* there is 
>no such thing as an efficient query, only a correct specification of the 
>required result, or an incorrect one.  Ingres is actually very good at 
>realizing that principle.  It is quite rare that Ingres produces a bad query 
>plan because of the way the SQL was written.  If it produces a bad plan 
>there are usually other reasons and therefore other ways of fixing the 
>problem.  If you approach Ingres thinking *you* have to solve the efficiency 
>problem you will end up wasting a lot of time.  With Ingres you worry about 
>how the SQL was written when all else fails.  (Which is not to say that you 
>have a license to write crap SQL.  My point is that if there is a clear way 
>to write what you want, and a less clear way that you fondly imagine is more 
>efficient, you will very often be wrong and you will have achieved nothing 
>except you will have made your code harder to read.)
>
>> All I can say, is that, this particular way of
>> retrieving data is the way it has to be done, as my real life case,
>> which is far more complicated, force it.
>
>Roy 
>
Gentlemen,

Let us step back and look at what is trying to be achieved here:

Roy, Unless I am missing something, do you not get the same results
from:
	select p.*, total=IFNULL(SUM(po.qty_on_hand), 0)
	from parts p LEFT JOIN parts_onhand po ON p.partnr = po.partnr
	group by p.partnr, p.col2, p.col3
\g

and Marek could try:

	SELECT corr.*, cnt=SUM(att.size)
	FROM	correspondence corr
	LEFT JOIN attachment att
		ON corr.correspondence.id = att.correspondence_id
	GROUP BY corr.correspondence.id, corr.col2, corr.col3, ....;

All with nothing more complicated than an outer join (so I do not even
have to think about whether the 'table' created by the subquery is
fully instantiated before it is used or whether the query optimizer
turns it into joins).

Chris Dawe
-- 
Ingres Consultant
cedawe at bcs.org.uk