#35018 closed Bug (fixed)
GeneratedField crashes on Oracle 19c when output_field is BooleanField.
Reported by: | Václav Řehák | Owned by: | Mariusz Felisiak |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 5.0 |
Severity: | Release blocker | Keywords: | oracle |
Cc: | Lily Foote, Jeremy Nauta | Triage Stage: | Accepted |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
I'm not sure this is actual bug or I am doing something wrong (please point me to the doc if I missed one).
I am trying GeneratedField
on Django 5.0 in a project running on Oracle Database 19c Enterprise Edition, Version 19.18.0.0.0. My real use case is bit more complicated so made up a simpler one - I have ts_password_changed
as DateTimeField(blank=True, null=True)
and I'm trying to add generated boolean field:
is_password_changed = GeneratedField( expression=ExpressionWrapper(Q(ts_password_changed__isnull=False), output_field=models.BooleanField()), output_field=models.BooleanField(), db_persist=False )
The generated migration SQL looks like this:
ALTER TABLE "CORE_USER" ADD "IS_PASSWORD_CHANGED" NUMBER(1) GENERATED ALWAYS AS ("TS_PASSWORD_CHANGED" IS NOT NULL) VIRTUAL;
and Oracle refuses it with
ORA-54016: Invalid column expression was specified, Position 81
However, it works when I modify the query to
ALTER TABLE "CORE_USER" ADD "IS_PASSWORD_CHANGED" NUMBER(1) GENERATED ALWAYS AS (CASE WHEN "TS_PASSWORD_CHANGED" IS NOT NULL THEN 1 ELSE 0 END) VIRTUAL;
which is probably what Django should be generating.
Change History (8)
follow-up: 2 comment:1 by , 11 months ago
Cc: | added |
---|---|
Severity: | Normal → Release blocker |
Summary: | Invalid SQL for GeneratedField on Oracle → GeneratedField crashes on Oracle 19c when output_field is BooleanField. |
Triage Stage: | Unreviewed → Accepted |
comment:2 by , 11 months ago
Replying to Mariusz Felisiak:
Unexpectedly, it works fine on Oracle 23c without
CASE ...
.
Oracle 23c introduced BOOLEAN
data type in SQL 😱 I never expected this to finally happen.
follow-up: 4 comment:3 by , 11 months ago
Oracle 23c introduced BOOLEAN data type in SQL 😱 I never expected this to finally happen.
Only a few more years of CASE/WHEN
wrapping shenanigans I guess 😅.
I support we'll want to branch of compiler.connection.features.supports_boolean_expr_in_select_clause
here I guess?
comment:4 by , 11 months ago
Replying to Simon Charette:
I support we'll want to branch of
compiler.connection.features.supports_boolean_expr_in_select_clause
here I guess?
Yes, working on this. I need to investigate some issues around binding parameters, it was not enough to switch the flag.
comment:5 by , 11 months ago
Owner: | changed from | to
---|---|
Status: | new → assigned |
Thanks for the report. Unexpectedly, it works fine on Oracle 23c without
CASE ...
.Regression in f333e3513e8bdf5ffeb6eeb63021c230082e6f95.