Answers.Body: <p>I just ran into this issue (on current Drupal, current CiviCRM setup) and confirmed that the way it's set now, a non-admin user needs the permission "access deleted contacts" to run this report.</p>
  
  <p>Hope that's helpful - not much of an answer but I lost access to my old account and don't have enough rep to post a comment :)</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2018-04-06T06:43:29.547Z
Answers.Text: Solution of Eli Lisseck worked for me. 
  Although I don't want them all to have access to the deleted contacts, it fixed my issue. And since my PHP knowledge is null, this is great!
Answers.UserDisplayName: Betty Dolfing
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-12-22T20:06:47.673Z
Answers.LastActivityDate: 2017-12-22T20:06:47.673Z
Answers.UserDisplayName: Eli Lisseck
Body: <p>UPDATE 29th July :</p>
  
  <p>Yes it's taken me this long to work it out so now I need some advice please. I have tracked the place where "rogue" SQL down is being created and it's source is in this hook : hook_civicrm_aclWhereClause. The PHP jumps around a little but calls "getSelectWhereClause" in civicrm-core/DAO.php which in turn calls "addSelectWhereClause" also in DAO.php.</p>
  
  <p>I have traced what is returned and it is from here that the "rogue" SQL :</p>
  
  <p>"AND <code>contact_civireport</code>.<code>is_deleted</code> != 1 
  AND <code>relationship_civireport</code>.<code>contact_id_a</code> IN .... etc."</p>
  
  <p>is being generated. Looking around I see that a caveat for hook_civicrm_aclWhereClause is that it not be called at all if the logged in user has access to the "edit all contacts" permission which a "super-user" does. I went into Joomla and added that permission to my member category but the SQL is still incorrect.</p>
  
  <p>My "fudge" (and before there are cries of outrage I know it's a fudge) is to comment out the lines in form/detail.php that append the field $this>aclWhere to the "WHERE" clause.</p>
  
  <p>My PHP skills are limited (but getting better) so what I need advice on is:</p>
  
  <p>a) Did I edit the permission in the correct place?
  b) If I did then it didn't seem to work so where does the PHP look to generate this (what is to me incorrect) code:</p>
  
  <p>Entering getSelectWhereClause
  tablename: contact_civireport
  vals: Array ( ) 
  vals: Array ( [0] => != 1 ) 
  Evaluating vals now
  field: is_deleted</p>
  
  <p>Leaving getSelectWhereClause
  clauses :Array ( [0] => <strong><code>contact_civireport</code>.<code>is_deleted</code> != 1 )</strong> 
  clause: string(38) "<code>contact_civireport</code>.<code>is_deleted</code> != 1" 
  ret: array(1) { ["civicrm_contact.is_deleted"]=> string(38) "<code>contact_civireport</code>.<code>is_deleted</code> != 1" } </p>
  
  <p>UPDATE : 24th May</p>
  
  <p>In my original post I said the issue appear to affect only those users that were designated as "Member". Doing some more playing around today it appears as if ALL users other than those designated as "Super Users" get the same issue with the rogue SQL being appended to the query.</p>
  
  <p>We have a search defined in CiviCRM to look for various vehicles. If I log onto the site using my id which has <strong>"Super User"</strong> privileges I can run the query and results are returned. However, if I use an account which doesn't have the same privileges (i.e. a "normal" member account) I get a screen back saying 
  "Sorry, but we are not able to provide this at the moment.
  DB Error: no such field"</p>
  
  <p>I put debug on and it further reported:</p>
  
  <p>Error Details</p>
  
  <blockquote>
    <p>Database Error Code: Unknown column 'relationship_civireport.contact_id_a' in 'IN/ALL/ANY subquery', 1054.</p>
  </blockquote>
  
  <p>When I look at the SQL it does indeed show that it can't resolve the field in question however my query is why would it work for an Admin user and not a normal one? It does seem to point a privilege issue somewhere but how can I identify what this is so I can fix it?</p>
  
  <p>UPDATE:</p>
  
  <p>Doing some more digging around it appears that the generated SQL for the non-Admin user includes these lines which is what causes the issue:</p>
  
  <pre><code>AND `contact_civireport`.`is_deleted` != 1 
  AND `relationship_civireport`.`contact_id_a` IN 
    (SELECT `id` FROM `civicrm_contact` WHERE is_deleted != 1) 
  AND `relationship_civireport`.`contact_id_b` IN 
    (SELECT `id` FROM `civicrm_contact` WHERE is_deleted != 1)
  </code></pre>
  
  <p>So logged in as Admin, run the report , SQL doesn't have those extra conditions in and the report runs.</p>
  
  <p>Log in as "normal" member , run the report , those lines appear and, as it says, it can't find the field because it's trying to get it from something that isn't a part of the query!</p>
  
  <p>UPDATE : 22nd May</p>
  
  <p>The version is CiviCRM 4.7.19. In terms of the report I believe the underlying one to be from "Contacts" under the "Constituent Report (Detail)" template. It is trying to display all those people who own a car made in a certain year range.</p>
  
  <p>UPDATE : 23rd May</p>
  
  <p>I will try and precis the differences I can see in generating the reports</p>
  
  <p>In the Member log I have this line which doesn't appear in the Admin log:</p>
  
  <pre><code>[info] $Query = SET @civicrm_user_id = 267
  </code></pre>
  
  <p>In the Admin log I have these lines which don't appear in the Member log :</p>
  
  <pre><code>[info] $Query = SELECT * FROM civicrm_uf_match WHERE (  civicrm_uf_match.domain_id = 1 )  AND (  civicrm_uf_match.uf_id = 187 )
  [info] $Query =
  SELECT    civicrm_contact.*,
            civicrm_email.email          as email
  FROM      civicrm_contact
  LEFT JOIN civicrm_email ON civicrm_email.contact_id = civicrm_contact.id
       AND  civicrm_email.is_primary = 1
  WHERE     civicrm_contact.id = 10909
  
  [info] $Query = SELECT *  FROM civicrm_contact_type  WHERE (  civicrm_contact_type.name  = 'Individual' )
  
  [info] $Query = SELECT  id  FROM civicrm_uf_group WHERE (  civicrm_uf_group.name  = 'summary_overlay' )
  [info] $Query =
  SELECT type.*, parent.name as parent, parent.label as parent_label
  FROM      civicrm_contact_type type
  LEFT JOIN civicrm_contact_type parent ON type.parent_id = parent.id
  WHERE  type.name IS NOT NULL
  </code></pre>
  
  <p>Then the two logs seem to follow the same route but the incorrect SQL generated in the Member log looks like this:</p>
  
  <pre><code>[info] $Query = SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name, contact_civireport.id as civicrm_contact_id, value_vehicle_1_civireport.car_year_1 as civicrm_value_vehicle_1_custom_1, value_vehicle_1_civireport.vehicle_make_2 as civicrm_value_vehicle_1_custom_2, value_vehicle_1_civireport.vehicle_model_3 as civicrm_value_vehicle_1_custom_3, value_vehicle_1_civireport.vehicle_model_code_31 as civicrm_value_vehicle_1_custom_31, value_vehicle_1_civireport.vehicle_body_style_4 as civicrm_value_vehicle_1_custom_4, value_vehicle_1_civireport.vehicle_body_description_29 as civicrm_value_vehicle_1_custom_29, value_vehicle_1_civireport.doors_5 as civicrm_value_vehicle_1_custom_5, value_vehicle_1_civireport.lhd_or_rhd_8 as civicrm_value_vehicle_1_custom_8, value_vehicle_1_civireport.body_serial_number_9 as civicrm_value_vehicle_1_custom_9, value_vehicle_1_civireport._of_cylinders_7 as civicrm_value_vehicle_1_custom_7, value_vehicle_1_civireport.engine_serial_number_10 as civicrm_value_vehicle_1_custom_10, value_vehicle_1_civireport.hudson_engine__11 as civicrm_value_vehicle_1_custom_11, value_vehicle_1_civireport.vehicle_condition_13 as civicrm_value_vehicle_1_custom_13, value_vehicle_1_civireport.vehicle_modified_32 as civicrm_value_vehicle_1_custom_32
  FROM civicrm_contact contact_civireport
  LEFT JOIN civicrm_value_vehicle_1 value_vehicle_1_civireport ON value_vehicle_1_civireport.entity_id = contact_civireport.id WHERE ( contact_civireport.is_deceased = 0 ) AND ( value_vehicle_1_civireport.car_year_1 IN (1909, 1910, 1911, 1912, 1913) ) AND ( ( value_vehicle_1_civireport.vehicle_make_2 IN ( 'Hudson') ) ) **AND `contact_civireport`.`is_deleted` != 1 AND `relationship_civireport`.`contact_id_a` IN (SELECT `id` FROM `civicrm_contact` WHERE is_deleted != 1) AND `relationship_civireport`.`contact_id_b` IN (SELECT `id` FROM `civicrm_contact` WHERE is_deleted != 1)**    ORDER BY value_vehicle_1_civireport.car_year_1 ASC, value_vehicle_1_civireport.vehicle_make_2 ASC, value_vehicle_1_civireport.vehicle_model_3 ASC  LIMIT 0, 10
  </code></pre>
  
  <p>Then these steps happen in the Member log which I assume is a "tidy-up":</p>
  
  <pre><code>[info] $Query = SELECT GET_LOCK( '181fec6b8c8018b4408d33805c7d9c73e37c2684', 3 )
  [info] $Query = SELECT COUNT(*) FROM civicrm_cache WHERE group_name = "CiviCRM Session" AND path = "_CRM_Report_Form_Contact_Detail_d3ccfb3ef5be498a753ab5c22d2f11d4_7593_container"
   [info] $Query = INSERT INTO civicrm_cache (`component_id`,`created_date`,`data`,`group_name`,`path`) VALUES
  (NULL,"2017-05-23 07:06:09","a:4:{s:8:\"defaults\";a:0:{}s:9:\"constants\";a:0:{}s:6:\"values\";a:1:{s:6:\"Detail\";a:0:{}}s:5:\"valid\";a:1:{s:6:\"Detail\";N;}}","CiviCRM Session","_CRM_Report_Form_Contact_Detail_d3ccfb3ef5be498a753ab5c22d2f11d4_7593_container")
  [info] $Query = SELECT RELEASE_LOCK( '181fec6b8c8018b4408d33805c7d9c73e37c2684' )
  [info] $Query = SELECT GET_LOCK( '70b6381734fcfe80b5c1603e4bcea118a9040f61', 3 )
  [info] $Query = SELECT COUNT(*) FROM civicrm_cache WHERE group_name = "CiviCRM Session" AND path = "CiviCRM_CRM_Report_Form_Contact_Detail_d3ccfb3ef5be498a753ab5c22d2f11d4_7593"
  [info] $Query = INSERT INTO civicrm_cache (`component_id`,`created_date`,`data`,`group_name`,`path`) VALUES
  (NULL,"2017-05-23 07:06:09","a:3:{s:8:\"entryURL\";s:127:\"http://xxx.xxx.org/index.php?option=com_civicrm&amp;amp;task=civicrm/report/instance/181&amp;amp;option=com_civicrm&amp;amp;reset=1\";s:5:\"qfKey\";s:37:\"d3ccfb3ef5be498a753ab5c22d2f11d4_7593\";s:6:\"crmPID\";i:1;}","CiviCRM Session","CiviCRM_CRM_Report_Form_Contact_Detail_d3ccfb3ef5be498a753ab5c22d2f11d4_7593")
  [info] $Query = SELECT RELEASE_LOCK( '70b6381734fcfe80b5c1603e4bcea118a9040f61' )
  </code></pre>
  
  <p>In the Admin log the SQL looks like this and then you can there is more activity afterwards which I assume is part of the report running correctly:</p>
  
  <pre><code>[info] $Query = SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name, contact_civireport.id as civicrm_contact_id, value_vehicle_1_civireport.car_year_1 as civicrm_value_vehicle_1_custom_1, value_vehicle_1_civireport.vehicle_make_2 as civicrm_value_vehicle_1_custom_2, value_vehicle_1_civireport.vehicle_model_3 as civicrm_value_vehicle_1_custom_3, value_vehicle_1_civireport.vehicle_model_code_31 as civicrm_value_vehicle_1_custom_31, value_vehicle_1_civireport.vehicle_body_style_4 as civicrm_value_vehicle_1_custom_4, value_vehicle_1_civireport.vehicle_body_description_29 as civicrm_value_vehicle_1_custom_29, value_vehicle_1_civireport.doors_5 as civicrm_value_vehicle_1_custom_5, value_vehicle_1_civireport.lhd_or_rhd_8 as civicrm_value_vehicle_1_custom_8, value_vehicle_1_civireport.body_serial_number_9 as civicrm_value_vehicle_1_custom_9, value_vehicle_1_civireport._of_cylinders_7 as civicrm_value_vehicle_1_custom_7, value_vehicle_1_civireport.engine_serial_number_10 as civicrm_value_vehicle_1_custom_10, value_vehicle_1_civireport.hudson_engine__11 as civicrm_value_vehicle_1_custom_11, value_vehicle_1_civireport.vehicle_condition_13 as civicrm_value_vehicle_1_custom_13, value_vehicle_1_civireport.vehicle_modified_32 as civicrm_value_vehicle_1_custom_32
          FROM civicrm_contact contact_civireport
  LEFT JOIN civicrm_value_vehicle_1 value_vehicle_1_civireport ON value_vehicle_1_civireport.entity_id = contact_civireport.id WHERE ( contact_civireport.is_deceased = 0 ) AND ( value_vehicle_1_civireport.car_year_1 IN (1909, 1910, 1911, 1912, 1913) ) AND ( ( value_vehicle_1_civireport.vehicle_make_2 IN ( 'Hudson') ) )   ORDER BY value_vehicle_1_civireport.car_year_1 ASC, value_vehicle_1_civireport.vehicle_make_2 ASC, value_vehicle_1_civireport.vehicle_model_3 ASC  LIMIT 0, 10
  
  [info] $Query = SELECT FOUND_ROWS();
  
  [info] $Query =
  SELECT cg.table_name, cf.id
  FROM  civicrm_custom_field cf
  INNER JOIN civicrm_custom_group cg ON cg.id = cf.custom_group_id
  WHERE cg.extends IN ('Contact','Individual','Household','Organization') AND
        cg.is_active = 1 AND
        cf.is_active = 1 AND
        cf.is_searchable = 1 AND
        cf.data_type   NOT IN ('ContactReference', 'Date') AND
        cf.id IN (1,2,3,31,4,29,5,8,9,7,10,11,13,32)
  
  [info] $Query = SELECT *  FROM civicrm_custom_field WHERE (  civicrm_custom_field.id = 1 )
  
  &lt; snip ; same sort of SQL &gt;
  
  [info] $Query = SELECT contribution_civireport.contact_id as civicrm_contribution_contact_id   FROM
                              civicrm_contact  contact_civireport
                              INNER JOIN civicrm_contribution       contribution_civireport
                                      ON contact_civireport.id = contribution_civireport.contact_id
  
  [info] $Query = SELECT membership_civireport.contact_id as civicrm_membership_contact_id, membership_civireport.end_date as civicrm_membership_membership_end_date   FROM
                              civicrm_contact  contact_civireport
                              INNER JOIN civicrm_membership       membership_civireport
                                      ON contact_civireport.id = membership_civireport.contact_id
                           WHERE    contact_civireport.id IN ( 11791,11792,11843,11844,11846,11847,11853,11857,11858,11845 )
  
  [info] $Query = SELECT participant_civireport.contact_id as civicrm_participant_contact_id   FROM
                              civicrm_contact  contact_civireport
                              INNER JOIN civicrm_participant       participant_civireport
                                      ON contact_civireport.id = participant_civireport.contact_id
                           WHERE    contact_civireport.id IN ( 11791,11792,11843,11844,11846,11847,11853,11857,11858,11845 )
  
  [info] $Query = SELECT  id, label_a_b, label_b_a, contact_type_a, contact_type_b, contact_sub_type_a, contact_sub_type_b
   FROM civicrm_relationship_type  WHERE (  civicrm_relationship_type.is_active = 1 )
  
  [info] $Query = SELECT relationship_civireport.relationship_type_id as civicrm_relationship_relationship_type_id, relationship_civireport.contact_id_b as civicrm_relationship_contact_id_b ,contact_civireport.display_name as contact_b_name,  contact_a.id as contact_a_id , contact_a.display_name  as contact_a_name  FROM
                              civicrm_relationship relationship_civireport
  
                              LEFT JOIN civicrm_contact  contact_civireport ON
                                  contact_civireport.id = relationship_civireport.contact_id_b
                              LEFT JOIN civicrm_contact  contact_a ON
                                 contact_a.id = relationship_civireport.contact_id_a
                           WHERE    (contact_civireport.id IN ( 11791,11792,11843,11844,11846,11847,11853,11857,11858,11845 )
                                    OR
                                    contact_a.id IN ( 11791,11792,11843,11844,11846,11847,11853,11857,11858,11845 ) ) AND
                                    relationship_civireport.is_active = 1 AND
                                    contact_a.is_deleted = 0 AND
  
  [info] $Query = SELECT *  FROM civicrm_component
  
  [info] $Query =
  SELECT  v.label as label ,v.value as value, v.grouping as grouping
  FROM   civicrm_option_value v,
         civicrm_option_group g
  WHERE  v.option_group_id = g.id
    AND  g.name            = 'activity_type'
    AND  g.is_active       = 1  AND  v.is_active = 1  AND ( v.component_id IS NULL  OR v.component_id IN (SELECT id FROM civicrm_component WHERE name IN ("CiviEvent","CiviContribute","CiviMember","CiviMail","CiviReport","CiviPledge")) )  AND  ( v.component_id IS NULL OR v.component_id IN (1,2,3,4,6,8)) ORDER BY v.weight
  
  [info] $Query =
  SELECT  v.label as label ,v.value as value, v.grouping as grouping
  FROM   civicrm_option_value v,
         civicrm_option_group g
  WHERE  v.option_group_id = g.id
    AND  g.name            = 'activity_status'
    AND  g.is_active       = 1  AND  v.is_active = 1  AND ( v.component_id IS NULL  OR v.component_id IN (SELECT id FROM civicrm_component WHERE name IN ("CiviEvent","CiviContribute","CiviMember","CiviMail","CiviReport","CiviPledge")) )  ORDER BY v.weight
  
  [info] $Query =
  SELECT  v.label as label ,v.value as value, v.grouping as grouping
  FROM   civicrm_option_value v,
         civicrm_option_group g
  WHERE  v.option_group_id = g.id
    AND  g.name            = 'relative_date_filters'
    AND  g.is_active       = 1  AND  v.is_active = 1  AND ( v.component_id IS NULL  OR v.component_id IN (SELECT id FROM civicrm_component WHERE name IN ("CiviEvent","CiviContribute","CiviMember","CiviMail","CiviReport","CiviPledge")) )  ORDER BY v.weight
  
  
  [info] $Query = SELECT * FROM civicrm_preferences_date WHERE (  civicrm_preferences_date.name  = 'searchDate' )
  
  [info] $Query = SELECT * FROM civicrm_report_instance WHERE (  civicrm_report_instance.id = 181 )
  
  [info] $Query = SELECT data FROM civicrm_cache WHERE group_name = "navigation" AND path = "navigationList"
  
  [info] $Query =
  SELECT id, title, description, visibility, parents
  FROM   civicrm_group
  WHERE  id IN (133,139,145,137,144,33,31,30,36,29,32,38,35,39,37,34,2,24,28,20,26,23,21,27,22,25,116,4,117,111,114,12,18,16,13,10,11,7,6,9,19,17,8,15,3,112,128,119,147,138,120,124,125,126,127,113,131,130,143,53,52,54,55,115,56,57,58,59,60,61,62,63,64,65,67,66,68,69,70,72,71,73,74,75,76,77,78,79,80,81,82,83,84,86,85,108,87,88,89,105,104,136,91,99,100,107,92,94,95,98,97,96,101,106,102,103,93,109,141,48,50,40,46,118,44,43,45,51,47,49,5,140,142,146,122,135,134)
   ORDER BY title
  
  [info] $Query = SELECT *  FROM civicrm_navigation  WHERE (  civicrm_navigation.id = 483 )  AND (  civicrm_navigation.domain_id = 1 )
  
  [info] $Query = SELECT GET_LOCK( '51f408a0235586dbe891208004a7e5fc67e72dad', 3 )
  
  [info]  QUERY DONE IN 0.000398  seconds. Result is 1 rows by 1 columns.
  
  [info] $Query = SELECT COUNT(*) FROM civicrm_cache WHERE group_name = "CiviCRM Session" AND path = "_CRM_Report_Form_Contact_Detail_6de5d8ad3d8789ed2d9a0038cbd3a7a6_5578_container"
  
  [info]  QUERY DONE IN 0.000546  seconds. Result is 1 rows by 1 columns.
  
  [info] $Query = INSERT INTO civicrm_cache (`component_id`,`created_date`,`data`,`group_name`,`path`) VALUES
  (NULL,"2017-05-23 07:07:57","a:4:{s:8:\"defaults\";a:0:{}s:9:\"constants\";a:0:{}s:6:\"values\";a:1:{s:6:\"Detail\";a:0:{}}s:5:\"valid\";a:1:{s:6:\"Detail\";N;}}","CiviCRM Session","_CRM_Report_Form_Contact_Detail_6de5d8ad3d8789ed2d9a0038cbd3a7a6_5578_container")
  
  [info] $Query = SELECT RELEASE_LOCK( '51f408a0235586dbe891208004a7e5fc67e72dad' )
  
  [info] $Query = SELECT GET_LOCK( '431ea828bed0270e81256e366f6cf4b682d6cfac', 3 )
  
  [info] $Query = SELECT COUNT(*) FROM civicrm_cache WHERE group_name = "CiviCRM Session" AND path = "CiviCRM_CRM_Report_Form_Contact_Detail_6de5d8ad3d8789ed2d9a0038cbd3a7a6_5578"
  
  [info] $Query = INSERT INTO civicrm_cache (`component_id`,`created_date`,`data`,`group_name`,`path`) VALUES
  (NULL,"2017-05-23 07:07:57","a:3:{s:8:\"entryURL\";s:127:\"http://xxxx.xxxx.org/index.php?option=com_civicrm&amp;amp;task=civicrm/report/instance/181&amp;amp;option=com_civicrm&amp;amp;reset=1\";s:5:\"qfKey\";s:37:\"6de5d8ad3d8789ed2d9a0038cbd3a7a6_5578\";s:6:\"crmPID\";i:1;}","CiviCRM Session","CiviCRM_CRM_Report_Form_Contact_Detail_6de5d8ad3d8789ed2d9a0038cbd3a7a6_5578")
  
  [info] $Query = SELECT RELEASE_LOCK( '431ea828bed0270e81256e366f6cf4b682d6cfac' )
  </code></pre>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-05-21T20:35:23.827Z
