[Sqlalchemy-tickets] Issue #3109: PostgreSQL ARRAY_AGG TypeError (zzzeek/sqlalchemy)

[Scott M. <iss...@bi...>]

New issue 3109: PostgreSQL ARRAY_AGG TypeError
https://bitbucket.org/zzzeek/sqlalchemy/issue/3109/postgresql-array_agg-typeerror

Scott Milliken:

I'm getting a TypeError at `util/_collections.py:729` in 0.9.6 (also tested in 0.9.4) when using the postgresql ARRAY_AGG function and a GROUP BY. Here's a repro script:

```
import sqlalchemy
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session

print('version: %s' % sqlalchemy.__version__)

Base = declarative_base()

class Parent(Base):
    __tablename__ = 'parent'

    id = Column(Integer, primary_key=True)
    name = Column(String)


class Child(Base):
    __tablename__ = 'child'

    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey(Parent.id))

engine = create_engine("postgresql://scott:tiger@localhost/test", echo=True)

conn = engine.connect()
session = Session(conn)
Base.metadata.create_all(conn)
session.add(Parent(id=1, name='Test'))
session.flush()
session.add(Child(id=1, parent_id=1))
session.flush()
session.query(Parent, func.ARRAY_AGG(Child.id)).filter(Parent.id == Child.parent_id).group_by(Parent).all()
```

And the output:

```
version: 0.9.6
2014-07-01 23:33:35,088 INFO sqlalchemy.engine.base.Engine select version()
2014-07-01 23:33:35,088 INFO sqlalchemy.engine.base.Engine {}
2014-07-01 23:33:35,090 INFO sqlalchemy.engine.base.Engine select current_schema()
2014-07-01 23:33:35,090 INFO sqlalchemy.engine.base.Engine {}
2014-07-01 23:33:35,092 INFO sqlalchemy.engine.base.Engine {}                                                                                                                                            
2014-07-01 23:33:35,093 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-07-01 23:33:35,093 INFO sqlalchemy.engine.base.Engine {}
2014-07-01 23:33:35,093 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2014-07-01 23:33:35,094 INFO sqlalchemy.engine.base.Engine {}
2014-07-01 23:33:35,096 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and relname=%(name)s
2014-07-01 23:33:35,096 INFO sqlalchemy.engine.base.Engine {'name': u'parent'}
2014-07-01 23:33:35,098 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where n.nspname=current_schema() and relname=%(name)s
2014-07-01 23:33:35,098 INFO sqlalchemy.engine.base.Engine {'name': u'child'}
2014-07-01 23:33:35,100 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2014-07-01 23:33:35,101 INFO sqlalchemy.engine.base.Engine INSERT INTO parent (id, name) VALUES (%(id)s, %(name)s)
2014-07-01 23:33:35,101 INFO sqlalchemy.engine.base.Engine {'name': 'Test', 'id': 1}
2014-07-01 23:33:35,104 INFO sqlalchemy.engine.base.Engine INSERT INTO child (id, parent_id) VALUES (%(id)s, %(parent_id)s)
2014-07-01 23:33:35,104 INFO sqlalchemy.engine.base.Engine {'parent_id': 1, 'id': 1}
2014-07-01 23:33:35,107 INFO sqlalchemy.engine.base.Engine SELECT parent.id AS parent_id, parent.name AS parent_name, ARRAY_AGG(child.id) AS "ARRAY_AGG_1" 
FROM parent, child 
WHERE parent.id = child.parent_id GROUP BY parent.id, parent.name
2014-07-01 23:33:35,108 INFO sqlalchemy.engine.base.Engine {}

Traceback (most recent call last):
  File "./bin/python", line 123, in <module>
    exec(compile(__file__f.read(), __file__, "exec"))
  File "test.py", line 32, in <module>
    session.query(Parent, func.ARRAY_AGG(Child.id)).filter(Parent.id == Child.parent_id).group_by(Parent).all()
  File "eggs/SQLAlchemy-0.9.6-py2.7-linux-x86_64.egg/sqlalchemy/orm/query.py", line 2293, in all
    return list(self)
  File "eggs/SQLAlchemy-0.9.6-py2.7-linux-x86_64.egg/sqlalchemy/orm/loading.py", line 78, in instances
    rows = util.unique_list(rows, filter_fn)
  File "eggs/SQLAlchemy-0.9.6-py2.7-linux-x86_64.egg/sqlalchemy/util/_collections.py", line 729, in unique_list
    if hashfunc(x) not in seen
TypeError: unhashable type: 'list'
```