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).