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 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.
