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

rowcount php

Question

Estoy volviendo a hacer esta question de una manera simplificada y ampliada.

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 devuelve:

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

Por lo que puedo decir, los resultados de mysql son correctos y los de sqlite son incorrectos. Intenté convertir en real con sqlite como se muestra a continuación, pero devuelve dos registros aún:

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 registros?

Actualización rápida :

Ejecuté la declaración contra la última versión de sqlite (3.7.11) y aún obtengo dos registros.

Otra actualización :

Envié un correo electrónico a [email protected] sobre el problema.

Yo mismo, he estado jugando con VDBE y encontré algo interesante. Dividí la traza de ejecución de cada bucle que not exists (una para cada grupo promedio).

Para tener tres grupos promedio, usé 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 :

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 a la bestia mucho mejor, aunque dejaré que el desarrollador original lo resuelva ya que parece que ya lo está haciendo:

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

Curiosamente, al menos para mí, parece que las versiones más nuevas (algunas veces después de la versión que estoy usando) admiten la inserción de múltiples registros como se usa en un caso de prueba agregado en el compromiso mencionado anteriormente:

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

Answer 1

¿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 subconsultas 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 

Answer 2

Intenté meterme con algunas variantes de consulta.

Parece que, como sqlite tiene errores en el uso de campos declarados anteriores en expresiones HAVING anidadas.

En tu ejemplo, avg1 en segundo lugar es siempre 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);

Este no devuelve nada, pero la ejecución de la siguiente consulta devuelve ambos registros:

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

No puedo encontrar ningún error similar en la lista de tickets de sqlite .

Answer 3

Veamos estas dos formas, usaré postgres 9.0 como mi base de datos de referencia

(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 movamos parte de la lógica dentro de la subconsulta, deshaciéndonos del ''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 debes preguntarte: ¿qué quieres decir realmente cuando haces esta subconsulta correlacionada dentro de una cláusula de haber, si evalúa cada fila contra cada fila de la consulta principal, estamos haciendo una unión cartesiana y no creo que Debería estar apuntando los dedos al motor SQL.

si desea 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))