Repository: dbt-labs/dbt-redshift (language: python)

Issue:
[Bug] Unit test infers the wrong data type when the first record has some NULL values
### Is this a new bug in dbt-core?

- [X] I believe this is a new bug in dbt-core
- [X] I have searched the existing issues, and I could not find an existing issue for this bug

### Current Behavior

```yml
unit_tests:
  - name: test_user_events_combined
    model: int_mycfg__user_events_combined
    given:
      - input: ref('stg_mycfg__user_events')
        rows:
          - {
              created_at: "2024-01-01 10:01:00",
              user_id: 1,
              is_closed: null, # <--- if we change both of these null values to true/false, the error goes away.
              is_validated: null, # <--- if we change both of these null values to true/false, the error goes away.
              user_action: created,
            }
          - {
              created_at: "2024-01-01 10:02:00",
              user_id: 1,
              is_closed: null,
              is_validated: true,
              user_action: validated,
            }
      - input: ref('stg_mycfg__user_snapshot')
        rows: []
    expect:
      rows:
        - { user_id: 1, is_closed: true, is_validated: true }
        - { user_id: 2, is_closed: null, is_validated: true }
        - { user_id: 3, is_closed: true, is_validated: null }
        - { user_id: 4, is_closed: null, is_validated: null }
        - { user_id: 5, is_closed: false, is_validated: true }
        - { user_id: 6, is_closed: true, is_validated: true }
```

When you run the test you will see error like this.

<img width="1151" alt="image" src="https://github.com/dbt-labs/dbt-core/assets/324418/9d7dbabf-8499-406f-b840-2793d531be69">

And here is the logs:

<img width="542" alt="image" src="https://github.com/dbt-labs/dbt-core/assets/324418/9ca15240-5b0e-4fda-9bf6-7d505984d8aa">

_Input build_

<img width="591" alt="image" src="https://github.com/dbt-labs/dbt-core/assets/324418/9d84ea6a-82bb-48ed-9fc6-68ea496006db">

_Expected result build_

Interestingly, the incorrect casting happen in the `dbt_internal_unit_test_expected` cte but not the input cte.

---

Our current workaround is to use sql to cast the correct type, eg.

```yml
select
    '2024-01-01 10:01:00' as created_at,
    1 as user_id,
    null::boolean as is_closed,
    null::boolean as is_validated,
    'created' as user_action
union all
select '2024-01-01 10:02:00', 1, null, true, 'validated'
```


### Expected Behavior

The test runner should be able to guess the data type correctly.

### Steps To Reproduce

1. Provide unit test data with some null values in the first record.
2. Run the test
3. ERROR!

### Relevant log output

<img width="542" alt="image" src="https://github.com/dbt-labs/dbt-core/assets/324418/9ca15240-5b0e-4fda-9bf6-7d505984d8aa">

_Input build_

<img width="591" alt="image" src="https://github.com/dbt-labs/dbt-core/assets/324418/9d84ea6a-82bb-48ed-9fc6-68ea496006db">

_Expected result build_

Interestingly, the incorrect casting happen in the `dbt_internal_unit_test_expected` cte but not the input cte.


### Environment

```markdown
- OS: Ubuntu 24.04 WSL
- Python: 3.12.2
- dbt: 1.8.0-rc1
```


### Which database adapter are you using with dbt?

redshift

### Additional Context

_No response_


Discussion / hints:
Thanks for reporting this @leesiongchan !

I wasn't able to replicate this on my first try. Could you try out the files below and make any tweaks needed to trigger the error you saw?

<details>
<summary>
Project files and commands
</summary>

<br />

`models/stg_mycfg__user_events.sql`

```sql
select
    cast('2024-01-01 10:01:00' as timestamp) as created_at,
    1 as user_id,
    true as is_closed,
    false as is_validated,
    'created' as user_action
```

`models/stg_mycfg__user_snapshot.sql`

```sql
select 1 as id
```


`models/int_mycfg__user_events_combined.sql`

