Problems with sql_mode=ONLY_FULL_GROUP_BY?

Question

MySQL

Good afternoon, help me defeat the error:
MySQL error in file: W:\eee3.ru\index.php at line 21

Expression #4 of SELECT list is not in GROUP BY clause and contains
nonaggregated column 'test.so.idate' which is not functionally
dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

select SQL_CALC_FOUND_ROWS name, ( max(word in ('ен')) + max(word in ('от')) ) as R, sum(weight) as W, idate, id_obj 
from search_object as so, search_index as i where so.id = i.id_obj 
and word in ('ен', 'от') group by name 
order by R desc, W desc, idate desc limit 0, 10

How to fit the request more or less correctly?

Answer 1

In the ONLY_FULL_GROUP_BY mode, all data received in the query must be in aggregate functions (SUM, COUNT, MAX, etc.) or grouped by them. In your query, `idate` is neither there nor there, so MySQL in strict mode doesn't know which table row to get the value from.
Either remove the `idate` column from the query, or include it in the aggregate function.
You can disable ONLY_FULL_GROUP_BY, then `idate` will be taken, as a rule, from the first line that got into the grouping.