[mythtv-users] mysql query failing after upgrade to 5.5

[Karl Newman]

On Thu, Aug 14, 2014 at 10:24 AM, John Marshall <mythtv at marshallparty.org>
wrote:

> On 8/14/2014 12:12 PM, Gary Buhrmaster wrote:
>
>> On Thu, Aug 14, 2014 at 3:57 PM, Karl Newman <newmank1 at asme.org> wrote:
>> .....
>>
>>> Thanks Mike, but I get the same results with or without the MIN. And I
>>> still
>>> get NO results unless I remove the final "AND R2.starttime IS NULL"
>>> clause.
>>>
>>
>> recorded.starttime in my mythconverg database
>> (and in the MythTV schema creation logic) is
>> defined to be NOT NULL, and, as a primary key
>> it would be silently promoted to NOT NULL even
>> if it was not explicit.  I am not sure how you are
>> seeing NULLs in the column list as you stated.
>>
>
> The NULLs are a result of a LEFT JOIN.  Column nullability doesn't apply
> here - every column in the row from R2 is reported as NULL because the row
> matching the left side isn't found.  He's checking for NULL because he
> specifically wants items where there's no match there.
>

Exactly. The LEFT JOIN is a trick that avoids a nested SELECT. I was using
R2 to filter out programs which have already been recorded on a comm free
channel. Although, that would only be in effect if I had more than 1
recording of the same movie with at least one each from a comm free and a
not comm free channel. A very unlikely case (and transient, since I would
shortly delete the non-comm free one) but it used to work until mysql 5.5...

Thanks,

Karl
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.mythtv.org/pipermail/mythtv-users/attachments/20140814/f1f21887/attachment.html>