Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

A WITH common table expression fails with a syntax error in MySQL

ErrorERROR 1064 (42000) on a statement that begins with WITH

Short answer

Common table expressions arrived in MySQL 8.0. On 5.7 or older, a query that starts with WITH is a syntax error. Check SELECT VERSION(). Upgrade to 8.0 or later if you own the server, or rewrite each CTE as a derived table: a subquery in the FROM clause with an alias.

  • Applies to MySQL 5.7, MySQL 8.0, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Ask the server that rejects the query for its version. Anything below 8.0 cannot run CTEs.

    SELECT VERSION();
  2. 2Match what you see, then jump to its fix

    1. 1VERSION() returns 5.x, and the error points at WITHThe server is older than MySQL 8.0Fix
    2. 2The platform is stuck on a 5.x releaseYou cannot change the server (judge, host or old client project)Fix
    3. 3A WITH query on MySQL 8.0 or later fails and the CTE refers to its own nameA recursive CTE is missing the RECURSIVE keywordFix

What does the error look like?

  • ERROR 1064 (42000) on a statement that begins with WITH
  • The same query works on your laptop but fails on an online SQL judge

Cause 1 of 3

The server is older than MySQL 8.0

1Check

The MySQL 8.0 release notes list common table expressions, both nonrecursive and recursive, as new in 8.0. Earlier versions have no WITH clause.

SELECT VERSION();

2Fix

If you control the server, plan an upgrade to a supported release (8.0 or later). If you do not, use the rewrite in the next cause.

-- needs MySQL 8.0 or later
WITH recent AS (SELECT id, total FROM orders WHERE total > 100)
SELECT * FROM recent;

3Verify

On the upgraded server the WITH query returns rows.

SELECT VERSION();

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 3

You cannot change the server (judge, host or old client project)

1Check

MySQL's WITH syntax is WITH [RECURSIVE] name AS (subquery). A non-recursive CTE can be replaced by a derived table, which is a subquery in the FROM clause; the alias is required.

SELECT VERSION();

2Fix

Move the subquery into FROM and give it an alias. If the CTE is used twice, repeat the subquery, or save it as a view or temporary table.

SELECT recent.id, recent.total
FROM (SELECT id, total FROM orders WHERE total > 100) AS recent;

3Verify

The rewritten query runs on the old server and returns the same rows.

SELECT COUNT(*) FROM (SELECT id FROM orders WHERE total > 100) AS recent;

Confirmed by dev.mysql.com

Did this fix it?

Cause 3 of 3

A recursive CTE is missing the RECURSIVE keyword

1Check

The manual says the WITH clause must begin with WITH RECURSIVE if any CTE in it refers to itself. A recursive CTE has a nonrecursive SELECT part and a recursive SELECT part joined with UNION ALL or UNION DISTINCT.

SELECT VERSION();

2Fix

Add the RECURSIVE keyword and join the two parts with UNION ALL.

WITH RECURSIVE cte (n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

3Verify

The query returns the numbers 1 to 5.

SELECT 1;

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Run SELECT VERSION() on a new platform before writing queries with CTEs or window functions.
  • Write portable SQL (derived tables) when the same query must run on servers you do not control.
  • Test queries on the version you deploy to, not only on your laptop.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.