```sql
-- {{ ref("stg_mycfg__user_snapshot") }}
select * from {{ ref("stg_mycfg__user_events") }}
```

`models/_unit.yml`

```yaml
unit_tests:
  - name: test_user_events_combined
    model: int_mycfg__user_events_combined
    given:
      - input: ref('stg_mycfg__user_events')
        rows:
          - {
              created_at: "2024-01-01 10:01:00",
              user_id: 1,
              is_closed: true,
              is_validated: true,
              user_action: created,
            }
          - {
              created_at: "2024-01-01 10:02:00",
              user_id: 2,
              is_closed: null,
              is_validated: true,
              user_action: validated,
            }
          - {
              created_at: "2024-01-01 10:01:00",
              user_id: 3,
              is_closed: true,
              is_validated: null,
              user_action: created,
            }
          - {
              created_at: "2024-01-01 10:01:00",
              user_id: 4,
              is_closed: null,
              is_validated: null,
              user_action: created,
            }
          - {
              created_at: "2024-01-01 10:01:00",
              user_id: 5,
              is_closed: false,
              is_validated: true,
              user_action: created,
            }
          - {
              created_at: "2024-01-01 10:01:00",
              user_id: 6,
              is_closed: true,
              is_validated: true,
              user_action: created,
            }
      - input: ref('stg_mycfg__user_snapshot')
        rows: []
    expect:
      rows:
        - { user_id: 1, is_closed: true, is_validated: true }
        - { user_id: 2, is_closed: null, is_validated: true }
        - { user_id: 3, is_closed: true, is_validated: null }
        - { user_id: 4, is_closed: null, is_validated: null }
        - { user_id: 5, is_closed: false, is_validated: true }
        - { user_id: 6, is_closed: true, is_validated: true }
```

Run this command:

```shell
dbt build -s +stg_mycfg__user_events+1
```

</details>
You have to change your first record with some NULL values: eg.

```yml
unit_tests:
  - name: test_user_events_combined
    model: int_mycfg__user_events_combined
    given:
      - input: ref('stg_mycfg__user_events')
        rows:
          - {
              created_at: "2024-01-01 10:01:00",
              user_id: 1,
              is_closed: null,
              is_validated: null,
              user_action: created,
            }
          - {
              created_at: "2024-01-01 10:02:00",
              user_id: 2,
              is_closed: null,
              is_validated: true,
              user_action: validated,
            }
        ...
```
@leesiongchan This worked for me -- is it not working for you?

```yaml
unit_tests:
  - name: test_user_events_combined
    model: int_mycfg__user_events_combined
    given:
      - input: ref('stg_mycfg__user_events')
        rows:
          - {
              created_at: "2024-01-01 10:01:00",
              user_id: 1,
              is_closed: null,
              is_validated: null,
              user_action: created,
            }
          - {
              created_at: "2024-01-01 10:02:00",
              user_id: 2,
              is_closed: null,
              is_validated: true,
              user_action: validated,
            }
          - {
              created_at: "2024-01-01 10:01:00",
              user_id: 3,
              is_closed: true,
              is_validated: null,
              user_action: created,
            }
          - {
              created_at: "2024-01-01 10:01:00",
              user_id: 4,
              is_closed: true,
              is_validated: true,
              user_action: created,
            }
          - {
              created_at: "2024-01-01 10:01:00",
              user_id: 5,
              is_closed: false,
              is_validated: true,
              user_action: created,
            }
          - {
              created_at: "2024-01-01 10:01:00",
              user_id: 6,
              is_closed: true,
              is_validated: true,
              user_action: created,
            }
      - input: ref('stg_mycfg__user_snapshot')
        rows: []
    expect:
      rows:
        - { user_id: 1, is_closed: null, is_validated: null }
        - { user_id: 2, is_closed: null, is_validated: true }
        - { user_id: 3, is_closed: true, is_validated: null }
        - { user_id: 4, is_closed: true, is_validated: true }
        - { user_id: 5, is_closed: false, is_validated: true }
        - { user_id: 6, is_closed: true, is_validated: true }
```
<img width="1461" alt="image" src="https://github.com/dbt-labs/dbt-core/assets/324418/1fbebc54-76b3-4239-a5f3-8ffa0d92d189">

