Problems with arithmetic in groupBy. Concerns 1.0.1 and 2.0.0-M2

[Björn Herzig]

Hi,

I tried to post this before but I'm afraid, but I'm afraid Google ate it for some reason.
Therefore second try:

Slick produces runtime exceptions when trying to do arithmetic in groupBy operations.
This happens in 1.0.1 as well as in 2.0.0-M2.

I've isolated a testcase for each version:

1.0.1:

import scala.slick.driver.H2Driver.simple._
import Database.threadLocalSession

case class Tab(col1: String, col2: String, col3: String, col4: Int, col5: Int)

object Tables {
object Tabs extends Table[Tab]("TAB") {
def col1 = column[String]("COL1")
def col2 = column[String]("COL2")
def col3 = column[String]("COL3")
def col4 = column[Int]("COL4")
def col5 = column[Int]("COL5")

def * = col1 ~ col2 ~ col3 ~
col4 ~ col5 <> (Tab.apply _, Tab.unapply _)
}
}

object Main extends App {
import Tables._

Database.forURL("jdbc:h2:mem:test1", driver = "org.h2.Driver") withSession {
Tabs.ddl.create

Tabs.insertAll(
Tab("foo", "bar", "bat", 1, 2),
Tab("foo", "bar", "bat", 2, 3),
Tab("foo", "quux", "bat", 3, 4),
Tab("baz", "quux", "bat", 4, 5)
)

// works
//val query = Query(Tabs).map {
//t => (t.col1, t.col4 + t.col5)
//}

val query = Query(Tabs).groupBy(_.col1).map {
case (grp, t) => (grp, t.map(x => x.col4 + x.col5).sum)
}

println(query.list)
}
}

Resulting exception:

