content_image/856522.jpg content_image/856523.jpg
The CTE subquery also has this recursive part: 

SELECT n + 1 FROM cte WHERE n < 5 

At each iteration, that SELECT  produces a row with a new value one greater than the value of n  from the previous row set. The first iteration operates on the initial row set ( 1 ) and produces 1+1=2 ; the second iteration operates on the first iteration’s row set ( 2 ) and produces 2+1=3 ; and so forth. This continues until recursion ends, which occurs when n  is no longer less than 5. 

If the recursive part of a CTE produces wider values for a column than the nonrecursive part, it may be necessary to widen the column in the nonrecursive part to avoid data truncation. Consider this statement: 

WITH RECURSIVE cte AS ( SELECT 1 AS n, ’abc’ AS str UNION ALL SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3 ) SELECT * FROM cte; 

In nonstrict SQL mode, the statement produces this output: 

<img src='content_image/856522.jpg'>

The str  column values are all ’abc’  because the nonrecursive SELECT  determines the column widths. Consequently, the wider str  values produced by the recursive SELECT  are truncated. 

In strict SQL mode, the statement produces an error: 

ERROR 1406 (22001): Data too long for column ’str’ at row 1 

To address this issue, so that the statement does not produce truncation or errors, use CAST()  in the nonrecursive SELECT  to make the str  column wider: 

WITH RECURSIVE cte AS ( SELECT 1 AS n, CAST(’abc’ AS CHAR(20)) AS str UNION ALL SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3 ) SELECT * FROM cte; 

Now the statement produces this result, without truncation: 

<img src='content_image/856523.jpg'>

Columns are accessed by name, not position, which means that columns in the recursive part can access columns in the nonrecursive part that have a different position, as this CTE illustrates: 

WITH RECURSIVE cte AS 

