Closed
Description
Migrated issue, originally created by Сергей Трошин
When i use LIMIT and FOR UPDATE OF, I got the invalid sql query#
1. If I use only with_for_update(of=column), then i get valid sql.
For example, query:
rs.query(table)\
.select_from(schema.SyncFlags)
.outerjoin(table, schema.SyncFlags.row_id ==table.__table__.primary_key.columns.values()[0])
.filter(
schema.SyncFlags.cache_group_id == cg_id,
schema.SyncFlags.table_name == table.__tablename__
)\
.with_for_update(nowait=True, of=schema.SyncFlags.sync, skip_locked=True)
valid SQL:
SELECT user_account.id_user_account AS user_account_id_user_acc_1, user_account.id_type AS user_account_id_type, user_account.id_user AS user_account_id_user, user_account.id_predpr AS user_account_id_predpr
FROM cg_sync_flags LEFT OUTER JOIN user_account ON cg_sync_flags.row_id = user_account.id_user_account
WHERE cg_sync_flags.cache_group_id = :cache_group_id_1 AND cg_sync_flags.table_name = :table_name_1 FOR UPDATE OF cg_sync_flags.sync NOWAIT SKIP LOCKED
2. When I add LIMIT clause, then query builder add parent query and my FOR UPDATE OF column reference become invalid. It is valid in subquery, but invalid in parent query.#
Example:
rs.query(table)\
.select_from(schema.SyncFlags)\
.outerjoin(table, schema.SyncFlags.row_id==table.__table__.primary_key.columns.values()[0])\
.filter(
schema.SyncFlags.cache_group_id == cg_id,
schema.SyncFlags.table_name == table.__tablename__
)\
.with_for_update(nowait=True, of=schema.SyncFlags.sync, skip_locked=True)\
.limit(1000)
Invalid SQL:
SELECT user_account_id_user_acc_1, user_account_id_type, user_account_id_user, user_account_id_predpr
FROM (SELECT user_account.id_user_account AS user_account_id_user_acc_1, user_account.id_type AS user_account_id_type, user_account.id_user AS user_account_id_user, user_account.id_predpr AS user_account_id_predpr
FROM cg_sync_flags LEFT OUTER JOIN user_account ON cg_sync_flags.row_id = user_account.id_user_account
WHERE cg_sync_flags.cache_group_id = :cache_group_id_1 AND cg_sync_flags.table_name = :table_name_1)
WHERE ROWNUM <= :param_1 FOR UPDATE OF cg_sync_flags.sync NOWAIT SKIP LOCKED
3. Work around(not portable): use filter criteria:#
rs.query(table)
.select_from(schema.SyncFlags)
.outerjoin(table, schema.SyncFlags.row_id == table.__table__.primary_key.columns.values()[0])
.filter(
schema.SyncFlags.cache_group_id == cg_id,
schema.SyncFlags.table_name == table.__tablename__,
text("rownum <= :rownum").bindparams(bindparam('rownum', value=5, type_=Integer))
)
.with_for_update(nowait=True, of=schema.SyncFlags.sync, skip_locked=True)
Valid SQL:
SELECT user_account.id_user_account AS user_account_id_user_acc_1, user_account.id_type AS user_account_id_type, user_account.id_user AS user_account_id_user, user_account.id_predpr AS user_account_id_predpr
FROM cg_sync_flags LEFT OUTER JOIN user_account ON cg_sync_flags.row_id = user_account.id_user_account
WHERE cg_sync_flags.cache_group_id = :cache_group_id_1 AND cg_sync_flags.table_name = :table_name_1 AND rownum <= :rownum FOR UPDATE OF cg_sync_flags.sync NOWAIT SKIP LOCKED
Metadata
Metadata
Assignees
Labels
Type
Projects
Relationships
Development
No branches or pull requests
Participants
Activity
sqlalchemy-bot commentedon Jul 5, 2016
Michael Bayer (@zzzeek) wrote:
can you clarify this please? As I was not given a model here to work with, can you please test if the "FOR UPDATE" clause can now be specified in subqueries ? Oracle has not allowed this in the past. thanks.
sqlalchemy-bot commentedon Jul 5, 2016
Michael Bayer (@zzzeek) wrote:
nope
for_update_clause
The FOR UPDATE clause lets you lock the selected rows so that other users cannot lock or update the rows until you end your transaction. You can specify this clause only in a top-level SELECT statement, not in subqueries.
sqlalchemy-bot commentedon Jul 5, 2016
Changes by Michael Bayer (@zzzeek):
sqlalchemy-bot commentedon Jul 5, 2016
Changes by Michael Bayer (@zzzeek):
sqlalchemy-bot commentedon Jul 5, 2016
Michael Bayer (@zzzeek) wrote:
https://gerrit.sqlalchemy.org/#/q/Ia71b5fc4df6d326e73863f8ae9f96e8f1a5acfc1
sqlalchemy-bot commentedon Jul 6, 2016
Сергей Трошин wrote:
On orale-10.2, for update do not permitted in subquery.
Second work around:
select needed_columns..., column_for_update_inner_alias
from (
select needed_columns..., column_for_update as column_for_update_inner_alias
from ....
)
where rownum <= 1000
for update of column_for_update_inner_alias
On Sqlalchemy:
.with_for_update(of=column_for_update_inner_alias???)
sqlalchemy-bot commentedon Jul 6, 2016
Сергей Трошин wrote:
Thanks
sqlalchemy-bot commentedon Jul 6, 2016
Michael Bayer (@zzzeek) wrote:
Adapt "FOR UPDATE OF" with Oracle limit/offset
This modifies the Oracle ROWNUM limit/offset approach
to accommodate for the "OF" clause in a "FOR UPDATE"
phrase. The column expressions must be added to the selected
subquery if necessary and adapted on the outside.
Change-Id: Ia71b5fc4df6d326e73863f8ae9f96e8f1a5acfc1
Fixes: #3741
→ f2ee514
sqlalchemy-bot commentedon Jul 6, 2016
Changes by Michael Bayer (@zzzeek):
sqlalchemy-bot commentedon Jul 6, 2016
Michael Bayer (@zzzeek) wrote:
Adapt "FOR UPDATE OF" with Oracle limit/offset
This modifies the Oracle ROWNUM limit/offset approach
to accommodate for the "OF" clause in a "FOR UPDATE"
phrase. The column expressions must be added to the selected
subquery if necessary and adapted on the outside.
Change-Id: Ia71b5fc4df6d326e73863f8ae9f96e8f1a5acfc1
Fixes: #3741
(cherry picked from commit d23797943786914cced58ed71ffb7b749e64c449)
→ d99ec75
sqlalchemy-bot commentedon Jul 6, 2016
Michael Bayer (@zzzeek) wrote:
Adapt "FOR UPDATE OF" with Oracle limit/offset
This modifies the Oracle ROWNUM limit/offset approach
to accommodate for the "OF" clause in a "FOR UPDATE"
phrase. The column expressions must be added to the selected
subquery if necessary and adapted on the outside.
Change-Id: Ia71b5fc4df6d326e73863f8ae9f96e8f1a5acfc1
Fixes: #3741
→ d23797943786
sqlalchemy-bot commentedon Jul 6, 2016
Michael Bayer (@zzzeek) wrote:
Adapt "FOR UPDATE OF" with Oracle limit/offset
This modifies the Oracle ROWNUM limit/offset approach
to accommodate for the "OF" clause in a "FOR UPDATE"
phrase. The column expressions must be added to the selected
subquery if necessary and adapted on the outside.
Change-Id: Ia71b5fc4df6d326e73863f8ae9f96e8f1a5acfc1
Fixes: #3741
(cherry picked from commit d23797943786914cced58ed71ffb7b749e64c449)
→ 271d4e9e712b
sqlalchemy-bot commentedon Jul 7, 2016
Сергей Трошин wrote:
Great! Thank you, Michael!
3 remaining items