[teiid-issues] [JBoss JIRA] (TEIID-5377) Type mismatch when issuing recursive common table expression query

[Andrej Šmigala (JIRA)]

Andrej Šmigala created TEIID-5377:
-------------------------------------

             Summary: 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}




--
This message was sent by Atlassian JIRA
(v7.5.0#75005)