Comportamiento de MySQL GROUP BY

Dada la siguiente tabla ' foo '

 ID | First Name | Last Name ---------------------------- 67 John Smith ---------------------------- 67 Bill Jacobs 

¿Qué first_name y last_name la siguiente consulta y por qué?

 SELECT * FROM foo WHERE ID = 67 GROUP BY ID 

MySQL elige una fila arbitrariamente. En la práctica, los motores de almacenamiento MySQL de uso común devuelven los valores de la primera fila del grupo, con respecto al almacenamiento físico.

 create table foo (id serial primary key, category varchar(10)); insert into foo (category) values ('foo'), ('foo'), ('foo'), ('bar'), ('bar'), ('bar'); select * from foo group by category; +----+----------+ | id | category | +----+----------+ | 4 | bar | | 1 | foo | +----+----------+ 

Otros opinan que MySQL le permite ejecutar esta consulta a pesar de que tiene resultados arbitrarios y potencialmente engañosos. El estándar SQL, y la mayoría de los demás proveedores RDBMS, no permiten este tipo de consulta GROUP BY ambigua. Esto se denomina Regla de valor único : todas las columnas en la list de selección deben ser parte explícita de los criterios GROUP BY, o bien dentro de una function agregada, por ejemplo, COUNT() , MAX() , etc.

MySQL admite un modo SQL ONLY_FULL_GROUP_BY que hace que MySQL devuelva un error si intenta ejecutar una consulta que infringe la semántica estándar de SQL.

AFAIK, SQLite es el único RDBMS que permite columnas ambiguas en una consulta agrupada. SQLite devuelve valores de la última fila del grupo:

 select * from foo group by category; 6|bar 3|foo 

Podemos imaginar consultas que no serían ambiguas, pero que aún violan la semántica estándar SQL.

 SELECT foo.*, parent_of_foo.* FROM foo JOIN parent_of_foo ON (foo.parent_id = parent_of_foo.parent_id) GROUP BY foo_id; 

No hay una manera lógica de que esto pueda producir resultados ambiguos. Cada fila en foo obtiene su propio grupo, si agrupamos por la key primaria de foo. Entonces cualquier columna de foo puede tener solo un valor en el grupo. Incluso join a otra tabla a la que hace reference una key foránea en foo puede tener solo un valor por grupo, si los grupos están definidos por la key primaria de foo.

MySQL y SQLite confían en que diseñe consultas lógicamente no ambiguas. Formalmente, cada columna en la list de selección debe ser una dependencia funcional de las columnas en los criterios GROUP BY. Si no se adhiere a esto, es su culpa. 🙂

El SQL estándar es más estricto y no permite algunas consultas que podrían ser inequívocas, probablemente porque sería demasiado complejo para que el RDBMS sea seguro en general.

El grupo de MySQL por no es consistente con el comportamiento estándar de SQL, MySQL hace que sea fácil get otras columnas PERO, al mismo time, nunca puedes estar seguro de cuál obtendrás.

Actualización: consulte esta página: http://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html

Al usar esta function, todas las filas en cada grupo deben tener los mismos valores para las columnas que se omiten de la parte GROUP BY. El server puede devolver cualquier valor del grupo, por lo que los resultados son indeterminados a less que todos los valores sean los mismos.

No está definido, lo que resulta que va a get.

Siempre me pregunté por qué este comportamiento estaba permitido. Realmente, desearía que ese código genere un error (preferiblemente, uno descifrable, ninguno de los usuales de MySQL "su statement tiene un problema, pero no sé dónde").

Es muy probable que se elija el primer nombre y apellido de la última (última) fila.

Puede agregar una cláusula ORDER BY para dar pistas sobre cómo desea orderar las filas agrupadas.

En SQL estándar, este SQL debería fallar, con un error del procesador del server algo así como

"firstname, and lastname no pueden includese en la cláusula select a less que también estén en el grupo By, o sean parte de una function agregada".

¿MySql realmente devuelve datos para esto?

El grupo de MySQL por no es consistente con el comportamiento estándar de SQL, MySQL hace que sea fácil get otras columnas PERO, al mismo time, nunca puedes estar seguro de cuál obtendrás.

Cierto. En realidad, corresponde más al modo SELECT DISTINCT ON en postgres, por ejemplo, excepto que esto le permite especificar el order de las filas antes de la distinción (?) Y, por lo tanto, qué fila obtendrá (es decir, más reciente, más antigua, lo que sea).

Nota MySQL en el modo "compatible con sql" rechazará GROUP BY con columnas no especificadas como en su ejemplo.