Refs #28900 -- Made SELECT respect the order specified by values(*selected).

Previously the order was always extra_fields + model_fields + annotations with
respective local ordering inferred from the insertion order of *selected.

This commits introduces a new `Query.selected` propery that keeps tracks of the
global select order as specified by on values assignment. This is crucial
feature to allow the combination of queries mixing annotations and table
references.

It also allows the removal of the re-ordering shenanigans perform by
ValuesListIterable in order to re-map the tuples returned from the database
backend to the order specified by values_list() as they'll be in the right
order at query compilation time.

Refs #28553 as the initially reported issue that was only partially fixed
for annotations by d6b6e5d0fd4e6b6d0183b4cf6e4bd4f9afc7bf67.

Thanks Mariusz Felisiak and Sarah Boyce for review.

4 files changed, 35 insertions, 4 deletions

diff --git a/tests/annotations/tests.py b/tests/annotations/tests.py
index f1260b4192..703847e1dd 100644
--- a/tests/annotations/tests.py
+++ b/tests/annotations/tests.py
@@ -568,6 +568,16 @@ class NonAggregateAnnotationTestCase(TestCase):
         self.assertEqual(book["other_rating"], 4)
         self.assertEqual(book["other_isbn"], "155860191")
 
+    def test_values_fields_annotations_order(self):
+        qs = Book.objects.annotate(other_rating=F("rating") - 1).values(
+            "other_rating", "rating"
+        )
+        book = qs.get(pk=self.b1.pk)
+        self.assertEqual(
+            list(book.items()),
+            [("other_rating", self.b1.rating - 1), ("rating", self.b1.rating)],
+        )
+
     def test_values_with_pk_annotation(self):
         # annotate references a field in values() with pk
         publishers = Publisher.objects.values("id", "book__rating").annotate(
diff --git a/tests/postgres_tests/test_array.py b/tests/postgres_tests/test_array.py
index 386a0afa3a..ff0c4aabb1 100644
--- a/tests/postgres_tests/test_array.py
+++ b/tests/postgres_tests/test_array.py
@@ -466,8 +466,8 @@ class TestQuerying(PostgreSQLTestCase):
                 ],
             )
         sql = ctx[0]["sql"]
-        self.assertIn("GROUP BY 2", sql)
-        self.assertIn("ORDER BY 2", sql)
+        self.assertIn("GROUP BY 1", sql)
+        self.assertIn("ORDER BY 1", sql)
 
     def test_order_by_arrayagg_index(self):
         qs = (
diff --git a/tests/queries/test_qs_combinators.py b/tests/queries/test_qs_combinators.py
index 4c2dbc5b17..e130613084 100644
--- a/tests/queries/test_qs_combinators.py
+++ b/tests/queries/test_qs_combinators.py
@@ -257,6 +257,23 @@ class QuerySetSetOperationTests(TestCase):
         )
         self.assertCountEqual(qs1.union(qs2), [(1, 0), (1, 2)])
 
+    def test_union_with_field_and_annotation_values(self):
+        qs1 = (
+            Number.objects.filter(num=1)
+            .annotate(
+                zero=Value(0, IntegerField()),
+            )
+            .values_list("num", "zero")
+        )
+        qs2 = (
+            Number.objects.filter(num=2)
+            .annotate(
+                zero=Value(0, IntegerField()),
+            )
+            .values_list("zero", "num")
+        )
+        self.assertCountEqual(qs1.union(qs2), [(1, 0), (0, 2)])
+
     def test_union_with_extra_and_values_list(self):
         qs1 = (
             Number.objects.filter(num=1)
@@ -265,7 +282,11 @@ class QuerySetSetOperationTests(TestCase):
             )
             .values_list("num", "count")
         )
-        qs2 = Number.objects.filter(num=2).extra(select={"count": 1})
+        qs2 = (
+            Number.objects.filter(num=2)
+            .extra(select={"count": 1})
+            .values_list("num", "count")
+        )
         self.assertCountEqual(qs1.union(qs2), [(1, 0), (2, 1)])
 
     def test_union_with_values_list_on_annotated_and_unannotated(self):
diff --git a/tests/queries/tests.py b/tests/queries/tests.py
index 7ac8a65d42..3621b6cb2c 100644
--- a/tests/queries/tests.py
+++ b/tests/queries/tests.py
@@ -2200,7 +2200,7 @@ class Queries6Tests(TestCase):
                 {"tag_per_parent__max": 2},
             )
         sql = captured_queries[0]["sql"]
-        self.assertIn("AS %s" % connection.ops.quote_name("col1"), sql)
+        self.assertIn("AS %s" % connection.ops.quote_name("parent"), sql)
 
     def test_xor_subquery(self):
         self.assertSequenceEqual(