On Jun 8, 2013, at 1:33 AM, Greg Yang <[email protected]> wrote:
> if __name__ == '__main__':
> engine = create_engine('sqlite:///:memory:')
> Session = sessionmaker(engine)
> session = Session()
> Base.metadata.create_all(engine)
>
> b1 = B()
> b2 = B()
> b3 = B()
>
> b1.a_color = 'blue'
> session.add_all([b1, b2, b3])
>
> q = session.query(B).filter(B.a_color == None).all()
> p = session.query(B).filter(B.a_color != None).all()
>
> assert q == []
> assert set(p) == set([b1, b2, b3])
>
> I find it surprising that, when only b1 really has a color string through
> B.a_color, filtering for B.a_color == None doesn't return b2 and b3, and
> filtering for B.a_color != None returns b2 and b3 at all. The latter is
> especially unintuitive.
The scalar comparison case was only rudimentally implemented. Along these
lines, the case of query(B).filter(B.a_color != 'blue') also needed to be
covered. Keeping in mind that in SQL, "x != 'somevalue'" does *not* return
rows where "x IS NULL", this will now return those B's for which an A with
non-NULL a_color is present but not equal to 'blue'.
While the results are pretty wrong especially in the != None case, this seems
strongly like an 0.9 only change, as it is very possible that applications are
relying upon the current behavior. Particularly the "x == None" case not
returning records for which an association row is not present could break an
application that isn't prepared for those rows. The "x != 'somevalue'" case
behaves very differently as well, no longer returning rows for which the
association is missing. An illustration of all these cases contrasted can be
seen in http://www.sqlalchemy.org/trac/ticket/2751. The changes are committed
in r20d1e9c3fa8ccc992079.
>
> On a similar note, is there a way to get a_re.has() or equivalent through
> only a_color? This can be important when a_re is supposed to be a private
> variable, and only the string a_color is exposed. I originally thought that
> != None would do the trick but that doesn't work, as I've shown here.
this is like a "special operation" so I also added support for an empty call to
B.a_color.has(). If you put criterion in there, it raises, because while you
can do that, it doesn't really make any sense. The difference between
B.a_color.has() and B.a_color != None is that the former will return you rows
for which an A exists, regardless of whether or not A.color is NULL.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
