<p dir="auto"><strong>Migrated issue, originally created by Anonymous</strong></p>
<p dir="auto">SQLAlchemy 0.3.0</p>
<p dir="auto">Oracle 10g</p>
<p dir="auto">cx_Oracle 4.2</p>
<p dir="auto">Red Hat Enterprise Linux WS release 3 (Taroon Update 7)</p>
<p dir="auto">If you have autoloaded tables Spam and Egg related by Spam has-many Egg like so:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="engine = create_engine(...)
meta = BoundMetaData(engine)
tspam = Table(&quot;spam&quot;, meta, autoload = True)
tegg  = Table(&quot;egg&quot;, meta, autoload = True)
class Egg(object):
    pass
mapper(Egg, tegg)
class Spam(object):
    pass
mapper(Spam, tspam, properties=dict(eggs=relation(Egg,lazy=False)))"><pre class="notranslate"><code class="notranslate">engine = create_engine(...)
meta = BoundMetaData(engine)
tspam = Table("spam", meta, autoload = True)
tegg  = Table("egg", meta, autoload = True)
class Egg(object):
    pass
mapper(Egg, tegg)
class Spam(object):
    pass
mapper(Spam, tspam, properties=dict(eggs=relation(Egg,lazy=False)))
</code></pre></div>
<p dir="auto">then the unconstrained select</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="session.query(Spam).select()"><pre class="notranslate"><code class="notranslate">session.query(Spam).select()
</code></pre></div>
<p dir="auto">works properly but</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="session.query(Spam).select_by(col=value)"><pre class="notranslate"><code class="notranslate">session.query(Spam).select_by(col=value)
</code></pre></div>
<p dir="auto">fails, where "col" is the name of a column present in Egg but not in Spam. The second query returns Egg objects with all values of "col". The only difference in result from the first query is that Spam objects lacking any associated Eggs are omitted.</p>
<p dir="auto">The unconstrained query produces SQL like this:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="SELECT
    egg_74ee.spam_id    AS egg_74ee_spam_id,
    egg_74ee.version    AS egg_74ee_version,
    egg_74ee.id         AS egg_74ee_id,
    egg_74ee.name       AS egg_74ee_name,
    spam.id             AS spam_id,
    spam.name           AS spam_name 
FROM
    spam
    LEFT OUTER JOIN egg egg_74ee
        ON spam.id = egg_74ee.spam_id
ORDER BY
    spam.rowid,
    egg_74ee.rowid"><pre class="notranslate"><code class="notranslate">SELECT
    egg_74ee.spam_id    AS egg_74ee_spam_id,
    egg_74ee.version    AS egg_74ee_version,
    egg_74ee.id         AS egg_74ee_id,
    egg_74ee.name       AS egg_74ee_name,
    spam.id             AS spam_id,
    spam.name           AS spam_name 
FROM
    spam
    LEFT OUTER JOIN egg egg_74ee
        ON spam.id = egg_74ee.spam_id
ORDER BY
    spam.rowid,
    egg_74ee.rowid
</code></pre></div>
<p dir="auto">The second query should have been just like the first with the addition of a WHERE clause. Instead a useless new join is added with a constraint on ''it'' instead of on the old join:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="SELECT
    egg_3216.spam_id     AS egg_3216_spam_id,
    egg_3216.version     AS egg_3216_version,
    egg_3216.id          AS egg_3216_id,
    egg_3216.name        AS egg_3216_name,
    spam.id              AS spam_id,
    spam.name            AS spam_name 
FROM 
    egg,
    spam
    LEFT OUTER JOIN egg   egg_3216
        ON spam.id = egg_3216.spam_id 
WHERE
        egg.col     = value
    AND spam.id     = egg.spam_id
