Running the following query in teiid:

with a (intkey, stringcolumn, lvl) as

    (

    select intkey, NULL as stringcolumn, 0 as lvl from bqt1.smallb where intkey = 1

    union all 

    select n.intkey, n.stringkey as stringcolumn, rcte.lvl + 1 as lvl from bqt1.smallb n inner join a rcte on n.intkey = rcte.intkey + 1

    )

 select * from a

results in the following source query:

WITH a (intkey, stringcolumn, lvl) AS

    (

    SELECT SmallB.IntKey, NULL AS stringcolumn, 0 AS lvl FROM SmallB WHERE SmallB.IntKey = 1

    UNION ALL

    SELECT n.IntKey, n.StringKey AS stringcolumn, (rcte.lvl + 1) AS lvl FROM SmallB n INNER JOIN a rcte ON n.IntKey = (rcte.intkey + 1)

    )

SELECT a.intkey, a.stringcolumn, a.lvl FROM a

which fails on SQL Server with

Types don't match between the anchor and the recursive part in column "stringcolumn" of recursive query "a".


The source query should be

WITH a (intkey, stringcolumn, lvl) AS

    (

    SELECT SmallB.IntKey, CAST(NULL AS VARCHAR(10)) AS stringcolumn, 0 AS lvl FROM SmallB WHERE SmallB.IntKey = 1

    UNION ALL

    SELECT n.IntKey, n.StringKey AS stringcolumn, (rcte.lvl + 1) AS lvl FROM SmallB n INNER JOIN a rcte ON n.IntKey = (rcte.intkey + 1)

    )

SELECT a.intkey, a.stringcolumn, a.lvl FROM a

i.e. the NULL has to be cast to the precise type of the column in the recursive part of the query.