SQLite LIKE matching characters don't work as they should

Beta Testing for Windows Products and plugins

Moderator: Gurus

ZvezdanD
Posts: 3098
Joined: Thu Jun 08, 2006 7:40 pm

SQLite LIKE matching characters don't work as they should

Post by ZvezdanD » Sun Apr 05, 2009 1:00 pm

If I have tracks with "Pink Floyd" as Artist, the following query doesn't return any track:

Code: Select all

SELECT * FROM Songs WHERE Artist LIKE 'P%k Fl%d'
Following queries also don't work as they should:

Code: Select all

SELECT * FROM Songs WHERE Artist Like 'P_nk Flo_d'

Code: Select all

SELECT * FROM Songs WHERE Artist Like '_ink Floy_'

Code: Select all

SELECT * FROM Songs WHERE Artist Like 'Pink Floy_'

Code: Select all

SELECT * FROM Songs WHERE Artist Like '_ink Floyd'
SQLiteSpy with same database and same queries returns correctly all "Pink FLoyd" tracks.

However, the following query works correctly in MM as well as in SQLiteSpy:

Code: Select all

SELECT * FROM Songs WHERE Artist Like '%nk Flo%'
What have you done with the SQLite engine? Please guys, this is really serious. Many queries require that matching character "%" be in the middle of the string, not only on its ends. And what with "_" matching character - it doesn't work even when it is on the ends of the string.
Magic Nodes 4.3.3 / 5.2 RegExp Find & Replace 4.4.9 / 5.2  Invert Selection/Select None 1.5.1  Export/Create Playlists for Child Nodes 4.1.1 / 5.4.1  Expand Child Nodes/Expand All 1.1.2  Event Logger 2.7  Filtered Statistics Report 1.6  Track Redirection & Synchronization 3.4.2  Restore/Synchronize Database 3.1.8 / 4.0.1  Find Currently Playing Track 1.3.2  Queue List 1.2.1  Add to Library on Play 1.0.1  Tree Report for Child Nodes 1.1.1  Update Location of Files in Database 1.4.5 / 2.3  Inherit Child Playlists 1.0.2  Add Currently Playing/Selected Track(s) to Playlist 1.1.2

Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Re: SQLite LIKE matching characters don't work as they should

Post by Bex » Sun Apr 05, 2009 3:21 pm

Reported as:
http://www.ventismedia.com/mantis/view.php?id=5474

For now you can instead use GLOB which has the same wildcard characters and escape syntax as SQL Jet. However it's case sensitive so if you don't want that you need to use it in conjunction with UPPERW().
E.g:

Code: Select all

SELECT Artist FROM Songs WHERE UPPERW(Artist) GLOB 'P*K FL*D'

Code: Select all

SELECT Artist FROM Songs WHERE UPPERW(Artist) GLOB 'P?NK FLO?D'
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!

All My Scripts

ZvezdanD
Posts: 3098
Joined: Thu Jun 08, 2006 7:40 pm

Re: SQLite LIKE matching characters don't work as they should

Post by ZvezdanD » Sun Apr 05, 2009 4:16 pm

Thanks for the suggestion, but it is not an option for me. I would like this bug to be corrected since it could confuse other users of my scripts too. For example, some user of Magic Nodes could type LIKE operator inside of the Filter qualifier and could get incorrect results: http://www.mediamonkey.com/forum/viewto ... 79#p180079
Magic Nodes 4.3.3 / 5.2 RegExp Find & Replace 4.4.9 / 5.2  Invert Selection/Select None 1.5.1  Export/Create Playlists for Child Nodes 4.1.1 / 5.4.1  Expand Child Nodes/Expand All 1.1.2  Event Logger 2.7  Filtered Statistics Report 1.6  Track Redirection & Synchronization 3.4.2  Restore/Synchronize Database 3.1.8 / 4.0.1  Find Currently Playing Track 1.3.2  Queue List 1.2.1  Add to Library on Play 1.0.1  Tree Report for Child Nodes 1.1.1  Update Location of Files in Database 1.4.5 / 2.3  Inherit Child Playlists 1.0.2  Add Currently Playing/Selected Track(s) to Playlist 1.1.2

jiri
Posts: 5395
Joined: Tue Aug 14, 2001 7:00 pm
Location: Czech Republic
Contact:

Re: SQLite LIKE matching characters don't work as they should

Post by jiri » Tue Apr 07, 2009 1:27 pm

Let me explain this: Yes, we use our own LIKE function implementation, because the default one in SQLite has one limitation (that's rather serious for usage in MM) - it doesn't support Unicode. Our own implementation is very simple in order to support what's needed for searches in MM and also to be as fast as possible.

As for the fix, rather than modifying the current LIKE implementation (particularly in order to avoid regressions and to not slow it down), I'd like to add support for RegExps. They will hopefully appear in the next version, at least in case I find a good implementation that is suitable for inclusion in MM.

Jiri

ZvezdanD
Posts: 3098
Joined: Thu Jun 08, 2006 7:40 pm

Re: SQLite LIKE matching characters don't work as they should

Post by ZvezdanD » Tue Apr 07, 2009 5:42 pm

At least! It would be really great if you add RegExp to the database engine. However, I disagree that you should leave the current implementation of the LIKE operator as it is now. As you said in one another thread, "not everybody knows regular expressions", so many would continue to use LIKE. I am pretty sure that in every possible SQL database engine with the expression Artist LIKE 'P%k Fl%d' you will get returned Pink Floyd tracks, not empty set, so I cannot see what kind of regression could result if you implement proper support for wildcards.

Honestly, I don't see a point to have LIKE operator as it is now. This operator is useful only if it used with wildcards - if you drop them, then we don't need that operator. Instead of Artist LIKE '%nk Flo%' we could write InStr(Artist, 'nk Flo'), instead of Artist LIKE 'Pink Fl%' we could write substr(Artist, 1, 7) = 'Pink Fl' and instead of Artist LIKE '%k Floyd' we could write substr(Artist, -1, 7). By the way, thank you very much for implementing InStr function, but it would be even more better if you implement InStrRev as well.
Magic Nodes 4.3.3 / 5.2 RegExp Find & Replace 4.4.9 / 5.2  Invert Selection/Select None 1.5.1  Export/Create Playlists for Child Nodes 4.1.1 / 5.4.1  Expand Child Nodes/Expand All 1.1.2  Event Logger 2.7  Filtered Statistics Report 1.6  Track Redirection & Synchronization 3.4.2  Restore/Synchronize Database 3.1.8 / 4.0.1  Find Currently Playing Track 1.3.2  Queue List 1.2.1  Add to Library on Play 1.0.1  Tree Report for Child Nodes 1.1.1  Update Location of Files in Database 1.4.5 / 2.3  Inherit Child Playlists 1.0.2  Add Currently Playing/Selected Track(s) to Playlist 1.1.2

Post Reply