Can't call update() when using CTE manager

[dimagi]

Can't call update() when using CTE manager

[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 (#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 #71