Incorrect transpilation of complex aggregation

[dimitri-yatsenko]

The following query

experiment.Session() & (
        (experiment.Scan() * stimulus.Clip()).aggr(stimulus.Trial(), n='count(*)') 
        & 'n>=2')

throws the error

---------------------------------------------------------------------------
InternalError                             Traceback (most recent call last)
/usr/local/lib/python3.6/dist-packages/IPython/core/formatters.py in __call__(self, obj)
    691                 type_pprinters=self.type_printers,
    692                 deferred_pprinters=self.deferred_printers)
--> 693             printer.pretty(obj)
    694             printer.flush()
    695             return stream.getvalue()

/usr/local/lib/python3.6/dist-packages/IPython/lib/pretty.py in pretty(self, obj)
    378                             if callable(meth):
    379                                 return meth(obj, self, cycle)
--> 380             return _default_pprint(obj, self, cycle)
    381         finally:
    382             self.end_group()

/usr/local/lib/python3.6/dist-packages/IPython/lib/pretty.py in _default_pprint(obj, p, cycle)
    493     if _safe_getattr(klass, '__repr__', None) is not object.__repr__:
    494         # A user-provided repr. Find newlines and replace them with p.break_()
--> 495         _repr_pprint(obj, p, cycle)
    496         return
    497     p.begin_group(1, '<')

/usr/local/lib/python3.6/dist-packages/IPython/lib/pretty.py in _repr_pprint(obj, p, cycle)
    691     """A pprint that just redirects to the normal repr function."""
    692     # Find newlines and replace them with p.break_()
--> 693     output = repr(obj)
    694     for idx,output_line in enumerate(output.splitlines()):
    695         if idx:

~/dev/datajoint-python/datajoint/relational_operand.py in __repr__(self)
    394 
    395     def __repr__(self):
--> 396         return super().__repr__() if config['loglevel'].lower() == 'debug' else self.preview()
    397 
    398     def preview(self, limit=None, width=None):

~/dev/datajoint-python/datajoint/relational_operand.py in preview(self, limit, width)
    406         if width is None:
    407             width = config['display.width']
--> 408         tuples = rel.fetch(limit=limit+1)
    409         has_more = len(tuples) > limit
    410         tuples = tuples[:limit]

~/dev/datajoint-python/datajoint/fetch.py in __call__(self, *attrs, **kwargs)
    186 
    187         if len(attrs) == 0: # fetch all attributes
--> 188             cur = self._relation.cursor(**sql_behavior)
    189             heading = self._relation.heading
    190             if sql_behavior['as_dict']:

~/dev/datajoint-python/datajoint/relational_operand.py in cursor(self, offset, limit, order_by, as_dict)
    548             sql += ' LIMIT %d' % limit + (' OFFSET %d' % offset if offset else "")
    549         logger.debug(sql)
--> 550         return self.connection.query(sql, as_dict=as_dict)
    551 
    552 

~/dev/datajoint-python/datajoint/connection.py in query(self, query, args, as_dict, suppress_warnings)
    124                     # suppress all warnings arising from underlying SQL library
    125                     warnings.simplefilter("ignore")
--> 126                 cur.execute(query, args)
    127 
    128         except err.OperationalError as e:

/usr/local/lib/python3.6/dist-packages/pymysql/cursors.py in execute(self, query, args)
    164         query = self.mogrify(query, args)
    165 
--> 166         result = self._query(query)
    167         self._executed = query
    168         return result

/usr/local/lib/python3.6/dist-packages/pymysql/cursors.py in _query(self, q)
    320         conn = self._get_db()
    321         self._last_executed = q
--> 322         conn.query(q)
    323         self._do_get_result()
    324         return self.rowcount

/usr/local/lib/python3.6/dist-packages/pymysql/connections.py in query(self, sql, unbuffered)
    854                 sql = sql.encode(self.encoding, 'surrogateescape')
    855         self._execute_command(COMMAND.COM_QUERY, sql)
--> 856         self._affected_rows = self._read_query_result(unbuffered=unbuffered)
    857         return self._affected_rows
    858 

/usr/local/lib/python3.6/dist-packages/pymysql/connections.py in _read_query_result(self, unbuffered)
   1055         else:
   1056             result = MySQLResult(self)
-> 1057             result.read()
   1058         self._result = result
   1059         if result.server_status is not None:

/usr/local/lib/python3.6/dist-packages/pymysql/connections.py in read(self)
   1338     def read(self):
   1339         try:
-> 1340             first_packet = self.connection._read_packet()
   1341 
   1342             if first_packet.is_ok_packet():

/usr/local/lib/python3.6/dist-packages/pymysql/connections.py in _read_packet(self, packet_type)
   1012 
   1013         packet = packet_type(buff, self.encoding)
-> 1014         packet.check_error()
   1015         return packet
   1016 

/usr/local/lib/python3.6/dist-packages/pymysql/connections.py in check_error(self)
    391             errno = self.read_uint16()
    392             if DEBUG: print("errno =", errno)
