querybuilder
querybuilder copied to clipboard
incorrect SQL generated by compilers when using update/insert/delete as a CTE
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"