GeneratedField crashes on Oracle 19c when output_field is BooleanField.

[Václav Řehák]

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.

[Mariusz Felisiak]

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

Regression in f333e3513e8bdf5ffeb6eeb63021c230082e6f95.

[Mariusz Felisiak]

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.

[Simon Charette]

​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?

[Mariusz Felisiak]

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.

[Mariusz Felisiak]

​PR

[GitHub <noreply@…>]

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.

[Mariusz Felisiak <felisiak.mariusz@…>]

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.