On 2018/10/31 9:21 PM, Simon Slavin wrote:
On 31 Oct 2018, at 7:14pm, Thomas Kurz <sqlite.2...@t-net.ruhr> wrote:
may I ask whether there is a deeper reason why using a column alias in the same
SELECT query doesn't work, e.g.
SELECT column1 AS a, 5*column2 AS b, a+b FROM.......
The canonical answer to this is that there's nothing in the SQL specification
that says the columns have to be evaluated in left-to-right order.
Theoretically a computer which had massive parallelism could fetch the data
from the database, then work out all the columns at the same time.
Simon is correct, and to add to it with some bit of expansion (I assume
you asked because you are interested in the answer):
The rules for identifiers used in expressions are very strict (it's math
after all), and the rules for aliases are by design very loose. Set
algebra further imposes no order on items. If a set contains A, B and
C... and you asked for everything in the set, you can receive it in any
order. (Most DB engines stick to the table-template order as a courtesy,
but they are not forced to do so).
To explain the strictness problem, imagine a query like
SELECT C+A as x, B+A as 'x', C+B as x FROM t
Those are all perfectly valid Aliases - but you can see how it will
never conform to the strict expression identifier rules. You cannot for
instance write a simple Algebraic expression that would explain what
value the added x2 column will hold if that query was adjusted to be:
SELECT C+A as x, B+A as 'x', C+B as x, x+x as x2 FROM t
Further, as Simon mentioned, you cannot be be sure that x+x won't (try
to) be evaluated before C+A for the same query.
"But..." I hear you ask, "can we not just use the Aliases if they do
conform? and can we not simply force left to right evaluation?"
Sure we can, but the price we pay in losing parallel processing,
multi-threading, and the stupendous inconvenience of either having to
use only strict aliases, or worse, having to code verification of
aliases as suitable expression identifiers in the same query level, is a
disaster in efficiency (and possibly mathematical integrity).
Incidentally, when the query evaluated, any aliases you've used may well
be referenced from the next level (outer) query, and here we do check
suitability. Doing this:
SELECT x+x AS x2 FROM (SELECT A+B as x, B+C as x);
will simply result in an "Ambiguous column x" error, while:
SELECT x+x AS x2 FROM (SELECT A+B as x, B+C as y);
will work perfectly.
Note also that most DB engines evaluate ORDER BY sorting after any other
Query parts, almost like a higher level outer query, and as such do
allow using a column aliases in the ORDER BY clause. I'm not sure that
SQLite does though, but it surely allows column indexing in the ORDER BY:
SELECT X*2, Y+7 FROM t ORDER BY 2,1;
is equivalent to the more convoluted:
SELECT X*2, Y+7 FROM t ORDER BY Y+7, X*2;
Cheers,
Ryan
PS: Pardon the verbose post - Once when I was young and wanted to change
the way the World programs, I had questions exactly like the above, and
it took me a lot longer to figure out the truth than reading a forum
post - so this is for all the younger-me's out there. :)
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
