BIN_AND_AGG, BIN_OR_AGG, and BIN_XOR_AGG (BIN_AND, BIN_OR and BIN_XOR as aggregate functions)

[livius2]

Hi

as #8173 is closed as not planned, please add BIN_OR and BIN_AND as aggregate functions (can be under different name if you see some conflicts).

Currently we must do this in stored procedures or execute block in the loop.
This will simplify things a lot.

example what we must do currently instead of simple grouping function:

EXECUTE BLOCK RETURNS(
  RODZ_REP_ID TYPE OF COLUMN RODZAJ_REPARTYCJI.RODZ_REP_ID
, RODZ_REP_NAZWA TYPE OF COLUMN RODZAJ_REPARTYCJI.RODZ_REP_NAZWA
, RODZ_REP_TEXT TYPE OF COLUMN RODZAJ_REPARTYCJI.RODZ_REP_TEXT 
, MASKA_OBSZAR_ID TYPE OF COLUMN RODZAJ_UMOWY.MASKA_OBSZAR_ID
, MASKA_STR VARCHAR(16)
)
AS
DECLARE VARIABLE TMP_MASKA_OBSZAR_ID TYPE OF COLUMN RODZAJ_UMOWY.MASKA_OBSZAR_ID;
DECLARE VARIABLE NEXT_RODZ_REP_ID TYPE OF COLUMN RODZAJ_REPARTYCJI.RODZ_REP_ID;
BEGIN
    NEXT_RODZ_REP_ID = '';
    MASKA_OBSZAR_ID = 0;
    FOR
        SELECT
            DISTINCT 
              RR.RODZ_REP_ID
            , RR.RODZ_REP_NAZWA
            , RR.RODZ_REP_TEXT
            , RU.MASKA_OBSZAR_ID
            /* , LICZ_BIT_TO_STR(RU.MASKA_OBSZAR_ID, 16) AS MASKA_STR */
            , LEAD(RR.RODZ_REP_ID, 1) over (order by RR.RODZ_REP_TEXT, RR.RODZ_REP_ID, RD.RDB$DB_KEY) AS NEXT_RODZ_REP_ID
        FROM
            RODZAJ_REPARTYCJI RR
            INNER JOIN RODZAJ_DZIAL RD ON RR.RODZ_REP_ID = RD.RODZ_REP_ID
            INNER JOIN RODZAJ_UMOWY RU ON RU.RODZ_UMOWY_ID = RD.RODZ_UMOWY_ID AND RU.TYP_UMOWY_ID = RD.TYP_UMOWY_ID
        WHERE
            RR.RODZ_REP_ID <> ''
        ORDER BY
            RR.RODZ_REP_TEXT, RR.RODZ_REP_ID, RD.RDB$DB_KEY
    INTO :RODZ_REP_ID, :RODZ_REP_NAZWA, :RODZ_REP_TEXT, :TMP_MASKA_OBSZAR_ID, :NEXT_RODZ_REP_ID
    DO
      BEGIN
         MASKA_OBSZAR_ID = BIN_OR(:MASKA_OBSZAR_ID, :TMP_MASKA_OBSZAR_ID);  
         IF (:RODZ_REP_ID IS DISTINCT FROM :NEXT_RODZ_REP_ID) THEN
           BEGIN
             MASKA_STR = LICZ_BIT_TO_STR(MASKA_OBSZAR_ID, 16);
             SUSPEND;
             TMP_MASKA_OBSZAR_ID = 0;
             MASKA_OBSZAR_ID = 0;
           END          
      END
END

[asfernandes]

#8173 was closed because it's duplicated.
I'm not sure this usage would be generic enough to be defined as a builtin aggregate function.

[mrotteveel]

There is actually a SQL standard alternative for this: the use of EVERY, ANY and SOME as aggregate functions (Firebird currently only supports ANY and SOME (and ALL) in quantified comparison predicates), and such this ticket is a duplicate of #6227.

[mrotteveel]

Reopened, I was thinking of AND/OR, not BIN_AND/BIN_OR.

[livius2]

I added BIN_AND additionally to the request, but now that I think about it, I don't know how it would work for BIN_AND. For BIN_OR, it is obvious, but for BIN_AND... Unless you see how it could work for BIN_AND as well, otherwise I will remove it from the request description.

[aafemt]

There is no difference between AND and OR. Both of these operations are communicative and associative so result doesn't depend on order of data. The only difference is that OR tends to end up in all bits set while AND ends up in all bits reset.

[livius2]

OK. So for BIN_OR it should start from b00000000... and for BNI_AND it should start from b11111111..

[sim1984]

Implemented in #8768