Support MySql 5.7 delete queries

[mahe-ymc]

Version: 4.6.0
Module: quill-jdbc
Database: mysql

Expected behavior

Delete queries should produce SQL accepted by MySql 5.7

Actual behavior

Delete queries use Table Aliases, which are unsupported by MySql 5.7.

Steps to reproduce the behavior

https://scastie.scala-lang.org/y4KUdZk3SLKtYkktHgJ3UA
Should be DELETE FROM person WHERE name = 'John'

Workaround

[plmuninn]

I have same issue - is there is a way to workaround it using sql ?

[rider-yi]

me too. #2540 did not solve the problem.

[deterdw]

I've hit this issue on MariaDB 10 too.

For those looking for a work-around, the following (snipped from SQLServerDialect) seems to do the trick:

import io.getquill._
import io.getquill.ast._
import io.getquill.idiom.StatementInterpolator._

trait MariaDBDialect extends MySQLDialect {
  override protected def actionTokenizer(insertEntityTokenizer: Tokenizer[Entity])(implicit astTokenizer: Tokenizer[Ast], strategy: NamingStrategy, idiomContext: IdiomContext): Tokenizer[ast.Action] =
    Tokenizer[ast.Action] {
      // Delete(Filter(...)) usually cause a table alias i.e. `DELETE FROM People <alias> WHERE ...`
      // since the alias is used in the WHERE clause. This functionality removes that because MariaDB (and MySQL 5.7) doesn't support aliasing in deletes.
      //case Update(Filter(table: Entity, x, where), assignments) =>
      //  stmt"UPDATE ${table.token} SET ${assignments.token} WHERE ${where.token}"
      case Delete(Filter(table: Entity, x, where)) =>
        stmt"DELETE FROM ${table.token} WHERE ${where.token}"
      case other => super.actionTokenizer(insertEntityTokenizer).token(other)
    }
}
object MariaDBDialect extends MariaDBDialect

Of course you'll have to copy & adapt your specific context too to use the new dialect.

[juliano]

Solved in #2704