[Django] #36722: MySQL backend raises error when selecting 0 using an expression targeting an autofield.

[Django]

#36722: MySQL backend raises error when selecting 0 using an expression targeting
an autofield.
-------------------------------------+-------------------------------------
Reporter: Clifford | Owner: Clifford Gama
Gama |
Type: Bug | Status: assigned
Component: Database | Version: dev
layer (models, ORM) |
Severity: Normal | Keywords: allows_auto_pk_0
Triage Stage: | Has patch: 1
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
In MySQL, Django currently raises a `ValueError` when trying to insert 0
into an `AutoField`, which is correct when saving because MySQL
[https://dev.mysql.com/doc/refman/8.4/en/sql-
mode.html#sqlmode_no_auto_value_on_zero interprets 0 as auto-increment]
unless `NO_AUTO_VALUE_ON_ZERO` is set.

However, the backend also raises an error when using expressions like
Value(0, BigAutoField()) in select filters, which I believe is a bug.

Discovered in [https://djangoci.com/job/pull-requests-
noble/database=mysql,label=noble-
pr,python=python3.12/1000/testReport/junit/aggregation.tests/AggregateAnnotationPruningTests/test_aggregate_reference_lookup_rhs_iter/
these logs].
--
Ticket URL: <https://code.djangoproject.com/ticket/36722>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#36722: MySQL backend raises error when selecting 0 using an expression targeting
an autofield.
-------------------------------------+-------------------------------------

Reporter: Clifford Gama | Owner: Clifford

| Gama
Type: Bug | Status: assigned

Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: allows_auto_pk_0 | Triage Stage:
| Unreviewed
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Jacob Walls):

Thanks for looking into this. Can you say more about the use case for
using `AutoField` in the `output_field`? It seems like this fell out of
the work in #36689, but maybe you can help me out with the motivation for
why we need to support this (do we have any existing test cases?)
--
Ticket URL: <https://code.djangoproject.com/ticket/36722#comment:1>

[Django]

#36722: MySQL backend raises error when selecting 0 using an expression targeting
an autofield.
-------------------------------------+-------------------------------------
Reporter: Clifford Gama | Owner: Clifford
| Gama
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: allows_auto_pk_0 | Triage Stage:
| Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Clifford Gama):

My thinking was that since the validation is save-specific, it should be
in `get_db_prep_save()`, which is specific to save, instead of
`get_db_prep_value()` which is also used in filters. I only used `Value(0,
AutoField())` as a testing strategy for the change, since it helped me
identify the issue. If it turns out that there's no other use-case where
filtering zero on an AutoField raises the ValueError, then perhaps this
ticket would become a cleanup/otimisation instead of a bug.

For further context:

The issue discovered in #36689 after I made `ExpressionList` wrap direct
values of an iterable rhs in `Value()` using an explicit `output_field`
([https://github.com/django/django/pull/20009/commits/0c56236dc2b5526044fa326fde8d52ab084b63de
commit]). The test in the log failed because it has
`filter=Q(id__in=[F("max_book_author"), 0])`, which, when wrapped in an
`ExpressionList`, was becoming `ExpressionList(F("max_book_author"),
Value(0, BigAutoField()))`.
--
Ticket URL: <https://code.djangoproject.com/ticket/36722#comment:2>

[Django]

#36722: MySQL backend raises error when selecting 0 using an expression targeting
an autofield.
-------------------------------------+-------------------------------------
Reporter: Clifford Gama | Owner: Clifford
| Gama
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:

Keywords: allows_auto_pk_0 | Triage Stage: Accepted

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Jacob Walls):

* stage: Unreviewed => Accepted

Comment:

Thanks, makes sense. We can leave it as a bug, as it unnecessarily makes a
query that should work on all backends fail on MySQL.
--
Ticket URL: <https://code.djangoproject.com/ticket/36722#comment:3>

[Django]

#36722: MySQL backend raises error when selecting 0 using an expression targeting
an autofield.
-------------------------------------+-------------------------------------
Reporter: Clifford Gama | Owner: Clifford
| Gama
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:
Keywords: allows_auto_pk_0 | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Jacob Walls):

* needs_better_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/36722#comment:4>

[Django]

#36722: MySQL backend raises error when selecting 0 using an expression targeting
an autofield.
-------------------------------------+-------------------------------------
Reporter: Clifford Gama | Owner: Clifford
| Gama
Type: Bug | Status: assigned
Component: Database layer | Version: dev
(models, ORM) |
Severity: Normal | Resolution:

Keywords: allows_auto_pk_0 | Triage Stage: Ready for
| checkin

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Jacob Walls):

* needs_better_patch: 1 => 0
* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/36722#comment:5>

[Django]

#36722: MySQL backend raises error when selecting 0 using an expression targeting
an autofield.
-------------------------------------+-------------------------------------
Reporter: Clifford Gama | Owner: Clifford
| Gama

Type: Bug | Status: closed

Component: Database layer | Version: dev
(models, ORM) |

Severity: Normal | Resolution: fixed

Keywords: allows_auto_pk_0 | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Jacob Walls <jacobtylerwalls@…>):

* resolution: => fixed
* status: assigned => closed

Comment:

In [changeset:"55888655a269f41025405b9a1bff14117ae58e2a" 5588865]:
{{{#!CommitTicketReference repository=""
revision="55888655a269f41025405b9a1bff14117ae58e2a"
Fixed #36722 -- Moved AutoFieldMixin validate_autopk_value() check to
get_db_prep_save.

The validation in validate_autopk_value is specific to saving. Having it
in
get_db_prep_value caused Value(0, AutoField()) to fail unexpectedly when
used
in a filter on MySQL.

Thanks Jacob Walls for the review.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36722#comment:6>