Why does SELECT results differ between mysql and sqlite?

Question

I'm re-asking this question in a simplified and expanded manner. Consider these sql statements: ``` 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); ``` Using sqlite, the `select` statement returns: ``` id avg1 ---------- ---------- 106 4.5 107 4.0 ``` and mysql returns: ``` +------+--------+ | id | avg1 | +------+--------+ | 106 | 4.5000 | +------+--------+ ``` As far as I can tell, mysql's results are correct, and sqlite's are incorrect. I tried to cast to `real` with sqlite as in the following but it returns two records still: ``` 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…

Accepted Answer

I tried to mess with some variants of query.

It seems, like sqlite has errors in using of previous declared fields in a nested HAVING expressions.

In your example `avg1` under second having is always equal to 5.0

Look:

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

This one returns nothing, but execution of the following query returns both records:

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

I can not find any similar bug at sqlite tickets list.

Why does SELECT results differ between mysql and sqlite?

Solution

Problem

Related problems