NULL does not override default value if new instance is created when session.merge(row)

Issue #4278 duplicate
jan.budinsky
created an issue

Default column value is not overridden by NULL in Declarative API as it should be.

If a column has a non-null default value that is overridden by another non-null value, everything works as expected - the specified value is always inserted as the value of the column instead of the default one.

This however does not seem to be the case when overriding non-null default value with NULL. If a matching row (via primary key) has been already created (merge does UPDATE), the column will have NULL filled as its value, as expected. But if a matching row hasn't been created yet (merge does INSERT INTO), the column will have default value instead of expected NULL.

The issue is the same using both postgresql and in-memory sqlite.

Please see the script below reproducing the issue, thank you.

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column
from sqlalchemy.dialects.postgresql import BIGINT, VARCHAR


CONN_STR = "sqlite:///:memory:"
Base = declarative_base()


class Table(Base):
    __tablename__ = "table"

    id = Column(BIGINT, primary_key=True)
    field = Column(VARCHAR, default="placeholder")


def merge(session, row):
    session.begin()
    try:
        session.merge(row)
        session.commit()
    except:
        session.rollback()
        raise


def select(session, table, **criteria):
    return session.query(table).filter_by(**criteria).all()


def delete(session, table, **criteria):
    return session.query(table).filter_by(**criteria).delete()


def main():
    engine = create_engine(CONN_STR, echo=False)
    Base.metadata.create_all(engine)  # create table
    session = scoped_session(sessionmaker(bind=engine, autocommit=True))()

    data_null = {
        "id": 1,
        "field": None
    }
    data_value = {
        "id": 2,
        "field": "value"
    }

    # cleanup
    delete(session=session, table=Table, id=data_null["id"])
    delete(session=session, table=Table, id=data_value["id"])

    merge(session=session, row=Table(**data_null))
    print(select(session=session, table=Table, id=data_null["id"])[0].field)  # "placeholder" ???
    merge(session=session, row=Table(**data_null))
    print(select(session=session, table=Table, id=data_null["id"])[0].field)  # None

    merge(session=session, row=Table(**data_value))
    print(select(session=session, table=Table, id=data_value["id"])[0].field)  # "value"
    merge(session=session, row=Table(**data_value))
    print(select(session=session, table=Table, id=data_value["id"])[0].field)  # "value"


if __name__ == "__main__":
    main()

Environment:

python version - 3.6.3
SQLAlchemy version - 1.2.8

Comments (4)

  1. Michael Bayer repo owner

    hi there -

    thanks for the very nice test case and complete bug report. This issue has already been addressed in #3250 using the .evaluates_none() marker:

    class Table(Base):
        __tablename__ = "table"
    
        id = Column(BIGINT, primary_key=True)
        field = Column(VARCHAR().evaluates_none(), default="placeholder")
    

    with this qualifier your program's output is:

    None
    None
    value
    value
    

    the ORM's behavior here has been the same way for many years, as it was not capable of reliably determining on INSERT if a "None" value was positively set or not until the 1.1 series, so the existing behavior was maintained, and the newer version of the behavior which is what you're looking for was added as an option to the datatype. Background on this APi overall is at http://docs.sqlalchemy.org/en/latest/orm/persistence_techniques.html#forcing-null-on-a-column-with-a-default.

  2. jan.budinsky reporter

    Hello, thanks for your fast answer. I somehow failed to find the marker, with that everything works as expected, thank you very much for your help. :)

  3. Michael Bayer repo owner

    it's unfortunately not easy to find in the docs nor very intuitive and at some point the behavior here should be the default but it would be extremely backwards incompatible for applications relying upon the previous behavior.

  4. Log in to comment