0 tickets found when using custom fields

Arkadiusz_Jakubas · February 13, 2008, 8:43am

RT 3.6.3

When I search for example : ‘CF.{Approval}’ != ‘3. Approved’ OR Queue = ’
my.queue’
I get 0 tickets, my.queue has a hundreds ticket
When I search only queue I get results.
Is this some bug or I’m doing something wrong ?

Arkadiusz Jakubas
Arces Network, LLC
http://www.arces.net

Kenn_Crocker · February 13, 2008, 5:43pm

Arksdjudz,

Are you sure there is no embedded space or special (unprintable)

character in the literal you are comparing with? I have found that when
code doesn’t behave like the code indicates (especially simple code),
then I have made an assumption somewhere along my debugging process.
Scrutinize everything. Hope this helps.

Kenn
LBNLOn 2/13/2008 12:43 AM, Arkadiusz Jakubas wrote:

RT 3.6.3

When I search for example : ‘CF.{Approval}’ != ‘3. Approved’ OR Queue =
‘my.queue’
I get 0 tickets, my.queue has a hundreds ticket
When I search only queue I get results.
Is this some bug or I’m doing something wrong ?

–
Arkadiusz Jakubas
Arces Network, LLC
http://www.arces.net

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Arkadiusz_Jakubas · February 14, 2008, 10:23am

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:

Is this some kind of bug ? There shouldn’t be so many NULLs

Arkadiusz_Jakubas · March 4, 2008, 8:09am

Can anyone answer to this ?

Joe_Casadonte1 · March 4, 2008, 2:21pm

Can anyone answer to this ?

What’s the question? What problem are you seeing? What error messages
are you getting? What version are you running?

Regards,

joe
Joe Casadonte
joe.casadonte@oracle.com

========== ==========
== The statements and opinions expressed here are my own and do not ==
== necessarily represent those of Oracle Corporation. ==
========== ==========

Arkadiusz_Jakubas · March 5, 2008, 8:37am

Can anyone answer to this ?

Arkadiusz Jakubas
Arces Network, LLC
http://www.arces.net

Arkadiusz_Jakubas · March 5, 2008, 8:45am

It this some kind of bug ?2008/2/14, Arkadiusz Jakubas ajakubas@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

Kenn_Crocker · March 5, 2008, 6:23pm

Arkadiusz,

Is this query you are writing in RT/Ticket SQL or native SQL? I use RT

query with custom fields all the time for reporting and have no problem
at all. I also use native SQL for other queries on our Oracle DataBase.
Using native SQL against the DataBase, however, requires some finesse
when trying to get certain data. For example, to get the value of a CF
that is applied to tickets in a queue I could use the Ticket ID to go to
the OBJECTCUSTOMFIELDVALUES, make sure it is a ticket CF and not
disabled and use the CONTENT from OBJECTCUSTOMFIELDVALUES with any other
Ticket data for my report. I don’t see a need for all those joins. But
hey, that’s just me. Hope this helps.

Kenn
LBNLOn 3/5/2008 12:45 AM, Arkadiusz Jakubas wrote:

It this some kind of bug ?

2008/2/14, Arkadiusz Jakubas <ajakubas@arces.net
mailto:ajakubas@arces.net>:

I extracted sql query ( 'CF.{Approval}' LIKE '1. Pending' ) :

