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 ( '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
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&task=civicrm/report/instance/181&option=com_civicrm&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 ( '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 )
< 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&task=civicrm/report/instance/181&option=com_civicrm&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' )
