[Sqlalchemy-tickets] Issue #4246: compound Query select apply for update to inner query? (zzzeek/sqlalchemy)

[Michael B. <iss...@bi...>]

New issue 4246: compound Query select apply for update to inner query?
https://bitbucket.org/zzzeek/sqlalchemy/issues/4246/compound-query-select-apply-for-update-to

Michael Bayer:

```
#!diff

diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py
index 6d2b144e3..d9a0f9f47 100644
--- a/lib/sqlalchemy/orm/query.py
+++ b/lib/sqlalchemy/orm/query.py
@@ -3500,6 +3500,8 @@ class Query(object):
         if self._correlate:
             inner = inner.correlate(*self._correlate)
 
+        inner._for_update_arg = context._for_update_arg
+
         inner = inner.alias()
 
         equivs = self.__all_equivs()
@@ -3510,7 +3512,7 @@ class Query(object):
             [inner] + context.secondary_columns,
             use_labels=context.labels)
 
-        statement._for_update_arg = context._for_update_arg
+        #statement._for_update_arg = context._for_update_arg
 
         from_clause = inner
         for eager_join in context.eager_joins.values():

```

so that given:


```
#!python

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

Base = declarative_base()


class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, Sequence('aid_seq', optional=True), primary_key=True)
    bs = relationship("B")


class B(Base):
    __tablename__ = 'b'
    id = Column(Integer, Sequence('bid_seq', optional=True), primary_key=True)
    a_id = Column(ForeignKey('a.id'))

e = create_engine("mysql://scott:tiger@localhost/test", echo=True)
#e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
#e = create_engine("oracle://scott:tiger@oracle1120/", echo=True)


Base.metadata.drop_all(e)
Base.metadata.create_all(e)

s = Session(e)
s.add_all([A(bs=[B(), B()]), A(bs=[B()])])

s.commit()

s.query(A).options(joinedload(A.bs)).with_for_update().first()




```

we get:


```
#!python

SELECT anon_1.a_id AS anon_1_a_id, b_1.id AS b_1_id, b_1.a_id AS b_1_a_id 
FROM (SELECT a.id AS a_id 
FROM a 
 LIMIT %s FOR UPDATE) AS anon_1 LEFT OUTER JOIN b AS b_1 ON anon_1.a_id = b_1.a_id

```

per https://bitbucket.org/zzzeek/sqlalchemy/issues/4100/with_for_update-interacts-badly-with-lazy#comment-44989256 the claim is that on MySQL, the lock is not applied if the FOR UPDATE is on the outside.

The above passes on MySQL and Posgresql.  On Oracle, the LIMIT is not rendering for some reason and that may be an additional issue.