H2 DB in MySQL mode doesn't accept BIT_OR agregation function

[GoogleCodeExporter]

Please send a question to the H2 Google Group or StackOverflow first,
and only then, once you are completely sure it is an issue, submit it here.
The reason is that only very few people actively monitor the issue tracker.

Before submitting a bug, please also check the FAQ:
http://www.h2database.com/html/faq.html

I know H2DB calls this function BITOR, but this will not work on MySQL.
What steps will reproduce the problem?
(simple SQL scripts or simple standalone applications are preferred)
1.
The following query work on MySQL :
SELECT BIT_OR(column1)
FROM table1
GROUP BY column2

but return the error below in H2DB : 
Cause: org.h2.jdbc.JdbcSQLException: Function "BIT_OR" not found; SQL statement:

2.The following query work on H2DB:
SELECT BITOR(column1)
FROM table1
GROUP BY column2

but returns the error below in MySQL
Code: 1305 SQL State: HY000 --- FUNCTION BITOR does not exist

What is the expected output? What do you see instead?

It will be nice to accept the BIT_OR function at the H2DB level too.

What version of the product are you using? On what operating system, file
system, and virtual machine?
1.3.175

Do you know a workaround?
No.

What is your use case, meaning why do you need this feature?
I'm using H2DB as an in memory DB to run unit tests on it. The production DB is 
MySQL so I can not change the queries so that they will only run on H2Db.  

How important/urgent is the problem for you?

Please provide any additional information below.

Original issue reported on code.google.com by ghe...@gmail.com on 24 Feb 2014 at 4:50

[GoogleCodeExporter]

The H2 BITOR and the MySQL BIT_OR are not actually the same:

* The H2 BITOR(a, b) is just like the Java "|" operator and needs two 
parameters. See http://h2database.com/html/functions.html#bitor

* The MySQL BIT_OR(x) is an aggregate function, and needs one parameter.

For H2, you could create a custom aggregate function called BIT_OR: 
http://h2database.com/html/grammar.html#create_aggregate

Would that work for you?

Original comment by thomas.t...@gmail.com on 24 Feb 2014 at 5:09

[GoogleCodeExporter]

The thing is that I don't want to change my embedded queries just to overcome 
H2DB limitations. 
Is it possible to have a similar BIT_OR function at the H2DB level?

Thanks

Original comment by ghe...@gmail.com on 24 Feb 2014 at 5:24

[GoogleCodeExporter]

Yes, you could use the exact same queries for H2 and MySQL. Only, you would 
have to create a user defined aggregate function that matches the MySQL 
aggregate function. Until H2 natively supports them. That way, you can use the 
current version of H2 without having to wait until this is implemented in H2.

I see that PostgreSQL supports BIT_OR and related as well: 
http://www.postgresql.org/docs/9.1/static/functions-aggregate.html - so in the 
long term it makes sense if H2 supports them as well.

Original comment by thomas.t...@gmail.com on 24 Feb 2014 at 6:06

[GoogleCodeExporter]

Original comment by thomas.t...@gmail.com on 6 Apr 2014 at 1:29

• Changed state: PatchesWelcome

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

For whoever is interested this is how my aggregation method looks:

package com.myorg.utils;

import org.h2.api.AggregateFunction;

/**
 * Aggregation function used by H2Db instance. It is required in order to allow the BIT_OR operations not available by 
 * default on the H2 instance.
 * In order for BIT_OR function to work  on a H2 instance you will have to be defined in your SQl script:
 * CREATE AGGREGATE BIT_OR FOR "com.myorg.utils.BitOr";  
 */
public class BitOr implements AggregateFunction{
    java.util.LinkedList<Byte> values = new java.util.LinkedList<Byte>();

    public void init(java.sql.Connection cnctn) throws java.sql.SQLException {
        // I ignored this
    }

    public int getType(int[] ints) throws java.sql.SQLException {
       return java.sql.Types.BIT;
    }

    public void add(Object o) throws java.sql.SQLException {
                byte input;
                if (o instanceof Integer) {
                        input = Byte.valueOf(o+"");
                } else {
                        input = (Byte)o;
                }

        values.add(input);
    }

    public Object getResult() throws java.sql.SQLException {
        byte result = (byte)0x00;
        java.util.Iterator<Byte> i;

        // Get value
        for( i = values.iterator(); i.hasNext(); ) {
            result |=  i.next();    
        }

        return result;
    }
}

Original comment by ghe...@gmail.com on 18 Sep 2014 at 2:10

[GoogleCodeExporter]

Don't forget to add this class into H2 instance class path 

Original comment by ghe...@gmail.com on 18 Sep 2014 at 2:12

[GoogleCodeExporter]

OK, this should work, but it is inefficient. I think I will wait for another, 
faster patch.

Original comment by thomas.t...@gmail.com on 25 Nov 2014 at 8:35

[GoogleCodeExporter]

Implemented in revision 6003

Original comment by noelgrandin on 6 Jan 2015 at 8:26

• Changed state: Fixed