incorrect SQL generated by compilers when using update/insert/delete as a CTE

[ianclegg]

It appears SqlKata is unable to generate 'data-modifying' common table expressions (CTE's). This can be observed when using the fluent api to build a Query using the With method and passing an Insert, Update or Delete query.
In this scenario SqlKata behaves as if the query passed into the With method did not have the AsInsert(), AsUpdate() or AsDelete() methods invoked on it and instead generates select statements.

For example:

var insert = new Query("table_one").AsInsert(new[] {"column_one"}, new object[] { 1 });
var query = new Query("table_two").Select("column")
    .With("insert_query", insert)

expected result:

WITH "insert_query" AS (
  INSERT INTO "table_one" ("column_one") VALUES  (1)
)
SELECT "column" FROM "table_two"

actual result:

WITH "insert_query" AS (
  SELECT * FROM "table_one"
)
SELECT "column" FROM "table_two"