On 27 Aug 2015, at 9:11am, Domingo Alvarez Duarte <sqlite-mail at dev.dadbiz.es> wrote:
> 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; That's very clever. You don't even need the 'ORDER BY' to show the problem, just a situation where a SELECT states specifically 'WHERE rr < 30' but gets values of rr greater than 30. The WHERE clause is definitely part of the SELECT command and not a processing of later results. It makes a better test for consistency than my original 'ORDER BY rr'. I just ran a specific test on my (not quite up-to-date) version of SQLite: SQLite version 3.8.10.2 2015-05-20 18:17:19 Enter ".help" for usage hints. sqlite> CREATE TABLE myTable (a INTEGER); sqlite> INSERT INTO myTable VALUES (1),(2),(3),(4),(5); sqlite> CREATE VIEW myView AS SELECT a,random()%100 AS rr FROM myTable; sqlite> SELECT rr FROM myView WHERE rr < 30; 21 83 8 I find it difficult to say that this result can be anything except wrong. It's worth noting that this is not a problem just with the 'random()' function but also with any other function which does not lead to the same result every time. For instance, a user-defined function. It may be that a fix which checks specifically for the 'random()' function is the wrong thing to do. On 27 Aug 2015, at 12:11pm, R.Smith <rsmith at rsweb.co.za> wrote: > Further to this, among other posts there seem to be some confusion about the > purpose of aliasing - especially since people think in programming terms. An > alias isn't a variable or an array, it doesn't magically hold a state - it > simply is a nick-name for a function. Sure. I chose to use an alias just to emphasise how wrong the result looked. However, I have seen code written by teams where the person writing the query has no real idea whether they're querying a TABLE, a VIEW, or a virtual table. They could end up using a column called 'rr' without any understanding of how it is generated. And they would be disconcerted by a result like the above. Simon.
