Issue 1:
Migrated issue, originally created by Adrian (@thiefmaster)

Example to reproduce:

from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import *
from sqlalchemy.dialects.postgresql import JSON

Base = declarative_base()

class ProtectionMode(object):
public = 0
inheriting = 1
protected = 2

class Event(Base):
__tablename__ = 'events'
id = Column(Integer, primary_key=True)
category_id = Column(Integer, ForeignKey('categories.id'), nullable=True, index=True)
category = relationship('Category', backref='events')

def __repr__(self):
return '<Event {}>'.format(self.id)

class Category(Base):
__tablename__ = 'categories'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('categories.id'), index=True, nullable=True)
protection_mode = Column(SmallInteger, nullable=False, default=ProtectionMode.inheriting)
icon_metadata = Column(JSON, nullable=False, default=lambda: None)
children = relationship('Category', primaryjoin=(id == remote(parent_id)),
backref=backref('parent', primaryjoin=(remote(id) == parent_id)))

@classmethod
def get_icon_data_cte(cls):
cat_alias = aliased(cls)
cte_query = (select([cat_alias.id, cat_alias.id.label('source_id'), cat_alias.icon_metadata])
.where(cat_alias.parent_id.is_(None))
.cte(recursive=True))
rec_query = (select([cat_alias.id,
case({'null': cte_query.c.source_id}, else_=cat_alias.id,
value=func.json_typeof(cat_alias.icon_metadata)),
case({'null': cte_query.c.icon_metadata}, else_=cat_alias.icon_metadata,
value=func.json_typeof(cat_alias.icon_metadata))])
.where(cat_alias.parent_id == cte_query.c.id))
return cte_query.union_all(rec_query)

@classmethod
def get_protection_cte(cls):
cat_alias = aliased(cls)
cte_query = (select([cat_alias.id, cat_alias.protection_mode])
.where(cat_alias.parent_id.is_(None))
.cte(recursive=True))
rec_query = (select([cat_alias.id,
case({ProtectionMode.inheriting: cte_query.c.protection_mode},
else_=cat_alias.protection_mode, value=cat_alias.protection_mode)])
.where(cat_alias.parent_id == cte_query.c.id))
return cte_query.union_all(rec_query)

def __repr__(self):
return '<Category {}>'.format(self.id)

cte = Category.get_protection_cte()
query = select([cte.c.protection_mode]).where(cte.c.id == Category.id).correlate_except(cte)
Category.effective_protection_mode = column_property(query, deferred=True)

cte = Category.get_icon_data_cte()
query = (select([func.json_build_object('source_id', cte.c.source_id,
'metadata', cte.c.icon_metadata)])
.where(cte.c.id == Category.id)
.correlate_except(cte))
Category.effective_icon_data = column_property(query, deferred=True)

e = create_engine('postgresql:///test', echo=True)
Base.metadata.create_all(e)
s = Session(e)

if not s.query(Category).count():
root = Category(protection_mode=ProtectionMode.public)
cat = Category(parent=root)
s.add(root)
e = Event(category=root)
s.add(e)
s.commit()
print 'created data'
else:
root = s.query(Category).filter_by(parent_id=None).one()
cat = root.children[0]
e = root.events[0]
print '\n\n\nFIRST READ\n'
print cat.effective_protection_mode
e.category = cat
s.flush()
print '\n\n\nSECOND READ\n'
print cat.effective_protection_mode

Relevant output (on the second run):

FIRST READ

2018-01-31 16:05:18,233 INFO sqlalchemy.engine.base.Engine WITH RECURSIVE anon_2(id, protection_mode) AS
(SELECT categories_1.id AS id, categories_1.protection_mode AS protection_mode
FROM categories AS categories_1
WHERE categories_1.parent_id IS NULL UNION ALL SELECT categories_1.id AS id, CASE categories_1.protection_mode WHEN %(param_1)s THEN anon_2.protection_mode ELSE categories_1.protection_mode END AS anon_3
FROM categories AS categories_1, anon_2
WHERE categories_1.parent_id = anon_2.id)
SELECT (SELECT anon_2.protection_mode
FROM anon_2
WHERE anon_2.id = categories.id) AS anon_1
FROM categories
WHERE categories.id = %(param_2)s
2018-01-31 16:05:18,233 INFO sqlalchemy.engine.base.Engine {'param_1': 1, 'param_2': 2}
0
2018-01-31 16:05:18,234 INFO sqlalchemy.engine.base.Engine UPDATE events SET category_id=%(category_id)s WHERE events.id = %(events_id)s
2018-01-31 16:05:18,234 INFO sqlalchemy.engine.base.Engine {'category_id': 2, 'events_id': 1}

SECOND READ

2018-01-31 16:05:18,236 INFO sqlalchemy.engine.base.Engine WITH RECURSIVE anon_2(id, protection_mode) AS
(SELECT categories_1.id AS id, categories_1.protection_mode AS protection_mode
FROM categories AS categories_1
WHERE categories_1.parent_id IS NULL UNION ALL SELECT categories_1.id AS id, CASE categories_1.protection_mode WHEN %(param_1)s THEN anon_2.protection_mode ELSE categories_1.protection_mode END AS anon_3
FROM categories AS categories_1, anon_2
WHERE categories_1.parent_id = anon_2.id),
anon_5(id, source_id, icon_metadata) AS
(SELECT categories_2.id AS id, categories_2.id AS source_id, categories_2.icon_metadata AS icon_metadata
FROM categories AS categories_2
WHERE categories_2.parent_id IS NULL UNION ALL SELECT categories_2.id AS id, CASE json_typeof(categories_2.icon_metadata) WHEN %(param_2)s THEN anon_5.source_id ELSE categories_2.id END AS anon_6, CASE json_typeof(categories_2.icon_metadata) WHEN %(param_3)s THEN anon_5.icon_metadata ELSE categories_2.icon_metadata END AS anon_7
FROM categories AS categories_2, anon_5
WHERE categories_2.parent_id = anon_5.id)
SELECT (SELECT anon_2.protection_mode
FROM anon_2
WHERE anon_2.id = categories.id) AS anon_1, (SELECT json_build_object(%(json_build_object_2)s, anon_5.source_id, %(json_build_object_3)s, anon_5.icon_metadata) AS json_build_object_1
FROM anon_5
WHERE anon_5.id = categories.id) AS anon_4
FROM categories
WHERE categories.id = %(param_4)s
2018-01-31 16:05:18,236 INFO sqlalchemy.engine.base.Engine {'param_4': 2, 'param_1': 1, 'param_3': 'null', 'param_2': 'null', 'json_build_object_3': 'metadata', 'json_build_object_2': 'source_id'}
0

As you can see, the second time the property is accessed, the other deferred column property is loaded as well - this results in a massive performance drop in the real (non-simplified) code since some of the deferred relationships are quite expensive.

This issue happens in 1.1.15 but not in 1.2.2, but I couldn't find any related changelog entry.

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

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
a = Column(Integer)
b = Column(Integer)

aplusb = column_property(
a + b, deferred=True
)

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

s = Session(e)
s.add(A(a=1, b=2))
s.commit()

a1 = s.query(A).first()
assert 'aplusb' not in a1.__dict__
a1.b = 5
s.flush()
s.commit()
a1.b
assert 'aplusb' not in a1.__dict__