Slick 3.3.3 .subquery used in rankOver doesn't add alias

[swathi patnaikuni]

I'm trying to replicate the below query using slick:

PSQL query:

```
select ranked.* from (
select queue_id, id, rank() over (partition by (queue_id) order by created_at desc)
from asyncjobs
where status in ('QUEUED')
)ranked where rank=1
```

Implementation in Scala:

private def rankOver[T0, T1](partition: Rep[T0], ordering: Rep[T1], descending: Boolean = false): Rep[Int] = {
val expr = SimpleExpression.binary[T0, T1, Int] { (partitionExpr, orderByExpr, builder) =>
// see http://www.postgresql.org/docs/current/static/tutorial-window.html
builder.sqlBuilder += "(rank() over (partition by "
builder.expr(partitionExpr)
builder.sqlBuilder += " order by "
builder.expr(orderByExpr)
if (descending) builder.sqlBuilder += " desc"
builder.sqlBuilder += "))"
()
}
expr(partition, ordering)
}

 def getTopQueuedJobsSortedByCreatedAt(status: AsyncJobStatus, limit: Int = 1) = { 

......

asyncJobs.map(asyncJob => (asyncJob,rankOver(asyncJob.queueId,asyncJob.createdAt)))

.subquery
.filter { case (_, rank) => rank <= 1 }
.map { case (asj, _) => asj } 

 .......

}

Generated Query:

select x2, x3, x4, x5, x6, x7, x8, x9, x10, x11, x12, x13, x14, x15, x16, x17, x18, x19, x20, x21, x22, x23 from (select "queue_id" as x8, "updated_at" as x17, "entity_ref_id" as x9, "profile_instance_id" as x19, "job_payload" as x14, "status" as x7, "entity_ref_json" as x11, "sandbox" as x6, "sandbox_real_id" as x5, "combiner_id" as x12, "entity_ref_type" as x10, "created_at" as x16, "ext_created_at" as x15, "id" as x2, "ext_job_retry_attempts" as x22, "ext_job_attributes" as x21, "attempt" as x18, "ims_org_id" as x4, "tracing_id" as x23, "ext_job_id" as x3, "job_config" as x13, "schema_class_id" as x20, rank() over (partition by ("queue_id") order by "created_at") as x24 from "asyncjobs" where "status" = 'QUEUED') where x24 <= 1

This fails with the exception:

*****ERROR: subquery in FROM must have an alias

  Hint: For example, FROM (SELECT ...) [AS] foo.

As clearly the generated query doesn't have any "alias"

This query works on Postgres:

select .... from( subquery...) alias where ...

Why doesn't the .subquery add an alias in Slick?