Partial indexes break future migrations in sqlite

[Pēteris Caune]

How to reproduce:

1. Create a dummy "Question" model (lifted from Django's tutorial)
2. Add a partial index on one of its fields, create a migration and apply it
3. Add another field to the model, create the migration (works) and apply it (throws an error)

The error I get looks like this:

$ ./manage.py migrate
Operations to perform:
  Apply all migrations: admin, auth, contenttypes, polls, sessions
Running migrations:
  Applying polls.0003_question_hint...Traceback (most recent call last):
  File "/tmp/htemp/lib/python3.7/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/tmp/htemp/lib/python3.7/site-packages/django/db/backends/sqlite3/base.py", line 383, in execute
    return Database.Cursor.execute(self, query, params)
sqlite3.OperationalError: no such column: new__polls_question.question_text

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "./manage.py", line 21, in <module>
    main()
  File "./manage.py", line 17, in main
    execute_from_command_line(sys.argv)
  File "/tmp/htemp/lib/python3.7/site-packages/django/core/management/__init__.py", line 381, in execute_from_command_line
    utility.execute()
  File "/tmp/htemp/lib/python3.7/site-packages/django/core/management/__init__.py", line 375, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/tmp/htemp/lib/python3.7/site-packages/django/core/management/base.py", line 323, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/tmp/htemp/lib/python3.7/site-packages/django/core/management/base.py", line 364, in execute
    output = self.handle(*args, **options)
  File "/tmp/htemp/lib/python3.7/site-packages/django/core/management/base.py", line 83, in wrapped
    res = handle_func(*args, **kwargs)
  File "/tmp/htemp/lib/python3.7/site-packages/django/core/management/commands/migrate.py", line 234, in handle
    fake_initial=fake_initial,
  File "/tmp/htemp/lib/python3.7/site-packages/django/db/migrations/executor.py", line 117, in migrate
    state = self._migrate_all_forwards(state, plan, full_plan, fake=fake, fake_initial=fake_initial)
  File "/tmp/htemp/lib/python3.7/site-packages/django/db/migrations/executor.py", line 147, in _migrate_all_forwards
    state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
  File "/tmp/htemp/lib/python3.7/site-packages/django/db/migrations/executor.py", line 245, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/tmp/htemp/lib/python3.7/site-packages/django/db/migrations/migration.py", line 124, in apply
    operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
  File "/tmp/htemp/lib/python3.7/site-packages/django/db/migrations/operations/fields.py", line 112, in database_forwards
    field,
  File "/tmp/htemp/lib/python3.7/site-packages/django/db/backends/sqlite3/schema.py", line 327, in add_field
    self._remake_table(model, create_field=field)
  File "/tmp/htemp/lib/python3.7/site-packages/django/db/backends/sqlite3/schema.py", line 300, in _remake_table
    self.execute(sql)
  File "/tmp/htemp/lib/python3.7/site-packages/django/db/backends/base/schema.py", line 137, in execute
    cursor.execute(sql, params)
  File "/tmp/htemp/lib/python3.7/site-packages/django/db/backends/utils.py", line 99, in execute
    return super().execute(sql, params)
  File "/tmp/htemp/lib/python3.7/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/tmp/htemp/lib/python3.7/site-packages/django/db/backends/utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/tmp/htemp/lib/python3.7/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/tmp/htemp/lib/python3.7/site-packages/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/tmp/htemp/lib/python3.7/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/tmp/htemp/lib/python3.7/site-packages/django/db/backends/sqlite3/base.py", line 383, in execute
    return Database.Cursor.execute(self, query, params)
django.db.utils.OperationalError: no such column: new__polls_question.question_text

Note that the migration creating an partial index works. It's the *next* migration that fails.

For me, this happens only with SQLite, no errors with PostgreSQL. Also no problems with MySQL, which does not support partial indexes.
If I remove the condition clause (i.e., create a regular index instead of an partial index) then it works fine.

Here's an isolated test-case, I've added the three steps in 3 separate commits: ​https://github.com/cuu508/sqlite_partial_indexes

I patched django/db/backends/sqlite3/base.py to print SQL queries to stdout. The relevant part:

DROP TABLE "polls_question"
ALTER TABLE "new__polls_question" RENAME TO "polls_question"
CREATE INDEX "polls_nonempty_pub_date" ON "polls_question" ("pub_date") WHERE NOT ("new__polls_question"."question_text" = '')
Traceback (most recent call last):
  File "/tmp/htemp/lib/python3.7/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/tmp/htemp/lib/python3.7/site-packages/django/db/backends/sqlite3/base.py", line 420, in execute
    return Database.Cursor.execute(self, query, params)
sqlite3.OperationalError: no such column: new__polls_question.question_text

It appears to be renaming the table, and then trying to use it by its old name.
Apologies if this is already reported – couldn't find a similar ticket with a quick search for "sqlite".

[Simon Charette]

This is probably another edge cased missed by the create/rename table workaround on SQLite to emulate ALTER TABLE wrt to partial indices.

If possible we should simply not include the table alias in the partial index predicate else I suspect we'll need to defer index re-creation on the table after the rename.

I haven't reproduced but I feel confident accepting the ticket based on the detailed report and reduced reproduction case.

[Mariusz Felisiak]

Bumping to a release blocker because partial indexes are a new feature.

[Simon Charette]

The issue was effectively table aliases inclusion for columns in the WHERE clause because of the rename during _remake_table.

Pēteris, could you confirm this patch effectively addresses your issue? I've tested it out locally but schema alteration on SQLite is particularly gnarly from versions to versions.

[Pēteris Caune]

Hello Simon, tested your patch with the minimal reproduction case and also in the project where I first encountered the issue. Migrations now run without errors in both!

[Mariusz Felisiak <felisiak.mariusz@…>]

In 34decdeb:

Fixed #30754 -- Prevented inclusion of aliases in partial index conditions.

SQLite doesn't repoint table aliases in partial index conditions on table
rename which breaks the documented table alteration procedure.

Thanks Pēteris Caune for the report.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 964dd4f:

[2.2.x] Fixed #30754 -- Prevented inclusion of aliases in partial index conditions.

SQLite doesn't repoint table aliases in partial index conditions on table
rename which breaks the documented table alteration procedure.

Thanks Pēteris Caune for the report.

Backport of 34decdebf157b6f05836009cc1967f74ee541fdf from master