0

I'm using Spring Boot 1.5.2 and the following @Query:

@Query(value = "SELECT m FROM Message m WHERE m.from.userId = :myId OR m.to.userId = :myId GROUP BY m.from.userId, m.to.userId ORDER BY m.date DESC")
List<Message> chatOverview(@Param("myId") final Long myUserId);

The intention of the query is to create a chat messenger overview, where you see the last message of each conversation you had. It works fine for me in dev, but in production (newer MySQL database version) I get this error:

java.sql.SQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'message0_.message_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I read in this thread what the reason for this change was, however I couldn't find a way to fix this with JPA / Spring. I cannot change any settings in the production MySQL database and I would like to avoid any upgrading in Spring either. How can I fix my query?

schneida
  • 729
  • 3
  • 11
  • 37
  • "Spring Boot" doesnt create that query, your JPA provider does (which one?). And you haven't posted the ACTUAL SQL being invoked –  Sep 24 '17 at 06:24
  • Sure but Spring Boot defines the JPA provider -> Hibernate. For the actual SQL I will try to get it (I think it's logged with debug logging), but I don't think it's going to help that much since I can't modify the actual SQL but only the JPQL.... – schneida Sep 24 '17 at 06:30
  • YOU define the JPA provider (to Spring). And the actual SQL is then used to report a BUG on your chosen JPA provider, saying I wanted to execute this JPQL on this database and got this SQL which is invalid. –  Sep 24 '17 at 06:32
  • Yes disabling it is also documented in the linked thread above - however as I said, I cannot modify the settings in production. – schneida Sep 24 '17 at 07:13

1 Answers1

0

Here is the definition and purpose of the GROUP BY (see section 4.7) clause:

The GROUP BY construct enables the aggregation of result values according to a set of properties.

That means it is used only if you're aggregating (sum, avg, max, min, ...) the value(s) of a field(s). But in your case I don't see any aggregation function. So just remove the GROUP BY clause and everything should be fine:

SELECT m FROM Message m WHERE m.from.userId = :myId OR m.to.userId = :myId ORDER BY m.date DESC

Grouping on userId doesn't make sense too because all the entities returned by this query will have the same value for this field.

ujulu
  • 3,289
  • 2
  • 11
  • 14
  • Wouldn't your solution change the result set? I think your query would result in returning all chat messages between me and all my chat partners. With the group by I make the combination unique and will only get one chat message per chat partner (and one in the other direction). The group by is expected to give me only distinct results. – schneida Sep 25 '17 at 18:31
  • "The group by is expected to give me only distinct results" is wrong assumption and has nothing to do with distinct results. You're passing in a value for `userId` and only values for this user will be returned. As you can see in the official specification (highlighted above) `group by` is used only for aggregation purposes, and you are not using any aggregation functions. – ujulu Sep 25 '17 at 18:53
  • Ok you are right in this regard, however with older MySQL versions (and still with e.g. the latest MariaDB version) my query resulted exactly in what I wanted. Maybe though the better question is what's the correct JPA query for my use case! – schneida Sep 26 '17 at 07:38
  • 1
    @schneida You don't need to convince me if the query works as you wished. I am only telling you that in this query it doesn't make any difference if you're using the `group by` clause or not (regardless of your DB server is complaining about it). If you want to reformulate your question in a different way, create another question and delete this one. – ujulu Sep 26 '17 at 08:21