RE: [Firebird-devel] Aggregate doesn't work inside UDF (new case) more clear

[Claudio V. C. <cv...@us...>]

> -----Original Message-----
> From: fir...@li...
> [mailto:fir...@li...]On Behalf Of
> Christian Pradelli
> Sent: Miércoles 5 de Diciembre de 2001 8:07
>
> BUT the following combination DOESN'T work:
>
> SELECT CUSTOMER, ABS(SUM(TOTAL))
> FROM SALES
> GROUP BY CUSTOMER, DIVIDE(ABS(TOTAL),TOTAL)
> // this is the like the last query but returns all values like positives
>
> invalid column reference

At first glance, it's invalid. The grouping happens on a different UDF than
the SELECT part. However, what makes the case special is the presence of
aggregate functions. I regret to accept that's a valid construction. I don't
know what Neil may suggest, but the only idea I have is:

- Given a concatenation of udfs, if we hit an aggregate function (count,
sum, avg, min, max), such branch is game over and we don't worry anymore: no
invalid reference. In other words,
select field, udf1(udf2(udf3(avg(field2))))
... group by field [, udf(...)]
and
select field, udf1(avg(udf2(udf3(field2))))
... group by field [, udf(...)]
are valid cases. The cases remain valid if the second line is
group by field, udf(...)
because aggregate functions work on the whole group.

- Someone may write
select customer, abs(trunc(sum(total)+field))
from sales
group by customer, abs(total)
that's invalid, but I prefer that this case goes undetected if it's a
nightmare, because
select customer, abs(trunc(sum(total)*2+3))
should be accepted.

- Neil may be figuring out that I gave him some rules and examples that lead
to reject valid cases, but I couldn't see a valid case until I realized that
summary/aggregate functions aren't affected by the presence of an udf, so
the udf doesn't create a potential invalid field. The example at the top
SELECT CUSTOMER, ABS(SUM(TOTAL))
could be rewritten as
SELECT CUSTOMER, SUM(ABS(TOTAL))
to work for now, but I expect that the first case is more efficient...
noticeable when any group comprises several individual rows.

C.