Name based call to report attribute parameter fails after data sharing agreement

[andrewvanbreda]

There is currently a bug in the warehouse which has reared its ugly head during development of EBMS and SPRING (EU PoMS).
The problem occurred where both projects used location attributes called CMS User ID (the one for SPRING has been renamed since....the IDs are 234 and 285).
In the code the projects used a get_population_data PHP call to the library/locations/locations_list report.
extraparams were passed to the report in the following way

$siteParams += array('locattrs'=>'CMS User ID', 'attr_location_cms_user_id'=>hostsite_get_user_field('id'));

Passing the param in as a name works automatically, however when an data sharing agreement was setup between the two websites, the system was taking into account the wrong parameter.

An example of a failing query is as follows (this was collected from live warehouse with Debug log threshold set).
There is a problem with the EXISTS statements that look at the attributes.

SELECT l.id as "location_id",
l.name as "name",
l.location_type_id as "location_type_id",
tinput.term as "location_type",
l.centroid_sref as "centroid_sref",
l.centroid_sref_system as "centroid_sref_system",
st_astext(coalesce(l.boundary_geom, l.centroid_geom)) as "geom",
(
SELECT string_agg(mvattr_id_location_cms_user_id.id::text, ', ')
FROM location_attribute_values mvattr_id_location_cms_user_id
WHERE mvattr_id_location_cms_user_id.location_id=l.id
AND mvattr_id_location_cms_user_id.location_attribute_id=234
AND deleted = FALSE
) as attr_id_location_cms_user_id,
(select string_agg(mvattr_location_cms_user_id.int_value::text, ', ')
from location_attribute_values mvattr_location_cms_user_id
where mvattr_location_cms_user_id.location_id=l.id and mvattr_location_cms_user_id.location_attribute_id=234 AND deleted = FALSE) as attr_location_cms_user_id,
(
SELECT string_agg(mvattr_id_location_cms_user_id.id::text, ', ')
FROM location_attribute_values mvattr_id_location_cms_user_id
WHERE mvattr_id_location_cms_user_id.location_id=l.id
AND mvattr_id_location_cms_user_id.location_attribute_id=285
AND deleted = FALSE
) as attr_id_location_cms_user_id,
(select string_agg(mvattr_location_cms_user_id.int_value::text, ', ')
from location_attribute_values mvattr_location_cms_user_id
where mvattr_location_cms_user_id.location_id=l.id and mvattr_location_cms_user_id.location_attribute_id=285 AND deleted = FALSE) as attr_location_cms_user_id
FROM locations l
LEFT JOIN locations_websites lw on lw.location_id=l.id AND lw.deleted=false
LEFT JOIN (termlists_terms ttl1
JOIN terms tinput ON tinput.id=ttl1.term_id
JOIN termlists_terms ttl2 ON ttl2.meaning_id=ttl1.meaning_id
JOIN terms t ON t.id=ttl2.term_id
) ON ttl1.id=l.location_type_id

WHERE l.deleted=false
AND (lw.website_id in (118) or lw.website_id is null)
AND (CAST(l.location_type_id AS character varying)='777' OR '777'='' OR t.term='777')
AND EXISTS(
SELECT mvattr_location_cms_user_id.int_value
FROM location_attribute_values mvattr_location_cms_user_id
WHERE mvattr_location_cms_user_id.location_id=l.id AND mvattr_location_cms_user_id.location_attribute_id=285
AND mvattr_location_cms_user_id.int_value = 1535
AND mvattr_location_cms_user_id.deleted = false
)

ORDER BY l.name ASC