[JIRA] (HHH-16349) ORA-00979 not a group by expression when using a polymorphic entity in HQL group by
by Jones (JIRA)
Jones ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *updated* an issue
Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiYmE5MzkxNGIw... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16349?atlOrigin=eyJpIjoiYmE5Mz... ) HHH-16349 ( https://hibernate.atlassian.net/browse/HHH-16349?atlOrigin=eyJpIjoiYmE5Mz... ) ORA-00979 not a group by expression when using a polymorphic entity in HQL group by ( https://hibernate.atlassian.net/browse/HHH-16349?atlOrigin=eyJpIjoiYmE5Mz... )
Change By: Jones ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... )
Given
* Polymorphic entities
When
* HQL with a group by using a polymorphic entity
Then
* An exception in oracle occurs {{ORA-00979 not a group by expression}}
{code:java}@Entity
@Table(name = "MY_TABLE")
public class MyEntity {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "ID")
private Integer id;
@Column(name = "amount")
private Integer amount;
@JoinColumn(name = "REL_ENTITY", foreignKey = @ForeignKey(name = "FK_REL_ENT_01"))
@ManyToOne
@Fetch(FetchMode.SELECT)
private RelatedEntity relatedEntity;
}{code}
{code:java}@Entity
@Table(name = "MY_TABLE")
@DiscriminatorColumn(name = "DISC_COL", discriminatorType = DiscriminatorType.INTEGER)
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public abstract class RelatedEntity {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "ID")
Integer id;
@Column(name = "RELATED_VALUE")
Integer value;
}{code}
{code:java}@Entity
@DiscriminatorValue("1")
public class ChildOneEntity extends RelatedEntity {
@Column(name = "ONLY_CHILD_ONE")
private Integer onlyChildOne;
}{code}
{code:java}@Entity
@DiscriminatorValue("2")
public class ChildTwoEntity extends RelatedEntity {
@Column(name = "ONLY_CHILD_TWO")
private Integer onlyChildTwo;
}{code}
{code:java}public class MyPojo {
Integer amount;
RelatedEntity relatedEntity;
public MyPojo(Integer amount, RelatedEntity relatedEntity) {
this.amount = amount;
this.relatedEntity = relatedEntity;
}
}{code}
{code:java} @Test
public void failingTest() throws Exception {
// BaseCoreFunctionalTestCase automatically creates the SessionFactory and
// provides the Session.
try (Session s = openSession()) {
Transaction tx = s.beginTransaction();
String hql = "select new org.hibernate.bugs.MyPojo (sum(e.amount), re ) from org.hibernate.bugs.MyEntity e join e.relatedEntity re where 1=1 group by re";
Query<MyPojo> query = s.createQuery(hql, MyPojo.class);
List<MyPojo> actual = query.list();
assertThat(actual).isEmpty();
// Verify the generated SQL
// In oracle we get ORA-00979 not a group by expression
tx.commit();
}
}{code}
{code:java}Hibernate:
select
sum(m1_0.amount),
r1_0.ID,
r1_0.DISC_COL,
r1_0.RELATED_VALUE,
r1_0.ONLY_CHILD_ONE,
r1_0.ONLY_CHILD_TWO
from
MY_TABLE m1_0
join
MY_TABLE r1_0
on r1_0.ID=m1_0.REL_ENTITY
where
1=1
group by
r1_0.ID,
r1_0.DISC_COL,
r1_0.RELATED_VALUE{code}
The generated group by does not include the {{r1_0.ONLY_CHILD_ONE}} and {{r1_0.ONLY_CHILD_TWO}}
which leads to ORA-00979 in oracle
Test case to be attached and also available here [https://github.com/ratoaq2/HHH-16349|https://github.com/ratoaq2/HHH-16349...]
( https://hibernate.atlassian.net/browse/HHH-16349#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16349#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#100219- sha1:eb3f1fe )
3 years
[JIRA] (HHH-16349) ORA-00979 not a group by expression when using a polymorphic entity in HQL group by
by Jones (JIRA)
Jones ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *created* an issue
Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiZDY4OGUxZjgw... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16349?atlOrigin=eyJpIjoiZDY4OG... ) HHH-16349 ( https://hibernate.atlassian.net/browse/HHH-16349?atlOrigin=eyJpIjoiZDY4OG... ) ORA-00979 not a group by expression when using a polymorphic entity in HQL group by ( https://hibernate.atlassian.net/browse/HHH-16349?atlOrigin=eyJpIjoiZDY4OG... )
Issue Type: Bug Affects Versions: 6.2.0.CR3, 6.2.0.CR4 Assignee: Unassigned Created: 22/Mar/2023 03:26 AM Priority: Major Reporter: Jones ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... )
Given
* Polymorphic entities
When
* HQL with a group by using a polymorphic entity
Then
* An exception in oracle occurs ORA-00979 not a group by expression
@Entity
@Table(name = "MY_TABLE" )
public class MyEntity {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "ID" )
private Integer id;
@Column(name = "amount" )
private Integer amount;
@JoinColumn(name = "REL_ENTITY" , foreignKey = @ForeignKey(name = "FK_REL_ENT_01" ))
@ManyToOne
@Fetch(FetchMode.SELECT)
private RelatedEntity relatedEntity;
}
@Entity
@Table(name = "MY_TABLE" )
@DiscriminatorColumn(name = "DISC_COL" , discriminatorType = DiscriminatorType.INTEGER)
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public abstract class RelatedEntity {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "ID" )
Integer id;
@Column(name = "RELATED_VALUE" )
Integer value;
}
@Entity
@DiscriminatorValue( "1" )
public class ChildOneEntity extends RelatedEntity {
@Column(name = "ONLY_CHILD_ONE" )
private Integer onlyChildOne;
}
@Entity
@DiscriminatorValue( "2" )
public class ChildTwoEntity extends RelatedEntity {
@Column(name = "ONLY_CHILD_TWO" )
private Integer onlyChildTwo;
}
public class MyPojo {
Integer amount;
RelatedEntity relatedEntity;
public MyPojo( Integer amount, RelatedEntity relatedEntity) {
this.amount = amount;
this.relatedEntity = relatedEntity;
}
}
@Test
public void failingTest() throws Exception {
// BaseCoreFunctionalTestCase automatically creates the SessionFactory and
// provides the Session.
try (Session s = openSession()) {
Transaction tx = s.beginTransaction();
String hql = "select new org.hibernate.bugs.MyPojo (sum(e.amount), re ) from org.hibernate.bugs.MyEntity e join e.relatedEntity re where 1=1 group by re" ;
Query<MyPojo> query = s.createQuery(hql, MyPojo.class);
List<MyPojo> actual = query.list();
assertThat(actual).isEmpty();
// Verify the generated SQL
// In oracle we get ORA-00979 not a group by expression
tx.commit();
}
}
Hibernate:
select
sum(m1_0.amount),
r1_0.ID,
r1_0.DISC_COL,
r1_0.RELATED_VALUE,
r1_0.ONLY_CHILD_ONE,
r1_0.ONLY_CHILD_TWO
from
MY_TABLE m1_0
join
MY_TABLE r1_0
on r1_0.ID=m1_0.REL_ENTITY
where
1=1
group by
r1_0.ID,
r1_0.DISC_COL,
r1_0.RELATED_VALUE
The generated group by does not include the r1_0.ONLY_CHILD_ONE and r1_0.ONLY_CHILD_TWO
which leads to ORA-00979 in oracle
Test case to be attached
( https://hibernate.atlassian.net/browse/HHH-16349#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16349#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#100219- sha1:eb3f1fe )
3 years