Are there known issues compiling Prepared Statements that group by CASE in SQL Server?

Question

I am trying to group based on a case expression. The below JOOQ seems like the right thing to do and produces more or less the SQL I expect. From the 'sakila' database (please excuse my Kotlin, which has to escape 'when' and 'as'):

val shared = `when`(ACTOR.LAST_NAME.like("A%"), "A")
    .`when`(ACTOR.LAST_NAME.like("B%"), "B")
    .otherwise("C")

val r = ctx.select(
    count(), shared.`as`("code")
)
    .from(ACTOR)
    .groupBy(shared)
    .fetch()

SQL Server 2017 Express (v14) complains:

Column 'DTB_DEV_SAKILA.dbo.actor.last_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Which is a surprise, because it is. Here's the full debug/stacktrace (I have semi-formatted it for readability):

Exception in thread "main" org.jooq.exception.DataAccessException: SQL [
select count(*), 
case when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like ? then ? 
when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like ? then ? 
else ? end [code] 
from [DTB_DEV_SAKILA].[dbo].[actor] 
group by case when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like ? then ? 
when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like ? then ? else ? end
]; Column 'DTB_DEV_SAKILA.dbo.actor.last_name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Infuriatingly, if I cut/paste the query (as spat out by the stacktrace) and replace the placeholder/prepared-statement '?'s with real values, SSMS executes it just fine.

select 
    count(*), 
    case 
        when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like 'A%' 
            then 'A' 
        when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like 'B%' 
            then 'B' 
        else 'C' 
    end as code
from
    [DTB_DEV_SAKILA].[dbo].[actor] 
group by 
    case 
        when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like 'A%' then 'A' 
        when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like 'B%' then 'B' 
        else 'C' 
    end as code

Notes:

• if I use JOOQ's inline() function to get rid of the Prepared Statement '?'s (er, bind-variables), it works just fine;
• there is no respite using IIF instead of CASE WHE N ... it complains of the same thing.

But this code (no bind variables, I guess, just column references) runs just fine:

val shared = length(ACTOR.LAST_NAME)

val r = ctx.select(
    count(), shared.`as`("voluminousness")
)
    .from(ACTOR)
    .groupBy(shared)
    .fetch()

So the issue is some nexus of grouping-by a case statement, when trying to compile a PreparedStatement.

It seems more a JDBC / SQL Server issue, than a JOOQ one. But would a JOOQ user normally take a different approach than the one here? Is there a workaround? Do other databases have such a limitation grouping-by a CASE?

JOOQ 3.19.10, MS JDBC 12.8.1.jre11, SQL Server 2017 (v14.0.2065)

Answer 1

The problem is that your generated code has specified parameters using ?. This is normal in JDBC/ODBC, but SQL Server doesn't directly support this. What actually happens is that they are translated into @p1 @p2.

So your generated SQL becomes the below, which is obvious why it doesn't work: the parameter names are different, and the compiler has no idea they are the same (theoretically you could pass through different values).

select
  count(*), 
  case when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like @p0 then @p1 
       when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like @p2 then @p3 
       else ?
  end [code] 
from [DTB_DEV_SAKILA].[dbo].[actor] 
group by
  case when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like @p4 then @p5 
       when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like @p6 then @p7 
       else ?
  end;

There are a number of ways around this. The easiest is probably an APPLY. I'm not sure the exact syntax in jOOQ but I believe it does support it.

You want something like this

select
  count(*), 
  v.code
from [DTB_DEV_SAKILA].[dbo].[actor] a
cross apply (
    select
      case when a.[last_name] like ? then ?
           when a.[last_name] like ? then ?
           else ?
      end as code
) v
group by
  v.CaseExpr;

And in jOOQ it seems maybe something like

val shared = `when`(ACTOR.LAST_NAME.like("A%"), "A")
    .`when`(ACTOR.LAST_NAME.like("B%"), "B")
    .otherwise("C")

val calc = select(shared.`as`("code")

val r = ctx.select(
    count(), 
    calc.shared
)
    .from(ACTOR.crossApply(calc))
    .groupBy(calc.shared)
    .fetch()