scalaanorm

Anorm, Play, Scala and Postgresql: Dynamic SQL clause not working

I have a problem with using dynamic filter on an SQL query on Postgres, I am using Scala, Anorm and the Play framework. The following query works fine:

val queryTest = SQL("""
  SELECT * FROM myTable
  WHERE (field1 = {field1} OR {field1} IS NULL)
  AND (field2 = {field2} OR {field2} IS NULL)
""").on("field1" -> Some("Foo"), "field2" -> Some("Bar"))
queryTest()

However when a field is set to None, like this:

  val queryTest = SQL("""
    SELECT * FROM myTable
    WHERE (field1 = {field1} OR {field1} IS NULL)
    AND (field2 = {field2} OR {field2} IS NULL)
  """).on("field1" -> Some("Foo"), "field2" -> None)
    queryTest()

I get the following error:

[PSQLException: ERROR: could not determine data type of parameter $4].

When I change the field2 clause this way: AND ({field2} IS NULL OR field2 = {field2}) I get:

[PSQLException: ERROR: could not determine data type of parameter $3]

So the problem seems to be specifically on the {field2} IS NULL.

According to this answer https://stackoverflow.com/a/18308023, it should be working, I don't see where I am going wrong, or maybe it is a specific Postgres issue.

EDIT:

Also, when I set the debug on, I can see the request being sent as:

SELECT * FROM myTable
WHERE (field1 = 'Foo' OR 'Foo' IS NULL)
AND (NULL IS NULL OR field2 = NULL)

And that request works if I paste it in my PostgrSQL client.

EDIT: Here is the stack trace:

play.api.Application$$anon$1: Execution exception[[PSQLException: ERROR: could not determine data type of parameter $3]]
    at play.api.Application$class.handleError(Application.scala:296) ~[play_2.11-2.3.4.jar:2.3.4]
    at play.api.DefaultApplication.handleError(Application.scala:402) [play_2.11-2.3.4.jar:2.3.4]
    at play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$14$$anonfun$apply$1.applyOrElse(PlayDefaultUpstreamHandler.scala:205) [play_2.11-2.3.4.jar:2.3.4]
    at play.core.server.netty.PlayDefaultUpstreamHandler$$anonfun$14$$anonfun$apply$1.applyOrElse(PlayDefaultUpstreamHandler.scala:202) [play_2.11-2.3.4.jar:2.3.4]
    at scala.runtime.AbstractPartialFunction.apply(AbstractPartialFunction.scala:36) [scala-library-2.11.1.jar:na]
    at scala.util.Failure$$anonfun$recover$1.apply(Try.scala:215) [scala-library-2.11.1.jar:na]
    at scala.util.Try$.apply(Try.scala:191) [scala-library-2.11.1.jar:na]
    at scala.util.Failure.recover(Try.scala:215) [scala-library-2.11.1.jar:na]
    at scala.concurrent.Future$$anonfun$recover$1.apply(Future.scala:324) [scala-library-2.11.1.jar:na]
    at scala.concurrent.Future$$anonfun$recover$1.apply(Future.scala:324) [scala-library-2.11.1.jar:na]
    at scala.concurrent.impl.CallbackRunnable.run(Promise.scala:32) [scala-library-2.11.1.jar:na]
    at play.api.libs.iteratee.Execution$trampoline$.execute(Execution.scala:46) [play-iteratees_2.11-2.3.4.jar:2.3.4]
    at scala.concurrent.impl.CallbackRunnable.executeWithValue(Promise.scala:40) [scala-library-2.11.1.jar:na]
    at scala.concurrent.impl.Promise$DefaultPromise.tryComplete(Promise.scala:248) [scala-library-2.11.1.jar:na]
    at scala.concurrent.Promise$class.complete(Promise.scala:55) [scala-library-2.11.1.jar:na]
    at scala.concurrent.impl.Promise$DefaultPromise.complete(Promise.scala:153) [scala-library-2.11.1.jar:na]
    at scala.concurrent.impl.Future$PromiseCompletingRunnable.run(Future.scala:23) [scala-library-2.11.1.jar:na]
    at akka.dispatch.TaskInvocation.run(AbstractDispatcher.scala:41) [akka-actor_2.11-2.3.4.jar:na]
    at akka.dispatch.ForkJoinExecutorConfigurator$AkkaForkJoinTask.exec(AbstractDispatcher.scala:393) [akka-actor_2.11-2.3.4.jar:na]
    at scala.concurrent.forkjoin.ForkJoinTask.doExec(ForkJoinTask.java:260) [scala-library-2.11.1.jar:na]
    at scala.concurrent.forkjoin.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1339) [scala-library-2.11.1.jar:na]
    at scala.concurrent.forkjoin.ForkJoinPool.runWorker(ForkJoinPool.java:1979) [scala-library-2.11.1.jar:na]
    at scala.concurrent.forkjoin.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:107) [scala-library-2.11.1.jar:na]
