This is just the way the SQL standard mandates it to work, not an oddity specific to SQLite. I imagine the optimizer is probably smart enough to not do the work twice here, but someone else will need to chime in to confirm that.
If you want to be sure, you can use a CTE. On Thu, Sep 25, 2014 at 2:02 PM, Mark Lawrence <[email protected]> wrote: > On Thu Sep 25, 2014 at 01:43:20PM -0500, Cory Nelson wrote: > > > GROUP BY works on your input data, not output data. You want to GROUP > > BY COALESCE(x.id, y.id) > > That describes the behaviour I demonstrated, but not the reasoning > behind it nor the documentation pointing to that reasoning. > > Is SQLite clever enough to recognize that a GROUP BY expression and a > SELECT column are the same? Because in my mind I think of the query as > working in the following stages for the most efficient operation: > > - JOIN ROWS > - SELECT COLUMNS -- COALESCE done here > - GROUP OUTPUT > - ORDER OUTPUT > > However, it appears to be the case that the order is more like this: > > - JOIN ROWS > - GROUP ROWS -- COALESCE done here > - SELECT COLUMNS -- COALESCE also done here? > - ORDER OUTPUT > > Which looks to me like the expression would be calculated twice. Is > SQLite smart enough to figure out that the columns are the same and > only do it once? > > If SQLite is capable of determining that the same expression is used > twice, why not just accept a SELECT expression? > > -- > Mark Lawrence > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Cory Nelson http://int64.org _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
