Thread: aggregate of bitstrings

AHA!  I hadn't encountered any null values in my bitstrings.
and having to include 'where xxx is not null' is rather
inconvenient and easy to forget.

indeed bitor(B'1000',null) returns null

but as a test, i nulled one row's data (the bitstring column portion only)
and my orsum without INITCOND returned the proper OR of the remaining values
when including all rows in the aggregate.  it did not return null.

maybe the aggregator (whoever,whatever that is) handles null args differently,
not calling the func when the arg is null?

pg8.1.3

TJ


Florian G. Pflug wrote:
> TJ O'Donnell wrote:
>
>>> create aggregate or_bit64(
>>>    basetype=bit,
>>>    sfunc=bitor,
>>>    stype=bit,
>>>    initcond=
>>>      '0000000000000000000000000000000000000000000000000000000000000000'
>>> ) ;
>>>
>>> I'm using this in production, and it works fine. I didn't find a way to
>>> make this length-agnostic, so I defined this for all lenghts of
>>> bitstrings the my app uses (32 and 64).
>>>
>>> greetings, Florian Pflug
>>
>>
>> I've created a similar aggregate using:
>> CREATE AGGREGATE gnova.orsum (
>>     BASETYPE = bit,
>>     SFUNC = bitor,
>>     STYPE = bit
>> );
>> Notice, not using INITCOND allows bit of any length.  While it may be
>> poor programming practice to not initialize, the docs say:
>> "If it is not supplied then the state value starts out null."
>> which is good enough for this old programmer.   AND it works :)
>
> The problem was, as far as I remember, that bitor returns NULL if any
> of it's arguments is null. So not specifying an INITCOND makes the
> aggregate work for any length, but always returns null then...
>
> greetings, Florian Pflug

TJ O'Donnell wrote:
>
> maybe the aggregator (whoever,whatever that is) handles null args
> differently,
> not calling the func when the arg is null?

see:

http://www.postgresql.org/docs/8.1/interactive/sql-createaggregate.html

specifically:

"If the state transition function is declared "strict", then it cannot
be called with null inputs. With such a transition function, aggregate
execution behaves as follows. Null input values are ignored (the
function is not called and the previous state value is retained). If the
initial state value is null, then the first nonnull input value replaces
the state value, and the transition function is invoked beginning with
the second nonnull input value. This is handy for implementing
aggregates like max. Note that this behavior is only available when
state_data_type is the same as input_data_type. When these types are
different, you must supply a nonnull initial condition or use a
nonstrict transition function.

If the state transition function is not strict, then it will be called
unconditionally at each input value, and must deal with null inputs and
null transition values for itself. This allows the aggregate author to
have full control over the aggregate's handling of null values.

If the final function is declared "strict", then it will not be called
when the ending state value is null; instead a null result will be
returned automatically. (Of course this is just the normal behavior of
strict functions.) In any case the final function has the option of
returning a null value. For example, the final function for avg returns
null when it sees there were zero input rows."

HTH,

Joe

TJ O'Donnell wrote:
> AHA!  I hadn't encountered any null values in my bitstrings.
> and having to include 'where xxx is not null' is rather
> inconvenient and easy to forget.
>
> indeed bitor(B'1000',null) returns null
>
> but as a test, i nulled one row's data (the bitstring column portion only)
> and my orsum without INITCOND returned the proper OR of the remaining
> values
> when including all rows in the aggregate.  it did not return null.
Hm.. strange.. I could have sworn that I added the initcond to fix some
null-related issue...
Maybe is was the aggregate returning null where there where no rows
to aggregate.. I'll test this again, I'd actually love to get rid of
that initcond.

greetings, Florian Pflug