[Slick 1.0] Aggregating on multiple groupBy columns gives run-time error

[Kevin Keech]

I'm trying to aggregate data based on a groupBy of multiple columns.

Here's a simple table:

case class FooBar (id: Option[Int], state: String, name: String, num: Int)
object FooBarT extends Table[FooBar]("FOO") {
    def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
    def state = column[String]("state")
    def name = column[String]("name")
    def num = column[Int]("num")
    def * = id.? ~ state ~ name ~ num <> (FooBar, FooBar.unapply _)
}

Some data:

    FooBarT.insert( FooBar(None,"California","Bill",1) )
    FooBarT.insert( FooBar(None,"California","Bill",2) )
    FooBarT.insert( FooBar(None,"California","Bill",3) )
    FooBarT.insert( FooBar(None,"California","Jane",2) )
    FooBarT.insert( FooBar(None,"California","Jane",3) )
    FooBarT.insert( FooBar(None,"California","Jane",4) )
    FooBarT.insert( FooBar(None,"California","Jane",5) )
    FooBarT.insert( FooBar(None,"Utah","Bill",1) )
    FooBarT.insert( FooBar(None,"Utah","Bill",2) )
    FooBarT.insert( FooBar(None,"Utah","Jane",2) )
    FooBarT.insert( FooBar(None,"Utah","Chuck",1) )
    FooBarT.insert( FooBar(None,"Utah","Chuck",4) )
    FooBarT.insert( FooBar(None,"Utah","Chuck",5) )
    FooBarT.insert( FooBar(None,"Nebraska","Jane",7) )

And finally, a simple query that groups on the 'state' and 'name' columns and finds the min/max of the 'num' column:

    val q = Query(FooBarT).groupBy(fb => (fb.state,fb.name)).map{case (a,b) => (a._1, a._2, b.map(_.num).min, b.map(_.num).max)}

    q foreach {r => println("%s %s %d %d".format(r._1, r._2, r._3, r._4))}

I should get something like this:

Nebraska Jane 7 7
Utah Chuck 1 5
Utah Jane 2 2
California Jane 2 5
California Bill 1 3
Utah Bill 1 2

Instead, I get the following run-time error:

Exception in thread "main" scala.slick.SlickException: Unexpected node Select _1 -- SQL prefix: select 
  at scala.slick.driver.BasicStatementBuilderComponent$QueryBuilder.toComprehension(BasicStatementBuilderComponent.scala:75) 
  at scala.slick.driver.H2Driver$QueryBuilder.scala$slick$driver$BasicStatementBuilderComponent$OracleStyleRowNum$$super$toComprehension(H2Driver.scala:41)
  at scala.slick.driver.BasicStatementBuilderComponent$OracleStyleRowNum$class.toComprehension(BasicStatementBuilderComponent.scala:397)
  ...

The following also gives the same run-time error:

println(q.selectStatement)

Am I constructing the groupBy incorrectly? Are multiple column groupings supported in Slick?

Thanks,

Kevin

[Stefan Zeiger]

On 2013-01-29 20:38, Kevin Keech wrote:

Instead, I get the following run-time error:

Exception in thread "main" scala.slick.SlickException: Unexpected node Select _1 -- SQL prefix: select 
  at scala.slick.driver.BasicStatementBuilderComponent$QueryBuilder.toComprehension(BasicStatementBuilderComponent.scala:75) 
  at scala.slick.driver.H2Driver$QueryBuilder.scala$slick$driver$BasicStatementBuilderComponent$OracleStyleRowNum$$super$toComprehension(H2Driver.scala:41)
  at scala.slick.driver.BasicStatementBuilderComponent$OracleStyleRowNum$class.toComprehension(BasicStatementBuilderComponent.scala:397)
  ...

The following also gives the same run-time error:

println(q.selectStatement)

Am I constructing the groupBy incorrectly? Are multiple column groupings supported in Slick?

It should be supported but it's possible that we never tested it. Can you file a ticket for this?

--
Stefan Zeiger
Typesafe - The software stack for applications that scale
Twitter: @StefanZeiger