Comments.Text: What version of CiviCRM is this?  What is the underlying report template, and is it custom?  Please update your question to reflect that.  If this is a current version of CiviCRM and a non-custom report template, we should document the steps to replicate this on demo.civicrm.org and file a bug.
Comments.UserDisplayName: Jon G - Megaphone Tech
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-05-23T16:56:03.570Z
Comments.Text: So I have done a little more digging and have attached two files from the SQL log. One is using a member account and the other the Admin account and you can see the differences in there. Is there a way to upload these files?
Comments.UserDisplayName: Hudson Essex Terraplane Club
Tags: <custom-reports><sql>
Title: Error occurs : Unknown column 'relationship_civireport.contact_id_a' however not for "Super User" users
ThreadText: # Error occurs : Unknown column 'relationship_civireport.contact_id_a' however not for "Super User" users
  
  (Asked by: username_0 on 2017-05-21)
  
  UPDATE 29th July :
  
  Yes it's taken me this long to work it out so now I need some advice please. I have tracked the place where "rogue" SQL down is being created and it's source is in this hook : hook\_civicrm\_aclWhereClause. The PHP jumps around a little but calls "getSelectWhereClause" in civicrm-core/DAO.php which in turn calls "addSelectWhereClause" also in DAO.php.
  
  I have traced what is returned and it is from here that the "rogue" SQL :
  
  "AND `contact_civireport`.`is_deleted` != 1
  AND `relationship_civireport`.`contact_id_a` IN .... etc."
  
  is being generated. Looking around I see that a caveat for hook\_civicrm\_aclWhereClause is that it not be called at all if the logged in user has access to the "edit all contacts" permission which a "super-user" does. I went into Joomla and added that permission to my member category but the SQL is still incorrect.
  
  My "fudge" (and before there are cries of outrage I know it's a fudge) is to comment out the lines in form/detail.php that append the field $this>aclWhere to the "WHERE" clause.
  
  My PHP skills are limited (but getting better) so what I need advice on is:
  
  a) Did I edit the permission in the correct place?
  b) If I did then it didn't seem to work so where does the PHP look to generate this (what is to me incorrect) code:
  
  Entering getSelectWhereClause
  tablename: contact\_civireport
  vals: Array ( )
  vals: Array ( [0] => != 1 )
  Evaluating vals now
  field: is\_deleted
  
  Leaving getSelectWhereClause
  clauses :Array ( [0] => **`contact_civireport`.`is_deleted` != 1 )**
  clause: string(38) "`contact_civireport`.`is_deleted` != 1"
  ret: array(1) { ["civicrm\_contact.is\_deleted"]=> string(38) "`contact_civireport`.`is_deleted` != 1" }
  
  UPDATE : 24th May
  
  In my original post I said the issue appear to affect only those users that were designated as "Member". Doing some more playing around today it appears as if ALL users other than those designated as "Super Users" get the same issue with the rogue SQL being appended to the query.
  
  We have a search defined in CiviCRM to look for various vehicles. If I log onto the site using my id which has **"Super User"** privileges I can run the query and results are returned. However, if I use an account which doesn't have the same privileges (i.e. a "normal" member account) I get a screen back saying
  "Sorry, but we are not able to provide this at the moment.
  DB Error: no such field"
  
  I put debug on and it further reported:
  
  Error Details
  
  > Database Error Code: Unknown column 'relationship\_civireport.contact\_id\_a' in 'IN/ALL/ANY subquery', 1054.
  
  When I look at the SQL it does indeed show that it can't resolve the field in question however my query is why would it work for an Admin user and not a normal one? It does seem to point a privilege issue somewhere but how can I identify what this is so I can fix it?
  
  UPDATE:
  
  Doing some more digging around it appears that the generated SQL for the non-Admin user includes these lines which is what causes the issue:
  
  ```
  AND `contact_civireport`.`is_deleted` != 1 
  AND `relationship_civireport`.`contact_id_a` IN 
    (SELECT `id` FROM `civicrm_contact` WHERE is_deleted != 1) 
  AND `relationship_civireport`.`contact_id_b` IN 
    (SELECT `id` FROM `civicrm_contact` WHERE is_deleted != 1)
  
  ```
  
  So logged in as Admin, run the report , SQL doesn't have those extra conditions in and the report runs.
  
  Log in as "normal" member , run the report , those lines appear and, as it says, it can't find the field because it's trying to get it from something that isn't a part of the query!
  
  UPDATE : 22nd May
  
  The version is CiviCRM 4.7.19. In terms of the report I believe the underlying one to be from "Contacts" under the "Constituent Report (Detail)" template. It is trying to display all those people who own a car made in a certain year range.
  
  UPDATE : 23rd May
  
  I will try and precis the differences I can see in generating the reports
  
  In the Member log I have this line which doesn't appear in the Admin log:
  
  ```
  [info] $Query = SET @civicrm_user_id = 267
  
  ```
  
  In the Admin log I have these lines which don't appear in the Member log :
  
  ```
  [info] $Query = SELECT * FROM civicrm_uf_match WHERE (  civicrm_uf_match.domain_id = 1 )  AND (  civicrm_uf_match.uf_id = 187 )
  [info] $Query =
  SELECT    civicrm_contact.*,
            civicrm_email.email          as email
  FROM      civicrm_contact
  LEFT JOIN civicrm_email ON civicrm_email.contact_id = civicrm_contact.id
       AND  civicrm_email.is_primary = 1
  WHERE     civicrm_contact.id = 10909
  
  [info] $Query = SELECT *  FROM civicrm_contact_type  WHERE (  civicrm_contact_type.name  = 'Individual' )
  
  [info] $Query = SELECT  id  FROM civicrm_uf_group WHERE (  civicrm_uf_group.name  = 'summary_overlay' )
  [info] $Query =
  SELECT type.*, parent.name as parent, parent.label as parent_label
  FROM      civicrm_contact_type type
  LEFT JOIN civicrm_contact_type parent ON type.parent_id = parent.id
  WHERE  type.name IS NOT NULL
  
  ```
  
  Then the two logs seem to follow the same route but the incorrect SQL generated in the Member log looks like this:
  
  ```
  [info] $Query = SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name, contact_civireport.id as civicrm_contact_id, value_vehicle_1_civireport.car_year_1 as civicrm_value_vehicle_1_custom_1, value_vehicle_1_civireport.vehicle_make_2 as civicrm_value_vehicle_1_custom_2, value_vehicle_1_civireport.vehicle_model_3 as civicrm_value_vehicle_1_custom_3, value_vehicle_1_civireport.vehicle_model_code_31 as civicrm_value_vehicle_1_custom_31, value_vehicle_1_civireport.vehicle_body_style_4 as civicrm_value_vehicle_1_custom_4, value_vehicle_1_civireport.vehicle_body_description_29 as civicrm_value_vehicle_1_custom_29, value_vehicle_1_civireport.doors_5 as civicrm_value_vehicle_1_custom_5, value_vehicle_1_civireport.lhd_or_rhd_8 as civicrm_value_vehicle_1_custom_8, value_vehicle_1_civireport.body_serial_number_9 as civicrm_value_vehicle_1_custom_9, value_vehicle_1_civireport._of_cylinders_7 as civicrm_value_vehicle_1_custom_7, value_vehicle_1_civireport.engine_serial_number_10 as civicrm_value_vehicle_1_custom_10, value_vehicle_1_civireport.hudson_engine__11 as civicrm_value_vehicle_1_custom_11, value_vehicle_1_civireport.vehicle_condition_13 as civicrm_value_vehicle_1_custom_13, value_vehicle_1_civireport.vehicle_modified_32 as civicrm_value_vehicle_1_custom_32
  FROM civicrm_contact contact_civireport
  LEFT JOIN civicrm_value_vehicle_1 value_vehicle_1_civireport ON value_vehicle_1_civireport.entity_id = contact_civireport.id WHERE ( contact_civireport.is_deceased = 0 ) AND ( value_vehicle_1_civireport.car_year_1 IN (1909, 1910, 1911, 1912, 1913) ) AND ( ( value_vehicle_1_civireport.vehicle_make_2 IN ( 'username_0') ) ) **AND `contact_civireport`.`is_deleted` != 1 AND `relationship_civireport`.`contact_id_a` IN (SELECT `id` FROM `civicrm_contact` WHERE is_deleted != 1) AND `relationship_civireport`.`contact_id_b` IN (SELECT `id` FROM `civicrm_contact` WHERE is_deleted != 1)**    ORDER BY value_vehicle_1_civireport.car_year_1 ASC, value_vehicle_1_civireport.vehicle_make_2 ASC, value_vehicle_1_civireport.vehicle_model_3 ASC  LIMIT 0, 10
  
  ```
  
  Then these steps happen in the Member log which I assume is a "tidy-up":
  
  ```
  [info] $Query = SELECT GET_LOCK( '181fec6b8c8018b4408d33805c7d9c73e37c2684', 3 )
  [info] $Query = SELECT COUNT(*) FROM civicrm_cache WHERE group_name = "CiviCRM Session" AND path = "_CRM_Report_Form_Contact_Detail_d3ccfb3ef5be498a753ab5c22d2f11d4_7593_container"
   [info] $Query = INSERT INTO civicrm_cache (`component_id`,`created_date`,`data`,`group_name`,`path`) VALUES
  (NULL,"2017-05-23 07:06:09","a:4:{s:8:\"defaults\";a:0:{}s:9:\"constants\";a:0:{}s:6:\"values\";a:1:{s:6:\"Detail\";a:0:{}}s:5:\"valid\";a:1:{s:6:\"Detail\";N;}}","CiviCRM Session","_CRM_Report_Form_Contact_Detail_d3ccfb3ef5be498a753ab5c22d2f11d4_7593_container")
  [info] $Query = SELECT RELEASE_LOCK( '181fec6b8c8018b4408d33805c7d9c73e37c2684' )
  [info] $Query = SELECT GET_LOCK( '70b6381734fcfe80b5c1603e4bcea118a9040f61', 3 )
  [info] $Query = SELECT COUNT(*) FROM civicrm_cache WHERE group_name = "CiviCRM Session" AND path = "CiviCRM_CRM_Report_Form_Contact_Detail_d3ccfb3ef5be498a753ab5c22d2f11d4_7593"
  [info] $Query = INSERT INTO civicrm_cache (`component_id`,`created_date`,`data`,`group_name`,`path`) VALUES
  (NULL,"2017-05-23 07:06:09","a:3:{s:8:\"entryURL\";s:127:\"http://xxx.xxx.org/index.php?option=com_civicrm&amp;task=civicrm/report/instance/181&amp;option=com_civicrm&amp;reset=1\";s:5:\"qfKey\";s:37:\"d3ccfb3ef5be498a753ab5c22d2f11d4_7593\";s:6:\"crmPID\";i:1;}","CiviCRM Session","CiviCRM_CRM_Report_Form_Contact_Detail_d3ccfb3ef5be498a753ab5c22d2f11d4_7593")
  [info] $Query = SELECT RELEASE_LOCK( '70b6381734fcfe80b5c1603e4bcea118a9040f61' )
  
  ```
  
  In the Admin log the SQL looks like this and then you can there is more activity afterwards which I assume is part of the report running correctly:
  
  ```
  [info] $Query = SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name as civicrm_contact_sort_name, contact_civireport.id as civicrm_contact_id, value_vehicle_1_civireport.car_year_1 as civicrm_value_vehicle_1_custom_1, value_vehicle_1_civireport.vehicle_make_2 as civicrm_value_vehicle_1_custom_2, value_vehicle_1_civireport.vehicle_model_3 as civicrm_value_vehicle_1_custom_3, value_vehicle_1_civireport.vehicle_model_code_31 as civicrm_value_vehicle_1_custom_31, value_vehicle_1_civireport.vehicle_body_style_4 as civicrm_value_vehicle_1_custom_4, value_vehicle_1_civireport.vehicle_body_description_29 as civicrm_value_vehicle_1_custom_29, value_vehicle_1_civireport.doors_5 as civicrm_value_vehicle_1_custom_5, value_vehicle_1_civireport.lhd_or_rhd_8 as civicrm_value_vehicle_1_custom_8, value_vehicle_1_civireport.body_serial_number_9 as civicrm_value_vehicle_1_custom_9, value_vehicle_1_civireport._of_cylinders_7 as civicrm_value_vehicle_1_custom_7, value_vehicle_1_civireport.engine_serial_number_10 as civicrm_value_vehicle_1_custom_10, value_vehicle_1_civireport.hudson_engine__11 as civicrm_value_vehicle_1_custom_11, value_vehicle_1_civireport.vehicle_condition_13 as civicrm_value_vehicle_1_custom_13, value_vehicle_1_civireport.vehicle_modified_32 as civicrm_value_vehicle_1_custom_32
          FROM civicrm_contact contact_civireport
  LEFT JOIN civicrm_value_vehicle_1 value_vehicle_1_civireport ON value_vehicle_1_civireport.entity_id = contact_civireport.id WHERE ( contact_civireport.is_deceased = 0 ) AND ( value_vehicle_1_civireport.car_year_1 IN (1909, 1910, 1911, 1912, 1913) ) AND ( ( value_vehicle_1_civireport.vehicle_make_2 IN ( 'username_0') ) )   ORDER BY value_vehicle_1_civireport.car_year_1 ASC, value_vehicle_1_civireport.vehicle_make_2 ASC, value_vehicle_1_civireport.vehicle_model_3 ASC  LIMIT 0, 10
  
  [info] $Query = SELECT FOUND_ROWS();
  
  [info] $Query =
  SELECT cg.table_name, cf.id
  FROM  civicrm_custom_field cf
  INNER JOIN civicrm_custom_group cg ON cg.id = cf.custom_group_id
  WHERE cg.extends IN ('Contact','Individual','Household','Organization') AND
        cg.is_active = 1 AND
        cf.is_active = 1 AND
        cf.is_searchable = 1 AND
        cf.data_type   NOT IN ('ContactReference', 'Date') AND
        cf.id IN (1,2,3,31,4,29,5,8,9,7,10,11,13,32)
  
  [info] $Query = SELECT *  FROM civicrm_custom_field WHERE (  civicrm_custom_field.id = 1 )
  
  < snip ; same sort of SQL >
  
  [info] $Query = SELECT contribution_civireport.contact_id as civicrm_contribution_contact_id   FROM
                              civicrm_contact  contact_civireport
                              INNER JOIN civicrm_contribution       contribution_civireport
                                      ON contact_civireport.id = contribution_civireport.contact_id
  
  [info] $Query = SELECT membership_civireport.contact_id as civicrm_membership_contact_id, membership_civireport.end_date as civicrm_membership_membership_end_date   FROM
                              civicrm_contact  contact_civireport
                              INNER JOIN civicrm_membership       membership_civireport
                                      ON contact_civireport.id = membership_civireport.contact_id
                           WHERE    contact_civireport.id IN ( 11791,11792,11843,11844,11846,11847,11853,11857,11858,11845 )
  
  [info] $Query = SELECT participant_civireport.contact_id as civicrm_participant_contact_id   FROM
                              civicrm_contact  contact_civireport
                              INNER JOIN civicrm_participant       participant_civireport
                                      ON contact_civireport.id = participant_civireport.contact_id
                           WHERE    contact_civireport.id IN ( 11791,11792,11843,11844,11846,11847,11853,11857,11858,11845 )
  
  [info] $Query = SELECT  id, label_a_b, label_b_a, contact_type_a, contact_type_b, contact_sub_type_a, contact_sub_type_b
   FROM civicrm_relationship_type  WHERE (  civicrm_relationship_type.is_active = 1 )
  
  [info] $Query = SELECT relationship_civireport.relationship_type_id as civicrm_relationship_relationship_type_id, relationship_civireport.contact_id_b as civicrm_relationship_contact_id_b ,contact_civireport.display_name as contact_b_name,  contact_a.id as contact_a_id , contact_a.display_name  as contact_a_name  FROM
                              civicrm_relationship relationship_civireport
  
                              LEFT JOIN civicrm_contact  contact_civireport ON
                                  contact_civireport.id = relationship_civireport.contact_id_b
                              LEFT JOIN civicrm_contact  contact_a ON
                                 contact_a.id = relationship_civireport.contact_id_a
                           WHERE    (contact_civireport.id IN ( 11791,11792,11843,11844,11846,11847,11853,11857,11858,11845 )
                                    OR
                                    contact_a.id IN ( 11791,11792,11843,11844,11846,11847,11853,11857,11858,11845 ) ) AND
                                    relationship_civireport.is_active = 1 AND
                                    contact_a.is_deleted = 0 AND
  
  [info] $Query = SELECT *  FROM civicrm_component
  
  [info] $Query =
  SELECT  v.label as label ,v.value as value, v.grouping as grouping
  FROM   civicrm_option_value v,
         civicrm_option_group g
  WHERE  v.option_group_id = g.id
    AND  g.name            = 'activity_type'
    AND  g.is_active       = 1  AND  v.is_active = 1  AND ( v.component_id IS NULL  OR v.component_id IN (SELECT id FROM civicrm_component WHERE name IN ("CiviEvent","CiviContribute","CiviMember","CiviMail","CiviReport","CiviPledge")) )  AND  ( v.component_id IS NULL OR v.component_id IN (1,2,3,4,6,8)) ORDER BY v.weight
  
  [info] $Query =
  SELECT  v.label as label ,v.value as value, v.grouping as grouping
  FROM   civicrm_option_value v,
         civicrm_option_group g
  WHERE  v.option_group_id = g.id
    AND  g.name            = 'activity_status'
    AND  g.is_active       = 1  AND  v.is_active = 1  AND ( v.component_id IS NULL  OR v.component_id IN (SELECT id FROM civicrm_component WHERE name IN ("CiviEvent","CiviContribute","CiviMember","CiviMail","CiviReport","CiviPledge")) )  ORDER BY v.weight
  
  [info] $Query =
  SELECT  v.label as label ,v.value as value, v.grouping as grouping
  FROM   civicrm_option_value v,
         civicrm_option_group g
  WHERE  v.option_group_id = g.id
    AND  g.name            = 'relative_date_filters'
    AND  g.is_active       = 1  AND  v.is_active = 1  AND ( v.component_id IS NULL  OR v.component_id IN (SELECT id FROM civicrm_component WHERE name IN ("CiviEvent","CiviContribute","CiviMember","CiviMail","CiviReport","CiviPledge")) )  ORDER BY v.weight
  
  [info] $Query = SELECT * FROM civicrm_preferences_date WHERE (  civicrm_preferences_date.name  = 'searchDate' )
  
  [info] $Query = SELECT * FROM civicrm_report_instance WHERE (  civicrm_report_instance.id = 181 )
  
  [info] $Query = SELECT data FROM civicrm_cache WHERE group_name = "navigation" AND path = "navigationList"
  
  [info] $Query =
  SELECT id, title, description, visibility, parents
  FROM   civicrm_group
  WHERE  id IN (133,139,145,137,144,33,31,30,36,29,32,38,35,39,37,34,2,24,28,20,26,23,21,27,22,25,116,4,117,111,114,12,18,16,13,10,11,7,6,9,19,17,8,15,3,112,128,119,147,138,120,124,125,126,127,113,131,130,143,53,52,54,55,115,56,57,58,59,60,61,62,63,64,65,67,66,68,69,70,72,71,73,74,75,76,77,78,79,80,81,82,83,84,86,85,108,87,88,89,105,104,136,91,99,100,107,92,94,95,98,97,96,101,106,102,103,93,109,141,48,50,40,46,118,44,43,45,51,47,49,5,140,142,146,122,135,134)
   ORDER BY title
  
  [info] $Query = SELECT *  FROM civicrm_navigation  WHERE (  civicrm_navigation.id = 483 )  AND (  civicrm_navigation.domain_id = 1 )
  
  [info] $Query = SELECT GET_LOCK( '51f408a0235586dbe891208004a7e5fc67e72dad', 3 )
  
  [info]  QUERY DONE IN 0.000398  seconds. Result is 1 rows by 1 columns.
  
  [info] $Query = SELECT COUNT(*) FROM civicrm_cache WHERE group_name = "CiviCRM Session" AND path = "_CRM_Report_Form_Contact_Detail_6de5d8ad3d8789ed2d9a0038cbd3a7a6_5578_container"
  
  [info]  QUERY DONE IN 0.000546  seconds. Result is 1 rows by 1 columns.
  
  [info] $Query = INSERT INTO civicrm_cache (`component_id`,`created_date`,`data`,`group_name`,`path`) VALUES
  (NULL,"2017-05-23 07:07:57","a:4:{s:8:\"defaults\";a:0:{}s:9:\"constants\";a:0:{}s:6:\"values\";a:1:{s:6:\"Detail\";a:0:{}}s:5:\"valid\";a:1:{s:6:\"Detail\";N;}}","CiviCRM Session","_CRM_Report_Form_Contact_Detail_6de5d8ad3d8789ed2d9a0038cbd3a7a6_5578_container")
  
  [info] $Query = SELECT RELEASE_LOCK( '51f408a0235586dbe891208004a7e5fc67e72dad' )
  
  [info] $Query = SELECT GET_LOCK( '431ea828bed0270e81256e366f6cf4b682d6cfac', 3 )
  
  [info] $Query = SELECT COUNT(*) FROM civicrm_cache WHERE group_name = "CiviCRM Session" AND path = "CiviCRM_CRM_Report_Form_Contact_Detail_6de5d8ad3d8789ed2d9a0038cbd3a7a6_5578"
  
  [info] $Query = INSERT INTO civicrm_cache (`component_id`,`created_date`,`data`,`group_name`,`path`) VALUES
  (NULL,"2017-05-23 07:07:57","a:3:{s:8:\"entryURL\";s:127:\"http://xxxx.xxxx.org/index.php?option=com_civicrm&amp;task=civicrm/report/instance/181&amp;option=com_civicrm&amp;reset=1\";s:5:\"qfKey\";s:37:\"6de5d8ad3d8789ed2d9a0038cbd3a7a6_5578\";s:6:\"crmPID\";i:1;}","CiviCRM Session","CiviCRM_CRM_Report_Form_Contact_Detail_6de5d8ad3d8789ed2d9a0038cbd3a7a6_5578")
  
  [info] $Query = SELECT RELEASE_LOCK( '431ea828bed0270e81256e366f6cf4b682d6cfac' )
  
  ```
  
  **Tags:** custom-reports, sql
  
  > **Comments:**
  > **username_1**: What version of CiviCRM is this?  What is the underlying report template, and is it custom?  Please update your question to reflect that.  If this is a current version of CiviCRM and a non-custom report template, we should document the steps to replicate this on demo.civicrm.org and file a bug.  
  > **username_0**: So I have done a little more digging and have attached two files from the SQL log. One is using a member account and the other the Admin account and you can see the differences in there. Is there a way to upload these files?  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_2 on 2017-12-22. Score: 4 | Accepted answer
  
  I just ran into this issue (on current Drupal, current CiviCRM setup) and confirmed that the way it's set now, a non-admin user needs the permission "access deleted contacts" to run this report.
  
  Hope that's helpful - not much of an answer but I lost access to my old account and don't have enough rep to post a comment :)
  
  > **Comments:**
  > **username_3**: Solution of username_2 worked for me.  Although I don't want them all to have access to the deleted contacts, it fixed my issue. And since my PHP knowledge is null, this is great!  
  
  ---
  
  