Issue 1:
Migrated issue, originally created by Anonymous

SQLAlchemy 0.3.0

Oracle 10g

cx_Oracle 4.2

Red Hat Enterprise Linux WS release 3 (Taroon Update 7)

If you have autoloaded tables Spam and Egg related by Spam has-many Egg like so:

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

then the unconstrained select

session.query(Spam).select()

works properly but

session.query(Spam).select_by(col=value)

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.

The unconstrained query produces SQL like this:

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

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:

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

I see a similar problem if I omit the lazy=False from the call to relation().

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.

Attachments: bug.2.py | bug.py | bugout.txt

Issue 2:
Hi,

I am getting a StaleDataError with SQLAlchemy==1.3.6 whenever sqlalchemy is trying to use session.bulk_update_mappings() 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.

This happens intermittently without any definite pattern. Following is the stack trace:

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.

I have tried multiple things, following is a snippet of the current code base:

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}

I even added .with_for_update(nowait=True) based on the recommendation from "https://stackoverflow.com/questions/30763961/update-statement-on-table-xxx-expected-to-update-1-rows-0-were-matched-with"

Also I have already checked #3476 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.

Any help or pointers in this regard would be really appreciated.