Почему результаты SELECT отличаются между mysql и sqlite?

Я перепрошу этот 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);

Ответы

Ответ 1

Я пытался использовать некоторые варианты запроса.

Кажется, что sqlite имеет ошибки в использовании предыдущих объявленных полей во вложенных выражениях HAVING.

В вашем примере avg1 при втором есть всегда 5,0

Облик:

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

Это ничего не возвращает, но выполнение следующего запроса возвращает обе записи:

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

Я не могу найти подобную ошибку в списке билетов sqlite.

Ответ 2

Давайте рассмотрим два способа: я буду использовать postgres 9.0 в качестве моей базы данных ссылок

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

затем переместите некоторую логику внутри подзапроса, избавившись от "нет": (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)

поэтому вы должны спросить себя: что вы на самом деле имеете в виду, когда вы выполняете этот коррелированный подзапрос внутри предложения, если он оценивает каждую строку по каждой строке из основного запроса, мы делаем декартовое соединение, и я не знаю, Думаю, мы должны указывать пальцами на движок SQL.

если вы хотите, чтобы каждая строка была меньше максимального значения. Что вы должны сказать:

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

Ответ 3

Вы пробовали эту версию?

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

Также этот (который должен давать одинаковые результаты):

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

Запрос можно также обрабатывать с помощью производных таблиц вместо подзапроса в разделе 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