As long as the first record doesn't have any NULL values, the test runs as expected.

<img width="1427" alt="image" src="https://github.com/dbt-labs/dbt-core/assets/324418/ac47897d-d1c3-488f-9072-f7c44aa916ee">

---

Oh... could this be dbt-redshift problem?
Ah! I can get the same error as you now.

It runs fine in dbt-postgres, but gives an error in dbt-redshift. See details below for a simplified example.

<details>
<summary>

### Reprex
</summary>

`models/some_input.sql`

```sql
select
    1 as user_id,
    cast(true as boolean) as is_closed
```

`models/my_model.sql`

```sql
select * from {{ ref("some_input") }}
```

`models/_unit.yml`

```yaml
unit_tests:

  - name: test__my_model
    model: my_model
    given:
      - input: ref('some_input')
        rows:
          - { user_id: 1, is_closed: null }
          - { user_id: 4, is_closed: true }
    expect:
      rows:
        - { user_id: 1, is_closed: null }
        - { user_id: 4, is_closed: true }
```

Run this command to see the error:
```shell
dbt build -s +my_model
```

Output:
```
17:09:03    Runtime Error in unit_test test__my_model (models/_unit.yml)
  An error occurred during execution of unit test 'test__my_model'. There may be an error in the unit test definition: check the data types.
   Database Error
    cannot cast type boolean to character varying
```

But if the order of the given inputs is swapped, then it runs successfully:

`models/_unit.yml`

```yaml
unit_tests:

  - name: test__my_model
    model: my_model
    given:
      - input: ref('some_input')
        rows:
          - { user_id: 4, is_closed: true }
          - { user_id: 1, is_closed: null }
    expect:
      rows:
        - { user_id: 1, is_closed: null }
        - { user_id: 4, is_closed: true }
```

</details>

I'm having a similar error where using redshift using csv's as the input and output 

<img width="885" alt="image" src="https://github.com/dbt-labs/dbt-core/assets/39012113/5964a6da-8ed3-4873-9ddd-95b198c07ed9">

Looking at the expected output 
It's casting something that should be numeric as a character
`cast('0' as character varying(128)) as conversion_value,`

When it should be casting it as whatever the output model schema is 

Thanks for reporting an additional error scenario @jnapoleon 👍

Could you share the relevant example code as well so we can make sure it gets covered by any potential solution?
I tested the dbt project described in https://github.com/dbt-labs/dbt-redshift/issues/821 against a variety of data platforms, and only Redshift failed. So depending on the nature of the solution, we may end up transferring this issue to dbt-redshift.

These all worked:
- dbt-postgres
- dbt-bigquery
- dbt-snowflake
- dbt-databricks
Hey @dbeatty10 I've attached the logs

