CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM (Moderator: Dave Greenberg) »
  • Query with empty 'OR' clause causing errors
Pages: [1]

Author Topic: Query with empty 'OR' clause causing errors  (Read 2023 times)

Matt2000

  • I post frequently
  • ***
  • Posts: 288
  • Karma: 27
    • http://www.ninjitsuweb.com
Query with empty 'OR' clause causing errors
May 06, 2008, 02:45:32 pm
I'm getting DB syntax errors when attempting to view contacts:


Here's the bad query. Scroll way over and look for "OR )" right before "ORDER BY" at the far end of the query:

Code: [Select]
SELECT
                    civicrm_group_contact.id as civicrm_group_contact_id,
                    civicrm_group.title as group_title,
                    civicrm_group.visibility as visibility,
                    civicrm_group_contact.status as status,
                    civicrm_group.id as group_id,
                    civicrm_subscription_history.date as date,
                    civicrm_subscription_history.method as method FROM civicrm_contact contact_a LEFT JOIN civicrm_group_contact ON contact_a.id = civicrm_group_contact.contact_id  LEFT JOIN civicrm_group ON civicrm_group.id =  civicrm_group_contact.group_id  LEFT JOIN civicrm_subscription_history
                                   ON civicrm_group_contact.contact_id = civicrm_subscription_history.contact_id
                                  AND civicrm_group_contact.group_id   =  civicrm_subscription_history.group_id WHERE contact_a.id = 2044 AND civicrm_group.is_active = 1  AND civicrm_group_contact.status = 'Added' AND  (  ( civicrm_group_contact.group_id IN (85, 86, 87, 88, 99, 100, 91, 92, 97, 98, 80, 78, 79, 81, 82, 1, 46, 47, 48, 49, 50, 51, 146, 147, 84, 54, 95, 96, 133, 134, 93, 94, 113, 114, 127, 128, 52, 131, 132, 55, 62, 53, 12, 119, 120, 101, 102, 83, 144, 145, 42, 32, 7, 8, 57, 73, 143, 15, 71, 148, 65, 66, 61, 63, 64, 141, 142, 56, 58, 125, 126, 44, 115, 116, 60, 68, 109, 110, 103, 104, 74, 107, 108, 67, 139, 140, 135, 136, 70, 137, 138, 69, 59, 129, 130, 123, 124, 72, 111, 112, 75, 121, 122, 105, 106, 117, 118 ) AND civicrm_group_contact.status = 'Added' )  OR  )   ORDER BY civicrm_group.title, civicrm_subscription_history.date ASC LIMIT 0, 3 [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 ')   ORDER BY civicrm_group.title, civicrm_subscription_history.date ASC LIMIT 0,' at line 10]
 

Any ideas? The backtrace is:

Code: [Select]
/usr/local/lib/civicrm/svn/v2.0/CRM/Core/Error.php, backtrace, 148
, handle,
/usr/local/lib/civicrm/svn/v2.0/packages/PEAR.php, call_user_func, 901
/usr/local/lib/civicrm/svn/v2.0/packages/DB.php, PEAR_Error, 888
/usr/local/lib/civicrm/svn/v2.0/packages/PEAR.php, DB_Error, 563
/usr/local/lib/civicrm/svn/v2.0/packages/DB/common.php, raiseError, 1849
/usr/local/lib/civicrm/svn/v2.0/packages/DB/mysql.php, raiseError, 894
/usr/local/lib/civicrm/svn/v2.0/packages/DB/mysql.php, mysqlRaiseError, 324
/usr/local/lib/civicrm/svn/v2.0/packages/DB/common.php, simpleQuery, 1162
/usr/local/lib/civicrm/svn/v2.0/packages/DB/DataObject.php, query, 2343
/usr/local/lib/civicrm/svn/v2.0/packages/DB/DataObject.php, _query, 1566
/usr/local/lib/civicrm/svn/v2.0/CRM/Core/DAO.php, query, 765
/usr/local/lib/civicrm/svn/v2.0/CRM/Contact/BAO/GroupContact.php, executeQuery, 382
/usr/local/lib/civicrm/svn/v2.0/CRM/Contact/BAO/GroupContact.php, getContactGroup, 112
/usr/local/lib/civicrm/svn/v2.0/CRM/Contact/BAO/Contact.php, getValues, 714
/usr/local/lib/civicrm/svn/v2.0/CRM/Contact/Page/View/Tabbed.php, retrieve, 115
/usr/local/lib/civicrm/svn/v2.0/CRM/Contact/Page/View/Tabbed.php, view, 77
/usr/local/lib/civicrm/svn/v2.0/CRM/Core/Invoke.php, run, 431
/usr/local/lib/civicrm/svn/v2.0/CRM/Core/Invoke.php, contact, 98
/usr/local/lib/civicrm/svn/v2.0/drupal/civicrm.module, invoke, 329
, civicrm_invoke,
/usr/local/lib/drupal/5.7/includes/menu.inc, call_user_func_array, 418
/usr/local/lib/drupal/5.7/index.php, menu_execute_active_handler, 15
Drupal/CiviCRM micro-blogging http://twitter.com/matt2000

Ninjitsu Web Development http://www.NinjitsuWeb.com/

Matt2000

  • I post frequently
  • ***
  • Posts: 288
  • Karma: 27
    • http://www.ninjitsuweb.com
Re: Query with empty 'OR' clause causing errors
May 06, 2008, 03:19:01 pm
Update:

The empty OR clause is being generated by CRM_Core_Permission_Drupal::groupClause() which use this line:

$clauses[] = CRM_Contact_BAO_SavedSearch::whereClause( $group->saved_search_id, $tables, $whereTables );

...and makes an empty value. I'm working on a fix...
Drupal/CiviCRM micro-blogging http://twitter.com/matt2000

Ninjitsu Web Development http://www.NinjitsuWeb.com/

Matt2000

  • I post frequently
  • ***
  • Posts: 288
  • Karma: 27
    • http://www.ninjitsuweb.com
Re: Query with empty 'OR' clause causing errors
May 06, 2008, 03:23:07 pm
I found success by replacing the line above with:

Code: [Select]
$BAOresult = CRM_Contact_BAO_SavedSearch::whereClause( $group->saved_search_id, $tables, $whereTables );
if($BAOresult) { $clauses[] = $BAOresult; }
Drupal/CiviCRM micro-blogging http://twitter.com/matt2000

Ninjitsu Web Development http://www.NinjitsuWeb.com/

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Query with empty 'OR' clause causing errors
May 07, 2008, 03:36:17 am

can u find out what the saved search was? i..e what was the query. It is a bit wierd to have a saved search not have any where clause (which might indicate it was a saved search for all contacts with no parameters).

I've integrated your fix in 2.1, i'd like to figure out what was causing the issue :)

thanx

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

Matt2000

  • I post frequently
  • ***
  • Posts: 288
  • Karma: 27
    • http://www.ninjitsuweb.com
Re: Query with empty 'OR' clause causing errors
May 07, 2008, 03:28:09 pm
I do have a saved search with NULL query clause fields, but it's not attached to any smart groups....
Drupal/CiviCRM micro-blogging http://twitter.com/matt2000

Ninjitsu Web Development http://www.NinjitsuWeb.com/
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM (Moderator: Dave Greenberg) »
  • Query with empty 'OR' clause causing errors

This forum was archived on 2017-11-26.