Array constant does not work in where condition

[obabichevjb]

Bug Report

Versions

• Driver: io.r2dbc:r2dbc-h2:1.0.0.RELEASE
• Database: H2 2.1.214
• Java: 17
• OS: osx, debian

Current Behavior

SQL SELECT my_col FROM codec_test WHERE my_col >= ALL (ARRAY [100,1000]) throws [42000] Syntax error in SQL statement error

I created a small test that reproduces the problem on the main branch:

public class CodecIntegrationTests extends IntegrationTestSupport {
    @Test
    void testAllArray() {
        createTable(connection, "INT");

        Integer value = 10000;

        Flux.from(connection.createStatement("INSERT INTO codec_test values($1)")
                        .bind("$1", value)
                        .execute())
                .flatMap(H2Result::getRowsUpdated)
                .as(StepVerifier::create)
                .expectNext(1L)
                .verifyComplete();

//        connection.createStatement("SELECT my_col FROM codec_test WHERE my_col >= 100 AND my_col >= 1000")
        connection.createStatement("SELECT my_col FROM codec_test WHERE my_col >= ALL (ARRAY [100,1000])")
                .execute()
                .flatMap(it -> it.map((row, rowMetadata) -> Optional.ofNullable(row.get(0))))
                .as(StepVerifier::create)
                .expectNext(Optional.of(10000))
                .verifyComplete();
    }
}

The variant SELECT my_col FROM codec_test WHERE my_col >= 100 AND my_col >= 1000 works well, the variant SELECT my_col FROM codec_test WHERE my_col >= ALL (ARRAY [100,1000]) throws the mentioned exception.

P.s. actually, I found that it fails when the method org.h2.engine.Session.prepareCommand() is called. The source of the issue is in the version of the H2, the mentioned error occurs in 2.1.214, but fixed in the version 2.2.220.

So looks like the question - do you have any plans on updating h2 version?

[mp911de]

The driver is currently unmaintained as the previous maintainer was let go from his previous company. I'd be more than happy to welcome new maintainers that are interested in moving this effort forward. Ideally, this driver-add-on to H2 would be included in the H2 core project.

[katzyn]

Sorry, there are no plans for such support on H2 side, especially after release of Java 21 with virtual threads. H2 fully supports them since version 2.2.222 and many other JDBC drivers support them too. Virtual threads are faster than asynchronous APIs, they are simpler, and relationships between various parts of code and database transactions are pretty clear.