Hello ! Nice explanation !
With your knowledge could you give your view about how evaluation of calculated/function columns should be done to have correct results. Like another example given on other thread: _________ CREATE TABLE a(b); INSERT INTO a(b) VALUES(1),(2),(3); SELECT a, random() as r FROM a WHERE r <> r; _________ > Wed Sep 09 2015 4:34:48 am CEST CEST from "James K. Lowden" ><jklowden at schemamania.org> Subject: Re: [sqlite] Third test of json and >index expressions, now it works > > On Sat, 5 Sep 2015 09:07:11 -0700 > Darko Volaric <lists at darko.org> wrote: > > >>I'm asking why the SQL standard restricts the use of aliases in this >> way and what the benefit of this restriction is. >> > Rationales in SQL are hard to come by. The language was promulgated by > a private firm, and the standard evolved under the aegis of what was, > for all intents and purposes, a private club. Rationales for most > warts in the language boil down to "because we say so". > > Nonetheless, there is a good reason! > > There are no aliases in SQL, Horatio. > > In every SQL database, column names are unique and unambiguous. If you > know the name of a table and a column, you've identified it. A query > can name *new* columns, but it can't create aliases for existing > ones. Consider, > > >>SELECT a+b AS x FROM t1 WHERE x=99; >> > Here, "x" is a new column, the product of the SELECT. By the rules of > SQL, it's *not* a macro for a+b, and it's not an alias. It's the name > of the column formed by computing a+b, a new column of a new table. > > Now consider, > > SELECT x from ( > SELECT a+b AS x FROM t1 > ) as TEETH_GNASHER > WHERE x=99; > > The outer query does not refer to t1, and thus not to "a" nor "b". It > sees only the new table, with its sole column, "x". And, although it's > a bit verbose, it also satisfies the sacred DRY criterion. > > Is that good? The "no aliases" rule has one thing going for it: it's > consistent. It's easy to understand and remember, and it reduces > opportunities for ambiguity. SQL is a logic-based language, and > ambiguity in logic is anathema because it's too easy to form > syntactically valid constructions that produce incorrect (and > unintended) results. > > Nearly every SQL programmer uses some other programming language as the > "real" language for his application. There's a temptation to make > informal, sometimes unwitting assumptions about the rules of SQL drawn > from that other language. The best way to understand any language > though, including SQL, is on its own terms. So double-quotes denote > identifiers, single-quotes strings, "||" contatenation, and there are > no aliases. It's not easy to slough off unwarranted associations with > other languages, but once that's done, SQL is impossible to > misconstrue. > > --jkl > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