--> 393             err.raise_mysql_exception(self._data)
    394 
    395     def dump(self):

/usr/local/lib/python3.6/dist-packages/pymysql/err.py in raise_mysql_exception(data)
    105         errval = data[3:].decode('utf-8', 'replace')
    106     errorclass = error_map.get(errno, InternalError)
--> 107     raise errorclass(errno, errval)

InternalError: (1054, "Unknown column 'n' in 'having clause'")

However, the following modification works correctly:

experiment.Session() & (
        (experiment.Scan() * stimulus.Clip()).aggr(stimulus.Trial(), n='count(*)') 
        & 'n>=2').proj()

[dimitri-yatsenko]

Here is another example

oracle_conditions = stimulus.Condition.aggr(stimulus.Trial & key, n='count(*)') & 'n=10'
stimulus.Trial & oracle_condition

---------------------------------------------------------------------------
InternalError                             Traceback (most recent call last)
/usr/local/lib/python3.6/dist-packages/IPython/core/formatters.py in __call__(self, obj)
    691                 type_pprinters=self.type_printers,
    692                 deferred_pprinters=self.deferred_printers)
--> 693             printer.pretty(obj)
    694             printer.flush()
    695             return stream.getvalue()

/usr/local/lib/python3.6/dist-packages/IPython/lib/pretty.py in pretty(self, obj)
    378                             if callable(meth):
    379                                 return meth(obj, self, cycle)
--> 380             return _default_pprint(obj, self, cycle)
    381         finally:
    382             self.end_group()

/usr/local/lib/python3.6/dist-packages/IPython/lib/pretty.py in _default_pprint(obj, p, cycle)
    493     if _safe_getattr(klass, '__repr__', None) is not object.__repr__:
    494         # A user-provided repr. Find newlines and replace them with p.break_()
--> 495         _repr_pprint(obj, p, cycle)
    496         return
    497     p.begin_group(1, '<')

/usr/local/lib/python3.6/dist-packages/IPython/lib/pretty.py in _repr_pprint(obj, p, cycle)
    691     """A pprint that just redirects to the normal repr function."""
    692     # Find newlines and replace them with p.break_()
--> 693     output = repr(obj)
    694     for idx,output_line in enumerate(output.splitlines()):
    695         if idx:

~/dev/datajoint-python/datajoint/relational_operand.py in __repr__(self)
    345 
    346     def __repr__(self):
--> 347         return super().__repr__() if config['loglevel'].lower() == 'debug' else self.preview()
    348 
    349     def preview(self, limit=None, width=None):

~/dev/datajoint-python/datajoint/relational_operand.py in preview(self, limit, width)
    357         if width is None:
    358             width = config['display.width']
--> 359         tuples = rel.fetch(limit=limit+1)
    360         has_more = len(tuples) > limit
    361         tuples = tuples[:limit]

~/dev/datajoint-python/datajoint/fetch.py in __call__(self, offset, limit, order_by, as_dict, squeeze, *attrs)
     57         if not attrs:
     58             # fetch all attributes
---> 59             cur = self._relation.cursor(as_dict=as_dict, limit=limit, offset=offset, order_by=order_by)
     60             heading = self._relation.heading
     61             if as_dict:

~/dev/datajoint-python/datajoint/relational_operand.py in cursor(self, offset, limit, order_by, as_dict)
    520             sql += ' LIMIT %d' % limit + (' OFFSET %d' % offset if offset else "")
    521         logger.debug(sql)
--> 522         return self.connection.query(sql, as_dict=as_dict)
    523 
    524 

~/dev/datajoint-python/datajoint/connection.py in query(self, query, args, as_dict, suppress_warnings)
    129                     # suppress all warnings arising from underlying SQL library
    130                     warnings.simplefilter("ignore")
--> 131                 cur.execute(query, args)
    132 
    133         except err.OperationalError as e:

/usr/local/lib/python3.6/dist-packages/pymysql/cursors.py in execute(self, query, args)
    164         query = self.mogrify(query, args)
    165 
--> 166         result = self._query(query)
    167         self._executed = query
    168         return result

/usr/local/lib/python3.6/dist-packages/pymysql/cursors.py in _query(self, q)
    320         conn = self._get_db()
    321         self._last_executed = q
--> 322         conn.query(q)
    323         self._do_get_result()
    324         return self.rowcount

/usr/local/lib/python3.6/dist-packages/pymysql/connections.py in query(self, sql, unbuffered)
    854                 sql = sql.encode(self.encoding, 'surrogateescape')
    855         self._execute_command(COMMAND.COM_QUERY, sql)
--> 856         self._affected_rows = self._read_query_result(unbuffered=unbuffered)
    857         return self._affected_rows
    858 

/usr/local/lib/python3.6/dist-packages/pymysql/connections.py in _read_query_result(self, unbuffered)
   1055         else:
   1056             result = MySQLResult(self)
-> 1057             result.read()
   1058         self._result = result
   1059         if result.server_status is not None:

