Queries with duplicate params incorrectly logged for Oracle

[Ahmet Kucuk]

When you try to update a value using Coalesce, Oracle compiler decides to filter by 0 rather than passed filter id 1.

TestTable.objects.filter(id=1).update(test_count=Coalesce(F('test_count'), Value(0), output_field=IntegerField()) + 0)

UPDATE "testtable" SET "test_count" = (COALESCE("testtable"."test_count", 0) + 1) WHERE "testtable"."id" = 0

If you pass more fields, it is still evaluated as 0.

TestTable.objects.filter(id=1).update(test_count=Coalesce(F('test_count'), Value(0), output_field=IntegerField()) + 0, test_count2=Coalesce(F('test_count2'), Value(0), output_field=IntegerField()) + 10)

[David Sanders]

Thanks for reporting that ♥️

I was able to reproduce on latest main.

[David Sanders]

It looks like this bug occurs when the expression Value(x) + x is supplied causing it to attempt to filter by x. If the expression uses different values like Value(x) + y then it filters correctly.

Confirmed this does not occur on Postgres or SQLite.

>>> Test.objects.filter(pk=1).update(test=Value(0) + 0)
UPDATE "TICKET_34103_TEST"
   SET "TEST" = (0 + 0)
 WHERE "TICKET_34103_TEST"."ID" = 0

>>> Test.objects.filter(pk=1).update(test=Value(42) + 42)
UPDATE "TICKET_34103_TEST"
   SET "TEST" = (42 + 42)
 WHERE "TICKET_34103_TEST"."ID" = 42

>>> Test.objects.filter(pk=1).update(test=Value(1) + 2)
UPDATE "TICKET_34103_TEST"
   SET "TEST" = (1 + 2)
 WHERE "TICKET_34103_TEST"."ID" = 1

[David Sanders]

After looking into the issue further, the problem isn't with the query executed - only the query that's logged:

Rerunning the above with logging enabled via LOGGING we can see the params are correct but the query logged is not:

>>> foo = Test.objects.create(test=10)
(0.001) INSERT INTO "TICKET_34103_TEST" ("TEST") VALUES (10) RETURNING "TICKET_34103_TEST"."ID" INTO <django.db.backends.oracle.utils.InsertVar object at 0x107a7e710>; args=(10, <django.db.backends.oracle.utils.InsertVar object at 0x107a7e710>); alias=default

>>> Test.objects.filter(pk=foo.pk).update(test=Value(42) + 42)
(0.002) UPDATE "TICKET_34103_TEST" SET "TEST" = (42 + 42) WHERE "TICKET_34103_TEST"."ID" = 42; args=(42, 42, 4); alias=default

>>> foo.refresh_from_db()
(log omitted)

>>> foo.test
84

[Mariusz Felisiak <felisiak.mariusz@…>]

In 64b3c41:

Fixed #34103 -- Fixed logging SQL queries with duplicate parameters on Oracle.