id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
29530	Chaining .annotate() on models generates wrong SQL aliases.	Volodymyr	Mariusz Felisiak	"I simplified my models code as I could, keeping necessary fields.
So with these models given:
{{{
class Station(models.Model):
    pass

class Route(models.Model):
    """""" just ties route nodes together """"""
    pass

class RouteNode(models.Model):
    """"""
    Class that handles many-to-many relationship between stations and routes.
    """"""
    route = models.ForeignKey(Route, on_delete=models.CASCADE)
    station = models.ForeignKey(Station, on_delete=models.CASCADE)
    duration = models.DurationField()

class Trip(models.Model):
    route = models.ForeignKey(Route, on_delete=models.DO_NOTHING)
}}}

I try to query them like:
{{{
Trip.objects.filter(
            route__routenode__station=1
        ).annotate(
            board_station_duration=F('route__routenode__duration'),
        ).filter(
            route__routenode__station=2,
        ).annotate(
            dest_station_duration=F('route__routenode__duration'),
        )
}}}
The code above being run on environment with
''Django 2.0, Python 3.6.2, DB engine : psycopg2''
generates the following SQL, so ""board_station_duration"" and ""dest_station_duration"" referencing same field of same routenode record.
{{{
SELECT
  -- not-relevant to this ticket fields --
  ""routenode"".""duration""  AS ""board_station_duration"",
  ""routenode"".""duration""  AS ""dest_station_duration""
FROM ""trip""
  INNER JOIN ""route"" ON (""trip"".""route_id"" = ""route"".""id"")
  INNER JOIN ""routenode"" ON (""route"".""id"" = ""routenode"".""route_id"")
  INNER JOIN ""routenode"" T5 ON (""route"".""id"" = T5.""route_id"")
WHERE (""routenode"".""station_id"" = 1 AND T5.""station_id"" = 2);
}}}
And the same python code being run on env
''Django 1.10, Python 2.7.14, DB engine : psycopg2''
generates the following SQL **(as expected)**, so ""board_station_duration"" and ""dest_station_duration"" referencing fields of different routenode record.
{{{
SELECT
  -- not-relevant to this ticket fields --
  ""routenode"".""duration""  AS ""board_station_duration"",
  T5.""duration""  AS ""dest_station_duration""
FROM ""trip""
  INNER JOIN ""route"" ON (""trip"".""route_id"" = ""route"".""id"")
  INNER JOIN ""routenode"" ON (""route"".""id"" = ""routenode"".""route_id"")
  INNER JOIN ""routenode"" T5 ON (""route"".""id"" = T5.""route_id"")
WHERE (""routenode"".""station_id"" = 1 AND T5.""station_id"" = 2);
}}}
"	Bug	closed	Database layer (models, ORM)	2.0	Normal	fixed	annotate, INNER JOIN, model,	Mariusz Felisiak	Ready for checkin	1	0	0	0	0	0