At 16:00 27/08/2015, you wrote: ><quote> >An *ORDER BY* clause in SQL <https://en.wikipedia.org/wiki/SQL> specifies >that a SQL SELECT <https://en.wikipedia.org/wiki/Select_(SQL)> statement >returns a result set <https://en.wikipedia.org/wiki/Result_set> with the >rows being sorted by the values of one or more columns. The sort criteria >do not have to be included in the result set. The sort criteria can be >expressions, including ? but not limited to ? column names, user-defined >functions <https://en.wikipedia.org/wiki/User_defined_function>, >arithmetic >operations, or CASE expressions. The expressions are evaluated and the >results are used for the sorting, i.e. the values stored in the column or >the results of the function call. ><quote>
Pretty clear until now. >So, applying this to our subject, "rr" is _NOT_ the name a column in the >table. Yes: it's a temporary schema name. > It is an alias to a "user-defined function" (user-defined in this >case meaning not defined in the SQL standard itself). You're bending the meaning above. rr is not a C preprocessor define nor a C++ function reference, it is an alias to a column, i.e. a schema name, not an invokation of the computation that took place for creating the value in the column beforehand. > Therefore, based on >the last sentence of the above quote, the random() is evaluated AT THE >TIME >THE ORDER BY IS EXECUTED. That would be a bug (like it currently is). Like Scott Hess just said shows that it isn't possible to perform a sort using this interpretation. Let's forget random() and use instead a user function inc() which initializes an integer sequence when fed an integral parameter and returns the next integer every time it's invoke without parameter. Then "select inc() col order by col" wouldn't return the correct value. Granted that the order by clause isn't of much use here but if ever such construct is used to assign increasing reference numbers in step=1 to items in a stock, then you're going to throw SQLite away. Worst, say a user function new_uuid() returns new unique IDs and also stores them elsewhere (e.g. by communication with the underlying application) you'll end up with diverging lists of "used" uuids. The fact that this example delivers a result clearly violating the where clause (and also the order by if you put "order by rr") demonstrates that this interpretation doesn't hold water. drop table if exists mytable; drop view if exists myview; CREATE TABLE myTable (a INTEGER); INSERT INTO myTable VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9); CREATE VIEW myView AS SELECT a,random()%100 AS rr FROM myTable; SELECT rr FROM myView WHERE rr < 30; The wrong behavior contradicts SQLite own documentation, http://www.sqlite.org/lang_select.html and especially the second paragraph below >Each ORDER BY expression is processed as follows: > * If the ORDER BY expression is a constant integer K then the > expression is considered an alias for the K-th column of the result > set (columns are numbered from left to right starting with 1). > * If the ORDER BY expression is an identifier that corresponds to > the alias of one of the output columns, then the expression is > considered an alias for that column. > * Otherwise, if the ORDER BY expression is any other expression, > it is evaluated and the returned value used to order the output rows. > If the SELECT statement is a simple SELECT, then an ORDER BY may > contain any arbitrary expressions. However, if the SELECT is a > compound SELECT, then ORDER BY expressions that are not aliases to > output columns must be exactly the same as an expression used as an > output column. -- jcd
