Início / coding / Perguntas / 79352109
Accepted
David Bullock
Asked: 2025-01-13 19:56:31 +0800 CST

Existem problemas conhecidos na compilação de instruções preparadas que agrupam por CASO no SQL Server?

  • 772

Estou tentando agrupar com base em uma expressão case. O JOOQ abaixo parece ser a coisa certa a fazer e produz mais ou menos o SQL que espero. Do banco de dados 'sakila' (por favor, desculpe meu Kotlin, que tem que escapar 'when' e '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()

O SQL Server 2017 Express (v14) reclama:

A coluna 'DTB_DEV_SAKILA.dbo.actor.last_name' é inválida na lista de seleção porque não está contida em uma função de agregação ou na cláusula GROUP BY.

O que é uma surpresa, porque é. Aqui está o debug/stacktrace completo (eu o semi-formatei para legibilidade):

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.

O mais irritante é que se eu recortar/colar a consulta (conforme gerada pelo stacktrace) e substituir o espaço reservado/instrução preparada '?' por valores reais, o SSMS a executa perfeitamente.

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

Notas:

  • se eu usar inline()a função do JOOQ para me livrar dos '?'s da Declaração Preparada (ou seja, variáveis ​​de ligação), funciona perfeitamente;
  • não há trégua usando IIF em vez de CASE WHE N... ele reclama da mesma coisa.

Mas esse código (sem variáveis ​​de vinculação, eu acho, apenas referências de coluna) funciona perfeitamente:

val shared = length(ACTOR.LAST_NAME)

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

Então o problema é algum nexo de agrupamento por uma declaração de caso, ao tentar compilar um PreparedStatement.

Parece mais um problema de JDBC/SQL Server do que de JOOQ. Mas um usuário de JOOQ normalmente adotaria uma abordagem diferente da que está aqui? Existe uma solução alternativa? Outros bancos de dados têm essa limitação de agrupamento por a CASE?

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

sql-server

2 respostas

  1. Best Answer

    O problema é que seu código gerado especificou parâmetros usando ?. Isso é normal em JDBC/ODBC, mas o SQL Server não oferece suporte direto a isso. O que realmente acontece é que eles são traduzidos para @p1 @p2.

    Então o SQL gerado se torna o abaixo, o que é o motivo óbvio para não funcionar: os nomes dos parâmetros são diferentes, e o compilador não tem ideia de que eles são iguais (teoricamente, você poderia passar valores diferentes).

    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;

    Há várias maneiras de contornar isso. A mais fácil é provavelmente um APPLY. Não tenho certeza da sintaxe exata no jOOQ, mas acredito que ele a suporte.

    Você quer algo assim

    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;

    E no jOOQ parece algo como

    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()
    • 4

  2. É definitivamente uma ideia interessante que vale a pena explorar como um novo recurso de adesão no jOOQ:

    Ele não seria habilitado por padrão devido às muitas implicações sutis que isso pode ter em métodos como Query.getBindValues(), ou quaisquer suposições feitas por usuários com relação à ordenação de valores de bind. O JDBC não suporta parâmetros nomeados, mas seria possível emular assim:

    -- Ordinary indexed parameters
declare @p1 varchar(max) = ?;
declare @p2 varchar(max) = ?;
declare @p3 varchar(max) = ?;
declare @p4 varchar(max) = ?;
declare @p5 varchar(max) = ?;

-- Now, the named parameters can be re-used safely
select
  count(*), 
  case
    when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like @p1 then @p2
    when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like @p3 then @p4 
    else @p5
  end [code] 
from [DTB_DEV_SAKILA].[dbo].[actor] 
group by
  case
    when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like @p1 then @p2
    when [DTB_DEV_SAKILA].[dbo].[actor].[last_name] like @p3 then @p4 
    else @p5
  end

    Isso também é o que o mssql-jdbc faz nos bastidores, exceto que ele não sabe que alguns dos valores de bind são idênticos (ele não pode saber disso, e o jOOQ não pode transmitir essa informação para o mssql-jdbc). Mas o jOOQ pode saber essa informação porque a identidade dos respectivos pares de wrappers de valor de bind (digitados org.jooq.Param) é a mesma.

    Enquanto isso, a abordagem mais simples é usar DSL.inline()o que você mencionou, para criar valores inline na expressão compartilhada para evitar variáveis ​​de vinculação:

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

    Como alternativa, basta executar uma instrução estática para esta consulta

    • 1

relate perguntas