Como seguramente sabes, cuando escribes un SELECT puedes agrupar los datos que obtendrás como resultado si utilizas la cláusula GROUP BY. Esto es muy útil en muchas ocasiones pero también a veces podrías encontrarte con un error y no entiendes el motivo. Aquí se muestra uno de esos casos.

Este artículo está basado en una consulta que respondieron Dimitry Sibiryakov y Dmitry Yemanov, desarrolladores de Firebird.

Supongamos que quieres usar COLLATE para indicarle al Firebird el orden en que debe aparecer el texto dentro de una columna CHAR o VARCHAR.

Un SELECT que sí funciona

SELECT
   CLI_NOMBRE COLLATE ES_ES_CI_AI
FROM
   CLIENTES
GROUP BY
   CLI_NOMBRE
ORDER BY
   CLI_NOMBRE

Otro SELECT que sí funciona

SELECT
   CLI_NOMBRE COLLATE ES_ES_CI_AI
FROM
   CLIENTES
GROUP BY
   CLI_NOMBRE COLLATE ES_ES_CI_AI
ORDER BY
   CLI_NOMBRE COLLATE ES_ES_CI_AI

Un SELECT que no funciona

SELECT
   CLI_NOMBRE
FROM
   CLIENTES
GROUP BY
   CLI_NOMBRE COLLATE ES_ES_CI_AI
ORDER BY
   CLI_NOMBRE COLLATE ES_ES_CI_AI

Al ejecutar el tercer SELECT el Firebird muestra el mensaje:

Invalid token.
Dynamic SQL Error.
SQL error code = -104.
Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause).

¿Por qué, qué pasó? ¿Por qué los dos primeros SELECT funcionaron bien y el tercero no?

El primero funcionó bien porque está agrupado por el valor original.

El segundo funcionó bien porque se usa el mismo COLLATE en el resultado y en el grupo. Siempre que uses exactamente los mismos valores en el resultado y en el grupo, funcionará.

Sin embargo, el tercero es diferente porque en el resultado se desea obtener el valor original pero se lo agrupa por el COLLATE y eso es contradictorio, ya que el agrupamiento siempre debe hacerse por el valor original o por un valor que lo incluya. Y como en el tercer ejemplo no es así, por eso obtendrás el error que se mostró arriba.

El problema puede ser más fácil de entender con los siguientes ejemplos:

  1. SELECT SIN(X) … GROUP BY X
  2. SELECT SIN(X) … GROUP BY SIN(X)
  3. SELECT X … GROUP BY SIN(X)

No puedes pedir que te muestre la columna X y que la agrupe por el seno de X ya que algo así no tiene sentido. Piensa en COLLATE como si se tratara de una especie de CAST. Al hacer un COLLATE se sobreescriben las reglas de la comparación y por lo tanto “MiColumna” y “MiColumna COLLATE ES_ES_CI_AI” pueden producir diferentes agrupamientos. Por ejemplo:

COLLATE1

Captura 1. Si haces clic en la imagen la verás más grande

En la Captura 1 vemos los datos de los empleados, tal y como fueron cargados en la tabla.

SELECT
   EMP_NOMBRE
FROM
   EMPLEADOS
GROUP BY
   EMP_NOMBRE

COLLATE2

Captura 2. Si haces clic en la imagen la verás más grande

En la Captura 2 vemos el resultado de escribir un SELECT sin COLLATE. Nos muestra los datos agrupados como fueron cargados.

SELECT
   EMP_NOMBRE COLLATE ES_ES_CI_AI
FROM
   EMPLEADOS
GROUP BY
   EMP_NOMBRE COLLATE ES_ES_CI_AI

COLLATE3

Captura 3. Si haces clic en la imagen la verás más grande

En la Captura 3 vemos el resultado de usar la cláusula COLLATE. Como puedes ver se obtiene un resultado diferente y además el nombre de la columna cambió a CAST. Eso significa que internamente para el Firebird el COLLATE es una especie de CAST. Y también explica el motivo por el cual obtuvimos un error en nuestro tercer SELECT: porque la cláusula GROUP BY requiere que la lista de columnas en el SELECT y la lista de columnas en el GROUP BY sean exactamente las mismas expresiones; y cuando no es así muestra un mensaje de error.

Artículo relacionado:

El índice del blog Firebird21

Anuncios