[Sqlalchemy-tickets] Issue #3252: Query.update on polymorphic tables generates statements with a very wide JOIN (zzzeek/sqlalchemy)

[Abhinav G. <iss...@bi...>]

New issue 3252: Query.update on polymorphic tables generates statements with a very wide JOIN
https://bitbucket.org/zzzeek/sqlalchemy/issue/3252/queryupdate-on-polymorphic-tables

Abhinav Gupta:

Consider the following setup,
```
#!python
class User(Base):
    __tablename__ = 'users'

    user_id = Column(Integer, primary_key=True)
    name = Column(String)
    role = Column('role', Enum('user', 'super'))

    __mapper_args__ = {'polymorphic_on': role, 'polymorphic_identity': 'user'}


class SuperUser(User):
    __tablename__ = 'superusers'
    __mapper_args__ = {'polymorphic_identity': "super"}

    super_id = Column(Integer, primary_key=True)
    users_user_id = Column(Integer, ForeignKey('users.user_id'))

    email = Column('email', String)
```

If you try to perform an update on a query using the base class's primary key like so,
```
#!python
session.query(SuperUser).filter_by(user_id=some_id).update(
    {'email': 'fo...@ex...'}
)
```

The system will generate the following `UPDATE` command.
```
#!sql
UPDATE superusers SET email=? FROM users WHERE users.user_id = ?
```

This is trying to join `superusers` with `users` without any join criteria. That's obviously wrong.

To make it work, you have to either use the foreign key column name or fetch the original object and update on that.
```
#!python
session.query(SuperUser).filter_by(users_user_id=some_id).update(
    {'email': 'fo...@ex...'}
)
session.commit()

# or

su = session.query(SuperUser).get(some_id)
su.email = 'fo...@ex...'
session.add(su)
session.commit()
```

This generates the correct command:
```
#!sql
UPDATE superusers SET email=? WHERE superusers.super_id = ?
```

I have tested it with SQLAlchemy 0.8.2 against Postgres and SQLite, and SQLAlchemy 0.9.8 against SQLite.

In case of SQLite, the command immediately fails because it doesn't support `JOIN`s in `UPDATE`s. Here's the full trace:
```
Traceback (most recent call last):
  File "bug.py", line 53, in <module>
    {'email': 'fo...@ex...'}
  File "$HOME/dev/py/sqlalchemy-bug-repro/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2796, in update
    update_op.exec_()
  File "$HOME/dev/py/sqlalchemy-bug-repro/env/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 897, in exec_
    self._do_exec()
  File "$HOME/dev/py/sqlalchemy-bug-repro/env/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 995, in _do_exec
    update_stmt, params=self.query._params)
  File "$HOME/dev/py/sqlalchemy-bug-repro/env/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 991, in execute
    bind, close_with_result=True).execute(clause, params or {})
  File "$HOME/dev/py/sqlalchemy-bug-repro/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 729, in execute
    return meth(self, multiparams, params)
  File "$HOME/dev/py/sqlalchemy-bug-repro/env/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 322, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "$HOME/dev/py/sqlalchemy-bug-repro/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 826, in _execute_clauseelement
    compiled_sql, distilled_params
  File "$HOME/dev/py/sqlalchemy-bug-repro/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 958, in _execute_context
    context)
  File "$HOME/dev/py/sqlalchemy-bug-repro/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1159, in _handle_dbapi_exception
    exc_info
  File "$HOME/dev/py/sqlalchemy-bug-repro/env/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "$HOME/dev/py/sqlalchemy-bug-repro/env/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 951, in _execute_context
    context)
  File "$HOME/dev/py/sqlalchemy-bug-repro/env/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 436, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (OperationalError) near "FROM": syntax error u'UPDATE superusers SET email=? FROM users WHERE users.user_id = ?' ('fo...@ex...', 2)
```

In case of Postgres, it ends up joining the tables and possibly updating everything (I haven't had a chance to confirm the last part).

Sample code to reproduce this attached to the report.