query limit and with_for_update of clause(oracle) v1.1.02b #3741
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