SQLException when filtering by two columns although each filter works separately

[Incanus3]

Expected behavior

query should work and return query result

Actual behavior

query fails with SQLException

Steps to reproduce

// the entity
@Entity
@DbName(REST_DB_NAME)
@Table(name = "COREVP_EVENT_PROCESSING")
class DatabaseEventProcessing(
    @ManyToOne
    @JoinColumn(name = "evp_evt_id_fk", nullable = false)
    override val event: DatabaseEvent,

    @Column(name = "evp_subscriber", nullable = false)
    override val subscriber: String,

    @Column(name = "evp_processed", nullable = false)
    override var processed: Boolean = false,
) : EventProcessing {
    @Id
    @Column(name = "evp_id", nullable = false)
    override val id: String = GuidGenerator.getNewGuid(braces = false)
}

// the test
class EventProcessingFilteringTest {
    private val dataSource = DataSourceBuilder.create()
        .url("jdbc:h2:mem:testdb")
        .driver(org.h2.Driver::class.java)
        .username("test")
        .password("test")
        .build()

    private val database = Database.builder()
        .ddlRun(true)
        .ddlGenerate(true)
        .defaultDatabase(false)
        .currentUserProvider { "test" }
        .dataSource(dataSource)
        .addClass(AddressConverter::class.java)
        .addClass(DatabaseEvent::class.java)
        .addClass(DatabaseEventProcessing::class.java)
        .build()

    @Test
    fun itWorks() {
        // works
        var query = database.createQuery(DatabaseEventProcessing::class.java)
        query
            .where(query.expressionFactory.ilike("id", "%1%"))
            .findCount()

        // works
        query = database.createQuery(DatabaseEventProcessing::class.java)
        query
            .where(query.expressionFactory.eq("processed", "true"))
            .findCount()

        // fails
        query = database.createQuery(DatabaseEventProcessing::class.java)
        query
            .where(query.expressionFactory.ilike("id", "%1%"))
            .where(query.expressionFactory.eq("processed", "true"))
            .findCount()
    }
}

Query threw SQLException:Values of types "BOOLEAN" and "CHARACTER VARYING(4)" are not comparable; SQL statement:
select count(*) from COREVP_EVENT_PROCESSING t0 where lower(t0.evp_id) like ? escape'' and t0.evp_processed = ? [90110-224] Bind values:[%1%,true] Query was:select count(*) from COREVP_EVENT_PROCESSING t0 where lower(t0.evp_id) like ? escape'' and t0.evp_processed = ?
jakarta.persistence.PersistenceException: Query threw SQLException:Values of types "BOOLEAN" and "CHARACTER VARYING(4)" are not comparable; SQL statement:
select count(*) from COREVP_EVENT_PROCESSING t0 where lower(t0.evp_id) like ? escape'' and t0.evp_processed = ? [90110-224] Bind values:[%1%,true] Query was:select count(*) from COREVP_EVENT_PROCESSING t0 where lower(t0.evp_id) like ? escape'' and t0.evp_processed = ?
    at io.ebean.config.dbplatform.SqlCodeTranslator.translate(SqlCodeTranslator.java:85)
    at io.ebean.config.dbplatform.DatabasePlatform.translate(DatabasePlatform.java:212)
    at io.ebeaninternal.server.query.CQueryEngine.translate(CQueryEngine.java:139)
    at io.ebeaninternal.server.query.CQueryEngine.findCount(CQueryEngine.java:175)
    at io.ebeaninternal.server.query.DefaultOrmQueryEngine.findCount(DefaultOrmQueryEngine.java:88)
    at io.ebeaninternal.server.core.OrmQueryRequest.findCount(OrmQueryRequest.java:326)
    at io.ebeaninternal.server.core.DefaultServer.findCountWithCopy(DefaultServer.java:1093)
    at io.ebeaninternal.server.core.DefaultServer.findCount(DefaultServer.java:1081)
    at io.ebeaninternal.server.querydefn.DefaultOrmQuery.findCount(DefaultOrmQuery.java:1448)
    at cz.sentica.qwazar.debug.EventProcessingFilteringTest.itWorks(EventProcessingFilteringTest.kt:48)
    at java.base/java.lang.reflect.Method.invoke(Method.java:569)
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Values of types "BOOLEAN" and "CHARACTER VARYING(4)" are not comparable; SQL statement:
select count(*) from COREVP_EVENT_PROCESSING t0 where lower(t0.evp_id) like ? escape'' and t0.evp_processed = ? [90110-224]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:644)
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:489)
    at org.h2.message.DbException.get(DbException.java:223)
    at org.h2.value.TypeInfo.checkComparable(TypeInfo.java:766)
    at org.h2.expression.condition.Comparison.optimize(Comparison.java:167)
    at org.h2.expression.condition.ConditionAndOr.optimize(ConditionAndOr.java:137)
    at org.h2.expression.Expression.optimizeCondition(Expression.java:148)
    at org.h2.command.query.Select.prepareExpressions(Select.java:1177)
    at org.h2.command.query.Query.prepare(Query.java:218)
    at org.h2.command.CommandContainer.recompileIfRequired(CommandContainer.java:148)
    at org.h2.command.CommandContainer.query(CommandContainer.java:247)
    at org.h2.command.Command.executeQuery(Command.java:192)
    at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:131)
    at io.ebean.datasource.pool.ExtendedPreparedStatement.executeQuery(ExtendedPreparedStatement.java:113)
    at io.ebeaninternal.server.query.CQueryRowCount.findCount(CQueryRowCount.java:104)
    at io.ebeaninternal.server.query.CQueryEngine.findCount(CQueryEngine.java:162)
    ... 9 more

