CharField migration with preserve_default=False keeps the DB default on Oracle

[Václav Řehák]

I have a following migration:

migrations.AlterField(
    model_name="account",
    name="aggregation_group",
    field=models.CharField(
        blank=True,
        default="",
        max_length=40,
    ),
    preserve_default=False,
)

for a CharField which was defined as null=True before. When I run sqlmigrate with Oracle backend, I get the following SQL:

ALTER TABLE "CORE_ACCOUNT" MODIFY "AGGREGATION_GROUP" DEFAULT '';
UPDATE "CORE_ACCOUNT" SET "AGGREGATION_GROUP" = '' WHERE "AGGREGATION_GROUP" IS NULL;
ALTER TABLE "CORE_ACCOUNT" MODIFY "AGGREGATION_GROUP" DEFAULT NULL;

Note the last command sets a DB default which I don't want.

When I run the same migration on Postgres backend, I get this SQL:

ALTER TABLE "core_account" ALTER COLUMN "aggregation_group" SET DEFAULT '';
UPDATE "core_account" SET "aggregation_group" = '' WHERE "aggregation_group" IS NULL; SET CONSTRAINTS ALL IMMEDIATE;
ALTER TABLE "core_account" ALTER COLUMN "aggregation_group" SET NOT NULL;
ALTER TABLE "core_account" ALTER COLUMN "aggregation_group" DROP DEFAULT;

with the default being properly dropped.

[Sarah Boyce]

Thank you Václav
I think both Oracle and MySQL need investigating

[Chris M]

I have been able to reproduce this behavior only on Oracle.

Using Sarah's original ​PR as a guide, I made a slightly more complete ​test .

This test passes for Postgres, MySQL, and Sqlite. Oracle is the outlier for two reasons.

First, it doesn't actually set the column to NOT NULL ever. That is because of the interprets_empty_strings_as_nulls property on the ​Oracle backend . This gets ignored, and so a NOT NULL statement is never generated.

The second issue is not dropping the default, which comes down to the sql_alter_column_no_default set on the ​Oracle backend. Instead of creating a DROP DEFAULT statement, this sets the default to NULL explicitly.

I'm not very familiar with the nuances of ​empty and null values in Oracle (I just finally got the DB running locally today), so I can't really say what the correct behavior here is from Django's perspective.

[Simon Charette]

Thanks for investigating Chris!

I think this simply cannot be fixed on Oracle as once a column had a DEFAULT assigned to it, which is a pre-requisite for adding a field to a table with existing rows for example, it doesn't allow you to drop it ​per their docs

If a column has a default value, then you can use the DEFAULT clause to change the default to NULL, but you cannot remove the default value completely. That is, if a column has ever had a default value assigned to it, then the DATA_DEFAULT column of the USER_TAB_COLUMNS data dictionary view will always display either a default value or NULL.

Given NULL is the closest equivalent to not provided I think we're taking the best course of action here as for non-textual columns a DEFAULT of NULL will be fine as if the column is nullable data will be allowed to be inserted otherwise it will crash with an integrity error. This quirk just happens to conflict with the awkward way Oracle deals with empty strings considering them the same as NULL as it basically means that DEFAULT "" and DEFAULT NULL are equivalent.

Hope that explains why the issue cannot be reproduced on MySQL and why this issue should be closed as wont-fix.