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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
