[rt-users] Searching Custom Fields for (no value)

[G. Richard Bellamy]

I realize this has been mentioned ad-nauseum on this list - but I can't
seem to find a post that discusses the fix.
 
First, the particulars:
Debian, tracking both sarge and sid. All software is installed "The
Debian Way."
Perl v5.8.4;
DBD::mysql v2.9003;
DBI v1.46;
DBIx::SearchBuilder v1.22;
mysql v14.7 Distrib 4.1.11
RT v3.4.1;
 
When searching for tickets with "(no value)" in the custom field, I see
this in the mysql.log:
 
SELECT
  COUNT(DISTINCT main.id)
FROM
  ((Tickets main  LEFT JOIN
  ObjectCustomFieldValues ObjectCustomFieldValues_1  ON
((ObjectCustomFieldValues_1.ObjectType = 'RT::Ticket'))
    AND( (ObjectCustomFieldValues_1.Disabled = '0'))
    AND(  main.id = ObjectCustomFieldValues_1.ObjectId))
  JOIN CustomFields CustomFields_2  ON (
ObjectCustomFieldValues_1.CustomField = CustomFields_2.id)
    AND( (CustomFields_2.Name = 'Client')))
WHERE
  ((main.EffectiveId = main.id))
    AND ((main.Status != 'deleted'))
    AND ((main.Type = 'ticket'))
    AND ((ObjectCustomFieldValues_1.Content LIKE '%NULL%')
      AND(main.Queue = '1')
      AND(main.Queue = '1'))
 
This returns a count of 0.
 
Whereas, when using a query put together by based on a post by Ruslan U.
Zakarov (posted to rt-users on 1/14/2005, titled "Re: [rt-users] Re:
Custom Field Searching broken again?"):
 
SELECT
  COUNT(DISTINCT main.id)
FROM
  ((Tickets main LEFT JOIN
  ObjectCustomFieldValues ObjectCustomFieldValues_1 ON ( main.id =
ObjectCustomFieldValues_1.ObjectId)))
WHERE
  ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND
  ((main.Type = 'ticket'))
  AND ((ObjectCustomFieldValues_1.Content IS NULL))
  AND main.Queue = '1'
 
This returns a count of 91.
 
I note that David Snyder (posted to rt-users on 1/14/2005 to the same
thread as Ruslan) indicates that an upgrade of mysql to 4.1 seemed to
fix his problem.
 
My problem still persists.
 
Thank you all for any attention you can give me on this...
 
- Richard
   
 
 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20050504/7cde2277/attachment.htm>