CiviCRM Community Forums (archive)

I have tried to use a few examples of custom searches: http://wiki.civicrm.org/confluence/display/CRMDOC/Community+Contributed+Custom+Searches such as:
http://wiki.civicrm.org/confluence/display/CRMDOC/Search+on+Gender+and+Age
http://wiki.civicrm.org/confluence/display/CRMDOC/Upcoming+Birthday+and+Anniversary+searches
and if I create a smartgroup based on the example, the query fails.  It does not fail for the built in custom queries.
I believe the problem is related to: https://issues.civicrm.org/jira/browse/CRM-9190 and https://issues.civicrm.org/jira/browse/CRM-11021
I was not able to see the code changes for the latter.

To reproduce:
Install a custom query referenced above.
select all results
create smartgroup.
An error is generated, the smartgroup is created, but will not function properly.
Anyone seen this or fixed it?
Backtrace:
Jun 17 20:53:40  [info] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -2
    [message] => DB Error: syntax error
    [mode] => 16
    [debug_info] => CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache1140 (SELECT 22 as group_id,
(contact.id) as contact_id,
contact.sort_name as sort_name,
ca.street_address as street_address,
ca.postal_code as postal_code,
ca.city as city,
cov.label as gender,
DATE_FORMAT( FROM_DAYS( TO_DAYS( NOW( ) ) - TO_DAYS( contact.birth_date ) ) , '%Y' ) +0 as age

FROM
civicrm_contact contact
LEFT JOIN civicrm_address ca ON (ca.contact_id = contact.id AND ca.is_primary = 1)
LEFT JOIN civicrm_option_value cov ON (cov.value = contact.gender_id AND cov.option_group_id =3)

WHERE  contact.contact_type = 'Individual' AND contact.birth_date <= '2013-06-17' AND contact.birth_date >= '1914-06-17'

ORDER BY sort_name desc AND contact_a.id NOT IN (
                              SELECT contact_id FROM civicrm_group_contact
                              WHERE civicrm_group_contact.status = 'Removed'
                              AND   civicrm_group_contact.group_id = 22 ) ); [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near 'AND contact_a.id NOT IN (
                              SELECT contact_id FROM c' at line 17]
    [type] => DB_Error
    [user_info] => CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache1140 (SELECT 22 as group_id,
(contact.id) as contact_id,
contact.sort_name as sort_name,
ca.street_address as street_address,
ca.postal_code as postal_code,
ca.city as city,
cov.label as gender,
DATE_FORMAT( FROM_DAYS( TO_DAYS( NOW( ) ) - TO_DAYS( contact.birth_date ) ) , '%Y' ) +0 as age

FROM
civicrm_contact contact
LEFT JOIN civicrm_address ca ON (ca.contact_id = contact.id AND ca.is_primary = 1)
LEFT JOIN civicrm_option_value cov ON (cov.value = contact.gender_id AND cov.option_group_id =3)

WHERE  contact.contact_type = 'Individual' AND contact.birth_date <= '2013-06-17' AND contact.birth_date >= '1914-06-17'

ORDER BY sort_name desc AND contact_a.id NOT IN (
                              SELECT contact_id FROM civicrm_group_contact
                              WHERE civicrm_group_contact.status = 'Removed'
                              AND   civicrm_group_contact.group_id = 22 ) ); [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND contact_a.id NOT IN (
                              SELECT contact_id FROM c' at line 17]
    [to_string] => [db_error: message="DB Error: syntax error" code=-2 mode=callback callback=CRM_Core_Error::handle prefix="" info="CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache1140 (SELECT 22 as group_id,
(contact.id) as contact_id,
contact.sort_name as sort_name,
ca.street_address as street_address,
ca.postal_code as postal_code,
ca.city as city,
cov.label as gender,
DATE_FORMAT( FROM_DAYS( TO_DAYS( NOW( ) ) - TO_DAYS( contact.birth_date ) ) , '%Y' ) +0 as age

FROM
civicrm_contact contact
LEFT JOIN civicrm_address ca ON (ca.contact_id = contact.id AND ca.is_primary = 1)
LEFT JOIN civicrm_option_value cov ON (cov.value = contact.gender_id AND cov.option_group_id =3)

WHERE  contact.contact_type = 'Individual' AND contact.birth_date <= '2013-06-17' AND contact.birth_date >= '1914-06-17'

ORDER BY sort_name desc AND contact_a.id NOT IN (
                              SELECT contact_id FROM civicrm_group_contact
                              WHERE civicrm_group_contact.status = 'Removed'
                              AND   civicrm_group_contact.group_id = 22 ) ); [nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND contact_a.id NOT IN (
                              SELECT contact_id FROM c' at line 17]"]
)


Jun 17 20:53:40  [info] $backTrace = #0 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/CRM/Core/Error.php(197): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/packages/PEAR.php(931): call_user_func((Array:2), Object(DB_Error))
#3 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/packages/DB.php(969): PEAR_Error->PEAR_Error("DB Error: syntax error", -2, 16, (Array:2), "CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache1140 (SELECT 22 as gro...")
#4 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/packages/PEAR.php(564): DB_Error->DB_Error(-2, 16, (Array:2), "CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache1140 (SELECT 22 as gro...")
#5 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -2, NULL, NULL, "CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache1140 (SELECT 22 as gro...", "DB_Error", TRUE)
#6 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/packages/DB/mysql.php(898): DB_common->raiseError(-2, NULL, NULL, NULL, "1064 ** You have an error in your SQL syntax; check the manual that correspon...")
#7 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/packages/DB/mysql.php(327): DB_mysql->mysqlRaiseError()
#8 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/packages/DB/common.php(1216): DB_mysql->simpleQuery("CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache1140 (SELECT 22 as gro...")
#9 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/packages/DB/DataObject.php(2421): DB_common->query("CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache1140 (SELECT 22 as gro...")
#10 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/packages/DB/DataObject.php(1613): DB_DataObject->_query("CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache1140 (SELECT 22 as gro...")
#11 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/CRM/Core/DAO.php(160): DB_DataObject->query("CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache1140 (SELECT 22 as gro...")
#12 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/CRM/Core/DAO.php(908): CRM_Core_DAO->query("CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache1140 (SELECT 22 as gro...", TRUE)
#13 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/CRM/Contact/BAO/GroupContactCache.php(491): CRM_Core_DAO::executeQuery("CREATE TEMPORARY TABLE civicrm_temp_group_contact_cache1140 (SELECT 22 as gro...")
#14 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/CRM/Contact/BAO/Query.php(2774): CRM_Contact_BAO_GroupContactCache::load(Object(CRM_Core_DAO))
#15 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/CRM/Contact/BAO/Query.php(2741): CRM_Contact_BAO_Query->addGroupContactCache((Array:1))
#16 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/CRM/Contact/BAO/Query.php(2707): CRM_Contact_BAO_Query->savedSearch((Array:5))
#17 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/CRM/Contact/BAO/Query.php(1545): CRM_Contact_BAO_Query->group((Array:5))
#18 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/CRM/Contact/BAO/Query.php(1771): CRM_Contact_BAO_Query->whereClauseSingle((Array:5))
#19 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/CRM/Contact/BAO/Query.php(505): CRM_Contact_BAO_Query->whereClause()
#20 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/CRM/Contact/BAO/Query.php(466): CRM_Contact_BAO_Query->initialize()
#21 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/CRM/Contact/BAO/Query.php(2268): CRM_Contact_BAO_Query->__construct((Array:1), NULL, NULL, FALSE, FALSE)
#22 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/CRM/Contact/BAO/Group.php(504): CRM_Contact_BAO_Query::getWhereClause((Array:1), NULL, (Array:0), (Array:0))
#23 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/CRM/Contact/BAO/Group.php(410): CRM_Contact_BAO_Group->buildClause()
#24 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/CRM/Contact/Form/Task/SaveSearch.php(215): CRM_Contact_BAO_Group::create((Array:8))
#25 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/CRM/Core/Form.php(261): CRM_Contact_Form_Task_SaveSearch->postProcess()
#26 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/CRM/Core/StateMachine.php(160): CRM_Core_Form->mainProcess()
#27 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/CRM/Core/QuickForm/Action/Next.php(60): CRM_Core_StateMachine->perform(Object(CRM_Contact_Form_Task_SaveSearch), "next", "Next")
#28 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Next->perform(Object(CRM_Contact_Form_Task_SaveSearch), "next")
#29 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Contact_Form_Task_SaveSearch), "next")
#30 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/CRM/Core/Controller.php(339): HTML_QuickForm_Page->handle("next")
#31 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/CRM/Core/Invoke.php(323): CRM_Core_Controller->run((Array:4), (Array:0))
#32 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/CRM/Core/Invoke.php(72): CRM_Core_Invoke::runItem((Array:12))
#33 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/CRM/Core/Invoke.php(52): CRM_Core_Invoke::_invoke((Array:4))
#34 /home/xxxxx/public_html/test/profiles/civicrm_starterkit/modules/civicrm/drupal/civicrm.module(458): CRM_Core_Invoke::invoke((Array:4))
#35 [internal function](): civicrm_invoke("contact", "search", "custom")
#36 /home/xxxxx/public_html/test/includes/menu.inc(517): call_user_func_array("civicrm_invoke", (Array:3))
#37 /home/xxxxx/public_html/test/index.php(21): menu_execute_active_handler()
#38 {main}