Contact search fails for multiple select custom fields

Originally posted on Crm-dev mailing list Fri, 16 Jun 2006 13:27:30 +0100, filed here at the request of Donald Lobo.

I'm finding that contact search fails to return any matches when searching on multiple select custom fields.

I created custom field "Test Multi" as Alphanumeric, Multi-Select, searchable and set up options labelled One, Two, Three with values 1, 2, 3 and weights 1, 2, 3. I edited an existing contact and selected options One and Three for "Test Multi".

When I try an advanced contacts search with One selected for "Test Multi", it fails to find the record and I get the message:
"No matches found for:

• Test Multi like - Array".
  Same if I select both One and Three on the search form. A similar search succeeds for the field "Test Checkbox" that is set up
  as Alphanumeric, CheckBox with the same options. Nothing appears in the Apache error log.

Reproduced on http://demo.civicrm.org/

• I have added the "Test Multi" field to the Voter Info group and selected One and Three for the contact Mrs Andrew R Adams Jr (cid=38).

Here's my setup:
Mac OS X 10.4.6
civicrm-drupal-php4-v1.4-rev5609M
Drupal 4.7.2
PHP 4.4.1
This program makes use of the Zend Scripting Language Engine:
Zend Engine v1.3.0, Copyright (c) 1998-2004 Zend Technologies
MySQL 4.1.14
Apache 1.3.33

mysql> select id, custom_group_id, name, data_type, html_type from
civicrm_custom_field where id = 23 or id = 24;
-----------------------------------------------------

-----------------------------------------------------

-----------------------------------------------------

mysql> select * from civicrm_custom_value where custom_field_id = 23 or
custom_field_id = 24\G

• • • • • • • • • • • • • • • • • • • • • • • • • • • 1. row ***************************
                                                      id: 11
                                                      custom_field_id: 23
                                                      entity_table: civicrm_contact
                                                      entity_id: 1
                                                      int_data: NULL
                                                      float_data: NULL
                                                      decimal_data: NULL
                                                      char_data: 13
                                                      date_data: NULL
                                                      memo_data: NULL
                                                    • 2. row ***************************
                                                      id: 12
                                                      custom_field_id: 24
                                                      entity_table: civicrm_contact
                                                      entity_id: 1
                                                      int_data: NULL
                                                      float_data: NULL
                                                      decimal_data: NULL
                                                      char_data: 13
                                                      date_data: NULL
                                                      memo_data: NULL
                                                      2 rows in set (0.00 sec)

I noticed that char_data appears to use, as a delimiter between
multiple values, a character that displays as zero-width. If I get
mysql to display char_data in tabular format, the right-hand border of
the table is shifted in by the number of delimiter characters. You may
be able to see this if viewing the table below in a fixed-width font:

mysql> select id, custom_field_id, char_data from civicrm_custom_value
where custom_field_id = 23 or custom_field_id = 24;
----------------------------

----------------------------

----------------------------

However this applies to both the working and non-working fields, so may
be irrelevant and normal.