0

I have a JPA query structured like this:

SELECT distinct item
FROM ... 
WHERE ...
ORDER BY ...

I'm using MySQL 8 and because I'm using the ORDER BY clause to sort by item properties, area name country name and brand name) I was getting this error:

Expression #1 of ORDER BY clause is not in SELECT list, references column 'db.country4_.name' which is not in SELECT list; this is incompatible with DISTINCT

I had to include the order by fields in the SELECT:

SELECT distinct item, item.area.name, item.country.name, item.brand.name 
FROM ... 
WHERE ...
ORDER BY ...

The problem is that doing so the query now doesn't select anymore the items that have country == NULL.

Beside removing the distinct or changing the sql-mode?

Thanks

demetrio812
  • 290
  • 3
  • 15

1 Answers1

0

https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html says:

Consider the case of a table t with three columns c1, c2, and c3 that contains these rows:

c1 c2 c3
1  2  A
3  4  B
1  2  C

Suppose that we execute the following query, expecting the results to be ordered by c3:

SELECT DISTINCT c1, c2 FROM t ORDER BY c3;

Should the result be:

c1 c2
1  2
3  4

Or should it be:

c1 c2
3  4
1  2

If it ordered by c3, it should be the first example. But DISTINCT is implemented by copying the result rows to a temporary table, sorting that table, and eliminating duplicates. If c3 is not part of the select-list, then it is not included in the temporary table. But if c3 is included as part of the select-list, so it's in the temporary table, then it affects the DISTINCT operation.

The documentation goes on:

To prevent this problem, a query that has DISTINCT and ORDER BY is rejected as invalid if any ORDER BY expression does not satisfy at least one of these conditions:

  • The expression is equal to one in the select list

  • All columns referenced by the expression and belonging to the query's selected tables are elements of the select list

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828