[Sqlalchemy-tickets] Issue #4000: Problems with Update and Table Inheritance (zzzeek/sqlalchemy)

[quasiben <iss...@bi...>]

New issue 4000: Problems with Update and Table Inheritance
https://bitbucket.org/zzzeek/sqlalchemy/issues/4000/problems-with-update-and-table-inheritance

quasiben:

## Dev Setup
 - Python 3.5
 - SQLAlchemy 1.13
 - OSX


I am a bit new to inheritance modeling for tables so it's quite possible this is not a bug but rather lack of understanding.  I've also tried scanning through past issues and I don't think I've seen this addressed though [this posting from google groups](https://groups.google.com/forum/#!topic/sqlalchemy/BKHxW-wpaak) may be relevant

## Goal

I'm trying to update a single column in the parent class (`People`) while using the child class (`Engineer`).  

### Working Code 

```python
import os
import sys

from sqlalchemy import Column, create_engine, ForeignKey, Integer, String, DateTime

from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql import func
from sqlalchemy.ext.declarative import declarative_base


try:
   os.remove('test.db')
except FileNotFoundError:
   pass 

engine = create_engine('sqlite:///test.db', echo=True)
Session = sessionmaker(engine)

Base = declarative_base()


class People(Base):
    __tablename__ = 'people'
    discriminator = Column('type', String(50))
    __mapper_args__ = {'polymorphic_on': discriminator}

    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    updated = Column(DateTime, server_default=func.now(), onupdate=func.now())

class Engineer(People):
    __tablename__ = 'engineer'
    __mapper_args__ = {'polymorphic_identity': 'engineer'}
    id = Column(Integer, ForeignKey('people.id'), primary_key=True)
    kind = Column(String(100), nullable=True)

Base.metadata.create_all(engine)

session = Session()

e = Engineer()
e.name = 'Mike'
session.add(e)
session.flush()
session.commit()

# works when updating the object
e.name = "Doug"
session.add(e)
session.commit()


# works using the base class for the query
count = session.query(People).filter(
                           People.name.is_('Doug')).update({People.name: 'James'})

# fails when using the derived class
count = session.query(Engineer).filter(
                           Engineer.name.is_('James')).update({Engineer.name: 'Mary'})

session.commit()
print("Count: {}".format(count))
```

## Problem

The last update fails:

```python
session.query(Engineer).filter(
                           Engineer.name.is_('James')).update({Engineer.name: 'Mary'})

# Generated SQL
# UPDATE engineer SET name=?, updated=CURRENT_TIMESTAMP FROM people WHERE # people.name IS ?
```

As you can see the statement is trying to update rows in incorrect table, `name` is in the parent table.

I'm a little unclear about how inheritance tables should work but it seems like updates should work transparently with the derived object. Meaning, when I update `Engineer.name` querying against the `Engineer` object, SQLAlchemy should know to update the `People` table. 

I believe the the google [google groups](https://groups.google.com/forum/#!topic/sqlalchemy/BKHxW-wpaak) possibly explains why SQLAlchemy doesn't emit two update statements if I were to update `kind`.  One for the update to `kind` and another for the `onupdate` column `updated`, correct?