Running the following query:

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

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

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):

  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

• SOURCE SRC COMMAND after 6.4.3 (not working, note the only difference is casting to nvarchar):

  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

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):

  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

• SOURCE SRC COMMAND after 6.4.3 (not working, note the only difference is call to TO_NCHAR):

  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