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

Reply via email to