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

[Arkadiusz Jakubas]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20080214/dfcc532c/attachment.htm>