On 11/19/16, Andy Goth <andrew.m.g...@gmail.com> wrote: > Is this extension documented? What is the precise behavior? Are there > guarantees that it will be preserved throughout future versions of SQLite? > When was it introduced?
I thought I had documented this someplace, but now I cannot find where. In any event, it is tested and verified behavior (see, for example, https://www.sqlite.org/src/artifact/936941484ebdceb8) and so it is guaranteed to be preserved in future versions of SQLite. The behavior only works for the built-in aggregate min() and max() functions. If an application defines it own override min() or max() it will not work for them. In an aggregate query, terms of the result set that are not enclosed in an aggregate function and that are not part o the GROUP BY clause take on the values that are computed for that term for unaggregated rows were either the min() or max() function sees a new minimum or a new maximum. If you only have a single min() or max() aggregate, that means all unenclosed terms end up with the value that was seen for the min() or max() row. If there are multiple min() or max() aggregate functions, then the value of unenclosed columns will be for a row where on of the min() or max() functions was satisfied, but you do not know which row. Also, if there are two rows that have the same max() or min() value, then you do not know which of those rows will be used for the unenclosed columns. It used to be that we would get multiple "bug reports" per year on the SQLite mailing list to the effect that the following query did not work: SELECT x, max(y) FROM table; The newbies would complain that the value returned for "x" was not on the same row as the maximum "y". Then we would have to explain why their query was wrong. After this went on for a while, I decided it would be easier just to enhance SQLite to do what the newbies expected. And later still, I discovered that this behavior is, in fact, quite convenient, as evidenced by its use in the /brlist page. I think it is also used elsewhere in Fossil, though I cannot say offhand where those other queries are. > > Repeating myself so we don't lose focus: I was originally looking at the > inconsistent definition of open/closed branches. Given what you say about > GROUP BY, the "new" definition of a closed branch is a branch whose most > recent leaf is referenced by a closed tag, whereas the classical definition > (used by the branch ls command and the original /brlist page) is a branch > whose leaves are all referenced by closed tags. In both cases, all branches > having the same name are considered to be a single branch. > > Going forward, which definition do we want? I think the definition used by /brlist. Reasoning: (1) I don't think the difference is all that important. If you have multiple branches with the same name, then you probably deserve whatever it is that happens. (2) The /brlist definition is easier to compute. > > On Nov 19, 2016 06:32, "Richard Hipp" <d...@sqlite.org> wrote: > >> On 11/19/16, Andy Goth <andrew.m.g...@gmail.com> wrote: >> > >> > If I understand correctly, the baseline branch ls query defines a >> > closed >> > branch as having no leaves not referenced by closed tags. The baseline >> > "new" /brlist query instead checks whether "the" check-in is referenced >> > by a closed tag. Which check-in? Hard to say. GROUP BY doesn't >> > define >> > which row is used to evaluate the result expressions. >> >> True enough for standard SQL. But SQLite is extended so that the >> result set evaluation is for the row where max(event.mtime) is >> maximized. So, it is looking at the most recent leaf of the branch. >> >> -- >> D. Richard Hipp >> d...@sqlite.org >> _______________________________________________ >> fossil-dev mailing list >> fossil-dev@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/fossil-dev >> > -- D. Richard Hipp d...@sqlite.org _______________________________________________ fossil-dev mailing list fossil-dev@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/fossil-dev
