Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-5377

Type mismatch when issuing recursive common table expression query

    Details

      Description

      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

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  shawkins Steven Hawkins
                  Reporter:
                  asmigala Andrej Šmigala
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  4 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: