With the addition of ONLY_FULL_GROUP_BY option to the datajoint/mysql recently, there is increased need and priority for this feature as simple, single-table aggregations (e.g. summation of all values in a column) that were allowed before are no longer properly defined queries.
As an example, previously this would work:
>> University.Student().proj('max(student_id) -> max_id')
ans =
Object dj.internal.GeneralRelvar
0: fakeservices.datajoint.io via TCP/IP Server version 5.7.33 (encrypted)
STUDENT_ID max_id
__________ ______
1 3
3 tuples (0.871 s)
However, it will now (as intended) throw this error on database servers with the new option:
>> University.Student().proj('max(student_id) -> max_id')
ans =
Object dj.internal.GeneralRelvar
Error using mym
In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column
'djtest_university.student.student_id'; this is incompatible with sql_mode=only_full_group_by
Error in dj.Connection/query (line 183)
ret=mym(self.connId, queryStr, v{:});
Error in dj.internal.GeneralRelvar/fetch (line 213)
ret = self.conn.query(sprintf('SELECT %s FROM %s%s', ...
Error in dj.internal.GeneralRelvar/disp (line 89)
preview = self.fetch(attrList{:}, sprintf('LIMIT %d', maxRows+1));
The real solution for this will be to structure a query with the new universal set feature once completed. The workaround for now is to simply fetch and perform the aggregation client side until this enhancement is implemented.
With the addition of ONLY_FULL_GROUP_BY option to the
datajoint/mysqlrecently, there is increased need and priority for this feature as simple, single-table aggregations (e.g. summation of all values in a column) that were allowed before are no longer properly defined queries.As an example, previously this would work:
However, it will now (as intended) throw this error on database servers with the new option:
The real solution for this will be to structure a query with the new universal set feature once completed. The workaround for now is to simply fetch and perform the aggregation client side until this enhancement is implemented.