Custom Field Searching broken again?

I’ve just installed 3.4.0rc5 (previously was testing rc2). Is it just
me, or is custom field searching still not working?

David

David Snyder wrote:

I’ve just installed 3.4.0rc5 (previously was testing rc2). Is it
just me, or is custom field searching still not working?

David

I meant to include this from my apache error log. This was when trying
to search for tickets where a global custom field contains "problem."
Also, I’ve got SearchBuilder 1.19

[Thu Jan 13 16:19:51 2005] [error] [client 130.132.101.242] FastCGI:
server “/opt/rt3.4rc5/bin/mason_handler.fcgi” stderr: DBD::mysql::st
execute failed: You have an error in your SQL syntax near ‘ON (
ObjectCustomFieldValues_1.CustomField = CustomFields_2.id)) WHERE
((main.’ at line 1 at
/usr/local/perl-5.8.5/lib/site_perl/5.8.5/DBIx/SearchBuilder/Handle.pm
line 475., referer: http://fasit-rhel1.its.yale.edu/rt3.4/Search/Build.html
[Thu Jan 13 16:19:51 2005] [error] [client 130.132.101.242] FastCGI:
server “/opt/rt3.4rc5/bin/mason_handler.fcgi” stderr:
RT::Handle=HASH(0xad70a30) couldn’t execute the query ‘SELECT
COUNT(DISTINCT main.id) FROM ((Tickets main LEFT JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_1 ON ( main.id =
ObjectCustomFieldValues_1.ObjectId)) JOIN CustomFields CustomFields_2
ON ( ObjectCustomFieldValues_1.CustomField = CustomFields_2.id)) WHERE
((main.EffectiveId = main.id)) AND ((main.Status != ‘deleted’)) AND
((main.Type = ‘ticket’)) AND ((ObjectCustomFieldValues_1.Content LIKE
’%problem%’)) ’ at
/usr/local/perl-5.8.5/lib/site_perl/5.8.5/DBIx/SearchBuilder/Handle.pm
line 489., referer: http://fasit-rhel1.its.yale.edu/rt3.4/Search/Build.html
[Thu Jan 13 16:19:51 2005] [error] [client 130.132.101.242] FastCGI:
server “/opt/rt3.4rc5/bin/mason_handler.fcgi” stderr: DBD::mysql::st
execute failed: You have an error in your SQL syntax near ‘ON (
ObjectCustomFieldValues_1.CustomField = CustomFields_2.id)) WHERE
((main.’ at line 1 at
/usr/local/perl-5.8.5/lib/site_perl/5.8.5/DBIx/SearchBuilder/Handle.pm
line 475., referer: http://fasit-rhel1.its.yale.edu/rt3.4/Search/Build.html
[Thu Jan 13 16:19:51 2005] [error] [client 130.132.101.242] FastCGI:
server “/opt/rt3.4rc5/bin/mason_handler.fcgi” stderr:
RT::Handle=HASH(0xad70a30) couldn’t execute the query ‘SELECT
COUNT(DISTINCT main.id) FROM ((Tickets main LEFT JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_1 ON ( main.id =
ObjectCustomFieldValues_1.ObjectId)) JOIN CustomFields CustomFields_2
ON ( ObjectCustomFieldValues_1.CustomField = CustomFields_2.id)) WHERE
((main.EffectiveId = main.id)) AND ((main.Status != ‘deleted’)) AND
((main.Type = ‘ticket’)) AND ((ObjectCustomFieldValues_1.Content LIKE
’%problem%’)) ’ at
/usr/local/perl-5.8.5/lib/site_perl/5.8.5/DBIx/SearchBuilder/Handle.pm
line 489., referer: http://fasit-rhel1.its.yale.edu/rt3.4/Search/Build.html
[Thu Jan 13 16:19:51 2005] [error] [client 130.132.101.242] FastCGI:
server “/opt/rt3.4rc5/bin/mason_handler.fcgi” stderr: DBD::mysql::st
execute failed: You have an error in your SQL syntax near ‘ON (
ObjectCustomFieldValues_1.CustomField = CustomFields_2.id)) WHERE
((main.’ at line 1 at
/usr/local/perl-5.8.5/lib/site_perl/5.8.5/DBIx/SearchBuilder/Handle.pm
line 475., referer: http://fasit-rhel1.its.yale.edu/rt3.4/Search/Build.html
[Thu Jan 13 16:19:51 2005] [error] [client 130.132.101.242] FastCGI:
server “/opt/rt3.4rc5/bin/mason_handler.fcgi” stderr:
RT::Handle=HASH(0xad70a30) couldn’t execute the query ‘SELECT
COUNT(DISTINCT main.id) FROM ((Tickets main LEFT JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_1 ON ( main.id =
ObjectCustomFieldValues_1.ObjectId)) JOIN CustomFields CustomFields_2
ON ( ObjectCustomFieldValues_1.CustomField = CustomFields_2.id)) WHERE
((main.EffectiveId = main.id)) AND ((main.Status != ‘deleted’)) AND
((main.Type = ‘ticket’)) AND ((ObjectCustomFieldValues_1.Content LIKE
’%problem%’)) ’ at
/usr/local/perl-5.8.5/lib/site_perl/5.8.5/DBIx/SearchBuilder/Handle.pm
line 489., referer: http://fasit-rhel1.its.yale.edu/rt3.4/Search/Build.html
[Thu Jan 13 16:19:51 2005] [error] [client 130.132.101.242] FastCGI:
server “/opt/rt3.4rc5/bin/mason_handler.fcgi” stderr: DBD::mysql::st
execute failed: You have an error in your SQL syntax near ‘ON (
ObjectCustomFieldValues_1.CustomField = CustomFields_2.id)) WHERE
((main.’ at line 1 at
/usr/local/perl-5.8.5/lib/site_perl/5.8.5/DBIx/SearchBuilder/Handle.pm
line 475., referer: http://fasit-rhel1.its.yale.edu/rt3.4/Search/Build.html
[Thu Jan 13 16:19:51 2005] [error] [client 130.132.101.242] FastCGI:
server “/opt/rt3.4rc5/bin/mason_handler.fcgi” stderr:
RT::Handle=HASH(0xad70a30) couldn’t execute the query ‘SELECT DISTINCT
main.* FROM ((Tickets main LEFT JOIN ObjectCustomFieldValues
ObjectCustomFieldValues_1 ON ( main.id =
ObjectCustomFieldValues_1.ObjectId)) JOIN CustomFields CustomFields_2
ON ( ObjectCustomFieldValues_1.CustomField = CustomFields_2.id)) WHERE
((main.EffectiveId = main.id)) AND ((main.Status != ‘deleted’)) AND
((main.Type = ‘ticket’)) AND ((ObjectCustomFieldValues_1.Content LIKE
’%problem%’)) ORDER BY main.id ASC’ at
/usr/local/perl-5.8.5/lib/site_perl/5.8.5/DBIx/SearchBuilder/Handle.pm
line 489., referer: http://fasit-rhel1.its.yale.edu/rt3.4/Search/Build.html

David Snyder wrote:

I’ve just installed 3.4.0rc5 (previously was testing rc2). Is it
just me, or is custom field searching still not working?

David

I meant to include this from my apache error log. This was when trying
to search for tickets where a global custom field contains "problem."
Also, I’ve got SearchBuilder 1.19

What version of mysql are you running?
If you run this command from the mysql commandline, what do you get?

SELECT COUNT(DISTINCT main.id) FROM ((Tickets main LEFT JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_1 ON ( main.id =
ObjectCustomFieldValues_1.ObjectId)) JOIN CustomFields CustomFields_2
ON ( ObjectCustomFieldValues_1.CustomField = CustomFields_2.id)) WHERE
((main.EffectiveId = main.id)) AND ((main.Status != ‘deleted’)) AND
((main.Type = ‘ticket’)) AND ((ObjectCustomFieldValues_1.Content LIKE
’%problem%’))

David Snyder wrote:

I’ve just installed 3.4.0rc5 (previously was testing rc2). Is it
just me, or is custom field searching still not working?

David

I meant to include this from my apache error log. This was when
trying
to search for tickets where a global custom field contains "problem."
Also, I’ve got SearchBuilder 1.19

What version of mysql are you running?
If you run this command from the mysql commandline, what do you get?

SELECT COUNT(DISTINCT main.id) FROM ((Tickets main LEFT JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_1 ON ( main.id =
ObjectCustomFieldValues_1.ObjectId)) JOIN CustomFields CustomFields_2
ON ( ObjectCustomFieldValues_1.CustomField = CustomFields_2.id))
WHERE
((main.EffectiveId = main.id)) AND ((main.Status != ‘deleted’)) AND
((main.Type = ‘ticket’)) AND ((ObjectCustomFieldValues_1.Content LIKE
’%problem%’))

oops–it seems I’m still running the stock redhat mysql-3.23.58-2.3 on
this box. I’ll upgrade to something that meets the stated system
requirements and get back to you. (FWIW that query fails from
commandline as well with the same error).

Sorry,

David

David Snyder wrote:

I’ve just installed 3.4.0rc5 (previously was testing rc2). Is it
just me, or is custom field searching still not working?

David

I meant to include this from my apache error log. This was when
trying
to search for tickets where a global custom field contains "problem."
Also, I’ve got SearchBuilder 1.19

What version of mysql are you running?
If you run this command from the mysql commandline, what do you get?

SELECT COUNT(DISTINCT main.id) FROM ((Tickets main LEFT JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_1 ON ( main.id =
ObjectCustomFieldValues_1.ObjectId)) JOIN CustomFields CustomFields_2
ON ( ObjectCustomFieldValues_1.CustomField = CustomFields_2.id))
WHERE
((main.EffectiveId = main.id)) AND ((main.Status != ‘deleted’)) AND
((main.Type = ‘ticket’)) AND ((ObjectCustomFieldValues_1.Content LIKE
’%problem%’))

I think I mentioned a while back that since upgrading (worked fine
before) to 3.4.0 and continuing with rc5, when I do a searching on
custom fields where the value IS (no value), I get 0 records returned.
I’m using PostgreSQL 7.4.6 on FC2/apache2/mod_perl (latest distro
versions), DBIx::SearchBuilder 1.19.

If the above represents the query that is run, did the second join
change from a full join?

This query returns 641 records:
SELECT COUNT(DISTINCT main.id) FROM ((Tickets main LEFT JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_1 ON ( main.id =
ObjectCustomFieldValues_1.ObjectId)) FULL JOIN CustomFields
CustomFields_2
ON ( ObjectCustomFieldValues_1.CustomField = CustomFields_2.id)) WHERE
((main.EffectiveId = main.id)) AND ((main.Status != ‘deleted’)) AND
((main.Type = ‘ticket’))
AND ((ObjectCustomFieldValues_1.Content IS NULL))

Thanks!

smime.p7s (3.63 KB)

IMHO all joins should be LEFT. MySQL doesn’t support FULL JOIN.

SELECT COUNT(DISTINCT main.id) FROM ((Tickets main LEFT JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_1 ON ( main.id =
ObjectCustomFieldValues_1.ObjectId)) LEFT JOIN CustomFields
CustomFields_2
ON ( ObjectCustomFieldValues_1.CustomField = CustomFields_2.id)) WHERE
((main.EffectiveId = main.id)) AND ((main.Status != ‘deleted’)) AND
((main.Type = ‘ticket’))
AND ((ObjectCustomFieldValues_1.Content IS NULL))

May be it can be automated in DBIx::SB or at least checked.
If we try NORMAL join into table1 that has restriction(in WHERE) ‘IS
NULL’ and that table1 is itself LEFT or RIGHT joined into any table2,
then it’s wrong wish.
Next template:
… LEFT JOIN Table1 ON(…) JOIN Foo ON(Table1.Foo …) …
Sorry… Looks like it’s more complex than I thought.

But exactly this query(with LEFT JOINs) could be stripped to:
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))

Micah Cooper wrote:> On Jan 13, 2005, at 4:57 PM, David Snyder wrote:

On Thu, Jan 13, 2005 at 04:25:41PM -0500, David Snyder wrote:

David Snyder wrote:

I’ve just installed 3.4.0rc5 (previously was testing rc2). Is it
just me, or is custom field searching still not working?

David

I meant to include this from my apache error log. This was when trying
to search for tickets where a global custom field contains "problem."
Also, I’ve got SearchBuilder 1.19

What version of mysql are you running?
If you run this command from the mysql commandline, what do you get?

SELECT COUNT(DISTINCT main.id) FROM ((Tickets main LEFT JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_1 ON ( main.id =
ObjectCustomFieldValues_1.ObjectId)) JOIN CustomFields
CustomFields_2
ON ( ObjectCustomFieldValues_1.CustomField = CustomFields_2.id))
WHERE
((main.EffectiveId = main.id)) AND ((main.Status != ‘deleted’)) AND
((main.Type = ‘ticket’)) AND ((ObjectCustomFieldValues_1.Content
LIKE
’%problem%’))

I think I mentioned a while back that since upgrading (worked fine
before) to 3.4.0 and continuing with rc5, when I do a searching on
custom fields where the value IS (no value), I get 0 records returned.
I’m using PostgreSQL 7.4.6 on FC2/apache2/mod_perl (latest distro
versions), DBIx::SearchBuilder 1.19.

If the above represents the query that is run, did the second join
change from a full join?

This query returns 641 records:
SELECT COUNT(DISTINCT main.id) FROM ((Tickets main LEFT JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_1 ON ( main.id =
ObjectCustomFieldValues_1.ObjectId)) FULL JOIN CustomFields
CustomFields_2
ON ( ObjectCustomFieldValues_1.CustomField = CustomFields_2.id)) WHERE
((main.EffectiveId = main.id)) AND ((main.Status != ‘deleted’)) AND
((main.Type = ‘ticket’))
AND ((ObjectCustomFieldValues_1.Content IS NULL))

Thanks!



http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users

Be sure to check out the RT wiki at http://wiki.bestpractical.com

David Snyder wrote:

I’ve just installed 3.4.0rc5 (previously was testing rc2). Is it
just me, or is custom field searching still not working?

David

I meant to include this from my apache error log. This was when
trying
to search for tickets where a global custom field contains "problem."
Also, I’ve got SearchBuilder 1.19

What version of mysql are you running?
If you run this command from the mysql commandline, what do you get?

SELECT COUNT(DISTINCT main.id) FROM ((Tickets main LEFT JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_1 ON ( main.id =
ObjectCustomFieldValues_1.ObjectId)) JOIN CustomFields CustomFields_2
ON ( ObjectCustomFieldValues_1.CustomField = CustomFields_2.id))
WHERE
((main.EffectiveId = main.id)) AND ((main.Status != ‘deleted’)) AND
((main.Type = ‘ticket’)) AND ((ObjectCustomFieldValues_1.Content LIKE
’%problem%’))

oops–it seems I’m still running the stock redhat mysql-3.23.58-2.3 on
this box. I’ll upgrade to something that meets the stated system
requirements and get back to you. (FWIW that query fails from
commandline as well with the same error).

Upgraded to mysql 4.1.9 and now it’s working with no problems.

Thanks,

David