Types Don't Match between the Anchor and the Recursive Part in Column...

• Download source - 895 B

The Problem

A quite common requirement is to introduce a path column in a recursive CTE query. With a path, I mean a column which gathers information from the previous levels in the same hierarchy. An easy way to do this is to concatenate data from previous levels to the current row.

For example, if you would need to present locations hierarchically, you could have the following table:

CREATE TABLE Locations (
   Id           int,
   ParentId     int,
   LocationType varchar(100),
   LocationName varchar(100)
)

and the data:

INSERT INTO Locations (Id, ParentId, LocationType, LocationName) VALUES
(1,  null, 'Continent', 'Europe'),
(2,  1,    'Country',   'France'),
(3,  2,    'City',      'Paris'),
(4,  2,    'City',      'Marseille'),
(5,  2,    'City',      'Lyon'),
(6,  1,    'Country',   'Italy'),
(7,  6,    'City',      'Rome'),
(8,  6,    'City',      'Milan'),
(9,  6,    'City',      'Venice'),
(10, 1,    'Country',   'United Kingdom'),
(11, 10,   'City',      'London'),
(12, 10,   'City',      'Cambridge'),
(13, 10,   'City',      'Bath');

The requirement could contain that you need to show for each city, where it is located (continent and country). We can do this with a small recursive CTE like the following:

-- This statement fails with error:
-- Types don't match between the anchor and the recursive part in column 
-- "FullLocationType" of recursive query "Places".
WITH Places (Id, ParentId, LocationName, Level, FullLocationType, FullLocationName) AS (
   SELECT l.Id,
          l.ParentId,
          l.LocationName,
          1,
          l.LocationType,
          l.LocationName
   FROM Locations l
   WHERE l.ParentId IS NULL
   UNION ALL
   SELECT l.Id,
          l.ParentId,
          l.LocationName,
          p.Level + 1,
          CONCAT(p.FullLocationType, ' / ', l.LocationType),
          CONCAT(p.FullLocationName, ' / ', l.LocationName)
   FROM Locations l
   INNER JOIN Places p ON p.Id = l.ParentId
)
SELECT  p.Level,
        p.FullLocationType,
        p.FullLocationName
FROM Places P
ORDER BY p.Id;

But when running this query, you'll end up receiving an error message:

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

Why is this? As we can see, both parts of the recursive query contain a varchar column for FullLocationType.

The thing is that even though the actual data type matches, for character types also the length must match. The definition for the column was varchar(100) but the concatenation produces a string with maximum data length.

So an easy fix is to convert the initial data to the same length as the concatenation produces. To do this, you can use CAST and CONVERT functions. Here's the modified query:

WITH Places (Id, ParentId, LocationName, Level, FullLocationType, FullLocationName) AS (
   SELECT l.Id,
          l.ParentId,
          l.LocationName,
          1,
          CAST(l.LocationType AS varchar(max)),
          CAST(l.LocationName AS varchar(max))
   FROM Locations l
   WHERE l.ParentId IS NULL
   UNION ALL
   SELECT l.Id,
          l.ParentId,
          l.LocationName,
          p.Level + 1,
          CONCAT(p.FullLocationType, ' / ', LocationType),
          CONCAT(p.FullLocationName, ' / ', l.LocationName)
   FROM Locations l
   INNER JOIN Places p ON p.Id = l.ParentId
)
SELECT  p.Level,
        p.FullLocationType,
        p.FullLocationName
FROM Places P
ORDER BY p.Id;

When executed, the query now happily returns the results:

Level   FullLocationType             FullLocationName
-----   --------------------------   -------------------------------------
1       Continent                    Europe
2       Continent / Country          Europe / France
3       Continent / Country / City   Europe / France / Paris
3       Continent / Country / City   Europe / France / Marseille
3       Continent / Country / City   Europe / France / Lyon
2       Continent / Country          Europe / Italy
3       Continent / Country / City   Europe / Italy / Rome
3       Continent / Country / City   Europe / Italy / Milan
3       Continent / Country / City   Europe / Italy / Venice
2       Continent / Country          Europe / United Kingdom
3       Continent / Country / City   Europe / United Kingdom / London
3       Continent / Country / City   Europe / United Kingdom / Cambridge
3       Continent / Country / City   Europe / United Kingdom / Bath

Points of Interest

An interesting thing is that this problem does not occur with a regular UNION statement. Consider the following example:

SELECT CAST('A' AS varchar(100))
UNION ALL
SELECT CONCAT('A', 'B', 'C');

Without any problems, it returns:

(No column name)
----------------
A
ABC

References

• CONCAT (Transact-SQL)
• CAST and CONVERT (Transact-SQL)

History

• 29^th December, 2019: Created