[h2] Problem with conditional COUNT aggregate

[Uli]

Hi,

after reading the documentation about the COUNT aggregate function (
http://www.h2database.com/html/functions.html#count) I would expect that it 
is possible to have a conditional COUNT.

But it does not seem to work as the test program below shows. It creates a 
table with two colums t1 and t2. t2 is always > t1. The program should 
count all rows with t2-t1 > 1000 and t2-t1 <= 1000. But both counts returns 
the number of all rows so it does not mention the conditions. 

Should this be supported in H2?

Thanks!
Uli


import java.sql.*;
import java.util.concurrent.ThreadLocalRandom;

public class CountTest
{
    public static void testCount()
        throws ClassNotFoundException, SQLException
    {
        Class.forName("org.h2.Driver");
        Connection conn = DriverManager.getConnection("jdbc:h2:~/test/db", 
"test",
            "test");

        Statement stmt = conn.createStatement();

        stmt.execute("CREATE TABLE TEST ( t1 BIGINT NOT NULL, t2 BIGINT NOT 
NULL)");

        PreparedStatement pStmt = conn.prepareStatement("INSERT INTO TEST 
(t1, t2) VALUES (?, ?)");
        long t = 100000;
        for (int i = 0; i < 100; i++)
        {
            long t2 = ThreadLocalRandom.current().nextLong(900, 1100);
            pStmt.setLong(1, t);
            pStmt.setLong(2, t + t2);
            pStmt.execute();
            t += t2;
        }

        ResultSet rs = stmt
            .executeQuery("SELECT COUNT((t2-t1) > 1000), COUNT((t2-t1) <= 
1000), COUNT(*) FROM TEST");

        rs.next();
        int lc = rs.getInt(1);
        int sc = rs.getInt(2);
        int count = rs.getInt(3);
        rs.close();

        System.out.format("Count: %3d, Below 1000: %3d, Greater 1000: 
%3d\n", count, lc, sc);
        System.out.format("(Below 1000 + Greater 1000) == Count ? %b\n", 
(lc + sc) == count);

        stmt.execute("DROP TABLE TEST");

        stmt.close();
        conn.close();
    }

    public static void main(String[] args)
        throws ClassNotFoundException, SQLException
    {
        testCount();
    }
}

-- 
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.