[Sqlalchemy-tickets] Issue #4063: result from operation on `array_agg() @> []` assumed to be array (zzzeek/sqlalchemy)

[Thijs D. <iss...@bi...>]

New issue 4063: result from operation on `array_agg() @> []` assumed to be array
https://bitbucket.org/zzzeek/sqlalchemy/issues/4063/result-from-operation-on-array_agg-assumed

Thijs Damsma:

Since sqlalchemy 1.2 the following code will throw a `TypeError: 'bool' object is not iterable`

I believe this is because the result is assumed to be an array. 

This is an example, where I use `array_agg` and `@>` as a poor-mans pivot table:

    from sqlalchemy import Column, Table, ForeignKey, Integer, create_engine, func
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy.orm import relationship
    from sqlalchemy.orm.session import sessionmaker

    engine = create_engine('postgresql://postgres:abc@localhost:5432/test')
    Session = sessionmaker(engine)

    Base = declarative_base()


    association_table = Table(
        'association', Base.metadata,
        Column('left_id', Integer, ForeignKey('left.id')),
        Column('right_id', Integer, ForeignKey('right.id'))
    )


    class Parent(Base):
        __tablename__ = 'left'
        id = Column(Integer, primary_key=True)
        children = relationship(
            "Child",
            secondary=association_table,
            back_populates="parents")


    class Child(Base):
        __tablename__ = 'right'
        id = Column(Integer, primary_key=True)
        parents = relationship(
            "Parent",
            secondary=association_table,
            back_populates="children")

    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)

    session = Session()

    children = [Child(), Child(), Child()]
    p1 = Parent()
    p1.children = [children[0], children[2]]

    p2 = Parent()
    p2.children = [children[1], children[2]]

    session.add_all([p1, p2])

    session.commit()

    q = session.query(
        Parent.id,
        func.array_agg(Child.id).op('@>')([children[0].id]).label('has_child_0'),
        func.array_agg(Child.id).op('@>')([children[1].id]).label('has_child_1'),
        func.array_agg(Child.id).op('@>')([children[2].id]).label('has_child_2'),
    ).\
        outerjoin(association_table, Child).\
        group_by(Parent.id)

    print(q.all())

Expected result is 

    [(1, True, False, True), (2, False, True, True)]