On 21 Jul 2013, at 4:41pm, E.Pasma <pasm...@concepts.nl> wrote: > Is a change in SQLite imaginable such that column expressions are not > re-evaluated with each reference to the column alias? > This could also improve queries that use aliases only in the order by clause, > like > select id, (subquery) as c from categories order by c;
One of the problems with this is that it's not standard SQL. You're not meant to be able to refer to column aliases inside the SELECT that defines them. For instance SELECT yearJoined AS y, ageWhenJoined AS a, (y-a) AS yob FROM members is not allowed in the SQL standard. This is partly because the order of evaluation of terms in a SELECT is not defined: SQL permits those three values to be evaluated in any order. Now taking a look at your query select id, (subquery) as c from categories order by c SQL can choose to evaluate the ORDER BY clause first, and only then to evaluate the expressions "id" and "c". But that leaves it ordering by a "c" value which it hasn't evaluated yet. Your suggestion introduces a new requirement on how SQL works that it must figure out the "AS" expressions first. Which might do nicely for your example but it would cause delays in other commands. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
