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

[Oleg Pidsadnyi]

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.

[Mike Bayer]

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

[Oleg Pidsadnyi]

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

[Mike Bayer]

I totally agree, but only in 1.0 do we even start breaking things up to work that way. I'll tag this as a question for 1.1.

[Mike Bayer]

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.

[Oleg Pidsadnyi]

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.

[Mike Bayer]

where in the docs would something like this be that anyone who needs to know it would even find it? :)

[Oleg Pidsadnyi]

http://docs.sqlalchemy.org/en/rel_0_8/core/defaults.html The first thing I did - tried to look here. I don't see a usage of null() here. Would be handy.

[Mike Bayer]

can't go there because this is ORM specific

[Mike Bayer]

that is, you don't really need null() with Core, it will work of course but a None vs. omitted is respected at the core level the way you expect.

[Mike Bayer]

We can expand what we did in #3514 here.

[Mike Bayer]

• 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>>

[Mike Bayer]

Issue #3794 was marked as a duplicate of this issue.