Issue 1:
Migrated issue, originally created by Michael Bayer (@zzzeek)

this patch begins to provide this behavior. However a lazyloader from an expired object emits a second unnecessary load (or an unnecessary join, depending on how you look at it), and polymorphic loading breaks entirely, probably due to an incompatibility with the partial joined inheritance loader.

diff -r cfdc41c75ce2e2228d71733212bbaca0fe17ce35 lib/sqlalchemy/orm/state.py
--- a/lib/sqlalchemy/orm/state.py	Mon Apr 05 13:16:29 2010 -0400
+++ b/lib/sqlalchemy/orm/state.py	Mon Apr 05 15:43:40 2010 -0400
@@ -262,9 +262,10 @@
if kw.get('passive') is attributes.PASSIVE_NO_FETCH:
return attributes.PASSIVE_NO_RESULT

- toload = self.expired_attributes.\
- intersection(self.unmodified)
-
+ toload = self.expired_attributes.intersection(self.unmodified).union(
+ self.unloaded.intersection(self.expire_on_missing)
+ )
+
self.manager.deferred_scalar_loader(self, toload)

# if the loader failed, or this
@@ -277,6 +278,10 @@
return ATTR_WAS_SET

@property
+ def expire_on_missing(self):
+ return set(key for key in self.manager if self.manager[key](key).impl.expire_missing)
+
+ @property
def unmodified(self):
"""Return the set of keys which have no uncommitted changes"""

diff -r cfdc41c75ce2e2228d71733212bbaca0fe17ce35 test/orm/test_expire.py
--- a/test/orm/test_expire.py	Mon Apr 05 13:16:29 2010 -0400
+++ b/test/orm/test_expire.py	Mon Apr 05 15:43:40 2010 -0400
@@ -425,17 +425,7 @@
assert len(u.addresses) == 2

@testing.resolve_artifact_names
- def test_joinedload_props_dontload(self):
- # relationships currently have to load separately from scalar instances.
- # the use case is: expire "addresses". then access it. lazy load
- # fires off to load "addresses", but needs foreign key or primary key
- # attributes in order to lazy load; hits those attributes, such as
- # below it hits "u.id". "u.id" triggers full unexpire operation,
- # joinedloads addresses since lazy='joined'. this is all wihtin lazy load
- # which fires unconditionally; so an unnecessary joinedload (or
- # lazyload) was issued. would prefer not to complicate lazyloading to
- # "figure out" that the operation should be aborted right now.
-
+ def test_joinedload_props_load(self):
mapper(User, users, properties={
'addresses':relationship(Address, backref='user', lazy='joined'),
})
@@ -444,11 +434,30 @@
u = sess.query(User).get(8)
sess.expire(u)
u.id
- assert 'addresses' not in u.__dict__
+ assert 'addresses' in u.__dict__
u.addresses
assert 'addresses' in u.__dict__

