View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0006071 | MMW v4 | Playlist / Search | public | 2009-10-21 05:09 | 2009-11-06 11:03 |
| Reporter | rusty | Assigned To | |||
| Priority | urgent | Severity | major | Reproducibility | sometimes |
| Status | closed | Resolution | fixed | ||
| Product Version | 3.1.2 | ||||
| Target Version | 3.1.2 | Fixed in Version | 3.1.2 | ||
| Summary | 0006071: Autoplaylist: multiple sorts don't work correctly sometimes | ||||
| Description | I haven't been able to narrow down the cause, but it MM sometimes seems to incorrectly sort secondary / tertiary sorts. When I tested on my library, I had the best luck replicating this by sorting on Bitrate, Title, length. This can be seen at: http://www.mediamonkey.com/forum/viewtopic.php?f=6&t=43394&st=0&sk=t&sd=a&start=15#p227938 In the first screengrab, the first 4 tracks are displayed correctly, but the 5th has the secondary sort out of order (i.e. 'Party...' should be prior to 'Shoot...'. Note: when I tested on my collection, the bug didn't occur until much later in the list, but it clearly did occur. i'm not providing a DB, because it has now been replicated by 4 of 4 people who've tried (though 2 of them didn't initially see the problem). | ||||
| Tags | No tags attached. | ||||
| Fixed in build | 1279 | ||||
|
|
The problem here is with sort orders like Length that is stored in milliseconds in the database, but in MM interface is shown in seconds. Therefore one would expect that 4:53 = 4:53, but currently 4:53 is not 4:53, because there are always some milliseconds that differentiate it. Therefore any other order added sub to this order (Length) has no effect. The fields are: File Length (B), Length (ms), Bitrate (320 = 320000 in DB), Last Played (ms), Added (ms) But probably only Length is the subject to be fixed? Because Bitrate is always only appended by the three zeros and the others fields are expected to be in Bytes or milliseconds rather than KB or seconds. |
|
|
It's almost surely because of Variable bitrate tracks, because their bitrate actually can be like 69123, 69253, ..., and it's only shown rounded to kbps, i.e. 69 in this case. So, technically this isn't a bug, but appears to be to users. I think that it will be best to solve it by comparing the already rounded values in MM, i.e. already divided by 1000. |
|
|
Fixed in build 1277. Length and Bitrate now sorts right. |
|
|
Performance glitch is minimal according to my tests: Old query (build 1276): SELECT * FROM Songs ORDER BY SongLength => takes 2.48 seconds New query (build 1277): SELECT * FROM Songs ORDER BY Round(SongLength/1000) => takes 2.59 seconds Just for interest: SELECT * FROM Songs => takes 1.2 seconds |
|
|
verified 1277, it really sorts correctly now. |
|
|
I reopened this due to some findings: 1. It doesn't work in the 1277 debug build 2. Ratings suffer from the same sort behaviour. It would be nice if it could be fixed. One way to fix it could be to use this SQL: ORDER BY CASE WHEN Rating=-1 THEN -1 WHEN Rating Between 0 AND 5 THEN 0 WHEN Rating Between 6 AND 15 THEN 10 WHEN Rating Between 16 AND 25 THEN 20 WHEN Rating Between 26 AND 35 THEN 30 WHEN Rating Between 36 AND 45 THEN 40 WHEN Rating Between 46 AND 55 THEN 50 WHEN Rating Between 56 AND 65 THEN 50 WHEN Rating Between 66 AND 75 THEN 70 WHEN Rating Between 76 AND 85 THEN 80 WHEN Rating Between 86 AND 95 THEN 90 WHEN Rating Between 96 AND 100 THEN 100 END 3. Regional settings locale is not considered when text fields are sorted in Auto-playlists but it is when you manually sort a column directly in the main window. (E.g. In Swedish, ÅÄÖ comes after Z.) 4. Manually sorted columns don't sort BitRate, Length (and Rating) in the same way as Auto-playlists now have been changed to. |
|
|
2. Might be simplified to something like: ORDER BY CASE WHEN Rating=-1 THEN -1 WHEN Rating Between 0 AND 100 THEN Round((Rating-0.01)/10) END or could possibly be left as it currently is, since 3. will be implemented. 3. This should be easy to achieve by applying the internal sorting after the SQL based sorting (it should be faster to do this than to completely get rid of the SQL based sorting since the SQL based one can use indexes sometimes). That said, I wonder whether we should do all this for MM 3.1.2, whether it isn't too big change for quite a small problem. Rather defer it? Rusty? |
|
|
Jiri, I see some problems here with your suggestion to get rid of the SQL ORDER and sort everything in operation memory. e.g. when you search for tracks by 'Title' with [x] Show at most [10] tracks checked then in case of your suggestion we would need to read all songs from DB, sort it and then throw away all the songs so that Å would be prior to A as Bex mentioned. So I think that ORDER part should be left, but should work properly. Another problem with getting rid of the ORDER part is with auto-playlists referencing another auto-playlists, e.g. the example from the issue 0006019 where the query for auto-playlist F would look like SELECT Songs.* FROM Songs WHERE ( Songs.Id IN (SELECT Songs.Id FROM Songs WHERE ((Songs.Rating>=56 and Songs.Rating<=65)) AND ( Songs.Id IN (SELECT Songs.Id FROM Songs WHERE (Songs.Rating>=16) ) ) ORDER BY Songs.SongTitle LIMIT 4) OR Songs.Id IN (SELECT Songs.Id FROM Songs WHERE ((Songs.Rating>=76 and Songs.Rating<=85)) ORDER BY Songs.SongTitle LIMIT 4) ) and is one SQL query using the LIMIT clause, by using your suggestion I would need to split the query to several sub-queries and do some sort operations directly in operation memory. It is another reason why I don't tend to the suggestion. |
|
|
Hmmm, now if I re-tested it in my case the ORDER part sorts it corectly, i.e. the sequence is A,Å,B,Z and not A,B,Z,Å as Bex indicated. I guess that 2. and 4. should be fixed for MM 3.1.2. |
|
|
What I meant was that when Swedish (or Danish or Norwegian) is selected as the locale, the sort order should be: A,B,Z,Å Most other locales sort as: A,Å,B,Z MM's IUNICODE always sort as A,Å,B,Z which to a Scandinavian is incorrect. I also think that 2) and 4) could be fixed for 3.1.2, 3) seems a bit complex and should be deferred until a suitable solution is found. Actually, everything could be deferred since they are all rather small problems, as Jiri says. |
|
|
3. Ok, that's right. There's quite a simple solution though: We can create a new collation sequence 'UserLocale' (that's just a very simple function in MM.exe) that would respect local user settings and change the SQL to: ORDER BY Title COLLATE UserLocale. |
|
|
2. I would rather say: ORDER BY CASE WHEN Rating Between 0 AND 100 THEN Round((Rating+4)/10) ELSE -1 END Because SQL's round doesn't behave like Round, but rather like Truncate, becase it just cut the decimal part as can be seen e.g. by using following SQL query: SELECT Songs.SongTitle, Songs.SongLength FROM Songs WHERE Round( Songs.SongLength/1000) = 212 |
|
|
Fixed all issues (2,3,4) in the build 1279. |
|
|
Reg SQLites Round(), it really does work as intended. The strange behaviour is instead about how SQLite treats divided integers vs. divided decimal values. An integer which is divided always gives an integer as result while divided decimal values gives a decimal result. Very confusing. This SQL demonstrates how it works SELECT 1/3, 1.0/3, Round(1/3,2), Round(1.0/3,2) The workaround is to cast the integer into Real, like this SELECT Round(1/3,2), Round(Cast(1 as Real)/3,2) But MM does "cut off" the Length and the Size instead of rounding it. That has always been the case. So using Round() in the sql does nothing but making it a fraction slower and would actually be incorrect if cast is applied. |
|
|
Peke, I suppose you re-opened this because of http://www.mediamonkey.com/forum/viewtopic.php?f=6&t=43394&sid=eed4246df5bfcf1581b0675d18c08fc5&p=229313#p229313 ? If yes, then it seems to be a test error. If you opened because of the Bex's note then I have already read it and yes, SQLite's Round() really doesn't work as one would expect and is confusing, but we at least know it now. |
|
|
Peke, why you have re-opened it again? Please assign back to me before resolving/closing, because if I search for an issue that I have already fixed then I use also "Assigned to Ludek" filter option for easier searching. |
|
|
It was due the BEX note. I think that there was similar problem in Delphi and VBScript it should be way to make similar to SQL like in this examples http://www.latiumsoftware.com/en/delphi/00033.php EDIT: I assigned to myself as it looked was something that I already encountered once. |
|
|
Maybe I don't understand, but the only problem I see is that SQLite's Round doesn't rounds up, but rounds down as I indicated and therefore the rating song order is ORDER BY CASE WHEN Rating Between 0 AND 100 THEN Round((Rating+4)/10) ELSE -1 END I don't see a problem related to this issue therefore I am re-resolving it. |
|
|
Closing I Agree with you. It's just like you said now we know the behavior. Just in case I created #6149 in case of future needs. |
