¿Por qué los resultados SELECT difieren entre mysql y sqlite?

Estoy volviendo a hacer esta pregunta de una manera simplificada y expandida.

Considere estas declaraciones sql:

create table foo (id INT, score INT); insert into foo values (106, 4); insert into foo values (107, 3); insert into foo values (106, 5); insert into foo values (107, 5); select T1.id, avg(T1.score) avg1 from foo T1 group by T1.id having not exists ( select T2.id, avg(T2.score) avg2 from foo T2 group by T2.id having avg2 > avg1); 

Usando sqlite, la instrucción select devuelve:

 id avg1 ---------- ---------- 106 4.5 107 4.0 

y mysql regresa:

 +------+--------+ | id | avg1 | +------+--------+ | 106 | 4.5000 | +------+--------+ 

Por lo que puedo decir, los resultados de mysql son correctos y los de sqlite son incorrectos. Traté de convertir a real con sqlite como en el siguiente pero aún devuelve dos loggings:

 select T1.id, cast(avg(cast(T1.score as real)) as real) avg1 from foo T1 group by T1.id having not exists ( select T2.id, cast(avg(cast(T2.score as real)) as real) avg2 from foo T2 group by T2.id having avg2 > avg1); 

¿Por qué sqlite devuelve dos loggings?

Actualización rápida :

Ejecuté la statement contra la última versión de sqlite (3.7.11) y aún obtuve dos loggings.

Otra actualización :

Envié un correo electrónico a sqlite-users@sqlite.org sobre el problema.

Yo mismo, he estado jugando con VDBE y encontré algo interesante. Divido el seguimiento de ejecución de cada bucle de not exists (uno para cada grupo de avg).

Para tener tres grupos de media, utilicé las siguientes afirmaciones:

 create table foo (id VARCHAR(1), score INT); insert into foo values ('c', 1.5); insert into foo values ('b', 5.0); insert into foo values ('a', 4.0); insert into foo values ('a', 5.0); PRAGMA vdbe_listing = 1; PRAGMA vdbe_trace=ON; select avg(score) avg1 from foo group by id having not exists ( select avg(T2.score) avg2 from foo T2 group by T2.id having avg2 > avg1); 

Vemos claramente que de alguna manera lo que debería ser r:4.5 ha convertido en i:5 :

enter image description here

Ahora estoy tratando de ver por qué es eso.

Edición final :

Así que he estado jugando lo suficiente con el código fuente de sqlite. Ahora entiendo mucho mejor a la bestia, aunque dejaré que el desarrollador original la resuelva ya que parece que ya lo está haciendo:

http://www.sqlite.org/src/info/430bb59d79

Curiosamente, para mí al less, parece que las versiones más nuevas (algunas veces después de la versión que estoy usando) admiten insert múltiples loggings como se usa en un caso de testing agregado en la confirmación mencionada anteriormente:

 CREATE TABLE t34(x,y); INSERT INTO t34 VALUES(106,4), (107,3), (106,5), (107,5); 

Traté de meterme con algunas variantes de consulta.

Parece que sqlite tiene errores en el uso de campos declarados previamente en expresiones HAVING anidadas.

En su ejemplo avg1 en segundo lugar siempre es igual a 5.0

Mira:

 select T1.id, avg(T1.score) avg1 from foo T1 group by T1.id having not exists ( SELECT 1 AS col1 GROUP BY col1 HAVING avg1 = 5.0); 

Éste no devuelve nada, pero la ejecución de la siguiente consulta devuelve ambos loggings:

 ... having not exists ( SELECT 1 AS col1 GROUP BY col1 HAVING avg1 <> 5.0); 

No puedo encontrar ningún error similar en la list de boletos sqlite .

Veamos esto de dos maneras, usaré postgres 9.0 como mi database de reference

(1)

 -- select rows from foo select T1.id, avg(T1.score) avg1 from foo T1 group by T1.id -- where we don't have any rows from T2 having not exists ( -- select rows from foo select T2.id, avg(T2.score) avg2 from foo T2 group by T2.id -- where the average score for any row is greater than the average for -- any row in T1 having avg2 > avg1); id | avg1 -----+-------------------- 106 | 4.5000000000000000 (1 row) 

luego vamos a mover parte de la lógica dentro de la subconsulta, eliminando el 'no': (2)

 -- select rows from foo select T1.id, avg(T1.score) avg1 from foo T1 group by T1.id -- where we do have rows from T2 having exists ( -- select rows from foo select T2.id, avg(T2.score) avg2 from foo T2 group by T2.id -- where the average score is less than or equal than the average for any row in T1 having avg2 <= avg1); -- I think this expression will be true for all rows as we are in effect doing a --cartesian join -- with the 'having' only we don't display the cartesian row set id | avg1 -----+-------------------- 106 | 4.5000000000000000 107 | 4.0000000000000000 (2 rows) 

así que tienes que preguntarte a ti mismo: ¿qué quieres decir cuando realizas esta subconsulta correlacionada dentro de una cláusula having, si evalúa cada fila contra cada fila de la consulta principal estamos haciendo una unión cartesiana y no creo que debería señalar con el dedo al motor SQL.

si quiere que cada fila sea inferior al promedio máximo, lo que debería decir es:

 select T1.id, avg(T1.score) avg1 from foo T1 group by T1.id having avg1 not in (select max(avg1) from (select id,avg(score) avg1 from foo group by id)) 

¿Has probado esta versión? :

 select T1.id, avg(T1.score) avg1 from foo T1 group by T1.id having not exists ( select T2.id, avg(T2.score) avg2 from foo T2 group by T2.id having avg(T2.score) > avg(T1.score)); 

También este (que debería estar dando los mismos resultados):

 select T1.* from ( select id, avg(score) avg1 from foo group by id ) T1 where not exists ( select T2.id, avg(T2.score) avg2 from foo T2 group by T2.id having avg(T2.score) > avg1); 

La consulta también se puede manejar con tablas derivadas, en lugar de subconsultar en la cláusula HAVING :

 select ta.id, ta.avg1 from ( select id, avg(score) avg1 from foo group by id ) ta JOIN ( select avg(score) avg1 from foo group by id order by avg1 DESC LIMIT 1 ) tmp ON tmp.avg1 = ta.avg1