Values of types "BOOLEAN" and "CHARACTER VARYING(4)" are not comparable; SQL statement:
select count(*) from COREVP_EVENT_PROCESSING t0 where lower(t0.evp_id) like ? escape'' and t0.evp_processed = ? [90110-224]
org.h2.jdbc.JdbcSQLSyntaxErrorException: Values of types "BOOLEAN" and "CHARACTER VARYING(4)" are not comparable; SQL statement:
select count(*) from COREVP_EVENT_PROCESSING t0 where lower(t0.evp_id) like ? escape'' and t0.evp_processed = ? [90110-224]
    at app//org.h2.message.DbException.getJdbcSQLException(DbException.java:644)
    at app//org.h2.message.DbException.getJdbcSQLException(DbException.java:489)
    at app//org.h2.message.DbException.get(DbException.java:223)
    at app//org.h2.value.TypeInfo.checkComparable(TypeInfo.java:766)
    at app//org.h2.expression.condition.Comparison.optimize(Comparison.java:167)
    at app//org.h2.expression.condition.ConditionAndOr.optimize(ConditionAndOr.java:137)
    at app//org.h2.expression.Expression.optimizeCondition(Expression.java:148)
    at app//org.h2.command.query.Select.prepareExpressions(Select.java:1177)
    at app//org.h2.command.query.Query.prepare(Query.java:218)
    at app//org.h2.command.CommandContainer.recompileIfRequired(CommandContainer.java:148)
    at app//org.h2.command.CommandContainer.query(CommandContainer.java:247)
    at app//org.h2.command.Command.executeQuery(Command.java:192)
    at app//org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:131)
    at app//io.ebean.datasource.pool.ExtendedPreparedStatement.executeQuery(ExtendedPreparedStatement.java:113)
    at app//io.ebeaninternal.server.query.CQueryRowCount.findCount(CQueryRowCount.java:104)
    at app//io.ebeaninternal.server.query.CQueryEngine.findCount(CQueryEngine.java:162)
    at app//io.ebeaninternal.server.query.DefaultOrmQueryEngine.findCount(DefaultOrmQueryEngine.java:88)
    at app//io.ebeaninternal.server.core.OrmQueryRequest.findCount(OrmQueryRequest.java:326)
    at app//io.ebeaninternal.server.core.DefaultServer.findCountWithCopy(DefaultServer.java:1093)
    at app//io.ebeaninternal.server.core.DefaultServer.findCount(DefaultServer.java:1081)
    at app//io.ebeaninternal.server.querydefn.DefaultOrmQuery.findCount(DefaultOrmQuery.java:1448)
    at app//cz.sentica.qwazar.debug.EventProcessingFilteringTest.itWorks(EventProcessingFilteringTest.kt:48)
    at java.base@17.0.12/java.lang.reflect.Method.invoke(Method.java:569)
    at java.base@17.0.12/java.util.ArrayList.forEach(ArrayList.java:1511)
    at java.base@17.0.12/java.util.ArrayList.forEach(ArrayList.java:1511)

