Inverted F expression (negation)

[Andrei Antoukh]

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.

[Andrei Antoukh]

I forgot this:

The result of the sql would look like:

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

(tested on sqlite3, postgresql and mysql)

[Luke Plant]

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

[Andrei Antoukh]

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

[Nate Bragg]

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.

[Andrei Antoukh]

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).

[Łukasz Rekucki]

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!).

[Łukasz Rekucki]

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'))

[Nate Bragg]

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.

[Nate Bragg]

Updated docs and added a test

[Łukasz Rekucki]

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.

[Zbigniew Siciarz]

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".

[Zbigniew Siciarz]

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.

[Aymeric Augustin]

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

[Tim Graham]

Duplicate of #16211