MySQL
Error 1055: ONLY_FULL_GROUP_BY: algo no está en la cláusula GROUP BY ...
Buscar..
Introducción
GROUP BY , o al menos para dificultar que los desarrolladores de escritura de consultas se quemen con ella.
Observaciones
Durante mucho tiempo, MySQL ha contenido una extensión notoriamente no estándar de GROUP BY , que permite un comportamiento extraño en nombre de la eficiencia. Esta extensión ha permitido que innumerables desarrolladores de todo el mundo usen GROUP BY en el código de producción sin entender completamente lo que estaban haciendo.
En particular, es una mala idea usar SELECT * en una consulta GROUP BY , porque una cláusula GROUP BY estándar requiere enumerar las columnas. Muchos desarrolladores, lamentablemente, lo han hecho.
Lee esto. https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
El equipo de MySQL ha estado tratando de solucionar este error sin arruinar el código de producción. sql_mode un indicador sql_mode en 5.7.5 llamado ONLY_FULL_GROUP_BY para obligar al comportamiento estándar. En una versión reciente, activaron esa bandera de forma predeterminada. Cuando actualizó su MySQL local a 5.7.14, la bandera se encendió y su código de producción, dependiendo de la extensión anterior, dejó de funcionar.
Si recientemente comenzó a recibir 1055 errores, ¿cuáles son sus opciones?
- arregle las consultas SQL ofensivas, o haga que sus autores hagan eso.
- vuelva a una versión de MySQL lista para usar con el software de la aplicación que utiliza.
- cambie el
sql_modesu servidor para deshacerse del modoONLY_FULL_GROUP_BYrecién configurado.
Puedes cambiar el modo haciendo un comando SET .
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'
debe hacer el truco si lo hace justo después de que su aplicación se conecte a MySQL.
O bien, puede encontrar el archivo init en su instalación de MySQL , ubicar la línea sql_mode= y cambiarlo para omitir ONLY_FULL_GROUP_BY , y reiniciar su servidor.
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
- mencionado en la cláusula
GROUP BY, o - 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.
