Re: bitwise and/or aggregate functions?

Dear hackers,

still in the spirit of "it may be useful to others, as it was to me, and
it does cost very little", and before submitting a small patch and being
exploded because it is obviously very stupid:

Would it be appropriate to contribute BIT_AND and BIT_OR aggregates
for integer types, with some documentation and minimal validation?
There has been a discussion recently on pgsql-general about that.

1) mysql has them... it seems to be an argument here around sometimes;-)
it is in their proud list-of-features that it has and that postgresql
does not have.

2) each declaration is a 4-line "CREATE AGGREGATE", the underlying
functions being already available for & and | operators.

3) I know that one can add them if they are needed, but what
would be the point of NOT providing such simple features, and
having the basic user to have to learn about creating aggregate
functions and browse a long time in the documentation for that?

I needed them for some application: I'm happy I know how to add them now,
but I would have been even happier if I had found them just available
without having to learn about these intesting details about postgresql
extensions.

Have a nice day,

--
Fabien Coelho - coelho(at)cri(dot)ensmp(dot)fr

Fabien COELHO wrote:
>
> Dear hackers,
>
> still in the spirit of "it may be useful to others, as it was to me, and
> it does cost very little", and before submitting a small patch and being
> exploded because it is obviously very stupid:
>
> Would it be appropriate to contribute BIT_AND and BIT_OR aggregates
> for integer types, with some documentation and minimal validation?
> There has been a discussion recently on pgsql-general about that.
>
> 1) mysql has them... it seems to be an argument here around sometimes;-)
> it is in their proud list-of-features that it has and that postgresql
> does not have.
>
> 2) each declaration is a 4-line "CREATE AGGREGATE", the underlying
> functions being already available for & and | operators.
>
> 3) I know that one can add them if they are needed, but what
> would be the point of NOT providing such simple features, and
> having the basic user to have to learn about creating aggregate
> functions and browse a long time in the documentation for that?
>
> I needed them for some application: I'm happy I know how to add them now,
> but I would have been even happier if I had found them just available
> without having to learn about these intesting details about postgresql
> extensions.

I am confused why you would use bit on integers when there is a bit type
with an AND operator:

pg_catalog | & | bit | bit | bit | bitwise and

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

> > Would it be appropriate to contribute BIT_AND and BIT_OR aggregates
>
> I am confused why you would use bit on integers

Well, (I think) I need them to manipulate pg_catalog's aclitem bitfields.
I plea not guilty for the design of pg_catalog;-)
Moreover, I added aclitem accessors which return INT4 in a recent patch
that you kindly applied.

> when there is a bit type with an AND operator:
> pg_catalog | & | bit | bit | bit | bitwise and

Sure. "&" is also available for all integer types.
BTW, I'm arguing about AGGREGATE functions, and there is no aggregate
functions at the time, neither for int* nor for bit.

SELECT BIT_OR(aclitem_privs(...)) AS effective_privs
FROM ...
WHERE aclitem_grantee(...)=... AND ... ;

Or maybe I cannot understand why you're confused?

--
Fabien Coelho - coelho(at)cri(dot)ensmp(dot)fr

> SELECT BIT_OR(aclitem_privs(...)) AS effective_privs
> FROM ...
> WHERE aclitem_grantee(...)=... AND ... ;

Is there anything in SQL2003 about such operators? If there is, we
should make sure we use the correct aggregate names.

Chris

> > SELECT BIT_OR(aclitem_privs(...)) AS effective_privs
> > FROM ...
> > WHERE aclitem_grantee(...)=... AND ... ;
>
> Is there anything in SQL2003 about such operators? If there is, we
> should make sure we use the correct aggregate names.

That's a point!

I thought of BIT_* because it is short and also used by mysql.
Ingres has BIT_AND and BIT_OR functions, but they are not aggregates.

