[Django] #31198: CheckConstraints with F objects don't apply on SQLite

[Django]

#31198: CheckConstraints with F objects don't apply on SQLite
-------------------------------------+-------------------------------------
               Reporter:  Adam       |          Owner:  nobody
  (Chainz) Johnson                   |
                   Type:  Bug        |         Status:  new
              Component:  Database   |        Version:  master
  layer (models, ORM)                |
               Severity:  Normal     |       Keywords:
           Triage Stage:             |      Has patch:  0
  Unreviewed                         |
    Needs documentation:  0          |    Needs tests:  0
Patch needs improvement:  0          |  Easy pickings:  0
                  UI/UX:  0          |
-------------------------------------+-------------------------------------
 Take this model with CheckConstraint:

 {{{
 class Book(models.Model):
     percent_read = models.PositiveIntegerField()
     percent_unread = models.PositiveIntegerField()
     percent_ignored = models.PositiveIntegerField()

     def __str__(self):
         return f"{self.id} - {self.percent_read}% read"

     class Meta:
         constraints = [
             models.CheckConstraint(
                 check=models.Q(
                     percent_read=(
                         100
                         - models.F("percent_unread")
                         - models.F("percent_ignored")
                     )
                 ),
                 name="percentages_sum_100",
             )
         ]
 }}}

 Applying the migration to create the constraint works on MariaDB but fails
 on SQLite.

 The `sqlmigrate` output on SQLite shows the F objects get resolved to the
 full table name, which is what breaks during renaming phase in the
 migration:

 {{{
 $ python manage.py sqlmigrate core 0002
 BEGIN;
 --
 -- Create constraint percentages_sum_100 on model book
 --
 CREATE TABLE "new__core_book" ("id" integer NOT NULL PRIMARY KEY
 AUTOINCREMENT, "percent_read" integer unsigned NOT NULL CHECK
 ("percent_read" >= 0), "percent_unread" integer unsigned NOT NULL CHECK
 ("percent_unread" >= 0), "percent_ignored" integer unsigned NOT NULL CHECK
 ("percent_ignored" >= 0), CONSTRAINT "percentages_sum_100" CHECK
 ("percent_read" = ((100 - "new__core_book"."percent_unread") -
 "new__core_book"."percent_ignored")));
 INSERT INTO "new__core_book" ("id", "percent_read", "percent_unread",
 "percent_ignored") SELECT "id", "percent_read", "percent_unread",
 "percent_ignored" FROM "core_book";
 DROP TABLE "core_book";
 ALTER TABLE "new__core_book" RENAME TO "core_book";
 COMMIT;
 $ python manage.py migrate
 ...
 django.db.utils.DatabaseError: malformed database schema (core_book) - no
 such column: new__core_book.percent_unread
 }}}

 Full repo for reproduction: https://github.com/adamchainz/django-issue-
 check-constraint-reference

-- 
Ticket URL: <https://code.djangoproject.com/ticket/31198>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

-- 
You received this message because you are subscribed to the Google Groups 
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-updates+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-updates/053.e2c93dcc0804a2e5aee69e391be112cf%40djangoproject.com.