Bug with grouping and HQL


I’m having an issue with grouping elements with HQL. I think it should be reproducible by making something like this http://sqlfiddle.com/#!9/0d4e2f/4, translate the second query to HQL and use query.list() to get the data. My actual query is a bit bigger, but I don’t think that is my issue. With normal SQL with dbaccess or sqlcmd it works and when I try to do it in HQL it just skips the ones where count(*) gives more than 1 number as you see in the first query in sqlfiddle.

Things I’ve tried

  • using select new class(params)
  • changing all the parameters in select to constant values
  • using just that part above

I’m using informix, hibernate 4.2.19.Final and I’m not the one making tables.

Any help with this would be much appreciated :slight_smile:

Try to replicate it with this test case template.

If you can’t replicate it, maybe it’s because of some bug that got fixed in 5.2. Hibernate 4.2 is quite old and lots of things have changed since 4.x.

I was hoping that someone here could do a simple test on their own DB, or had heard about this as a bug from before. Thing is I don’t have an own DB to test on…

If you tested it on your DB and it is working in hibernate >4.2.19 then I’d guess it is fixed, and I don’t have to do anything more with it. Otherwise there is still a bug that hasn’t been fixed.

HQL translates to SQL, so you have to enable SQL logging and check what SQL query gets generated.

You need to add the actual HQL, mappings and the generated SQL to this post, otherwise it’s not clear what the problem is.

I’ve already enabled logging and caught the sql from the jboss console log. I tried with the values plugged in and used it in a normal sqlquery, which worked. Then the same values in the HQL and it didn’t work (as in there was no output when I put in the value that gives multiple items for count and it worked for the one that only had a single value from count). I’m sure there is an error here on the hibernate side, but I don’t want to share everything around the query.

The way I’ve described it should make it easy to test if there is an issue.

Looking forward to seeing the replicating test case. It’s still not clear what the problem is, so the replicating test case will better demonstrate that.

My code was working :slight_smile: problem with a replicating service that was stopped. Just coincidental that all the data I was working on had just 1 grouping and that there were more groups in the server that wasn’t replicating anymore.

Is there a way of marking solved or delete?

Yes. I marked your answer as the solution