Repository: DeepLcom/sql-mock (language: python)

Issue:
v0.6.1 Can't parse Clickhouse `argMaxIf` function
Since v0.6.1 SQLMock is unable to parse Clickhouse's `argMaxIf` function.

Consider the following example:

```python
from datetime import datetime

from sql_mock.clickhouse import column_mocks as col
from sql_mock.clickhouse.table_mocks import ClickHouseTableMock
from sql_mock.table_mocks import table_meta

query = """SELECT
    user_id,
    count() AS num_sessions,
    countIf(valid = 1) AS num_valid_sessions,
    argMaxIf(in_trial, dt, isNotNull(in_trial)) AS in_trial
FROM sessions
GROUP BY user_id
"""


@table_meta(table_ref="sessions")
class SessionsMock(ClickHouseTableMock):
    dt = col.Datetime(default=datetime(2024, 1, 1 ,10, 30))
    valid = col.Boolean(default=True)
    user_id = col.String(default="foo")
    in_trial = col.Int(default=0, nullable=True)


@table_meta(query=query)
class ResultMock(ClickHouseTableMock):
    user_id = col.String(default="foo")
    num_sessions = col.Int(default=0)
    num_valid_sessions = col.Int(default=0)
    in_trial = col.Int(default=0)


def test_example() -> None:
    sessions_mock = SessionsMock.from_dicts(
        [
            dict(
                dt=datetime(2024, 1, 1, 10),
                valid=1,
                user_id="foo",
                in_trial=1,
            ),
            dict(
                dt=datetime(2024, 1, 2, 10),
                valid=0,
                user_id="foo",
                in_trial=1,
            ),
            dict(
                dt=datetime(2024, 1, 3, 10),
                valid=1,
                user_id="foo",
                in_trial=1,
            ),
            dict(
                dt=datetime(2024, 1, 4, 10),
                valid=1,
                user_id="foo",
                in_trial=None,
            ),
            dict(
                dt=datetime(2024, 1, 5, 10),
                valid=1,
                user_id="foo",
                in_trial=0,
            ),
        ]
    )

    result = ResultMock.from_mocks(input_data=[sessions_mock])

    expected = [
        dict(
            user_id="foo",
            num_sessions=5,
            num_valid_sessions=4,
            in_trial=0,
        )
    ]

    result.assert_equal(expected)
```

Running `test_example` results in the following exception:

```
self = <sqlglot.generator.Generator object at 0x107920d60>
expression = ('argMax', 'If'), key = None, comment = True

    def sql(
        self,
        expression: t.Optional[str | exp.Expression],
        key: t.Optional[str] = None,
        comment: bool = True,
    ) -> str:
        if not expression:
            return ""
    
        if isinstance(expression, str):
            return expression
    
        if key:
            value = expression.args.get(key)
            if value:
                return self.sql(value)
            return ""
    
        transform = self.TRANSFORMS.get(expression.__class__)
    
        if callable(transform):
            sql = transform(self, expression)
        elif transform:
            sql = transform
        elif isinstance(expression, exp.Expression):
            exp_handler_name = f"{expression.key}_sql"
    
            if hasattr(self, exp_handler_name):
                sql = getattr(self, exp_handler_name)(expression)
            elif isinstance(expression, exp.Func):
                sql = self.function_fallback_sql(expression)
            elif isinstance(expression, exp.Property):
                sql = self.property_sql(expression)
            else:
                raise ValueError(f"Unsupported expression type {expression.__class__.__name__}")
        else:
>           raise ValueError(f"Expected an Expression. Received {type(expression)}: {expression}")
E           ValueError: Expected an Expression. Received <class 'tuple'>: ('argMax', 'If')
```

We do not see this error for other `xIf` functions (e.g. the `countIf` in the example query) or for functions without it `If` (e.g. plain `argMax`). My initial thought was a change in `sqlglot`, but the version specified in `poetry.lock` has not changed in this release.


Discussion / hints:
@smoothml Just to confirm: With previous versions it was working?

