ERROR: failed to find conversion function from unknown to text

Hi.

select cast(NULL::text as unknown);
ERROR:  failed to find conversion function from unknown to text

I found similar issues in [1] and [2], and both have already been resolved.

Looking at resolveTargetListUnknowns -> coerce_type, it seems it can cope with
transforming a source expression from an Unknown Const to a Text Const. However,
it cannot coerce other Unknown type expressions, such as COERCEVIAIO, to a Text
Const.

It can fail for real table data, not just constant literals, specifically when
you try to cast a text column to an Unknown data type.
While people generally don't do this, it is still possible.

create table t(a text);
select cast(a as unknown) from t;

we don't need to worry about the domain over UNKNOWNOID, since it's not allowed.
seems like coerce_type don't have logic handle targettype as UNKNOWNOID.
in function coerce_type, right above find_coercion_pathway, we can add

    if (targetTypeId == UNKNOWNOID)
    {
        Oid            inputBaseTypeId = getBaseType(inputTypeId);
        TYPCATEGORY s_typcategory = TypeCategory(inputBaseTypeId);

        if (s_typcategory == TYPCATEGORY_STRING)
            return node;
    }

to solve this issue.


[1]: https://www.postgresql.org/message-id/flat/41E555DA.1060707%40gmail.com
[2]: https://postgr.es/m/65937bea0901052223w162a977dyeaaf888a854f7324@mail.gmail.com



--
jian
https://www.enterprisedb.com/