Hello ! Although your explanation is interesting it doesn't seems to be correct:
First of all there is no point to to do anything if the whole sql statement is no correct. I mean: - If there is any reference to inexistent columns/functions/tables/views - If any expression is not valid (ex SELECT a+=b/0 AS c ...) - If there is any ambiguity .. Then and only then after a full parsing of all elements have passed the grammatical/syntactical/logical analysis then we should proceed to make a query plan to find the best alternative to answer the query. So if we need to do all of the mentioned checks before start to dig on the data we already know all the expressions and it's aliases and are able to switch between then without force users to explicitly and error prone duplicate then more than once. Cheers ! > Sat Sep 05 2015 6:23:01 pm CEST CEST from "Simon Slavin" ><slavins at bigfraud.org> Subject: Re: [sqlite] Third test of json and index >expressions, now it works > > On 5 Sep 2015, at 5:07pm, Darko Volaric <lists at darko.org> wrote: > > >>That's not what I said, why don't you read/quote the whole sentence: >> >> "Besides being part of the standard (I assume), what's the rationale for >> this restriction?" >> >> I'm asking why the SQL standard restricts the use of aliases in this way >> and what the benefit of this restriction is. >> > I'm sorry. I did not understand your grammar. Please put it down to me >being more used to British English than American English. I would have >expected "Apart from being part of the standard ...". > > The reason is that the SQL engine has to select the correct rows before it >has to work out the value of each column in the row. Consider these >statements: > > SELECT s FROM myTable WHERE l/z < 456; > SELECT * FROM myTable WHERE l/z < 456; > SELECT (s-70)*l/z, myfunction(l/z) FROM myTable WHERE l/z < 456; > > There is no point in SQL evaluating c-70*l/z or myfunction(l/z) for the >whole table if only two or three are going to satisfy b < 456. So it does the >WHERE clause first. Only once it has picked the right rows does it need to >pay attention to the bit between SELECT and FROM. Maybe no rows will satisfy >the WHERE clause and it won't have to bother at all. For a column alias to be >useful in both parts of the clause the syntax might be more like > > SELECT c-70*fract, myfunction(fract) FROM myTable WHERE (l/z AS fract) < >456; > > which is, of course, not valid SQL. > > You might also be interested to see whether this works: > > SELECT l/z AS fract, c-70*fract, myfunction(fract) FROM myTable WHERE l/z >< 456; > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