My first thought was that we might have forgotten to add a `dialect` to some sqlglot call somewhere - but I cannot spot anything (apart from that we probably want to add the dialect to[ those test parsings ](https://github.com/DeepLcom/sql-mock/compare/v0.6.0...v0.6.1#diff-33dbc97573e1c4998aa8b0b3ce33cd1ad6bd0cb430fc81ad7a6c57489ce0b412R81)as well)


What I found though is that we forgot to remove a [print statement](https://github.com/DeepLcom/sql-mock/compare/v0.6.0...v0.6.1#diff-652cb1c89729fbb9b66ec7c8c260a583c9a4fb89c0607f46f161805972bf0af9R81).

Fix patch:
diff --git a/CONTRIBUTION.md b/CONTRIBUTION.md
index c3355c5..55d3518 100644
--- a/CONTRIBUTION.md
+++ b/CONTRIBUTION.md
@@ -45,22 +45,42 @@ Once you have Poetry, you can install the project's dependencies:
 poetry install --all-extras
 ```
 
-### 3. Pre-Commit Hooks
+### 3. External Dependencies
 
-This project uses pre-commit hooks to ensure code quality. To install the hooks, run:
+This project uses several external dependencies in the developer workflow. These are:
+
+- [Pre-commit](https://pre-commit.com/) hooks are used to ensure code quality. Install the hooks with
+    ```
+    poetry run pre-commit install
+    ```
+    This will set up the necessary hooks to check code formatting, linting, and other code quality checks before each commit.
+- [Docker](https://www.docker.com/) and [Docker Compose](https://docs.docker.com/compose/) for running integrations tests (see below).
+- [Make](https://www.gnu.org/software/make/) is used as convenience wrapper around some of the more convoluted commands. This is optional as you can always run the commands directly (see the `Makefile` for the actual commands being run).
+
+### 4. Running Tests
+
+We use [pytest](https://docs.pytest.org/en/latest/) for running tests. You can run all the tests with:
 
 ```bash
-poetry run pre-commit install
+make test
 ```
 
-This will set up the necessary hooks to check code formatting, linting, and other code quality checks before each commit.
+There are two types of tests: those testing the internal functions and methods of the library and those testing the test execution with example queries. The latter require a running database instance to be available (either locally or remotely). Note, integration testing is currently only supported for Clickhouse. You can run internal testing only with:
 
-### 4. Running Tests
+```bash
+make test-unit
+```
 
-We use [pytest](https://docs.pytest.org/en/latest/) for running tests. You can run all the tests with:
+Running integration tests locally requires [Docker](https://www.docker.com/) and [Docker Compose](https://docs.docker.com/compose/). First, start the local database services with:
+
+```bash
+docker compose up -d
+```
+
+Then you can run integration tests with:
 
 ```bash
-poetry run pytest tests/
+make test-integration
 ```
 
 ### 5. Environment Variables
diff --git a/Makefile b/Makefile
new file mode 100644
index 0000000..ad0efc1
--- /dev/null
+++ b/Makefile
@@ -0,0 +1,18 @@
+.DEFAULT_GOAL := help
+SHELL := /bin/bash
+
+.PHONY: help
+help: ## Show all available commands
+	@awk 'BEGIN {FS = ":.*##"; printf "Usage: make \033[36m<target>\033[0m\n"} /^[a-zA-Z_-]+:.*?##/ { printf "  \033[36m%-13s\033[0m %s\n", $$1, $$2 } /^##@/ { printf "\n\033[1m%s\033[0m\n", substr($$0, 5) } ' $(MAKEFILE_LIST);
+
+.PHONY: test
+test: ## Run test pipeline
+	poetry run pytest tests/
+
+.PHONY: test-integration
+test-integration: ## Run integration tests
+	poetry run pytest -m "integration" tests/
+
+.PHONY: test-unit
+test-unit: ## Run unit tests
+	poetry run pytest -m "not integration" tests/
\ No newline at end of file
diff --git a/compose.yaml b/compose.yaml
new file mode 100644
index 0000000..be37575
--- /dev/null
+++ b/compose.yaml
@@ -0,0 +1,10 @@
+services:
+  clickhouse:
+    image: clickhouse/clickhouse-server:24.1.5.6
+    ports:
+      - 8123:8123
+      - 9000:9000
+    ulimits:
+      nofile:
+        soft: "262144"
+        hard: "262144"
\ No newline at end of file
diff --git a/pyproject.toml b/pyproject.toml
index 2e961e0..16985f8 100644
--- a/pyproject.toml
+++ b/pyproject.toml
@@ -75,9 +75,12 @@ flake8 = "^6.1.0"
 requires = ["poetry-core>=1.0.0"]
 build-backend = "poetry.core.masonry.api"
 
-# Ignore Google Bigquery namespace deprecation warnings
 [tool.pytest.ini_options]
+# Ignore Google Bigquery namespace deprecation warnings
 filterwarnings = [
     "ignore:Deprecated call to `pkg_resources\\.declare_namespace\\('.*'\\):DeprecationWarning",
     "ignore::DeprecationWarning:google.rpc",
 ]
+markers = [
+    "integration: Integration tests requiring running database instances"
+]


Test patch:
diff --git a/.github/workflows/test-package.yml b/.github/workflows/test-package.yml
index 37ba202..fd81749 100644
--- a/.github/workflows/test-package.yml
+++ b/.github/workflows/test-package.yml
@@ -16,6 +16,12 @@ jobs:
       fail-fast: false
       matrix:
         python-version: ["3.9", "3.10", "3.11"]
+    services:
+      clickhouse:
+        image: clickhouse/clickhouse-server:24.1.5.6
+        ports:
+          - 8123:8123
+          - 9000:9000
 
     steps:
     - uses: actions/checkout@v3
@@ -36,5 +42,9 @@ jobs:
         # exit-zero treats all errors as warnings. The GitHub editor is 127 chars wide
         poetry run flake8 . --count --exit-zero --max-complexity=10 --max-line-length=119 --statistics --ignore E203,E266,E501,W503
     - name: Test with pytest
+      env:
+        SQL_MOCK_CLICKHOUSE_HOST: 127.0.0.1
+        SQL_MOCK_CLICKHOUSE_PORT: 8123
+        SQL_MOCK_CLICKHOUSE_USER: default
       run: |
         poetry run pytest tests/
diff --git a/tests/sql_mock/clickhouse/test_query_execution.py b/tests/sql_mock/clickhouse/test_query_execution.py
new file mode 100644
index 0000000..2a8efbe
--- /dev/null
+++ b/tests/sql_mock/clickhouse/test_query_execution.py
@@ -0,0 +1,57 @@
+import os
+
+import pytest
+
+from sql_mock.clickhouse import column_mocks as col
+from sql_mock.clickhouse.table_mocks import ClickHouseTableMock
+from sql_mock.table_mocks import table_meta
+
+pytestmark = pytest.mark.integration
+
+
+@pytest.fixture(autouse=True)
+def set_env():
+    if not os.getenv("SQL_MOCK_CLICKHOUSE_HOST"):
+        os.environ["SQL_MOCK_CLICKHOUSE_HOST"] = "localhost"
+    if not os.getenv("SQL_MOCK_CLICKHOUSE_PORT"):
+        os.environ["SQL_MOCK_CLICKHOUSE_PORT"] = "8123"
+    if not os.getenv("SQL_MOCK_CLICKHOUSE_USER"):
+        os.environ["SQL_MOCK_CLICKHOUSE_USER"] = "default"
+    if not os.getenv("SQL_MOCK_CLICKHOUSE_PASSWORD"):
+        os.environ["SQL_MOCK_CLICKHOUSE_PASSWORD"] = ""
+
+
+def test_simple_query():
+    query = """SELECT
+        user_id,
+        count() AS sessions
+    FROM sessions
+    GROUP BY user_id
+    """
+
+    @table_meta(table_ref="sessions")
+    class SessionsMock(ClickHouseTableMock):
+        user_id = col.String(default="foo")
+
+    @table_meta(query=query)
+    class ResultMock(ClickHouseTableMock):
+        user_id = col.String(default="foo")
+        sessions = col.Int(default=0)
+
+    sessions_mock = SessionsMock.from_dicts(
+        [
+            {"user_id": "a"},
+            {"user_id": "a"},
+            {"user_id": "a"},
+            {"user_id": "b"},
+        ],
+    )
+
+    result = ResultMock.from_mocks(input_data=[sessions_mock])
+
+    expected = [
+        {"user_id": "a", "sessions": 3},
+        {"user_id": "b", "sessions": 1},
+    ]
+
+    result.assert_equal(expected)