Opened 13 years ago

Closed 12 years ago

#4757 closed defect (fixed)

mythweb unable to edit power searches that use LEFT JOIN

Reported by: Ron Frazier <ron@…> Owned by: Rob Smith
Priority: minor Milestone: 0.22
Component: mythweb Version: head
Severity: medium Keywords:
Cc: Ticket locked: no

Description

If you use mythfrontend to create a Custom Record (Power Search) that includes either the programgenres or programrating tables, those tables will be include with a LEFT JOIN. If you then go to edit it via the mythweb interface, mythweb is unable to save the changes and generates an SQL error related to the LEFT JOIN.

Attachments (1)

record.sql (1.1 KB) - added by Ron Frazier <ron@…> 12 years ago.
power search example

Download all attachments as: .zip

Change History (9)

comment:1 Changed 13 years ago by stuartm

Milestone: unknown0.21

comment:2 Changed 13 years ago by danielk

Milestone: 0.210.22

non-critical.. moving to 0.22

comment:3 Changed 12 years ago by Rob Smith

Owner: changed from xris to Rob Smith
Status: newassigned

comment:4 Changed 12 years ago by Rob Smith

Status: assignedinfoneeded

Works for me, can you give me an example search that fails?

comment:5 Changed 12 years ago by anonymous

Most likely your test only used one table besides the outer joined table. The problem appears to come when you have 2 tables + the outer joined table. The generated SQL ends up mixing comma separated joins with the LEFT JOIN. The SQL you end up with is: ...FROM table1, table2, LEFT JOIN table3 ON ...

Heres a SQL dump for a record that doesn't work.

INSERT INTO record (recordid, type, chanid, starttime, startdate, endtime, enddate, title, subtitle, description, category, profile, recpriority, autoexpire, maxepisodes, maxnewest, startoffset, endoffset, recgroup, dupmethod, dupin, station, seriesid, programid, search, autotranscode, autocommflag, autouserjob1, autouserjob2, autouserjob3, autouserjob4, findday, findtime, findid, inactive, parentid, transcoder, tsdefault, playgroup, prefinput, next_record, last_record, last_delete, storagegroup, avg_delay) VALUES (173, 4, 0, '11:47:09', '2008-02-10', '11:47:09', '2008-02-10', 'Survivor (Power Search)', 'LEFT JOIN programgenres ON program.chanid = programgenres.chanid AND program.starttime = programgenres.starttime \n', 'program.title LIKE Survivor: % \nAND programgenres.genre = Reality \nAND channel.callsign = WWJ ', , 'Default', 0, 1, 0, 0, 0, 0, 'Default', 6, 31, , , , 1, 0, 1, 1, 0, 0, 0, 1, '00:00:00', 733447, 0, 0, 0, 1, 'Default', 0, '0000-00-00 00:00:00', '2008-05-11 22:00:04', '2008-05-12 08:45:42', 'Default', 43);

comment:6 Changed 12 years ago by anonymous

Hmmmm...that SQL didn't come through right. It would seem trac ate the pairs of single-quotes. Apparently theres a bug in the trac software too. Anyway, I'll repost the SQL in an attached file

Changed 12 years ago by Ron Frazier <ron@…>

Attachment: record.sql added

power search example

comment:7 Changed 12 years ago by Rob Smith

Status: infoneededassigned

comment:8 Changed 12 years ago by Rob Smith

Resolution: fixed
Status: assignedclosed

(In [17575]) Fixes #4757, this fixes the custom scheduler from incorrectly adding a , before a LEFT JOIN. Ron, your schedule still doesnt work, but its no longer the LEFT JOIN breaking things.

Note: See TracTickets for help on using tickets.