View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0013070 | MMW v4 | DB/FileMonitor | public | 2016-01-13 16:54 | 2017-03-31 15:39 |
| Reporter | Ludek | Assigned To | |||
| Priority | immediate | Severity | minor | Reproducibility | always |
| Status | closed | Resolution | fixed | ||
| Product Version | 4.0.7 | ||||
| Target Version | 5.0.0 | Fixed in Version | 4.1.15 | ||
| Summary | 0013070: SQLite parser stack overflow for overcomplex SQL created by complex (nested) auto-playlists | ||||
| Description | User from ticket SHP-228-32414 created an auto-playlist referencing other auto-playlist and so on... On the ninth autoplaylist in the stack there is SQL error. Test data are provided via the ticket SHP-228-32414 | ||||
| Additional Information | https://www.sqlite.org/limits.html | ||||
| Tags | No tags attached. | ||||
| Fixed in build | 1816 | ||||
| related to | 0004805 | closed | Ludek | MMW v4 | Auto-playlist can be indirectly self-referenced - add a prevention (regression) |
| related to | 0006019 | closed | Ludek | MMW v4 | Auto-playlist's "Playlist is" feature not working properly (regression) |
| related to | 0011635 | closed | Ludek | MMW v4 | SQL error when working with Device Profile (overcomplex SQL issues) |
| related to | 0013074 | closed | jiri | MMW 5 | Auto-playlist configuration is limited |
| related to | 0013590 | closed | Ludek | MMW v4 | WiFi sync incomplete may appear when an over-complex auto-playlist is on sync-list |
| related to | 0014157 | closed | Ludek | MMW v4 | DROP TABLE _TempPlaylistContent_392_Th_2068": database schema has changed (6,6) |
|
|
The produced SQL for the ninth nested auto-playlists is: SELECT Songs.* FROM Songs WHERE (( Songs.Id IN (SELECT Songs.Id FROM Songs WHERE (( Songs.Id IN (SELECT Songs.Id FROM Songs WHERE (( Songs.Id IN (SELECT Songs.Id FROM Songs WHERE (( Songs.Id IN (SELECT Songs.Id FROM Songs WHERE (( Songs.Id IN (SELECT Songs.Id FROM Songs WHERE (( Songs.Id IN (SELECT Songs.Id FROM Songs WHERE (( Songs.Id IN (SELECT Songs.Id FROM Songs WHERE (( Songs.Id IN (SELECT Songs.Id FROM Songs WHERE (( Songs.Id IN (SELECT PlaylistSongs.IdSong FROM PlaylistSongs WHERE PlaylistSongs.IdPlaylist IN ( 120) ) )) AND Songs.TrackType in (1,7) ) )) AND Songs.TrackType in (1,7) ) )) AND Songs.TrackType in (1,7) ) )) AND Songs.TrackType in (1,7)))) AND Songs.TrackType in (1,7)))) AND Songs.TrackType in (1,7)))) AND Songs.TrackType in (1,7)))) AND Songs.TrackType in (1,7)))) AND Songs.TrackType in (1,7) Max level depth of nesting like this seems to be 8 (fails for 9 and above), surprising as the limitation is not mentioned here https://www.sqlite.org/limits.html This one works (8 nested auto-playlists): SELECT Songs.* FROM Songs WHERE Songs.Id IN (SELECT Songs.Id FROM Songs WHERE (( Songs.Id IN (SELECT Songs.Id FROM Songs WHERE (( Songs.Id IN (SELECT Songs.Id FROM Songs WHERE (( Songs.Id IN (SELECT Songs.Id FROM Songs WHERE (( Songs.Id IN (SELECT Songs.Id FROM Songs WHERE (( Songs.Id IN (SELECT Songs.Id FROM Songs WHERE (( Songs.Id IN (SELECT Songs.Id FROM Songs WHERE (( Songs.Id IN (SELECT PlaylistSongs.IdSong FROM PlaylistSongs WHERE PlaylistSongs.IdPlaylist IN ( 120) ) )) AND Songs.TrackType in (1,7) ) )) AND Songs.TrackType in (1,7) ) )) AND Songs.TrackType in (1,7) ) )) AND Songs.TrackType in (1,7)))) AND Songs.TrackType in (1,7)))) AND Songs.TrackType in (1,7)))) AND Songs.TrackType in (1,7)) So far confirmed this limitation with all SQLite editors I have tried (SQLiteStudio, SQLiteSpy, SQLiteManager, SQLiteMan, SQLiteBrowser, SQLiteAnalyzer), if we are unable to go over this limit with SQLite then we also shouldn't allow users to create such auto-playlists. |
|
|
Like in 0011635 this should be fixable by using temporary tables. Since this solution will most likely be slower than the current one, we should implement a heuristics that will decide whether to use temp tables or direct SQL. I guess that temp tables should always be used when the AP hierarchy is deeper than 1. Note that since more AP queries can be executed at once (e.g. for device sync), we should use a global counter for naming the temporary tables in order to avoid conflicts. |
|
|
Fixed in 5.0.0.2037 (same fix as 0011635 ) |
|
|
Because the fix looks quite low risk I merged it into 4.1.15.1814 |
|
|
Verified 1814 |
|
|
Re-opened: It seems that the temp table solution caused a regression: http://www.mediamonkey.com/forum/viewtopic.php?f=6&t=86280#p428463 |
|
|
Fixed in 1816 |
|
|
verified 1816 waiting for user confirmation I tested using 4 9 nested auto-playlists for syncing. |
|
|
User has confirmed http://www.mediamonkey.com/forum/viewtopic.php?f=6&t=86280&p=428727#p428727 |
