JoergWausW Daily subscriber Best completion time: 1:58 Time on first attempt: 2:04
tiny question: in the full chart rankings I'm ahead of dazmagna right now with the same time. But "Best time without any aid" shows "dazmagna"...?
Posted 13th Nov 2020 at 14:04 Last edited by gareth 13th Nov 2020 at 14:06
gareth Administrator Daily subscriber Rated puzzle: Moderate Completion time: 1:31 Used 'auto remove' Used 'show invalid'
Right now it shows that you're 6th and he's 5th, but it shouldn't be random when people have equal times so probably I'm just sorting by number of seconds and not ordering in any other way. I guess I should second-level sort by something obscure but deterministic...
Posted 13th Nov 2020 at 18:37
JoergWausW Daily subscriber Best completion time: 1:58 Time on first attempt: 2:04
Thank you, but maybe you didn't quite understand my question (or I didn't understand you understanding my question).
I didn't want to address the sorting algorithm used on the ranking page. I wanted to know why there seems to be more than one sorting rule (or do you really use two different sort methods on those two pages?). You may have checked in after Gigfy44 made it impossible to see the issue I'm asking about.
In the ranking table I was 4th and dazmagna was 5th (1st, 2nd and 3rd used minor aid).
In the 'Overall puzzle statistic'->'Best time without any aid' display dazmagna was shown, not according to the order in the ranking table. I don't mind being 2nd - but it should be consistent. Now Gigfy44 is shown, because he/she didn't wait long enough to be faster ;-)
The order was not random, because every time I reloaded, the order on both pages didn't change :-o
Posted 13th Nov 2020 at 19:30
gareth Administrator Daily subscriber Rated puzzle: Moderate Completion time: 1:31 Used 'auto remove' Used 'show invalid'
I'm not an expert on the internal implementation of MySQL but I don't think you're necessarily correct. What appears to be random is certainly deterministic in a way that I haven't deliberately defined, but is no doubt defined deep down in terms of row orderings or something not necessarily meaningful in a semantic way. In any case, it certainly caches query results and, while I don't know what logic it uses to decide when a result is invalidated, but it may simply be that because the variance between the results was not defined by my query that whatever changed between the two did not invalidate whichever query result had been stored first.
Posted 13th Nov 2020 at 19:52 Last edited by gareth 13th Nov 2020 at 19:52
gareth Administrator Daily subscriber Rated puzzle: Moderate Completion time: 1:31 Used 'auto remove' Used 'show invalid'
So I've had a look. The one on this page queries the fastest first solving time using MIN() and the one on the ranking page fetches all first results ordered them by time.
Posted 14th Nov 2020 at 01:41
JoergWausW Daily subscriber Best completion time: 1:58 Time on first attempt: 2:04
thank you for your effort. If both methods are provided the same time accuracy then the MIN() algorithm obviously works differently than the ORDER BY algorithm. That's the solution :-)
Impossible to say for sure without seeing the actual implementation but I suspect Gareth has the right of it.
My understanding of ORDER BY is that the returned order is explicitly undefined when all values in the ORDER BY clause are equal. Case in point, when I loaded the full chart rankings for this puzzle after reading these comments, dazmagna is listed in 7th place and JoergWausW is in 8th. This is the opposite order that JoergWausW mentions in his comments, even though the first solve time for each user has obviously not changed in the interim.
MIN() is consistent in that it will always return the lowest value from the data set, regardless of the number of occurrences of that value in the set. MIN(1,2,3,4,5,6), MIN(1,1,2,3,5,8) and MIN (1,1,1,1,1,1) will all return "1".
If we assume that the time_elapsed value is stored with a precision of 1 second, then at the time of JoergWausW's writing, MIN(time_elapsed) would have returned "2:04". It would then depend on the specifics of both the rest of the SQL query and the code that takes the query results and converts them to HTML whether the username that was ultimately displayed on the page was clearly deterministic or apparently random.
Sorry: You must log in (create a free user) in order to be able to post comments on this puzzle.
You can however view other players' statistics and comments in the tables above.
Post comment
Key
A yellow/light blue highlight in the time distribution charts highlights your time, where relevant.
Rating scores out of 10.0 show the average difficulty rating chosen by users, where 1.0 is "Easy" and 10.0 is "Hard".
If a puzzle is opened more than once, including by loading from a saved position, then this is potentially a significant aid so it is listed as being completed with 'multiple sessions' for the purpose of the best time/average rating displays above.
Minor aid is defined as no more than one use of 'Check solution' when incomplete and/or no more than one use of 'Check solution' when wrong; and/or using highlighting aids (show repeated digits, show broken inequalities and show valid/invalid placements [slitherlink] only). Major aid is any and all other use of the solving aids except for 'show wrong'.
in the full chart rankings I'm ahead of dazmagna right now with the same time. But "Best time without any aid" shows "dazmagna"...?
Last edited by gareth 13th Nov 2020 at 14:06
I didn't want to address the sorting algorithm used on the ranking page. I wanted to know why there seems to be more than one sorting rule (or do you really use two different sort methods on those two pages?).
You may have checked in after Gigfy44 made it impossible to see the issue I'm asking about.
In the ranking table I was 4th and dazmagna was 5th (1st, 2nd and 3rd used minor aid).
In the 'Overall puzzle statistic'->'Best time without any aid' display dazmagna was shown, not according to the order in the ranking table. I don't mind being 2nd - but it should be consistent.
Now Gigfy44 is shown, because he/she didn't wait long enough to be faster ;-)
The order was not random, because every time I reloaded, the order on both pages didn't change :-o
Last edited by gareth 13th Nov 2020 at 19:52
If both methods are provided the same time accuracy then the MIN() algorithm obviously works differently than the ORDER BY algorithm.
That's the solution :-)
My understanding of ORDER BY is that the returned order is explicitly undefined when all values in the ORDER BY clause are equal. Case in point, when I loaded the full chart rankings for this puzzle after reading these comments, dazmagna is listed in 7th place and JoergWausW is in 8th. This is the opposite order that JoergWausW mentions in his comments, even though the first solve time for each user has obviously not changed in the interim.
MIN() is consistent in that it will always return the lowest value from the data set, regardless of the number of occurrences of that value in the set. MIN(1,2,3,4,5,6), MIN(1,1,2,3,5,8) and MIN (1,1,1,1,1,1) will all return "1".
If we assume that the time_elapsed value is stored with a precision of 1 second, then at the time of JoergWausW's writing, MIN(time_elapsed) would have returned "2:04". It would then depend on the specifics of both the rest of the SQL query and the code that takes the query results and converts them to HTML whether the username that was ultimately displayed on the page was clearly deterministic or apparently random.
You can however view other players' statistics and comments in the tables above.