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

[Karl Newman]

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

>     SELECT program.title,
>>             program.starttime,
>>     channel.name <http://channel.name>
>>
>>     FROM program
>>     JOIN channel USING (chanid)
>>     JOIN recorded AS R USING (programid)
>>     JOIN channel AS C ON (    R.chanid = C.chanid
>>                            AND C.commmethod != -2)
>>     LEFT JOIN (recorded AS R2
>>                 JOIN channel AS C2 ON (    R2.chanid = C2.chanid
>>                                        AND C2.commmethod = -2)
>>                ) USING (programid)
>>     WHERE program.category_type = "movie"
>>     AND program.starttime > CONVERT_TZ(NOW(),"SYSTEM","UTC")
>>     AND R2.starttime IS NULL
>>     GROUP BY title, name;
>>
>
> Disclaimer: I'm not that familiar with this schema, nor mysql-specific
> SQL, nor have I tested this.  However, it appears that the "USING" form of
> joins infers what tables/columns to match.  Perhaps it's guessing
> differently now?
>
> With the "ON" form of joins the tables are explicitly named.  Perhaps
> rewrite it like this, which also names the subquery and makes it more
> standard:
>
> SELECT p.title,
>        p.starttime,
>        ch.name
> FROM program AS p
>   JOIN channel AS ch ON p.chanid = ch.chanid
>   JOIN recorded AS R ON p.programid = R.programid
>
>   JOIN channel AS C ON (R.chanid = C.chanid AND C.commmethod != -2)
>   LEFT JOIN (SELECT r2.starttime, r2.programid
>            FROM recorded AS R2
>
>            JOIN channel AS C2 ON
>               (R2.chanid = C2.chanid AND C2.commmethod = -2)
>           ) AS rec2 on rec2.programid = p.programid
> WHERE p.category_type = "movie"
>   AND p.starttime > CONVERT_TZ(NOW(),"SYSTEM","UTC")
>   AND rec2.starttime IS NULL
>
>
> It's not clear to me why there are two joins to channel - c and ch, nor
> whether rec2 should join to p.programid or r.programid, but perhaps the
> general suggestion will help you out.  I also don't think there needs to be
> a GROUP BY, since there is no aggregation.
>

The second join to channel (i.e., C2) is for the subquery to pick up
recordings that were made on comm free channels. The recording table
doesn't have that information, hence I have to pull in that data from the
channel table. The GROUP BY is an attempt to show only one listing for each
combination of (title, channel.name). It's probably an abuse of GROUP BY
but it worked at the time so I didn't pursue it further. Ideally it would
show the earliest showing for the title on the channel.

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