Searches by custom fields

RT Users
1

Hello.

I’ve commited fixes several fixes into 3.5 branch that should fix
issues with searches by CFs.
Please test it with your test cases and report back.

Best regards, Ruslan.

2

Hello.

I’ve commited fixes several fixes into 3.5 branch that should fix
issues with searches by CFs.
Please test it with your test cases and report back.

I’ve downloaded the current release of 3.5-TESTING (5204), built it and
copied the database from my other RT instance to it. I still get zero
results. I’ve verified that the custom fields are still populated on the
test instance. Is there any information that I can provide that would
help?

Is anyone able to successfully search custom fields in 3.6, or is it
likely that Rangarajan and I have just mis-configured something?

Thanks for the help.

Joshua Colson jcolson@voidgate.org

3

Joshua,
I am somewhat tied up with other stuff, but will give it a shot as soon
as I get a chance.

Will it be possible for you to put a debug statement in
DBIx::SearchBuilder as part of DoQuery that prints out the QueryString.

Details in this thread:
http://lists.bestpractical.com/pipermail/rt-users/2006-May/039168.html
http://lists.bestpractical.com/pipermail/rt-users/2006-May/039185.html

Thanks

Joshua Colson wrote:>On Thu, 2006-05-11 at 11:53 +0400, Ruslan Zakirov wrote:

Hello.

I’ve commited fixes several fixes into 3.5 branch that should fix
issues with searches by CFs.
Please test it with your test cases and report back.

I’ve downloaded the current release of 3.5-TESTING (5204), built it and
copied the database from my other RT instance to it. I still get zero
results. I’ve verified that the custom fields are still populated on the
test instance. Is there any information that I can provide that would
help?

Is anyone able to successfully search custom fields in 3.6, or is it
likely that Rangarajan and I have just mis-configured something?

Thanks for the help.

4

Will it be possible for you to put a debug statement in
DBIx::SearchBuilder as part of DoQuery that prints out the
QueryString.

Details in this thread:
[rt-users] scrip and transaction
[rt-users] Lack of trailing slash causing porblems in IE

At least in the 3.6 version, RT now supports the following in the
RT_SiteConfig to log SQL queries:

Set($StatementLog, ‘’);

… for example:

Set($StatementLog, ‘debug’);

will log all SQL statements when logging is enabled.

I’ll turn on logging and paste them.

Joshua Colson jcolson@voidgate.org

5

At least in the 3.6 version, RT now supports the following in the
RT_SiteConfig to log SQL queries:

Set($StatementLog, ‘’);

… for example:

Set($StatementLog, ‘debug’);

will log all SQL statements when logging is enabled.

… this should say “when debug logging is enabled.”

Joshua Colson jcolson@voidgate.org

6

I reproduced problem when people search by one field with several
restrictions, for example “CF.{cf1} = ‘foo’ OR CF.{cf1} = ‘bar’”.
Also, change should fix issue when RT generate wrong SQL query with
reference to ‘main.name’ column that doesn’t exist.

If you see other issues feel free to send links to the archives.On 5/11/06, Joshua Colson jcolson@voidgate.org wrote:

On Thu, 2006-05-11 at 11:53 +0400, Ruslan Zakirov wrote:

Hello.

I’ve commited fixes several fixes into 3.5 branch that should fix
issues with searches by CFs.
Please test it with your test cases and report back.

I’ve downloaded the current release of 3.5-TESTING (5204), built it and
copied the database from my other RT instance to it. I still get zero
results. I’ve verified that the custom fields are still populated on the
test instance. Is there any information that I can provide that would
help?

Is anyone able to successfully search custom fields in 3.6, or is it
likely that Rangarajan and I have just mis-configured something?

Thanks for the help.

–
Joshua Colson jcolson@voidgate.org

Best regards, Ruslan.

7

I reproduced problem when people search by one field with several
restrictions, for example “CF.{cf1} = ‘foo’ OR CF.{cf1} = ‘bar’”.
Also, change should fix issue when RT generate wrong SQL query with
reference to ‘main.name’ column that doesn’t exist.

If you see other issues feel free to send links to the archives.