Further info

• this is ebean version 15.5.2
• querying using query beans works without problems
• I guess this could also be a problem in the h2 driver, but I don't know how to determine that

[rob-bygrave]

Instead try:

        query.where()
              .ilike("id", "%1%")
              .eq("processed", "true")
              .findCount()

[Incanus3]

Hi @rob-bygrave and thanks for your time.

Instead try:

        query.where()
              .ilike("id", "%1%")
              .eq("processed", "true")
              .findCount()

Nope, fails the same way.

[rbygrave]

So instead of binding string literal "true", bind true [boolean type].

That is, H2 isn't able to transparently cast the varchar type to boolean type.

[Incanus3]

Hmm. How is it possible that it works when only the bool filter is applied?

A few implementation related questions then, if I may. This code is part of a generic JSON API implementation, so 1) it doesn't know the specific bean class and property at compile time and 2) the filter value is coming from query params so it always comes in as a string and I'll have to coerce it manually. Hence these questions:

1. I guess I could use (database as DefaultServer).descriptor(beanClass).property(propName).type().name == "boolean". Is this the best way, or is there a simpler/more effective one?
2. I've noticed this before but it didn't seem important enough to ask separately. Is there a reason why this casting is needed to access bean descriptors? there are are two things that currently make it so:
   • although DatabaseBuilder.build() and DatabaseFactory.create() always return DefaultServer (coming from DatabaseFactory.createInternal() -> DefaultContainer.createServer()), their return type is declared as Database
   • the descriptor*() methods are only available on SpiEbeanServer (which DefaultServer implements), not on generic Database

[rbygrave]

I guess I could use

That is internal API, and instead the Database.pluginApi() ... returns the io.ebean.plugin.SpiServer which has methods to get io.ebean.plugin.BeanType etc ... and this is the public API that should be used to access the bean properties. BeanDescriptor, DefaultServer - these are internal types that should not really be used by application code.

Is there a reason why this casting is needed to access bean descriptors?

Use the Database.pluginApi() etc ... we shouldn't need any casting.

comes in as a string and I'll have to coerce it

Yes. Some databases with some types will automatically coerce the types at the database query level but generally with number, boolean and date/time types those should be coerced into the correct type to ultimately bind using JDBC with the correct type.

[Incanus3]

Use the Database.pluginApi() etc ... we shouldn't need any casting.

Nice, tried this and it works like a charm. Thanks a lot.

generally with number, boolean and date/time types those should be coerced into the correct type

Yeah, I have no problem with that, I'm just pretty surprised that it manages to correctly coerce the param type if it's the only param, but fails when there are two.

Anyway, you managed to solve my problem again. Thank you so much for all the work you put into this great project.

[rbygrave]

it manages to correctly coerce the param type if it's the only param, but fails when there are two.

Huh, I actually missed that point - yes that seems weird !!

Thank you so much for all the work you put into this great project.

Thanks for that, I really appreciate it.

[rbygrave]

Ok, I think we can close this one now.