[Bug] Unit Testing compilation error

[Kartikeya-Tiwari]

Is this a new bug in dbt-core?

• I believe this is a new bug in dbt-core
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

I am using dbt core version 1.8.2 with dbt-greenplum adapter version 1.8.2
Link for dbt-greenplum adapter - https://github.com/greenplum-db/dbt-greenplum

When I run a unit test I keep getting the below error -
"
There may be an error in the unit test definition: check the data types.
Database Error
failed to find conversion function from unknown to text (parse_coerce.c:587)
"

Expected Behavior

The unit test should have run as they do on all other DW.

Steps To Reproduce

Mart - models/marts/mart_for_unit_test.sql

WITH account_mth AS (
  SELECT * FROM {{ ref('stg_account_mth') }}
),

final AS (
  SELECT
    as_of_dt,
    CASE
        WHEN (CAST(as_of_dt AS DATE)) > CAST('1990-01-01' AS DATE) THEN CAST('good' AS TEXT)
        ELSE CAST('bad' AS TEXT)
    END AS date_status
  FROM account_mth
)

SELECT * FROM final

Unit test - /models/__unit_test_1.yml

unit_tests:
  - name: first_unit_test 
    model: mart_for_unit_test
    given: 
      - input: ref('stg_account_mth')
        rows:
          - {as_of_dt: 2023-01-01}
          - {as_of_dt: 1989-12-31}
    expect: 
      rows:
        - {as_of_dt: 2023-01-01, date_status: 'good'}
        - {as_of_dt: 1989-12-31, date_status: 'bad'}  

Command
dbt test --select "mart_for_unit_test, test_type:unit"

Log
Error 
Runtime Error in unit_test first_unit_test (models/__unit_test_1.yml)
An error occurred during execution of unit test 'first_unit_test'. There may be an error in the unit test definition: check the data types.
Database Error
failed to find conversion function from unknown to text (parse_coerce.c:587)

Compiled Code for unit test

-- Build actual result given inputs
with dbt_internal_unit_test_actual as (
  select
    as_of_dt,date_status, 'actual' as "actual_or_expected"
  from (
    WITH  __dbt__cte__stg_account_mth as (

-- Fixture for stg_account_mth
select cast(null as text) as acct_id, cast(null as text) as acct_nbr, cast(null as text) as plp_id, 
    
    cast('2023-01-01' as date)
 as as_of_dt, cast(null as text) as mad_p_typ, cast(null as text) as mad_s_typ
union all
select cast(null as text) as acct_id, cast(null as text) as acct_nbr, cast(null as text) as plp_id, 
    
    cast('1989-12-31' as date)
 as as_of_dt, cast(null as text) as mad_p_typ, cast(null as text) as mad_s_typ
), account_mth AS (
  SELECT * FROM __dbt__cte__stg_account_mth
),

final AS (
  SELECT
    as_of_dt,
    CASE
        WHEN (CAST(as_of_dt AS DATE)) > CAST('1990-01-01' AS DATE) THEN CAST('good' AS TEXT)
        ELSE CAST('bad' AS TEXT)
    END AS date_status
  FROM account_mth
)

SELECT * FROM final
  ) _dbt_internal_unit_test_actual
),
-- Build expected result
dbt_internal_unit_test_expected as (
  select
    as_of_dt, date_status, 'expected' as "actual_or_expected"
  from (
    select 
    
    cast('2023-01-01' as date)
 as as_of_dt, 
    
    cast('good' as text)
 as date_status
union all
select 
    
    cast('1989-12-31' as date)
 as as_of_dt, 
    
    cast('bad' as text)
 as date_status
  ) _dbt_internal_unit_test_expected
)
-- Union actual and expected results
select * from dbt_internal_unit_test_actual
union all
select * from dbt_internal_unit_test_expected

Error when running the compiled code in Dbeaver
SQL Error [XX000]: ERROR: failed to find conversion function from unknown to text (parse_coerce.c:587)

What was changed to fix and have the query run in dbeaver
Update these lines

as_of_dt,date_status, 'actual' as "actual_or_expected"
to 
as_of_dt,date_status, CAST('actual' AS TEXT) AS "actual_or_expected"

and

as_of_dt, date_status, 'expected' as "actual_or_expected"
to 
as_of_dt, date_status, CAST('expected' AS TEXT) AS "actual_or_expected"

Relevant log output

Runtime Error in unit_test first_unit_test (models/__unit_test_1.yml)
  An error occurred during execution of unit test 'first_unit_test'. There may be an error in the unit test definition: check the data types.
   Database Error
    failed to find conversion function from unknown to text (parse_coerce.c:587)

Environment

- OS:Debian (Image being used - dockerhub/python:3.11-bullseye)
- Python:3.11.11
- dbt:1.8.2

more details - 
Core:
  - installed: 1.8.2
  - latest:    1.9.2 - Update available!

Plugins:
  - greenplum: 1.8.2 - Ahead of latest version!

Which database adapter are you using with dbt?

other (mention it in "Additional Context")

Additional Context

Adapter being used - dbt-Greenplum.
Not the community built one, and rather the official dbt-Greenplum adapter maintained by Broadcom
link - https://github.com/greenplum-db/dbt-greenplum