View Issue Details

IDProjectCategoryView StatusLast Update
0013070MediaMonkey (current)DB/FileMonitorpublic2017-03-31 15:39
ReporterLudek 
PriorityimmediateSeverityminorReproducibilityalways
Status closedResolutionfixed 
Product Version4.0.7 
Target Version5.0.0Fixed in Version4.1.15 
Summary0013070: SQLite parser stack overflow for overcomplex SQL created by complex (nested) auto-playlists
DescriptionUser 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 Informationhttps://www.sqlite.org/limits.html
TagsNo tags attached.
Fixed in build1816

Relationships

related to 0004805 closedLudek MediaMonkey (current) Auto-playlist can be indirectly self-referenced - add a prevention (regression) 
related to 0006019 closedLudek MediaMonkey (current) Auto-playlist's "Playlist is" feature not working properly (regression) 
related to 0011635 closedLudek MediaMonkey (current) SQL error when working with Device Profile (overcomplex SQL issues) 
related to 0013074 resolvedjiri MediaMonkey 5 Auto-playlist configuration is limited 
related to 0013590 closedLudek MediaMonkey (current) WiFi sync incomplete may appear when an over-complex auto-playlist is on sync-list 
related to 0014157 closedLudek MediaMonkey (current) DROP TABLE _TempPlaylistContent_392_Th_2068": database schema has changed (6,6) 

Activities

Ludek

2016-01-13 17:38

developer   ~0043854

Last edited: 2016-01-13 19:21

View 10 revisions

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.

Ludek

2016-10-11 13:24

developer   ~0045896

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.

Ludek

2016-10-13 19:29

developer   ~0045924

Last edited: 2016-10-13 19:29

View 2 revisions

Fixed in 5.0.0.2037 (same fix as 0011635 )

Ludek

2016-10-13 20:46

developer   ~0045928

Because the fix looks quite low risk I merged it into 4.1.15.1814

peke

2016-10-19 23:10

developer   ~0045966

Verified 1814

Ludek

2016-10-24 09:53

developer   ~0046004

Re-opened: It seems that the temp table solution caused a regression:
http://www.mediamonkey.com/forum/viewtopic.php?f=6&t=86280#p428463

Ludek

2016-10-24 11:47

developer   ~0046008

Fixed in 1816

peke

2016-10-31 02:01

developer   ~0046058

verified 1816

waiting for user confirmation I tested using 4 9 nested auto-playlists for syncing.

Ludek

2016-11-01 21:21

developer   ~0046077

User has confirmed http://www.mediamonkey.com/forum/viewtopic.php?f=6&t=86280&p=428727#p428727