[OpenSER-Devel] FUNCTION ser.bitand does not exist

[Dan Pascu]

On Tuesday 13 May 2008, Juha Heinanen wrote:
> Iouri Kharon writes:
>  > > May 13 00:31:25 Etch-3 /usr/sbin/openser[3985]:
>  > > ERROR:db_mysql:db_mysql_submit_query: driver error on query:
>  > > FUNCTION ser.bitand does not exist
>  >
>  > You must recreate base. See scripts/openserdbctl.mysql line ~135.
>
> iouri,
>
> i'm not sure i like this.  this select is executed by nathelper once
> per second on location table.  how much does the user defined function
> slow it down compared to if the same select would have been done using
> built-in functions?

I have to agree with Juha here. While you may know that I'm not a fanatic 
about performance and most of the time I would favor more functionality 
or consistency and simplified code even if they incur a small performance 
penalty, you may also know that I always advocated that performance 
optimizations should be made where they really matter, not where they 
make little to no difference. And I think we can all agree that the 
database is one of the major bottlenecks in openser. Which means that 
making it even slower than it is, doesn't make for a good move.

I do not have any benchmarks here, but I would expect that userspace 
functions to be slower. Especially if they are interpreted.

>
> why is the emulation function defined in mysql and not in oracle?  i
> would prefer it to be defined in oracle, because so far majority of
> openser users is using mysql.

Unfortunately this is not that simple. The BIT_AND function you mentioned 
from mysql is a group (aggregate) function. You cannot use it to make a 
bitwise and between 2 values, only between values in different rows for 
the same column. It is used in conjunction with GROUP BY to make a 
bitwise and over the values of a column in multiple rows.

MySQL however, has an operator for this, which is like in C: &
But Oracle doesn't have the operator and it can't define one.

However given that mysql and postgres are the most used databases, we 
shouldn't cripple their abilities just to support other less used 
databases. Maybe a different approach is needed here. Like have the 
database backends provide functions that would encode certain operations 
in the appropriate way for each database. Or do the bitwise operations in 
C instead of the database (if possible).

-- 
Dan