Por que os resultados do SELECT diferem entre o mysql e o sqlite?
Estou pedindo novamentequestão de forma simplificada e expandida.
Considere estas instruções 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 o sqlite, oselect
declaração retorna:
id avg1
---------- ----------
106 4.5
107 4.0
e o mysql retorna:
+------+--------+
| id | avg1 |
+------+--------+
| 106 | 4.5000 |
+------+--------+
Tanto quanto eu posso dizer, os resultados do mysql estão corretos e os do sqlite estão incorretos. Tentei conjurarreal
com sqlite como no seguinte, mas ainda retorna dois registros:
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 que o sqlite retorna dois registros?
Rápida atualização:
Eu corri a declaração contra a versão mais recente do sqlite (3.7.11) e ainda obter dois registros.
Outra atualização:
Enviei um email para [email protected] sobre o assunto.
Eu, eu venho jogando com o VDBE e encontrei algo interessante. Eu divido o traço de execução de cada loop denot exists
(um para cada grupo médio).
Para ter três grupos avg, usei as seguintes instruções:
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);
Nós vemos claramente que de alguma forma o que deveria serr:4.5
se tornoui:5
:
Agora estou tentando ver por que isso acontece.
Edição final:
Então, eu tenho jogado o suficiente com o código-fonte do sqlite. Eu entendo a besta muito melhor agora, embora eu deixe odesenvolvedor original resolva como ele parece já estar fazendo isso:
http://www.sqlite.org/src/info/430bb59d79
Curiosamente, para mim, pelo menos, parece que as versões mais recentes (algumas vezes após a versão que estou usando) suportam a inserção de vários registros, conforme usado em um caso de teste adicionado no commit acima mencionado:
CREATE TABLE t34(x,y);
INSERT INTO t34 VALUES(106,4), (107,3), (106,5), (107,5);