On Fri, May 4, 2012 at 12:44 PM, Eric Sink <e...@sourcegear.com> wrote:
> > Is this new syntax likely to perform any better than the traditional way > of writing the query? > Dunno. Depends on which "traditional way" you are talking about, I suppose. Here's how it works: SQLite internally maintains a set of "registers" (objects in which it can store any valid value) for the result set. As it is evaluating each row of the inputs to an aggregate query, if there is a min() or max() agg function that hits a new minimum or maximum value, then the other values of the current input row are copied into the output registers. If the min() or max() did not reach a new low or high, then the values are not copied. So, after all input rows have been examined, the output registers will contain values that correspond to the last min() or max() row. The above should make it clear what happens if you have multiple min() and/or max() functions in the same aggregate query. The one that reached its extreme last is the one whose row values will appear in the output set. Of course, the order of the input rows is usually undefined, so there are no guarantees about which row that will actually be. You know that whatever values appear in the final output will correspond to the extreme of at least one of the various min()/max() functions - you just don't know which one. BTW, this ability for min()/max() to trigger the copying of input values into the output register - it is not a capability that is exposed to application-defined functions. So you cannot add new functions that work exactly like the built-in min() and max(). You can override the built-in min() and max() aggregates, but if you do, you lose the magical processing that makes output values all come from the row that contained the min or max value. > > -- > E > > > On May 4, 2012, at 11:42 AM, Nico Williams <n...@cryptonector.com> wrote: > > > On Fri, May 4, 2012 at 9:20 AM, Richard Hipp <d...@sqlite.org> wrote: > >>> Queries of the form: "SELECT max(x), y FROM table" returns the > >>> value of y on the same row that contains the maximum x value. > >>> > >>> Is that standard SQL behavior? I'd have expected that to return one > row > >>> for every row in the table. To get the behavior described above, I'd > use > >>> "SELECT x, y FROM table WHERE x = (SELECT max(x) FROM table)". > >> > >> It is definitely NOT standard behavior. The standard behavior is > >> undefined. Or (with many SQL engines) it will throw an error if you > have a > >> term in the result set that is not part of an aggregate function or an > >> element of the GROUP BY clause. But lots of newbies expect SQL to work > as > >> described in the 3.7.11 release comments, and we used to get support > >> questions because it did not. And so rather than continue to answer the > >> questions over and over, I figured it would be easier to tweak SQLite to > >> reliably do what newbies expect. I never anticipated that this change > >> would be so controversial or confusing. > > > > This is very clever. I'm not sure that an informative error message > > wouldn't have been better, but I think you made the right choice given > > SQLite3's previous behavior. A pragma by which to cause SQLite3 to > > return an error instead might be useful, but then, it's SQL_Lite_. > > > > Is there any way to define aggregate functions that pick a row for > > providing column values in non-aggregate expressions? E.g., you could > > have a median() or mode(), no? I don't think this is important, am > > just curious. > > > > Nico > > -- > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
