id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
37376	UUID4() function on Oracle persists data in uppercase hex but queries in lowercase hex	Jacob Walls	Jacob Walls	"`field_defaults.tests.DefaultTests.test_foreign_key_to_parent_with_expression_pk()`, fails on Oracle 26ai (i.e. 23.26.2.0):

{{{#!py
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:
{{{#!sql
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:

{{{#!py
    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 [https://docs.oracle.com/en/database/oracle/oracle-database/26/sqlrf/uuid.html#:~:text=848DC57A12AA4F81BFB42EA509879467 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.)

{{{#!diff
diff --git a/django/db/models/functions/uuid.py b/django/db/models/functions/uuid.py
index 7059798ff3..17754a48c1 100644
--- a/django/db/models/functions/uuid.py
+++ b/django/db/models/functions/uuid.py
@@ -32,7 +32,13 @@ class UUID4(Func):
             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)."	Bug	assigned	Database layer (models, ORM)	6.1	Release blocker		UUID	Lily Mariusz Felisiak	Unreviewed	0	0	0	0	0	0