There is nothing "stable" about the randomizer (at least not if it does
its work well).
In your queries you are simply seeing 2 different orderings with the
values accompanying the "randomized" order being linked to two different
random sets. To put it another way, the "last" order-by is not so much
redundant as it is the ONLY one that counts and the first order-by
(inside the sub-1uery) causes the sub-query to be "made stable" while it
can still have rows where the random value and the random order are
calculating to two different values so the order doesn't seem to hold to
the value (as it shouldn't). Once this sub-query has been "remembered"
though, any next order-by will do the job just fine because now values
do not get re-calculated anymore, they just get recalled from flat memory.
The only reason your two "randomiser" references in the outer query
shows the same value is that they are using the same alias in a single
row, which provides the Query Planner an easy reference to just
duplicate while inside the same single row in the cursor, but the
calculation for the ORDER BY clause happens at another time and place,
and so there is no re-referencing of the value that used to be referred
to by "randomiser" way back when. Alias != Variable.
Hope that is semi-clear...
Ryan
On 2018/03/11 9:52 AM, Cecil Westerhof wrote:
I have the following query:
SELECT *
, randomiser
, randomiser
FROM (
SELECT *
, ABS(RANDOM()) / 5E17 AS randomiser
FROM proverbs
WHERE used <> 'notUsed'
ORDER BY randomiser + IFNULL(JULIANDAY(used), 0) ASC
)
ORDER BY randomiser + IFNULL(JULIANDAY(used), 0) ASC
And this gives for example:
"Voor niets gaat de zon op." "2017-01-12" "0.337325790117148"
"0.337325790117148" "0.337325790117148"
"Met de wolven in het bos meehuilen." "2017-01-11"
"2.59601454335206" "2.59601454335206" "2.59601454335206"
"Als katten muizen,
mauwen ze niet." "2017-01-11" "2.7932230420896"
"2.7932230420896" "2.7932230420896"
"Uitstel is afstel." "2017-01-12" "1.88933779146209"
"1.88933779146209" "1.88933779146209"
"Het is rozengeur en maneschijn." "2017-01-13" "1.16363975452034"
"1.16363975452034" "1.16363975452034"
But when I remove the last ORDER (which seems redundant) I get:
"Het is rozengeur en maneschijn." "2017-01-13" "3.0795495790489"
"2.7886449148631" "6.78717082169993"
"Met de kippen op stok." "2017-01-16" "6.56483737827297"
"1.32007069440753" "5.18920985400017"
"Schoenmaker,
blijf bij je leest." "2017-01-12" "8.17180081902947"
"5.91518750003302" "4.78956808218011"
"Een goed begin is het halve werk." "2017-01-17"
"7.70627730482033" "15.580638189131" "2.0146022387495"
"Zoals het klokje thuis tikt,
tikt het nergens." "2017-01-18" "8.53059705262686"
"15.7307229942" "1.1516802288132"
So randomiser is not stable any-more.
For the moment I work with:
SELECT *
, randomiser
, randomiser
FROM (
SELECT *
, ABS(RANDOM()) / 5E17 AS randomiser
FROM proverbs
WHERE used <> 'notUsed'
ORDER BY used ASC
)
ORDER BY randomiser + IFNULL(JULIANDAY(used), 0) ASC
Then randomiser is stable and the time needed is only slightly more as
time for the second query. (It is about 43, 53 and 46 ms.)
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
