A very instructive post, could you give your opinion about what should be the behavior for the "WHERE" clause ?
I meam if we have a function on the field definition and reference it on the "WHERE" clause: CREATE TABLE myTable (a INTEGER); INSERT INTO myTable VALUES (1),(2),(3),(4),(5); CREATE VIEW myView AS SELECT a,random()%100 AS rr FROM myTable; SELECT rr FROM myView WHERE rr < 30 ORDER BY rr; ? Cheers ! > Thu Aug 27 2015 3:03:21 am CEST CEST from "James K. Lowden" ><jklowden at schemamania.org> Subject: Re: [sqlite] order by not working in >combination with random() > > On Wed, 26 Aug 2015 13:39:09 +0100 > Simon Slavin <slavins at bigfraud.org> wrote: > > > >>On 26 Aug 2015, at 11:27am, tonyp at acm.org wrote: >> >> >>>Plus, it apparently recognizes if the random() expression in the >>> ORDER BY is the same as the SELECT one and again sort correctly >>> (without re-evaluating) and without needing an alias. >>> >> Ah, but I would call /that/ a bug ! >> > I think I can convince you Postgres is right and SQLite is not. :-) > I'll give you 4 reasons: > > 1. Theory > 2. Authority > 3. Atomicity > 4. Consistency > > Theory. Like the relation of relational algebra, a SQL table has no > order. (Not only tables, but any table-like thing: view, CTE, > subquery.) If you take a table and permute its order, it's still the > same table. ORDER BY permutes the order in a particular way. It does > not change the table. Relationally, the input and output of ORDER BY > must be identical. > > Authority. As a matter of fact, CJ Date says the output of ORDER BY is > *not* a table, but a cursor. That makes sense, and also explains why > ORDER BY can, according to the SQL standard, appear only once in a > query, and only at the end. You could think of ORDER BY not as a sort > operator but as a row-returning operator that magically returns the > "next" row per a specification. > > The SQL-92 standard distinqushes between the SELECT statement with > all its many clauses, and ORDER BY: > > """ > Format > > <direct select statement: multiple rows> ::= > <query expression> [ <order by clause> ] > """ > > SQLite would do well to adopt this notion in its query processor. It > would simplify the system, and would have avoided this error. > > Atomicity. The rule in SQL is that all functions act *as if* they were > invoked only once. We had this confusion some time back with > "date(now)". There's only one "now" in a query, and there's only one > random() in a row-column. > > The column-specification of SELECT is applied *once* to each row. The > column-specification of ORDER BY (and GROUP BY) is *not* applied; it > merely *designates* the column by using the same specification text. > Consider this example: > > sqlite> select 2+2 order by '2+2'; > 2+2 > ---------- > 4 > > '2+2' here is only the column name. It's not an instruction to create > data. > > Consistency. Any two semantically equivalent queries that produce > different outputs from the same input constitute prima facia evidence > of a bug. However you restate a query, if it is logically equivalent, > it should produce the same results. The fact that you can rejigger > queries with RANDOM() and get different answers tells you immediately > that something is wrong. > > It's sometimes useful to think about how a DBMS works, and imagine > traversing trees, sorting, etc. It helps in understanding how to > express a query, and in how it will be processed. But you have to > throw all that away when thinking about query semantics. The user has > *one* chance only to state the query; he has no control over how its > executed. As he is constrained, so is the system: it cannot let > implementation details affect query semantics. That's the way it is > and should be, because it best serves the interests of both parties. > > --jkl > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
