Repository: tobymao/sqlglot
Pull request: Add support for aggregates without the GROUP BY clause
Fixes #703 

Problem statement:
execute(): Exception when aggregating without GROUP BY
sqlglot 10.0.2, Python 3.8.4

`>>> execute('select sum(x) from t', tables={'t': [{'x': 1}, {'x': 2}]})`

Result:

`sqlglot.errors.ExecuteError: Step 'Scan: t (140100782263984)' failed: '_col_0'`

If I add column 'y' above, with the same value for every row, and group by that column, the execute returns as expected.

Hints from issue discussion:
this isn't supported yet but we can add it soon
Note: I'm planning to tackle this issue soon, just spending some time to understand the executor's logic since I haven't had the chance to play around with it.

Commit message: Add support for aggregates without the GROUP BY clause

Patch to review:
diff --git a/sqlglot/executor/__init__.py b/sqlglot/executor/__init__.py
index 1954e54699..22f42ee585 100644
--- a/sqlglot/executor/__init__.py
+++ b/sqlglot/executor/__init__.py
@@ -51,17 +51,3 @@ def execute(sql, schema=None, read=None, tables=None):
     result = PythonExecutor(tables=tables).execute(plan)
     logger.debug("Query finished: %f", time.time() - now)
     return result
-
-
-def _ensure_tables(tables):
-    result = {}
-    if not tables:
-        return result
-    for name, table in tables.items():
-        if isinstance(table, Table):
-            result[name] = table
-        else:
-            columns = tuple(table[0]) if table else ()
-            rows = [tuple(row[c] for c in columns) for row in table]
-            result[name] = Table(columns=columns, rows=rows)
-    return result
diff --git a/sqlglot/executor/context.py b/sqlglot/executor/context.py
index 393347bc88..8c14f4944a 100644
--- a/sqlglot/executor/context.py
+++ b/sqlglot/executor/context.py
@@ -1,5 +1,12 @@
+from __future__ import annotations
+
+import typing as t
+
 from sqlglot.executor.env import ENV
 
+if t.TYPE_CHECKING:
+    from sqlglot.executor.table import Table, TableIter
+
 
 class Context:
     """
@@ -12,11 +19,11 @@ class Context:
     evaluation of aggregation functions.
     """
 
-    def __init__(self, tables, env=None):
+    def __init__(self, tables: t.Dict[str, Table], env: t.Optional[t.Dict] = None) -> None:
         """
         Args
-            tables (dict): table_name -> Table, representing the scope of the current execution context
-            env (Optional[dict]): dictionary of functions within the execution context
+            tables: representing the scope of the current execution context.
+            env: dictionary of functions within the execution context.
         """
         self.tables = tables
         self._table = None
@@ -31,18 +38,18 @@ def eval_tuple(self, codes):
         return tuple(self.eval(code) for code in codes)
 
     @property
-    def table(self):
+    def table(self) -> Table:
         if self._table is None:
-            self._table = list(self.tables.values())[0]
+            self._table = list(self.tables.values())[0]  # type: ignore
             for other in self.tables.values():
-                if self._table.columns != other.columns:
+                if self._table.columns != other.columns:  # type: ignore
                     raise Exception(f"Columns are different.")
-                if len(self._table.rows) != len(other.rows):
+                if len(self._table.rows) != len(other.rows):  # type: ignore
                     raise Exception(f"Rows are different.")
-        return self._table
+        return self._table  # type: ignore
 
     @property
-    def columns(self):
+    def columns(self) -> t.Tuple:
         return self.table.columns
 
     def __iter__(self):
@@ -52,35 +59,35 @@ def __iter__(self):
                 reader = table[i]
             yield reader, self
 
-    def table_iter(self, table):
+    def table_iter(self, table: str) -> t.Generator[t.Tuple[TableIter, Context], None, None]:
         self.env["scope"] = self.row_readers
 
         for reader in self.tables[table]:
             yield reader, self
 
-    def sort(self, key):
+    def sort(self, key) -> None:
         table = self.table
 
-        def sort_key(row):
+        def sort_key(row: t.Tuple) -> t.Tuple:
             table.reader.row = row
             return self.eval_tuple(key)
 
         table.rows.sort(key=sort_key)
 
-    def set_row(self, row):
+    def set_row(self, row: t.Tuple) -> None:
         for table in self.tables.values():
             table.reader.row = row
         self.env["scope"] = self.row_readers
 
-    def set_index(self, index):
+    def set_index(self, index: int) -> None:
         for table in self.tables.values():
             table[index]
         self.env["scope"] = self.row_readers
 
-    def set_range(self, start, end):
+    def set_range(self, start: int, end: int) -> None:
         for name in self.tables:
             self.range_readers[name].range = range(start, end)
         self.env["scope"] = self.range_readers
 
-    def __contains__(self, table):
+    def __contains__(self, table: str) -> bool:
         return table in self.tables
diff --git a/sqlglot/planner.py b/sqlglot/planner.py
index 7870a66891..72620b79d3 100644
--- a/sqlglot/planner.py
+++ b/sqlglot/planner.py
@@ -150,7 +150,7 @@ def from_expression(
 
         group = expression.args.get("group")
 
-        if group:
+        if group or aggregations:
             aggregate = Aggregate()
             aggregate.source = step.name
             aggregate.name = step.name
@@ -158,7 +158,7 @@ def from_expression(
                 alias(operand, alias_) for operand, alias_ in operands.items()
             )
             aggregate.aggregations = aggregations
-            aggregate.group = group.expressions
+            aggregate.group = group.expressions if group else []
             aggregate.add_dependency(step)
             step = aggregate
 
diff --git a/tests/test_executor.py b/tests/test_executor.py
index db71f96eb0..15b885867b 100644
--- a/tests/test_executor.py
+++ b/tests/test_executor.py
@@ -73,7 +73,7 @@ def to_csv(expression):
                 )
             return expression
 
-        for sql, _ in self.sqls[0:3]:
+        for sql, _ in self.sqls[:5]:
             a = self.cached_execute(sql)
             sql = parse_one(sql).transform(to_csv).sql(pretty=True)
             table = execute(sql, TPCH_SCHEMA)
@@ -256,3 +256,8 @@ def test_static_queries(self):
             result = execute(sql)
             self.assertEqual(result.columns, tuple(cols))
             self.assertEqual(result.rows, rows)
+
+    def test_aggregate_without_group_by(self):
+        result = execute("SELECT SUM(x) FROM t", tables={"t": [{"x": 1}, {"x": 2}]})
+        self.assertEqual(result.columns, ("_col_0",))
+        self.assertEqual(result.rows, [(3,)])


Code review comments:
diff_hunk: @@ -31,18 +38,18 @@ def eval_tuple(self, codes):
         return tuple(self.eval(code) for code in codes)
 
     @property
-    def table(self):
+    def table(self) -> Table:
         if self._table is None:
-            self._table = list(self.tables.values())[0]
+            self._table = list(self.tables.values())[0]  # type: ignore
path: sqlglot/executor/context.py
text: @user1:
don't do type ignores