Running the following commands: --setup create temporary table t(a text); insert into t values ('one'); insert into t values ('two'); insert into t values ('three'); insert into t values ('four'); insert into t values ('five'); --test select * from (select a, (random() & 3) r from t) t2 where r<2 ;
I find that the output in column "r" is not consistent with the test. Apparently the random() function is evaluated twice, once to test, and once to output. I did this with 3.7.2, 3.7.5, and 3.6.23.1, and got the same problem in each. ---------------------------------------- I originally tried: select a, (random() & 3) r from t where (random() & 3)<2 ; This should produce inconsistent results, since the test is not on column "r". I also tried: select a, (random() & 3) r from t where r<2 ; I think this should produce consistent results (but doesn't in sqlite or mysql). Just to see what would happen, I also tried: select r, r from (select random() r from t) t2; I would expect this to return the same data twice. It doesn't. ---------------------------------------- In the comparable commands in mysql 5.0.70: select * from (select a, ((rand()*4) & 3) r from t) t2 where r<2; r was always consistent with the test. select a, ((rand()*4) & 3) r from t having r<2 ; r was inconsistent, like sqlite. select r, r from (select rand() r from t) t2; The columns matched. ---------------------------------------- I think the problem here may be that derived expressions are always evaluated from the original data. Changing that might be a performance improvement. --David Garfield _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users