Type mismatch when issuing recursive common table expression query ------------------------------------------------------------------ Key: TEIID-5377 URL: https://issues.jboss.org/browse/TEIID-5377 Project: Teiid Issue Type: Bug Components: Query Engine Affects Versions: 8.12.14.6_4 Reporter: Andrej Šmigala Assignee: Steven Hawkins Priority: Critical Running the following query: {code:sql} WITH tmp_cte(id, name, fk, fkname, lvl) AS (SELECT id, name, fk, cast(NULL as string) as fkname, 0 as lvl FROM SourceModel.cte_source WHERE fk IS NULL UNION ALL SELECT e.id, e.name, e.fk, ecte.name as fkname, lvl + 1 as lvl FROM SourceModel.cte_source AS e INNER JOIN tmp_cte AS ecte ON ecte.id = e.fk ) SELECT * FROM tmp_cte order by lvl {code} fails when run against an Oracle or MS SQL server (all versions). This issue only occurs with DV 6.4.3 CR1, not with previous versions. Probably caused by fix for TEIID-5313 h4. For MS SQL Server: * name column in source is defined as {{varchar(255)}} * name column in VDB is defined as {{string(4000)}} * query plan same before and after 6.4.3 * SOURCE SRC COMMAND before 6.4.3 (working): {code:sql}WITH tmp_cte (id, name, fk, fkname, lvl) AS (SELECT cast(g_0.id AS int), cast(g_0.name AS varchar(4000)), cast(g_0.fk AS int), cast(NULL AS varchar(4000)), cast(0 AS int) FROM cte_source g_0 WHERE g_0.fk IS NULL UNION ALL SELECT cast(g_0.id AS int), cast(g_0.name AS varchar(4000)), cast(g_0.fk AS int), cast(g_1.name AS varchar(4000)), cast((g_1.lvl + 1) AS int) FROM cte_source g_0, tmp_cte g_1 WHERE g_1.id = g_0.fk) SELECT g_0.id AS c_0, g_0.name AS c_1, g_0.fk AS c_2, g_0.fkname AS c_3, g_0.lvl AS c_4 FROM tmp_cte g_0 ORDER BY c_4{code} * SOURCE SRC COMMAND after 6.4.3 (not working, note the only difference is casting to nvarchar): {code:sql}WITH tmp_cte (id, name, fk, fkname, lvl) AS (SELECT cast(g_0.id AS int), cast(g_0.name AS varchar(4000)), cast(g_0.fk AS int), cast(NULL AS varchar(4000)), cast(0 AS int) FROM cte_source g_0 WHERE g_0.fk IS NULL UNION ALL SELECT cast(g_0.id AS int), cast(g_0.name AS varchar(4000)), cast(g_0.fk AS int), cast(g_1.name AS nvarchar(4000)), cast((g_1.lvl + 1) AS int) FROM cte_source g_0, tmp_cte g_1 WHERE g_1.id = g_0.fk) SELECT g_0.id AS c_0, g_0.name AS c_1, g_0.fk AS c_2, g_0.fkname AS c_3, g_0.lvl AS c_4 FROM tmp_cte g_0 ORDER BY c_4{code} h4. For Oracle: * name column in source is defined as {{varchar2(255)}} * name column in VDB is defined as {{string(4000)}} * query plan same before and after 6.4.3 * SOURCE SRC COMMAND before 6.4.3 (working): {code:sql}WITH tmp_cte (id, name, fk, fkname, lvl) AS (SELECT g_0.id, g_0.name, g_0.fk, NULL, 0 FROM cte_source g_0 WHERE g_0.fk IS NULL UNION ALL SELECT g_0.id, g_0.name, g_0.fk, g_1.name, (g_1.lvl + 1) FROM cte_source g_0, tmp_cte g_1 WHERE g_1.id = g_0.fk) SELECT g_0.id AS c_0, g_0.name AS c_1, g_0.fk AS c_2, g_0.fkname AS c_3, g_0.lvl AS c_4 FROM tmp_cte g_0 ORDER BY c_4{code} * SOURCE SRC COMMAND after 6.4.3 (not working, note the only difference is call to {{TO_NCHAR}}): {code:sql}WITH tmp_cte (id, name, fk, fkname, lvl) AS (SELECT g_0.id, g_0.name, g_0.fk, TO_NCHAR(NULL), 0 FROM cte_source g_0 WHERE g_0.fk IS NULL UNION ALL SELECT g_0.id, g_0.name, g_0.fk, g_1.name, (g_1.lvl + 1) FROM cte_source g_0, tmp_cte g_1 WHERE g_1.id = g_0.fk) SELECT g_0.id AS c_0, g_0.name AS c_1, g_0.fk AS c_2, g_0.fkname AS c_3, g_0.lvl AS c_4 FROM tmp_cte g_0 ORDER BY c_4{code}