Re: [sqlite] use column alias in same SELECT
Thank you very much for the detailed answer. I now understand things better and can better accept that they are how they are :-)) - Original Message - From: R Smith To: sqlite-users@mailinglists.sqlite.org Sent: Wednesday, October 31, 2018, 21:34:30 Subject: [sqlite] use column alias in same SELECT On 2018/10/31 9:21 PM, Simon Slavin wrote: > On 31 Oct 2018, at 7:14pm, Thomas Kurz 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] use column alias in same SELECT
On 2018/10/31 9:21 PM, Simon Slavin wrote: On 31 Oct 2018, at 7:14pm, Thomas Kurz 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
Re: [sqlite] use column alias in same SELECT
On 31 Oct 2018, at 7:14pm, Thomas Kurz 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. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
