Formatting errors on ticket export

DaveWPinUK

There appears to be a bug with the SQL generated for exporting ticket data in so far as two columns are labelled as "merged". This issue leads to formatting problems with retrieved data as an associative array to retrieve the record from database. The effect is for the values from the two columns to be merged (MySqlExecutor->getArray line 4) , but the cast information retrieved (MySqlExecutor->setupCast line 1) for the query has two columns. This causes problems when MySqlExecutor->_cast is applied to the retrieved record as typing information is misaligned after the 'merged' column.

It appears the problem might be caused by definition of MergedField->addToQuery where a field labelled "merged" is added as well as being used to annotate the query.

KevinTheJedi

DaveWPinUK

You'll need to provide:

A screenshot of Admin Panel > Dashboard > Information
Clear steps to replicate the issue
What your export columns look like (ie. what columns are you exporting specifically)
A comparison of the "incorrect" query vs. what you think it should look like (or just an example query and what you think the issue is)

Once we have this we will be able to better assist you.

Cheers.

DaveWPinUK

I discovered this issue when attempting to debug some changes I am making to the core functionality to include the ability to record an Agent's time spent when completing a thread entry. i.e. I have added an extra column to the Thread entry table called duration.

Here is the diff file for include/class.search.php

@@ -1500,7 +1500,7 @@
)))
))));

- return $query->values('merged');
+ return $query->values();
}