Let me know if you would rather me create a new issue?
[log.txt](https://github.com/dbt-labs/dbt-redshift/files/15322838/log.txt)

Thanks

John
Thanks for attaching your logs @jnapoleon 👍 

Could you also provide a minimal code example like the "reprex" within https://github.com/dbt-labs/dbt-redshift/issues/821#issuecomment-2108918024? That would allow us to determine if you are having the same issue or a different issue.
@dbeatty10 I managed to resolve the issue, I don't think there was a bug with dbt. 

There was some strange behaviour getting the data types using information schema from the created temporary table. It thought the date type was varchar for the temporary table but in the real final output model it was a numeric

The way I solved it was explicitly casting the fields as numeric in the final output model 
I am also seeing this. Because I'm doing cross-platform unit tests for the audit helper package, I have a diff of what snowflake generated vs redshift: 
<img width="1635" alt="image" src="https://github.com/dbt-labs/dbt-redshift/assets/7335046/2bcf6b4f-d629-497f-9f03-024edf3c66c7">

When nulls are present alongside numeric values, dbt is casting everything as a varchar(1), which makes unions between expected and actual impossible. 

Here's the full file that ran in [this CI run](https://app.circleci.com/pipelines/github/dbt-labs/dbt-audit-helper/413/workflows/97f91684-a874-422c-a943-59e0fee85e98/jobs/406/steps)
[reworked_compare_identical_tables_multiple_null_pk.txt](https://github.com/dbt-labs/dbt-redshift/files/15359952/reworked_compare_identical_tables_multiple_null_pk.txt)

In my case, the input rows lead with the null but the expected output has a non-null value first. 

```yml
unit_tests:
  - name: reworked_compare_identical_tables_multiple_null_pk
    model: unit_reworked_compare
    
    given:
      - input: ref('unit_test_model_a')
        rows:
          - { "id": , "col1": "abc", "col2": "def" }
          - { "id": , "col1": "hij", "col2": "klm" }
          - { "id": 3, "col1": "nop", "col2": "qrs" }
      - input: ref('unit_test_model_b')
        rows:
          - { "id": , "col1": "abc", "col2": "def" }
          - { "id": , "col1": "hij", "col2": "klm" }
          - { "id": 3, "col1": "nop", "col2": "qrs" }
        
    expect:
      rows:
        - {"dbt_audit_row_status": 'identical', 'id': 3, dbt_audit_num_rows_in_status: 1}
        - {"dbt_audit_row_status": 'nonunique_pk', 'id': , dbt_audit_num_rows_in_status: 2}
        - {"dbt_audit_row_status": 'nonunique_pk', 'id': , dbt_audit_num_rows_in_status: 2}

    overrides:
      vars:
        reworked_compare__columns: ['id', 'col1', 'col2']
        reworked_compare__event_time:
        reworked_compare__primary_key_columns: ['id']
```
I made a little reprex for Redshift 💟 This will serve as a test in the PR to be 

Note this _does_ work on Postgres!

```sql
-- models/staging/a.sql
{{ config(materialized="table") }}

select 1 as id, 'a' as col1
union all
select 2, 'b'
union all
select 3, 'c'
```

```sql
-- models/staging/b.sql
{{config(materialized="table")}}

select * from {{ ref('a') }}
```

```yaml
unit_tests:
  - name: test_simple

    model: b
    given:
      - input: ref('a')
        rows:
          - { "id":  , "col1": "d"}
          - { "id": , "col1": "e"}
          - { "id": 6, "col1": "f"}

    expect:
      rows:
        - {id:  , "col1": "d"}
        - {id:  , "col1": "e"}
        - {id: 6, "col1": "f"}

```

Fix patch:
diff --git a/.changes/unreleased/Fixes-20240625-170324.yaml b/.changes/unreleased/Fixes-20240625-170324.yaml
new file mode 100644
index 000000000..316a92b95
--- /dev/null
+++ b/.changes/unreleased/Fixes-20240625-170324.yaml
@@ -0,0 +1,7 @@
+kind: Fixes
+body: 'Handle unit test fixtures where typing goes wrong from first value in column
+  being Null. '
+time: 2024-06-25T17:03:24.73937-07:00
+custom:
+  Author: versusfacit
+  Issue: "821"


Test patch:
diff --git a/tests/functional/adapter/unit_testing/fixtures.py b/tests/functional/adapter/unit_testing/fixtures.py
new file mode 100644
index 000000000..36212dff3
--- /dev/null
+++ b/tests/functional/adapter/unit_testing/fixtures.py
@@ -0,0 +1,73 @@
+model_none_value_base = """
+{{ config(materialized="table") }}
+
+select 1 as id, 'a' as col1
+"""
+
+model_none_value_model = """
+{{config(materialized="table")}}
+
+select * from {{ ref('none_value_base') }}
+"""
+
+
+test_none_column_value_doesnt_throw_error_csv = """
+unit_tests:
+  - name: test_simple
+
+    model: none_value_model
+    given:
+      - input: ref('none_value_base')
+        format: csv
+        rows: |
+          id,col1
+          ,d
+          ,e
+          6,f
+
+    expect:
+        format: csv
+        rows: |
+          id,col1
+          ,d
+          ,e
+          6,f
+"""
+
+test_none_column_value_doesnt_throw_error_dct = """
+unit_tests:
+  - name: test_simple
+
+    model: none_value_model
+    given:
+      - input: ref('none_value_base')
+        rows:
+          - { "id":  , "col1": "d"}
+          - { "id":  , "col1": "e"}
+          - { "id": 6, "col1": "f"}
+
+    expect:
+      rows:
+        - {id:  , "col1": "d"}
+        - {id:  , "col1": "e"}
+        - {id: 6, "col1": "f"}
+"""
+
+test_none_column_value_will_throw_error = """
+unit_tests:
+  - name: test_simple
+
+    model: none_value_model
+    given:
+      - input: ref('none_value_base')
+        rows:
+          - { "id":  , "col1": "d"}
+          - { "id":  , "col1": "e"}
+          - { "id": 6, "col1": }
+
+    expect:
+      rows:
+        - {id:  , "col1": "d"}
+        - {id:  , "col1": "e"}
+        - {id: 6, "col1": }
+"""
diff --git a/tests/functional/adapter/unit_testing/test_unit_testing.py b/tests/functional/adapter/unit_testing/test_unit_testing.py
index 4d89c4b08..f54a22a88 100644
--- a/tests/functional/adapter/unit_testing/test_unit_testing.py
+++ b/tests/functional/adapter/unit_testing/test_unit_testing.py
@@ -1,7 +1,19 @@
 import pytest
+
+from dbt.exceptions import ParsingError
+from dbt.tests.fixtures.project import write_project_files
+from dbt.tests.util import run_dbt
+
 from dbt.tests.adapter.unit_testing.test_types import BaseUnitTestingTypes
 from dbt.tests.adapter.unit_testing.test_case_insensitivity import BaseUnitTestCaseInsensivity
 from dbt.tests.adapter.unit_testing.test_invalid_input import BaseUnitTestInvalidInput
+from tests.functional.adapter.unit_testing.fixtures import (
+    model_none_value_base,
+    model_none_value_model,
+    test_none_column_value_doesnt_throw_error_csv,
+    test_none_column_value_doesnt_throw_error_dct,
+    test_none_column_value_will_throw_error,
+)
 
 
 class TestRedshiftUnitTestingTypes(BaseUnitTestingTypes):
@@ -34,6 +46,47 @@ def data_types(self):
         ]
 
 
+class RedshiftUnitTestingNone:
+    def test_nones_handled_dict(self, project):
+        run_dbt(["build"])
+
+
+class TestRedshiftUnitTestCsvNone(RedshiftUnitTestingNone):
+    @pytest.fixture(scope="class")
+    def models(self):
+        return {
+            "none_value_base.sql": model_none_value_base,
+            "none_value_model.sql": model_none_value_model,
+            "__properties.yml": test_none_column_value_doesnt_throw_error_csv,
+        }
+
+
+class TestRedshiftUnitTestDictNone(RedshiftUnitTestingNone):
+    @pytest.fixture(scope="class")
+    def models(self):
+        return {
+            "none_value_base.sql": model_none_value_base,
+            "none_value_model.sql": model_none_value_model,
+            "__properties.yml": test_none_column_value_doesnt_throw_error_dct,
+        }
+
+
+class TestRedshiftUnitTestingTooManyNonesFails:
+    @pytest.fixture(scope="class")
+    def models(self):
+        return {
+            "__properties.yml": test_none_column_value_will_throw_error,
+            "none_value_base.sql": model_none_value_base,
+            "none_value_model.sql": model_none_value_model,
+        }
+
+    def test_invalid_input(self, project):
+        with pytest.raises(ParsingError) as e:
+            run_dbt(["build"])
+
+        assert "Unit Test fixtures require at least one row free of None" in str(e)
+
+
 class TestRedshiftUnitTestCaseInsensitivity(BaseUnitTestCaseInsensivity):
     pass