Adding a field with default and db_default drops database level DEFAULT

[Simon Charette]

​The documentation for Field.db_default states

If both db_default and Field.default are set, default will take precedence when creating instances in Python code. db_default will still be set at the database level and will be used when inserting rows outside of the ORM or when adding a new field in a migration.

However adding a field with both results in the following SQL

ALTER TABLE "foo" ADD COLUMN "bar" integer DEFAULT 42 NOT NULL;
ALTER TABLE "foo" ALTER COLUMN "bar" DROP DEFAULT;

The DEFAULT should never be dropped if the field has a db_default defined.

The following patch reproduces and demonstrate ​why the test that intended to cover this case didn't catch it.

tests/migrations/test_operations.py

@@ -1692 +1692 @@
         field = new_state.models[app_label, "pony"].fields["height"]
         self.assertEqual(field.default, 3)
         self.assertEqual(field.db_default, Value(4))
-        project_state.apps.get_model(app_label, "pony").objects.create(weight=4)
+        pre_pony_pk = (
+            project_state.apps.get_model(app_label, "pony").objects.create(weight=4).pk
+        )
         self.assertColumnNotExists(table_name, "height")
         # Add field.
         with connection.schema_editor() as editor:
             operation.database_forwards(app_label, editor, project_state, new_state)
         self.assertColumnExists(table_name, "height")
+        post_pony_pk = (
+            project_state.apps.get_model(app_label, "pony").objects.create(weight=10).pk
+        )
         new_model = new_state.apps.get_model(app_label, "pony")
-        old_pony = new_model.objects.get()
-        self.assertEqual(old_pony.height, 4)
+        pre_pony = new_model.objects.get(pk=pre_pony_pk)
+        self.assertEqual(pre_pony.height, 4)
+        post_pony = new_model.objects.get(pk=post_pony_pk)
+        self.assertEqual(post_pony.height, 4)
         new_pony = new_model.objects.create(weight=5)
         if not connection.features.can_return_columns_from_insert:
             new_pony.refresh_from_db()

tests/schema/tests.py

@@ -2257 +2257 @@
         columns = self.column_classes(AuthorDbDefault)
         self.assertEqual(columns["renamed_year"][1].default, "1985")
 
+    @isolate_apps("schema")
+    def test_add_field_both_defaults_preserves_db_default(self):
+        class Author(Model):
+            class Meta:
+                app_label = "schema"
+
+        #self.isolated_local_models = [Author]
+        with connection.schema_editor() as editor:
+            editor.create_model(Author)
+
+        field = IntegerField(default=1985, db_default=1988)
+        field.set_attributes_from_name("birth_year")
+        field.model = Author
+        with connection.schema_editor() as editor:
+            editor.add_field(Author, field)
+        columns = self.column_classes(Author)
+        self.assertEqual(columns["birth_year"][1].default, "1988")
+
     @skipUnlessDBFeature(
         "supports_column_check_constraints", "can_introspect_check_constraints"
     )

[Mariusz Felisiak]

Thanks!

[Natalia Bidart]

Replying to Simon Charette:

However adding a field with both results in the following SQL

ALTER TABLE "foo" ADD COLUMN "bar" integer DEFAULT 42 NOT NULL;
ALTER TABLE "foo" ALTER COLUMN "bar" DROP DEFAULT;

Thank you Simon for the report. I've been trying to reproduce the issue exactly as described to progress with the PR review. I've created a test app with a Foo model with a dummy name field, and then I've added a bar integer field with both default and db_default. I can't get the SQL that you reported, so far I'm only obtaining a SQL that would create an intermediary table. I think I'm missing something to craft the proper reproducer:

BEGIN;
--
-- Add field bar to foo
--
CREATE TABLE "new__ticket_34946_foo" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "bar" integer DEFAULT 42 NOT NULL, "name" varchar(10) DEFAULT 'dbdef' NOT NULL);
INSERT INTO "new__ticket_34946_foo" ("id", "name") SELECT "id", "name" FROM "ticket_34946_foo";
DROP TABLE "ticket_34946_foo";
ALTER TABLE "new__ticket_34946_foo" RENAME TO "ticket_34946_foo";
COMMIT;

I've also tried this with a Foo model with no other field than bar, same result. Could you help me understand the steps to reproduce the error as reported? Thank you!

[Simon Charette]

Hello Natalia!

SQLite doesn't support alteration such as DROP DEFAULT so it's not affected by this issue, you should be able to reproduce with Postgres or MySQL.

[GitHub <noreply@…>]

In 8a28e983:

Fixed #34946 -- Preserved db_default on combined default field addition.

Regression in 7414704e88d73dafbcfbb85f9bc54cb6111439d3.

[Mariusz Felisiak <felisiak.mariusz@…>]

In 0265eaa5:

[5.0.x] Fixed #34946 -- Preserved db_default on combined default field addition.

Regression in 7414704e88d73dafbcfbb85f9bc54cb6111439d3.
Backport of 8a28e983df091d94eaba77cb82fbe3ef60a80799 from main