Recursive CTE required "WITH RECURSIVE" in MySQL

[killswtch]

The WITH clause for CTEs needs to be able to support recursive CTEs under MySQL by using the WITH RECURSIVE syntax. Without the RECURSIVE keyword, self-referencing CTEs will fail with a "Table 'xxx' doesn't exist" error. There is a similar ticket for this for Postgres.

[ahmad-moussawi]

@killswtch can you please post an example that can easily reproduce the issue?

[prathikm93]

Fix for Recursive CTEs on MySQL, PostgreSQL & SQLite

@killswtch MySQL (and similarly PostgreSQL/SQLite) require the RECURSIVE keyword for self‑referencing CTEs. Without it, MySQL will error: ERROR 1347 (HY000): Recursive CTE 'xxx' requires RECURSIVE keyword

Change in CompileCteQuery

 protected virtual SqlResult CompileCteQuery(SqlResult ctx, Query query)
 {
     var cteFinder = new CteFinder(query, EngineCode);
     var cteSearchResult = cteFinder.Find();

-    var rawSql = new StringBuilder("WITH ");
+    // MySQL, PostgreSQL or SQLite need RECURSIVE; others use plain WITH
+    var rawSql = new StringBuilder(
+        (EngineCode.Equals("mysql",      StringComparison.OrdinalIgnoreCase)
+      || EngineCode.Equals("postgresql", StringComparison.OrdinalIgnoreCase)
+      || EngineCode.Equals("sqlite",     StringComparison.OrdinalIgnoreCase))
+          ? "WITH RECURSIVE "
+          : "WITH "
+    );

     var cteBindings = new List<object>();

     foreach (var cte in cteSearchResult)
     {
         var cteCtx = CompileCte(cte);
         cteBindings.AddRange(cteCtx.Bindings);
         rawSql.Append(cteCtx.RawSql.Trim());
         rawSql.Append(",\n");
     }

     rawSql.Length -= 2; // remove last comma
     rawSql.Append('\n');
     rawSql.Append(ctx.RawSql);

     ctx.Bindings.InsertRange(0, cteBindings);
     ctx.RawSql = rawSql.ToString();

     return ctx;
 }