Null value comparision in slick

Question

I encounter a problem of nullable column comparison.

If some columns are Option[T], I wonder how slick translate like === operation on these columns to sql. There are two possibilities: null value( which is None in scala ) and non-null value. In the case of null value, sql should use is instead of =. However slick doesn't handle it correctly in the following case.

Here is the code (with H2 database):

object Test1 extends Controller {


  case class User(id: Option[Int], first: String, last: String)

  class Users(tag: Tag) extends Table[User](tag, "users") {
    def id = column[Int]("id",O.Nullable)
    def first = column[String]("first")
    def last = column[String]("last")
    def * = (id.?, first, last) <> (User.tupled, User.unapply)
  }
  val users = TableQuery[Users]


  def find_u(u:User) =  DB.db.withSession{ implicit session =>
    users.filter( x=> x.id === u.id && x.first === u.first && x.last === u.last ).firstOption

  }

  def t1 = Action {
    DB.db.withSession { implicit session =>
      DB.createIfNotExists(users)

      val u1 = User(None,"123","abc")
      val u2 = User(Some(1232),"123","abc")

      users += u1
      val r1 = find_u(u1)
      println(r1)
      val r2 = find_u(u2)
      println(r2)

    }
    Ok("good")
  }
}

I print out the sql. It is following result for the first find_u.

[debug] s.s.j.J.statement - Preparing statement: select x2."id", x2."first", x2."last" from "users" x2 where (
(x2."id" = null) and (x2."first" = '123')) and (x2."last" = 'abc')

Notice that (x2."id" = null) is incorrect here. It should be (x2."id" is null).

Update:

Is it possible to only compare non-null fields in an automatic fashion? Ignore those null columns. E.g. in the case of User(None,"123","abc"), only do where (x2."first" = '123')) and (x2."last" = 'abc')

Don't use column[Int]("id",O.Nullable). Correct is column[Option[Int]]("id"). Otherwise you may get exceptions. — cvogt
Hi @cvogt, thank you for the advice. However column[Option[Int]]("id") doesn't solve my problem. sql remains the same. — worldterminator

cvogt cvogt · Accepted Answer · 2014-12-19T09:21:38

Slick uses three-valued-logic. This shows when nullable columns are involved. In that regard it does not adhere to Scala semantics, but uses SQL semantics. So (x2."id" = null) is indeed correct under these design decisions. To to a strict NULL check use x.id.isEmpty. For strict comparison do

(if(u.id.isEmpty) x.id.isEmpty else (x.id === u.id))

Update:

To compare only when the user id is non-null use

(u.id.isEmpty || (x.id === u.id)) && ...

Null value comparision in slick

1 Answers