Okay, after some digging, I found this SQL query (line numbers added):

1 SELECT DISTINCT main.*
2 FROM Tickets main
3 LEFT JOIN ObjectCustomFields ObjectCustomFields_1
4 ON ((ObjectCustomFields_1.ObjectId = ‘0’))
5 AND( ObjectCustomFields_1.ObjectId = main.Queue)
6 LEFT JOIN CustomFields CustomFields_2
7 ON ( CustomFields_2.id = ObjectCustomFields_1.CustomField)
8 LEFT JOIN ObjectCustomFieldValues ObjectCustomFieldValues_3
9 ON ((ObjectCustomFieldValues_3.ObjectId = main.id))
10 AND(ObjectCustomFieldValues_3.CustomField = CustomFields_2.id)
11 AND( (ObjectCustomFieldValues_3.Disabled = ‘0’))
12 AND( (ObjectCustomFieldValues_3.ObjectType = ‘RT::Ticket’))
13 WHERE ((CustomFields_2.Name = ‘Registrar-Lock’))
14 AND ((main.EffectiveId = main.id))
15 AND ((main.Status != ‘deleted’))
16 AND ((main.Type = ‘ticket’))
17 AND ((main.Queue = ‘3’)
18 AND ( (ObjectCustomFieldValues_3.Content = ‘Yes’) ) )
19 ORDER BY main.id ASC
20 LIMIT 50;

I ran this against the DB and got zero results. So after closer
inspection, it seems that this could only ever be true if the custom
field is Global. The reason for this lies in line 5 of the statement.
The aggregator should be ‘OR’, not ‘AND’. If I change that, I get an
accurate listing of tickets in the result set. However, I tried digging
at the code to find where it is being set and haven’t found it, yet. I
figured I would send this to the list in case somebody knows off the the
top of their head.

Thanks.

Joshua Colson jcolson@voidgate.org

8

DBIx::SearchBuilder 1.39 and greater should fix this issue.
Don’t forget to run make testdeps on updates from SVN too :)On 5/12/06, Joshua Colson jcolson@voidgate.org wrote:

On Thu, 2006-05-11 at 21:55 +0400, Ruslan Zakirov wrote:

I reproduced problem when people search by one field with several
restrictions, for example “CF.{cf1} = ‘foo’ OR CF.{cf1} = ‘bar’”.
Also, change should fix issue when RT generate wrong SQL query with
reference to ‘main.name’ column that doesn’t exist.

If you see other issues feel free to send links to the archives.

Okay, after some digging, I found this SQL query (line numbers added):

1 SELECT DISTINCT main.*
2 FROM Tickets main
3 LEFT JOIN ObjectCustomFields ObjectCustomFields_1
4 ON ((ObjectCustomFields_1.ObjectId = ‘0’))
5 AND( ObjectCustomFields_1.ObjectId = main.Queue)
6 LEFT JOIN CustomFields CustomFields_2
7 ON ( CustomFields_2.id = ObjectCustomFields_1.CustomField)
8 LEFT JOIN ObjectCustomFieldValues ObjectCustomFieldValues_3
9 ON ((ObjectCustomFieldValues_3.ObjectId = main.id))
10 AND(ObjectCustomFieldValues_3.CustomField = CustomFields_2.id)
11 AND( (ObjectCustomFieldValues_3.Disabled = ‘0’))
12 AND( (ObjectCustomFieldValues_3.ObjectType = ‘RT::Ticket’))
13 WHERE ((CustomFields_2.Name = ‘Registrar-Lock’))
14 AND ((main.EffectiveId = main.id))
15 AND ((main.Status != ‘deleted’))
16 AND ((main.Type = ‘ticket’))
17 AND ((main.Queue = ‘3’)
18 AND ( (ObjectCustomFieldValues_3.Content = ‘Yes’) ) )
19 ORDER BY main.id ASC
20 LIMIT 50;

I ran this against the DB and got zero results. So after closer
inspection, it seems that this could only ever be true if the custom
field is Global. The reason for this lies in line 5 of the statement.
The aggregator should be ‘OR’, not ‘AND’. If I change that, I get an
accurate listing of tickets in the result set. However, I tried digging
at the code to find where it is being set and haven’t found it, yet. I
figured I would send this to the list in case somebody knows off the the
top of their head.