@testing.resolve_artifact_names
+ def test_joinedload_props_load_two(self):
+ mapper(User, users, properties={
+ 'addresses':relationship(Address, backref='user', lazy='joined'),
+ })
+ mapper(Address, addresses)
+ sess = create_session()
+ u = sess.query(User).get(8)
+ sess.expire(u)
+
+ # current bug: u.addresses unexpires attributes,
+ # eagerlaods u.addresses. lazyloader for u.addresses
+ # then runs anyway.
+ def go():
+ u.addresses
+ assert 'addresses' in u.__dict__
+ assert 'id' in u.__dict__
+ self.assert_sql_count(testing.db, go, 1)
+
+ @testing.resolve_artifact_names
def test_expire_synonym(self):
mapper(User, users, properties={
'uname': sa.orm.synonym('name')

Issue 2:
How do we avoid session.bulk_update_mappings() generating WHERE clause with table name prefixed to the column name? Is this a bug?

Please refer to the MCVE posted by @zzzeek at:

#4963 (comment)

Following is the debug of running the same:

2021-05-06 15:01:06,697 INFO sqlalchemy.engine.base.Engine select version()
2021-05-06 15:01:06,698 INFO sqlalchemy.engine.base.Engine {}
2021-05-06 15:01:06,702 INFO sqlalchemy.engine.base.Engine select current_schema()
2021-05-06 15:01:06,702 INFO sqlalchemy.engine.base.Engine {}
2021-05-06 15:01:06,706 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2021-05-06 15:01:06,706 INFO sqlalchemy.engine.base.Engine {}
2021-05-06 15:01:06,708 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2021-05-06 15:01:06,708 INFO sqlalchemy.engine.base.Engine {}
2021-05-06 15:01:06,710 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings
2021-05-06 15:01:06,710 INFO sqlalchemy.engine.base.Engine {}
2021-05-06 15:01:06,714 INFO sqlalchemy.engine.base.Engine select relname from pg_class c join pg_namespace n on n.oid=c.relnamespace where pg_catalog.pg_table_is_visible(c.oid) and relname=%(name)s
2021-05-06 15:01:06,714 INFO sqlalchemy.engine.base.Engine {'name': 'test'}
2021-05-06 15:01:06,718 INFO sqlalchemy.engine.base.Engine
CREATE TABLE test (
test_id SERIAL NOT NULL,
col_1 INTEGER,
col_2 INTEGER,
PRIMARY KEY (test_id)
)

2021-05-06 15:01:06,718 INFO sqlalchemy.engine.base.Engine {}
2021-05-06 15:01:06,724 INFO sqlalchemy.engine.base.Engine COMMIT
2021-05-06 15:01:06,728 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2021-05-06 15:01:06,729 INFO sqlalchemy.engine.base.Engine INSERT INTO test (test_id, col_1, col_2) VALUES (%(test_id)s, %(col_1)s, %(col_2)s)
2021-05-06 15:01:06,729 INFO sqlalchemy.engine.base.Engine ({'test_id': 1, 'col_1': 1, 'col_2': None}, {'test_id': 2, 'col_1': 2, 'col_2': None}, {'test_id': 3, 'col_1': 3, 'col_2': None})
2021-05-06 15:01:06,737 INFO sqlalchemy.engine.base.Engine COMMIT
2021-05-06 15:01:06,742 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2021-05-06 15:01:06,742 INFO sqlalchemy.engine.base.Engine UPDATE test SET col_1=%(col_1)s, col_2=%(col_2)s WHERE test.test_id = %(test_test_id)s
2021-05-06 15:01:06,743 INFO sqlalchemy.engine.base.Engine ({'col_1': 1, 'col_2': -1, 'test_test_id': 1}, {'col_1': 2, 'col_2': -2, 'test_test_id': 2}, {'col_1': 3, 'col_2': -3, 'test_test_id': 3})
2021-05-06 15:01:06,751 INFO sqlalchemy.engine.base.Engine SELECT test.col_1 AS test_col_1, test.col_2 AS test_col_2
FROM test
2021-05-06 15:01:06,751 INFO sqlalchemy.engine.base.Engine {}

Process finished with exit code 0

Versions

OS: MacOS Catalina

Python: 3.6

SQLAlchemy: 1.3.15

Database: Postgres

DBAPI:

Duplicating my comment at #4963 (comment) below:

Here is the exact line:

2021-05-06 15:01:06,742 INFO sqlalchemy.engine.base.Engine UPDATE test SET col_1=%(col_1)s, col_2=%(col_2)s WHERE test.test_id = %(test_test_id)s

When I tried changing the PK to id instead of test_id, the where clause changed to:

WHERE test.id = %(test_id)s

In both the cases, the bulk update fails because of mismatching column names. Even with introducing a name attribute as below, the issue still persists.

test_id = Column(Integer, name="test_id", nullable=False, primary_key=True)

Am I missing something?