Opened 12 months ago

Closed 12 months ago

Last modified 12 months ago

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

comment:1 by Mariusz Felisiak, 12 months ago

Cc: Lily Foote Jeremy Nauta added
Severity: NormalRelease blocker
Summary: Invalid SQL for GeneratedField on OracleGeneratedField crashes on Oracle 19c when output_field is BooleanField.
Triage Stage: UnreviewedAccepted

Thanks for the report. Unexpectedly, it works fine on Oracle 23c without CASE ....

Regression in f333e3513e8bdf5ffeb6eeb63021c230082e6f95.

in reply to:  1 comment:2 by Mariusz Felisiak, 12 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.

comment:3 by Simon Charette, 12 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?

in reply to:  3 comment:4 by Mariusz Felisiak, 12 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 Mariusz Felisiak, 12 months ago

Owner: changed from nobody to Mariusz Felisiak
Status: newassigned

comment:6 by Mariusz Felisiak, 12 months ago

Has patch: set

comment:7 by GitHub <noreply@…>, 12 months ago

Resolution: fixed
Status: assignedclosed

In fcf95e5:

Fixed #35018 -- Fixed migrations crash on GeneratedField with BooleanField as output_field on Oracle < 23c.

Thanks Václav Řehák for the report.

Regression in f333e3513e8bdf5ffeb6eeb63021c230082e6f95.

comment:8 by Mariusz Felisiak <felisiak.mariusz@…>, 12 months ago

In 5f89da0:

[5.0.x] Fixed #35018 -- Fixed migrations crash on GeneratedField with BooleanField as output_field on Oracle < 23c.

Thanks Václav Řehák for the report.

Regression in f333e3513e8bdf5ffeb6eeb63021c230082e6f95.

Backport of fcf95e592774a6ededec35481a2061474d467a2b from main.

Note: See TracTickets for help on using tickets.
Back to Top