Hi, I experimented with random numbers recently (with an older version of sqlite) and notice a strange behavior, I tried to reproduce this with the latest version and noticed that it was probably partially fixed, but still returns wrong results for my query.
The simplest table possible CREATE TABLE [TestTable] ( [Id] INTEGER PRIMARY KEY AUTOINCREMENT ) Let's fill it with numbers from 1 to 1000. For example by repeating INSERT INTO TestTable (id) values ((Select max(id) from TestTable) + 1) The following query is the problem one Select id, MinId, MaxId, abs(random() % (MaxId-MinId + 1)) as goodRangeRnd, (MinId + abs(random() % (MaxId-MinId + 1))) as actualrndid from TestTable left join (Select (select Min(Id) from TestTable) as MinId, (select Max(Id) from TestTable) as MaxId ) StatTable where id<=actualrndid order by id desc limit 10 So actualrndid here should produce random values between Min(Id) and Max(Id) and the query should return 10 rows prior or equal to this value. But the results are different depending on the version (goodRangeRnd is here just for testing purposes and to show the difference with "problem" actualrndid. It's similar to actualrndid but without (MinId +) member ) 3.6.10 - goodRangeRnd is good in every row (jumps in the full range (1..1000)) actualrndid never gets higher than 900 (wrong) the query returns correct results if actualrndid is considered correct 3.7.10 goodRangeRnd is good in every row actualrndid is good this time (jumps in the full range (1..1000)) the query returns bad results similar to the version above, but this time it is wrong if we take (id<=actualrndid) into account. So for example, there's id=911 and actualrndid=3 in the same row So looks on the way from 3.6.10 to 3.7.10 something was really fixed related to this issue, but seems like not everything. Max _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
