Search Builder Query Errors - Undefined index: Home-email in restWhere()

[sonicthoughts]

Search builder fails with various queries like email is not Null.
Spent quite a while trying to understand the root cause.  Have verified the schema and settings.  Tried disabling word replace.  Issue seems to be related to location for email.  message log gives php error: |Notice: Undefined index: Home-email in restWhere() (line 2088 of /home/xxx/public_html/drupal/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php)
tName is not parsed correctly.  Perhaps it needs to be the location_type table??? should the name be email-1?
The offending line is:

Code: [Select]

$this->_where[$grouping][] = self::buildClause("LOWER($where)", $op, $value);
          $this->_whereTables[$tName] = $this->_tables[$tName];
          $this->_qill[$grouping][] = "$field[title] $op '$value'";
I printed out :
print_r($where);`Home-email`.email
print_r($name);email-1
print_r($tName);Home-email
This may be an edge case in the query.php code.  I'm not able to debug further nor want to make a bug until I get some feedback.  Thanks.

[Kurund Jalmi]

What the version of your CiviCRM install? Can you please try to replicate this on http://drupal.sandbox.civicrm.org

Kurund

[sonicthoughts]

4.4.5 - I am not able to replicate on the sandbox.  I have upgraded the db many times but the data structure is identical to a new install and I have checked integrity.  The only other error that I cannot account for is (I mention only as it could be related.  ) and I just have one domain:
"Warning: Invalid argument supplied for foreach() in civicrm_api3_domain_get() (line 59 of /home/jicny/public_html/drupal/sites/all/modules/civicrm/api/v3/Domain.php"
_civicrm_api3_dao_set_filter($bao, $params, true, 'domain');
  $domains = _civicrm_api3_dao_to_array($bao, $params, true,'domain');
  foreach ($domains as $domain) {

Please suggest some additional debug steps if possible.  thx.

[Kurund Jalmi]

"Warning: Invalid argument supplied for foreach() in civicrm_api3_domain_get() (line 59 of /home/jicny/public_html/drupal/sites/all/modules/civicrm/api/v3/Domain.php"
_civicrm_api3_dao_set_filter($bao, $params, true, 'domain');
  $domains = _civicrm_api3_dao_to_array($bao, $params, true,'domain');
  foreach ($domains as $domain) {

When do you get this error? Can you enable debugging and backtrace to check which function is calling this.

Kurund

[sonicthoughts]

ok, sorry to mix 2 issues - I'm not sure if they are related. 
I did not see a civicrm backtrace for the Domain.php - it is a php warning and shows up in the drupal log and system log.  I'm running watchdog_debug which does give more stack info:

Code: [Select]

debug_backtrace()
Id Function Location
Id Function Location
#31 eval ( ) /sites/all/modules/watchdog_debug/watchdog_debug.module : 108
#30 watchdog_debug_watchdog ( Array )
#29 call_user_func_array ( Array ) /includes/module.inc : 866
#28 module_invoke ( Array ) /includes/bootstrap.inc : 1735
#27 watchdog ( Array ) /sites/all/modules/devel/devel.module : 750
#26 backtrace_error_handler ( Array ) /sites/all/modules/civicrm/api/v3/Domain.php : 59
#25 civicrm_api3_domain_get ( Array ) /sites/all/modules/civicrm/api/api.php : 87
#24 civicrm_api ( Array ) /sites/all/modules/civicrm/api/v3/Generic.php : 138
#23 civicrm_api3_generic_getcount ( Array ) /sites/all/modules/civicrm/api/api.php : 82
#22 civicrm_api ( Array ) /sites/default/files/civicrm/extensions/eu.tttp.setting-1.2/setting.php : 43
#21 setting_civicrm_navigationMenu ( Array ) /sites/all/modules/civicrm/CRM/Utils/Hook.php : 148
#20 runHooks ( Array ) /sites/all/modules/civicrm/CRM/Utils/Hook/Drupal.php : 67
#19 invoke ( Array ) /sites/all/modules/civicrm/CRM/Utils/Hook.php : 809
#18 navigationMenu ( Array ) /sites/all/modules/civicrm/CRM/Core/BAO/Navigation.php : 325
#17 buildNavigation ( ) /sites/all/modules/civicrm/CRM/Core/BAO/Navigation.php : 596
#16 createNavigation ( Array ) /sites/all/modules/civicrm/CRM/Core/Smarty/plugins/function.crmNavigationMenu.php : 55
#15 smarty_function_crmNavigationMenu ( Array ) /sites/default/files/civicrm/templates_c/en_US/%%06/069/0693F89E%%drupal.tpl.php : 24
#14 include ( Array ) /sites/all/modules/civicrm/packages/Smarty/Smarty.class.php : 1264
#13 fetch ( Array ) /sites/all/modules/civicrm/CRM/Core/Smarty.php : 194
#12 fetch ( Array ) /sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Display.php : 138
#11 renderForm ( Array ) /sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Display.php : 100
#10 perform ( Array ) /sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php : 203
#9 handle ( Array ) /sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php : 103
#8 handle ( Array ) /sites/all/modules/civicrm/CRM/Core/Controller.php : 339
#7 run ( ) /sites/all/modules/civicrm/CRM/Utils/Wrapper.php : 117
#6 run ( Array ) /sites/all/modules/civicrm/CRM/Core/Invoke.php : 297
#5 runItem ( Array ) /sites/all/modules/civicrm/CRM/Core/Invoke.php : 72
#4 _invoke ( Array ) /sites/all/modules/civicrm/CRM/Core/Invoke.php : 52
#3 invoke ( Array ) /sites/all/modules/civicrm/drupal/civicrm.module : 456
#2 civicrm_invoke ( Array )
#1 call_user_func_array ( Array ) /includes/menu.inc : 517
#0 menu_execute_active_handler ( ) /index.php : 21

[Kurund Jalmi]

Looks like it is generated by eu.tttp.setting-1.2 extension. So you should disable the extension and check if error persist.

Kurund

[sonicthoughts]

As for the search builder error.  I also have a civicrm backtrace that gives some more detail:

Code: [Select]

Jun 13 14:53:14  [info] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -19
    [message] => DB Error: no such field
    [mode] => 16
    [debug_info] => SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicrm_contact contact_a WHERE  (  (  ( LOWER(`Home-email`.email) IS NOT NULL AND LOWER(`Home-email`.email) <> '' )  )  )  AND (contact_a.is_deleted = 0)    ORDER BY UPPER(LEFT(contact_a.sort_name, 1)) asc  [nativecode=1054 ** Unknown column 'Home-email.email' in 'where clause']
    [type] => DB_Error
    [user_info] => SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicrm_contact contact_a WHERE  (  (  ( LOWER(`Home-email`.email) IS NOT NULL AND LOWER(`Home-email`.email) <> '' )  )  )  AND (contact_a.is_deleted = 0)    ORDER BY UPPER(LEFT(contact_a.sort_name, 1)) asc  [nativecode=1054 ** Unknown column 'Home-email.email' in 'where clause']
    [to_string] => [db_error: message="DB Error: no such field" code=-19 mode=callback callback=CRM_Core_Error::handle prefix="" info="SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicrm_contact contact_a WHERE  (  (  ( LOWER(`Home-email`.email) IS NOT NULL AND LOWER(`Home-email`.email) <> '' )  )  )  AND (contact_a.is_deleted = 0)    ORDER BY UPPER(LEFT(contact_a.sort_name, 1)) asc  [nativecode=1054 ** Unknown column 'Home-email.email' in 'where clause']"]
)


Jun 13 14:53:14  [info] $backTrace = #0 /home/xxx/public_html/drupal/sites/all/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/xxx/public_html/drupal/sites/all/modules/civicrm/packages/PEAR.php(931): call_user_func((Array:2), Object(DB_Error))
#3 /home/xxx/public_html/drupal/sites/all/modules/civicrm/packages/DB.php(969): PEAR_Error->PEAR_Error("DB Error: no such field", -19, 16, (Array:2), "SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicr...")
#4 /home/xxx/public_html/drupal/sites/all/modules/civicrm/packages/PEAR.php(564): DB_Error->DB_Error(-19, 16, (Array:2), "SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicr...")
#5 /home/xxx/public_html/drupal/sites/all/modules/civicrm/packages/DB/common.php(1905): PEAR->raiseError(NULL, -19, NULL, NULL, "SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicr...", "DB_Error", TRUE)
#6 /home/xxx/public_html/drupal/sites/all/modules/civicrm/packages/DB/mysql.php(898): DB_common->raiseError(-19, NULL, NULL, NULL, "1054 ** Unknown column 'Home-email.email' in 'where clause'")
#7 /home/xxx/public_html/drupal/sites/all/modules/civicrm/packages/DB/mysql.php(327): DB_mysql->mysqlRaiseError()
#8 /home/xxx/public_html/drupal/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysql->simpleQuery("SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicr...")
#9 /home/xxx/public_html/drupal/sites/all/modules/civicrm/packages/DB/DataObject.php(2421): DB_common->query("SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicr...")
#10 /home/xxx/public_html/drupal/sites/all/modules/civicrm/packages/DB/DataObject.php(1613): DB_DataObject->_query("SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicr...")
#11 /home/xxx/public_html/drupal/sites/all/modules/civicrm/CRM/Core/DAO.php(160): DB_DataObject->query("SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicr...")
#12 /home/xxx/public_html/drupal/sites/all/modules/civicrm/CRM/Core/DAO.php(908): CRM_Core_DAO->query("SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicr...", TRUE)
#13 /home/xxx/public_html/drupal/sites/all/modules/civicrm/CRM/Contact/BAO/Query.php(4416): CRM_Core_DAO::executeQuery("SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicr...")
#14 /home/xxx/public_html/drupal/sites/all/modules/civicrm/CRM/Contact/Selector.php(1075): CRM_Contact_BAO_Query->searchQuery(NULL, NULL, NULL, FALSE, FALSE, TRUE)
#15 /home/xxx/public_html/drupal/sites/all/modules/civicrm/CRM/Utils/PagerAToZ.php(81): CRM_Contact_Selector->alphabetQuery()
#16 /home/xxx/public_html/drupal/sites/all/modules/civicrm/CRM/Utils/PagerAToZ.php(106): CRM_Utils_PagerAToZ::getDynamicCharacters(Object(CRM_Contact_Selector), FALSE)
#17 /home/xxx/public_html/drupal/sites/all/modules/civicrm/CRM/Utils/PagerAToZ.php(53): CRM_Utils_PagerAToZ::createLinks(Object(CRM_Contact_Selector), NULL, FALSE)
#18 /home/xxx/public_html/drupal/sites/all/modules/civicrm/CRM/Contact/Form/Search.php(941): CRM_Utils_PagerAToZ::getAToZBar(Object(CRM_Contact_Selector), NULL)
#19 /home/xxx/public_html/drupal/sites/all/modules/civicrm/CRM/Contact/Form/Search/Builder.php(394): CRM_Contact_Form_Search->postProcess()
#20 /home/xxx/public_html/drupal/sites/all/modules/civicrm/CRM/Core/Form.php(261): CRM_Contact_Form_Search_Builder->postProcess()
#21 /home/xxx/public_html/drupal/sites/all/modules/civicrm/CRM/Core/QuickForm/Action/Refresh.php(75): CRM_Core_Form->mainProcess()
#22 /home/xxx/public_html/drupal/sites/all/modules/civicrm/packages/HTML/QuickForm/Controller.php(203): CRM_Core_QuickForm_Action_Refresh->perform(Object(CRM_Contact_Form_Search_Builder), "refresh")
#23 /home/xxx/public_html/drupal/sites/all/modules/civicrm/packages/HTML/QuickForm/Page.php(103): HTML_QuickForm_Controller->handle(Object(CRM_Contact_Form_Search_Builder), "refresh")
#24 /home/xxx/public_html/drupal/sites/all/modules/civicrm/CRM/Core/Controller.php(339): HTML_QuickForm_Page->handle("refresh")
#25 /home/xxx/public_html/drupal/sites/all/modules/civicrm/CRM/Core/Invoke.php(323): CRM_Core_Controller->run((Array:4), (Array:0))


[Kurund Jalmi]

Looks like query is failing because 'Home-email.email' should be '`Home-email`.email'

You will have to debug query building in CRM/Contact/BAO/Query.php

Kurund

P.S. looks like this problem is already fixed in 4.5, but code is bit different

[sonicthoughts]

I tried the latest 4.5 branch and same error.
For those who may also run into this and are able to debug Query.php I can give you some clues:
in function whereClauseSingle(&$values)
the Value[0] field has "email-1" and it should have"email".
The switch statement hits default:
      default:
        $this->restWhere($values);
and that triggers all of the problem code.

I don't know what is setting email-1 but it is probably from the upgrades .... would appreciate some help or confirmation of a bug that I can file.

[Kurund Jalmi]

I tried the latest 4.5 branch and same error.

Just to confirm as mentioned above you were not able to replicate this on http://drupal.sandbox.civicrm.org

So it might be some problem with your local install / DB

Kurund

[sonicthoughts]

I've been unable to debug this.  the code is clean and the data structure is identical to a new install without the issue.  I feel quite certain there is an issue in the query.php and this code is invoked in many functions.  I did reproduce a similar issue on the demo:
1. search builder
2. Contact | Master Address Belongs to | Home | Not Empty

Sorry but we are not able to provide this at the moment.
DB Error: no such field

The strange thing is that the error is part of the query construction so why would the data influence this?
I have seen many potentially related issues on the forums:
http://forum.civicrm.org/index.php/topic,28983.msg124313.html#msg124313
http://forum.civicrm.org/index.php/topic,32820.msg139873.html#msg139873
http://forum.civicrm.org/index.php/topic,33057.msg140785.html#msg140785
http://forum.civicrm.org/index.php/topic,32802.msg139818.html#msg139818

I think my last comment would get a developer at least 1/2 way to identifying the problem code ... Would like to raise this with whoever is refactoring Query.php

[davej]

Similar problem here on a 4.4.7 instance. The instance has no custom code and is only a few months old, started as a fresh Civi on 4.4.x I believe.

Search builder throws a mysql error for any query I've tried involving address, phone or email.

Here's the query that errors out, in the example of searching for Work Country is null:

Code: [Select]

[db_error: message="DB Error: no such field"
 code=-19
 mode=callback
 callback=CRM_Core_Error::handle
 prefix=""
 info="SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicrm_contact contact_a WHERE  (  ( `Work-address`.country_id IS NULL )  )  AND (contact_a.is_deleted = 0)   GROUP BY contact_a.id  ORDER BY UPPER(LEFT(contact_a.sort_name, 1)) asc
  [nativecode=1054 ** Unknown column 'Work-address.country_id' in 'where clause']"]

So the WHERE clause refers to a table that fails to get added as a join.

As sonicthoughts mentions, whereClauseSingle() hits the default in switch($values[0]), because $values[0] is here 'country-2'.

But it's not just the WHERE clause that's going wrong, FROM evidently isn't working either, as civicrm_contact is the only table in the FROM clause.

Haven't got much further than that so far. Guidance from someone who knows their way around CRM/Contact/BAO/Query.php would be welcome.

Cheers,

Dave

[Eileen]

I'm fairly sure we fixed this in our repo & tagged it to be fixed in the LTS. I think the bug might be

https://issues.civicrm.org/jira/browse/CRM-14263

but actually if you look in our repo & think we have a small handful of fixes applied that are not in any released version of 4.4.7

https://github.com/fuzionnz/civicrm-core/commits/4.4.8rc1/CRM/Contact/BAO/Query.php

I spent about a month after upgrade fixing query problems but haven't had any problems for a few months - presumably since June looking at the commits.

You could just try banging our repo in place of your code base & seeing what happens as a test - the repo is the full tarball minus the drupal folder

[davej]

Thanks Eileen, that's a very helpful response. Your pointer to CRM-14263 led me to check whether the site specifies a default search profile and sure enough, it does. That solves the mystery of why the problem occurs on this site but not other 4.4.7s.

We're going back to the client to see whether they actually need the default search profile and if so, to discuss the options for upgrading or patching. Are all the relevant fixes in 4.5.1? And in the 4.4 branch ready for 4.4.8 ?

Thanks again,

Dave

[Eileen]

Well - maybe once it goes LTS - we've been running them for 4 months so they are pretty solid