#34946 closed Bug (fixed)
Adding a field with default and db_default drops database level DEFAULT
Reported by: | Simon Charette | Owned by: | Simon Charette |
---|---|---|---|
Component: | Migrations | Version: | 5.0 |
Severity: | Release blocker | Keywords: | sql, default, migrations |
Cc: | Paolo Melchiorre | Triage Stage: | Accepted |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
The documentation for Field.db_default
states
If both
db_default
andField.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
diff --git a/tests/migrations/test_operations.py b/tests/migrations/test_operations.py index d58da8b7ac..57a9086c19 100644
a b def test_add_field_both_defaults(self): 1692 1692 field = new_state.models[app_label, "pony"].fields["height"] 1693 1693 self.assertEqual(field.default, 3) 1694 1694 self.assertEqual(field.db_default, Value(4)) 1695 project_state.apps.get_model(app_label, "pony").objects.create(weight=4) 1695 pre_pony_pk = ( 1696 project_state.apps.get_model(app_label, "pony").objects.create(weight=4).pk 1697 ) 1696 1698 self.assertColumnNotExists(table_name, "height") 1697 1699 # Add field. 1698 1700 with connection.schema_editor() as editor: 1699 1701 operation.database_forwards(app_label, editor, project_state, new_state) 1700 1702 self.assertColumnExists(table_name, "height") 1703 post_pony_pk = ( 1704 project_state.apps.get_model(app_label, "pony").objects.create(weight=10).pk 1705 ) 1701 1706 new_model = new_state.apps.get_model(app_label, "pony") 1702 old_pony = new_model.objects.get() 1703 self.assertEqual(old_pony.height, 4) 1707 pre_pony = new_model.objects.get(pk=pre_pony_pk) 1708 self.assertEqual(pre_pony.height, 4) 1709 post_pony = new_model.objects.get(pk=post_pony_pk) 1710 self.assertEqual(post_pony.height, 4) 1704 1711 new_pony = new_model.objects.create(weight=5) 1705 1712 if not connection.features.can_return_columns_from_insert: 1706 1713 new_pony.refresh_from_db() -
tests/schema/tests.py
diff --git a/tests/schema/tests.py b/tests/schema/tests.py index 20a00e29f7..80fdead190 100644
a b class Meta: 2257 2257 columns = self.column_classes(AuthorDbDefault) 2258 2258 self.assertEqual(columns["renamed_year"][1].default, "1985") 2259 2259 2260 @isolate_apps("schema") 2261 def test_add_field_both_defaults_preserves_db_default(self): 2262 class Author(Model): 2263 class Meta: 2264 app_label = "schema" 2265 2266 #self.isolated_local_models = [Author] 2267 with connection.schema_editor() as editor: 2268 editor.create_model(Author) 2269 2270 field = IntegerField(default=1985, db_default=1988) 2271 field.set_attributes_from_name("birth_year") 2272 field.model = Author 2273 with connection.schema_editor() as editor: 2274 editor.add_field(Author, field) 2275 columns = self.column_classes(Author) 2276 self.assertEqual(columns["birth_year"][1].default, "1988") 2277 2260 2278 @skipUnlessDBFeature( 2261 2279 "supports_column_check_constraints", "can_introspect_check_constraints" 2262 2280 )
Change History (7)
comment:1 by , 13 months ago
Has patch: | set |
---|
comment:2 by , 13 months ago
Owner: | changed from | to
---|---|
Triage Stage: | Unreviewed → Accepted |
comment:3 by , 13 months ago
Cc: | added |
---|---|
Keywords: | sql default migrations added |
comment:4 by , 13 months ago
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!
comment:5 by , 13 months ago
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.
Thanks!