This question is about linq2db.MySql 3.1.5 and MySQL 8.0.21
I am getting "You have an error in your SQL syntax..." when trying to run an update (marking duplicate emails) using CTE on MySql as per below
var cte = (
from x in db.GetTable<PEmailCleanupJobItem>()
group x by x.EmailAddress into eGroup
where eGroup.Count() > 1
select new { EmailAddress = eGroup.Key }
).AsCte();
var qry = (
from e in db.GetTable<PEmailCleanupJobItem>()
from d in cte
.InnerJoin(d => e.EmailAddress == d.EmailAddress)
select e
);
await qry
.Set(e => e.IsDuplicate, true)
.UpdateAsync();
This generates and sends the following to MySQL
-- MySql.Official MySql (asynchronously)
DECLARE @IsDuplicate UByte -- Boolean
SET @IsDuplicate = 1
UPDATE `CTE_1` (`EmailAddress`)
AS
(
SELECT
`t1`.`email_address`
FROM
`emails` `t1`
GROUP BY
`t1`.`email_address`
HAVING
Count(*) > 1
)
FROM
`emails` `d`
INNER JOIN `CTE_1` `e` ON (`d`.`email_address` IS NULL AND `e`.`EmailAddress` IS NULL OR `d`.`email_address` = `e`.`EmailAddress`)
SET
`d`.`is_duplicate` = @IsDuplicate
Note how UPDATE `CTE_1` (`EmailAddress`)
is generated instead of WITH `CTE_1` (`EmailAddress`)
I would manually write this query (which works) as
WITH `CTE_1` (`EmailAddress`)
AS
(
SELECT
`t1`.`email_address`
FROM
`emails` `t1`
GROUP BY
`t1`.`email_address`
HAVING
Count(*) > 1
)
UPDATE
`emails` `d`
INNER JOIN `CTE_1` `e` ON (`d`.`email_address` IS NULL AND `e`.`EmailAddress` IS NULL OR `d`.`email_address` = `e`.`EmailAddress`)
SET
`d`.`is_duplicate` = 1
Is this a bug with linq2db or am I missing something?