MySQL backend raises error when selecting 0 using an expression targeting an autofield.

[Clifford Gama]

In MySQL, Django currently raises a ValueError when trying to insert 0 into an AutoField, which is correct when saving because MySQL ​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 ​these logs.

[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?)

[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 (​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())).

[Jacob Walls]

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.

[Jacob Walls <jacobtylerwalls@…>]

In 5588865:

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.