window functions broken when used in filter

[mberndt123]

Hi, thanks for creating this project!

I have a query that looks basically like this:

FooTable.map { foo => 
  (foo, count_* :: Over.partitionBy(foo.bar))
}.filter( _._2 === 1L).map(_._1)

This yields the error “window functions are not allowed in WHERE”.

However it's possible to make it work using subquery:

FooTable.map { foo => 
  (foo, Over.partitionBy(foo.bar).::(count_*))
}.subquery.filter( _._2 === 1L).map(_._1)

However the comment about subquery says that “If a query works with an explicit .subquery call but fails without, this should be considered a bug in Slick”, hence this report.

[mberndt123]

Note that I'm not sure what the solution would be here. It's probably not possible to automatically generate subqueries in such cases because it's inherently ambiguous. Take this query:

Foo.filter(f => f.x && Over.partitionBy(f.y).::(count_*) > 3)`

What should the meaning be? This

Foo
  .map(f => (f, Over.partitionBy(f.y).::(count_*) > 3))
  .subquery
  .filter(_._2)
  .map(_._1)
  .filter(_.x)

or this:

Foo
  .filter(_.x)
  .map(f => (f, Over.partitionBy(f.y).::(count_*) > 3))
  .subquery
  .filter(_._2)
  .map(_._1)

The only solution I can see is what the SQL designers did: forbid window functions in where clauses, but doing this on the type level is probably non-trivial.