Opened 14 years ago

Closed 14 years ago

#1286 closed defect (fixed)

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

Reported by: potterr@… Owned by: bjm
Priority: minor Milestone: unknown
Component: mythtv Version: head
Severity: medium Keywords:
Cc: Ticket locked: no

Description

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.

Change History (4)

comment:1 Changed 14 years ago by 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 .

comment:2 Changed 14 years ago by bjm

Owner: changed from Isaac Richards to bjm
Status: newassigned

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.

comment:3 Changed 14 years ago by 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 .

comment:4 Changed 14 years ago by bjm

Resolution: fixed
Status: assignedclosed

(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

Note: See TracTickets for help on using tickets.