filterMany generates invalid sql when the many side uses `@EmbeddedId` to model composite primary key

[raphaelNguyen]

Expected behavior

When using filterMany from ExpressionList or QueryBean, the generated sql is syntactically valid.

Actual behavior

The following exception is thrown when said orm query is run. The syntax error is complaining about the character $, part of a ${<prefix>.<embIdProperty>} that seems to have not been replaced correctly with a table alias (e.g. t0, t1, etc...).

Persistence Query threw SQLException:Syntax error in SQL statement

Steps to reproduce

I originally enountered this problem in my own project trying to upgrade from ebean 15.5.0 to 15.6.0 using the postgres platform.

Upon closer inspections, this issue seems to have been introduced by #3453 which was introduced in ebean:15.5.2.

To reproduce this error, please see these 2 tests I added in raphaelNguyen/ebean@ea374b6. These 2 tests try to run the same query in ExpressionList and QueryBean forms. These tests used the existing models DataWithFormula and DataWithFormulaMain under package org.tests.model.composite in ebean-test.

• org.tests.query.TestQueryFilterMany.testFilterManyComposite added in ebean-test submodule.
• org.querytest.QDataWithFormulaMainTest.testFilterMany added in ebean-querybean submodule.

These tests produce the outputs below when run with the default configuration test (just running mvn test out of the box). I have not set it up to run against other database containers.

It looks to me that the pattern ${<prefix>.<embIdProperty>} generated at the line below in IdBinderEmbedded.idOrNull could not be correctly replaced with the right table alias and remain through to when the sql is sent to the server.

ebean/ebean-core/src/main/java/io/ebeaninternal/server/deploy/id/IdBinderEmbedded.java

Line 57 in dc7d349

sb.append("${").append(prefix).append('.').append(embIdProperty.name()).append('}').append(props[i].name()).append(" is null");

Thank you very much for looking into this issue.

org.querytest.QDataWithFormulaMainTest.testFilterMany -- Time elapsed: 1.297 s <<< ERROR!
jakarta.persistence.PersistenceException:
Query threw SQLException:Syntax error in SQL statement "select /* QDataWithFormulaMainTest.testFilterMany */ t0.id, t0.title, t1.main_id, t1.meta_key, t1.value_index, t1.meta_key, t1.value_index, t1.string_value, t1.main_id from data_with_formula_main t0 left join data_with_formula t1 on t1.main_id = t0.id where (($ [*]metaData.id mainId is null and $ metaData.id metaKey is null and $ metaData.id valueIndex is null) or (t1.meta_key = ?)) order by t0.id"; expected "[, ., ::, AT, FORMAT, *, /, %, +, -, ||, NOT, IS, ILIKE, REGEXP, AND, OR, ,, )"; SQL statement:
select /* QDataWithFormulaMainTest.testFilterMany */ t0.id, t0.title, t1.main_id, t1.meta_key, t1.value_index, t1.meta_key, t1.value_index, t1.string_value, t1.main_id from data_with_formula_main t0 left join data_with_formula t1 on t1.main_id = t0.id where (($ metaData.id mainId is null and $ metaData.id metaKey is null and $ metaData.id valueIndex is null) or (t1.meta_key = ?)) order by t0.id [42001-220] Bind values:[null] Query was:select /* QDataWithFormulaMainTest.testFilterMany */ t0.id, t0.title, t1.main_id, t1.meta_key, t1.value_index, t1.meta_key, t1.value_index, t1.string_value, t1.main_id from data_with_formula_main t0 left join data_with_formula t1 on t1.main_id = t0.id where ((${metaData.id}mainId is null and ${metaData.id}metaKey is null and ${metaData.id}valueIndex is null) or (t1.meta_key = ?)) order by t0.id
        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.DefaultOrmQueryEngine.translate(DefaultOrmQueryEngine.java:37)
        at io.ebeaninternal.server.core.OrmQueryRequest.translate(OrmQueryRequest.java:58)
        at io.ebeaninternal.server.query.CQuery.createPersistenceException(CQuery.java:647)
        at io.ebeaninternal.server.query.CQueryEngine.findMany(CQueryEngine.java:364)
        at io.ebeaninternal.server.query.DefaultOrmQueryEngine.findMany(DefaultOrmQueryEngine.java:126)
        at io.ebeaninternal.server.core.OrmQueryRequest.findList(OrmQueryRequest.java:404)
        at io.ebeaninternal.server.core.DefaultServer.findList(DefaultServer.java:1430)
        at io.ebeaninternal.server.core.DefaultServer.findList(DefaultServer.java:1409)
        at io.ebeaninternal.server.querydefn.DefaultOrmQuery.findList(DefaultOrmQuery.java:1564)
        at io.ebean.typequery.QueryBean.findList(QueryBean.java:781)
        at org.querytest.QDataWithFormulaMainTest.testFilterMany(QDataWithFormulaMainTest.java:12)
        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: Syntax error in SQL statement "select /* QDataWithFormulaMainTest.testFilterMany */ t0.id, t0.title, t1.main_id, t1.meta_key, t1.value_index, t1.meta_key, t1.value_index, t1.string_value, t1.main_id from data_with_formula_main t0 left join data_with_formula t1 on t1.main_id = t0.id where (($ [*]metaData.id mainId is null and $ metaData.id metaKey is null and $ metaData.id valueIndex is null) or (t1.meta_key = ?)) order by t0.id"; expected "[, ., ::, AT, FORMAT, *, /, %, +, -, ||, NOT, IS, ILIKE, REGEXP, AND, OR, ,, )"; SQL statement:
