Issue with any.between and unnest

[strokyl]

Hello

My need was to do a filter on a array of integer asserting that one of this integer should be between two values.

My first naive attempt was to use any.between(start, end) which compile but result in invalid SQL (postgres does not support between in combination with any).

After I tried (also naively) _.value.unnest.between(start, end) which even if it doesn't really make sens (missing exists part) still compile and but therefore result in an invalid SQL query.

Then I tried Query(line.value.unnest).map(_.between(start, end)) which for me make sens and should be the slick translation of how I would handle this issue in RAW SQL. But this also does not translate into correct SQL.

I had to go for Query(line.value.unnest).subquery.map(_.between(start, end)) which compile and work! But the documentation of subquery specify that if one need to use it, it means there is bug in Slick, therefore my ticket. Also I think in a ideal word my first and second naive try should not have compile. But after using slick for a while I have accepted and I think that it would be really hard to make sure that all slick expressions that compile result in valid SQL without killing it expressiveness.

Also I open this ticket in slick-pg| and not slick` but I am not sure which side is the bug.

Here a pseudo test to help understanding the issue better:

  case class IntsMetaLine(id: String, key: String, value: List[Int])
  class PgIntsMetaMapping(tag: Tag) extends Table[IntsMetaLine](tag, "multiple_int_metas") {
    def id = column[String]("id")
    def key = column[String]("key")
    def value = column[List[Int]]("value")

    override def * = (id, key, value) <> (IntsMetaLine.tupled, IntsMetaLine.unapply)
  }
  val pgIntsMetaTable = TableQuery[PgIntsMetaMapping]

  "any.between" should "not produce invalid sql" in {
    //provoke: org.postgresql.util.PSQLException: ERROR: syntax error at or near "any"
    //in fact it build the following query:
    //select "id", "key", "value" from "int_metas" where any("value") between 1 and 3
    database.run(
      pgIntsMetaTable
        .filter(_.value.any.between(1, 3))
        .result
    ).futureValue
  }

  "let's try using unnest for that in a naive way" should "not produce invalid sql" in {
    //provoke: org.postgresql.util.PSQLException, with message: ERROR: set-returning functions are not allowed in WHERE
    //in fact it build the following query:
    //select "id", "key", "value" from "int_metas" where unnest("value") between 1 and 3
    database.run(
      pgIntsMetaTable.filter(_.value.unnest.between(1, 3))
        .result
    ).futureValue
  }

  "let's try using unnest using Query" should "not produce invalid sql" in {
    //provoke: org.postgresql.util.PSQLException, with message:  ERROR: function unnest(integer) does not exist
    //in fact it build the following query:
    //select x2."id", x2."key", x2."value" from "multiple_int_metas" x2 where exists(select unnest(x2."value") where unnest(x2."value") between 1 and 3)
    database.run(
      pgIntsMetaTable.filter{l => Query(l.value.unnest).filter(_.between(1, 3)).exists }
        .result
    ).futureValue
  }

  "using unnest using Query and subquery" should "finally work" in {
    //finally work building the following query:
    //select x2."id", x2."key", x2."value" from "multiple_int_metas" x2 where exists(select x3.x4 from (select unnest(x2."value") as x4) x3 where x3.x4 between 1 and 3)
    database.run(
      pgIntsMetaTable.filter{l => Query(l.value.unnest).subquery.filter(_.between(1, 3)).exists }
        .result
    ).futureValue

    //but the comment in top of subquery method is:

    /* Force a subquery to be created when using this Query as part of a larger Query. This method
      * should never be necessary for correctness. If a query works with an explicit `.subquery` call
      * but fails without, this should be considered a bug in Slick. The method is exposed in the API
      * to enable workarounds to be written in such cases. */

    //therefore my ticket
  }

[tminglei]

@strokyl the extension method between was applied to any BaseTypedType, which is super class of JdbcType.

I can't find a way to disable it just for array types.

[vnt-83]

I also use this method. When I need to apply a "like" filter to an array of strings.

package models.dao.utils

import slick.lifted.CanBeQueryCondition

case class OptionalFilter[X, Y, C[_]](val query: slick.lifted.Query[X, Y, C]) {
  def filter[T, R: CanBeQueryCondition]
  (data: Option[T])(f: T => X => R): OptionalFilter[X, Y, C] = {
    data.map(v =>
      OptionalFilter(query.withFilter(f(v)))).getOrElse(this)
  }
}

class OrganizationTable(tag: Tag) extends GenericTable[Organization, UUID](tag, "organization") {
    def m3 = (id.?
      ...
      :: phones
      ...
      :: HNil).mapTo[Organization]


    def * = m3
      ...    
    def phones = column[List[String]]("phones")
      ...
}

  override val table = OrganizationDAO.table

  def searchPageable(offset: Int, limit: Int, orderBy: String, direction: String, searchFilter: Option[String]) = {
    val baseQuery = OptionalFilter(table)
      .filter(searchFilter)(v => d => Query(d.phones.unnest).subquery.filter(_ like "%" + v + "%").exists)
      .query
      .map(_.name)

    val query = for {
      sss <- baseQuery.result
    } yield sss
    db.run(query)
  }