Searching for "Last played - Is empty" does return no results - though there are lots of never played tracks in my database.
Searching for "Last played - Not in the last - 10 - Years" does return no results - though never played tracks haven't been played in this time-frame.
I could reproduce the first problem without any problem.
I could not reproduce the second problem, it works correct:
inner join tblhistory on
tblhistory.detail_id = tbldetail.detail_id
((tblhistory.user_id=2) AND DATE(tblhistory.playeddate) <= '2006-07-13')
In short, it returns all entries that have been played from todays date minus ten years (=2006-07-13).
On my system no results are returned, which is correct.
Addendum: Please note that this is not the same as "never played", this expression is used to check for specific date ranges.
I think tracks that have never been played should be returned here,
since they have although not been played in the last ten years...
But if the first case is repaired, I can build this by myself (Not played the last OR Is empty)...
The first is fixed here:
The second must work as it does now, since it's usefull in smart playlists to use for date ranges, making it behave like you suggest should have totally other impact on the date fields, so this works as designed.
Why this: DATE(tblhistory.playeddate) <= '2016-07-11'
Instead of: tblhistory.playeddate <= DATE_SUB(NOW(), INTERVAL 2 DAY)
That's correct, because of compatibility dates needs to be generated in runtime (without time).
Dates are never compatible between SQL dialects not languages (if you have exterimented with java.util.date you know what I mean... ;))
Last played - Is empty OR Last played - Not in the last - 10 - years
--> does not work (0 returns, while it should return the same tracks as the first test...
Your query looks like this:
WHERE ( ( tblhistory.user_id=1 ) AND DATE(tblhistory.playeddate) <= '2016-07-14' ) Or ( ( tbldetail.lastplayed is null ) )
While it should look like this:
WHERE ( tblhistory.user_id=1 ) AND ( ( DATE(tblhistory.playeddate) <= '2016-07-14' ) OR ( tbldetail.lastplayed is null ) )
The where expression is built using an engine where each line in the designer represents one part of the expression.
Last played - Is empty OR will be translated to:
( (tbldetail.lastplayed is null) )Or
And Last played - Not in the last - 10 - years will be translated to:
((tblhistory.user_id=2) AND DATE(tblhistory.playeddate) <= '2006-07-16')
The last expression is the expression that has a dependency on tblHistory hence it needs to be connected to that part of the expression.
So unfortunately, this cannot be fixed at the moment.
A possible future fix would be to eliminate the use of tbldetail.lastplayed and instead work with tblhistory only, but that will require a lot of retests.
The generated SQL does not contain any errors and will not crash though, which is important.
Marked as solved (it's a pity that there are only two states available), but analysis move to our internal backlog for further analysing.
SELECT DISTINCT tbldetail.detail_id, tbldetail.filesize, tbldetail.songlength, tbldetail.lastplayed FROM tbldetail INNER JOIN tblhistory ON tblhistory.detail_id=tbldetail.detail_id WHERE 1
I will only see tracks, I've played at least once, because INNER JOIN will drop all tracks that are not listed in tblhistory.
I need to do a LEFT JOIN:
SELECT DISTINCT tbldetail.detail_id, tbldetail.filesize, tbldetail.songlength, tbldetail.lastplayed FROM tbldetail LEFT JOIN tblhistory ON tblhistory.detail_id=tbldetail.detail_id WHERE 1
Now I can see all tracks.
Yes, I noticed that also, the problem is that most often INNER JOIN should be used and this is also a part of the query engine.
So the improvement is to create exclusions in the engine which uses LEFT JOIN instead of INNER JOIN, without breaking the common cases.. I will do some quick tests, if it breaks too much it must wait.
Please try the fix in:
I made an exclusion for a specific query pattern (the two fields in this case), hopefully it should not disturb related queries.
If it seems to do, I will have to remove this exclusion and review all relevant cases for a future release.