Loading /home/raichoo/Local/sbt/sbt-0.13.0/bin/sbt-launch-lib.bash
[0m[ [0minfo [0m] [0mSet current project to slicktest (in build file:/mnt/nami/slicktest/) [0m
[0m[ [0minfo [0m] [0mRunning Main [0m
[0m[ [31merror [0m] [0m(run-main) org.h2.jdbc.JdbcSQLException: Column "X2.COL1" not found; SQL statement: [0m
[0m[ [31merror [0m] [0mCREATE FORCE VIEW PUBLIC._0 AS [0m
[0m[ [31merror [0m] [0mSELECT [0m
[0m[ [31merror [0m] [0m X2.COL1 AS X6, [0m
[0m[ [31merror [0m] [0m X2.COL4 AS X4, [0m
[0m[ [31merror [0m] [0m X2.COL5 AS X5 [0m
[0m[ [31merror [0m] [0mFROM SYSTEM_RANGE(1, 1) [42122-166] [0m
org.h2.jdbc.JdbcSQLException: Column "X2.COL1" not found; SQL statement:
CREATE FORCE VIEW PUBLIC._0 AS
SELECT
X2.COL1 AS X6,
X2.COL4 AS X4,
X2.COL5 AS X5
FROM SYSTEM_RANGE(1, 1) [42122-166]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
at org.h2.message.DbException.get(DbException.java:169)
at org.h2.message.DbException.get(DbException.java:146)
at org.h2.expression.ExpressionColumn.optimize(ExpressionColumn.java:138)
at org.h2.expression.Alias.optimize(Alias.java:47)
at org.h2.command.dml.Select.prepare(Select.java:799)
at org.h2.command.Parser.prepare(Parser.java:202)
at org.h2.engine.Session.prepare(Session.java:388)
at org.h2.engine.Session.prepare(Session.java:375)
at org.h2.table.TableView.compileViewQuery(TableView.java:99)
at org.h2.table.TableView.initColumnsAndTables(TableView.java:144)
at org.h2.table.TableView.init(TableView.java:95)
at org.h2.table.TableView.<init>(TableView.java:61)
at org.h2.table.TableView.createTempView(TableView.java:420)
at org.h2.command.Parser.readTableFilter(Parser.java:1037)
at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1690)
at org.h2.command.Parser.parseSelectSimple(Parser.java:1797)
at org.h2.command.Parser.parseSelectSub(Parser.java:1684)
at org.h2.command.Parser.parseSelectUnion(Parser.java:1527)
at org.h2.command.Parser.parseSelect(Parser.java:1515)
at org.h2.command.Parser.readTerm(Parser.java:2444)
at org.h2.command.Parser.readFactor(Parser.java:2046)
at org.h2.command.Parser.readSum(Parser.java:2033)
at org.h2.command.Parser.readConcat(Parser.java:2006)
at org.h2.command.Parser.readCondition(Parser.java:1871)
at org.h2.command.Parser.readAnd(Parser.java:1852)
at org.h2.command.Parser.readExpression(Parser.java:1844)
at org.h2.command.Parser.readTerm(Parser.java:2558)
at org.h2.command.Parser.readFactor(Parser.java:2046)
at org.h2.command.Parser.readSum(Parser.java:2033)
at org.h2.command.Parser.readConcat(Parser.java:2006)
at org.h2.command.Parser.readCondition(Parser.java:1871)
at org.h2.command.Parser.readAnd(Parser.java:1852)
at org.h2.command.Parser.readExpression(Parser.java:1844)
at org.h2.command.Parser.parseSelectSimpleSelectPart(Parser.java:1757)
at org.h2.command.Parser.parseSelectSimple(Parser.java:1789)
at org.h2.command.Parser.parseSelectSub(Parser.java:1684)
at org.h2.command.Parser.parseSelectUnion(Parser.java:1527)
at org.h2.command.Parser.parseSelect(Parser.java:1515)
at org.h2.command.Parser.parsePrepared(Parser.java:405)
at org.h2.command.Parser.parse(Parser.java:279)
at org.h2.command.Parser.parse(Parser.java:251)
at org.h2.command.Parser.prepareCommand(Parser.java:217)
at org.h2.engine.Session.prepareLocal(Session.java:415)
at org.h2.engine.Session.prepareCommand(Session.java:364)
at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1111)
at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:71)
at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:628)
at scala.slick.session.Session$class.prepareStatement(Session.scala:29)
at scala.slick.session.BaseSession.prepareStatement(Session.scala:201)
at scala.slick.jdbc.StatementInvoker.results(StatementInvoker.scala:29)
at scala.slick.jdbc.StatementInvoker.elementsTo(StatementInvoker.scala:17)
at scala.slick.jdbc.Invoker$class.foreach(Invoker.scala:90)
at scala.slick.jdbc.StatementInvoker.foreach(StatementInvoker.scala:10)
at scala.slick.jdbc.Invoker$class.build(Invoker.scala:66)
at scala.slick.jdbc.StatementInvoker.build(StatementInvoker.scala:10)
at scala.slick.jdbc.Invoker$class.list(Invoker.scala:56)
at scala.slick.jdbc.StatementInvoker.list(StatementInvoker.scala:10)
at scala.slick.jdbc.UnitInvoker$class.list(Invoker.scala:150)
at scala.slick.driver.BasicInvokerComponent$QueryInvoker.list(BasicInvokerComponent.scala:19)
at Main$$anonfun$1.apply$mcV$sp(Test.scala:42)
at Main$$anonfun$1.apply(Test.scala:23)
at Main$$anonfun$1.apply(Test.scala:23)
at scala.util.DynamicVariable.withValue(DynamicVariable.scala:57)
at scala.slick.session.Database$$anonfun$withSession$1.apply(Database.scala:46)
at scala.slick.session.Database$$anonfun$withSession$1.apply(Database.scala:46)
at scala.slick.session.Database.withSession(Database.scala:38)
at scala.slick.session.Database.withSession(Database.scala:46)
at Main$delayedInit$body.apply(Test.scala:23)
at scala.Function0$class.apply$mcV$sp(Function0.scala:40)
at scala.runtime.AbstractFunction0.apply$mcV$sp(AbstractFunction0.scala:12)
at scala.App$$anonfun$main$1.apply(App.scala:71)
at scala.App$$anonfun$main$1.apply(App.scala:71)
at scala.collection.immutable.List.foreach(List.scala:318)
at scala.collection.generic.TraversableForwarder$class.foreach(TraversableForwarder.scala:32)
at scala.App$class.main(App.scala:71)
at Main$.main(Test.scala:20)
at Main.main(Test.scala)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
[0m[ [31mtrace [0m] [0mStack trace suppressed: run [34mlast compile:run [0m for the full output. [0m
java.lang.RuntimeException: Nonzero exit code: 1
at scala.sys.package$.error(package.scala:27)
[0m[ [31mtrace [0m] [0mStack trace suppressed: run [34mlast compile:run [0m for the full output. [0m
[0m[ [31merror [0m] [0m(compile: [31mrun [0m) Nonzero exit code: 1 [0m
[0m[ [31merror [0m] [0mTotal time: 4 s, completed Sep 25, 2013 2:44:34 PM [0m

2.0.0-M2:

import scala.slick.driver.H2Driver.simple._

object Tables {
type Tab = (String, String, String, Int, Int)
class Tabs(tag: Tag) extends Table[Tab](tag, "TAB") {
def col1 = column[String]("COL1")
def col2 = column[String]("COL2")
def col3 = column[String]("COL3")
def col4 = column[Int]("COL4")
def col5 = column[Int]("COL5")

def * = (col1, col2, col3, col4, col5)
}
}

object Main extends App {
import Tables._

val db = Database.forURL("jdbc:h2:mem:test1", driver = "org.h2.Driver")
val Q = TableQuery[Tabs]

db.withSession {
implicit session: Session =>

Q.ddl.create
Q.insertAll(
("foo", "bar", "bat", 1, 2),
("foo", "bar", "bat", 2, 3),
("foo", "quux", "bat", 3, 4),
("baz", "quux", "bat", 4, 5)
)

// works
//val query = Q.map(
//t => (t.col1, t.col4 + t.col5)
//)

val query = Q.groupBy(t => (t.col1, t.col2, t.col3)).map({
case (grp, t) => ( grp._1
, grp._2
, grp._3
, t.map(x => x.col4 + x.col5).sum
)
})

println(query.list)
}
}

Resulting exception:

Loading /home/raichoo/Local/sbt/sbt-0.13.0/bin/sbt-launch-lib.bash
[0m[ [0minfo [0m] [0mSet current project to slick2test (in build file:/mnt/nami/slick2test/) [0m
[0m[ [0minfo [0m] [0mRunning Main [0m
[0m[ [31merror [0m] [0m(run-main) scala.slick.SlickException: Expected a collection type, found NominalType($@158216890)({s2: String/VARCHAR, s3: String/VARCHAR, s4: String/VARCHAR, s5: Int/INTEGER, s6: Int/INTEGER}) [0m
scala.slick.SlickException: Expected a collection type, found NominalType($@158216890)({s2: String/VARCHAR, s3: String/VARCHAR, s4: String/VARCHAR, s5: Int/INTEGER, s6: Int/INTEGER})
at scala.slick.ast.TypeUtil$.asCollectionType$extension(Type.scala:160)
at scala.slick.ast.Comprehension$$anonfun$3.apply(Comprehension.scala:42)
at scala.slick.ast.Comprehension$$anonfun$3.apply(Comprehension.scala:40)
at scala.collection.LinearSeqOptimized$class.foldLeft(LinearSeqOptimized.scala:111)
at scala.collection.immutable.List.foldLeft(List.scala:84)
at scala.slick.ast.Comprehension.nodeWithComputedType2(Comprehension.scala:40)
at scala.slick.ast.Comprehension.nodeWithComputedType2(Comprehension.scala:7)
at scala.slick.ast.Node$class.nodeWithComputedType(Node.scala:115)
at scala.slick.ast.Comprehension.nodeWithComputedType(Comprehension.scala:7)
at scala.slick.compiler.FuseComprehensions.fuse(Relational.scala:165)
at scala.slick.compiler.FuseComprehensions$$anonfun$7.apply(Relational.scala:158)
at scala.slick.compiler.FuseComprehensions$$anonfun$7.apply(Relational.scala:158)
at scala.slick.ast.Util$$anonfun$mapOrNone$1.apply(Util.scala:25)
at scala.slick.ast.Util$$anonfun$mapOrNone$1.apply(Util.scala:24)
at scala.collection.mutable.ResizableArray$class.foreach(ResizableArray.scala:59)
at scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:47)
at scala.slick.ast.Util$.mapOrNone(Util.scala:24)
at scala.slick.ast.Node$class.nodeMapChildren(Node.scala:47)
at scala.slick.ast.Apply.nodeMapChildren(Node.scala:503)
at scala.slick.compiler.FuseComprehensions.fuse(Relational.scala:158)
at scala.slick.compiler.FuseComprehensions$$anonfun$7.apply(Relational.scala:158)
at scala.slick.compiler.FuseComprehensions$$anonfun$7.apply(Relational.scala:158)
at scala.slick.ast.Util$$anonfun$mapOrNone$1.apply(Util.scala:25)
at scala.slick.ast.Util$$anonfun$mapOrNone$1.apply(Util.scala:24)
at scala.collection.mutable.ResizableArray$class.foreach(ResizableArray.scala:59)
at scala.collection.mutable.ArrayBuffer.foreach(ArrayBuffer.scala:47)
at scala.slick.ast.Util$.mapOrNone(Util.scala:24)
at scala.slick.ast.Node$class.nodeMapChildren(Node.scala:47)
at scala.slick.ast.ProductNode$$anon$2.nodeMapChildren(Node.scala:161)
at scala.slick.compiler.FuseComprehensions.fuse(Relational.scala:158)
at scala.slick.compiler.FuseComprehensions$$anonfun$7.apply(Relational.scala:158)
at scala.slick.compiler.FuseComprehensions$$anonfun$7.apply(Relational.scala:158)
at scala.slick.ast.Util$$anonfun$mapOrNone$1.apply(Util.scala:25)
at scala.slick.ast.Util$$anonfun$mapOrNone$1.apply(Util.scala:24)
at scala.collection.immutable.List.foreach(List.scala:318)
at scala.slick.ast.Util$.mapOrNone(Util.scala:24)
at scala.slick.ast.Node$class.nodeMapChildren(Node.scala:47)
at scala.slick.ast.Pure.nodeMapChildren(Node.scala:256)
at scala.slick.compiler.FuseComprehensions.fuse(Relational.scala:158)
at scala.slick.compiler.FuseComprehensions$$anonfun$7.apply(Relational.scala:158)
at scala.slick.compiler.FuseComprehensions$$anonfun$7.apply(Relational.scala:158)
at scala.slick.ast.Util$$anonfun$mapOrNone$1.apply(Util.scala:25)
at scala.slick.ast.Util$$anonfun$mapOrNone$1.apply(Util.scala:24)
at scala.collection.immutable.List.foreach(List.scala:318)
at scala.slick.ast.Util$.mapOrNone(Util.scala:24)
at scala.slick.ast.Node$class.nodeMapChildren(Node.scala:47)
at scala.slick.ast.Comprehension.nodeMapChildren(Comprehension.scala:7)
at scala.slick.compiler.FuseComprehensions.fuse(Relational.scala:158)
at scala.slick.compiler.FuseComprehensions$$anonfun$apply$4$$anonfun$apply$5.apply(Relational.scala:155)
at scala.slick.compiler.FuseComprehensions$$anonfun$apply$4$$anonfun$apply$5.apply(Relational.scala:155)
at scala.slick.ast.ClientSideOp$.mapServerSide(ClientSideOp.scala:19)
at scala.slick.ast.ClientSideOp$$anonfun$mapServerSide$1.apply(ClientSideOp.scala:18)
at scala.slick.ast.ClientSideOp$$anonfun$mapServerSide$1.apply(ClientSideOp.scala:18)
at scala.slick.ast.ResultSetMapping$$anonfun$1.apply(ClientSideOp.scala:65)
at scala.slick.ast.ResultSetMapping$$anonfun$1.apply(ClientSideOp.scala:64)
at scala.Function2$$anonfun$tupled$1.apply(Function2.scala:54)
at scala.Function2$$anonfun$tupled$1.apply(Function2.scala:53)
at scala.collection.TraversableLike$$anonfun$map$1.apply(TraversableLike.scala:244)
at scala.collection.TraversableLike$$anonfun$map$1.apply(TraversableLike.scala:244)
at scala.collection.Iterator$class.foreach(Iterator.scala:727)
at scala.collection.AbstractIterator.foreach(Iterator.scala:1157)
at scala.collection.IterableLike$class.foreach(IterableLike.scala:72)
at scala.collection.AbstractIterable.foreach(Iterable.scala:54)
at scala.collection.TraversableLike$class.map(TraversableLike.scala:244)
at scala.collection.AbstractTraversable.map(Traversable.scala:105)
at scala.slick.ast.DefNode$class.nodeMapScopedChildren(Symbol.scala:69)
at scala.slick.ast.ResultSetMapping.nodeMapScopedChildren(ClientSideOp.scala:42)
at scala.slick.ast.ResultSetMapping.nodeMapServerSide(ClientSideOp.scala:64)
at scala.slick.ast.ResultSetMapping.nodeMapServerSide(ClientSideOp.scala:42)
at scala.slick.ast.ClientSideOp$.mapServerSide(ClientSideOp.scala:18)
at scala.slick.compiler.FuseComprehensions$$anonfun$apply$4.apply(Relational.scala:155)
at scala.slick.compiler.FuseComprehensions$$anonfun$apply$4.apply(Relational.scala:154)
at scala.slick.compiler.CompilerState.map(QueryCompiler.scala:143)
at scala.slick.compiler.FuseComprehensions.apply(Relational.scala:154)
at scala.slick.compiler.QueryCompiler$$anonfun$runPhase$1.apply(QueryCompiler.scala:55)
at scala.slick.compiler.QueryCompiler$$anonfun$runPhase$1.apply(QueryCompiler.scala:54)
at scala.util.DynamicVariable.withValue(DynamicVariable.scala:57)
at scala.slick.ast.SymbolNamer.use(Symbol.scala:114)
at scala.slick.compiler.QueryCompiler.runPhase(QueryCompiler.scala:54)
at scala.slick.compiler.QueryCompiler$$anonfun$run$2.apply(QueryCompiler.scala:46)
at scala.slick.compiler.QueryCompiler$$anonfun$run$2.apply(QueryCompiler.scala:46)
at scala.collection.TraversableOnce$$anonfun$foldLeft$1.apply(TraversableOnce.scala:144)
at scala.collection.TraversableOnce$$anonfun$foldLeft$1.apply(TraversableOnce.scala:144)
at scala.collection.Iterator$class.foreach(Iterator.scala:727)
at scala.collection.AbstractIterator.foreach(Iterator.scala:1157)
at scala.collection.IterableLike$class.foreach(IterableLike.scala:72)
at scala.collection.AbstractIterable.foreach(Iterable.scala:54)
at scala.collection.TraversableOnce$class.foldLeft(TraversableOnce.scala:144)
at scala.collection.AbstractTraversable.foldLeft(Traversable.scala:105)
at scala.slick.compiler.QueryCompiler.run(QueryCompiler.scala:46)
at scala.slick.compiler.QueryCompiler.run(QueryCompiler.scala:41)
at scala.slick.driver.JdbcProfile$LowPriorityImplicits$class.queryToAppliedQueryInvoker(JdbcProfile.scala:40)
at scala.slick.driver.JdbcProfile$$anon$1.queryToAppliedQueryInvoker(JdbcProfile.scala:21)
at Main$$anonfun$2.apply(Test.scala:46)
at Main$$anonfun$2.apply(Test.scala:23)
at scala.slick.backend.DatabaseComponent$DatabaseDef$class.withSession(DatabaseComponent.scala:31)
at scala.slick.jdbc.JdbcBackend$DatabaseFactoryDef$$anon$5.withSession(JdbcBackend.scala:61)
at Main$delayedInit$body.apply(Test.scala:22)
at scala.Function0$class.apply$mcV$sp(Function0.scala:40)
at scala.runtime.AbstractFunction0.apply$mcV$sp(AbstractFunction0.scala:12)
at scala.App$$anonfun$main$1.apply(App.scala:71)
at scala.App$$anonfun$main$1.apply(App.scala:71)
at scala.collection.immutable.List.foreach(List.scala:318)
at scala.collection.generic.TraversableForwarder$class.foreach(TraversableForwarder.scala:32)
at scala.App$class.main(App.scala:71)
at Main$.main(Test.scala:16)
at Main.main(Test.scala)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
[0m[ [31mtrace [0m] [0mStack trace suppressed: run [34mlast compile:run [0m for the full output. [0m
java.lang.RuntimeException: Nonzero exit code: 1
at scala.sys.package$.error(package.scala:27)
[0m[ [31mtrace [0m] [0mStack trace suppressed: run [34mlast compile:run [0m for the full output. [0m
[0m[ [31merror [0m] [0m(compile: [31mrun [0m) Nonzero exit code: 1 [0m
[0m[ [31merror [0m] [0mTotal time: 4 s, completed Sep 25, 2013 2:54:43 PM [0m

Kind regards,
Björn

[Pedro Furlanetto]

It might help printing the queries' .selectStatement.

> --
>
> ---
> You received this message because you are subscribed to the Google Groups "Slick / ScalaQuery" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to scalaquery+...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.

[Christopher Vogt]

Using .map on groups seems definitely broken. I created a ticket for
this issue

https://www.assembla.com/spaces/typesafe-slick/tickets/312-groupby-broken--expected-a-collection-type--found-nominaltype

and more for other related issues I found:

https://www.assembla.com/spaces/aI9RDsj1ar4ziEacwqjQWU/tickets/315

Chris
--
Slick Team

[Christopher Vogt]

What works for bow is doing the projection before the grouping:

val query = Tabs.map(t => (t,t.col4 + t.col5)).groupBy(_._1.col1).map {
case (grp, t) => (grp, t.map(_._2).sum)
}

Chris

[her...@saltation.de]

Yep, this is how I do it at the moment. Glad to see that you are aware of this issue :)

 

Chris

Kind regards,

Björn