version counter fires off when no net change

[tonymillion]

I have a model as such (in this case I'm using Flask-SQLAlchemy, but thats not relevant to the error):

class Person(BaseModel):
    <snip - stuff for model, id as primary key etc>
    purchase_uuid = db.Column(postgresql.UUID(as_uuid=True), default=uuid.uuid4)

    __mapper_args__ = {
        'version_id_col': purchase_uuid,
        'version_id_generator': False
    }

this works fine i.e. we can block a certain UPDATES unless purchase_uuid is being updated (taken from http://docs.sqlalchemy.org/en/latest/orm/versioning.html#programmatic-or-conditional-version-counters )

We also have another model

class Session(BaseModel):
    <snip - stuff for model, id as primary key etc>

    # the person who is represented by this Session
    person_id = db.Column(db.Integer, db.ForeignKey("people.id"), index=True)

    person = db.relationship("Person",
                             # backref=db.backref("sessions", lazy="dynamic"),
                             lazy="joined",
                             uselist=False,
                             foreign_keys=[person_id])

When I uncomment the backref and then create a session object and assign a person:

    session = Session()
    session.person = person
    db.session.add(session)
    db.session.commit()

I get the following:

ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near "WHERE"
LINE 1: UPDATE people SET  WHERE people.id = 1 AND people.purchase_u...
                           ^
 [SQL: 'UPDATE people SET  WHERE people.id = %(people_id)s AND people.purchase_uuid = %(people_purchase_uuid)s RETURNING people.purchase_uuid'] [parameters: {'people_id': 1, 'people_purchase_uuid': UUID('e3a73745-0979-42e3-90c5-72e6a32219c7')}]

As you can see SQLAlchemy is outputting some invalid SQL i.e. UPDATE people SET WHERE

If I comment out either the backref (the sessions relationship) or the __mapper_args__ the problem goes away.

[Mike Bayer]

<snip - stuff for model, id as primary key etc>

Thanks for this bug report, though I'm now recreating all that "stuff" because i have no idea what the problem is without running it myself (see http://www.sqlalchemy.org/participate.html#bugs)

[Mike Bayer]

the backref here has the effect that Person receives a net change when you associate the SessionThing with it. (note the name Session, I just can't use that for a mapped class name). the surprise for me is that this generates a new version counter for Person. It should not. But this is very strange that it does and unfortunately that's nothing I can change in a point release. in the case where there is absolutely no version generator, that should at least be less controversial.

[Mike Bayer]

ah. It's a regression. We can change away.

[Mike Bayer]

• Fixed a major regression in the 1.0 series where the version_id_counter feature would cause an object's version counter to be incremented when there was no net change to the object's row, but instead an object related to it via relationship (e.g. typically many-to-one) were associated or de-associated with it, resulting in an UPDATE statement that updates the object's version counter and nothing else. In the use case where the relatively recent "server side" and/or "programmatic/conditional" version counter feature were used (e.g. setting version_id_generator to False), the bug could cause an UPDATE without a valid SET clause to be emitted. fixes #3465

→ <<cset 1e2f1f5baabd>>

[Mike Bayer]

that was a serious issue, thanks for reporting. 1.0.6 would ideally be released this week.