select /* QDataWithFormulaMainTest.testFilterMany */ t0.id, t0.title, t1.main_id, t1.meta_key, t1.value_index, t1.meta_key, t1.value_index, t1.string_value, t1.main_id from data_with_formula_main t0 left join data_with_formula t1 on t1.main_id = t0.id where (($ metaData.id mainId is null and $ metaData.id metaKey is null and $ metaData.id valueIndex is null) or (t1.meta_key = ?)) order by t0.id [42001-220]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:514)
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:489)
        at org.h2.message.DbException.getSyntaxError(DbException.java:261)
        at org.h2.command.Parser.getSyntaxError(Parser.java:910)
        at org.h2.command.Parser.read(Parser.java:5793)
        at org.h2.command.Parser.readIfMore(Parser.java:1312)
        at org.h2.command.Parser.readTermWithoutIdentifier(Parser.java:5079)
        at org.h2.command.Parser.readTerm(Parser.java:4983)
        at org.h2.command.Parser.readFactor(Parser.java:3463)
        at org.h2.command.Parser.readSum(Parser.java:3450)
        at org.h2.command.Parser.readConcat(Parser.java:3415)
        at org.h2.command.Parser.readCondition(Parser.java:3205)
        at org.h2.command.Parser.readExpression(Parser.java:3125)
        at org.h2.command.Parser.readTermWithoutIdentifier(Parser.java:5078)
        at org.h2.command.Parser.readTerm(Parser.java:4983)
        at org.h2.command.Parser.readFactor(Parser.java:3463)
        at org.h2.command.Parser.readSum(Parser.java:3450)
        at org.h2.command.Parser.readConcat(Parser.java:3415)
        at org.h2.command.Parser.readCondition(Parser.java:3205)
        at org.h2.command.Parser.readExpressionWithGlobalConditions(Parser.java:3115)
        at org.h2.command.Parser.parseSelect(Parser.java:2953)
        at org.h2.command.Parser.parseQueryPrimary(Parser.java:2834)
        at org.h2.command.Parser.parseQueryTerm(Parser.java:2690)
        at org.h2.command.Parser.parseQueryExpressionBody(Parser.java:2669)
        at org.h2.command.Parser.parseQueryExpressionBodyAndEndOfQuery(Parser.java:2662)
        at org.h2.command.Parser.parseQueryExpression(Parser.java:2655)
        at org.h2.command.Parser.parseQuery(Parser.java:2624)
        at org.h2.command.Parser.parsePrepared(Parser.java:732)
        at org.h2.command.Parser.parse(Parser.java:697)
        at org.h2.command.Parser.parse(Parser.java:674)
        at org.h2.command.Parser.prepareCommand(Parser.java:577)
        at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:634)
        at org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:557)
        at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1116)
        at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:92)
        at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:288)
        at io.ebean.datasource.pool.PooledConnection.prepareStatement(PooledConnection.java:308)
        at io.ebean.datasource.pool.PooledConnection.prepareStatement(PooledConnection.java:285)
        at io.ebeaninternal.server.query.CQuery.prepareResultSet(CQuery.java:329)
        at io.ebeaninternal.server.query.CQuery.prepareBindExecuteQueryWithOption(CQuery.java:298)
        at io.ebeaninternal.server.query.CQuery.prepareBindExecuteQuery(CQuery.java:294)
        at io.ebeaninternal.server.query.CQueryEngine.findMany(CQueryEngine.java:343)
        ... 10 more

