[Info-Ingres] Few questions concerning Ingres 2006

[Marek.Patrzek at gmail.com]

On 10 Kwi, 21:38, c... at usenet.invalid wrote:
> On Tue, 10 Apr 2007 08:21:08 +0100, "Roy Hann"
>
>
>
> <specia... at processed.almost.meat> wrote:
> ><Marek.Patr... 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
> ced... at bcs.org.uk

First of all - thanks for all replays.

Chris, everything you said is nothing new to me, however ! I just have
to use notation that I presented in my previous post - nested/sub
query that is. And Roy, I have no idea, how did you manage to do that,
as I always end up with "Table "select" does not exists or is not
owned by you"... at first, I thought that I can put all the blame on
JDBC, but than, I executed my query in a "native" way, using official
ingres 2006 RDBMS Release 2 and got same results.

Well than - what can possibly be wrong with my version of ingres, that
it DOES NOT support subqueries ?

A new issue - a bit off topic. I made a view which had casting
function (from varchar to integer) in the select statement. Everything
was fine, until I wanted to join my view with another table using
table.column which was an integer to some view.column which - in
theory was an integer too. Again - all I got was an error msg saying
that I got incompatible data types... I can assure you, that all the
data in my view was easily casted to integer. The thing is, I than
created the view WITHOUT casting and than in my join clause I added
casting function and everything was fine.

To sum up - it seems that Ingres omits somehow cast functions and
execute the select without them, so the question is - why ? Imho,
values AFTER the cast functions should be returned, or am I wrong ?