[rt-users] 0 tickets found when using custom fields

[Arkadiusz Jakubas]

It this some kind of bug ?


2008/2/14, Arkadiusz Jakubas <ajakubas at arces.net>:
>
> I extracted sql query ( 'CF.{Approval}' LIKE '1. Pending' ) :
>
> SELECT COUNT(DISTINCT main.id) FROM (((Tickets main  LEFT JOIN
> ObjectCustomFields ObjectCustomFields_1  ON ((ObjectCustomFields_1.ObjectId
> = '0')) AND(  ObjectCustomFields_1.ObjectId = main.Queue))  LEFT JOIN
> CustomFields CustomFields_2  ON ( CustomFields_2.id =
> ObjectCustomFields_1.CustomField))  LEFT JOIN ObjectCustomFieldValues
> ObjectCustomFieldValues_3  ON ((ObjectCustomFieldValues_3.ObjectId =
> main.id)) AND(  ObjectCustomFieldValues_3.CustomField = CustomFields_2.id)
> AND( (ObjectCustomFieldValues_3.Disabled = '0')) AND(
> (ObjectCustomFieldValues_3.ObjectType = 'RT::Ticket')))   WHERE
> ((CustomFields_2.Name = 'Approval')) AND ((main.EffectiveId = main.id))
> AND ((main.Status != 'deleted')) AND ((main.Type = 'ticket')) AND ( (
> (ObjectCustomFieldValues_3.Content LIKE '%1. Pending%') ) )
>
> result  :
> +-------------------------+
> | COUNT(DISTINCT main.id) |
> +-------------------------+
> |                       0 |
> +-------------------------+
>
> Then i modified query a little removed:
> (ObjectCustomFieldValues_3.Content LIKE '%1. Pending%')
> and
> (CustomFields_2.Name = 'Approval'))
>
> changed from:
> SELECT COUNT(DISTINCT main.id)
> to
> SELECT *
>
> query:
> SELECT * FROM (((Tickets main  LEFT JOIN ObjectCustomFields
> ObjectCustomFields_1  ON ((ObjectCustomFields_1.ObjectId = '0')) AND(
> ObjectCustomFields_1.ObjectId = main.Queue))  LEFT JOIN CustomFields
> CustomFields_2  ON ( CustomFields_2.id = ObjectCustomFields_1.CustomField))
> LEFT JOIN ObjectCustomFieldValues ObjectCustomFieldValues_3  ON
> ((ObjectCustomFieldValues_3.ObjectId = main.id)) AND(
> ObjectCustomFieldValues_3.CustomField = CustomFields_2.id) AND(
> (ObjectCustomFieldValues_3.Disabled = '0')) AND(
> (ObjectCustomFieldValues_3.ObjectType = 'RT::Ticket')))   WHERE  ((
> main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND ((
> main.Type = 'ticket')) order by main.LastUpdated desc limit 100 ;
>
>
> some result:
>
> | id    | EffectiveId | Queue | Type   | IssueStatement | Resolution |
> Owner | Subject                   | InitialPriority | FinalPriority |
> Priority | TimeEstimated | TimeWorked | Status | TimeLeft |
> Told                | Starts              | Started             |
> Due                 | Resolved            | LastUpdatedBy |
> LastUpdated         | Creator | Created             | Disabled | id   |
> CustomField | ObjectId | SortOrder | Creator | Created | LastUpdatedBy |
> LastUpdated | id   | Name | Type | Description | SortOrder | Creator |
> Created | LastUpdatedBy | LastUpdated | Disabled | LookupType | Repeated |
> Pattern | MaxValues | id   | ObjectId | CustomField | Content | Creator |
> Created | LastUpdatedBy | LastUpdated | ObjectType | LargeContent |
> ContentType | ContentEncoding | SortOrder | Disabled |
>
> +-------+-------------+-------+--------+----------------+------------+-------+---------------------------+-----------------+---------------+----------+---------------+------------+--------+----------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------+---------------------+---------+---------------------+----------+------+-------------+----------+-----------+---------+---------+---------------+-------------+------+------+------+-------------+-----------+---------+---------+---------------+-------------+----------+------------+----------+---------+-----------+------+----------+-------------+---------+---------+---------+---------------+-------------+------------+--------------+-------------+-----------------+-----------+----------+
> | 22285 |       22285 |     6 | ticket |              0 |          0 |
> 91191 | Juniper   |              20 |            39 |       22 |
> 0 |          0 | open   |        0 | 2008-02-14 08:24:01 | 1970-01-01
> 00:00:00 | 1970-01-01 00:00:00 | 2008-02-24 01:13:50 | 1970-01-01 00:00:00
> |           620 | 2008-02-14 08:24:01 |   50067 | 2008-02-13 20:18:20
> |        0 | NULL |        NULL |     NULL |      NULL |    NULL | NULL
> |          NULL | NULL        | NULL | NULL | NULL | NULL        |      NULL
> |    NULL | NULL    |          NULL | NULL        |     NULL | NULL
> |     NULL | NULL    |      NULL | NULL |     NULL |        NULL | NULL
> |    NULL | NULL    |          NULL | NULL        | NULL       |
> NULL         | NULL        | NULL            |      NULL |     NULL |
> | 22269 |       22269 |    53 | ticket |              0 |          0 |
> 93603 | Account  |              10 |            29 |       19 |
> 0 |         30 | open   |        0 | 2008-02-13 10:04:11 | 1970-01-01
> 00:00:00 | 1970-01-01 00:00:00 | 2008-02-17 16:01:13 | 1970-01-01 00:00:00
> |          5786 | 2008-02-14 07:00:43 |   93260 | 2008-02-12 16:01:13
> |        0 | NULL |        NULL |     NULL |      NULL |    NULL | NULL
> |          NULL | NULL        | NULL | NULL | NULL | NULL        |      NULL
> |    NULL | NULL    |          NULL | NULL        |     NULL | NULL
> |     NULL | NULL    |      NULL | NULL |     NULL |        NULL | NULL
> |    NULL | NULL    |          NULL | NULL        | NULL       |
> NULL         | NULL        | NULL            |      NULL |     NULL |
> | 22286 |       22286 |    47 | ticket |              0 |          0 |
> 50067 | Server reboot             |              10 |            29 |
> 14 |             0 |         60 | open   |        0 | 2008-02-14 04:13:11 |
> 1970-01-01 00:00:00 | 1970-01-01 00:00:00 | 2008-02-19 02:50:52 | 1970-01-01
> 00:00:00 |          5786 | 2008-02-14 07:00:30 |   96040 | 2008-02-14
> 02:50:52 |        0 | NULL |        NULL |     NULL |      NULL |    NULL |
> NULL    |          NULL | NULL        | NULL | NULL | NULL | NULL
> |      NULL |    NULL | NULL    |          NULL | NULL        |     NULL |
> NULL       |     NULL | NULL    |      NULL | NULL |     NULL |        NULL
> | NULL    |    NULL | NULL    |          NULL | NULL        | NULL       |
> NULL         | NULL        | NULL            |      NULL |     NULL |
>
>
> Is this some kind of bug ?  There shouldn't be so many NULLs
>
>


-- 
Arkadiusz Jakubas
Arces Network, LLC
http://www.arces.net
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20080305/5950f741/attachment.htm>