4

I just stumbled upon this behaviour of sqlalchemy:

>>> from sqlalchemy import Column, null

>>> print(Column("x") == null())
x IS NULL
>>> print(Column("x") == None)
x IS NULL

>>> print(null() == Column("x"))
NULL = x
>>> print(None == Column("x"))
x IS NULL

Can someone explain why == is not commutative for null() while it is when using None? What's the purpose?

Lee
  • 2,610
  • 5
  • 29
  • 36
chpolste
  • 43
  • 3

1 Answers1

1

To begin, take a look at Identity vs. Equailty for an explanation on why your None conditions are both returning x IS NULL.

The reason why your null() clauses are returning different results is due to how the operators work.

As per the documentation, the operators work like the following:

In a column context, produces the clause a = b. If the target is None, produces a IS NULL.

Explanation:

Column("x") == null() # a = b -> b is NullType; returning IS NULL

null() == Column('x') # a = b -> b is ColumnType; not returning IS NULL

For more information, you may view the Sqlalchemy Docs

Community
  • 1
  • 1
Wondercricket
  • 7,651
  • 2
  • 39
  • 58
  • `None == Column("x")` calls `Column("x") == None` since the first comparison returns `NotImplemented`, therefore it works both ways - the Python part makes sense. The second question remains: is there any case in which one would actually want to obtain `NULL = `? The docs say "if the target is None", highlighting the 2nd argument, and looking at `sqlalchemy.sql.default_comparator._boolean_compare`, the function only handles the comparison separately if the 2nd argument is `None`/`Null` but not when the 1st is. Still, this behaviour seems like a bug to me or at least very unintuitive. – chpolste Aug 28 '15 at 08:15
  • @chpolste I agree, it is not intuitive and more than likely a bug. If I knew how, I would report the bug to whomever maintains sqlalchemy. – Wondercricket Aug 28 '15 at 12:38
  • @chpolste And as far as I am aware, I cannot think of any case where one would use `NULL =` – Wondercricket Aug 28 '15 at 13:12