Why Does Select Results Differ Between Mysql And Sqlite?

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

Solution 1:

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
groupby T1.id
havingnotexists (
    SELECT1AS col1 GROUPBY col1 HAVING avg1 =5.0);

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

...
havingnotexists (
    SELECT1AS col1 GROUPBY col1 HAVING avg1 <>5.0);

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

Solution 2:

Lets look at this two ways, i'll use postgres 9.0 as my reference database

(1)

-- select rows from foo select T1.id, avg(T1.score) avg1
from foo T1
groupby T1.id
-- where we don't have any rows from T2havingnotexists (
-- select rows from fooselect T2.id, avg(T2.score) avg2
from foo T2
groupby T2.id
-- where the average score for any row is greater than the average for -- any row in T1having avg2 > avg1);

 id  |        avg1        
-----+--------------------106|4.5000000000000000
(1row)

then let's move some of the logic inside the subquery, getting rid of the 'not' : (2)

-- select rows from foo select T1.id, avg(T1.score) avg1
from foo T1
groupby T1.id
-- where we do have rows from T2havingexists (
-- select rows from fooselect T2.id, avg(T2.score) avg2
from foo T2
groupby T2.id
-- where the average score is less than or equal than the average for any row in T1having 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.5000000000000000107|4.0000000000000000
(2rows)

so you have got to ask yourself -- what do you actually mean when you do this correlated subquery inside a having clause, if it evaluates every row against every row from the primary query we are making a cartesian join and I don't think we should be pointing fingers at the SQL engine.

if you want every row that is less than the maximum average What you should be saying is:

select T1.id, avg(T1.score) avg1 
from foo T1 groupby T1.id
having avg1 notin 
(selectmax(avg1) from (select id,avg(score) avg1 from foo groupby id)) 

Solution 3:

Have you tried this version? :

select T1.id, avg(T1.score) avg1
from foo T1
groupby T1.id
havingnotexists (
    select T2.id, avg(T2.score) avg2
    from foo T2
    groupby T2.id
    havingavg(T2.score) >avg(T1.score));

---

Also this one (which should be giving same results):

select T1.*from
  ( select id, avg(score) avg1
    from foo 
    groupby id
  ) T1
wherenotexists (
    select T2.id, avg(T2.score) avg2
    from foo T2
    groupby T2.id
    havingavg(T2.score) > avg1);

---

The query can also be handled with derived tables, instead of subquery in HAVING clause:

select ta.id, ta.avg1
from 
  ( select id, avg(score) avg1
    from foo
    groupby id
  ) ta
  JOIN
  ( select avg(score) avg1
    from foo 
    groupby id
    orderby avg1 DESC
    LIMIT 1
  ) tmp
  ON tmp.avg1 = ta.avg1