diff options
Diffstat (limited to 'tests/test_executor.py')
| -rw-r--r-- | tests/test_executor.py | 58 |
1 files changed, 58 insertions, 0 deletions
diff --git a/tests/test_executor.py b/tests/test_executor.py index 3a37cd4..6dd530f 100644 --- a/tests/test_executor.py +++ b/tests/test_executor.py @@ -254,6 +254,11 @@ class TestExecutor(unittest.TestCase): [("a",)], ), ( + "(SELECT a FROM x) EXCEPT (SELECT a FROM y)", + ["a"], + [("a",)], + ), + ( "SELECT a FROM x INTERSECT SELECT a FROM y", ["a"], [("b",), ("c",)], @@ -646,3 +651,56 @@ class TestExecutor(unittest.TestCase): self.assertEqual(result.columns, ("id", "price")) self.assertEqual(result.rows, [(1, 1.0), (2, 2.0), (3, 3.0)]) + + def test_group_by(self): + tables = { + "x": [ + {"a": 1, "b": 10}, + {"a": 2, "b": 20}, + {"a": 3, "b": 28}, + {"a": 2, "b": 25}, + {"a": 1, "b": 40}, + ], + } + + for sql, expected, columns in ( + ( + "SELECT a, AVG(b) FROM x GROUP BY a ORDER BY AVG(b)", + [(2, 22.5), (1, 25.0), (3, 28.0)], + ("a", "_col_1"), + ), + ( + "SELECT a, AVG(b) FROM x GROUP BY a having avg(b) > 23", + [(1, 25.0), (3, 28.0)], + ("a", "_col_1"), + ), + ( + "SELECT a, AVG(b) FROM x GROUP BY a having avg(b + 1) > 23", + [(1, 25.0), (2, 22.5), (3, 28.0)], + ("a", "_col_1"), + ), + ( + "SELECT a, AVG(b) FROM x GROUP BY a having sum(b) + 5 > 50", + [(1, 25.0)], + ("a", "_col_1"), + ), + ( + "SELECT a + 1 AS a, AVG(b + 1) FROM x GROUP BY a + 1 having AVG(b + 1) > 26", + [(4, 29.0)], + ("a", "_col_1"), + ), + ( + "SELECT a, avg(b) FROM x GROUP BY a HAVING a = 1", + [(1, 25.0)], + ("a", "_col_1"), + ), + ( + "SELECT a + 1, avg(b) FROM x GROUP BY a + 1 HAVING a + 1 = 2", + [(2, 25.0)], + ("_col_0", "_col_1"), + ), + ): + with self.subTest(sql): + result = execute(sql, tables=tables) + self.assertEqual(result.columns, columns) + self.assertEqual(result.rows, expected) |