function getSearchQ($method, $value, $name=false) {

Here is the SQL statement generated to do a full ticket data export before change was made

SELECT A1.number AS number,
A1.ticket_id AS ticket_id,
A1.source AS source,
A1.lastupdate AS lastupdate,
B2.subject AS cdata__subject,
A1.isoverdue AS isoverdue,
B4.name AS user__name,
B2.priority AS cdata__priority,
B5.firstname AS staff__firstname,
B5.lastname AS staff__lastname,
B6.name AS team__name,
A1.team_id AS team_id,
A1.created AS created,
B7.address AS user__emails__address,
A1.dept_id AS dept_id,
B8.name AS dept__name,
A1.topic_id AS topic_id,
A2.id AS status__id,
A1.est_duedate AS est_duedate,
A1.sla_id AS sla_id,
A1.duedate AS duedate,
A1.closed AS closed,
A1.flags & 1 != 0 AS merged,
A1.ticket_pid AS ticket_pid,
A1.flags AS flags,
A1.isanswered AS isanswered,
A1.staff_id AS staff_id,
B2.OS_Type AS cdata__OS_Type,
B2.Extra_Details AS cdata__Extra_Details,
A1.isanswered = '0' AS 2GkBZxz,
(SELECT COUNT(R0.id) AS count FROM ost_thread Q7 JOIN ost_ticket Q8 ON (Q7.object_type = 'T' AND Q7.object_id = Q8.ticket_id) LEFT JOIN ost_thread_entry R0 ON (Q7.id = R0.thread_id) WHERE Q8.ticket_id = A1.ticket_id AND NOT R0.flags & 4 != 0) AS _thread_count,
(SELECT COUNT(R1.id) AS count FROM ost_thread Q7 JOIN ost_ticket Q8 ON (Q7.object_type = 'T' AND Q7.object_id = Q8.ticket_id) LEFT JOIN ost_thread_entry R0 ON (Q7.id = R0.thread_id) LEFT JOIN ost_attachment R1 ON (R1.type = 'H' AND R0.id = R1.object_id) WHERE Q8.ticket_id = A1.ticket_id AND R1.inline = 0) AS _att_count,
C0.expire > NOW() AND NOT C0.staff_id = 1 AS _locked,
(SELECT COUNT(R0.id) AS count FROM ost_thread Q7 JOIN ost_ticket Q8 ON (Q7.object_type = 'T' AND Q7.object_id = Q8.ticket_id) LEFT JOIN ost_thread_collaborator R0 ON (Q7.id = R0.thread_id) WHERE Q8.ticket_id = A1.ticket_id) AS _collabs,
COALESCE(B5.firstname, B5.lastname, B6.name, 'zzz') AS assignee,
A1.flags & 1 != 0 AS merged,
(SELECT COUNT(R0.id) AS count FROM ost_thread Q7 JOIN ost_ticket Q8 ON (Q7.object_type = 'T' AND Q7.object_id = Q8.ticket_id) LEFT JOIN ost_thread_entry R0 ON (Q7.id = R0.thread_id) WHERE Q8.ticket_id = A1.ticket_id AND NOT R0.flags & 4 != 0) AS thread_count,
(SELECT COUNT(R0.id) AS count FROM ost_thread Q7 JOIN ost_ticket Q8 ON (Q7.object_type = 'T' AND Q7.object_id = Q8.ticket_id) LEFT JOIN ost_thread_event R0 ON (Q7.id = R0.thread_id) WHERE Q8.ticket_id = A1.ticket_id AND R0.annulled = 0 AND R0.event_id = 3) AS reopen_count,
(SELECT SEC_TO_TIME(SUM( TIME_TO_SEC(R0.duration))) AS count FROM ost_thread Q7 JOIN ost_ticket Q8 ON (Q7.object_type = 'T' AND Q7.object_id = Q8.ticket_id) LEFT JOIN ost_thread_entry R0 ON (Q7.id = R0.thread_id) WHERE Q8.ticket_id = A1.ticket_id AND NOT R0.flags & 4 != 0) AS logged_time,
(SELECT COUNT(R1.id) AS count FROM ost_thread Q7 JOIN ost_ticket Q8 ON (Q7.object_type = 'T' AND Q7.object_id = Q8.ticket_id) LEFT JOIN ost_thread_entry R0 ON (Q7.id = R0.thread_id) LEFT JOIN ost_attachment R1 ON (R1.type = 'H' AND R0.id = R1.object_id) WHERE Q8.ticket_id = A1.ticket_id AND R1.inline = 0) AS attachment_count,
(SELECT COUNT(Q7.id) AS count FROM ost_task Q7 LEFT JOIN ost_ticket Q8 ON (Q7.object_id = Q8.ticket_id) WHERE Q8.ticket_id = A1.ticket_id) AS task_count
FROM ost_ticket A1 JOIN ost_ticket_status A2 ON (A1.status_id = A2.id) LEFT JOIN ost_thread A3 ON (A3.object_type = 'T' AND A1.ticket_id = A3.object_id) LEFT JOIN ost_thread_referral A4 ON (A3.id = A4.thread_id) LEFT JOIN ost_staff A5 ON (A4.object_type = 'S' AND A4.object_id = A5.staff_id) LEFT JOIN ost_thread A6 ON (A1.ticket_id = A6.object_id AND A6.object_type = 'C') LEFT JOIN ost_thread_referral A7 ON (A6.id = A7.thread_id) LEFT JOIN ost_staff A8 ON (A7.object_type = 'S' AND A7.object_id = A8.staff_id) LEFT JOIN ost_department B0 ON (A4.object_type = 'D' AND A4.object_id = B0.id) LEFT JOIN ost_department B1 ON (A7.object_type = 'D' AND A7.object_id = B1.id) LEFT JOIN ost_ticket__cdata B2 ON (A1.ticket_id = B2.ticket_id) LEFT JOIN ost_ticket_priority B3 ON (B2.priority = B3.priority_id) LEFT JOIN ost_user B4 ON (A1.user_id = B4.id) LEFT JOIN ost_staff B5 ON (A1.staff_id = B5.staff_id) LEFT JOIN ost_team B6 ON (A1.team_id = B6.team_id) LEFT JOIN ost_user_email B7 ON (B4.id = B7.user_id) LEFT JOIN ost_department B8 ON (A1.dept_id = B8.id) LEFT JOIN ost_lock C0 ON (A1.lock_id = C0.lock_id) WHERE A2.state = 'open' AND ((A2.state = 'open' AND (A1.staff_id = 1 OR A5.staff_id = 1 OR A6.object_type = 'C' AND A8.staff_id = 1)) OR A1.dept_id IN (1, 2, 3, 4) OR B0.id IN (1, 2, 3, 4) OR A6.object_type = 'C' AND B1.id IN (1, 2, 3, 4)) GROUP BY A1.ticket_id ORDER BY B3.priority_urgency ASC, A1.lastupdate DESC

and here is the SQL statement after applying the change:

SELECT A1.number AS number,
A1.ticket_id AS ticket_id,
A1.source AS source,
A1.lastupdate AS lastupdate,
B2.subject AS cdata__subject,
A1.isoverdue AS isoverdue,
B4.name AS user__name,
B2.priority AS cdata__priority,
B5.firstname AS staff__firstname,
B5.lastname AS staff__lastname,
B6.name AS team__name,
A1.team_id AS team_id,
A1.created AS created,
B7.address AS user__emails__address,
A1.dept_id AS dept_id,
B8.name AS dept__name,
A1.topic_id AS topic_id,
A2.id AS status__id,
A1.est_duedate AS est_duedate,
A1.sla_id AS sla_id,
A1.duedate AS duedate,
A1.closed AS closed,
A1.ticket_pid AS ticket_pid,
A1.flags AS flags,
A1.isanswered AS isanswered,
A1.staff_id AS staff_id,
B2.OS_Type AS cdata__OS_Type,
B2.Extra_Details AS cdata__Extra_Details,
A1.isanswered = '0' AS 2GkBZxz,
(SELECT COUNT(R0.id) AS count FROM ost_thread Q7 JOIN ost_ticket Q8 ON (Q7.object_type = 'T' AND Q7.object_id = Q8.ticket_id) LEFT JOIN ost_thread_entry R0 ON (Q7.id = R0.thread_id) WHERE Q8.ticket_id = A1.ticket_id AND NOT R0.flags & 4 != 0) AS _thread_count,
(SELECT COUNT(R1.id) AS count FROM ost_thread Q7 JOIN ost_ticket Q8 ON (Q7.object_type = 'T' AND Q7.object_id = Q8.ticket_id) LEFT JOIN ost_thread_entry R0 ON (Q7.id = R0.thread_id) LEFT JOIN ost_attachment R1 ON (R1.type = 'H' AND R0.id = R1.object_id) WHERE Q8.ticket_id = A1.ticket_id AND R1.inline = 0) AS _att_count,
C0.expire > NOW() AND NOT C0.staff_id = 1 AS _locked,
(SELECT COUNT(R0.id) AS count FROM ost_thread Q7 JOIN ost_ticket Q8 ON (Q7.object_type = 'T' AND Q7.object_id = Q8.ticket_id) LEFT JOIN ost_thread_collaborator R0 ON (Q7.id = R0.thread_id) WHERE Q8.ticket_id = A1.ticket_id) AS _collabs,
COALESCE(B5.firstname, B5.lastname, B6.name, 'zzz') AS assignee,
A1.flags & 1 != 0 AS merged,
(SELECT COUNT(R0.id) AS count FROM ost_thread Q7 JOIN ost_ticket Q8 ON (Q7.object_type = 'T' AND Q7.object_id = Q8.ticket_id) LEFT JOIN ost_thread_entry R0 ON (Q7.id = R0.thread_id) WHERE Q8.ticket_id = A1.ticket_id AND NOT R0.flags & 4 != 0) AS thread_count,
(SELECT COUNT(R0.id) AS count FROM ost_thread Q7 JOIN ost_ticket Q8 ON (Q7.object_type = 'T' AND Q7.object_id = Q8.ticket_id) LEFT JOIN ost_thread_event R0 ON (Q7.id = R0.thread_id) WHERE Q8.ticket_id = A1.ticket_id AND R0.annulled = 0 AND R0.event_id = 3) AS reopen_count,
(SELECT SEC_TO_TIME(SUM( TIME_TO_SEC(R0.duration))) AS count FROM ost_thread Q7 JOIN ost_ticket Q8 ON (Q7.object_type = 'T' AND Q7.object_id = Q8.ticket_id) LEFT JOIN ost_thread_entry R0 ON (Q7.id = R0.thread_id) WHERE Q8.ticket_id = A1.ticket_id AND NOT R0.flags & 4 != 0) AS logged_time,
(SELECT COUNT(R1.id) AS count FROM ost_thread Q7 JOIN ost_ticket Q8 ON (Q7.object_type = 'T' AND Q7.object_id = Q8.ticket_id) LEFT JOIN ost_thread_entry R0 ON (Q7.id = R0.thread_id) LEFT JOIN ost_attachment R1 ON (R1.type = 'H' AND R0.id = R1.object_id) WHERE Q8.ticket_id = A1.ticket_id AND R1.inline = 0) AS attachment_count,
(SELECT COUNT(Q7.id) AS count FROM ost_task Q7 LEFT JOIN ost_ticket Q8 ON (Q7.object_id = Q8.ticket_id) WHERE Q8.ticket_id = A1.ticket_id) AS task_count
FROM ost_ticket A1 JOIN ost_ticket_status A2 ON (A1.status_id = A2.id) LEFT JOIN ost_thread A3 ON (A3.object_type = 'T' AND A1.ticket_id = A3.object_id) LEFT JOIN ost_thread_referral A4 ON (A3.id = A4.thread_id) LEFT JOIN ost_staff A5 ON (A4.object_type = 'S' AND A4.object_id = A5.staff_id) LEFT JOIN ost_thread A6 ON (A1.ticket_id = A6.object_id AND A6.object_type = 'C') LEFT JOIN ost_thread_referral A7 ON (A6.id = A7.thread_id) LEFT JOIN ost_staff A8 ON (A7.object_type = 'S' AND A7.object_id = A8.staff_id) LEFT JOIN ost_department B0 ON (A4.object_type = 'D' AND A4.object_id = B0.id) LEFT JOIN ost_department B1 ON (A7.object_type = 'D' AND A7.object_id = B1.id) LEFT JOIN ost_ticket__cdata B2 ON (A1.ticket_id = B2.ticket_id) LEFT JOIN ost_ticket_priority B3 ON (B2.priority = B3.priority_id) LEFT JOIN ost_user B4 ON (A1.user_id = B4.id) LEFT JOIN ost_staff B5 ON (A1.staff_id = B5.staff_id) LEFT JOIN ost_team B6 ON (A1.team_id = B6.team_id) LEFT JOIN ost_user_email B7 ON (B4.id = B7.user_id) LEFT JOIN ost_department B8 ON (A1.dept_id = B8.id) LEFT JOIN ost_lock C0 ON (A1.lock_id = C0.lock_id) WHERE A2.state = 'open' AND ((A2.state = 'open' AND (A1.staff_id = 1 OR A5.staff_id = 1 OR A6.object_type = 'C' AND A8.staff_id = 1)) OR A1.dept_id IN (1, 2, 3, 4) OR B0.id IN (1, 2, 3, 4) OR A6.object_type = 'C' AND B1.id IN (1, 2, 3, 4)) GROUP BY A1.ticket_id ORDER BY B3.priority_urgency ASC, A1.lastupdate DESC

Note that the only reason this issue came to light as I found my calculated time "duration" field was being erroneously changed to an DOUBLE which took a fair amount of tracking back through the post database query code where I saw an associative array was involved.