Repository: dbeatty10/dbt-mysql (language: python)

Issue:
Bug in macro incremental_delete when using composite keys for unique_key
### Describe the bug
When using array of columns for `unique_key` in incremental materialization, the macro `incremental_delete` chokes and the following error is thrown, using MySQL 8.
I'm using code from main branch.


Example of model:
```
{{ config(materialized="incremental", unique_key=["actor_id", "film_id"]) }}
select * from {{ source('raw', 'seed') }}
```

Example of seed:
```
actor_id,film_id,some_date
1,1,1981-05-20T06:46:51
2,1,1978-09-03T18:10:33
3,1,1982-03-11T03:59:51
```

Exception thrown when running `dbt run`:
```
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '['actor_id', 'film_id']) in (
ERROR    configured_std_out:functions.py:236 20:04:01    1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '['actor_id', 'film_id']) in (
```

### Steps To Reproduce

```
git clone https://github.com/lpezet/dbt-mysql.git
git checkout fix-incremental-composite-keys
# get setup for integration tests
docker-compose up -d
PYTHONPATH=. pytest -v --profile mysql tests/functional/adapter/test_basic.py::TestIncrementalCompositeKeysMySQL::test_incremental_with_composite_keys
docker-compose down
```

### Expected behavior
Exception not thrown and rows from seed deleted and then re-inserted without creating duplicates.

**The operating system you're using:**
Linux GoldenHind 5.15.90.1-microsoft-standard-WSL2 #1 SMP Fri Jan 27 02:56:13 UTC 2023 x86_64 x86_64 x86_64 GNU/Linux

**The output of `python --version`:**
Python 3.8.10

MySQL Server 8.0.29

