positive set of None should override a default on nullable col?

Issue #3250 resolved
Oleg Pidsadnyi
created an issue

Mike, I've encountered a behavior that is a bit unexpected:

class Model(Base):
    a = Column(Integer, nullable=True, default=1)

m = Model()
m.a = None
session.add(m)

In: print m.a
Out: None

session.commit()

In: print m.a
Out: 1

None is expected :(

I understand that the configuration is silly, but this is the existing code. Setting None to the column doesn't cancel the default and the default thinks that the column wasn't set, so it uses the default value in the end.

Comments (14)

  1. Michael Bayer repo owner

    there's not a bug here as that is just how it works. It has to do with the fact that the "implicit" value for an attribute is also None, e.g.:

    >>> m = Model()
    >>> m.x
    None
    

    for a long time, the above "get" operation would actually set the value of None in the state. This was just changed in 1.0 which is described in http://docs.sqlalchemy.org/en/latest/changelog/migration_10.html#changes-to-attribute-events-and-other-operations-regarding-attributes-that-have-no-pre-existing-value. So only with that change is it even at all feasible for us to tell the difference between a column where one said m.x = None vs. print m.x, as far as if that None is the thing we want to insert.

    However, a lot of things are changing in 1.0, like, lots, so changing this behavior more fundamentally is really not something I want to get into, it's very deep and would really cause a lot of problems for people as it's worked this way for literally ten years.

    If you want to force it to NULL, you can actually say that, using null():

    from sqlalchemy import Column, Integer, create_engine, null
    from sqlalchemy.orm import Session
    from sqlalchemy.ext.declarative import declarative_base
    
    Base = declarative_base()
    
    
    class A(Base):
        __tablename__ = 'a'
        id = Column(Integer, primary_key=True)
        x = Column(Integer, nullable=True, default=1)
    
    e = create_engine("sqlite://", echo=True)
    Base.metadata.create_all(e)
    
    s = Session(e)
    s.add(A(x=null()))
    s.commit()
    assert s.query(A.x).scalar() == None
    
  2. Oleg Pidsadnyi reporter

    Thanks for reminding about null()! Still in the bottom of my heart I feel like m.a = None assignment is an explicit operation, unlike get indeed. I guess the setter could set null() when explicit assignment to None is going on:

    m.a = None # null() is assigned here internally

    It feels like assigning implicit None to explicit None makes sense. Like a dirty state anyway. But m.a = null() is also fine with me. Thanks

  3. Michael Bayer repo owner

    in particular, take a look at expected DB behavior when defaults are present, as far as nullable vs non-nullable columns (what happens if you insert NULL into a col w/ default that is NOT NULL? does the DB ignore the NULL or does it raise)? This would potentially be the first time the ORM takes the nullability of a column into account for a behavior.

  4. Oleg Pidsadnyi reporter

    Exactly. In my case there's not reasonable default there since ages ago. Before the huge refactoring I had to cancel the default somehow in one case. Normally people wouldn't notice this behavior. Would be nice to have a note in the documentation explaining the default. Thanks again.

  5. Michael Bayer repo owner
    • Added a new type-level modifier :meth:.TypeEngine.evaluates_none which indicates to the ORM that a positive set of None should be persisted as the value NULL, instead of omitting the column from the INSERT statement. This feature is used both as part of the implementation for 🎫3514 as well as a standalone feature available on any type. fixes #3250
    • add new documentation section illustrating the "how to force null" use case of #3250
    • alter our change from #3514 so that the class-level flag is now called "should_evaluate_none"; so that "evaluates_none" is now a generative method.

    → <<cset 80aeba3d5e02>>

  6. Log in to comment