WITH (Common Table Expressions) The CTE subquery also has this recursive part: 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: In nonstrict SQL mode, the statement produces this output: 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: 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: Now the statement produces this result, without truncation: 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 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; ERROR 1406 (22001): Data too long for column ’str’ at row 1 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; SELECT n + 1 FROM cte WHERE n < 5 +------+------+ | n | str | +------+------+ | 1 | abc | | 2 | abc | | 3 | abc | +------+------+ +------+--------------+ | n | str | +------+--------------+ | 1 | abc | | 2 | abcabc | | 3 | abcabcabcabc | +------+--------------+ 2712