Dan, Richard, Igor, thanks for your input, and yes, it seems as if the gamble is no longer safe. Hopefully I'm the only one that has run into this side effect ;)
:-David On 01/17/2011 04:57 PM, Igor Tandetnik wrote: > David Burström<david.burst...@gmail.com> wrote: >> SELECT starttime, endtime from entry LEFT JOIN interval ON >> interval.entryid = entry.id GROUP BY entry.id HAVING starttime = >> MAX(starttime); > The behavior of this statement is unspecified. In standard SQL, it is > syntactically invalid - in a GROUP BY statement, all columns that appear in > SELECT or HAVING clauses must be either mentioned in GROUP BY, or appear only > in expressions that are arguments of aggregate functions. > > As an extension, SQLite allows ungrouped columns in these contexts. The value > of such an expression is the column value taken from some random, unspecified > row belonging to the group. > > Thus, your condition of "HAVING starttime = MAX(starttime)" is a gamble. You > are saying, pick some random row from the group, and if that row just happens > to contain the largest value across the group, then include the group in the > resultset, otherwise throw it away. With 3.6.22, you got lucky and won the > gamble. With 3.7.2, the implementation details have changed so that a > different row is now being picked, and you are losing the gamble (and blaming > the roulette wheel). _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
