sql query malformed for "person named in the credits" custom record

[potterr@…]

Using the example "person named in the credits" custom record rules causes mythtv to produce a malformed sql query. The query it forms is:

SELECT record_tmp.recordid, program.chanid, program.starttime, IF(search = 5, recordid, 0) FROM record_tmp, program ,people, credits INNER JOIN channel ON program.chanid = channel.chanid WHERE record_tmp.recordid = '13' AND program.manualid = 0 AND ( people.name = 'Alyson Hannigan' AND credits.person = people.person AND program.chanid = credits.chanid AND program.starttime = credits.starttime ) AND channel.visible = 1 AND ((record_tmp.type = 4 OR record_tmp.type = 6 OR record_tmp.type = 9 OR record_tmp.type = 10) OR ((record_tmp.station = channel.callsign) AND ((record_tmp.type = 3) OR ((TIME_TO_SEC(record_tmp.starttime) = TIME_TO_SEC(program.starttime)) AND ((record_tmp.type = 2) OR ((DAYOFWEEK(record_tmp.startdate) = DAYOFWEEK(program.starttime) AND ((record_tmp.type = 5) OR ((TO_DAYS(record_tmp.startdate) = TO_DAYS(program.starttime)) ) ) ) ) ) ) ) ));

This leads to the error: " Unknown column 'program.chanid' in 'on clause'".

Putting parentheses around "record_tmp, program ,people, credits" after "FROM" near the beginning solves the problem.

The last post here: ​http://bugs.mysql.com/bug.php?id=13832. Explains why the syntax is wrong and why the parentheses are needed.

I'm using the latest SVN at least from a couple days ago.

Thanks.

[bjm]

(In [8972]) References #1286

In Scheduler::UpdateMatches?(), list 'program' as the first FROM table for 'JOIN ON program.* =' type queries with MySQL 5.x .

[bjm]

This does work for mysqld 4.1.15 and other earlier versions. David Engel reported the same error for the genre example. Because the 'fromclauses' excerpt is inserted into the existing query in UpdateMatches?(), a simple solution for joins from any listed table isn't obvious. However, the custom examples join on 'program'. Proglist queries through FromProgram?() work where 'program' is listed as the first table after FROM as is the case with CustomRecord::checkSyntax().

Please verify if current SVN succeeds with mysql 5.x.

[bjm]

(In [8976]) References #1286

Reverting the last change to the order of FROM clause tables as I'm told this causes other problems for MySQL 5.x .

[bjm]

(In [9264]) Fixes for JOINs in kPeopleSearch and kPowerSearch with MySQL > 5.0.12. Searches involving an additional FROM clause were broken due to syntax changes for the "table_reference" field needed for JOIN ... ON . This fixes these problems for kPeopleSearch and Custom Record examples using the Additional Tables field. Verified that the examples work in the syntax checker, proglist and for rules created from the examples.

Closes #1286