ERROR: missing FROM-clause entry for table "art"

[Tiago]

Hello all,

after successfully updating two instances of OTRS from 3.0.11 to 3.1.3, upgrading a third instance was not fully successful. The upgrade process itself was smooth and no errors occurred. Almost everything works well, except searching tickets on certain criterias. Instead of the search result only the message »No ticket data found« is shown, but in the system log the following error is recorded:

Code: Select all

Fri Apr 20 08:19:38 2012   error        OTRS-CGI-10   ERROR: missing FROM-clause entry for table "art"

The apache error log contains more information:

Code: Select all

[Fri Apr 20 08:19:38 2012] -e: DBD::Pg::st execute failed: ERROR:  missing FROM-clause entry for table "art"
[Fri Apr 20 08:19:38 2012] -e: LINE 1: ...JOIN queue sq ON sq.id = st.queue_id  AND st.id = art.ticket...
[Fri Apr 20 08:19:38 2012] -e:                                                              ^ at /opt/otrs//Kernel/System/DB.pm line 618.
[Fri Apr 20 08:19:38 2012] -e: Use of uninitialized value in concatenation (.) or string at /opt/otrs//Kernel/System/Log.pm line 161.
ERROR: OTRS-CGI-10 Perl: 5.10.1 OS: linux Time: Fri Apr 20 08:19:38 2012

 Message: ERROR:  missing FROM-clause entry for table "art"
LINE 1: ...JOIN queue sq ON sq.id = st.queue_id  AND st.id = art.ticket...
                                                             ^, SQL: 'SELECT DISTINCT st.id, st.tn, st.create_time_unix FROM ticket st INNER JOIN queue sq ON sq.id = st.queue_id  AND st.id = art.ticket_id WHERE 1=1 AND sq.group_id IN (1, 2, 5, 6, 7, 8, 9, 10, 11, 12, 15, 16, 16, 17, 18, 19, 20, 21, 22, 23, 27)  AND (((LOWER(art.a_subject) LIKE LOWER('%Sound%') ) )) ORDER BY st.create_time_unix DESC LIMIT 2000'

 Traceback (3581): 
   Module: Kernel::System::TicketSearch::TicketSearch (v1.12) Line: 1614
   Module: Kernel::Modules::AgentTicketSearch::Run (v1.143) Line: 738
   Module: Kernel::System::Web::InterfaceAgent::Run (v1.64) Line: 868
   Module: ModPerl::ROOT::ModPerl::Registry::opt_otrs_bin_cgi_2dbin_index_2epl::handler (unknown version) Line: 46
   Module: (eval) (v1.90) Line: 204
   Module: ModPerl::RegistryCooker::run (v1.90) Line: 204
   Module: ModPerl::RegistryCooker::default_handler (v1.90) Line: 170
   Module: ModPerl::Registry::handler (v1.99) Line: 31

Does anybody have a clue what could have caused this error?

Thanks in advance

Tiago

[Tiago]

Just some more information:

I compared the parameters of Kernel::System::TicketSearch::TicketSearch on two of my OTRS instances. These are the parameters used on the system where the search fails:

Code: Select all

> SQLSelect: SELECT DISTINCT st.id, st.tn, st.create_time_unix
> SQLFrom  :  FROM ticket st INNER JOIN queue sq ON sq.id = st.queue_id  AND st.id = art.ticket_id
> SQLExt   :  WHERE 1=1 AND sq.group_id IN (1, 2, 5, 6, 7, 8, 9, 10, 11, 12, 15, 16, 16, 17, 18, 19, 20, 21, 22, 23, 27)  AND (((LOWER(art.a_subject) LIKE LOWER('%Sound%') ) )) ORDER BY st.create_time_unix DESC
> Limit    : 2000

And here the parameters used on the other system:

Code: Select all

SQLSelect: SELECT DISTINCT st.id, st.tn, st.create_time_unix
SQLFrom  :  FROM ticket st INNER JOIN queue sq ON sq.id = st.queue_id INNER JOIN article art ON st.id = art.ticket_id
SQLExt   :  WHERE 1=1 AND sq.group_id IN (1, 2, 3, 7, 8, 9, 9, 10, 11, 11, 12, 13, 15)  AND (((LOWER(art.a_subject) LIKE LOWER('%Telefontickettest%') ) )) ORDER BY st.create_time_unix DESC
Limit    : 2000

Looks like that the FROM statement is incomplete. The part

INNER JOIN article art ON st.id = art.ticket_id

is missing. Hope that this is a hint.

Yours

Tiago