Re: [Django] #31135: OuterRef generates invalid SQL with __in filter.
Django
-------------------------------------+-------------------------------------
Reporter: Bernd Wechner | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Bernd Wechner):
Indeed an issue in my code. But also an issue with Django. There is no
excuse to go from generating valid SQL to generating invalid SQL. If that
usage is not supported, fine, but throw an exception before it bombs on a
SQL syntax error. Half the raison d'etre of Django is surely to isolate a
web designer from the innards of SQL queries as best possible, and not to
thrust them into SQL land trying work out why their ORM is generating
invalid SQL, when it could just as well through a lucid DJango exception.
To wit, I would suggest reopening it and flagging a fix if only for Django
to provide more lucid feedback than a SQL syntax error.
--
Ticket URL: <https://code.djangoproject.com/ticket/31135#comment:2>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: Bernd Wechner | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by mgaitan):
Hi, I'm upgrading a project from 2.1 to 3.2 and also hit this issue.
My code does something like this
{{{
agrupaciones_subquery = (
AgrupacionCircuitos.objects.filter(id__in=self.agrupaciones_a_considerar())
.filter(
id__in=(OuterRef("carga__mesa_categoria__mesa__circuito__agrupaciones"))
)
.values_list("id", flat=True)
)
return (
self.votos_reportados(categoria, mesas)
.values_list("opcion__id")
.annotate(id_agrupacion=Subquery(agrupaciones_subquery))
.exclude(id_agrupacion__isnull=True)
.annotate(sum_votos=Sum("votos"))
)
}}}
I can't change `
id__in=(OuterRef("carga__mesa_categoria__mesa__circuito__agrupaciones"))`
by `id=(OuterRef)` because in my case, it may contains multiples
related "agrupaciones" . Which should be the workaround?
Btw, I also agree with Bernd Wechner about the error raised should be more
clear.
--
Ticket URL: <https://code.djangoproject.com/ticket/31135#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: Bernd Wechner | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Vasili Korol):
I bumped into this after upgrading from 2.2 to 3.2. Not sure why was this
issue closed, the issue is still there!
--
Ticket URL: <https://code.djangoproject.com/ticket/31135#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: Bernd Wechner | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak):
Replying to [comment:4 Vasili Korol]:
> I bumped into this after upgrading from 2.2 to 3.2. Not sure why was
this ticket closed, the issue is still there!
It was closed because this usage has never been officially supported.
--
Ticket URL: <https://code.djangoproject.com/ticket/31135#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: Bernd Wechner | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Bernd Wechner):
Replying to [comment:5 Mariusz Felisiak]:
> Replying to [comment:4 Vasili Korol]:
> > I bumped into this after upgrading from 2.2 to 3.2. Not sure why was
this ticket closed, the issue is still there!
>
> It was closed because this usage has never been officially supported.
It remains a bug that it produces invalid SQL where a prior version did
not, and not a sensible exception - causing much wasted time on the part
of anyone who used the old (unsupported but functional) syntax.
--
Ticket URL: <https://code.djangoproject.com/ticket/31135#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: Bernd Wechner | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Mariusz Felisiak):
> It remains a bug that it produces invalid SQL where a prior version did
not, ...
Yes but unintentionally and only on some databases.
> and not a sensible exception - causing much wasted time on the part of
anyone who used the old
Feel-free to prepare a patch with raising a Python level error when using
many-to-many field in `F()` or `OuterRef()` (see also #32414). I would be
happy to review.
--
Ticket URL: <https://code.djangoproject.com/ticket/31135#comment:7>