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.
On Tuesday, February 28, 2012 6:54:42 PM UTC+2, Michael Bayer wrote: > > 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. > > > > > On Feb 28, 2012, at 11:43 AM, Michael Bayer wrote: > > 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,)] > > > > > > > > On Feb 28, 2012, at 11:37 AM, Michael Bayer wrote: > > > 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. > > > > On Feb 28, 2012, at 11:02 AM, naktinis wrote: > > 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? > > On Tuesday, February 28, 2012 5:53:42 PM UTC+2, Michael Bayer wrote: >> >> >> On Feb 28, 2012, at 9:40 AM, naktinis wrote: >> >> 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? >> >> >> 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. >> >> >> >> >> > -- > 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 [email protected]. > To unsubscribe from this group, send email to > [email protected]. > 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 [email protected]. > To unsubscribe from this group, send email to > [email protected]. > 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 [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > > On Tuesday, February 28, 2012 6:54:42 PM UTC+2, Michael Bayer wrote: > > 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. > > > > > On Feb 28, 2012, at 11:43 AM, Michael Bayer wrote: > > 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,)] > > > > > > > > On Feb 28, 2012, at 11:37 AM, Michael Bayer wrote: > > > 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. > > > > On Feb 28, 2012, at 11:02 AM, naktinis wrote: > > 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? > > On Tuesday, February 28, 2012 5:53:42 PM UTC+2, Michael Bayer wrote: >> >> >> On Feb 28, 2012, at 9:40 AM, naktinis wrote: >> >> 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? >> >> >> 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. >> >> >> >> >> > -- > 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 [email protected]. > To unsubscribe from this group, send email to > [email protected]. > 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 [email protected]. > To unsubscribe from this group, send email to > [email protected]. > 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 [email protected]. > To unsubscribe from this group, send email to > [email protected]. > 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 view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/J8H248llZF4J. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
