MySQL
Error 1055: ONLY_FULL_GROUP_BY: algo no está en la cláusula GROUP BY ...

Introducción

Observaciones

SET  sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

Uso y mal uso de GROUP BY

 SELECT item.item_id, item.name,     /* not SQL-92 */ 
        COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

mostrará las filas en una tabla llamada item y mostrará el recuento de filas relacionadas en una tabla llamada uses . Esto funciona bien, pero desafortunadamente no es estándar SQL-92.

Por qué no? porque la cláusula SELECT (y la cláusula ORDER BY ) en las consultas GROUP BY deben contener columnas que son

1. mencionado en la cláusula GROUP BY , o
2. funciones agregadas como COUNT() , MIN() y similares.

La cláusula SELECT este ejemplo menciona item.name , una columna que no cumple ninguno de esos criterios. MySQL 5.6 y anteriores rechazarán esta consulta si el modo SQL contiene ONLY_FULL_GROUP_BY .

Esta consulta de ejemplo se puede hacer para cumplir con el estándar SQL-92 cambiando la cláusula GROUP BY , de esta manera.

SELECT item.item_id, item.name, 
       COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id, item.name

El último estándar SQL-99 permite que una instrucción SELECT omita las columnas no agregadas de la clave de grupo si el DBMS puede probar una dependencia funcional entre ellas y las columnas de la clave de grupo. Debido a que item.name es funcionalmente dependiente de item.item_id , el ejemplo inicial es válido SQL-99. MySQL ganó un probador de dependencia funcional en la versión 5.7. El ejemplo original funciona en ONLY_FULL_GROUP_BY .

Mal uso de GROUP BY para devolver resultados impredecibles: la ley de Murphy

SELECT item.item_id, uses.category,   /* nonstandard */ 
       COUNT(*) number_of_uses 
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

mostrará las filas en una tabla llamada elemento y mostrará el recuento de filas relacionadas en una tabla llamada usos. También mostrará el valor de una columna llamada uses.category .

Esta consulta funciona en MySQL (antes de que ONLY_FULL_GROUP_BY indicador ONLY_FULL_GROUP_BY ). Utiliza la extensión no estándar de MySQL para GROUP BY .

Pero la consulta tiene un problema: si varias filas en la tabla de uses coinciden con la condición ON en la cláusula JOIN , MySQL devuelve la columna de category de solo una de esas filas. Que fila El autor de la consulta y el usuario de la aplicación no lo saben de antemano. Hablando formalmente, es impredecible : MySQL puede devolver cualquier valor que desee.

Lo impredecible es como aleatorio, con una diferencia significativa. Uno podría esperar que una elección aleatoria cambie de vez en cuando. Por lo tanto, si una elección fuera aleatoria, podría detectarla durante la depuración o la prueba. El resultado impredecible es peor: MySQL devuelve el mismo resultado cada vez que usa la consulta, hasta que no lo hace. A veces es una nueva versión del servidor MySQL que causa un resultado diferente. A veces es una mesa cada vez mayor que causa el problema. Lo que puede salir mal, saldrá mal, y cuando no lo espere. Eso se llama la ley de Murphy .

El equipo de MySQL ha estado trabajando para que a los desarrolladores les resulte más difícil cometer este error. Las nuevas versiones de MySQL en la secuencia de 5,7 tienen un sql_mode bandera llamada ONLY_FULL_GROUP_BY . Cuando se establece ese indicador, el servidor MySQL devuelve el error 1055 y se niega a ejecutar este tipo de consulta.

Mal uso de GROUP BY con SELECT *, y cómo solucionarlo.

A veces, una consulta se ve así, con un * en la cláusula SELECT .

 SELECT item.*,     /* nonstandard */ 
        COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

Dicha consulta debe ser reformulada para cumplir con el estándar ONLY_FULL_GROUP_BY .

Para ello, necesitamos una subconsulta que utiliza GROUP BY correctamente para devolver el number_of_uses valor para cada item_id . Esta subconsulta es breve y dulce, porque solo necesita mirar la tabla de uses .

                              SELECT item_id, COUNT(*) number_of_uses
                                FROM  uses 
                               GROUP BY item_id

Luego, podemos unir esa subconsulta con la tabla de item .

 SELECT item.*, usecount.number_of_uses
   FROM item
   JOIN (
                              SELECT item_id, COUNT(*) number_of_uses
                                FROM  uses 
                               GROUP BY item_id
        ) usecount ON item.item_id = usecount.item_id

Esto permite que la cláusula GROUP BY sea ​​simple y correcta, y también nos permite usar el especificador * .

Nota: sin embargo, los desarrolladores inteligentes evitan el uso del especificador * en cualquier caso. Por lo general, es mejor enumerar las columnas que desea en una consulta.

ALGÚN VALOR()

 SELECT item.item_id, ANY_VALUE(uses.tag) tag,   
        COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

muestra las filas en una tabla llamada item , el recuento de filas relacionadas y uno de los valores en la tabla relacionada llamados uses .

Puede pensar en esta función ANY_VALUE() como un tipo extraño de función agregada. En lugar de devolver un conteo, suma o máximo, le indica al servidor MySQL que elija, de forma arbitraria, un valor del grupo en cuestión. Es una forma de evitar el error 1055.

Tenga cuidado al usar ANY_VALUE() en consultas en aplicaciones de producción.

Realmente debería llamarse SURPRISE_ME() . Devuelve el valor de alguna fila en el grupo GRUPO POR. La fila que devuelve es indeterminada. Eso significa que depende completamente del servidor MySQL. Formalmente, devuelve un valor impredecible.

El servidor no elige un valor aleatorio, es peor que eso. Devuelve el mismo valor cada vez que ejecuta la consulta, hasta que no lo hace. Puede cambiar, o no, cuando una tabla crece o se encoge, o cuando el servidor tiene más o menos RAM, o cuando la versión del servidor cambia, o cuando Marte está en retroceso (lo que sea que eso signifique), o sin ninguna razón.

Usted ha sido advertido.