Check the coherence of the aliases of GROUP BY and ORDER BY expressions

cedk · cedk · commit df2a504aacfe · 2025-11-24T22:19:12.000+01:00
diff --git a/CHANGELOG b/CHANGELOG
@@ -1,3 +1,4 @@
+* Check the coherence of the aliases of GROUP BY and ORDER BY expressions
 * Do not use parameter for EXTRACT field
 * Remove support for Python older than 3.6
 
diff --git a/sql/__init__.py b/sql/__init__.py
@@ -629,6 +629,20 @@ def __str__(self):
                 and (self.limit is not None or self.offset is not None)):
             return self._rownum(str)
 
+        for expression in chain(
+                self.group_by or [],
+                self.order_by or []):
+            if not isinstance(expression, As):
+                continue
+            for column in self.columns:
+                if not isinstance(column, As):
+                    continue
+                if column.output_name != expression.output_name:
+                    continue
+                if (str(column.expression) != str(expression.expression)
+                        or column.params != expression.params):
+                    raise ValueError("%r != %r" % (expression, column))
+
         with AliasManager():
             if self.from_ is not None:
                 from_ = ' FROM %s' % self.from_
diff --git a/sql/tests/test_select.py b/sql/tests/test_select.py
@@ -253,6 +253,12 @@ def test_select_invalid_group_by(self):
         with self.assertRaises(ValueError):
             self.table.select(group_by=['foo'])
 
+    def test_select_invalid_group_by_alias(self):
+        query = self.table.select(
+            self.table.c1.as_('c'), group_by=self.table.c2.as_('c'))
+        with self.assertRaises(ValueError):
+            str(query)
+
     def test_select_having(self):
         col1 = self.table.col1
         col2 = self.table.col2
@@ -268,16 +274,28 @@ def test_select_invalid_having(self):
             self.table.select(having='foo')
 
     def test_select_order(self):
-        c = self.table.c
-        query = self.table.select(c, order_by=Literal(1))
+        column = self.table.c
+        query = self.table.select(column, order_by=column)
         self.assertEqual(str(query),
-            'SELECT "a"."c" FROM "t" AS "a" ORDER BY %s')
-        self.assertEqual(tuple(query.params), (1,))
+            'SELECT "a"."c" FROM "t" AS "a" ORDER BY "a"."c"')
+        self.assertEqual(tuple(query.params), ())
+
+        output = column.as_('c1')
+        query = self.table.select(output, order_by=output)
+        self.assertEqual(str(query),
+            'SELECT "a"."c" AS "c1" FROM "t" AS "a" ORDER BY "c1"')
+        self.assertEqual(tuple(query.params), ())
 
     def test_select_invalid_order(self):
         with self.assertRaises(ValueError):
             self.table.select(order_by='foo')
 
+    def test_select_invalid_order_alias(self):
+        query = self.table.select(
+            self.table.c1.as_('c'), order_by=self.table.c2.as_('c'))
+        with self.assertRaises(ValueError):
+            str(query)
+
     def test_select_limit_offset(self):
         try:
             Flavor.set(Flavor(limitstyle='limit'))

Original file line number	Diff line number	Diff line change
`@@ -1,3 +1,4 @@`
	`1`	`+* Check the coherence of the aliases of GROUP BY and ORDER BY expressions`
`1`	`2`	`* Do not use parameter for EXTRACT field`
`2`	`3`	`* Remove support for Python older than 3.6`
`3`	`4`