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

SQL Server requires CAST when using NULL in anchor part of recursive CTE

    XMLWordPrintable

Details

    • Bug
    • Resolution: Done
    • Major
    • 9.0, 8.12.5
    • 8.12.x
    • JDBC Connector
    • None

    Description

      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.

      Attachments

        Activity

          People

            rhn-engineering-shawkins Steven Hawkins
            asmigala@redhat.com Andrej Smigala
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: