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

[John Marshall]

>     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.