Deep conditions in FilteredRelation raise ProgrammingError if annotation reused

[Jacob Walls]

I was debugging a query that failed like this:

psycopg.errors.UndefinedTable: missing FROM-clause entry for table "book_editor"
LINE 1: ...r" LEFT OUTER JOIN "filtered_relation_editor" ON (book_edito...

#33929 seems related. Here, the key is using a deeper condition relation (book__editor) than the filtered relation (book) and then reusing that attempted annotation in another annotate().

Failing unit test. The test right above ​asserts that a ValueError is raised in a similar situation, so maybe this test should do that also?

tests/filtered_relation/tests.py

@@ -668 +668 @@
                 ),
             )
 
+    def test_condition_deeper_relation_name_reused_annotation(self):
+        qs = Author.objects.annotate(
+            book_editor=FilteredRelation(
+                "book",
+                condition=Q(book__editor__name="b"),
+            ),
+        ).annotate(reused_annotation=F("book_editor"))
+        self.assertEqual(qs.count(), 2)
+
     def test_with_empty_relation_name_error(self):
         with self.assertRaisesMessage(ValueError, "relation_name cannot be empty."):
             FilteredRelation("", condition=Q(blank=""))