Can't call update() when using CTE manager

[kingbuzzman]

>>> type(qs)
<class 'django_cte.cte.CTEQuerySet'>
>>> str(qs.query)
'SELECT * FROM model INNER JOIN model2 ON (model."obj_id" = "model2"."id") WHERE ( "model"."id" IN (1, 2, 3, 5, 11, 100)) ORDER BY "notification_inbox"."sent_timestamp" DESC'
>>> qs.update(field1='Hello')
*** django.db.utils.ProgrammingError: syntax error at or near ")"
LINE 1: ... SET "field1" = 'Hello' WHERE "model"."id" IN ()

Looking at the postgres logs, im able to see that the query being ran is: UPDATE "model" SET "field1" = 'Hello' WHERE "model"."id" IN ()

If i swap out the objects = CTEManager() for a objects = models.Manager() everything works as expected.

As a temporary solution ive keep the objects = models.Manager() and added a cte_objects = CTEManager() -- while not ideal, it works.

[ryanhiebert]

I'm seeing this issue or a variant of it, but I've needed to use the CTEQuerySet, so using the regular manager wasn't really an option, but I was able to do what I needed in my case by updating directly with filtering only on the ids from values_list.

[Azd325]

I'm running also in this issue.

product.py:1900: in update_products
    products_with.update(has_product_in_same_category=True)
../../.pyenv/versions/3.7.2/envs/project/lib/python3.7/site-packages/django/db/models/query.py:741: in update
    rows = query.get_compiler(self.db).execute_sql(CURSOR)
../../.pyenv/versions/3.7.2/envs/project/lib/python3.7/site-packages/django/db/models/sql/compiler.py:1471: in execute_sql
    cursor = super().execute_sql(result_type)
../../.pyenv/versions/3.7.2/envs/project/lib/python3.7/site-packages/django/db/models/sql/compiler.py:1142: in execute_sql
    cursor.execute(sql, params)
../../.pyenv/versions/3.7.2/envs/project/lib/python3.7/site-packages/django/db/backends/utils.py:67: in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
../../.pyenv/versions/3.7.2/envs/project/lib/python3.7/site-packages/django/db/backends/utils.py:76: in _execute_with_wrappers
    return executor(sql, params, many, context)
../../.pyenv/versions/3.7.2/envs/project/lib/python3.7/site-packages/django/db/backends/utils.py:84: in _execute
    return self.cursor.execute(sql, params)
../../.pyenv/versions/3.7.2/envs/project/lib/python3.7/site-packages/django/db/utils.py:89: in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value

[timworx]

I'm running into this issue currently.

I can't replace the objects manager without this blowing up other parts of the app that use .update on the queryset.

I think it's winding up on the query here: https://github.com/django/django/blob/main/django/db/models/sql/compiler.py#L1646

[jnns]

After making the test suite run in pytest instead of nose (https://github.com/dimagi/django-cte/pull/63), I wrote the following test to reproduce the behaviour of this issue:

@pytest.mark.skipif(
    connection.vendor == "sqlite",
    reason="SQLite doesn't fail on 'WHERE field IN ()'"
)
def test_regular_update_query(self):
    """Regression test for https://github.com/dimagi/django-cte/issues/9"""
    Order.objects.filter(region="mars").update(amount=100)  # Works.
    Order.objects.filter(region__parent="mars").update(amount=100)  # Doesn't work.

While the first queryset correctly produces the following query …

UPDATE "orders" SET "amount" = 100 WHERE "orders"."region_id" = 'mars'

… the latter results in:

UPDATE "orders" SET "amount" = %s WHERE "orders"."id" IN ()

I tried to pinpoint the issue and ended up with the same discoveries as @timworx: CTEQueryUpdateCompiler.pre_sql_setup() has a WHERE clause like this

<WhereNode: (AND: RelatedExact(Col(U1, tests.Region.parent), 'mars'))>

until self.query.clear_where() is called. It is then rebuilt with the broken WHERE clause when self.query.add_filter("pk__in", query) is called:

<WhereNode: (AND: In(Col(U0, tests.Order.id), <django_cte.query.CTEUpdateQuery object at 0x7ff13e3e9e10>))>

[millerdev]

Fixed by https://github.com/dimagi/django-cte/pull/71