PostgreSQL DATERANGE(): comparison with None does not generate expected "IS NOT NULL"

Issue #4229 resolved
Lele Gaifax
created an issue

The following script:

import sqlalchemy as sa
import sqlalchemy.orm as orm
import sqlalchemy.types as types
import sqlalchemy.dialects.postgresql as sapg


md = sa.MetaData()


testtable = sa.Table(
    'testtable', md,
    sa.Column('id', types.Integer(),
              primary_key=True,
              nullable=False),
    sa.Column('name', types.Unicode(192),
              nullable=False),
    sa.Column('visibility', sapg.DATERANGE())
)


class Base:
    def __init__(self, **kwargs):
        for k in kwargs:
            setattr(self, k, kwargs[k])


class TestTable(Base):
    pass


orm.mapper(TestTable, testtable)


e = sa.create_engine('postgresql://localhost/test', echo=True)
md.create_all(e)


s = orm.Session(e)

o1 = TestTable(id=1, name='foo')
s.add(o1)
s.commit()

q = s.query(TestTable).filter(TestTable.name != None)
print(str(q))
q = s.query(TestTable).filter(TestTable.name == None)
print(str(q))

q = s.query(TestTable).filter(TestTable.visibility != None)
print(str(q))
q = s.query(TestTable).filter(TestTable.visibility == None)
print(str(q))

prints:

SELECT testtable.id AS testtable_id, testtable.name AS testtable_name, testtable.visibility AS testtable_visibility 
FROM testtable 
WHERE testtable.name IS NOT NULL
SELECT testtable.id AS testtable_id, testtable.name AS testtable_name, testtable.visibility AS testtable_visibility 
FROM testtable 
WHERE testtable.name IS NULL
SELECT testtable.id AS testtable_id, testtable.name AS testtable_name, testtable.visibility AS testtable_visibility 
FROM testtable 
WHERE testtable.visibility <> %(visibility_1)s
SELECT testtable.id AS testtable_id, testtable.name AS testtable_name, testtable.visibility AS testtable_visibility 
FROM testtable 
WHERE testtable.visibility IS NULL

Am I missing something or is this unexpected?

Comments (6)

  1. Michael Bayer repo owner

    hm daterange was totally third party contribution :). looks wrong though.

    this would be the patch:

    diff --git a/lib/sqlalchemy/dialects/postgresql/ranges.py b/lib/sqlalchemy/dialects/postgresql/ranges.py
    index 38bfb37d4..034189a46 100644
    --- a/lib/sqlalchemy/dialects/postgresql/ranges.py
    +++ b/lib/sqlalchemy/dialects/postgresql/ranges.py
    @@ -33,7 +33,10 @@ class RangeOperators(object):
    
             def __ne__(self, other):
                 "Boolean expression. Returns true if two ranges are not equal"
    -            return self.expr.op('<>')(other)
    +            if other is None:
    +                return super(RangeOperators.comparator_factory, self).__ne__(other)
    +            else:
    +                return self.expr.op('<>')(other)
    
             def contains(self, other, **kw):
                 """Boolean expression. Returns true if the right hand operand,
    
  2. Lele Gaifax reporter

    Thank you Michael, as always, you're so fast... I was about to dig that out this evening!

    Next time I'll try to open an issue when I already have a fix at hand ;-)

  3. Michael Bayer repo owner

    Use base ne implementation for range types w/ None

    Fixed bug where the special "not equals" operator for the Postgresql "range" datatypes such as DATERANGE would fail to render "IS NOT NULL" when compared to the Python None value.

    Also break up range tests into backend round trip and straight compilation suites.

    Change-Id: Ibaee132b1ea7dac8b799495a27f98f82a7d9c028 Fixes: #4229

    → <<cset e1ac5dc63cc1>>

  4. Michael Bayer repo owner

    Use base ne implementation for range types w/ None

    Fixed bug where the special "not equals" operator for the Postgresql "range" datatypes such as DATERANGE would fail to render "IS NOT NULL" when compared to the Python None value.

    Also break up range tests into backend round trip and straight compilation suites.

    Change-Id: Ibaee132b1ea7dac8b799495a27f98f82a7d9c028 Fixes: #4229 (cherry picked from commit e1ac5dc63cc13cfbabe0ec7fbb3521bfb1b7b750)

    → <<cset a2499837456d>>

  5. Log in to comment