/usr/local/lib/python3.6/dist-packages/pymysql/connections.py in read(self)
   1338     def read(self):
   1339         try:
-> 1340             first_packet = self.connection._read_packet()
   1341 
   1342             if first_packet.is_ok_packet():

/usr/local/lib/python3.6/dist-packages/pymysql/connections.py in _read_packet(self, packet_type)
   1012 
   1013         packet = packet_type(buff, self.encoding)
-> 1014         packet.check_error()
   1015         return packet
   1016 

/usr/local/lib/python3.6/dist-packages/pymysql/connections.py in check_error(self)
    391             errno = self.read_uint16()
    392             if DEBUG: print("errno =", errno)
--> 393             err.raise_mysql_exception(self._data)
    394 
    395     def dump(self):

/usr/local/lib/python3.6/dist-packages/pymysql/err.py in raise_mysql_exception(data)
    105         errval = data[3:].decode('utf-8', 'replace')
    106     errorclass = error_map.get(errno, InternalError)
--> 107     raise errorclass(errno, errval)

InternalError: (1054, "Unknown column 'n' in 'having clause'")

---------------------------------------------------------------------------
InternalError                             Traceback (most recent call last)
/usr/local/lib/python3.6/dist-packages/IPython/core/formatters.py in __call__(self, obj)
    334             method = get_real_method(obj, self.print_method)
    335             if method is not None:
--> 336                 return method()
    337             return None
    338         else:

~/dev/datajoint-python/datajoint/relational_operand.py in _repr_html_(self)
    376         rel = self.proj(*heading.non_blobs)
    377         info = heading.table_info
--> 378         tuples = rel.fetch(limit=config['display.limit']+1)
    379         has_more = len(tuples) > config['display.limit']
    380         tuples = tuples[0:config['display.limit']]

~/dev/datajoint-python/datajoint/fetch.py in __call__(self, offset, limit, order_by, as_dict, squeeze, *attrs)
     57         if not attrs:
     58             # fetch all attributes
---> 59             cur = self._relation.cursor(as_dict=as_dict, limit=limit, offset=offset, order_by=order_by)
     60             heading = self._relation.heading
     61             if as_dict:

~/dev/datajoint-python/datajoint/relational_operand.py in cursor(self, offset, limit, order_by, as_dict)
    520             sql += ' LIMIT %d' % limit + (' OFFSET %d' % offset if offset else "")
    521         logger.debug(sql)
--> 522         return self.connection.query(sql, as_dict=as_dict)
    523 
    524 

~/dev/datajoint-python/datajoint/connection.py in query(self, query, args, as_dict, suppress_warnings)
    129                     # suppress all warnings arising from underlying SQL library
    130                     warnings.simplefilter("ignore")
--> 131                 cur.execute(query, args)
    132 
    133         except err.OperationalError as e:

/usr/local/lib/python3.6/dist-packages/pymysql/cursors.py in execute(self, query, args)
    164         query = self.mogrify(query, args)
    165 
--> 166         result = self._query(query)
    167         self._executed = query
    168         return result

/usr/local/lib/python3.6/dist-packages/pymysql/cursors.py in _query(self, q)
    320         conn = self._get_db()
    321         self._last_executed = q
--> 322         conn.query(q)
    323         self._do_get_result()
    324         return self.rowcount

/usr/local/lib/python3.6/dist-packages/pymysql/connections.py in query(self, sql, unbuffered)
    854                 sql = sql.encode(self.encoding, 'surrogateescape')
    855         self._execute_command(COMMAND.COM_QUERY, sql)
--> 856         self._affected_rows = self._read_query_result(unbuffered=unbuffered)
    857         return self._affected_rows
    858 

/usr/local/lib/python3.6/dist-packages/pymysql/connections.py in _read_query_result(self, unbuffered)
   1055         else:
   1056             result = MySQLResult(self)
-> 1057             result.read()
   1058         self._result = result
   1059         if result.server_status is not None:

/usr/local/lib/python3.6/dist-packages/pymysql/connections.py in read(self)
   1338     def read(self):
   1339         try:
-> 1340             first_packet = self.connection._read_packet()
   1341 
   1342             if first_packet.is_ok_packet():

/usr/local/lib/python3.6/dist-packages/pymysql/connections.py in _read_packet(self, packet_type)
   1012 
   1013         packet = packet_type(buff, self.encoding)
-> 1014         packet.check_error()
   1015         return packet
   1016 

/usr/local/lib/python3.6/dist-packages/pymysql/connections.py in check_error(self)
    391             errno = self.read_uint16()
    392             if DEBUG: print("errno =", errno)
--> 393             err.raise_mysql_exception(self._data)
    394 
    395     def dump(self):

/usr/local/lib/python3.6/dist-packages/pymysql/err.py in raise_mysql_exception(data)
    105         errval = data[3:].decode('utf-8', 'replace')
    106     errorclass = error_map.get(errno, InternalError)
--> 107     raise errorclass(errno, errval)

InternalError: (1054, "Unknown column 'n' in 'having clause'")