{"action":"created","comment":{"url":"https://api.github.com/repos/django/django/pulls/comments/94142332","pull_request_review_id":14673200,"id":94142332,"diff_hunk":"@@ -667,3 +667,35 @@ def test_add_then_remove_after_prefetch(self):\n         self.assertEqual(city.districts.count(), 2)\n         city.districts.remove(d2)\n         self.assertEqual(city.districts.count(), 1)\n+\n+    def test_cannot_reuse_trimmed_joins(self):\n+        \"\"\"\n+        When a trimmable join is specified in the query (here city__), the ORM\n+        detects it and removes unnecessary joins. The set of reusable joins\n+        should be updated after trimming the query, so that other lookups\n+        doesn't consider that outer query's filters are in effect for the\n+        subquery.\n+\n+        See #26551 for more details.\n+        \"\"\"\n+        vancouver = City.objects.create(id=1, name=\"Vancouver\")\n+        paris = City.objects.create(id=2, name=\"Paris\")\n+        Road.objects.create(city=vancouver, name=\"6th\")\n+        r = Road.objects.create(city=paris, name=\"10th\")\n+        District.objects.create(city=paris, name=\"XVII\")\n+        District.objects.create(city=vancouver, name=\"Fairview\")\n+\n+        qs = Road.objects.filter(\n+            # many_to_one_city is trimmable, do not add it to reusable set\n+            ~(models.Q(city__districts__name='Fairview') &\n+              # If many_to_one_city is in the reusable set, split_exclude will\n+              # add a restriction so that filters related to city are in effect\n+              # in the subquery. Since there hasn't been any filter on city,\n+              # this restriction should not be added.\n+              models.Q(city__districts__creation_date=None)))\n+\n+        # If a restriction has been used, it will be in the form:\n+        # WHERE (... AND many_to_one_city_alias_in_subq.id = many_to_one_city.id)\n+        # which is an incorrect SQL statement since many_to_one_city is not\n+        # used anywhere outside the subquery.\n+        self.assertEqual(list(qs), [r])","path":"tests/many_to_one/tests.py","position":44,"original_position":44,"commit_id":"416b37edd39ddf826382662e0ae04eae247f27c2","original_commit_id":"416b37edd39ddf826382662e0ae04eae247f27c2","user":{"login":"timgraham","id":411869,"avatar_url":"https://avatars.githubusercontent.com/u/411869?v=3","gravatar_id":"","url":"https://api.github.com/users/timgraham","html_url":"https://github.com/timgraham","followers_url":"https://api.github.com/users/timgraham/followers","following_url":"https://api.github.com/users/timgraham/following{/other_user}","gists_url":"https://api.github.com/users/timgraham/gists{/gist_id}","starred_url":"https://api.github.com/users/timgraham/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/timgraham/subscriptions","organizations_url":"https://api.github.com/users/timgraham/orgs","repos_url":"https://api.github.com/users/timgraham/repos","events_url":"https://api.github.com/users/timgraham/events{/privacy}","received_events_url":"https://api.github.com/users/timgraham/received_events","type":"User","site_admin":false},"body":"use `assertSequenceEqual`","created_at":"2016-12-29T14:43:56Z","updated_at":"2016-12-29T14:47:04Z","html_url":"https://github.com/django/django/pull/7451#discussion_r94142332","pull_request_url":"https://api.github.com/repos/django/django/pulls/7451","_links":{"self":{"href":"https://api.github.com/repos/django/django/pulls/comments/94142332"},"html":{"href":"https://github.com/django/django/pull/7451#discussion_r94142332"},"pull_request":{"href":"https://api.github.com/repos/django/django/pulls/7451"}}},"pull_request":{"url":"https://api.github.com/repos/django/django/pulls/7451","id":91422253,"html_url":"https://github.com/django/django/pull/7451","diff_url":"https://github.com/django/django/pull/7451.diff","patch_url":"https://github.com/django/django/pull/7451.patch","issue_url":"https://api.github.com/repos/django/django/issues/7451","number":7451,"state":"open","locked":false,"title":" Fixed #26551 -- Prevented reuse of trimmed joins ","user":{"login":"francoisfreitag","id":2758243,"avatar_url":"https://avatars.githubusercontent.com/u/2758243?v=3","gravatar_id":"","url":"https://api.github.com/users/francoisfreitag","html_url":"https://github.com/francoisfreitag","followers_url":"https://api.github.com/users/francoisfreitag/followers","following_url":"https://api.github.com/users/francoisfreitag/following{/other_user}","gists_url":"https://api.github.com/users/francoisfreitag/gists{/gist_id}","starred_url":"https://api.github.com/users/francoisfreitag/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/francoisfreitag/subscriptions","organizations_url":"https://api.github.com/users/francoisfreitag/orgs","repos_url":"https://api.github.com/users/francoisfreitag/repos","events_url":"https://api.github.com/users/francoisfreitag/events{/privacy}","received_events_url":"https://api.github.com/users/francoisfreitag/received_events","type":"User","site_admin":false},"body":"https://code.djangoproject.com/ticket/26551\n\nThe query generated by the ORM incorrecly assumes that many_to_one_city is used in the outer query, thus generates the line `AND U1.\"id\" = (\"many_to_one_city\".\"id\")` (because alias is in `can_reuse` during the [split_exclude operation](https://github.com/francoisfreitag/django/blob/e3e6495298fe727a3281df452ee8646c55014832/django/db/models/sql/query.py#L1524-L1535)):\n\n``` sql\nSELECT \"many_to_one_road\".\"id\", \"many_to_one_road\".\"city_id\", \"many_to_one_road\".\"name\" \nFROM \"many_to_one_road\" \nWHERE NOT (\n    \"many_to_one_road\".\"city_id\" IN (\n        SELECT U2.\"city_id\" AS Col1 \n        FROM \"many_to_one_district\" U2 \n        WHERE (U2.\"name\" = 'Fairview' AND U2.\"city_id\" IS NOT NULL)\n    ) AND \"many_to_one_road\".\"city_id\" IN (\n        SELECT U1.\"id\" AS Col1 \n        FROM \"many_to_one_city\" U1 \n        LEFT OUTER JOIN \"many_to_one_district\" U2 ON (U1.\"id\" = U2.\"city_id\") \n        WHERE (\n            U2.\"creation_date\" IS NULL \n            AND U1.\"id\" = (\"many_to_one_city\".\"id\")  -- Should not be here\n        )\n    )\n)\n```\n\nThe query should simply be:\n\n``` sql\nSELECT \"many_to_one_road\".\"id\", \"many_to_one_road\".\"city_id\", \"many_to_one_road\".\"name\" \nFROM \"many_to_one_road\" \nWHERE NOT (\n    \"many_to_one_road\".\"city_id\" IN (\n        SELECT U2.\"city_id\" AS Col1 \n        FROM \"many_to_one_district\" U2 \n        WHERE (U2.\"name\" = 'Fairview' AND U2.\"city_id\" IS NOT NULL)\n    ) AND \"many_to_one_road\".\"city_id\" IN (\n        SELECT U1.\"id\" AS Col1 \n        FROM \"many_to_one_city\" U1 \n        LEFT OUTER JOIN \"many_to_one_district\" U2 ON (U1.\"id\" = U2.\"city_id\") \n        WHERE U2.\"creation_date\" IS NULL\n    )\n)\n```\n\nAbout the second commit, I found it surprising to update `used_joins` before trimming `join_list` and I think a comment doesn't hurt.\n","created_at":"2016-10-28T17:43:48Z","updated_at":"2016-12-29T14:47:04Z","closed_at":null,"merged_at":null,"merge_commit_sha":"7a91a1d1e3b5bb89b4af44233197cecbef593e6f","assignee":null,"assignees":[],"milestone":null,"commits_url":"https://api.github.com/repos/django/django/pulls/7451/commits","review_comments_url":"https://api.github.com/repos/django/django/pulls/7451/comments","review_comment_url":"https://api.github.com/repos/django/django/pulls/comments{/number}","comments_url":"https://api.github.com/repos/django/django/issues/7451/comments","statuses_url":"https://api.github.com/repos/django/django/statuses/416b37edd39ddf826382662e0ae04eae247f27c2","head":{"label":"francoisfreitag:26551/inverted_Q_invalid_query","ref":"26551/inverted_Q_invalid_query","sha":"416b37edd39ddf826382662e0ae04eae247f27c2","user":{"login":"francoisfreitag","id":2758243,"avatar_url":"https://avatars.githubusercontent.com/u/2758243?v=3","gravatar_id":"","url":"https://api.github.com/users/francoisfreitag","html_url":"https://github.com/francoisfreitag","followers_url":"https://api.github.com/users/francoisfreitag/followers","following_url":"https://api.github.com/users/francoisfreitag/following{/other_user}","gists_url":"https://api.github.com/users/francoisfreitag/gists{/gist_id}","starred_url":"https://api.github.com/users/francoisfreitag/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/francoisfreitag/subscriptions","organizations_url":"https://api.github.com/users/francoisfreitag/orgs","repos_url":"https://api.github.com/users/francoisfreitag/repos","events_url":"https://api.github.com/users/francoisfreitag/events{/privacy}","received_events_url":"https://api.github.com/users/francoisfreitag/received_events","type":"User","site_admin":false},"repo":{"id":48332638,"name":"django","full_name":"francoisfreitag/django","owner":{"login":"francoisfreitag","id":2758243,"avatar_url":"https://avatars.githubusercontent.com/u/2758243?v=3","gravatar_id":"","url":"https://api.github.com/users/francoisfreitag","html_url":"https://github.com/francoisfreitag","followers_url":"https://api.github.com/users/francoisfreitag/followers","following_url":"https://api.github.com/users/francoisfreitag/following{/other_user}","gists_url":"https://api.github.com/users/francoisfreitag/gists{/gist_id}","starred_url":"https://api.github.com/users/francoisfreitag/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/francoisfreitag/subscriptions","organizations_url":"https://api.github.com/users/francoisfreitag/orgs","repos_url":"https://api.github.com/users/francoisfreitag/repos","events_url":"https://api.github.com/users/francoisfreitag/events{/privacy}","received_events_url":"https://api.github.com/users/francoisfreitag/received_events","type":"User","site_admin":false},"private":false,"html_url":"https://github.com/francoisfreitag/django","description":"The Web framework for perfectionists with deadlines.","fork":true,"url":"https://api.github.com/repos/francoisfreitag/django","forks_url":"https://api.github.com/repos/francoisfreitag/django/forks","keys_url":"https://api.github.com/repos/francoisfreitag/django/keys{/key_id}","collaborators_url":"https://api.github.com/repos/francoisfreitag/django/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/francoisfreitag/django/teams","hooks_url":"https://api.github.com/repos/francoisfreitag/django/hooks","issue_events_url":"https://api.github.com/repos/francoisfreitag/django/issues/events{/number}","events_url":"https://api.github.com/repos/francoisfreitag/django/events","assignees_url":"https://api.github.com/repos/francoisfreitag/django/assignees{/user}","branches_url":"https://api.github.com/repos/francoisfreitag/django/branches{/branch}","tags_url":"https://api.github.com/repos/francoisfreitag/django/tags","blobs_url":"https://api.github.com/repos/francoisfreitag/django/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/francoisfreitag/django/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/francoisfreitag/django/git/refs{/sha}","trees_url":"https://api.github.com/repos/francoisfreitag/django/git/trees{/sha}","statuses_url":"https://api.github.com/repos/francoisfreitag/django/statuses/{sha}","languages_url":"https://api.github.com/repos/francoisfreitag/django/languages","stargazers_url":"https://api.github.com/repos/francoisfreitag/django/stargazers","contributors_url":"https://api.github.com/repos/francoisfreitag/django/contributors","subscribers_url":"https://api.github.com/repos/francoisfreitag/django/subscribers","subscription_url":"https://api.github.com/repos/francoisfreitag/django/subscription","commits_url":"https://api.github.com/repos/francoisfreitag/django/commits{/sha}","git_commits_url":"https://api.github.com/repos/francoisfreitag/django/git/commits{/sha}","comments_url":"https://api.github.com/repos/francoisfreitag/django/comments{/number}","issue_comment_url":"https://api.github.com/repos/francoisfreitag/django/issues/comments{/number}","contents_url":"https://api.github.com/repos/francoisfreitag/django/contents/{+path}","compare_url":"https://api.github.com/repos/francoisfreitag/django/compare/{base}...{head}","merges_url":"https://api.github.com/repos/francoisfreitag/django/merges","archive_url":"https://api.github.com/repos/francoisfreitag/django/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/francoisfreitag/django/downloads","issues_url":"https://api.github.com/repos/francoisfreitag/django/issues{/number}","pulls_url":"https://api.github.com/repos/francoisfreitag/django/pulls{/number}","milestones_url":"https://api.github.com/repos/francoisfreitag/django/milestones{/number}","notifications_url":"https://api.github.com/repos/francoisfreitag/django/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/francoisfreitag/django/labels{/name}","releases_url":"https://api.github.com/repos/francoisfreitag/django/releases{/id}","deployments_url":"https://api.github.com/repos/francoisfreitag/django/deployments","created_at":"2015-12-20T18:37:20Z","updated_at":"2016-09-07T01:04:46Z","pushed_at":"2016-12-27T23:36:08Z","git_url":"git://github.com/francoisfreitag/django.git","ssh_url":"git@github.com:francoisfreitag/django.git","clone_url":"https://github.com/francoisfreitag/django.git","svn_url":"https://github.com/francoisfreitag/django","homepage":"https://www.djangoproject.com/","size":153163,"stargazers_count":0,"watchers_count":0,"language":"Python","has_issues":false,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":0,"mirror_url":null,"open_issues_count":0,"forks":0,"open_issues":0,"watchers":0,"default_branch":"master"}},"base":{"label":"django:master","ref":"master","sha":"e2112a5e1af001449f0d1c183261369f00e93195","user":{"login":"django","id":27804,"avatar_url":"https://avatars.githubusercontent.com/u/27804?v=3","gravatar_id":"","url":"https://api.github.com/users/django","html_url":"https://github.com/django","followers_url":"https://api.github.com/users/django/followers","following_url":"https://api.github.com/users/django/following{/other_user}","gists_url":"https://api.github.com/users/django/gists{/gist_id}","starred_url":"https://api.github.com/users/django/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/django/subscriptions","organizations_url":"https://api.github.com/users/django/orgs","repos_url":"https://api.github.com/users/django/repos","events_url":"https://api.github.com/users/django/events{/privacy}","received_events_url":"https://api.github.com/users/django/received_events","type":"Organization","site_admin":false},"repo":{"id":4164482,"name":"django","full_name":"django/django","owner":{"login":"django","id":27804,"avatar_url":"https://avatars.githubusercontent.com/u/27804?v=3","gravatar_id":"","url":"https://api.github.com/users/django","html_url":"https://github.com/django","followers_url":"https://api.github.com/users/django/followers","following_url":"https://api.github.com/users/django/following{/other_user}","gists_url":"https://api.github.com/users/django/gists{/gist_id}","starred_url":"https://api.github.com/users/django/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/django/subscriptions","organizations_url":"https://api.github.com/users/django/orgs","repos_url":"https://api.github.com/users/django/repos","events_url":"https://api.github.com/users/django/events{/privacy}","received_events_url":"https://api.github.com/users/django/received_events","type":"Organization","site_admin":false},"private":false,"html_url":"https://github.com/django/django","description":"The Web framework for perfectionists with deadlines.","fork":false,"url":"https://api.github.com/repos/django/django","forks_url":"https://api.github.com/repos/django/django/forks","keys_url":"https://api.github.com/repos/django/django/keys{/key_id}","collaborators_url":"https://api.github.com/repos/django/django/collaborators{/collaborator}","teams_url":"https://api.github.com/repos/django/django/teams","hooks_url":"https://api.github.com/repos/django/django/hooks","issue_events_url":"https://api.github.com/repos/django/django/issues/events{/number}","events_url":"https://api.github.com/repos/django/django/events","assignees_url":"https://api.github.com/repos/django/django/assignees{/user}","branches_url":"https://api.github.com/repos/django/django/branches{/branch}","tags_url":"https://api.github.com/repos/django/django/tags","blobs_url":"https://api.github.com/repos/django/django/git/blobs{/sha}","git_tags_url":"https://api.github.com/repos/django/django/git/tags{/sha}","git_refs_url":"https://api.github.com/repos/django/django/git/refs{/sha}","trees_url":"https://api.github.com/repos/django/django/git/trees{/sha}","statuses_url":"https://api.github.com/repos/django/django/statuses/{sha}","languages_url":"https://api.github.com/repos/django/django/languages","stargazers_url":"https://api.github.com/repos/django/django/stargazers","contributors_url":"https://api.github.com/repos/django/django/contributors","subscribers_url":"https://api.github.com/repos/django/django/subscribers","subscription_url":"https://api.github.com/repos/django/django/subscription","commits_url":"https://api.github.com/repos/django/django/commits{/sha}","git_commits_url":"https://api.github.com/repos/django/django/git/commits{/sha}","comments_url":"https://api.github.com/repos/django/django/comments{/number}","issue_comment_url":"https://api.github.com/repos/django/django/issues/comments{/number}","contents_url":"https://api.github.com/repos/django/django/contents/{+path}","compare_url":"https://api.github.com/repos/django/django/compare/{base}...{head}","merges_url":"https://api.github.com/repos/django/django/merges","archive_url":"https://api.github.com/repos/django/django/{archive_format}{/ref}","downloads_url":"https://api.github.com/repos/django/django/downloads","issues_url":"https://api.github.com/repos/django/django/issues{/number}","pulls_url":"https://api.github.com/repos/django/django/pulls{/number}","milestones_url":"https://api.github.com/repos/django/django/milestones{/number}","notifications_url":"https://api.github.com/repos/django/django/notifications{?since,all,participating}","labels_url":"https://api.github.com/repos/django/django/labels{/name}","releases_url":"https://api.github.com/repos/django/django/releases{/id}","deployments_url":"https://api.github.com/repos/django/django/deployments","created_at":"2012-04-28T02:47:18Z","updated_at":"2016-12-29T14:08:01Z","pushed_at":"2016-12-29T13:55:38Z","git_url":"git://github.com/django/django.git","ssh_url":"git@github.com:django/django.git","clone_url":"https://github.com/django/django.git","svn_url":"https://github.com/django/django","homepage":"https://www.djangoproject.com/","size":155650,"stargazers_count":22839,"watchers_count":22839,"language":"Python","has_issues":false,"has_downloads":true,"has_wiki":false,"has_pages":false,"forks_count":9230,"mirror_url":null,"open_issues_count":100,"forks":9230,"open_issues":100,"watchers":22839,"default_branch":"master"}},"_links":{"self":{"href":"https://api.github.com/repos/django/django/pulls/7451"},"html":{"href":"https://github.com/django/django/pull/7451"},"issue":{"href":"https://api.github.com/repos/django/django/issues/7451"},"comments":{"href":"https://api.github.com/repos/django/django/issues/7451/comments"},"review_comments":{"href":"https://api.github.com/repos/django/django/pulls/7451/comments"},"review_comment":{"href":"https://api.github.com/repos/django/django/pulls/comments{/number}"},"commits":{"href":"https://api.github.com/repos/django/django/pulls/7451/commits"},"statuses":{"href":"https://api.github.com/repos/django/django/statuses/416b37edd39ddf826382662e0ae04eae247f27c2"}}}}
{"actor":{"display_login":"timgraham"}}