Column "anon_1.anon_2" is generated in a select statement

[naktinis]

Column "anon_1.anon_2" is generated in the following scenario:

dbsession.query(FirstThing, FirstThing.moved_by.any(User.id == user_id)).options(joinedload_all('some_property'))
query = query.join(SecondThing, SecondThing.first_thing_id == FirstThing.id)
query = query.order_by(OneThing.ordering_field).limit(count)

Also, it is important that both FirstThing and SecondThing polymorphically inherit from Thing.

Effectively, query.all() generates a query like

SELECT ... anon_1.anon_2 AS anon_1_anon_2 ...
FROM
(SELECT first_thing.id AS first_thing.id, EXISTS (SELECT 1 FROM first_thing_moves, users ...) AS anon_2
 FROM thing JOIN first_thing ON ... JOIN (SELECT ... FROM thing JOIN second_thing) AS anon_3 ON ... ORDER BY ... LIMIT ...) AS anon_1 ORDER BY ...

Why would "anon_1.anon_2" column be generated there - it is, I think, not even a valid syntax?

[Michael Bayer]

it's valid, "anon_1" is the label applied to a subquery, you can see where it has "(SELECT .... ) AS anon_1".  "anon_1" becomes what we sometimes call a "derived table" in the query and is then valid like any other alias name.

The join is because when we have a joined inheritance class B inherits from A, then we join to it from C, we are effectively joining:

SELECT * FROM C JOIN (A JOIN B ON A.id=B.id) ON C.x=A.y

That is valid SQL, however, it doesn't work on SQLite, and also doesn't work on MySQL versions before 5.   It also may or may not have issues on some other backends.    So SQLAlchemy turns "A JOIN B" into a subquery:

SELECT * FROM C JOIN (SELECT * FROM A JOIN B ON A.id=B.id) AS anon_1 ON C.x=anon_1.y

as it turns out, this approach generalizes much more nicely than just putting "A JOIN B" in there.  Suppose classes B1 and B2 inherit from A in a concrete fashion, using tables "B1" and "B2" to represent the full row.   Then you wanted to join from C to A.    SQLAlchemy would have you doing a "polymorphic union" which means you select from the UNION of B1 and B2:

SELECT * FROM C JOIN (SELECT * FROM B1 UNION SELECT * FROM B2) AS anon_1 ON C.x=anon_1.y

where "anon_1.y" here would be "y" from B1 unioned to "y" from B2.   

Anyway, SQLAlchemy is very quick to wrap up a series of rows in a subquery, applying an alias to it, since that syntax works the most consistently across not only all backends but across a really wide range of scenarios.

[naktinis]

I should have pointed out that I got a NoSuchColumnError because of "anon_1.anon_2". There is no column "anon_2" in any of the tables. It's just an alias name of a derived table.

Is "table_name_1.table_name_2" supposed to mean anything?

[Michael Bayer]

it appears here the "anon_2" is a label being given to your otherwise unnamed FirstThing.moved_by.any() call, which is a subquery.   

you're not showing me the full query being rendered but I would imagine the important bits are:

SELECT anon_1.anon_2 AS anon_1_anon_2 FROM

(SELECT EXISTS (...) AS anon_2) AS anon_1

which is valid.  The query would fail to execute if it weren't.

NoSuchColumnError here would likely be alleviated if you just said FirstThing.moved_by.any().label("some_label").

I'll look into seeing why an anonymous any() subquery doesn't get targeted by Query correctly here.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/g5juNMWd4moJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Michael Bayer]

Here's a test which generates essentially the same form and runs fine, I'll try to simulate more of exactly what you're doing.   Or if you had a real test case ready to go, would save me a ton of time.

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)

s.add_all([

    A(bs=[B(), B()])

])

s.commit()

print s.query(A.bs.any()).select_from(A).from_self().all()

SQL:

SELECT anon_1.anon_2 AS anon_1_anon_2 

FROM (SELECT EXISTS (SELECT 1 

FROM b 

WHERE a.id = b.a_id) AS anon_2 

FROM a) AS anon_1

2012-02-28 11:41:19,912 INFO sqlalchemy.engine.base.Engine ()

[(True,)]

[Michael Bayer]

OK it's another limit + joinedload -> subquery targeting issue, so this is http://www.sqlalchemy.org/trac/ticket/2419 and workaround for now is use subqueryload_all() instead of joinedload_all() for this specific query.   

[naktinis]

Cool! Thanks so much! I'll give the workaround a try.

Yes, now I see - "anon_2" was a column name, not a table name.

To unsubscribe from this group, send email to sqlalchemy+unsubscribe@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlal...@googlegroups.com.

To unsubscribe from this group, send email to sqlalchemy+unsubscribe@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlal...@googlegroups.com.

To unsubscribe from this group, send email to sqlalchemy+unsubscribe@googlegroups.com.

To unsubscribe from this group, send email to sqlalchemy+unsubscribe@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlal...@googlegroups.com.

To unsubscribe from this group, send email to sqlalchemy+unsubscribe@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlal...@googlegroups.com.

To unsubscribe from this group, send email to sqlalchemy+unsubscribe@googlegroups.com.