( SELECT 1 AS n, 1 AS p, -1 AS q UNION ALL SELECT n + 1, q * 2, p * 2 FROM cte WHERE n < 5 ) SELECT * FROM cte; 

Because p  in one row is derived from q  in the previous row, and vice versa, the positive and negative values swap positions in each successive row of the output: 

+------+------+------+ | n | p | q | +------+------+------+ | 1 | 1 | -1 | | 2 | -2 | 2 | | 3 | 4 | -4 | | 4 | -8 | 8 | | 5 | 16 | -16 | +------+------+------+ 

Some syntax constraints apply within recursive CTE subqueries: 

•The recursive SELECT  part must not contain these constructs: 

•Aggregate functions such as SUM() 

•Window functions 

• GROUP BY 

• ORDER BY 

• DISTINCT 

Prior to MySQL 8.0.19, the recursive SELECT  part of a recursive CTE also could not use a LIMIT clause. This restriction is lifted in MySQL 8.0.19, and LIMIT  is now supported in such cases, along with an optional OFFSET  clause. The effect on the result set is the same as when using LIMIT  in the outermost SELECT , but is also more efficient, since using it with the recursive SELECT  stops the generation of rows as soon as the requested number of them has been produced. 

These constraints do not apply to the nonrecursive SELECT  part of a recursive CTE. The prohibition on DISTINCT  applies only to UNION  members; UNION DISTINCT  is permitted. 

•The recursive SELECT  part must reference the CTE only once and only in its FROM  clause, not in any subquery. It can reference tables other than the CTE and join them with the CTE. If used in a join like this, the CTE must not be on the right side of a LEFT JOIN . 

These constraints come from the SQL standard, other than the MySQL-specific exclusions of ORDER BY , LIMIT  (MySQL 8.0.18 and earlier), and DISTINCT . 

For recursive CTEs, EXPLAIN  output rows for recursive SELECT  parts display Recursive  in the Extra column. 

Cost estimates displayed by EXPLAIN  represent cost per iteration, which might differ considerably from total cost. The optimizer cannot predict the number of iterations because it cannot predict at what point the WHERE  clause becomes false. 

CTE actual cost may also be affected by result set size. A CTE that produces many rows may require an internal temporary table large enough to be converted from in-memory to on-disk format and may 

