UUID4() function on Oracle persists data in uppercase hex but queries in lowercase hex

[Jacob Walls]

field_defaults.tests.DefaultTests.test_foreign_key_to_parent_with_expression_pk(), fails on Oracle 26ai (i.e. 23.26.2.0):

django.db.utils.IntegrityError: ORA-02291: integrity constraint (DJANGO_TESTS_DEFAULT_22.FIELD_DEF_PARENT_ID_42B58311_F) violated - parent key not found
Help: https://docs.oracle.com/error-help/db/ora-02291/

The queries in this test are:

1. INSERT INTO "FIELD_DEFAULTS_DBDEFAULTSF0214" ("UUID") VALUES (UUID()) RETURNING "FIELD_DEFAULTS_DBDEFAULTSF0214"."UUID" INTO <django.db.backends.oracle.utils.BoundVar object at 0xffff7d1da360>
2. INSERT INTO "FIELD_DEFAULTS_DBDEFAULTSF22F2" ("PARENT_ID") VALUES (b41dd9331e764f988fc47e139ef0251d) RETURNING "FIELD_DEFAULTS_DBDEFAULTSF22F2"."ID" INTO <django.db.backends.oracle.utils.BoundVar object at 0xffff7d1da360>

The UUID value returned by query 1 differs in case from the value bound in query 2. Check by skipping Django's deserialization and reading it from a raw cursor:

    def test_foreign_key_to_parent_with_expression_pk(self):
        parent = DBDefaultsFunctionPK(pk=UUID4())
        obj = DBDefaultsFunctionFK(parent=parent)
        parent.save()
        with connection.cursor() as cursor:
            cursor.execute('SELECT "UUID" FROM "FIELD_DEFAULTS_DBDEFAULTSF0214"')
            print(cursor.fetchall())
        obj.save()
        self.assertEqual(obj.parent_id, parent.pk)

[('B41DD9331E764F988FC47E139EF0251D',)]

The Oracle ​docs show the uppercase form being persisted. My understanding is that the FK fails integrity when a lowercase value is used in the second query, via Django/Python-side normalization, and fails a case-sensitive check on the varchar column in the db.

Bug in accceec9493d08e19d59fa1a59f69c0fdf23bb13.

---

The test passes after adjusting UUID4.as_oracle() to lowercase like this. (RAWTOHEX makes explicit the implicit conversion done today when inserting raw UUID into varchar columns.)

django/db/models/functions/uuid.py

@@ -32 +32 @@
             raise NotSupportedError(
                 "UUID4 requires Oracle version 23ai/26ai (23.9) or later."
             )
-        return self.as_sql(compiler, connection, function="UUID", **extra_context)
+        return self.as_sql(
+            compiler,
+            connection,
+            function="UUID",
+            template=f"LOWER(RAWTOHEX({self.template}))",
+            **extra_context,
+        )
 
 
 class UUID7(Func):

I presume if we go through with this we will need to write a release note advising Oracle users already on Django 6.1 to migrate their data created via UUID4 to lowercase (including foreign keys).

[Mariusz Felisiak]

In UUIDField.get_db_prep_value() we call to_python() which normalizes values using uuid.UUID(). We could follow you suggestion or return value.hex.upper() for Oracle in UUIDField.get_db_prep_value(). The second should be "more" backward compatible.

[Jacob Walls]

That's a much better approach. I'll explore the various get_db_prep_* methods to make sure I understand the differences.

Loïc, this is the remaining failure on Oracle 26ai on Test Pilot.

[Jacob Walls]

Loïc wrote in an email:

My understanding is that when we deal with UUIDs in Oracle, RAW(16) is usually the data type we target.
...
Now are you saying the Python driver at some point converts it into a varchar2 which makes it lowercase? (just to understand fully the issue).

Django prefers the VARCHAR2(32) format for UUIDField() (​docs), which creates unfriendliness for RAW(16)'s created by Oracle either via UUID4() (recent) or SYS_GUID(), discussed in #24012. So it's not an issue in the driver; it's an issue with an asymmetry between Django's default=uuid.uuid4, which will persist lowercase, and Django's db_default=UUID4(), which will persist uppercase.

Replying to Mariusz Felisiak:

In UUIDField.get_db_prep_value() we call to_python() which normalizes values using uuid.UUID(). We could follow you suggestion or return value.hex.upper() for Oracle in UUIDField.get_db_prep_value(). The second should be "more" backward compatible.

My concern about returning value.hex.upper() is that it will break projects using Python-generated values, i.e. default=uuid.uuid4, which persist lowercase.

I feel like the more compatible option might be to lowercase inside UUID4() like originally suggested. How does that sound?

[Jacob Walls]

​PR

[Jacob Walls <jacobtylerwalls@…>]

In 3db0898e:

Fixed #37376 -- Made UUID4() persist data in lowercase on Oracle.

On Oracle, UUID4() produces a RAW(16) value. Because Django uses
VARCHAR2(32) for UUIDField(), Oracle implicitly ran RAWTOHEX() when
inserting, producing uppercased values. Lookups then failed as all other
value preparation by UUIDField produces lowercased values.

Making get_db_prep_value() normalize values to uppercase on Oracle was
rejected, as it would break too many existing uses without the new UUID4()
function.

Bug in accceec9493d08e19d59fa1a59f69c0fdf23bb13.

[Jacob Walls <jacobtylerwalls@…>]

In d21284a6:

[6.1.x] Fixed #37376 -- Made UUID4() persist data in lowercase on Oracle.

On Oracle, UUID4() produces a RAW(16) value. Because Django uses
VARCHAR2(32) for UUIDField(), Oracle implicitly ran RAWTOHEX() when
inserting, producing uppercased values. Lookups then failed as all other
value preparation by UUIDField produces lowercased values.

Making get_db_prep_value() normalize values to uppercase on Oracle was
rejected, as it would break too many existing uses without the new UUID4()
function.

Bug in accceec9493d08e19d59fa1a59f69c0fdf23bb13.

Backport of 3db0898eee4061d44de5052953ef0fe3afb5951f from main.