Я перепрошу этот question в упрощенном и расширенном виде.
Рассмотрим эти операторы 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);
Используя sqlite, оператор select
возвращает:
id avg1
---------- ----------
106 4.5
107 4.0
и mysql возвращает:
+------+--------+
| id | avg1 |
+------+--------+
| 106 | 4.5000 |
+------+--------+
Насколько я могу судить, результаты mysql верны, а sqlite неверны. Я попытался использовать real
с sqlite как в следующем, но он возвращает две записи:
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);
Почему sqlite возвращает две записи?
Быстрое обновление:
Я запустил инструкцию против последней версии sqlite (3.7.11) и все еще получаю две записи.
Другое обновление:
Я отправил электронное письмо на адрес [email protected]
Я сам, я играл с VDBE и нашел что-то интересное. Я разбил след выполнения каждого цикла not exists
(по одному для каждой группы avg).
Чтобы иметь три группы avg, я использовал следующие утверждения:
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);
Мы ясно видим, что каким-то образом r:4.5
стало i:5
:
Теперь я пытаюсь понять, почему.
Окончательное редактирование:
Итак, я играл достаточно с исходным кодом sqlite. Я понимаю, что зверь намного лучше теперь, хотя я дам оригинальный разработчик разобраться, как он, кажется, уже делает это:
http://www.sqlite.org/src/info/430bb59d79
Интересно, что, по крайней мере, мне кажется, что более новые версии (несколько раз после версии, которые я использую) поддерживают вставку нескольких записей, используемых в тестовом примере, добавленном в вышеупомянутый commit:
CREATE TABLE t34(x,y);
INSERT INTO t34 VALUES(106,4), (107,3), (106,5), (107,5);