Caused by: org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $3
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103) ~[postgresql-9.1-901-1.jdbc4.jar:na]
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836) ~[postgresql-9.1-901-1.jdbc4.jar:na]
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257) ~[postgresql-9.1-901-1.jdbc4.jar:na]
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512) ~[postgresql-9.1-901-1.jdbc4.jar:na]
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388) ~[postgresql-9.1-901-1.jdbc4.jar:na]
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273) ~[postgresql-9.1-901-1.jdbc4.jar:na]
    at com.jolbox.bonecp.PreparedStatementHandle.executeQuery(PreparedStatementHandle.java:174) ~[bonecp-0.8.0.RELEASE.jar:na]
    at anorm.Sql$class.resultSet(Anorm.scala:286) ~[anorm_2.11-2.3.4.jar:2.3.4]
    at anorm.SimpleSql.resultSet(Anorm.scala:190) ~[anorm_2.11-2.3.4.jar:2.3.4]
    at anorm.Sql$class.apply(Anorm.scala:281) ~[anorm_2.11-2.3.4.jar:2.3.4]
    at anorm.SimpleSql.apply(Anorm.scala:190) ~[anorm_2.11-2.3.4.jar:2.3.4]
    at engine.dao.HandPlayerDao$$anonfun$getStats$1.apply(HandPlayerDao.scala:21) ~[classes/:na]
    at engine.dao.HandPlayerDao$$anonfun$getStats$1.apply(HandPlayerDao.scala:14) ~[classes/:na]
    at play.api.db.DBApi$class.withConnection(DB.scala:81) ~[play-jdbc_2.11-2.3.4.jar:2.3.4]
    at play.api.db.BoneCPApi.withConnection(DB.scala:267) ~[play-jdbc_2.11-2.3.4.jar:2.3.4]
    at play.api.db.DB$$anonfun$withConnection$3.apply(DB.scala:162) ~[play-jdbc_2.11-2.3.4.jar:2.3.4]
    at play.api.db.DB$$anonfun$withConnection$3.apply(DB.scala:162) ~[play-jdbc_2.11-2.3.4.jar:2.3.4]
    at scala.Option.map(Option.scala:145) ~[scala-library-2.11.1.jar:na]
    at play.api.db.DB$.withConnection(DB.scala:162) ~[play-jdbc_2.11-2.3.4.jar:2.3.4]
    at engine.dao.HandPlayerDao.getStats(HandPlayerDao.scala:14) ~[classes/:na]
    at engine.stats.PlayerStats.<init>(PlayerStats.scala:35) ~[classes/:na]
    at engine.stats.PlayerStats.<init>(PlayerStats.scala:19) ~[classes/:na]
    at controllers.StatsCtrl$$anonfun$allPlayerStats$1$$anonfun$apply$3$$anonfun$apply$4$$anonfun$1.apply(StatsCtrl.scala:39) ~[classes/:na]
    at controllers.StatsCtrl$$anonfun$allPlayerStats$1$$anonfun$apply$3$$anonfun$apply$4$$anonfun$1.apply(StatsCtrl.scala:39) ~[classes/:na]
    at scala.collection.immutable.List.map(List.scala:274) ~[scala-library-2.11.1.jar:na]
    at controllers.StatsCtrl$$anonfun$allPlayerStats$1$$anonfun$apply$3$$anonfun$apply$4.apply(StatsCtrl.scala:39) ~[classes/:na]
    at controllers.StatsCtrl$$anonfun$allPlayerStats$1$$anonfun$apply$3$$anonfun$apply$4.apply(StatsCtrl.scala:31) ~[classes/:na]
    at controllers.Secured$$anonfun$withAuth$3$$anonfun$apply$3.apply(AuthenticationCtrl.scala:68) ~[classes/:na]
    at controllers.Secured$$anonfun$withAuth$3$$anonfun$apply$3.apply(AuthenticationCtrl.scala:68) ~[classes/:na]
    at play.api.mvc.ActionBuilder$$anonfun$apply$16.apply(Action.scala:433) ~[play_2.11-2.3.4.jar:2.3.4]
    at play.api.mvc.ActionBuilder$$anonfun$apply$16.apply(Action.scala:432) ~[play_2.11-2.3.4.jar:2.3.4]
    at play.api.mvc.Action$.invokeBlock(Action.scala:556) ~[play_2.11-2.3.4.jar:2.3.4]
    at play.api.mvc.Action$.invokeBlock(Action.scala:555) ~[play_2.11-2.3.4.jar:2.3.4]
    at play.api.mvc.ActionBuilder$$anon$1.apply(Action.scala:518) ~[play_2.11-2.3.4.jar:2.3.4]
    at play.api.mvc.Action$$anonfun$apply$1$$anonfun$apply$4$$anonfun$apply$5.apply(Action.scala:130) ~[play_2.11-2.3.4.jar:2.3.4]
    at play.api.mvc.Action$$anonfun$apply$1$$anonfun$apply$4$$anonfun$apply$5.apply(Action.scala:130) ~[play_2.11-2.3.4.jar:2.3.4]
    at play.utils.Threads$.withContextClassLoader(Threads.scala:21) ~[play_2.11-2.3.4.jar:2.3.4]
    at play.api.mvc.Action$$anonfun$apply$1$$anonfun$apply$4.apply(Action.scala:129) ~[play_2.11-2.3.4.jar:2.3.4]
    at play.api.mvc.Action$$anonfun$apply$1$$anonfun$apply$4.apply(Action.scala:128) ~[play_2.11-2.3.4.jar:2.3.4]
    at scala.Option.map(Option.scala:145) ~[scala-library-2.11.1.jar:na]
    at play.api.mvc.Action$$anonfun$apply$1.apply(Action.scala:128) ~[play_2.11-2.3.4.jar:2.3.4]
    at play.api.mvc.Action$$anonfun$apply$1.apply(Action.scala:121) ~[play_2.11-2.3.4.jar:2.3.4]
    at play.api.libs.iteratee.DoneIteratee$$anonfun$mapM$2.apply(Iteratee.scala:705) ~[play-iteratees_2.11-2.3.4.jar:2.3.4]
    at play.api.libs.iteratee.DoneIteratee$$anonfun$mapM$2.apply(Iteratee.scala:705) ~[play-iteratees_2.11-2.3.4.jar:2.3.4]
    at scala.concurrent.impl.Future$PromiseCompletingRunnable.liftedTree1$1(Future.scala:24) [scala-library-2.11.1.jar:na]
    at scala.concurrent.impl.Future$PromiseCompletingRunnable.run(Future.scala:24) [scala-library-2.11.1.jar:na]
Solution

  • There is a issue with such undefined value (None) which will be fixed.

    For now you can replace the None with null.asInstanceOf[String].