ORDER BY
    spam.rowid,
    egg_3216.rowid"><pre class="notranslate"><code class="notranslate">SELECT
    egg_3216.spam_id     AS egg_3216_spam_id,
    egg_3216.version     AS egg_3216_version,
    egg_3216.id          AS egg_3216_id,
    egg_3216.name        AS egg_3216_name,
    spam.id              AS spam_id,
    spam.name            AS spam_name 
FROM 
    egg,
    spam
    LEFT OUTER JOIN egg   egg_3216
        ON spam.id = egg_3216.spam_id 
WHERE
        egg.col     = value
    AND spam.id     = egg.spam_id
ORDER BY
    spam.rowid,
    egg_3216.rowid
</code></pre></div>
<p dir="auto">I see a similar problem if I omit the lazy=False from the call to relation().</p>
<p dir="auto">By the way, it isn't clear to me whether the select_by() query should return Spam objects with empty Egg lists or omit them. A simple equality constraint would omit them since the comparision with NULL would fail; you'd have to use (table.col = value OR table.col is null) to get them.</p>
<hr>
<p dir="auto">Attachments: <a href="../wiki/imported_issue_attachments/367/bug.2.py">bug.2.py</a> | <a href="../wiki/imported_issue_attachments/367/bug.py">bug.py</a> | <a href="../wiki/imported_issue_attachments/367/bugout.txt">bugout.txt</a></p>
<p dir="auto">Hi,</p>
<p dir="auto">I am getting a StaleDataError with <code class="notranslate">SQLAlchemy==1.3.6</code> whenever sqlalchemy is trying to use <code class="notranslate">session.bulk_update_mappings()</code> and its always 1 less than the number of rows to be updated. I am running the code as part of an aws lambda function.</p>
<p dir="auto">This happens intermittently without any definite pattern. Following is the stack trace:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="UPDATE statement on table 'data_table' expected to update 33 row(s); 32 were matched.: StaleDataError
Traceback (most recent call last):
File &quot;/var/task/saving/rds/serverless.py&quot;, line 185, in handler
output = event_processor(event)
File &quot;/var/task/saving/rds/serverless.py&quot;, line 178, in event_processor
save_to_database(data)
File &quot;/var/task/saving/rds/serverless.py&quot;, line 133, in save_to_database
session.bulk_update_mappings(Data, update_data)
File &quot;/var/task/sqlalchemy/orm/session.py&quot;, line 2849, in bulk_update_mappings
mapper, mappings, True, False, False, False, False
File &quot;/var/task/sqlalchemy/orm/session.py&quot;, line 2888, in _bulk_save_mappings
transaction.rollback(_capture_exception=True)
File &quot;/var/task/sqlalchemy/util/langhelpers.py&quot;, line 68, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File &quot;/var/task/sqlalchemy/util/compat.py&quot;, line 153, in reraise
raise value
File &quot;/var/task/sqlalchemy/orm/session.py&quot;, line 2873, in _bulk_save_mappings
update_changed_only,
File &quot;/var/task/sqlalchemy/orm/persistence.py&quot;, line 180, in _bulk_update
bookkeeping=False,
File &quot;/var/task/sqlalchemy/orm/persistence.py&quot;, line 1029, in _emit_update_statements
% (table.description, len(records), rows)
sqlalchemy.orm.exc.StaleDataError: UPDATE statement on table 'data_table' expected to update 33 row(s); 32 were matched."><pre class="notranslate"><code class="notranslate">UPDATE statement on table 'data_table' expected to update 33 row(s); 32 were matched.: StaleDataError
Traceback (most recent call last):
File "/var/task/saving/rds/serverless.py", line 185, in handler
output = event_processor(event)
File "/var/task/saving/rds/serverless.py", line 178, in event_processor
save_to_database(data)
File "/var/task/saving/rds/serverless.py", line 133, in save_to_database
session.bulk_update_mappings(Data, update_data)
File "/var/task/sqlalchemy/orm/session.py", line 2849, in bulk_update_mappings
mapper, mappings, True, False, False, False, False
File "/var/task/sqlalchemy/orm/session.py", line 2888, in _bulk_save_mappings
transaction.rollback(_capture_exception=True)
File "/var/task/sqlalchemy/util/langhelpers.py", line 68, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File "/var/task/sqlalchemy/util/compat.py", line 153, in reraise
raise value
File "/var/task/sqlalchemy/orm/session.py", line 2873, in _bulk_save_mappings
update_changed_only,
File "/var/task/sqlalchemy/orm/persistence.py", line 180, in _bulk_update
bookkeeping=False,
File "/var/task/sqlalchemy/orm/persistence.py", line 1029, in _emit_update_statements
% (table.description, len(records), rows)
sqlalchemy.orm.exc.StaleDataError: UPDATE statement on table 'data_table' expected to update 33 row(s); 32 were matched.
</code></pre></div>
<p dir="auto">I have tried multiple things, following is a snippet of the current code base:</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="def connect_db():
    def connect():
        return mysql.connector.connect(host=hostname, user=username, password=password, database=database, port=port)

    db = create_engine(f'mysql+mysqlconnector://', creator=connect, isolation_level='REPEATABLE_READ', echo=False)
    return db