[ERROR] org.tests.query.TestQueryFilterMany.testFilterManyComposite -- Time elapsed: 0.005 s <<< ERROR!
jakarta.persistence.PersistenceException:
Query threw SQLException:Syntax error in SQL statement "select t0.id, t0.title, t1.main_id, t1.meta_key, t1.value_index, t1.meta_key, t1.value_index, t1.string_value, t1.main_id from data_with_formula_main t0 left join data_with_formula t1 on t1.main_id = t0.id where (($ [*]metaData.id mainId is null and $ metaData.id metaKey is null and $ metaData.id valueIndex is null) or (t1.meta_key = ?)) order by t0.id"; expected "[, ., ::, AT, FORMAT, *, /, %, +, -, ||, NOT, IS, ILIKE, REGEXP, AND, OR, ,, )"; SQL statement:
select t0.id, t0.title, t1.main_id, t1.meta_key, t1.value_index, t1.meta_key, t1.value_index, t1.string_value, t1.main_id from data_with_formula_main t0 left join data_with_formula t1 on t1.main_id = t0.id where (($ metaData.id mainId is null and $ metaData.id metaKey is null and $ metaData.id valueIndex is null) or (t1.meta_key = ?)) order by t0.id [42001-220] Bind values:[null] Query was:select t0.id, t0.title, t1.main_id, t1.meta_key, t1.value_index, t1.meta_key, t1.value_index, t1.string_value, t1.main_id from data_with_formula_main t0 left join data_with_formula t1 on t1.main_id = t0.id where ((${metaData.id}mainId is null and ${metaData.id}metaKey is null and ${metaData.id}valueIndex is null) or (t1.meta_key = ?)) order by t0.id
        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.DefaultOrmQueryEngine.translate(DefaultOrmQueryEngine.java:37)
        at io.ebeaninternal.server.core.OrmQueryRequest.translate(OrmQueryRequest.java:58)
        at io.ebeaninternal.server.query.CQuery.createPersistenceException(CQuery.java:647)
        at io.ebeaninternal.server.query.CQueryEngine.findMany(CQueryEngine.java:364)
        at io.ebeaninternal.server.query.DefaultOrmQueryEngine.findMany(DefaultOrmQueryEngine.java:126)
        at io.ebeaninternal.server.core.OrmQueryRequest.findList(OrmQueryRequest.java:404)
        at io.ebeaninternal.server.core.DefaultServer.findList(DefaultServer.java:1430)
        at io.ebeaninternal.server.core.DefaultServer.findList(DefaultServer.java:1409)
        at io.ebeaninternal.server.querydefn.DefaultOrmQuery.findList(DefaultOrmQuery.java:1564)
        at io.ebeaninternal.server.expression.FilterExpressionList.findList(FilterExpressionList.java:69)
        at org.tests.query.TestQueryFilterMany.testFilterManyComposite(TestQueryFilterMany.java:404)
        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: Syntax error in SQL statement "select t0.id, t0.title, t1.main_id, t1.meta_key, t1.value_index, t1.meta_key, t1.value_index, t1.string_value, t1.main_id from data_with_formula_main t0 left join data_with_formula t1 on t1.main_id = t0.id where (($ [*]metaData.id mainId is null and $ metaData.id metaKey is null and $ metaData.id valueIndex is null) or (t1.meta_key = ?)) order by t0.id"; expected "[, ., ::, AT, FORMAT, *, /, %, +, -, ||, NOT, IS, ILIKE, REGEXP, AND, OR, ,, )"; SQL statement:
