> <quote> > > If a HAVING clause is specified, it is evaluated once for each group of > rows as a boolean expression <http://sqlite.org/lang_expr.html#booleanexpr>. > If the result of evaluating the HAVING clause is false, the group is > discarded. If the HAVING clause is an aggregate expression, it is evaluated > across all rows in the group. If a HAVING clause is a non-aggregate > expression, it is evaluated with respect to an arbitrarily selected row > from the group. The HAVING expression may refer to values, even aggregate > functions, that are not in the result. > > </quote> > > So, it turns out that SQLite is documented as accepting the statement. So > this is not a "bug", per se. But basing a resultant row on "evaluated with > respect an arbitrarily selected row from the group" seems "un-useful".
Thanks John. That matches the behaviour I observed. I always liked about SQL (at least how it is implemented in MS SQL Server) that it forces you to be non-ambiguous, so the result is deterministic. In this case this led to a quite subtle bug in my program because the result was right (as in what I expected) most of the time, since it picked the right row, but not always. Jann Winton Capital Management Limited (?Winton?) is a limited company registered in England and Wales with its registered offices at 16 Old Bailey, London, EC4M 7EG (Registered Company No. 3311531). Winton is authorised and regulated by the Financial Conduct Authority in the United Kingdom, registered as an investment adviser with the US Securities and Exchange Commission, registered with the US Commodity Futures Trading Commission and a member of the National Futures Association in the United States. This communication, including any attachments, is confidential and may be privileged. This email is for use by the intended recipient only. If you receive it in error, please notify the sender and delete it. You should not copy or disclose all or any part of this email. This email does not constitute an offer or solicitation and nothing contained in this email constitutes, and should not be construed as, investment advice. Prospective investors should request offering materials and consult their own advisers with respect to investment decisions and inform themselves as to applicable legal requirements, exchange control regulations and taxes in the countries of their citizenship, residence or domicile. Past performance is not indicative of future results. Winton takes reasonable steps to ensure the accuracy and integrity of its communications, including emails. However Winton accepts no liability for any materials transmitted. Emails are not secure and cannot be guaranteed to be error free. Winton handles personal information in accordance with its privacy notice.<https://www.wintoncapital.com/en/regulatory-disclosures#privacypolicy>