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

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-5377?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13592175#comment-13592175 ] 

Steven Hawkins edited comment on TEIID-5377 at 6/15/18 10:23 AM:
-----------------------------------------------------------------

This is a regression from TEIID-5313 affecting rcte, but not regular unions.  It affects master as well.


was (Author: shawkins):
This is a regression from TEIID-5313 affecting rcte, but not regular unions.

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