decide what func.XYZ().alias() should do

[Adrian]

The following query is causing an error inside the SQLAlchemy code since 0.9.4:

subquery = (select(['latitude'])
            .select_from(func.unnest(func.array_agg(Room.latitude))
                         .alias('latitude')).limit(1).as_scalar())
Room.query.with_entities(Room.building, subquery).group_by(Room.building).all()

Partial traceback:

  File ".../sqlalchemy/sql/selectable.py", line 429, in columns
    self._populate_column_collection()
  File ".../sqlalchemy/sql/selectable.py", line 992, in _populate_column_collection
    for col in self.element.columns._all_columns:
AttributeError: 'list' object has no attribute '_all_columns'

Related SO question with more details: http://stackoverflow.com/questions/24930155/strange-error-after-sqlalchemy-update-list-object-has-no-attribute-all-colu

[Mike Bayer]

per reporting bugs please post a fully reproducing test case , cannot reproduce, thank you

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class A(Base):
    __tablename__ = 'a'

    id = Column(Integer, primary_key=True)
    bs = relationship("B")


class B(Base):
    __tablename__ = 'b'

    id = Column(Integer, primary_key=True)
    a_id = Column(Integer, ForeignKey('a.id'))

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

s = Session(e)
subquery = (select(['id'])
            .select_from(A)).limit(1).as_scalar()
q = s.query(B).with_entities(B.a_id, subquery).group_by(B.a_id)
print q
print q.all()

output:

SELECT b.a_id AS b_a_id, (SELECT id 
FROM a
 LIMIT :param_1) AS anon_1 
FROM b GROUP BY b.a_id
2014-07-24 09:35:20,524 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2014-07-24 09:35:20,526 INFO sqlalchemy.engine.base.Engine SELECT b.a_id AS b_a_id, (SELECT id 
FROM a
 LIMIT ? OFFSET ?) AS anon_1 
FROM b GROUP BY b.a_id
2014-07-24 09:35:20,526 INFO sqlalchemy.engine.base.Engine (1, 0)
[]

[Mike Bayer]

reopen with complete test case illustrating the error, feel free to modify the one I posted, thanks!

[Adrian]

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class A(Base):
    __tablename__ = 'a'

    id = Column(Integer, primary_key=True)
    latitude = Column(String)
    building = Column(String)


e = create_engine('sqlite://', echo=True)
Base.metadata.create_all(e)

s = Session(e)
subquery = (select(['latitude']).select_from(func.unnest(func.array_agg(A.latitude)).alias('latitude'))
            .limit(1)
            .as_scalar())
q = s.query(A).with_entities(A.building, subquery).group_by(A.building)
print q
print q.all()

Output:

Traceback (most recent call last):
  File "satest.py", line 23, in <module>
    q = s.query(A).with_entities(A.building, subquery).group_by(A.building)
  File "<string>", line 2, in with_entities
  File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/orm/base.py", line 191, in generate
    fn(self, *args[1:], **kw)
  File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 1028, in with_entities
    self._set_entities(entities)
  File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 116, in _set_entities
    entity_wrapper(self, ent)
  File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 3426, in __init__
    for elem in visitors.iterate(column, {})
  File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/util/_collections.py", line 350, in __init__
    self.update(d)
  File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/util/_collections.py", line 390, in update
    for e in iterable:
  File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 3425, in <genexpr>
    elem._annotations['parententity']
  File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 219, in iterate
    for c in t.get_children(**opts):
  File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/selectable.py", line 1020, in get_children
    for c in self.c:
  File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 725, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/selectable.py", line 429, in columns
    self._populate_column_collection()
  File "/home/adrian/dev/indico/env/lib/python2.7/site-packages/sqlalchemy/sql/selectable.py", line 992, in _populate_column_collection
    for col in self.element.columns._all_columns:
AttributeError: 'list' object has no attribute '_all_columns'

[Adrian]

see updated testcase

[Mike Bayer]

OK the original query is using some undefined API, the expected form in this particular case is:

subquery = (select(['latitude']).select_from(
                    func.foo(A.latitude)
                )
            .limit(1)
            .as_scalar())

func.foo().alias() should in fact be equivalent to func.foo().select().alias(), however in this case that will push out a second level of nesting here which you don't want. So to make that correction to the API probably needs to be a 1.0 thing, unless I can determine that func.foo().alias() is totally unusable right now.

[Adrian]

How would I change the query to get the same SQL like I got before?

[Mike Bayer]

I'm restoring the old behavior, but for now just use select(['*']). the column is unnamed. PG's behavior of assigning the column name based on the alias in the FROM is a little bit magic (e.g., if the function returned multiple columns, then it ignores that name and uses the ones the function reports?)

[Mike Bayer]

• Added a supported :meth:.FunctionElement.alias method to functions, e.g. the func construct. Previously, behavior for this method was undefined. The current behavior mimics that of pre-0.9.4, which is that the function is turned into a single-column FROM clause with the given alias name, where the column itself is anonymously named. fixes #3137

→ <<cset 0df977ccad63>>

[Mike Bayer]

• Added a supported :meth:.FunctionElement.alias method to functions, e.g. the func construct. Previously, behavior for this method was undefined. The current behavior mimics that of pre-0.9.4, which is that the function is turned into a single-column FROM clause with the given alias name, where the column itself is anonymously named. fixes #3137

→ <<cset 9a7c411def96>>