Edit Question - KnowledgeHub

Title Body

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 T2 group by T2.id having avg2 > avg1); Why does sqlite return two records? Quick update : I ran the statement against the latest sqlite version (3.7.11) and still get two records. Another update : I sent an email to sqlite-users@sqlite.org about the issue. Myself, I've been playing with VDBE and found something interesting. I split the execution trace of each loop of not exists (one for each avg group). To have three avg groups, I used the following statements: create table foo (id VARCHAR(1), score INT); insert into foo values ('c', 1.5)

Tags (comma-separated) Cancel