Pytam o to ponowniepytanie w sposób uproszczony i rozszerzony.

Rozważ następujące instrukcje 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);

Używając sqlite,select oświadczenie zwraca:

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

i mysql zwraca:

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

O ile wiem, wyniki mysql są poprawne, a sqlite są niepoprawne. Próbowałem rzucićreal z sqlite jak poniżej, ale nadal zwraca dwa rekordy:

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);

Dlaczego sqlite zwraca dwa rekordy?

Szybka aktualizacja:

Uruchomiłem instrukcję przeciwko najnowszej wersji sqlite (3.7.11) i nadal otrzymuję dwa rekordy.

Kolejna aktualizacja:

Wysłałem wiadomość e-mail na adres [email protected] w tej sprawie.

Sam grałem z VDBE i znalazłem coś interesującego. Podzielę ślad wykonania każdej pętlinot exists (po jednym dla każdej średniej grupy).

Aby mieć trzy średnie grupy, użyłem następujących stwierdzeń:

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);

Wyraźnie widzimy, jak powinno byćr:4.5 stał sięi:5:

Próbuję teraz zrozumieć, dlaczego tak jest.

Ostatnia edycja:

Więc grałem wystarczająco z kodem źródłowym sqlite. Teraz rozumiem bestię znacznie lepiej, chociaż pozwolęoryginalny programista uporządkuj to, co wydaje się już robić:

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

Co ciekawe, przynajmniej dla mnie wydaje się, że nowsze wersje (czasami po wersji, której używam) obsługują wstawianie wielu rekordów, tak jak w przypadku testowym dodanym w wyżej wymienionym zatwierdzeniu:

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