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

[Christian P. <c_p...@ya...>]

Neil McCalden <nm...@zi...> escribió en el mensaje de noticias
200...@zi......
>
> I think that grouping by fields/udf without them being in the select
> list is a bad idea but as it allowed for straight fields it should be
> allowed for udfs. Making aggregates a special case was where I was
> heading with aggregate_in_list but the udf test was blocking it first in
> this case.
In my opinion grouping by fields/udf without them being in the select list
should be valid, BUT in this case,
not including the grouping in the select was my mistake, this is not the
point here.
I'll try to explain it again, here is my example with a modification:

SELECT CUSTOMER, DIVIDE(ABS(TOTAL),TOTAL), SUM(TOTAL)
FROM SALES
GROUP BY CUSTOMER, DIVIDE(ABS(TOTAL),TOTAL)

this example is working FINE and the result is as spected.
But if you add the ABS to the SUM, to get all values as positive, then you
get the error:

SELECT CUSTOMER, DIVIDE(ABS(TOTAL),TOTAL), ABS(SUM(TOTAL))
FROM SALES
GROUP BY CUSTOMER, DIVIDE(ABS(TOTAL),TOTAL)

here you have an error.

Look that the possible values for the structure DIVIDE(ABS(TOTAL),TOTAL) are
only two "1" if TOTAL > 0 and "-1" if TOTAL > 0. I use this to separate
negative values from positive values.

----

I made some tests and I found that the problem ocurr when the field inside
the aggregate and the field inside the UDF in the GROUP BY is the same. For
a better explain of this, the following example works FINE:

SELECT CUSTOMER, ANYUDF(XFIELD), ABS(SUM(TOTAL))
FROM SALES
GROUP BY CUSTOMER, ANYUDF(XFIELD)

where XFIELD is other than TOTAL

----

Of course this is not a compatibility issue at all, since UDF in GROUP BY
are not allowed in INTERBASE, but it restricts some interesting things that
you could be do with FIREBIRD.

with best regards
Christian

c_p...@ya...