Opened 13 years ago

Closed 9 years ago

#17186 closed New feature (duplicate)

Inverted F expression (negation)

Reported by: Andrei Antoukh Owned by: nobody
Component: Database layer (models, ORM) Version: dev
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 Andrei Antoukh 13 years ago.
invert.f-2.patch (2.5 KB ) - added by Andrei Antoukh 13 years ago.
17186-3.patch (3.0 KB ) - added by Nate Bragg 13 years ago.
17186-4.patch (4.3 KB ) - added by Nate Bragg 13 years ago.
Updated docs and added a test

Download all attachments as: .zip

Change History (17)

by Andrei Antoukh, 13 years ago

Attachment: invert.f.patch added

comment:1 by Andrei Antoukh, 13 years ago

Has patch: set

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 by Luke Plant, 13 years ago

Component: UncategorizedDatabase layer (models, ORM)
Needs tests: set
Triage Stage: UnreviewedAccepted

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

by Andrei Antoukh, 13 years ago

Attachment: invert.f-2.patch added

comment:3 by Andrei Antoukh, 13 years ago

Needs tests: unset

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

comment:4 by Nate Bragg, 13 years ago

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.

by Nate Bragg, 13 years ago

Attachment: 17186-3.patch added

comment:5 by Andrei Antoukh, 13 years ago

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 by Łukasz Rekucki, 13 years ago

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 by Łukasz Rekucki, 13 years ago

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 by Nate Bragg, 13 years ago

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.

by Nate Bragg, 13 years ago

Attachment: 17186-4.patch added

Updated docs and added a test

in reply to:  8 comment:9 by Łukasz Rekucki, 13 years ago

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 by Zbigniew Siciarz, 13 years ago

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 by Zbigniew Siciarz, 13 years ago

Triage Stage: AcceptedDesign 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 by Aymeric Augustin, 12 years ago

Triage Stage: Design decision neededAccepted

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

comment:13 by Tim Graham, 9 years ago

Resolution: duplicate
Status: newclosed

Duplicate of #16211

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