Hello ! Thank you for all feedbacks to this problem !
I originally gave a wrong/difficult example, the original one that other gave here use the equality operator instead of inequality. _______test-random.sql create table a(a); insert into a values (1),(2),(3); select 'direct select =' as ds, a, random() as r FROM a WHERE r = r; select 'indirect select =' as ds, a, r from (SELECT a, random() as r FROM a) as R WHERE r = r; select 'direct select <>' as ds, a, random() as r FROM a WHERE r <> r; select 'indirect select <>' as ds, a, r from (SELECT a, random() as r FROM a) as R WHERE r <> r; _______ _______sqlite3 < test-random.sql direct select <>|1|-192231578641646654 direct select <>|2|-4389394818887506060 direct select <>|3|-863257711086374254 indirect select <>|1|6957843808066646337 indirect select <>|2|-7871367914388137813 indirect select <>|3|3858147129752439584 _______ Given the explanations so far I still say that there is a bug in handling "columns aliases/wrapped queries" that should be addressed in sqlite. Cheers ! > Sun Sep 13 2015 10:46:21 am CEST CEST from "Clemens Ladisch" ><clemens at ladisch.de> Subject: Re: [sqlite] Third test of json and index >expressions, now it works > > James K. Lowden wrote: > >>select a, r from ( >> SELECT a, random() as r FROM a >> ) as R >> WHERE r <> r; >> >> will yield zero rows, every time. >> > $ sqlite3 > SQLite version 3.8.12 2015-09-12 19:50:58 > ... > sqlite> create table a(a); > sqlite> insert into a values (1),(2),(3); > sqlite> select a, r from (SELECT a, random() as r FROM a) as R WHERE r <> >r; > 1|-909199267849538533 > 2|8366540922678358399 > 3|-6124149463908475628 > > It happens to work only without a FROM clause (where SQLite uses > a coroutine instead of a plain table scan): > > sqlite> select r, r from (select random() as r from a); > -52277736304787193|5169235301562468199 > 563040897996752099|-5125387114614101287 > 8645211790850467701|5445986856138557805 > sqlite> select r, r from (select random() as r); > -283842963402875928|-283842963402875928 > > > Regards, > Clemens > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ?
