Ganesh Tiwari (
https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5ee7797...
) *created* an issue
Hibernate ORM (
https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiOTYyYjE4ZTZm...
) / Bug (
https://hibernate.atlassian.net/browse/HHH-14112?atlOrigin=eyJpIjoiOTYyYj...
) HHH-14112 (
https://hibernate.atlassian.net/browse/HHH-14112?atlOrigin=eyJpIjoiOTYyYj...
) Invalid Pagination COUNT query generated with @Inheritance(strategy =
InheritanceType.JOINED) (
https://hibernate.atlassian.net/browse/HHH-14112?atlOrigin=eyJpIjoiOTYyYj...
)
Issue Type: Bug Assignee: Unassigned Components: hibernate-core Created: 17/Jul/2020 12:14
PM Priority: Major Reporter: Ganesh Tiwari (
https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5ee7797...
)
This bug might be related to
https://hibernate.atlassian.net/browse/HHH-13712
* Whats the issue?*
If you run the
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: 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: select count(subobject0_.id) as col_0_0_ from sub_object subobject0_ where (
subobject0_1_.DELETED = 0)
```
Which results in following exception:
```
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]
```
* How to reproduce:*
Clone and run
https://github.com/gtiwari333/hibernate-inheritance-JOINED-with-where-bug
then open
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-no...
* Related JIRA issue
https://hibernate.atlassian.net/browse/HHH-13712?jql=text%20~%20%22Inheri....
Looks like its not fixed for JOINED with @Where
* AST for both issues
AST for select query
```
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)'
```
AST for count query
```
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
Unknown macro: {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)'
```
(
https://hibernate.atlassian.net/browse/HHH-14112#add-comment?atlOrigin=ey...
) Add Comment (
https://hibernate.atlassian.net/browse/HHH-14112#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=Em...
) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100133- sha1:d093d11 )