### Additional context
The branch `fix-incremental-composite-keys` actually contains the test to ,well, test incremental materialization with composite keys.
I couldn't find the equivalent in `dbt-tests-adapter` code...strangely...I must have missed something. I therefore created the class `tests/functional/adapter/incremental_composite.py` based on [dbt-tests-adapter's very own BaseIncremental class](https://github.com/dbt-labs/dbt-tests-adapter/blob/main/dbt/tests/adapter/basic/test_incremental.py) and added a test in `tests/functional/adapter/test_basic.py` called `TestIncrementalCompositeKeysMySQL`.

The problem, I believe, is in 2 parts:
* The use of parenthesis **()** in the `select ( {{ unique_key }} )` to get values for the `in ()`.
* The  use of **{{ unique_key }}** directly as it will resolve in "[ key1, key2, ... ]" (an array representation) when an array is specified in the model like " {{ config( ..., unique_key=[ "key1", "key2", ...] }}". [This seems possible since dbt-core 1.1](https://docs.getdbt.com/docs/build/incremental-models#defining-a-uniqueness-constraint-optional:~:text=The%20unique_key%20should%20be%20supplied%20in%20your%20model%20definition%20as%20a%20string%20representing%20a%20single%20column%20or%20a%20list%20of%20single%2Dquoted%20column%20names%20that%20can%20be%20used%20together%2C%20for%20example%2C%20%5B%27col1%27%2C%20%27col2%27%2C%20%E2%80%A6%5D).

I believe the following would fix the issue:
```
{% macro incremental_delete(tmp_relation, target_relation, unique_key=none, statement_name="pre_main") %}
    {%- if unique_key is not none -%}
    delete
    from {{ target_relation }}
    where ({{ unique_key | join(',') }}) in (
        select {{ unique_key | join(',') }}
        from {{ tmp_relation }}
    )
    {%- endif %}

{%- endmacro %}
```

NB: No *GPT has been harmed, or even used!, during the creation of all this. Just lots of time spent figuring out dbt-tests-adapter and dbt-mysql, git-ing, coding, and typing this very long ticket. Just like old (?) times ;)




Discussion / hints:

Fix patch:
diff --git a/CHANGELOG.md b/CHANGELOG.md
index 19bc248..d79f1bf 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -3,8 +3,14 @@
 ### Features
 - Support dbt v1.4 ([#146](https://github.com/dbeatty10/dbt-mysql/pull/146))
 
+### Fixes
+- Fix incremental composite keys ([#144](https://github.com/dbeatty10/dbt-mysql/issues/144))
+
 ### Contributors
-- [@lpezet](https://github.com/lpezet) ([#146](https://github.com/dbeatty10/dbt-mysql/pull/146))
+- [@lpezet](https://github.com/lpezet) ([#146](https://github.com/dbeatty10/dbt-mysql/pull/146), [#144](https://github.com/dbeatty10/dbt-mysql/issues/144))
+- [@moszutij](https://github.com/moszutij) ([#146](https://github.com/dbeatty10/dbt-mysql/pull/146), [#144](https://github.com/dbeatty10/dbt-mysql/issues/144))
+- [@wesen](https://github.com/wesen) ([#146](https://github.com/dbeatty10/dbt-mysql/pull/146))
+
 
 ## dbt-mysql 1.1.0 (Feb 5, 2023)
 
diff --git a/dbt/include/mariadb/macros/materializations/incremental/helpers.sql b/dbt/include/mariadb/macros/materializations/incremental/helpers.sql
index baa999f..0a8a6e2 100644
--- a/dbt/include/mariadb/macros/materializations/incremental/helpers.sql
+++ b/dbt/include/mariadb/macros/materializations/incremental/helpers.sql
@@ -1,10 +1,10 @@
 
 {% macro incremental_delete(tmp_relation, target_relation, unique_key=none, statement_name="pre_main") %}
-    {%- if unique_key is not none -%}
+    {%- if unique_key is not none and unique_key|length -%}
     delete
     from {{ target_relation }}
-    where ({{ unique_key }}) in (
-        select ({{ unique_key }})
+    where ({{ unique_key if unique_key is string else unique_key | join(',') }}) in (
+        select {{ unique_key if unique_key is string else unique_key | join(',') }}
         from {{ tmp_relation }}
     )
     {%- endif %}
diff --git a/dbt/include/mysql/macros/materializations/incremental/helpers.sql b/dbt/include/mysql/macros/materializations/incremental/helpers.sql
index baa999f..0a8a6e2 100644
--- a/dbt/include/mysql/macros/materializations/incremental/helpers.sql
+++ b/dbt/include/mysql/macros/materializations/incremental/helpers.sql
@@ -1,10 +1,10 @@
 
 {% macro incremental_delete(tmp_relation, target_relation, unique_key=none, statement_name="pre_main") %}
-    {%- if unique_key is not none -%}
+    {%- if unique_key is not none and unique_key|length -%}
     delete
     from {{ target_relation }}
-    where ({{ unique_key }}) in (
-        select ({{ unique_key }})
+    where ({{ unique_key if unique_key is string else unique_key | join(',') }}) in (
+        select {{ unique_key if unique_key is string else unique_key | join(',') }}
         from {{ tmp_relation }}
     )
     {%- endif %}
diff --git a/dbt/include/mysql5/macros/materializations/incremental/helpers.sql b/dbt/include/mysql5/macros/materializations/incremental/helpers.sql
index baa999f..0a8a6e2 100644
--- a/dbt/include/mysql5/macros/materializations/incremental/helpers.sql
+++ b/dbt/include/mysql5/macros/materializations/incremental/helpers.sql
@@ -1,10 +1,10 @@
 
 {% macro incremental_delete(tmp_relation, target_relation, unique_key=none, statement_name="pre_main") %}
-    {%- if unique_key is not none -%}
+    {%- if unique_key is not none and unique_key|length -%}
     delete
     from {{ target_relation }}
-    where ({{ unique_key }}) in (
-        select ({{ unique_key }})
+    where ({{ unique_key if unique_key is string else unique_key | join(',') }}) in (
+        select {{ unique_key if unique_key is string else unique_key | join(',') }}
         from {{ tmp_relation }}
     )
     {%- endif %}


Test patch:
diff --git a/tests/functional/adapter/test_basic.py b/tests/functional/adapter/test_basic.py
index 8505b1c..8150f13 100644
--- a/tests/functional/adapter/test_basic.py
+++ b/tests/functional/adapter/test_basic.py
@@ -1,5 +1,9 @@
 import pytest
 
+from dbt.tests.adapter.incremental.test_incremental_unique_id import (
+    BaseIncrementalUniqueKey,
+)
+
 from dbt.tests.adapter.basic.test_base import BaseSimpleMaterializations
 from dbt.tests.adapter.basic.test_singular_tests import BaseSingularTests
 from dbt.tests.adapter.basic.test_singular_tests_ephemeral import (
@@ -47,6 +51,10 @@ class TestIncrementalMySQL(BaseIncremental):
     pass
 
 
+class TestIncrementalUniqueKey(BaseIncrementalUniqueKey):
+    pass
+
+
 class TestGenericTestsMySQL(BaseGenericTests):
     pass