Chaining .annotate() on models generates wrong SQL aliases.

[Volodymyr]

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);

[Mariusz Felisiak]

Regression test.

[Mariusz Felisiak]

I reproduced this issue and prepared regression test in our test suite. I will bisect it to check if that should be marked as a release blocker.

[Mariusz Felisiak]

Can you tell me in which exact version that works for you?

[Volodymyr]

Django 1.10, Python 2.7.14, DB engine : psycopg2

[Mariusz Felisiak]

Thanks. It passes on Django 1.10 but only on Python 2.7, it fails on Python 3.6 🤯

Replying to Volodymyr:

Django 1.10, Python 2.7.14, DB engine : psycopg2

[Mariusz Felisiak]

Bisected to the ​8d66bffbae8e5a230da51c7638d24fdbd327a96b on Python 2.7

commit 8d66bffbae8e5a230da51c7638d24fdbd327a96b
Author: Bo Marchman <bo.marchman@gmail.com>
Date:   Thu Mar 2 09:36:25 2017 -0500

    [1.11.x] Fixed #26522 -- Fixed a nondeterministic AssertionError in QuerySet combining.
    
    Thanks Andrew Brown for the test case.

    Backport of 9bbb6e2d2536c4ac20dc13a94c1f80494e51f8d9 from master

It is related with changing self.alias_map to OrderedDict(). That's why it doesn't work on Python 3.6.

[Mariusz Felisiak]

​PR

[GitHub <noreply@…>]

In 0e64e04:

Fixed #29530 -- Fixed aliases ordering when chaining annotate() and filter().