Nathan Kurz <[EMAIL PROTECTED]> wrote:
I was just about to file a bug about a segfault when I have an
aggregate in the where clause like:
select id from test where avg(rating) > 10 group by id;
But when I looked in the bug reports, I found that several similar
bugs had been reported and that the problem had been 'fixed':
http://www.sqlite.org/cvstrac/chngview?cn=2769
Unfortunately, the fix just makes it illegal to do. Is this the
permanent state, or just a placeholder until a further fix is made?
It is indeed an illegal SQL query. You probably want
select id from test
group by id
having avg(rating) > 10;
The workaround of putting the aggregate in a subselect works fine:
select id from
(select id, avg(rating) as average from test where average > 10);
Does this actually work? What does avg do when there's no "group by"
clause? If it works, I somehow think it's not doing what you think it's
doing. The subquery probably averages over the whole table and produces
a single row with this average and an id that it just happened to
encounter first.
Igor Tandetnik
