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

[Arkadiusz Jakubas]

I didn't get any useful help from mailing list .

RT 3.6.3

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/20080327/b71de714/attachment.htm>