Type mismatch between anchor point and recursive section in the "TXT" column of recursive query "CTE".

mille 126 Reputation points
2022-04-14T08:25:25.653+00:00

Someone can help me understand the mistake below.. In detail? Someone explained why this happens in CTE.
Type mismatch between anchor point and recursive section in the "TXT" column of recursive query "CTE".
Here's an example. I solved this problem with CAST, but why does it work?

WITH CTE(n, txt) AS
(
--SELECT 1, '1' --This does not work.
--SELECT 1, CAST('1' AS varchar) --This does not work.
--SELECT 1, CAST('1' AS varchar(1000)) --This does not work.
SELECT
1,
CAST('1' AS varchar(max)) --This works. Why?
UNION ALL
SELECT
n+1,
txt + ', ' + CAST(n+1 AS varchar) --Why is (max) NOT needed?
FROM
CTE
WHERE
n < 10
)
SELECT *
FROM CTE
I assume there are default variable types that I don't understand, for example:

Similar to SELECT 'Hello world! What is the type of?
What is the type of the string concatenation operator SELECT 'A' + 'B'?
What is a mathematical type like SELECT n+1?

Thanks!!!

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,117 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,583 questions
0 comments No comments
{count} votes

Accepted answer
  1. Bert Zhou-msft 3,421 Reputation points
    2022-04-14T08:37:43.417+00:00

    Hi,@mille
    Welcome to Microsoft T-SQL Q&A Forum!

    First, when you cast the anchor part to varchar(max), it automatically means that the recursive part will also be varchar(max).
    The data type of 'Hello world!' is varchar, and the summed length of the two data types, I think is varchar(2). n+1 is still int.
    In a recursive CTE, the data types must match exactly, so '1' is varchar(1). If you specify varchar without length in CAST, you get varchar(30), so txt + ', ' + CAST(n+1 AS varchar) is varchar(33).

    Taking a look at the example below to help you understand:

    ;WITH CTE(n, txt) AS  
    (  
        SELECT      1, CAST('1' AS varchar(30))          
        UNION ALL  
        SELECT        
            n+1,   
            CAST(CONCAT(txt, ', ', n+1) AS varchar(30))  
        FROM          
            CTE  
        WHERE         
            n < 10  
    )  
    SELECT *  
    FROM CTE  
    

    193081-image.png

    Best regards,
    Bert Zhou


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 104.2K Reputation points MVP
    2022-04-14T21:39:49.467+00:00

    txt + ', ' + CAST(n+1 AS varchar) --Why is (max) NOT needed?

    Because the data type of txt is varchar(MAX). When two data types means, the one with lower precedence will be implicitly converted to the type with higher precedence, provided that a implicit conversion is available. varchar(MAX) has higher precedence than varchar(n).

    0 comments No comments