datajoint-python
datajoint-python copied to clipboard
Published
20 hours ago •
datajoint
datajoint
Error with `.aggr()` in datajoint 0.13.X
Bug Report
Description
When using the ... argument in table1.aggr(table2, ...) to retain all attributes from table1 the following error occurs: OperationalError: (1055, "'neuro_scan.scan.scanner' isn't in GROUP BY")
Reproducibility
- MACOS Version 11.5
- Python Version 3.7.9
- MariaDB Version 10.5.6
- MariaDB Deployment Strategy local-native
- DataJoint Version 0.13.1
- Error stack:
In [4]: session=dj.create_virtual_module('neuro_session','neuro_session')
In [5]: scan=dj.create_virtual_module('neuro_scan','neuro_scan')
In [6]: scan.Scan()
Out[6]:
*subject *session_datet *scan_id scanner acq_software scan_notes
+------------+ +------------+ +---------+ +------------+ +------------+ +------------+
test_scanimag 2020-06-09 17: 0 MINI2P_Imaging ScanImage
(Total: 1)
In [7]: session.Session()
Out[7]:
*subject *session_datet
+------------+ +------------+
test_scanimag 2020-06-09 17:
In [8]: session.Session.aggr(scan.Scan)
Out[8]:
*subject *session_datet
+------------+ +------------+
test_scanimag 2020-06-09 17:
(Total: 1)
In [9]: session.Session.aggr(scan.Scan,...)
Out[9]:
*subject *session_datet
+------------+ +------------+
test_scanimag 2020-06-09 17:
(Total: 1)
In [10]: scan.Scan.aggr(session.Session)
Out[10]:
*subject *session_datet *scan_id
+------------+ +------------+ +---------+
test_scanimag 2020-06-09 17: 0
(Total: 1)
In [11]: scan.Scan.aggr(session.Session,...)
Out[11]: ---------------------------------------------------------------------------
OperationalError Traceback (most recent call last)
/opt/miniconda3/envs/workflow-calcium-imaging/lib/python3.7/site-packages/IPython/core/formatters.py in __call__(self, obj)
700 type_pprinters=self.type_printers,
701 deferred_pprinters=self.deferred_printers)
--> 702 printer.pretty(obj)
703 printer.flush()
704 return stream.getvalue()
/opt/miniconda3/envs/workflow-calcium-imaging/lib/python3.7/site-packages/IPython/lib/pretty.py in pretty(self, obj)
392 if cls is not object \
393 and callable(cls.__dict__.get('__repr__')):
--> 394 return _repr_pprint(obj, self, cycle)
395
396 return _default_pprint(obj, self, cycle)
/opt/miniconda3/envs/workflow-calcium-imaging/lib/python3.7/site-packages/IPython/lib/pretty.py in _repr_pprint(obj, p, cycle)
698 """A pprint that just redirects to the normal repr function."""
699 # Find newlines and replace them with p.break_()
--> 700 output = repr(obj)
701 lines = output.splitlines()
702 with p.group():
/opt/miniconda3/envs/workflow-calcium-imaging/lib/python3.7/site-packages/datajoint/expression.py in __repr__(self)
527 :rtype: str
528 """
--> 529 return super().__repr__() if config['loglevel'].lower() == 'debug' else self.preview()
530
531 def preview(self, limit=None, width=None):
/opt/miniconda3/envs/workflow-calcium-imaging/lib/python3.7/site-packages/datajoint/expression.py in preview(self, limit, width)
531 def preview(self, limit=None, width=None):
532 """ :return: a string of preview of the contents of the query. """
--> 533 return preview(self, limit, width)
534
535 def _repr_html_(self):
/opt/miniconda3/envs/workflow-calcium-imaging/lib/python3.7/site-packages/datajoint/preview.py in preview(query_expression, limit, width)
11 if width is None:
12 width = config['display.width']
---> 13 tuples = rel.fetch(limit=limit + 1, format="array")
14 has_more = len(tuples) > limit
15 tuples = tuples[:limit]
/opt/miniconda3/envs/workflow-calcium-imaging/lib/python3.7/site-packages/datajoint/fetch.py in __call__(self, offset, limit, order_by, format, as_dict, squeeze, download_path, *attrs)
191 else: # fetch all attributes as a numpy.record_array or pandas.DataFrame
192 cur = self._expression.cursor(
--> 193 as_dict=as_dict, limit=limit, offset=offset, order_by=order_by)
194 heading = self._expression.heading
195 if as_dict:
/opt/miniconda3/envs/workflow-calcium-imaging/lib/python3.7/site-packages/datajoint/expression.py in cursor(self, offset, limit, order_by, as_dict)
517 sql += ' LIMIT %d' % limit + (' OFFSET %d' % offset if offset else "")
518 logger.debug(sql)
--> 519 return self.connection.query(sql, as_dict=as_dict)
520
521 def __repr__(self):
/opt/miniconda3/envs/workflow-calcium-imaging/lib/python3.7/site-packages/datajoint/connection.py in query(self, query, args, as_dict, suppress_warnings, reconnect)
300 cursor = self._conn.cursor(cursor=cursor_class)
301 try:
--> 302 self._execute_query(cursor, query, args, suppress_warnings)
303 except errors.LostConnectionError:
304 if not reconnect:
/opt/miniconda3/envs/workflow-calcium-imaging/lib/python3.7/site-packages/datajoint/connection.py in _execute_query(cursor, query, args, suppress_warnings)
266 cursor.execute(query, args)
267 except client.err.Error as err:
--> 268 raise translate_query_error(err, query)
269
270 def query(self, query, args=(), *, as_dict=False, suppress_warnings=True, reconnect=None):
/opt/miniconda3/envs/workflow-calcium-imaging/lib/python3.7/site-packages/datajoint/connection.py in _execute_query(cursor, query, args, suppress_warnings)
264 # suppress all warnings arising from underlying SQL library
265 warnings.simplefilter("ignore")
--> 266 cursor.execute(query, args)
267 except client.err.Error as err:
268 raise translate_query_error(err, query)
/opt/miniconda3/envs/workflow-calcium-imaging/lib/python3.7/site-packages/pymysql/cursors.py in execute(self, query, args)
146 query = self.mogrify(query, args)
147
--> 148 result = self._query(query)
149 self._executed = query
150 return result
/opt/miniconda3/envs/workflow-calcium-imaging/lib/python3.7/site-packages/pymysql/cursors.py in _query(self, q)
308 self._last_executed = q
309 self._clear_result()
--> 310 conn.query(q)
311 self._do_get_result()
312 return self.rowcount
/opt/miniconda3/envs/workflow-calcium-imaging/lib/python3.7/site-packages/pymysql/connections.py in query(self, sql, unbuffered)
546 sql = sql.encode(self.encoding, "surrogateescape")
547 self._execute_command(COMMAND.COM_QUERY, sql)
--> 548 self._affected_rows = self._read_query_result(unbuffered=unbuffered)
549 return self._affected_rows
550
/opt/miniconda3/envs/workflow-calcium-imaging/lib/python3.7/site-packages/pymysql/connections.py in _read_query_result(self, unbuffered)
773 else:
774 result = MySQLResult(self)
--> 775 result.read()
776 self._result = result
777 if result.server_status is not None:
/opt/miniconda3/envs/workflow-calcium-imaging/lib/python3.7/site-packages/pymysql/connections.py in read(self)
1154 def read(self):
1155 try:
-> 1156 first_packet = self.connection._read_packet()
1157
1158 if first_packet.is_ok_packet():
/opt/miniconda3/envs/workflow-calcium-imaging/lib/python3.7/site-packages/pymysql/connections.py in _read_packet(self, packet_type)
723 if self._result is not None and self._result.unbuffered_active is True:
724 self._result.unbuffered_active = False
--> 725 packet.raise_for_error()
726 return packet
727
/opt/miniconda3/envs/workflow-calcium-imaging/lib/python3.7/site-packages/pymysql/protocol.py in raise_for_error(self)
219 if DEBUG:
220 print("errno =", errno)
--> 221 err.raise_mysql_exception(self._data)
222
223 def dump(self):
/opt/miniconda3/envs/workflow-calcium-imaging/lib/python3.7/site-packages/pymysql/err.py in raise_mysql_exception(data)
141 if errorclass is None:
142 errorclass = InternalError if errno < 1000 else OperationalError
--> 143 raise errorclass(errno, errval)
OperationalError: (1055, "'neuro_scan.scan.scanner' isn't in GROUP BY")
Expected Behavior
From my understanding, the ... argument used in table1.aggr(table2, ...) should retain all attributes from table1.
Thank you!
@kabilar as it turns out this is a MariaDB specific error. MariaDB's implementation of the sql mode ONLY_FULL_GROUP_BY is incorrect when it comes to select on fields that are functionally dependent, it wants those functionally dependent fields to also be in the group by which is unnecessary from a database perspective. MySQL does not do that with the ONLY_FULL_GROUP_BY mode enabled which is correct. The next step would be to confirm if this error persists in the current version of MariaDB.
It seems the MariaDB devs have known about this for a while but has not fixed it yet, https://jira.mariadb.org/browse/MDEV-11588