Thanks.

–
Joshua Colson jcolson@voidgate.org

Best regards, Ruslan.

9

I reproduced problem when people search by one field with several
restrictions, for example “CF.{cf1} = ‘foo’ OR CF.{cf1} = ‘bar’”.
Also, change should fix issue when RT generate wrong SQL query with
reference to ‘main.name’ column that doesn’t exist.

If you see other issues feel free to send links to the archives.

Okay, after some digging, I found this SQL query (line numbers added):

1 SELECT DISTINCT main.*
2 FROM Tickets main
3 LEFT JOIN ObjectCustomFields ObjectCustomFields_1
4 ON ((ObjectCustomFields_1.ObjectId = ‘0’))
5 AND( ObjectCustomFields_1.ObjectId = main.Queue)
6 LEFT JOIN CustomFields CustomFields_2
7 ON ( CustomFields_2.id = ObjectCustomFields_1.CustomField)
8 LEFT JOIN ObjectCustomFieldValues ObjectCustomFieldValues_3
9 ON ((ObjectCustomFieldValues_3.ObjectId = main.id))
10 AND(ObjectCustomFieldValues_3.CustomField = CustomFields_2.id)
11 AND( (ObjectCustomFieldValues_3.Disabled = ‘0’))
12 AND( (ObjectCustomFieldValues_3.ObjectType = ‘RT::Ticket’))
13 WHERE ((CustomFields_2.Name = ‘Registrar-Lock’))
14 AND ((main.EffectiveId = main.id))
15 AND ((main.Status != ‘deleted’))
16 AND ((main.Type = ‘ticket’))
17 AND ((main.Queue = ‘3’)
18 AND ( (ObjectCustomFieldValues_3.Content = ‘Yes’) ) )
19 ORDER BY main.id ASC
20 LIMIT 50;

I ran this against the DB and got zero results. So after closer
inspection, it seems that this could only ever be true if the custom
field is Global. The reason for this lies in line 5 of the statement.
The aggregator should be ‘OR’, not ‘AND’. If I change that, I get an
accurate listing of tickets in the result set. However, I tried digging
at the code to find where it is being set and haven’t found it, yet. I
figured I would send this to the list in case somebody knows off the the
top of their head.

On further inspection, it seems that this query could never return
results, even with Global Custom Fields.

Joshua Colson jcolson@voidgate.org

10

DBIx::SearchBuilder 1.39 and greater should fix this issue.
Don’t forget to run make testdeps on updates from SVN too :slight_smile:

I’ve upgraded DBIx::SearchBuilder to 1.43 from CPAN and the problem
remains.

Joshua Colson jcolson@voidgate.org

11

DBIx::SearchBuilder 1.39 and greater should fix this issue.
Don’t forget to run make testdeps on updates from SVN too :slight_smile:

I’m running DBIx::SearchBuilder 1.40. I did run ‘make testdeps’ which
reported ‘Everything was found’.

Joshua Colson jcolson@voidgate.org

12

Hello.

I’ve commited fixes several fixes into 3.5 branch that should fix
issues with searches by CFs.
Please test it with your test cases and report back.

I’ve been trying (as time permits) to isolate this custom field search
issue. As I stated before, I’ve tried the latest SVN and RC2 with no
luck. However, I migrated my 3.6.0pre1 instance to use
Apache2/Mod_Perl2/MySQL4.1 and now the problem is gone. I did a
recursive diff on the respective installations and they show that there
are no differences in the code (with the exception of ‘use lib’ paths,
etc) between the two installations. This leads me to believe that there
is a problem somewhere in SearchBuilder in the abstraction differences
in database types. Rangarajan’s problem is on an Oracle system, my
problem was with SQLite, and it seems to work flawlessly on MySQL. If I
get the chance, I’m going to try to dig deeper into the problem. I just
wanted to put the info that I had out so that others could think about
it also.

Thanks.

Joshua Colson joshua.colson@ination.com
iNation, LLC