# establish connection once per container
engine = connect_db()
Data.__table__.create(bind=engine, checkfirst=True)
Session = sessionmaker(bind=engine)

def save_to_database(data):
    session = Session()
    try:
        ids = [result[0] for result in session.query(Data.id).with_for_update(nowait=True).all()]
        ...
        #saving logic
        ...
        session.bulk_insert_mappings(Data, insert_data)
        session.flush()

        session.bulk_update_mappings(Data, update_data)
        session.flush()

        session.commit()

    except:
        session.rollback()
        raise
    finally:
        session.close()

def handler(event, context):
    output = save_to_database(event)
    return {'records': output}"><pre class="notranslate"><code class="notranslate">def connect_db():
    def connect():
        return mysql.connector.connect(host=hostname, user=username, password=password, database=database, port=port)

    db = create_engine(f'mysql+mysqlconnector://', creator=connect, isolation_level='REPEATABLE_READ', echo=False)
    return db


# establish connection once per container
engine = connect_db()
Data.__table__.create(bind=engine, checkfirst=True)
Session = sessionmaker(bind=engine)

def save_to_database(data):
    session = Session()
    try:
        ids = [result[0] for result in session.query(Data.id).with_for_update(nowait=True).all()]
        ...
        #saving logic
        ...
        session.bulk_insert_mappings(Data, insert_data)
        session.flush()

        session.bulk_update_mappings(Data, update_data)
        session.flush()

        session.commit()

    except:
        session.rollback()
        raise
    finally:
        session.close()

def handler(event, context):
    output = save_to_database(event)
    return {'records': output}
</code></pre></div>
<p dir="auto">I even added <code class="notranslate">.with_for_update(nowait=True)</code> based on the recommendation from "<a href="https://stackoverflow.com/questions/30763961/update-statement-on-table-xxx-expected-to-update-1-rows-0-were-matched-with" rel="nofollow">https://stackoverflow.com/questions/30763961/update-statement-on-table-xxx-expected-to-update-1-rows-0-were-matched-with</a>"</p>
<p dir="auto">Also I have already checked <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="384634406" data-permission-text="Title is private" data-url="https://github.com/sqlalchemy/sqlalchemy/issues/3476" data-hovercard-type="issue" data-hovercard-url="/sqlalchemy/sqlalchemy/issues/3476/hovercard" href="https://github.com/sqlalchemy/sqlalchemy/issues/3476">#3476</a> as it talked about similar issue but the problem is I am not deleting any ids from the table yet, its just insert or update. I am segregating insert and update after querying for all ids and then depending on that deciding whether the ids should be updated or inserted.</p>
<p dir="auto">Any help or pointers in this regard would be really appreciated.</p>