January 2023 - hibernate-issues - Jboss List Archives

[JIRA] (HHH-15984) Pagination error with Postgres : SQL Error: 0, SQLState: 42601 ERROR: syntax error

by Christian Beikov (JIRA)

Christian Beikov ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *commented* on HHH-15984 ( https://hibernate.atlassian.net/browse/HHH-15984?atlOrigin=eyJpIjoiZWU3Nz... ) Re: Pagination error with Postgres : SQL Error: 0, SQLState: 42601 ERROR: syntax error ( https://hibernate.atlassian.net/browse/HHH-15984?atlOrigin=eyJpIjoiZWU3Nz... ) No idea what is wrong on your end, but according to the documentation ( https://www.postgresql.org/docs/9.6/sql-select.html#SQL-LIMIT ) , this syntax should also be supported on 9.6. In fact, I just tried running your query on 9.5 and it works for me. Maybe try updating your JDBC driver. Apart from that, note that 9.6 is not supported anymore and version 10 just recently EOLed. Starting with Hibernate 6.2 we make it clear by emitting a warning, that we only support a certain range of DB versions. In fact, we generally only support DB versions that are also still supported by the vendors. For 6.2 we will only support PostgreSQL 10+. If you can point us to a mistake that we made, we can look into that, but from what I see, it seems like your DB doesn’t behave well. Until then, I’ll close this ticket, but can reopen it if you can point me to something specific. ( https://hibernate.atlassian.net/browse/HHH-15984#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-15984#add-comment?atlOrigin=ey... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100213- sha1:1fa7b87 )

1 year, 5 months

1
0
0 / 0

[JIRA] (HHH-16009) jakarta @OrderBy annotation generates a non-transformed column name in the native query for a composite key's field

by Andrey Lebedev (JIRA)

Andrey Lebedev ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *updated* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiM2Q4ZGNmMjU0... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16009?atlOrigin=eyJpIjoiM2Q4ZG... ) HHH-16009 ( https://hibernate.atlassian.net/browse/HHH-16009?atlOrigin=eyJpIjoiM2Q4ZG... ) jakarta @OrderBy annotation generates a non-transformed column name in the native query for a composite key's field ( https://hibernate.atlassian.net/browse/HHH-16009?atlOrigin=eyJpIjoiM2Q4ZG... ) Change By: Andrey Lebedev ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) Please, refer to this Repository that contains a project that reproduces the problem: [https://github.com/Andremoniy/hibernate-6-order-by-issue|https://github.c...] The error is: {noformat}o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 42122, SQLState: 42S22 o.h.engine.jdbc.spi.SqlExceptionHelper : Column "D1_0.DEPARTMENTCODE" not found; SQL statement: select c1_0.id,d1_0.company_id,d1_0.department_code,d1_0.name,c1_0.name from company c1_0 left join department d1_0 on c1_0.id=d1_0.company_id where c1_0.name=? order by d1_0.departmentCode desc [42122-214]{noformat} Basically, under certain conditions the column name provided in the {{OrderBy}} annotation is not transformed to the physical name of the column in the database. This is a combination of the three different conditions: # The field used in the {{OrderBy}} should be a part of a composite key on the mapped entity. In our case this is {{DepartmentId}} composite key: {noformat}public class DepartmentId implements Serializable { private Company company; private String departmentCode; }{noformat} # 2. The field used in the {{OrderBy}} entity should contain more than a word in its name (if we rename {{departmentCode}} into {{code}}, the issue goes away). # 3. There should be an HQL query that uses {{LEFT JOIN FETCH}} on the problematic entity. {noformat}@Query(""" SELECT c FROM Company c LEFT JOIN FETCH c.departments WHERE c.name = :name """ ) Optional<Company> findCompanyByName(@Param("name") String name);{noformat} ( https://hibernate.atlassian.net/browse/HHH-16009#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16009#add-comment?atlOrigin=ey... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100213- sha1:1fa7b87 )

1 year, 5 months

1
0
0 / 0

[JIRA] (HHH-16009) jakarta @OrderBy annotation generates a non-transformed column name in the native query for a composite key's field

by Andrey Lebedev (JIRA)

Andrey Lebedev ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *updated* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiMjJhN2E0OTdh... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16009?atlOrigin=eyJpIjoiMjJhN2... ) HHH-16009 ( https://hibernate.atlassian.net/browse/HHH-16009?atlOrigin=eyJpIjoiMjJhN2... ) jakarta @OrderBy annotation generates a non-transformed column name in the native query for a composite key's field ( https://hibernate.atlassian.net/browse/HHH-16009?atlOrigin=eyJpIjoiMjJhN2... ) Change By: Andrey Lebedev ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) Please, refer to this Repository that contains a project that reproduces the problem: [https://github.com/Andremoniy/hibernate-6-order-by-issue|https://github.c...] The error is: {noformat}o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 42122, SQLState: 42S22 o.h.engine.jdbc.spi.SqlExceptionHelper : Column "D1_0.DEPARTMENTCODE" not found; SQL statement: select c1_0.id,d1_0.company_id,d1_0.department_code,d1_0.name,c1_0.name from company c1_0 left join departme department d1_0 on c1_0.id=d1_0.company_id where c1_0.name=? order by d1_0.departmentCode desc [42122-214] {noformat} Basically, under certain conditions the column name provided in the {{OrderBy}} annotation is not transformed to the physical name of the column in the database. This is a combination of the three different conditions: # The field used in the {{OrderBy}} should be a part of a composite key on the mapped entity. In our case this is {{DepartmentId}} composite key: {noformat}public class DepartmentId implements Serializable { private Company company; private String departmentCode; }{noformat} # The field used in the {{OrderBy}} entity should contain more than a word in its name (if we rename {{departmentCode}} into {{code}}, the issue goes away). # There should be an HQL query that uses {{LEFT JOIN FETCH}} on the problematic entity. {noformat}@Query(""" SELECT c FROM Company c LEFT JOIN FETCH c.departments WHERE c.name = :name """ ) Optional<Company> findCompanyByName(@Param("name") String name);{noformat} ( https://hibernate.atlassian.net/browse/HHH-16009#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16009#add-comment?atlOrigin=ey... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100213- sha1:1fa7b87 )

1 year, 5 months

1
0
0 / 0

[JIRA] (HHH-16009) jakarta @OrderBy annotation generates a non-transformed column name in the native query for a composite key's field

by AndreyL (JIRA)

AndreyL ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *updated* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiMGZlZmRmYmNm... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16009?atlOrigin=eyJpIjoiMGZlZm... ) HHH-16009 ( https://hibernate.atlassian.net/browse/HHH-16009?atlOrigin=eyJpIjoiMGZlZm... ) jakarta @OrderBy annotation generates a non-transformed column name in the native query for a composite key's field ( https://hibernate.atlassian.net/browse/HHH-16009?atlOrigin=eyJpIjoiMGZlZm... ) Change By: AndreyL ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) Please, refer to this Repository that contains a project that reproduces the problem: [https://github.com/Andremoniy/hibernate-6-order-by-issue|https://github.c...] The error is: {noformat}o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 42122, SQLState: 42S22 o.h.engine.jdbc.spi.SqlExceptionHelper : Column "D1_0.DEPARTMENTCODE" not found; SQL statement: select c1_0.id,d1_0.company_id,d1_0.department_code,d1_0.name,c1_0.name from company c1_0 left join departme{noformat} Basically, under certain conditions the column name provided in the {{OrderBy}} annotation is not transformed to the physical name of the column in the databse database. This is a combination of the three different conditions: # The field used in the {{OrderBy}} should be a part of a composite key on the mapped entity. In our case this is {{DepartmentId}} composite key: {noformat}public class DepartmentId implements Serializable { private Company company; private String departmentCode; }{noformat} # The field used in the {{OrderBy}} entity should contain more than a word in its name (if we rename {{departmentCode}} into {{code}}, the issue goes away). # There should be an HQL query that uses {{LEFT JOIN FETCH}} on the problematic entity. {noformat}@Query(""" SELECT c FROM Company c LEFT JOIN FETCH c.departments WHERE c.name = :name """ ) Optional<Company> findCompanyByName(@Param("name") String name);{noformat} ( https://hibernate.atlassian.net/browse/HHH-16009#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16009#add-comment?atlOrigin=ey... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100213- sha1:1fa7b87 )

1 year, 5 months

1
0
0 / 0

[JIRA] (HHH-16009) jakarta @OrderBy annotation generates a non-transformed column name in the native query for a composite key's field

by AndreyL (JIRA)

AndreyL ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *commented* on HHH-16009 ( https://hibernate.atlassian.net/browse/HHH-16009?atlOrigin=eyJpIjoiODFhZD... ) Re: jakarta @OrderBy annotation generates a non-transformed column name in the native query for a composite key's field ( https://hibernate.atlassian.net/browse/HHH-16009?atlOrigin=eyJpIjoiODFhZD... ) The error is: o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 42122, SQLState: 42S22 o.h.engine.jdbc.spi.SqlExceptionHelper : Column "D1_0.DEPARTMENTCODE" not found; SQL statement: select c1_0.id,d1_0.company_id,d1_0.department_code,d1_0.name,c1_0.name from company c1_0 left join departme ( https://hibernate.atlassian.net/browse/HHH-16009#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16009#add-comment?atlOrigin=ey... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100213- sha1:1fa7b87 )

1 year, 5 months

1
0
0 / 0

[JIRA] (HHH-16009) jakarta @OrderBy annotation generates a non-transformed column name in the native query for a composite key's field

by AndreyL (JIRA)

AndreyL ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *created* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiNzYwZmFmN2Q5... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16009?atlOrigin=eyJpIjoiNzYwZm... ) HHH-16009 ( https://hibernate.atlassian.net/browse/HHH-16009?atlOrigin=eyJpIjoiNzYwZm... ) jakarta @OrderBy annotation generates a non-transformed column name in the native query for a composite key's field ( https://hibernate.atlassian.net/browse/HHH-16009?atlOrigin=eyJpIjoiNzYwZm... ) Issue Type: Bug Affects Versions: 6.1.6 Assignee: Unassigned Created: 09/Jan/2023 09:38 AM Environment: Hibernate 6.1.6 Java 17 Priority: Blocker Reporter: AndreyL ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) Please, refer to this Repository that contains a project that reproduces the problem: https://github.com/Andremoniy/hibernate-6-order-by-issue ( https://github.com/Andremoniy/hibernate-6-order-by-issue ) Basically, under certain conditions the column name provided in the OrderBy annotation is not transformed to the physical name of the column in the databse. This is a combination of the three different conditions: * The field used in the OrderBy should be a part of a composite key on the mapped entity. In our case this is DepartmentId composite key: public class DepartmentId implements Serializable { private Company company; private String departmentCode; } * The field used in the OrderBy entity should contain more than a word in its name (if we rename departmentCode into code , the issue goes away). * There should be an HQL query that uses LEFT JOIN FETCH on the problematic entity. @Query(""" SELECT c FROM Company c LEFT JOIN FETCH c.departments WHERE c.name = :name """ ) Optional<Company> findCompanyByName(@Param("name") String name); ( https://hibernate.atlassian.net/browse/HHH-16009#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16009#add-comment?atlOrigin=ey... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100213- sha1:1fa7b87 )

1 year, 5 months

1
0
0 / 0

[JIRA] (HHH-16003) Wrong alias used in subquery generated from JPQL

by Volker Seibt (JIRA)

Volker Seibt ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5a4ea7a... ) *updated* an issue Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiYzkzYmRiZWJm... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16003?atlOrigin=eyJpIjoiYzkzYm... ) HHH-16003 ( https://hibernate.atlassian.net/browse/HHH-16003?atlOrigin=eyJpIjoiYzkzYm... ) Wrong alias used in subquery generated from JPQL ( https://hibernate.atlassian.net/browse/HHH-16003?atlOrigin=eyJpIjoiYzkzYm... ) Change By: Volker Seibt ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5a4ea7a... ) For this JPQL {code:java}select mdf.id from MasterDataFileEntity mdf where mdf.dataImportStatus = 'SUCCESS' and mdf.metaData.country = :countryCode and mdf.metaData.transportMode = :transportMode and mdf.metaData.product = :product and mdf.importFinishedAt = (select max(mdf.importFinishedAt) from MasterDataFileEntity mdf where mdf.dataImportStatus = 'SUCCESS' and mdf.metaData.country = :countryCode and mdf.metaData.transportMode = :transportMode and mdf.metaData.product = :product){code} Hibernate generates this SQL {code:sql}select m1_0.id from PEL.pel_master_data_file m1_0 where m1_0.data_import_status='SUCCESS' and m1_0.country_code=? and m1_0.transport_mode=? and m1_0.product=? and m1_0.import_finished_at= (select max(m2_0.import_finished_at) from PEL.pel_master_data_file m2_0 where m2_0.data_import_status='SUCCESS' and m1_0.country_code=? and m1_0.transport_mode=? and m1_0.product=?){code} The table alias for the subquery for all conditions joined by {{and}} is wrong, taken not from the subquery but from the main query. Workaround is to use a different alias for the subquery in JPQL. Test case: [https://github.com/vseibt/hibernate-test-case-templates/commit/405bc954f7...] ( https://hibernate.atlassian.net/browse/HHH-16003#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16003#add-comment?atlOrigin=ey... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100213- sha1:1fa7b87 )

1 year, 5 months

1
0
0 / 0

[JIRA] (HHH-15984) Pagination error with Postgres : SQL Error: 0, SQLState: 42601 ERROR: syntax error

by Dimitris Valasidis (JIRA)

Dimitris Valasidis ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=6356848... ) *commented* on HHH-15984 ( https://hibernate.atlassian.net/browse/HHH-15984?atlOrigin=eyJpIjoiN2FkYj... ) Re: Pagination error with Postgres : SQL Error: 0, SQLState: 42601 ERROR: syntax error ( https://hibernate.atlassian.net/browse/HHH-15984?atlOrigin=eyJpIjoiN2FkYj... ) PostgreSQL 9.6.2 on x86_64-pc-linux-gnu ( https://hibernate.atlassian.net/browse/HHH-15984#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-15984#add-comment?atlOrigin=ey... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100213- sha1:1fa7b87 )

1 year, 5 months

1
0
0 / 0

[JIRA] (HHH-15984) Pagination error with Postgres : SQL Error: 0, SQLState: 42601 ERROR: syntax error

by Christian Beikov (JIRA)

Christian Beikov ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *commented* on HHH-15984 ( https://hibernate.atlassian.net/browse/HHH-15984?atlOrigin=eyJpIjoiYTFjMD... ) Re: Pagination error with Postgres : SQL Error: 0, SQLState: 42601 ERROR: syntax error ( https://hibernate.atlassian.net/browse/HHH-15984?atlOrigin=eyJpIjoiYTFjMD... ) What is the version of the database that you are using? ( https://hibernate.atlassian.net/browse/HHH-15984#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-15984#add-comment?atlOrigin=ey... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100213- sha1:1fa7b87 )

1 year, 5 months

1
0
0 / 0

[JIRA] (HHH-15984) Pagination error with Postgres : SQL Error: 0, SQLState: 42601 ERROR: syntax error

by Dimitris Valasidis (JIRA)

Dimitris Valasidis ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=6356848... ) *commented* on HHH-15984 ( https://hibernate.atlassian.net/browse/HHH-15984?atlOrigin=eyJpIjoiMTA4Zj... ) Re: Pagination error with Postgres : SQL Error: 0, SQLState: 42601 ERROR: syntax error ( https://hibernate.atlassian.net/browse/HHH-15984?atlOrigin=eyJpIjoiMTA4Zj... ) I have used : * org.hibernate.dialect.PostgreSQL94Dialect * org.hibernate.dialect.PostgreSQLDialect * org.hibernate.dialect.PostgreSQL95Dialect and i havent seen any changes. ( https://hibernate.atlassian.net/browse/HHH-15984#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-15984#add-comment?atlOrigin=ey... ) Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100213- sha1:1fa7b87 )

1 year, 5 months

1
0
0 / 0

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009

2008

2007

2006

hibernate-issues January 2023