Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Closed
raphaelNguyen opened this issue Oct 9, 2024 · 2 comments · Fixed by #3493
Closed

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

raphaelNguyen opened this issue Oct 9, 2024 · 2 comments · Fixed by #3493
Assignees
@rbygrave
Labels
bug
Milestone
14.7.0

Comments

@raphaelNguyen
Copy link
Contributor

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
rbygrave added a commit that referenced this issue Oct 10, 2024
@rbygrave
#3490 filterMany generates invalid sql when the many side uses @Embed…
45cc78b 
…dedId to model composite primary key
@rbygrave rbygrave linked a pull request Oct 10, 2024 that will close this issue
#3490 filterMany generates invalid sql when the many side uses @EmbeddedId to model composite primary key #3493
Merged
rbygrave added a commit that referenced this issue Oct 10, 2024
@rbygrave
#3490 filterMany generates invalid sql when the many side uses @Embed…
8e627c7 
…dedId to model composite primary key
rbygrave added a commit that referenced this issue Oct 10, 2024
@rbygrave
Merge pull request #3493 from ebean-orm/feature/3490
a499c8c 
#3490 filterMany generates invalid sql when the many side uses @EmbeddedId to model composite primary key
@rbygrave rbygrave self-assigned this Oct 10, 2024
@rbygrave rbygrave added the bug label Oct 10, 2024
@rbygrave rbygrave added this to the 14.6.1 milestone Oct 10, 2024
@raphaelNguyen
Copy link
Contributor Author

Thanks for the quick fix @rbygrave!

@rbygrave
Copy link
Member

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@rbygrave rbygrave

Labels
bug
Projects
None yet
Milestone
14.7.0
2 participants
@rbygrave @raphaelNguyen