Hibernate SQLServer2017 Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Question

Ok I am running the SQL native query below in my SpringBoot application with hibernate, please note this query is running fine in Sql Management Studio and returning some results. In my IDE I can use JDBC driver to connect to my SQL server 2017 and I can still run this query and return some results, now comes the strange part, when the same query is run inside the application I am getting the following error:

Column 'ESB_TRANSACTION.time_in' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

Here is the query:

SELECT
        CASE WHEN (suc.transaction_time IS NOT NULL) THEN suc.transaction_time ELSE err.transaction_time END as transaction_time,
        CASE WHEN (suc.success_count IS NOT NULL) THEN suc.success_count ELSE 0 END as success_count,
        CASE WHEN (err.error_count IS NOT NULL) THEN err.error_count ELSE 0 END as error_count
FROM
  (
    SELECT
          COUNT(et.status) error_count,
          DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, 0, et.time_in) / 1440) * 1440, 0) AS transaction_time
    FROM
          ESB_TRANSACTION et
    WHERE
          (et.status='ERROR')  AND (et.time_in BETWEEN '2019-01-01 00:00:00' AND '2019-12-12 23:59:59')
    GROUP BY DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, 0, et.time_in) / 1440) * 1440, 0)
  ) err
  RIGHT OUTER JOIN (
    SELECT
          COUNT(et.status) success_count,
          DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, 0, et.time_in) / 1440) * 1440, 0) AS transaction_time
    FROM
          ESB_TRANSACTION et
    WHERE
          (et.status='SUCCESS')  AND (et.time_in BETWEEN '2019-01-01 00:00:00' AND '2019-12-12 23:59:59')
    GROUP BY DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, 0, et.time_in) / 1440) * 1440, 0)
  ) suc ON err.transaction_time = suc.transaction_time
  ORDER BY transaction_time ASC

How exactly am i supposed to solve that?

Here is the relevant java code:

`public List execute(final String query, final DateRange selectedRange, final int minutes) {
    return (List) repository.getEntityManager()
            .createNativeQuery(query)
            .setParameter("startTime", selectedRange.getFrom())
            .setParameter("endTime", selectedRange.getTo())
            .setParameter("periodInterval", minutes   )
            .getResultList();
  }

The following simple java program to test the validity of the SQL is working fine:

        try (Connection con = DriverManager.getConnection(connectionUrl); Statement stmt = con.createStatement();) {
          String contents = new String(Files.readAllBytes(Paths.get("C:\\Temp\\mssqlserver.sql")));
            ResultSet rs = stmt.executeQuery(contents);
            while (rs.next()) {
                System.out.println(rs.getString("transaction_time") + " =>" + rs.getInt("success_count") +" =>"+ rs.getInt("error_count"));
            }
        }
        catch (IOException | SQLException e) {
            e.printStackTrace();
        }

I actually get proper output, so what is wrong with my Spring Boot + Hibernate?

Adeel Ansari Adeel Ansari · Accepted Answer · 2019-04-11T07:13:41

Try this,

SELECT
    CASE WHEN (suc.transaction_time IS NOT NULL) THEN suc.transaction_time ELSE err.transaction_time END as transaction_time,
    CASE WHEN (suc.success_count IS NOT NULL) THEN suc.success_count ELSE 0 END as success_count,
    CASE WHEN (err.error_count IS NOT NULL) THEN err.error_count ELSE 0 END as error_count
FROM
    (
    SELECT
         COUNT(et.status) error_count, et.time_in AS transaction_time
    FROM
        (
        SELECT
            eti.status,
            DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, 0, eti.time_in) / 1440) * 1440, 0) AS time_in
        FROM ESB_TRANSACTION eti
        WHERE
            (eti.status='ERROR')  AND (eti.time_in BETWEEN '2019-01-01 00:00:00' AND '2019-12-12 23:59:59')
        ) et    
    GROUP BY et.time_in
    ) err
RIGHT OUTER JOIN
    (
    SELECT
          COUNT(et.status) success_count, et.time_in AS transaction_time
    FROM
        (
        SELECT eti.status,
               DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, 0, eti.time_in) / 1440) * 1440, 0) AS time_in
        FROM ESB_TRANSACTION eti
        WHERE
            (eti.status='SUCCESS')  AND (eti.time_in BETWEEN '2019-01-01 00:00:00' AND '2019-12-12 23:59:59')
        ) et    
    GROUP BY et.time_in
    ) suc  ON err.transaction_time = suc.transaction_time
ORDER BY transaction_time ASC

Note: I don't have SQLServer, neither I gave it a go on any other DB. It should run as it is, nonetheless; if not, then probably it might require some minor syntax fix. However, result should be the one expected. In any case, it would, definitely, give you the idea. Hoping for good.

Hibernate SQLServer2017 Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

2 Answers