Opened 3 years ago

Last modified 2 years ago

#17186 new New feature

Inverted F expression (negation)

Reported by: niwi Owned by: nobody
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: yes Needs documentation: yes
Needs tests: yes Patch needs improvement: yes
Easy pickings: no UI/UX: no

Description

By default, the expression F django, can not make something like:

UPDATE "testmodel" SET "done" = NOT "testmodel"."done";

The idea is to make updates or queries like this:

TestModel.objects.update(done=~F('done'))

Taking as a reference, this model:

class TestModel(models.Model):
    done = models.BooleanField(default=False)

Attached is a patch that works for me, but not if it's the best way to do it.

Attachments (4)

invert.f.patch (1.1 KB) - added by niwi 3 years ago.
invert.f-2.patch (2.5 KB) - added by niwi 3 years ago.
17186-3.patch (3.0 KB) - added by nate_b 3 years ago.
17186-4.patch (4.3 KB) - added by nate_b 3 years ago.
Updated docs and added a test

Download all attachments as: .zip

Change History (16)

Changed 3 years ago by niwi

comment:1 Changed 3 years ago by niwi

  • Has patch set
  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

I forgot this:

The result of the sql would look like:

UPDATE "testmodel" SET "done" = NOT "testmodel"."done";

(tested on sqlite3, postgresql and mysql)

comment:2 Changed 3 years ago by lukeplant

  • Component changed from Uncategorized to Database layer (models, ORM)
  • Needs tests set
  • Triage Stage changed from Unreviewed to Accepted

Seems reasonable, and I can't think of an alternative use of negating an F object.

Changed 3 years ago by niwi

comment:3 Changed 3 years ago by niwi

  • Needs tests unset

I modified a little test, to cover this modification. Attached the new patch.

comment:4 Changed 3 years ago by nate_b

There are several problems with this patch.

For example, you should be able to negate compound expressions:

>>> Demo.objects.filter(a_field=~(F('a_field')+1))
Traceback (most recent call last):
  File "<console>", line 1, in <module>
TypeError: bad operand type for unary ~: 'ExpressionNode'

Secondly, there is no indication of negation in the string representation, like there is for other operators:

>>> print ~F('field')
(DEFAULT: )
>>> print F('field') + F('other')
(+: (DEFAULT: ), (DEFAULT: ))

Thirdly, tree.Node already supplies a negate() function and a negated field. Why not use those instead?

I'm not entirely pleased with my alternative as it still doesn't make use of connector, but it solves these listed problems.

Changed 3 years ago by nate_b

comment:5 Changed 3 years ago by niwi

Your solution seems to me pretty good. I had not thought of fields that are not Boolean. In fact it was an idea that surely someone could have implemented better than me. (just this case).

comment:6 Changed 3 years ago by lrekucki

  • Needs documentation set
  • Needs tests set
  • Patch needs improvement set

As a new feature this needs documentation. Also:

  • What's the use case for negation on non-boolean values? How does this behave on different DBs (the tests should check that).
  • No tests for compounds statements (should they be boolean only?).
  • Why ~F() instead of more Pythonic not F(). After all ~True is -2 not False. You could use not for boolean expressions and ~ for bit negation on fields (which might be useful too!).

comment:7 Changed 3 years ago by lrekucki

One more thing worth documenting, due to difference in None and NULL semantics:

class A(Model):
  f = NullBooleanField()

for m in A.objects.all():
  m.f = not m.f; m.save()  # all NULL values are now True 

# while this will leave NULL values unchanged as NOT NULL is NULL
A.objects.update(f=not F('f'))

comment:8 follow-up: Changed 3 years ago by nate_b

Regarding use case for negation on non-boolean values: That's a good question; I'm not sure that there is one. I provided that only as a stupid example of lack of composability (at the db level negation on non-boolean values can work, but that doesn't mean it is useful). If you can propose a test, I would be glad to include it. Altogether, though, that does raise a good question - so far, all F() objects are generally composable across all operations, and I would not want to break that by adding another operation. Not all computations in general are composable, though; should F() object composability reflect that? Now my head hurts.

Regarding "Why ~F() instead of not F()": there are two reasons. One: To match with Q objects; Two, and more importantly: python itself does not provide a __not__() method for object instances. So yes, you are absolutely right, but I don't think it can be done.

As to your other points, well taken. I hope my updated patch is an improvement.

Changed 3 years ago by nate_b

Updated docs and added a test

comment:9 in reply to: ↑ 8 Changed 3 years ago by lrekucki

Replying to nate_b:

Regarding "Why ~F() instead of not F()": there are two reasons. One: To match with Q objects; Two, and more importantly: python itself does not provide a __not__() method for object instances. So yes, you are absolutely right, but I don't think it can be done.

Ok, now I feel stupid ;)

As to your other points, well taken. I hope my updated patch is an improvement.

The patch looks good. Now we just need to work out the non-boolean cases somehow.

comment:10 Changed 3 years ago by zsiciarz

Apparently various database engines work differently regarding negation of non-boolean values. For example on Postgres each of the following queries raises an error:

SELECT NOT 5;
SELECT NOT "5";
SELECT NOT "5abc";

while on SQLite it happily returns 0. SQLite casts the expression to a NUMERIC value when used in a boolean context, so in each case it is "truthy".


comment:11 Changed 3 years ago by zsiciarz

  • Triage Stage changed from Accepted to Design decision needed

I guess it's a DDN. Generally it is even possible to do something like that in SQLite:

Company.objects.update(name=F("name") * F("name"))

And the name field after that is "0". This is rather a wider case, whether the ORM should allow silly expressions like that, and not only negation of non-booleans. It is completely DBMS-dependent and most "true" DB engines will barf immediately.

comment:12 Changed 2 years ago by aaugustin

  • Triage Stage changed from Design decision needed to Accepted

This feature shouldn't be expected to give meaningful results on anything other than a boolean value.

Note: See TracTickets for help on using tickets.
Back to Top