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

Question

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!!!

Answer 1

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  

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.

Answer 2

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).