ERROR: column "this_.created_time" must appear in the GROUP BY clause or be used in an aggregate function

Question

I encountered a problem when I did a query with hibernate's criteria. The generated SQL is very simple:

/* criteria query */ 
select
    count(*) as y0_ 
from
    cx_vss_video this_ 
where
    this_.uploader_id=? 
order by
    this_.created_time asc

And it complained the error:

    Caused by: org.postgresql.util.PSQLException: ERROR: column "this_.created_time" must appear in the GROUP BY clause or be used in an aggregate function
  Position: 105
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157)

There was a existed samilar problem here. But its answer seems very complicated and not resonable. So any another solution?

My entity class:

@Entity
@Table(name = "cx_vss_video")
public class Video {

    public enum TranscodingStatus {
      NOT_START, TRANSCODING, SUCCESS, ERROR, CANCELED;
    }

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "cx_vss_video_seq")
    @SequenceGenerator(name = "cx_vss_video_seq", sequenceName = "cx_vss_video_seq")
    private Long id;

    @Column(name = "uploader_name", nullable = false)
    private String uploaderName;

    @Column(name = "file_name")
    @NaturalId
    private String fileName;

    @Column(name = "created_time")
    private Date createdTime = new Date();

    @Column(name = "transcoding_status")
    @Enumerated(EnumType.STRING)
    private TranscodingStatus transcodingStatus = TranscodingStatus.TRANSCODING;
}

I can order by file_name normaly but no luck when ordered by created_time. I'm confused where does the problem come about? And the problem also arise when I using H2.

EDIT I finally solved the problem by removing the order argument and it works now.

CriteriaImpl criteriaImpl = criteria instanceof CriteriaImpl ? (CriteriaImpl) criteria : null;
  if (criteriaImpl != null) {
    Iterator<OrderEntry> it = criteriaImpl.iterateOrderings();
    while (it.hasNext()) {
      it.next();
      it.remove();
    }
    criteria.setProjection(Projections.rowCount());
    totalResults = (long) criteria.uniqueResult();
  }

score 0 · Accepted Answer · answered Oct 25 '16 at 10:45

You need an aggregation function on the query. So, it should look like this:

select count(*) as y0_ 
from cx_vss_video this_ 
where this_.uploader_id = ? 
group by ??
order by max(this_.created_time) asc;

That is, you need a group by and to fix the order by.

Why? Well, your original query -- without the group by -- is an aggregation query that returns one row. It doesn't make sense to order a result set with one row. So, I assume you want to aggregate by something, hence the ??. Then, you can order by either an aggregation column or an aggregation function.

ERROR: column "this_.created_time" must appear in the GROUP BY clause or be used in an aggregate function

1 Answers1