if/else statement inside quote is compiled to invalid query

[mxl]

Version: 2.2.0 Module: quill-jdbc Database: sqlite

Expected behavior

Expected to fail during compilation.

Actual behavior

It's compiled to invalid query and fails with runtime error:

11:14:06.640 [scala-execution-context-global-84] ERROR io.udash.rpc.AtmosphereService - RPC request handling failed
org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (near "CASE": syntax error)
        at org.sqlite.core.DB.newSQLException(DB.java:909) ~[sqlite-jdbc-3.18.0.jar:na]
        at org.sqlite.core.DB.newSQLException(DB.java:921) ~[sqlite-jdbc-3.18.0.jar:na]
        at org.sqlite.core.DB.throwex(DB.java:886) ~[sqlite-jdbc-3.18.0.jar:na]
        at org.sqlite.core.NativeDB.prepare_utf8(Native Method) ~[sqlite-jdbc-3.18.0.jar:na]
        at org.sqlite.core.NativeDB.prepare(NativeDB.java:127) ~[sqlite-jdbc-3.18.0.jar:na]
        at org.sqlite.core.DB.prepare(DB.java:227) ~[sqlite-jdbc-3.18.0.jar:na]
        at org.sqlite.core.CorePreparedStatement.<init>(CorePreparedStatement.java:41) ~[sqlite-jdbc-3.18.0.jar:na]
        at org.sqlite.jdbc3.JDBC3PreparedStatement.<init>(JDBC3PreparedStatement.java:30) ~[sqlite-jdbc-3.18.0.jar:na]
        at org.sqlite.jdbc4.JDBC4PreparedStatement.<init>(JDBC4PreparedStatement.java:19) ~[sqlite-jdbc-3.18.0.jar:na]
        at org.sqlite.jdbc4.JDBC4Connection.prepareStatement(JDBC4Connection.java:48) ~[sqlite-jdbc-3.18.0.jar:na]
        at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:263) ~[sqlite-jdbc-3.18.0.jar:na]
        at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:235) ~[sqlite-jdbc-3.18.0.jar:na]
        at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:317) ~[HikariCP-2.7.2.jar:na]
        at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java) ~[HikariCP-2.7.2.jar:na]
        at io.getquill.context.jdbc.JdbcContext.$anonfun$executeAction$1(JdbcContext.scala:98) ~[quill-jdbc_2.12-2.2.0.jar:2.2.0]
        at io.getquill.context.jdbc.JdbcContext.$anonfun$executeAction$1$adapted(JdbcContext.scala:97) ~[quill-jdbc_2.12-2.2.0.jar:2.2.0]
        at io.getquill.context.jdbc.JdbcContext.$anonfun$withConnection$1(JdbcContext.scala:46) ~[quill-jdbc_2.12-2.2.0.jar:2.2.0]
        at scala.Option.getOrElse(Option.scala:121) ~[scala-library-2.12.2.jar:1.0.0-M1]
        at io.getquill.context.jdbc.JdbcContext.withConnection(JdbcContext.scala:44) ~[quill-jdbc_2.12-2.2.0.jar:2.2.0]
        at io.getquill.context.jdbc.JdbcContext.executeAction(JdbcContext.scala:97) ~[quill-jdbc_2.12-2.2.0.jar:2.2.0]

Steps to reproduce the behavior

case class Video(
  id: String,
  title: String,
  url: String,
  pictureUrl: String,
  publishedAt: Date,
  channel: String,
  duration: Option[String],
  createdOn: Date
)

def saveToCache(item: Video): Unit = {
    logger.trace(item)
    import ctx._
    val x = quote {
      val lItem = lift(item)
      val fromDb = query[Video].filter(_.id == lItem.id)
      if (fromDb.isEmpty) query[Video].insert(lItem)
      else fromDb.update(lItem)
    }
    logger.trace(x.ast)
    run(x)
  }

See https://stackoverflow.com/q/47484978/746347

@getquill/maintainers

[fwbrasil]

@mxl this doesn't seem to be a bug, right? Quill does the translation correctly but the databases don't support this kind of query. See https://scastie.scala-lang.org/bEJeaVdrQnWwmY9NNWR5Iw