On Fri, Feb 14, 2014 at 6:35 AM, James K. Lowden <jklow...@schemamania.org>wrote:
> > > select id, (select id from TestTable where id = abs(random() % 100)) > > > as rndid from TestTable where id=rndid > > On Thu, 13 Feb 2014 07:26:55 -0500 > Richard Hipp <d...@sqlite.org> wrote: > > > It is undefined behavior, subject to change depending the specific > > version of SQLite, compile-time options, optimization settings, and > > the whim of the query planner. > > It should be defined. > > In the above query, random() should be evaluated once. In the SQL > model the user provides inputs once, and the system evaluates them > once. > > Once for the query or once for the row? It does evaluated once for the query. To check, just remove where select id, (select id from TestTable where id = abs(random() % 100)) as rndid from TestTable and we have plenty of the same rndid as the right column I almost understood the logic behind this, but just found the query that surprised me once again. When I wrote it, I wanted to show that to compare engines we have to remove alias usage from WHERE because most of engines forbid using it in WHERE. But the following one that probably should be compatible with other RDBMSes still shows different column values select id, rndid from ( select id, (select id from TestTable where id = abs(random() % 100)) as rndid from TestTable ) where id=rndid But I supposed the rndid in outer query should have known (fixed) value and the values from where clause should be shown identical. Max _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