select t0.id, t0.title, t1.main_id, t1.meta_key, t1.value_index, t1.meta_key, t1.value_index, t1.string_value, t1.main_id from data_with_formula_main t0 left join data_with_formula t1 on t1.main_id = t0.id where (($ metaData.id mainId is null and $ metaData.id metaKey is null and $ metaData.id valueIndex is null) or (t1.meta_key = ?)) order by t0.id [42001-220]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:514)
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:489)
        at org.h2.message.DbException.getSyntaxError(DbException.java:261)
        at org.h2.command.Parser.getSyntaxError(Parser.java:910)
        at org.h2.command.Parser.read(Parser.java:5793)
        at org.h2.command.Parser.readIfMore(Parser.java:1312)
        at org.h2.command.Parser.readTermWithoutIdentifier(Parser.java:5079)
        at org.h2.command.Parser.readTerm(Parser.java:4983)
        at org.h2.command.Parser.readFactor(Parser.java:3463)
        at org.h2.command.Parser.readSum(Parser.java:3450)
        at org.h2.command.Parser.readConcat(Parser.java:3415)
        at org.h2.command.Parser.readCondition(Parser.java:3205)
        at org.h2.command.Parser.readExpression(Parser.java:3125)
        at org.h2.command.Parser.readTermWithoutIdentifier(Parser.java:5078)
        at org.h2.command.Parser.readTerm(Parser.java:4983)
        at org.h2.command.Parser.readFactor(Parser.java:3463)
        at org.h2.command.Parser.readSum(Parser.java:3450)
        at org.h2.command.Parser.readConcat(Parser.java:3415)
        at org.h2.command.Parser.readCondition(Parser.java:3205)
        at org.h2.command.Parser.readExpressionWithGlobalConditions(Parser.java:3115)
        at org.h2.command.Parser.parseSelect(Parser.java:2953)
        at org.h2.command.Parser.parseQueryPrimary(Parser.java:2834)
        at org.h2.command.Parser.parseQueryTerm(Parser.java:2690)
        at org.h2.command.Parser.parseQueryExpressionBody(Parser.java:2669)
        at org.h2.command.Parser.parseQueryExpressionBodyAndEndOfQuery(Parser.java:2662)
        at org.h2.command.Parser.parseQueryExpression(Parser.java:2655)
        at org.h2.command.Parser.parseQuery(Parser.java:2624)
        at org.h2.command.Parser.parsePrepared(Parser.java:732)
        at org.h2.command.Parser.parse(Parser.java:697)
        at org.h2.command.Parser.parse(Parser.java:674)
        at org.h2.command.Parser.prepareCommand(Parser.java:577)
        at org.h2.engine.SessionLocal.prepareLocal(SessionLocal.java:634)
        at org.h2.engine.SessionLocal.prepareCommand(SessionLocal.java:557)
        at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1116)
        at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:92)
        at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:288)
        at io.ebean.datasource.pool.PooledConnection.prepareStatement(PooledConnection.java:308)
        at io.ebean.datasource.pool.PooledConnection.prepareStatement(PooledConnection.java:285)
        at io.ebeaninternal.server.query.CQuery.prepareResultSet(CQuery.java:329)
        at io.ebeaninternal.server.query.CQuery.prepareBindExecuteQueryWithOption(CQuery.java:298)
        at io.ebeaninternal.server.query.CQuery.prepareBindExecuteQuery(CQuery.java:294)
        at io.ebeaninternal.server.query.CQueryEngine.findMany(CQueryEngine.java:343)
        ... 10 more

[raphaelNguyen]

Thanks for the quick fix @rbygrave!

[rbygrave]

Cheers, pretty easy when you provide the nice failing test for us - thanks for that !!