SELECT COUNT(DISTINCT main.id <http://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 <http://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 <http://main.id>)) AND ((main.Status != 'deleted')) AND
((main.Type = 'ticket')) AND ( ( (ObjectCustomFieldValues_3.Content
LIKE '%1. Pending%') ) )

result  :
+-------------------------+
| COUNT(DISTINCT main.id <http://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 <http://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 <http://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 <http://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

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Arkadiusz_Jakubas · March 6, 2008, 9:56am

I extracted query which RT sends to database server.
Query " ‘CF.{Approval}’ LIKE ‘1. Pending’ " which is in RT current search
field.2008/3/5, Kenneth Crocker KFCrocker@lbl.gov:

Arkadiusz,

    Is this query you are writing in RT/Ticket SQL or native SQL? I

use RT
query with custom fields all the time for reporting and have no problem
at all. I also use native SQL for other queries on our Oracle DataBase.
Using native SQL against the DataBase, however, requires some finesse
when trying to get certain data. For example, to get the value of a CF
that is applied to tickets in a queue I could use the Ticket ID to go to
the OBJECTCUSTOMFIELDVALUES, make sure it is a ticket CF and not
disabled and use the CONTENT from OBJECTCUSTOMFIELDVALUES with any other
Ticket data for my report. I don’t see a need for all those joins. But
hey, that’s just me. Hope this helps.

Kenn
LBNL

On 3/5/2008 12:45 AM, Arkadiusz Jakubas wrote:

It this some kind of bug ?

2008/2/14, Arkadiusz Jakubas <ajakubas@arces.net

mailto:ajakubas@arces.net>:

I extracted sql query ( 'CF.{Approval}' LIKE '1. Pending' ) :

SELECT COUNT(DISTINCT main.id <http://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 <http://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 <http://main.id>)) AND ((main.Status != 'deleted')) AND

((main.Type = 'ticket')) AND ( ( (ObjectCustomFieldValues_3.Content
LIKE '%1. Pending%') ) )

result  :
+-------------------------+

| COUNT(DISTINCT main.id <http://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 <http://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 <http://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 <http://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

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Arkadiusz Jakubas
Arces Network, LLC
http://www.arces.net

Arkadiusz_Jakubas · March 27, 2008, 10:47am

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:

Is this some kind of bug ? There shouldn’t be so many NULLs
Arkadiusz Jakubas
Arces Network, LLC
http://www.arces.net

Peterson_Erik · March 27, 2008, 12:46pm

From: Arkadiusz Jakubas ajakubas@arces.net
Date: Thu, 27 Mar 2008 11:47:24 +0100
To: rt-users@bestpractical.com
Subject: [rt-users] 0 tickets found when using custom fields

I extracted sql query ( ‘CF.{Approval}’ LIKE ‘1. Pending’ ) :

Hi,

I found yesterday, that I could solve a similar problem by making sure that
I used the ³is² and ³isn¹t² conditions instead of ³contains² and ³doesn¹t
contain² when using CustomFields that were chosen from dropdown select
options. I was searching for CF with {No Value}, so it may be different for
you. This changed:

(CF.{Center} LIKE ‘NULL’)

into

(CF.{Center} IS NULL)

and that seemed to return the correct tickets. Not exactly sure why, but
hopefully it can at least help get the problem solved.

I believe the number of NULL values in your return is because of the LEFT
JOIN not finding any matches in the ObjectCustomFields_1 and
ObjectCustomFields_2 (which also produces the COUNT being 0).

Hope that helps,
Erik

Erik Peterson
Manager, Project Technology Services
Education Development Center, Inc.
http://main.edc.org

Kenn_Crocker · March 27, 2008, 8:35pm

Erik,

I believe that the reason your inital SQL failed was that when you

specify a “literal” (CF.XXX LIKE ‘FFF’) then to code LOOKS for that
literal in the field, not the translated value of what NULL means.
Looking for ‘NULL’ will LITERALLY look for the letters NULL, not the hex
value of nulls (CF.XXX IS NULL). Anyway, I THINK that is why it failed.

Kenn
LBNLOn 3/27/2008 5:46 AM, Peterson, Erik wrote:

From: Arkadiusz Jakubas ajakubas@arces.net
Date: Thu, 27 Mar 2008 11:47:24 +0100
To: rt-users@bestpractical.com
Subject: [rt-users] 0 tickets found when using custom fields
I extracted sql query ( ‘CF.{Approval}’ LIKE ‘1. Pending’ ) :

Hi,

I found yesterday, that I could solve a similar problem by making sure that
I used the ï¿½isï¿½ and ï¿½isnï¿½tï¿½ conditions instead of ï¿½containsï¿½ and ï¿½doesnï¿½t
containï¿½ when using CustomFields that were chosen from dropdown select
options. I was searching for CF with {No Value}, so it may be different for
you. This changed:

(CF.{Center} LIKE ‘NULL’)

into

(CF.{Center} IS NULL)

and that seemed to return the correct tickets. Not exactly sure why, but
hopefully it can at least help get the problem solved.

I believe the number of NULL values in your return is because of the LEFT
JOIN not finding any matches in the ObjectCustomFields_1 and
ObjectCustomFields_2 (which also produces the COUNT being 0).

Hope that helps,
Erik

–
Erik Peterson
Manager, Project Technology Services
Education Development Center, Inc.
http://main.edc.org

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com