Case when with aggregated subquery

[mtawk]

Hello,

I migrated lately from Teiid 9 to Teiid 10.2.1.

I have the below simplified query using oracle translator that works with Teiid 9 but not with Teiid 10.2.1

The issue happens when having a case when expression using a string field from main query and another from an aggregated subquery.

If aggregation is removed from subquery, it executes successfully.

select CASE WHEN  ( "Product_Sub"."product_sub" IS null )

        THEN "Product"."Product"

        ELSE "Product_Sub"."product_sub"  END  as "CaseField1"

from "orcldbdataositModel_WZ"."OrclSchema"."Product" "Product"

,(select  "Product_sub"."Product"  as "product_sub", count(1)

from "orcldbdataositModel_WZ"."OrclSchema"."Product" "Product_sub"

group by "Product_sub"."Product") "Product_Sub" 

For some reason, it is considering that "Product"."Product" and "Product_Sub"."product_sub"  are from different data types even though they are coming from the same table.

So the case is written in jdbc like that: CASE WHEN v_0.c_0 IS NULL THEN TO_NCHAR(g_0."Product") ELSE v_0.c_0 END

the use of TO_NCHAR is resulting the oracle error: SQLException: ORA-12704: character set mismatch

CASE WHEN v_0.c_0 IS NULL THEN TO_NCHAR(g_0."Product") ELSE v_0.c_0 END

[shawkins]

What is the native type for product and is that captured in the teiid metadata?

[shawkins]

If it's typed as varchar, then you likely need [TEIID-5377] Type mismatch when issuing recursive common table expression query - JBoss Issue Tracker

Can you try 10.2.3 or later?

[mtawk]

The native Oracle type of product is varchar2(255)

I applied the fix done in Teiid-5377 and it solved the problem