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

[Karl Newman]

On Thu, Aug 14, 2014 at 8:03 AM, Mike Holden <mythtv at mikeholden.org> wrote:

> Karl Newman wrote:
> > Hello list,
> >
> > I'm hoping the resident SQL experts can help me out with this one.
> > For the
> > last few months I've been running this SQL statement to find any
> > movies
> > which we've recorded that might be re-airing on commercial-free
> > (i.e.,
> > premium) channels so I can re-record them:
> >
> > SELECT program.title, program.starttime, 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;
> >
> > It had been working great and prints me out a short table of movie
> > names
> > and the names of the channels they're airing on (I have it showing
> > every
> > channel, not just the comm free ones). However, I recently upgraded
> > to
> > mysql 5.5 and now the same query returns an empty set. If I remove
> > the
> > final clause (AND R2.starttime IS NULL) then I seem to get the list
> > I had
> > before. However, removing this clause means it won't filter out
> > movies
> > which have already been recorded on a comm free channel. Further, I
> > can't
> > see WHY that clause would exclude the results, because if I include
> > R2.starttime in the column list, every result is NULL.
> >
> > I'm not 100% sure that the cause is mysql 5.5, but it seems likely
> > as
> > that's the only related change I believe I've made to the system in
> > the
> > last few days.
> >
> > Any ideas? I tried enclosing the clause in parentheses but no joy.
> > Maybe
> > I'm misusing the GROUP BY clause? (since I don't really have any
> > aggregate
> > functions) All I want is a list with distinct values for (title,
> > channel.name). I could leave out the starttime column if necessary
> > but it's
> > sort of nice to have it. Maybe it could be the earliest starttime
> > for the
> > (title, channel.name) combo?
> >
>
> I'm not familiar enough with the differences between Oracle and
> MySQL SQL to know if this is the answer here, but in Oracle, I would
> expect to include the program.starttime in the GROUP BY clause, or
> to include an aggregate function (such as MAX, MIN etc., as you
> suggested) on that column instead.
>
> So either:
>
> SELECT program.title,
>        MIN(program.starttime),
>        channel.name
> ...
> GROUP BY title, name;
>
> or
>
> SELECT program.title,
>        program.starttime,
>        channel.name
> ...
> GROUP BY title, starttime, name;
>
> As an aside, a bit of formatting on SQL statements makes them a lot
> more readable!
>
> SELECT program.title,
>        program.starttime,
>        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;
> --
> Mike Holden
>

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. Sorry about the formatting--I developed the query using mysql and
it does not preserve formatting when re-entering statements. Once I got
something working I just copied it to my shell script as-is.

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