I don't know where these standards are available online... It seems they
are not available:-(

--
Fabien Coelho - coelho(at)cri(dot)ensmp(dot)fr

>>Is there anything in SQL2003 about such operators? If there is, we
>>should make sure we use the correct aggregate names.
>
> That's a point!
>
> I thought of BIT_* because it is short and also used by mysql.
> Ingres has BIT_AND and BIT_OR functions, but they are not aggregates.
>
> I don't know where these standards are available online... It seems they
> are not available:-(

Neil - can you check your SQL2003 copy to see if it mentions standard
aggregates on bit types?

Thanks,

Chris

> > I thought of BIT_* because it is short and also used by mysql.
> > Ingres has BIT_AND and BIT_OR functions, but they are not aggregates.
> >
> > I don't know where these standards are available online... It seems they
> > are not available:-(
>
> Neil - can you check your SQL2003 copy to see if it mentions standard
> aggregates on bit types?

I haven't seen such a function in the "draft" standard I have found.
It seems it is not yet available from INCITS.

There are also EVERY (= aggregate AND) and SOME/ANY (= aggregate OR) for
booleans, that are interesting. I'll add them with the patch. However I
think that I may need to add two small functions for that.

--
Fabien Coelho - coelho(at)cri(dot)ensmp(dot)fr

On Tue, May 18, 2004 at 12:39:08PM -0400, Neil Conway wrote:

> A copy that claims to "represent an almost indistinuishable delta on the
> actual SQL 2003 database standard" is available online here:
>
> http://www.wiscorp.com/sql/sql_2003_standard.zip

Those are PDFs AFAIR, not easily greppable ... do you have a text version,
or do you always look up things by looking at the TOC? I'm not thrilled
with the idea of reading all 1500 pages of it ...

--
Alvaro Herrera (<alvherre[(at)]dcc(dot)uchile(dot)cl>)
"El día que dejes de cambiar dejarás de vivir"

[ Sorry for the latency of my response, Chris -- this got buried in my
inbox... ]

Fabien COELHO wrote:
>>>I don't know where these standards are available online... It seems they
>>>are not available:-(

A copy that claims to "represent an almost indistinuishable delta on the
actual SQL 2003 database standard" is available online here:

http://www.wiscorp.com/sql/sql_2003_standard.zip

>>Neil - can you check your SQL2003 copy to see if it mentions standard
>>aggregates on bit types?

I couldn't see any mention of any aggregates specific to the bit types,
although my ability to accurately divine information from the standard
has been less than perfect in the past. There are the EVERY() and ANY()
aggregates that Fabien mentioned, though.

-Neil

Alvaro Herrera Munoz wrote:
> Those are PDFs AFAIR, not easily greppable

Not greppable, but any half-decent PDF viewer should have a "search"
feature that should allow much the same thing. Checking the index is
another way to go, although it is somewhat time-consuming.

I don't have access to an ASCII version (of SQL2003; I believe I've got
an ASCII copy of SQL92 around here somewhere).

-Neil

Neil Conway <neilc(at)samurai(dot)com> writes:
> Fabien COELHO wrote:
>> Neil - can you check your SQL2003 copy to see if it mentions standard
>> aggregates on bit types?

> I couldn't see any mention of any aggregates specific to the bit types,

There certainly are none, since in fact SQL2003 removes the BIT types
entirely. See Annex E:

2) ISO/IEC 9075-2:1999 defined data types called BIT and BIT VARYING.
These data types have been deleted from this edition of ISO/IEC 9075.

regards, tom lane

Tom Lane wrote:
> Neil Conway <neilc(at)samurai(dot)com> writes:
> > Fabien COELHO wrote:
> >> Neil - can you check your SQL2003 copy to see if it mentions standard
> >> aggregates on bit types?
>
> > I couldn't see any mention of any aggregates specific to the bit types,
>
> There certainly are none, since in fact SQL2003 removes the BIT types
> entirely. See Annex E:
>
> 2) ISO/IEC 9075-2:1999 defined data types called BIT and BIT VARYING.
> These data types have been deleted from this edition of ISO/IEC 9075.

Understand. To me, allowing bitwise and boolean aggregates on a column
seemed like a natural capability we should have.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073