Generate different name for tables generated every time we call high order function.

[posa88]

Version: (e.g. 0.4.1-SNAPSHOT) Module: (e.g. quill-jdbc) Database: (e.g. mysql)

Expected behavior

use high-order functions like:

 def find(condition: FilterQuery): List[Any] = {
    val distinctAndSum = quote { 
      new {
        def apply(xs: Query[StatItem])(p: StatItem => Boolean) =
          xs.filter(p(_)).map(s => (s.mid, s.realpid, s.gender, s.age, s.day, s.pv, s.click, s.uv)).distinct
              .nested
              .groupBy(x => (x._1, x._2)).map{
              case (group, items) =>
                (group._1, group._2,
                  items.map(_._6).sum, items.map(_._7).sum, items.map(_._8).sum)
              }
      }
    }

    val q = quote {
      for {
        audienceSum <- distinctAndSum(query[StatItem])(c =>
          c.day > lift(condition.startDay) && c.day < lift(condition.endDay)
            && (liftQuery(condition.labels.split(",").toList).contains(c.tags)) // default should be all labels
            && liftQuery(condition.gender.split(",").toList).contains(c.gender)
            && liftQuery(condition.age.split(",").toList).contains(c.age))
        adPlaceSum <-
        (distinctAndSum(query[StatItem])(d =>
          d.day > lift(condition.startDay) && d.day < lift(condition.endDay))
          ) if (audienceSum._1 == adPlaceSum._1)
      } yield {
        (audienceSum._1, audienceSum._2)
      }
    }
    ctx.run(q)
  }

Actual behavior

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: table name "x" specified more than once
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2284)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2003)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:200)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:424)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:161)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
    at io.getquill.JdbcContext$$anonfun$executeQuery$1.apply(JdbcContext.scala:88)
    at io.getquill.JdbcContext$$anonfun$executeQuery$1.apply(JdbcContext.scala:85)
    at io.getquill.JdbcContext$$anonfun$withConnection$1.apply(JdbcContext.scala:51)
    at scala.Option.getOrElse(Option.scala:121)
    at io.getquill.JdbcContext.withConnection(JdbcContext.scala:49)
    at io.getquill.JdbcContext.executeQuery(JdbcContext.scala:85)
    at com.zintow.dmp.rest.stat.StatDalImp.find(StatActor.scala:86)
    at com.zintow.dmp.rest.stat.StatActor$.main(StatActor.scala:31)
    at com.zintow.dmp.rest.stat.StatActor.main(StatActor.scala)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:140)

I think we should generate different name for the generated table if we call a high-order function twice or more.

@getquill/maintainers

[posa88]

I rewrite it as follow and it works.But use a duplicated function and it's ugly i'm afraid.

def find(condition: FilterQuery): List[Any] = {
    val distinctAndSum1= quote {
      new {
        def apply(xs: Query[StatItem])(p: StatItem => Boolean) =
          xs.filter(p(_)).map(s => (s.mid, s.realpid, s.gender, s.age, s.day, s.pv, s.click, s.uv)).distinct
            .nested
            .groupBy(x => (x._1, x._2)).map{
            case (group, items) =>
              (group._1, group._2,
                items.map(_._6).sum, items.map(_._7).sum, items.map(_._8).sum)
          }
      }
    }

    val distinctAndSum2 = quote { 
      new {
        def apply(xs: Query[StatItem])(p: StatItem => Boolean) =
          xs.filter(p(_)).map(s => (s.mid, s.realpid, s.gender, s.age, s.day, s.pv, s.click, s.uv)).distinct
            .nested
            .groupBy(y => (y._1, y._2)).map{
            case (group, items) =>
              (group._1, group._2,
                items.map(_._6).sum, items.map(_._7).sum, items.map(_._8).sum)
          }
      }
    }

    val q = quote {
      for {
      audienceSum <- distinctAndSum1(query[StatItem])(c =>
        c.day > lift(condition.startDay) && c.day < lift(condition.endDay)
      && (liftQuery(condition.labels.split(",").toList).contains(c.tags))  // default should be all labels
      && liftQuery(condition.gender.split(",").toList).contains(c.gender)
      && liftQuery(condition.age.split(",").toList).contains(c.age)
      )
        adPlaceSum <- distinctAndSum2(query[StatItem])(d =>
          d.day > lift(condition.start) && d.day < lift(condition.endDay))
          if (audienceSum._1 == adPlaceSum._1 && audienceSum._2 == adPlaceSum._2)
      } yield {
        (audienceSum._1)
      }
    }
    ctx.run(q)
  }

[fwbrasil]

simplified code:

object Bug extends App {

  //    val ctx = io.getquill.testContext
  val ctx = io.getquill.context.sql.testContext

  import ctx._

  val q = quote {
    for {
      a <- qr1.nested.groupBy(a => a.i).map { case (i, l) => (i, l.map(_.i).sum) }
      b <- qr1.nested.groupBy(a => a.i).map { case (i, l) => (i, l.map(_.i).sum) }
    } yield {
      (a, b)
    }
  }

  println(ctx.run(q.dynamic).string)

  /*
SELECT
  a._1_1,
  a._1_2,
  a._2_1,
  a._2_2
FROM (SELECT
       a.i _1_1,
       a.i _1_2,
       SUM(a.i) _2_1,
       SUM(a.i) _2_2
     FROM (SELECT
       x.i
     FROM TestEntity x) a
     GROUP BY a.i) a,
     (SELECT
       a.i _1_1,
       a.i _1_2,
       SUM(a.i) _2_1,
       SUM(a.i) _2_2
     FROM (SELECT
       x.i
     FROM TestEntity x) a
     GROUP BY a.i) a
   */
}

Note that both sub-queries have identifier a