OK, I am truly grateful for what I have learned in this thread! And I have composed an new, personal, rule: don't use a function in _anything_ other than in the column list portion of a SELECT statement. If necessary, this means I will be using a CREATE TEMPORARY TABLE results type construct when I need to ORDER BY or WHERE using a "function". Such as:
BEGIN TRANSACTION; DROP TABLE __results; CREATE TEMPORARY TABLE __results AS SELECT ...columns..., ...functions... FROM sometable WHERE ...only have column names...; SELECT * FROM __results ORDER BY ???? WHERE ..columns containing results of functions... ; COMMIT TRANSACTION; Does anybody see where this will fail (other than, perhaps disk space or an existing real table called __results already existing)? I agree is will likely not perform as well as it could. But, personally, I will embrace lower performance for correctness (I.e. getting what I really want). On Fri, Aug 28, 2015 at 4:20 AM, Domingo Alvarez Duarte < sqlite-mail at dev.dadbiz.es> wrote: > Now that we have solved the expression column reevaluation problem on the > "ORDER BY" clause what about the same problem 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 a, rr FROM myView WHERE rr < 30 ORDER BY rr; > > Some outputs of the above through sqlite3 "sqlite3 < > test-where-random.sql": > > > ===== > > 2|-86 > 4|-60 > 1|59 > ==== > > 5|5 > 3|66 > > ==== > > 5|-83 > 4|30 > 1|64 > ==== > > Please remember that the "random" function here is only a detail the real > problem is the "column expression" been reevaluated more than once. > > Cheers ! > > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Schrodinger's backup: The condition of any backup is unknown until a restore is attempted. Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
