Opened 16 years ago
Closed 16 years ago
#4757 closed defect (fixed)
mythweb unable to edit power searches that use LEFT JOIN
Reported by: | 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)
Change History (9)
comment:1 Changed 16 years ago by
Milestone: | unknown → 0.21 |
---|
comment:2 Changed 16 years ago by
Milestone: | 0.21 → 0.22 |
---|
comment:3 Changed 16 years ago by
Owner: | changed from xris to Rob Smith |
---|---|
Status: | new → assigned |
comment:4 Changed 16 years ago by
Status: | assigned → infoneeded |
---|
Works for me, can you give me an example search that fails?
comment:5 Changed 16 years ago by
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 16 years ago by
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
comment:7 Changed 16 years ago by
Status: | infoneeded → assigned |
---|
non-critical.. moving to 0.22