PDA

View Full Version : Database Error when searching issues


Trevor Hannant
29 Aug 2011, 09:23
I have the What's New Reloaded modification installed on a site and when enabling the search for Issue Notes, it throws the following Database error:

Invalid SQL:

SELECT issue.issueid
FROM vb__pt_issue AS issue

LEFT JOIN vb__pt_issueread AS issueread ON (issueread.issueid = issue.issueid AND issueread.userid = 1)
INNER JOIN vb__pt_project AS project ON (project.projectid = issue.projectid)
LEFT JOIN vb__pt_projectread AS projectread ON (projectread.projectid = project.projectid AND projectread.userid = 1)

WHERE issue.projectid IN(2, 1)

AND issue.lastpost > IF(issueread.readtime IS NULL, 1312185139, issueread.readtime)
AND issue.lastpost > IF(projectread.readtime IS NULL, 1312185139, projectread.readtime)
AND issue.lastpost > 1312185139

AND issue.open <> 10
ORDER BY issue.lastpost desc
LIMIT 5000;

MySQL Error : Unknown column 'issue.open' in 'where clause'
Error Number : 1054
Request Date : Monday, August 29th 2011 @ 08:52:19 AM
Error Date : Monday, August 29th 2011 @ 08:52:19 AM
Script : http://www.domain.net/search.php?do=getnew&contenttype=All_Content
A quick search finds the following code in packages/projecttools/search/searchcontroller/newissuenote.php:

$contenttypeid = vB_Search_Core::get_instance()->get_contenttypeid('vBProjectTools', 'Issue');
$threads = $db->query_read_slave($q = "
SELECT issue.issueid
FROM " . TABLE_PREFIX . "pt_issue AS issue
$marking_join
WHERE issue.projectid IN(" . implode(', ', $projectids) . ")
$lastpost_where
AND issue.open <> 10
ORDER BY $orderby
LIMIT " . intval($vbulletin->options['maxresults'])
);
'open' is not a valid column in version 2.1.2.

PitchouneN64ngc
30 Aug 2011, 20:52
I will see what is causing this issue :)

--------------- Added Tuesday 30 August 2011 at 22:03 ---------------

Okay issue.open was changed to issue.state, I don't remember in which release (maybe before 2.1.0).

--------------- Added Tuesday 30 August 2011 at 22:33 ---------------

Okay I copied code from thread search and didn't see the open/close state wasn't the same :p

You can remove this line:

AND issue.open <> 10In threads, open == 10 means a poll. There is no poll in issues :p