[hibernate-issues] [JIRA] (HHH-14112) Invalid Pagination COUNT query generated with @Inheritance(strategy = InheritanceType.JOINED)
Ganesh Tiwari (JIRA)
jira at hibernate.atlassian.net
Fri Jul 17 15:16:49 EDT 2020
Ganesh Tiwari ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5ee779794784510aca5d04fa ) *updated* an issue
Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiYzkxM2E3NmUwNDZmNGRlNmFlZDYzMDE4ZWRhN2I0NmYiLCJwIjoiaiJ9 ) / Bug ( https://hibernate.atlassian.net/browse/HHH-14112?atlOrigin=eyJpIjoiYzkxM2E3NmUwNDZmNGRlNmFlZDYzMDE4ZWRhN2I0NmYiLCJwIjoiaiJ9 ) HHH-14112 ( https://hibernate.atlassian.net/browse/HHH-14112?atlOrigin=eyJpIjoiYzkxM2E3NmUwNDZmNGRlNmFlZDYzMDE4ZWRhN2I0NmYiLCJwIjoiaiJ9 ) Invalid Pagination COUNT query generated with @Inheritance(strategy = InheritanceType.JOINED) ( https://hibernate.atlassian.net/browse/HHH-14112?atlOrigin=eyJpIjoiYzkxM2E3NmUwNDZmNGRlNmFlZDYzMDE4ZWRhN2I0NmYiLCJwIjoiaiJ9 )
Change By: Ganesh Tiwari ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5ee779794784510aca5d04fa )
This bug might be related to [ https://hibernate.atlassian.net/browse/HHH-13712 |https://hibernate.atlassian.net/browse/HHH-13712]
* Whats the issue?*
If you run the [ http://localhost:8080/subobj, |http://localhost:8080/subobj,] the select query generates fine. But the count query doesn't contain JOIN statement so making the ' where ( subobject0_1_.DELETED = 0)' clause invalid.
```sql
Hibernate: {noformat}// select query --- is valid
select subobject0_.id as id1_1_, subobject0_1_.deleted as deleted2_1_, subobject0_.age as age1_0_, subobject0_.name as name2_0_ from sub_object subobject0_ inner join super subobject0_1_ on subobject0_.id=subobject0_1_.id where ( subobject0_1_.DELETED = 0) limit ?
Hibernate: //count query does't have join
select count(subobject0_.id) as col_0_0_ from sub_object subobject0_ where ( subobject0_1_.DELETED = 0) {noformat}
```
Which results in following exception:
```
{noformat} org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "SUBOBJECT0_1_.DELETED" not found; SQL statement:
select count(subobject0_.id) as col_0_0_ from sub_object subobject0_ where ( subobject0_1_.DELETED = 0) [42122-200] {noformat}
```
* How to reproduce:*
Clone and run [ https://github.com/gtiwari333/hibernate-inheritance-JOINED-with-where-bug |https://github.com/gtiwari333/hibernate-inheritance-JOINED-with-where-bug] then open [ http://localhost:8080/subobj |http://localhost:8080/subobj] on your browser
* Notes:*
* - It appears the issue is only with InheritanceType.JOINED. Other types works fine (see ``bug.entity.Super.java``)
*
* No issue when hibernate.version = 5.4.4.Final
* Stackoverflow question: [ https://stackoverflow.com/questions/62826016/spring-data-jpa-page-does-not-work-when-the-entity-has-a-soft-delete-and-is-in |https://stackoverflow.com/questions/62826016/spring-data-jpa-page-does-not-work-when-the-entity-has-a-soft-delete-and-is-in]
* Related JIRA issue [ https://hibernate.atlassian.net/browse/HHH-13712?jql=text%20~%20%22Inheritance%20count%20where%22 |https://hibernate. atlassian.net/browse/HHH-13712?jql=text%20~%20%22Inheritance%20count%20where%22]. Looks like its not fixed for JOINED with @Where
* AST for both issues
AST for select query
```
{noformat} 2020-07-17 13:46:53.856 DEBUG 15860 --- [nio-8080-exec-1] o.h.h.internal.ast.QueryTranslatorImpl : --- SQL AST ---
\-[SELECT] QueryNode: 'SELECT' querySpaces (super,sub_object)
+-[SELECT_CLAUSE] SelectClause: '{select clause}'
| +-[ALIAS_REF] IdentNode: 'subobject0_.id as id1_1_' {alias=generatedAlias0, className=bug.entity.SubObject, tableAlias=subobject0_}
| \-[SQL_TOKEN] SqlFragment: 'subobject0_1_.deleted as deleted2_1_, subobject0_.age as age1_0_, subobject0_.name as name2_0_'
+-[FROM] FromClause: 'from' FromClause{level=1, fromElementCounter=1, fromElements=1, fromElementByClassAlias=[generatedAlias0], fromElementByTableAlias=[subobject0_], fromElementsByPath=[], collectionJoinFromElementsByPath=[], impliedElements=[]}
| \-[FROM_FRAGMENT] FromElement: 'sub_object subobject0_ inner join super subobject0_1_ on subobject0_.id=subobject0_1_.id' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=generatedAlias0,role=null,tableName=sub_object,tableAlias=subobject0_,origin=null,columns={,className=bug.entity.SubObject}}
\-[WHERE] SqlNode: 'WHERE'
\-[FILTERS] SqlNode: '{filter conditions}'
\-[SQL_TOKEN] SqlFragment: '( subobject0_1_.DELETED = 0)'
```
{noformat}
AST for count query
```
{noformat} 2020-07-17 13:46:53.895 DEBUG 15860 --- [nio-8080-exec-1] o.h.h.internal.ast.QueryTranslatorImpl : --- SQL AST ---
\-[SELECT] QueryNode: 'SELECT' querySpaces (super,sub_object)
+-[SELECT_CLAUSE] SelectClause: '{select clause}'
| +-[COUNT] CountNode: 'count'
| | \-[ALIAS_REF] IdentNode: 'subobject0_.id' {alias=generatedAlias0, className=bug.entity.SubObject, tableAlias=subobject0_}
| \-[SELECT_COLUMNS] SqlNode: ' as col_0_0_'
+-[FROM] FromClause: 'from' FromClause{level=1, fromElementCounter=1, fromElements=1, fromElementByClassAlias=[generatedAlias0], fromElementByTableAlias=[subobject0_], fromElementsByPath=[], collectionJoinFromElementsByPath=[], impliedElements=[]}
| \-[FROM_FRAGMENT] FromElement: 'sub_object subobject0_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=generatedAlias0,role=null,tableName=sub_object,tableAlias=subobject0_,origin=null,columns={,className=bug.entity.SubObject}}
\-[WHERE] SqlNode: 'WHERE'
\-[FILTERS] SqlNode: '{filter conditions}'
\-[SQL_TOKEN] SqlFragment: '( subobject0_1_.DELETED = 0)'
``` {noformat}
( https://hibernate.atlassian.net/browse/HHH-14112#add-comment?atlOrigin=eyJpIjoiYzkxM2E3NmUwNDZmNGRlNmFlZDYzMDE4ZWRhN2I0NmYiLCJwIjoiaiJ9 ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-14112#add-comment?atlOrigin=eyJpIjoiYzkxM2E3NmUwNDZmNGRlNmFlZDYzMDE4ZWRhN2I0NmYiLCJwIjoiaiJ9 )
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.core&referrer=utm_source%3DNotificationLink%26utm_medium%3DEmail ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailNotificationLink&mt=8 ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100133- sha1:d093d11 )
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/hibernate-issues/attachments/20200717/70af4d98/attachment.html
More information about the hibernate-issues
mailing list