Redshift regression: failed to find conversion function from "unknown" to numeric

Question

On `Redshift 1.0.60353`, I'm getting the following error for a query that previously worked:
```
SQL Error [XX000]: ERROR: failed to find conversion function from "unknown" to numeric
```
According to [this list](https://docs.aws.amazon.com/redshift/latest/mgmt/rsql-query-tool-error-codes.html), `XX000` is an internal error.

Using `DBeaver 23.2.5`, I am able to reproduce the error with the following query:
```
with
    a as (select cast(0 as decimal(15, 4)) as test),
    b as (select null as test),
    c as (
        select * from a
        union all
        select * from b
    )
select * from c
```

Answer

The error you're encountering in Redshift, ERROR: failed to find conversion function from "unknown" to numeric, suggests that the query engine is having difficulty inferring the correct data type for the null value in your UNION query.

In your query, you have two Common Table Expressions (CTEs), a and b. a returns a column test of type decimal(15, 4), while b returns a column test with null values but doesn't explicitly define the data type for null. When you try to UNION these two CTEs in c, Redshift struggles to reconcile the unknown data type of the null in b with the decimal(15, 4) type in a.

To resolve this, you should explicitly cast the null in CTE b to the same data type as the corresponding column in CTE a.

```
with
    a as (select cast(0 as decimal(15, 4)) as test),
    b as (select cast(null as decimal(15, 4)) as test),
    c as (
        select * from a
        union all
        select * from b
    )
select * from c;

```

Always ensure that your queries explicitly define data types, especially when dealing with null values and set operations like UNION

Redshift regression: failed to find conversion function from "unknown" to numeric

Relevant content