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
